Tom Kyte

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

Visit the link when the event is about to start.

Thu, 2021-10-21 18:26
Please can I get the zoom link for today's conference?
Categories: DBA Blogs

Autonomous transactions and commit point optimization

Thu, 2021-10-21 00:26
Hi We have an application written with PL/SQL and thus with commit point optimization. However AWR reports show a lot of log file sync waits. How do autonomous transactions, which we are using for logging purposes operate with pl/sql code. Is the commit point optimization utilized? If some procedure is called which is defined as an autonomous transaction and no actual dml operations are done (which could happen in logging operations), is there still some commit handling done ? If so, what ? lh
Categories: DBA Blogs

How to calculate timestamp from an old ora_rowsn taking as a reference a current ora_rowscn timestamp_to_scn(sysdate) from DUAL

Thu, 2021-10-21 00:26
As you know, the association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited time period and an error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old. Example: <code>select scn_to_timestamp(max(ora_rowscn)) from MY_TABLE</code> <i><b>ORA-08181: el numero especificado no es un numero de cambio del sistema valido ORA-06512: en "SYS.SCN_TO_TIMESTAMP", linea 1 08181. 00000 - "specified number is not a valid system change number" *Cause: supplied scn was beyond the bounds of a valid scn. *Action: use a valid scn.</b></i> Nevertheless, I can obtain the max(ora_rowscn) from MY_TABLE without any kind of error: <code> select max(ora_rowscn) from MY_TABLE MAX(ORA_ROWSCN) --------------- 99464620 </code> I would like to obtain the corresponding timestamp of this ORA_ROWSCN approximately (I don't care if it may vary some hours), <b>without using any AUDIT tool but using PL/SQL statements</b>. Does exist any relationship between the above MAX(ORA_ROWSCN) from MY_TABLE and the current ORA_ROWSCN from sysdate? <code> select timestamp_to_scn(sysdate), TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') from dual TIMESTAMP_TO_SCN(SYSDATE) TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') ------------------------- ---------------------------------------- 116631465 13-10-2021 12:12:02 </code> Could I calculate, using any kind of algorithm, the corresponding timestamp to <b>99464620</b> value? Thanks in advance.
Categories: DBA Blogs

i want to export only the objects owned by given schema using EXPDP

Thu, 2021-10-21 00:26
Hi, i want to export only the objects owned by given schema using EXPDP, but unfortunately it is exporting all the objects which schema has access to. i am using below syntax. <code>expdp school/school@orcl schemas=(demo) directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=expdp.log</code> Please help. Thanks Fahd
Categories: DBA Blogs

Request for script to import data

Thu, 2021-10-21 00:26
Dear Ask TOM Team, Thank you for your appreciated efforts and providing the guidance to all concerned clients. My question: I have running and in production oracle DB, 11g ... and would like to import and update the data from other DB with the same structure and version, Please: 1- provide me the script to do such required import data to the running DB. 2- provide me the script to do such required export data to the running DB. Regards, Reda Eltayef
Categories: DBA Blogs

index

Thu, 2021-10-21 00:26
Tom: I have a question regarding index. can you explain me in detail what the following means. If the index is a concatenation of multiple columns and one of the columns contains a NULL value, the row will be in the index column containing the NULL value and will be left empty. +++++++++++++++++++++++++++++++++++++++++++++ above is exactly what it says in a book. I can't even understand it's english."the row will be in the index column containing the NULL value and will be left empty" what does that mean?
Categories: DBA Blogs

Different sql id/sql text showing up in v$session

Wed, 2021-10-20 06:06
Hello! I have a particular ETL job that fires below 4 select queries on a view definition to our Oracle database - <code>select * from view where mod(id,4) = 1; select * from view where mod(id,4) = 2; select * from view where mod(id,4) = 3; select * from view where mod(id,4) = 0; </code> However when the queries start executing and I check v$session for the 4 sessions running these 4 queries, I see a common sql id showing up for all these 4 queries and the sql text for this sql id seems completely unfamiliar/unrelated to the queries that are actually being executed. So I am a bit lost as I have not come across such scenario earlier. What could this sql id be and why would it be showing up in v$session for all 4 different queries?
Categories: DBA Blogs

Out Parameter using the scheduler

Tue, 2021-10-19 11:46
Hi, There is no test case needed, it's more of a question. We have a stored proc which on success of the execution of the stored proc there is either a 1 or 0 returned. Can the 1 or 0 be captured by the oracle scheduler? Thanks Vic
Categories: DBA Blogs

Help to understand this recursive query

Thu, 2021-10-14 22:06
Hi Tom, I am not able to fully understand below recursive query. Especially, the condition "WHERE POS > 0" and " WHERE POS = 0". my understanding is pos is result of REGEXP_INSTR (STR, '[A-Z][0-9]{5}',1, RN). 1. if the first POS > 0, how could the final condition has pos = 0. 2. what's difference between pos > o and regexp_instr(str, '[A-Z][0-9]{5}', 1, rn)>0. regexp_instr(str, '[A-Z][0-9]{5}', 1, rn)>0 doesn't give expected result. thanks in advance. <code>WITH T AS (SELECT '///2E/A12345/E53421$$@#/A123456*#/A123456**4E53421$$$$$@@!!!' STR FROM DUAL UNION ALL SELECT 'ABC' FROM DUAL UNION ALL SELECT 'ABC67890' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679UVW384453' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679UVW384453' || 'ABC67890XYZ345679' FROM DUAL UNION ALL SELECT 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' FROM DUAL), TMP (RN, POS, STR, STR0) AS (SELECT 1, 1, STR, STR FROM T UNION ALL SELECT RN + 1, REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN), REGEXP_REPLACE (STR, SUBSTR (STR, REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN), 6), '', REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN) + 6), STR0 FROM TMP WHERE POS > 0) SELECT * FROM TMP WHERE POS = 0</code>
Categories: DBA Blogs

begin_time/end_time in DBA_HIST_SNAPSHOT vs DBA_HIST_SYSMETRIC_SUMMARY vs AWR report

Wed, 2021-10-13 09:26
Hello Connor & Chris :-), I have some confusion about the <b>begin_time/end_time</b> in <b>DBA_HIST_SNAPSHOT</b> vs <b>DBA_HIST_SYSMETRIC_SUMMARY</b> vs <b>AWR report</b>. <code> 13:52:22 SYS@emcdb> desc dba_hist_snapshot Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER STARTUP_TIME NOT NULL TIMESTAMP(3) <b>BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3) END_INTERVAL_TIME NOT NULL TIMESTAMP(3)</b> FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1) SNAP_LEVEL NUMBER ERROR_COUNT NUMBER SNAP_FLAG NUMBER SNAP_TIMEZONE INTERVAL DAY(0) TO SECOND(0) BEGIN_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE END_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE CON_ID NUMBER </code> <code> 13:54:21 SYS@emcdb> desc dba_hist_sysmetric_summary Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER <b>BEGIN_TIME NOT NULL DATE END_TIME NOT NULL DATE</b> INTSIZE NOT NULL NUMBER GROUP_ID NOT NULL NUMBER METRIC_ID NOT NULL NUMBER METRIC_NAME NOT NULL VARCHAR2(64) METRIC_UNIT NOT NULL VARCHAR2(64) NUM_INTERVAL NOT NULL NUMBER MINVAL NOT NULL NUMBER MAXVAL NOT NULL NUMBER AVERAGE NOT NULL NUMBER STANDARD_DEVIATION NOT NULL NUMBER SUM_SQUARES NUMBER CON_DBID NUMBER CON_ID NUMBER </code> I understand that there have begin/end snap time in an AWR report, is it like this, begin_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT) and end_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT)? or begin_snap_time (AWR) = begin_time (DBA_HIST_SYSMETRIC_SUMMARY) and end_snap_time (AWR) = end_time (DBA_HIST_SYSMETRIC_SUMMARY)? You know, since a snap_id has a begin/end_interval_time but an AWR report usually captures between two snap_id, so now I'm not sure where is the begin/end snap time in an AWR report? Best Regards Quanwen Zhao
Categories: DBA Blogs

Database link : Relation of sessions between databases

Mon, 2021-10-11 21:06
Hello, In the context of database link, is there a possibility to get the relation between both sessions of database "A" and database "B"? (database "A" query objects from database "B" using a dblink) In other words, from database "B" can we retrieve the SID of database "A" that create the session in database "B" (and vis versa, from database "A" the SID of database "B" that is created for the dblink)? I hope that my question is understandable. Thank you very much, Sebastien.
Categories: DBA Blogs

Can kafka read uncommitted data from oracle database

Fri, 2021-10-08 01:26
Hi tom, Hope you are doing well. I want to ask you that, can kafka read uncommitted data form oracle database from a session where data is not committed?
Categories: DBA Blogs

Archivelog backup using RMAN

Fri, 2021-10-08 01:26
Hi I have a multi tenant 19c database below is the directory where my archives are generated /erpprd64/oradata/prd64cdb/fast_recovery_area/PRD64CDB/archivelog/ in this I have a folder with today's date 2021_10_05 and below are archives generated in it o1_mf_1_783_joqsfg35_.arc o1_mf_1_785_joqy8dk1_.arc o1_mf_1_787_jor31mh6_.arc o1_mf_1_789_jor7wz6n_.arc o1_mf_1_791_jorf9hoc_.arc o1_mf_1_784_joqw4oq3_.arc o1_mf_1_786_jor0d91w_.arc o1_mf_1_788_jor5681t_.arc o1_mf_1_790_jorbn5gh_.arc o1_mf_1_792_jorhyqhs_.arc is there a way to only backup (using rman) the archives generated in today's folder I need this because I will then create a backup script which will take rman backup of database archives generated today control file then using this backup I will refresh my database on test server please assist Thanks, Abdul Wahid Solanki
Categories: DBA Blogs

How to move the Jobs from one User to another User Present in the Different DB server

Fri, 2021-10-08 01:26
Team, This is is continuation with the below link : https://asktom.oracle.com/pls/apex/f?p=100:12::::RP:P12_QUESTION_ID,P12_PREV_PAGE:9543884800346428497,11 I could not continue on that link , so sorry to raise a new request. The solution provided there works well in case of both the DB users are in the same server. But now we are migrating to new DB user, we want to migrate the scheduled jobs as well. Please let us know if it can be done via SQl or PL/SQL. Any sample code on this is most welcome. Regards, Vinesh
Categories: DBA Blogs

Trying to stop export import job and get an error

Fri, 2021-10-08 01:26
hi tom i wanted to ask about interactive command(CTRL+C) when using datapump import export when i use an export utility and trying to kill the export jobs it works with no problem expdp test/asdzxc@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test <code>Export: Release 19.0.0.0.0 - Production on Tue Oct 5 11:46:24 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_SCHEMA_02": test/********@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Export> kill_job Are you sure you wish to stop this job ([yes]/no): yes</code> but when i try to stop the export job using stop_job i get an error <code>expdp test/asdzxc@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test Export: Release 19.0.0.0.0 - Production on Tue Oct 5 11:50:08 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_SCHEMA_02": test/********@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Export> stop_job UDE-31626: operation generated ORACLE error 31626 ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4791 ORA-06512: at "SYS.DBMS_DATAPUMP", line 6204 ORA-06512: at line 1 </code> how does this error happen and how to fix it
Categories: DBA Blogs

Undo blocks stored in the redo log buffer

Fri, 2021-10-08 01:26
hey tom I read from couple of article which state that when we issue a dml statement and commit, the changes made to the database and undo block is stored into the redo log buffer. what purpose does this undo block do exactly?
Categories: DBA Blogs

Tablespace Offline

Fri, 2021-10-08 01:26
Hey tom i wanted to ask a simple thing, i wanted to know when do we need to recover a tablespace when we alter the tablespace offline temporary. i heard that sometimes we need to recover it especially when there is an offline tablespace when we alter the table space to offline temporary is this true or we dont have to recover a tablespace when we alter the tablespace to offline temporary?
Categories: DBA Blogs

Using the DBMS_STATS-package

Fri, 2021-10-08 01:26
We are doing performance-testing of SQL-statements in our project. When running explain-plans on the statements, the results are often not realistic due to the fact that we have not loaded the tables with data, and therefore the indexes, in most cases, will not be used as they will in production. I found the DBMS_STATS-package and am wondering if I can use this package to virtually load my test-environment when using the explain-plan. Our Oracle-support team doubt that it will be possible to extract the data using GET/EXPORT_TABLE/INDEX/COLUMN_STATS, modifying the data and then using SET/IMPORT_TABLE/INDEX/COLUMN_STATS to put new statistics back into the data-dictionary-tables. Are there any descriptions on the tables the CREATE_STAT_TABLE makes? Regards, Harald
Categories: DBA Blogs

Exception Handling for bulk insert

Fri, 2021-10-08 01:26
Hi Tom, Thank you. Your blogs are really helpful. I have a specific requirement while capture the exception that might occur at the time of bulk insert. What I need to do is when an exception has occurred, while updating the information in the exception table I need to concatenate all the fields from my staging table into a single column as oppose to the have each and every column from staging table into exception table. Below is the example of the stored procedure. So just to make it more clear after the mandatory columns in the exception table I need a column which would have all the fields from AP_ITME table into a single column in error table. Please let me know if this is possible. create or replace procedure AP_ITEM_ADD_CHANGE(ITEM_IN IN AP_ITEM_ARRAY) AS begin FORALL indx in 1 .. ITEM_IN.COUNT insert /*+ APPEND */ into AP_ITEMS ("ITEM_ID","ITEM_NAME","SERIAL","ADDED_ON") values(ITEM_IN(indx).ITEM_ID,ITEM_IN(indx).ITEM_NAME,ITEM_IN(indx).SERIAL,ITEM_IN(indx).ADDED_ON) LOG ERRORS INTO err$_AP_ITEM REJECT LIMIT UNLIMITED; COMMIT; END; create table err$_AP_TIEM (ora_err_number$ number, ora_err_mesg$ varchar2(2000),ora_err_rowid$ rowid, ora_err_optyp$ varchar2(2), ora_err_tag$ varchar2(2000), <How to get the concatenate all the column from AP_ITEM table>);
Categories: DBA Blogs

deq_condition on dbms_aq.dequeue not helping with improving performance in Oracle AQ

Wed, 2021-10-06 12:46
Hi Tom, I have an AQ and the Queue table [QT_WRITE_OFF] has 40,196, 299 records. The task is to do dequeue about 28 million items[which meet a certain criterion] from this queue. Unfortunately, the queue was left to grow too big and I have to find a way of de-queueing. The dbms_aq.dequeue takes way too long to process. The deq_condition we've isn't helping much because the column isn't part of the index on the q_table, so the deq_condition still does a full table scan. May you please kindly advise on an approach/optimization which can improve performance of the dequeue? Can I use a non-payload column in the deq_condition? The script: ******************************************** <code>DECLARE v_dequeueoptions dbms_aq.dequeue_options_t; v_msgprop dbms_aq.message_properties_t; v_msgid raw (16); v_payload ttableid; CURSOR woff_trxns IS SELECT t.rowid, t.msgid, t.user_data.obj_id obj_id, from trxns tut, --normal partitioned table qt_write_off t --q_table where tut.trxn_type = 1 and t.user_data.obj_id =tut.obj_id and tut.gen_status = 'AFAIP'; TYPE c1data IS TABLE OF woff_trxns%ROWTYPE; c1rec c1data; handle_exception EXCEPTION; BEGIN OPEN woff_trxns; LOOP FETCH woff_trxns BULK COLLECT INTO c1rec LIMIT 1000; EXIT WHEN c1rec.COUNT = 0; FOR i IN 1..c1rec.count LOOP v_dequeueoptions.deq_condition := 'tab.user_data.obj_id = ' || c1rec(i).obj_id; v_dequeueoptions.wait := DBMS_AQ.no_wait; dbms_aq.dequeue ( queue_name => 'Q_WRITE_OFF', dequeue_options => v_dequeueoptions, message_properties => v_msgprop, payload => v_payload, msgid => v_msgid ); COMMIT; END LOOP; END LOOP; CLOSE woff_trxns; COMMIT; errPos := 5; EXCEPTION WHEN OTHERS THEN Error_Log.LogErr(v_descr, 'Update', errPos, Error_Log.ERR_LEVEL_ERROR, SQLERRM); END;</code> *************************************************** The queue was created as a normal queue. <code>begin sys.dbms_aqadm.create_queue( queue_name => 'Q_WRITE_OFF', queue_table => 'QT_WRITE_OFF', queue_type => sys.dbms_aqadm.normal_queue, max_retries => 5, retry_delay => 0, retention_time => 0); end; / -- Create table begin sys.dbms_aqadm.create_queue_table( queue_table => 'QT_WRITE_OFF', queue_payload_type => 'TTABLEID', sort_list => 'PRIORITY, ENQ_TIME', compatible => '10.0.0', primary_instance => 0, secondary_instance => 0, storage_clause => 'tablespace VPC_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited )'); end; / AQ$_QT_WRITE_OFF_I [q_name, local_order_no, state, priority, enq_time, step_no, chain] / CREATE OR REPLACE TYPE "TTABLEID" as object ( obj_id number(16) );</code> Thank you for your assistance. The LiveSQL Link isn't available at the moment.
Categories: DBA Blogs

Pages