Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to find the SQL statement of uncommitted transactions in ORACLE, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Can we find SQL statements or other relevant information about uncommitted transactions (uncommit transactin) in the Oracle database? On this question, let's first take a look at the experimental test. Experience is the best teacher.

First, we construct an uncommitted transaction in session 1 (SID=63), as follows:

SQL > create table test 2 as 3 select * from dba_objects; Table created.SQL > select userenv ('sid') from dual; USERENV (' SID')-63 SQL > delete from test where object_id=12; 1 row deleted. SQL >

Then in session 2 (SID=70), we use the following SQL to query for uncommitted SQL statements. As follows:

SQL > select userenv ('sid') from dual; USERENV (' SID')-70 SQL > SQL > SET SERVEROUTPUT ON SIZE 99999-WHERE > EXECUTE PRINT_TABLE ('SELECT SQL_TEXT FROM V$SQL SMagneVRANSACTION T WHERE S.LASTIX active timekeeping T.STARTRODATE') SQL_TEXT: delete from test where object_id=12-SQL_TEXT: selectgrantee#,privilege#,nvl (col#,0), max (mod (nvl (option$,0), 2) from objauth$ whereobj#=:1 group by grantee#,privilege#,nvl (col#) 0) order by grantee#-SQL_TEXT: SELECT / * OPT_DYN_SAMP * / + ALL_ROWSIGNORE_WHERE_CLAUSE NO_PARALLEL (SAMPLESUB) opt_param ('parallel_execution_enabled',' false') NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0), NVL (SUM (C2) 0) FROM (SELECT / * + IGNORE_WHERE_CLAUSE NO_PARALLEL ("TEST") FULL ("TEST") NO_PARALLEL_INDEX ("TEST") * / 1 AS C1, CASE WHEN "TEST". "OBJECT_ID" = 12 THEN 1 ELSE 0 END AS C2 FROM "TEST" SAMPLE BLOCK (6.134372, 1) SEED (1) "TEST" SAMPLESUB-SQL_TEXT: select col#, grantee#,privilege#,max (mod (nvl (option$,0)) 2) from objauth$ where obj#=:1 and col# isnot null group by privilege#, col#, grantee# order by col#, grantee#-SQL_TEXT: selecttype#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL (lists,65535), NVL (groups,65535), cachehint,hwmincr,NVL (spare1,0), NVL (scanhint,0), NVL (bitmapranges) 0) from seg$ where ts#=:1 andfile#=:2 and block#=:3-PL/SQL procedure successfully completed.

As shown above, we will find a lot of irrelevant SQL statements in this SQL, and then we can use the following SQL query (shown in SQL Developer instead, because SQL*Plus is not convenient to show). As shown below, this SQL will not find irrelevant SQL. But can this SQL be qualified for any scenario? The answer is no.

SELECT S.SID, S.SERIAL#, S.USERNAME, S.OSUSER, S.PROGRAM, S.EVENT, TO_CHAR (S.LOGON time HH24:MI:SS'), TO_CHAR (T.STARTER date), S.LAST_CALL_ET, S.BLOCKING_SESSION, S.STATUS (SELECT Q.SQL_TEXT FROM V$SQL Q WHERE Q.LAST_ACTIVE_TIME=T.START_DATE AND ROWNUM col sql_text for A80 SQL > select sql_text 2, sql_id 3, version_count 4, executions 5, address 6, hash_value 7 from v$sqlarea where sql_text 8 like 'delete from test%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE -delete from test where object_id=12 5xaqyzz8p863u 11 0000000097FAE648 3511949434 SQL > exec sys.dbms_shared_pool.purge ('0000000097FAE648311949434' 'C') PL/SQL procedure successfully completed. SQL >

At this point, the SQL statement we query is an unrelated SQL or its value is Null.

Next, we roll back the SQL statement, and then proceed to the new experimental test, as shown below, where two DML operation statements are executed in session 1 (SID=63), neither of which committed the transaction.

SQL > delete from test where object_id=12; 1 row deleted. SQL > update test set object_name='kkk' where object_id=14; 1 row updated. SQL >

Next, we use SQL statements to find uncommitted SQL and find that only the initial DELETE statements are captured, not the UPDATE statements that are executed later. This experiment also confirms from the side that we may not be able to accurately find out the SQL statements of uncommitted transactions.

So combined with the above experiments, we can basically come to the conclusion that we may not be able to accurately find the SQL statements of uncommitted transactions, depending on the situation or scenario. There is this uncertainty.

Thank you for reading this article carefully. I hope the article "how to find SQL sentences for uncommitted transactions in ORACLE" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.