Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 14 hours 54 min ago

Change local_temp_tablespace to shared TEMP

Wed, 2020-08-12 13:06
Hi, I am trying to find the downside of setting local_temp_tablespace to TEMP tablespace which is a shared temp. The reason is because of a bug, if local_temp_tablespace is NULL and dba_users.spare9 is NULL, then Oracle assigns SYSTEM tablespace as local_temp_tablespace when I issue alter user command. For example, if a user AGUPTA has spare9 as NULL in DBA_USERS and local_temp_tablespace is currently NULL and I issue the command to change password: <code>alter user AGUPTA identified by xpS2Z^4%g%0h;</code> Then, the local_temp_tablespace for AGUTPA changes to SYSTEM. This is not good. Mike Dietrich has a blog post about it. So, we did a small test and found that if we switch all users who have NULL for local_temp_tablespace to use TEMP tablespace, then the issue does not appear. The local_temp_tablespace stays at TEMP when changing password. So, my question is: Is there a downside to changing every user's local_temp_tablespace to shared TEMP? Thanks
Categories: DBA Blogs

PRAGMA SERIALLY_REUSABLE implications in a callback service

Wed, 2020-08-12 13:06
<b></b>Scenario: Oracle Recipe Tool / Microservices JNDI : jdbc/SOAXAOPS PLSQL : schema.pkg1.procedure This is the entry point to an on-premise DB package. It can be called from cloud and non-cloud services. Now whenever I would compile schema.pkg1 The callback will give below error : ORA-04065: not executed, altered or dropped package body "schema.pkg1" ORA-06508: PL/SQL: could not find program unit being called: "schema.pkg1" This error can be bypassed if a DDL was issued in this schema or existing stale connections are explicitly killed. So I added PRAGMA SERIALLY_REUSABLE; to schema.pkg1 based on below url ======================================================================== https://stackoverflow.com/questions/1761595/frequent-error-in-oracle-ora-04068-existing-state-of-packages-has-been-discarde https://docs.oracle.com/en/cloud/paas/integration-cloud/database-adapter/resolve-error-ora-04068-existing-state-packages-has-been-discarded.html https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99977 In addition integrations team did this modification: Under Connection Properties: Test Connections on Reserve: Yes Test Table Name: SQL begin dbms_session.modify_package_state(dbms_session.reinitialize); end; Seconds to trust Idle Pool Connection: 0 This solved initial error of ORA-04065 , ORA-06508 However it gave 1-off error : ORA-06508: PL/SQL: could not find program unit being called This was at the line in schema.pkg1 which was giving call to schema.pkg2 Now schema.pkg2 is not having PRAGMA SERIALLY_REUSABLE; In a request set of 2 requests... first one faced this err and subsequent requests have been fine so far... (This is UAT environment) ============== Question is : ============== When I recompile pkg (HOT patch) in UAT can it re happen. Do I need to add this pragma to all nested packages. What are the pros and cons of using this Pragma apart from trigger and SQl prompt usage as mentioned on Oracle documentation. Is there an alternate way to deal with these errors for callback services.
Categories: DBA Blogs

Oracle has any feature similar to "Always Encrypted" that is offered by SQL server?

Tue, 2020-08-11 18:46
Hello, It would be great if you can help me here. Can you please share if Oracle has any feature similar to the "Always Encrypted" feature offered by SQL server? Link pasted at end has information on "Always Encrypted". I understand that Oracle offers data redaction to mask data. However, my understanding is that users with high authorization can bypass it. Oracle also offers Vault to control data access. However, there still will be Oracle users that can see the data in clear. It would be really helpful if you can share some pointers. Thanks, AB ------------------------------------------------------------------------------------------------------------------------------- Link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15 Text from this link: Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. By ensuring on-premises database administrators, cloud database operators, or other high-privileged unauthorized users, can't access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to store their data in Azure, and enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.
Categories: DBA Blogs

Dynamically passing sequence name to get currval

Tue, 2020-08-11 18:46
I am trying to get the currval of all the user sequences in the schema. When I run the below sql it gives me invalid sql statement. I am not sure if the below is the right way to achieve it. Please advise. Assumption: The current value of the sequences are already set in the session. <code> set serveroutput on; declare sq number; sqnm varchar2(50); stmt varchar2(1000); cursor sqnc is (select sequence_name from user_sequences); begin for row in sqnc loop sqnm := row.sequence_name; stmt := 'SELECT' || sqnm ||'.currval into' || sq || 'from dual'; execute immediate stmt; dbms_output_put_line(sqnm || ' ' ||sq); end loop; end; </code>
Categories: DBA Blogs

Options to quickly access large portions of rows

Tue, 2020-08-11 18:46
Hello, Tom. We have a fact table, that is partitioned by day and stores the last 90 days of data. Sometimes users of the application can change the status of record from 'ACTIVE' to 'CANCELED'. There are a lot of heavy analytical queries against that table that include full scans but only consider the 'ACTIVE' records. The number of 'CANCELED' record can wary greatly over time, from 5% to 60%. Right now it has 37 million active ones, and 67 million canceled, so my full scan could be 3 times faster. My question is: what is the best option to quickly access all the active records? B-tree index won't help, because there are too many rows to retrieve. Bitmap index seems to be a bad choice, since there are a lot of DML operations. I wanted to try subpartitioning by list and move the rows to the 'CANCELED' subpartition, but I immediately have concerns: There are 7 indexes on the table now. Moving a lot of rows between sections would require a lot of time and could potentially fill up the undo if someone decides to change the status of tens of millions of rows at a time(users can do and will do that). Since the table is partitioned by day, any blank space left after row movent in sections older than today won't be reused or reclaimed and a full scan will take just a much time. That makes the whole idea almost useless. I am afraid that shrinking the entire table could fill up the undo segment. I don't have an environment close in specs to our PROD environment, so I can't even really test my concerns with undo. Unfortunately we can't upgrade to 12.2 for a few more months, so move online is not availbable. Is there another option that I am missing or should I just run shrink space partition by partition on a daily basis?
Categories: DBA Blogs

Ranking based on time break

Tue, 2020-08-11 18:46
i want to make rankning of trucks exit based on break more than 1 hour like eg below TRUCK EXIT T1 10:00 PM T2 10:05 PM T3 12:00 PM T4 12:05 PM T5 12:10 PM T6 12:20 PM Result should be like...... leaving break more than 1 hours gaps 10:00 10:05 1 12:00 12:20 2
Categories: DBA Blogs

Update in Oracle DB

Tue, 2020-08-11 18:46
Hi dear AskTOM team. Have a great day to everyone. I have some confusion about UPDATE TABLE statement in Oracle DB 12cr2. Let's assume we have 3 users: U1; U2; U3; U1 has a table called TEST_1, and U2 and U3 both have UPDATE privilege on that table. My question is that: <b>If U2 and U3 try to update same rows in that particular table at the same time what will happen? How Oracle will control such kind of processes?</b> Thanks beforehand!
Categories: DBA Blogs

how to import sequence

Tue, 2020-08-11 18:46
export is done on table level using 9.2.0.1 exp user/password tables=emp,foo file=test.dmp during import sequences never got imported. This is the default behavior of oracle. I would appreciate if you please advise on the followings: 1. How to import sequences in table level export? 2. How to get the same sequence value at the time of export? Thanks
Categories: DBA Blogs

Converting XML to JSON using Apex

Tue, 2020-08-11 18:46
Hello Everyone, There is a table(xxln_vs_publish_stg) has xmltype column(xml_data) which stores XML data. I have a requirement to convert XML data to json data. For that, I am using apex_json.write to convert. While executing below logic for changing it, I am getting Error as: ORA-20987: APEX - JSON.WRITER.NOT_OPEN - Contact your application administrator. Can you please help what is it I am doing which is wrong. <code> DECLARE l_xml sys.xmltype; l_amount BINARY_INTEGER := 32000; l_buffer RAW(32000); l_pos INTEGER := 1; l_stage NUMBER; content CLOB; content_blob BLOB; content_length NUMBER; BEGIN SELECT xml_data INTO l_xml FROM xxln_vs_publish_stg WHERE xml_data IS NOT NULL AND ROWNUM < 2; content := xmltype.getclobval(l_xml); xxln.convert_clob_to_blob(content, content_blob); content_length := dbms_lob.getlength(content_blob); dbms_output.put_line(content_length); apex_json.initialize_clob_output; IF dbms_lob.getlength(content_blob) < 32000 THEN apex_json.write(content); ELSE WHILE l_pos < content_length--DBMS_LOB.GETLENGTH(v_output_file_blob) LOOP dbms_lob.read(content_blob, l_amount, l_pos, l_buffer); apex_json.write(content); l_pos := l_pos + l_amount; END LOOP; END IF; dbms_output.put_line(apex_json.get_clob_output); apex_json.free_output; END; </code>
Categories: DBA Blogs

Online table redefinition

Fri, 2020-08-07 23:06
This function DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS copy the dependent objects of the base table into interim. What is the use of it? And what will happen if I do not run this step?
Categories: DBA Blogs

DDL Script of partition in oracle

Fri, 2020-08-07 23:06
Hi Tom, How to get DDL scripts of table partition and index partition in oracle. Thanks, Leon.
Categories: DBA Blogs

private database link public synonym

Fri, 2020-08-07 04:46
Tom, What I am attempting to do is create a private database link then create a public synonym for that link. I need the link to run procedeure A on the remote database. i.e Execute immediate procedure a @remote.database. These reasoning behind the private link is a secure issue dictated by the higher offices. Is this possible? Is there a better way to do it?
Categories: DBA Blogs

Total weight selection of rows

Fri, 2020-08-07 04:46
I have 1000 cubes of weight between 360 and 430 grams. I need to choose a set of cubes which will have weigh 50.000 grams. Do you have any idea how to do it in SQL or PL/SQL? <code>CREATE TABLE CUBES ( ID VARCHAR2(100 CHAR), WEIGHT NUMBER(10,3) ); BEGIN FOR i IN 1..1000 LOOP INSERT INTO cubes VALUES (i, round(dbms_random.value( 360, 430), 3)); END LOOP; COMMIT; END;</code>
Categories: DBA Blogs

Can we use RETURNING CLAUSE along with CURRENT OF clause in update statement.

Thu, 2020-08-06 10:26
Hi Tom, I am using below update statement in my procedure to return few columns that are getting updated in the update statement. <code>UPDATE DUMMY_TABLE SET DUMMY_STATUS = 'ABC' WHERE CURRENT OF DUMMY_CURSOR RETURNING DUMMY_FIELD1, DUMMY_FIELD2 BULK COLLECT INTO TAB_FIELD1, TAB_FIELD2;</code> The above code works if i am not using CURRENT OF CLAUSE, but is giving error when used like above... I want to know if i am making any syntax error here...or this is not possible at all.. Note: I cannot remove current of clause from the update statement, and still I have to return the columns that are getting updated. Thanks
Categories: DBA Blogs

ORA-01031: insufficient privileges, cannot login as any user

Thu, 2020-08-06 10:26
I ran below command and restarted docker container. <code>alter system set processes = 1 scope = spfile;</code> after this I am not able to login to DB at all. <code> root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges </code> I tried to resolve it with below command, tried login again, still getting same error orapwd file=filename password=password entries=100 Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file and manually assign processes=100 and back to processes=1 this didnt help. can someone guide here please?
Categories: DBA Blogs

Loading CLOB data (more than 32k char) into Oracle DB through Apex

Thu, 2020-08-06 10:26
Hi, I am currently working on developing apex web application which requires to capture CLOB data and save it to DB. I am using Rich text editor to capture the content and noticed that only max of 32k characters can be loaded through the page item. when I try to push data more than 32k char, no data is being sent to the DB. Please suggest me a way to capture data more than 32k through apex page item. Oracle DB Version: 12c Apex version:20.1 Kindly let me know if I am missing any details Thanks, Murugananth
Categories: DBA Blogs

are WITH READ ONLY sand WITH CHECK OPTION syntaxes used for views and tables?

Thu, 2020-08-06 10:26
Hi, I am not sure whether or not WITH READ ONLY and WITH CHECK OPTION syntaxes used for views and tables only. I read on Oracle Help Center with the following link https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30. It is said that: WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated. WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause. I hope to receive your response. Thanks, Kris.
Categories: DBA Blogs

Comparing 2 Nested Table Collection Which have 180 field defined in each collection by passing field name dynamically.

Wed, 2020-08-05 16:06
I have 2 tables which of same structure with around 180 columns in each table with one columns as PK. Each table have around 200 k records. I need to compare to tables columns by column and if for that records any difference is found for any of the remaining 179 columns then need to track that column name and the both the old values and new value from that column from both tables. This can be achieve by SQL statement with UNION and group and LEAD functions but as need to compare the for 179 fields the length of the SQL code is very long. So thought of used nested table collection to compare both the tables by bulk collect both the values is 2 different nested tables and iterate them. First loop to iterate using collection count value and second loop using USER_TAB_COLS to iterate based on number of columns in the tables. Is there any possible to pass the field name to the nested loop dynamically ? Below is the sample code for that. <code>SET SERVEROUTPUT ON; DECLARE TYPE TEST1_TYPE IS TABLE OF TEST1%ROWTYPE ; TEST1_TAB TEST1_TYPE; TEST2_TAB TEST1_TYPE; lcCol1 VARCHAR2(3000); lcCol2 VARCHAR2(3000); lQuery VARCHAR2(3000); CURSOR CUR_TAB_COL IS SELECT COLUMN_NAME ,DATA_TYPE FROM USER_TAB_COLS WHERE TABLE_NAME='TEST1' ORDER BY COLUMN_ID; TYPE COL_TYPE IS TABLE OF CUR_TAB_COL%ROWTYPE; COL_TAB COL_TYPE; BEGIN SELECT * BULK COLLECT INTO TEST1_TAB FROM TEST1 ORDER BY ID; SELECT * BULK COLLECT INTO TEST2_TAB FROM TEST2 ORDER BY ID; OPEN CUR_TAB_COL; FETCH CUR_TAB_COL BULK COLLECT INTO COL_TAB; CLOSE CUR_TAB_COL; FOR I IN 1..TEST2_TAB.count LOOP FOR j IN COL_TAB.FIRST..COL_TAB.LAST LOOP lQuery:='SELECT TEST1_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol1; lQuery:='SELECT TEST2_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol2; END LOOP; END LOOP; END; /</code>
Categories: DBA Blogs

JSON_OBJECT throws error in Stored Procedure

Wed, 2020-08-05 16:06
Dear Team, I am trying to use the JSON functions in a stored procedure and TOAD throws the syntax error. <b> Found 'value', an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)</b> Below is the query that I use. However when I try to run outside the stored procedure, it works fine. <code>SELECT JSON_OBJECT ( 'empid' value '1' , 'name' value 'Tom' , 'address' value '23333' ) into emp_json FROM dual ;</code> Could you please help me to identify the cause of the error. Thank You.
Categories: DBA Blogs

Rebuilding Oracle Text Indexes

Wed, 2020-08-05 16:06
Dear Team, In our IFS Applications product, we heavily use Oracle text indexes. we are not updating the indexes real time instead we do it at a separate time interval through <i>Ctx_Ddl.Sync_Index</i> passing a CLOB document. We synchronize the indexes with a default 30 minute interval. In the maintenance cycle, we optimize an index once a week using <i>Ctx_DdL.Optimize_Index</i> method using FAST option. Also we perform an ALTER INDEX REBUILD for the text index once a week. Recently we encountered some errors with this REBUILD operation at few customers so having a thought how we should go forward. When we went through the Oracle documentation, we saw some articles related to this but they are somewhat confusing. https://docs.oracle.com/en/database/oracle/oracle-database/19/ccref/CTX_DDL-package.html#GUID-28E079B1-D5CA-4264-B1C0-A1C5CE174C55 It says: "Using this procedure to optimize the index is recommended over using the <i>ALTER INDEX</i> statement." https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/maintaining-oracle-text-indexes.html#GUID-10365262-8B48-40AA-B1F3-DE3268EA9B39 It says: "You might rebuild an index when you want to index with a new preference" We have few questions to clarify. 1. We are <b>not changing any preferences</b> of the text index once after it was created at the installation time, so do we want to rebuild the text index in a scheduled manner? Is there any other benefit doing so? 2. Do you believe <i>Ctx_Ddl_Optimize_Index </i>with <b>REBUILD </b>option instead <b>FAST </b>option would be a good option to have if we skip rebuilding the index using <i>ALTER INDEX</i> statement? Or is it unnecessary in our situation. 3. Going forward, do you see any other risks if we are to remove <i>ALTER INDEX REBUILD</i> & continue with only <i>Ctx_Ddl_Optimize_Index</i> with <b>FAST </b>option? May be in aspects such as performance, etc. Thanks & Best Regards, Navinth
Categories: DBA Blogs

Pages