[20240813]跟踪sqlplus登录执行了什么5(21c).txt
[20240813]跟踪sqlplus登录执行了什么5(21c).txt
--//跟踪看看sqlplus 21c版本访问数据库21c时，在执行用户调用命令前执行一些什么sql语句。
1.环境:
SYS@192.168.56.101:1521/book> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
--//在测试前看sqlplus命令帮助,发现使用-R 3,可以屏蔽一些glogin.sql,login.sql执行一些@的调用.如果害怕包含一些sql语句,
--//建议测试前改名.
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
--//在测试前可以执行多次,避免一些递归sql语句.
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p <<< quit
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
select sid from v$mystat where rownum<=1;
SID
----------
20
--//再次打开新的会话执行:
SYS@book> column user_name format a10
SYS@book> column CURSOR_TYPE format a32
SYS@book> select * from v$open_cursor where sid=20;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE CHILD_ADDRESS CON_ID
---------------- ---------- ---------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- -------------------------------- ---------------- ------
000000007BC64F58 20 SYSTEM 00000000660CD140 1282415807 9fmrst167075z select sid from v$mystat where rownum<=1 2024-08-13 14:27:29 OPEN 00000000660CCF10 3
000000007BC64F58 20 SYSTEM 0000000064474898 4087094668 g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000064473138 3
000000007BC64F58 20 SYSTEM 000000006448F758 1015030160 8btyg0wy808ch update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), 2024-08-13 14:27:23 OPEN-RECURSIVE 000000006448A468 3
000000007BC64F58 20 00000000663F5A10 3008674554 5dqz0hqtp9fru select /*+ connect_by_filtering index(sysauth$ i_sysauth1) * 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000066332DB0 3
000000007BC64F58 20 000000006B0AE550 3876120609 f0h5rpzmhju11 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000064480E48 3
000000007BC64F58 20 000000006B0A2930 2652695549 d9q2q82g1tuzx select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 2024-08-13 14:27:23 SESSION CURSOR CACHED 000000006447D2B8 3
000000007BC64F58 20 000000006449E348 3117107889 9mb61uqwwqkpj select spare6 from sys.user$ where user#=:1 2024-08-13 14:27:23 SESSION CURSOR CACHED 000000006449CBE8 3
000000007BC64F58 20 000000006449E348 3117107889 9mb61uqwwqkpj select spare6 from sys.user$ where user#=:1 2024-08-13 14:27:23 SESSION CURSOR CACHED 00000000644923E8 3
000000007BC64F58 20 SYSTEM 0000000064479958 3309933740 6u5zqzz2nm55c SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION', 2024-08-13 14:27:23 SESSION CURSOR CACHED 00000000644781F8 3
000000007BC64F58 20 00000000668C5E30 3873422482 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grante 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000066344330 3
10 rows selected.
--//从LAST_SQL_ACTIVE_TIM时间看,可以排除sql_id=9fmrst167075z 这条语句，是我手工执行的sql语句。
--//sql_id=9mb61uqwwqkpj出现2次.为什么?
--//实际上执行9条,其中9mb61uqwwqkpj执行2次,涉及8条sql语句. */
SYS@book> @ unshare 9mb61uqwwqkpj
SQL_ID NONSHARED_REASON COUNT(*)
------------- ----------------------------- ----------
9mb61uqwwqkpj HASH_MATCH_FAILED 1
--//应该是执行2次,产生了新的子光标.
--//实际执行这些sql语句,做了格式化处理.上面sql_text仅仅记录前60个字符.
SYS@book> @ sql_id g4y6nw3tts7cc
--SQL_ID = g4y6nw3tts7cc
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;;
SYS@book> @ sql_id 8btyg0wy808ch
--SQL_ID = 8btyg0wy808ch
UPDATE sys.user$ SET spare1=spare1-BITAND(spare1, 1048576), spare6 = DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) WHERE user# = :1;
SYS@book> @ sql_id 5dqz0hqtp9fru
--SQL_ID = 5dqz0hqtp9fru
SELECT /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#,
bitand(nvl(option$, 0), 72), grantee#, level
FROM sysauth$
CONNECT BY GRANTEE #=prior privilege#
AND privilege#>0
START WITH grantee#=:1
AND privilege#>0;
SYS@book> @ sql_id f0h5rpzmhju11
--SQL_ID = f0h5rpzmhju11
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST'),
SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE'),
STARTUP_TIME,
SYS_CONTEXT('USERENV', 'DB_DOMAIN'),
SYS_CONTEXT('USERENV', 'SERVICE_NAME')
FROM v$instance;
SYS@book> @ sql_id d9q2q82g1tuzx
--SQL_ID = d9q2q82g1tuzx
SELECT decode(upper(failover_method), NULL, 0, 'BASIC', 1, 'PRECONNECT', 2, 'PREPARSE', 4, 0),
decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO', 32, 1 ),
failover_retries,
failover_delay,
flags,
nvl(replay_initiation_timeout, 900),
decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore
FROM sys.service$
WHERE name = :1;
SYS@book> @ sql_id 9mb61uqwwqkpj
--SQL_ID = 9mb61uqwwqkpj
select spare6 from sys.user$ where user#=:1;
SYS@book> @ sql_id 6u5zqzz2nm55c
--SQL_ID = 6u5zqzz2nm55c
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL;
SYS@book> @ sql_id 0k8522rmdzg4k
--SQL_ID = 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0;
3.继续测试:
--//以上的执行看不出执行顺序.
SYS@book> alter system set events '10046 trace name context forever, level 12';
System altered.
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p <<<"SELECT value tracefile FROM v\$diag_info WHERE name = 'Default Trace File';"
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc
SYS@book> alter system set events '10046 trace name context off';
System altered.
$ . /home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
select spare6 from sys.user$ where user#=:1
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
select spare6 from sys.user$ where user#=:1
update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
select text from view$ where obj#=:1
select 1 from sensitive_fixed$ where name=:1 and flag=:2
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//共14条,最后一条不算.实际上13条.
$ grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc | awk '{print $NF}'
sqlid='0k8522rmdzg4k'
sqlid='5dqz0hqtp9fru'
sqlid='9mb61uqwwqkpj'
sqlid='945wb5h8yha2h'
sqlid='9mb61uqwwqkpj'
sqlid='8btyg0wy808ch'
sqlid='f0h5rpzmhju11'
sqlid='d9q2q82g1tuzx'
sqlid='6u5zqzz2nm55c'
sqlid='g4y6nw3tts7cc'
sqlid='8rfhqfbwpss1h'
sqlid='6vc4tzxw1h3b7'
sqlid='87gaftwrm2h68'
sqlid='67276f8xf68cy'
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc| awk '{print $NF}') \
<(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc)
sqlid='0k8522rmdzg4k' select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
sqlid='5dqz0hqtp9fru' select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
~~~~~~~~~~~~~~~~~~~
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
~~~~~~~~~~~~~~~~~~~~
sqlid='8btyg0wy808ch' update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
sqlid='6u5zqzz2nm55c' SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
sqlid='g4y6nw3tts7cc' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
--//注意看下划线,sql_id=9mb61uqwwqkpj 2次.
--//前面执行的共10条,其中ALTER SESSION没有记录在v$open_cursor视图里面.
--//后面执行sql语句没有出现在前面访问v$open_cursor视图里面?
sqlid='8rfhqfbwpss1h' select text from view$ where obj#=:1
sqlid='6vc4tzxw1h3b7' select 1 from sensitive_fixed$ where name=:1 and flag=:2
sqlid='87gaftwrm2h68' select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//注:我又重复1次,可以验证后面4条实际上是SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'的递
--//归sql语句.
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4749.trc| awk '{print $NF}') <(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4749.trc)
sqlid='0k8522rmdzg4k' select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
sqlid='5dqz0hqtp9fru' select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
sqlid='8btyg0wy808ch' update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
--//以上在oracle执行文件里面可以找到.
sqlid='6u5zqzz2nm55c' SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
sqlid='g4y6nw3tts7cc' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//最后2条语句的中间就没有 8rfhqfbwpss1h 6vc4tzxw1h3b7 87gaftwrm2h68
--//sql_id = 0k8522rmdzg4k 5dqz0hqtp9fru 9mb61uqwwqkpj 945wb5h8yha2h 9mb61uqwwqkpj 8btyg0wy808ch f0h5rpzmhju11 d9q2q82g1tuzx
--//sql_id = 6u5zqzz2nm55c g4y6nw3tts7cc
4.这些语句来自那里呢,实际上来自oracle执行文件里面,可以简单验证:
$ ll $(which oracle)
-rwsr-s--x. 1 oracle oinstall 498943224 2024-08-08 17:52:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle
$ du -sm $(which oracle)
476 /u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle
--//oracle的执行文件真是越来越大,竟然达到476M.
$ strings $(which oracle) > aa.txt
$ grep 'select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0' aa.txt
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
$ grep 'select /\*+ connect_by_filtering index(sysauth$ i_sysauth1) \*/ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0' a>
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 and privilege# in ( %s )
$ strings $(which oracle) | grep 'select spare6 from sys.user$ where user#=:1'
select spare6 from sys.user$ where user#=:1
$ grep 'update sys.user$ set spare1=spare1-BITAND' aa.txt
update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
$ grep "select SYS_CONTEXT" aa.txt | grep instance
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
$ grep "select decode(upper(failover_method)" aa.txt
select decode(upper(failover_method), NULL, %d , 'BASIC', %d, 'PRECONNECT', %d , 'PREPARSE', %d , %d), decode(upper(failover_type), NULL, %d, 'NONE', %d, 'SESSION', %d, 'SELECT', %d, 'TRANSACTION', %d, 'AUTO' , %d, %d ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, %d), decode(upper(session_state_consistency), 'STATIC', %d, %d), failover_restore from sys.service$ where name = :1
--//这条语句里面的%d在实际语句被替换真实的值.
--//我查询aa.txt文本可以找到 0k8522rmdzg4k 5dqz0hqtp9fru 9mb61uqwwqkpj 945wb5h8yha2h 9mb61uqwwqkpj 8btyg0wy808ch
--//f0h5rpzmhju11 d9q2q82g1tuzx,共9条.2条重复.
--//剩下的3条来自sqlplus的执行:
$ strings $(which oracle) | grep 'BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END'
--//这条没有找到,估计来自sqlplus.
$ ldd $(which sqlplus)
linux-vdso.so.1 => (0x00007ffc73fa2000)
libsqlplus.so => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so (0x00007f370735a000)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
libclntsh.so.21.1 => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libclntsh.so.21.1 (0x00007f3702faf000)
libclntshcore.so.21.1 => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libclntshcore.so.21.1 (0x00007f37029ff000)
libnnz21.so => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libnnz21.so (0x00007f3702251000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f3702024000)
libm.so.6 => /lib64/libm.so.6 (0x00007f3701d22000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3701b06000)
librt.so.1 => /lib64/librt.so.1 (0x00007f37018fd000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f37016fb000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f37014e1000)
libc.so.6 => /lib64/libc.so.6 (0x00007f370111f000)
/lib64/ld-linux-x86-64.so.2 (0x00007f370764f000)
$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so| grep "SELECT DECODE(USER, 'XS\$NULL', XS_SYS_CONTEXT('XS\$SESSION','USERNAME'), USER) FROM SYS.DUAL"
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
--//注:我这里外层使用双引号,如果外层使用单引号里面的单引号不知道如何转义.
$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so| grep 'BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
--//最后补充sys用户执行的情况,测试前可以执行多次,避免递归.sys用户执行涉及到sql语句要少许多.
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5947.trc| awk '{print $NF}') <(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5947.trc)
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
--//以上在oracle执行文件里面找到.
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//最后附上extractsql.sh的脚本:
$ cat /home/oracle/sqllaji/bin/extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
--//跟踪看看sqlplus 21c版本访问数据库21c时，在执行用户调用命令前执行一些什么sql语句。
1.环境:
SYS@192.168.56.101:1521/book> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
--//在测试前看sqlplus命令帮助,发现使用-R 3,可以屏蔽一些glogin.sql,login.sql执行一些@的调用.如果害怕包含一些sql语句,
--//建议测试前改名.
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
--//在测试前可以执行多次,避免一些递归sql语句.
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p <<< quit
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
select sid from v$mystat where rownum<=1;
SID
----------
20
--//再次打开新的会话执行:
SYS@book> column user_name format a10
SYS@book> column CURSOR_TYPE format a32
SYS@book> select * from v$open_cursor where sid=20;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE CHILD_ADDRESS CON_ID
---------------- ---------- ---------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- -------------------------------- ---------------- ------
000000007BC64F58 20 SYSTEM 00000000660CD140 1282415807 9fmrst167075z select sid from v$mystat where rownum<=1 2024-08-13 14:27:29 OPEN 00000000660CCF10 3
000000007BC64F58 20 SYSTEM 0000000064474898 4087094668 g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000064473138 3
000000007BC64F58 20 SYSTEM 000000006448F758 1015030160 8btyg0wy808ch update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), 2024-08-13 14:27:23 OPEN-RECURSIVE 000000006448A468 3
000000007BC64F58 20 00000000663F5A10 3008674554 5dqz0hqtp9fru select /*+ connect_by_filtering index(sysauth$ i_sysauth1) * 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000066332DB0 3
000000007BC64F58 20 000000006B0AE550 3876120609 f0h5rpzmhju11 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000064480E48 3
000000007BC64F58 20 000000006B0A2930 2652695549 d9q2q82g1tuzx select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 2024-08-13 14:27:23 SESSION CURSOR CACHED 000000006447D2B8 3
000000007BC64F58 20 000000006449E348 3117107889 9mb61uqwwqkpj select spare6 from sys.user$ where user#=:1 2024-08-13 14:27:23 SESSION CURSOR CACHED 000000006449CBE8 3
000000007BC64F58 20 000000006449E348 3117107889 9mb61uqwwqkpj select spare6 from sys.user$ where user#=:1 2024-08-13 14:27:23 SESSION CURSOR CACHED 00000000644923E8 3
000000007BC64F58 20 SYSTEM 0000000064479958 3309933740 6u5zqzz2nm55c SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION', 2024-08-13 14:27:23 SESSION CURSOR CACHED 00000000644781F8 3
000000007BC64F58 20 00000000668C5E30 3873422482 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grante 2024-08-13 14:27:23 SESSION CURSOR CACHED 0000000066344330 3
10 rows selected.
--//从LAST_SQL_ACTIVE_TIM时间看,可以排除sql_id=9fmrst167075z 这条语句，是我手工执行的sql语句。
--//sql_id=9mb61uqwwqkpj出现2次.为什么?
--//实际上执行9条,其中9mb61uqwwqkpj执行2次,涉及8条sql语句. */
SYS@book> @ unshare 9mb61uqwwqkpj
SQL_ID NONSHARED_REASON COUNT(*)
------------- ----------------------------- ----------
9mb61uqwwqkpj HASH_MATCH_FAILED 1
--//应该是执行2次,产生了新的子光标.
--//实际执行这些sql语句,做了格式化处理.上面sql_text仅仅记录前60个字符.
SYS@book> @ sql_id g4y6nw3tts7cc
--SQL_ID = g4y6nw3tts7cc
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;;
SYS@book> @ sql_id 8btyg0wy808ch
--SQL_ID = 8btyg0wy808ch
UPDATE sys.user$ SET spare1=spare1-BITAND(spare1, 1048576), spare6 = DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) WHERE user# = :1;
SYS@book> @ sql_id 5dqz0hqtp9fru
--SQL_ID = 5dqz0hqtp9fru
SELECT /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#,
bitand(nvl(option$, 0), 72), grantee#, level
FROM sysauth$
CONNECT BY GRANTEE #=prior privilege#
AND privilege#>0
START WITH grantee#=:1
AND privilege#>0;
SYS@book> @ sql_id f0h5rpzmhju11
--SQL_ID = f0h5rpzmhju11
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST'),
SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE'),
STARTUP_TIME,
SYS_CONTEXT('USERENV', 'DB_DOMAIN'),
SYS_CONTEXT('USERENV', 'SERVICE_NAME')
FROM v$instance;
SYS@book> @ sql_id d9q2q82g1tuzx
--SQL_ID = d9q2q82g1tuzx
SELECT decode(upper(failover_method), NULL, 0, 'BASIC', 1, 'PRECONNECT', 2, 'PREPARSE', 4, 0),
decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO', 32, 1 ),
failover_retries,
failover_delay,
flags,
nvl(replay_initiation_timeout, 900),
decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore
FROM sys.service$
WHERE name = :1;
SYS@book> @ sql_id 9mb61uqwwqkpj
--SQL_ID = 9mb61uqwwqkpj
select spare6 from sys.user$ where user#=:1;
SYS@book> @ sql_id 6u5zqzz2nm55c
--SQL_ID = 6u5zqzz2nm55c
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL;
SYS@book> @ sql_id 0k8522rmdzg4k
--SQL_ID = 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0;
3.继续测试:
--//以上的执行看不出执行顺序.
SYS@book> alter system set events '10046 trace name context forever, level 12';
System altered.
$ rlwrap sqlplus -s -l -R 3 system/bookbook@book01p <<<"SELECT value tracefile FROM v\$diag_info WHERE name = 'Default Trace File';"
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc
SYS@book> alter system set events '10046 trace name context off';
System altered.
$ . /home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
select spare6 from sys.user$ where user#=:1
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
select spare6 from sys.user$ where user#=:1
update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
select text from view$ where obj#=:1
select 1 from sensitive_fixed$ where name=:1 and flag=:2
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//共14条,最后一条不算.实际上13条.
$ grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc | awk '{print $NF}'
sqlid='0k8522rmdzg4k'
sqlid='5dqz0hqtp9fru'
sqlid='9mb61uqwwqkpj'
sqlid='945wb5h8yha2h'
sqlid='9mb61uqwwqkpj'
sqlid='8btyg0wy808ch'
sqlid='f0h5rpzmhju11'
sqlid='d9q2q82g1tuzx'
sqlid='6u5zqzz2nm55c'
sqlid='g4y6nw3tts7cc'
sqlid='8rfhqfbwpss1h'
sqlid='6vc4tzxw1h3b7'
sqlid='87gaftwrm2h68'
sqlid='67276f8xf68cy'
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc| awk '{print $NF}') \
<(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4426.trc)
sqlid='0k8522rmdzg4k' select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
sqlid='5dqz0hqtp9fru' select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
~~~~~~~~~~~~~~~~~~~
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
~~~~~~~~~~~~~~~~~~~~
sqlid='8btyg0wy808ch' update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
sqlid='6u5zqzz2nm55c' SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
sqlid='g4y6nw3tts7cc' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
--//注意看下划线,sql_id=9mb61uqwwqkpj 2次.
--//前面执行的共10条,其中ALTER SESSION没有记录在v$open_cursor视图里面.
--//后面执行sql语句没有出现在前面访问v$open_cursor视图里面?
sqlid='8rfhqfbwpss1h' select text from view$ where obj#=:1
sqlid='6vc4tzxw1h3b7' select 1 from sensitive_fixed$ where name=:1 and flag=:2
sqlid='87gaftwrm2h68' select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//注:我又重复1次,可以验证后面4条实际上是SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'的递
--//归sql语句.
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4749.trc| awk '{print $NF}') <(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4749.trc)
sqlid='0k8522rmdzg4k' select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
sqlid='5dqz0hqtp9fru' select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='9mb61uqwwqkpj' select spare6 from sys.user$ where user#=:1
sqlid='8btyg0wy808ch' update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
--//以上在oracle执行文件里面可以找到.
sqlid='6u5zqzz2nm55c' SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
sqlid='g4y6nw3tts7cc' BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//最后2条语句的中间就没有 8rfhqfbwpss1h 6vc4tzxw1h3b7 87gaftwrm2h68
--//sql_id = 0k8522rmdzg4k 5dqz0hqtp9fru 9mb61uqwwqkpj 945wb5h8yha2h 9mb61uqwwqkpj 8btyg0wy808ch f0h5rpzmhju11 d9q2q82g1tuzx
--//sql_id = 6u5zqzz2nm55c g4y6nw3tts7cc
4.这些语句来自那里呢,实际上来自oracle执行文件里面,可以简单验证:
$ ll $(which oracle)
-rwsr-s--x. 1 oracle oinstall 498943224 2024-08-08 17:52:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle
$ du -sm $(which oracle)
476 /u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle
--//oracle的执行文件真是越来越大,竟然达到476M.
$ strings $(which oracle) > aa.txt
$ grep 'select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0' aa.txt
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
$ grep 'select /\*+ connect_by_filtering index(sysauth$ i_sysauth1) \*/ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0' a>
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 and privilege# in ( %s )
$ strings $(which oracle) | grep 'select spare6 from sys.user$ where user#=:1'
select spare6 from sys.user$ where user#=:1
$ grep 'update sys.user$ set spare1=spare1-BITAND' aa.txt
update sys.user$ set spare1=spare1-BITAND(spare1, 1048576), spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
$ grep "select SYS_CONTEXT" aa.txt | grep instance
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
$ grep "select decode(upper(failover_method)" aa.txt
select decode(upper(failover_method), NULL, %d , 'BASIC', %d, 'PRECONNECT', %d , 'PREPARSE', %d , %d), decode(upper(failover_type), NULL, %d, 'NONE', %d, 'SESSION', %d, 'SELECT', %d, 'TRANSACTION', %d, 'AUTO' , %d, %d ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, %d), decode(upper(session_state_consistency), 'STATIC', %d, %d), failover_restore from sys.service$ where name = :1
--//这条语句里面的%d在实际语句被替换真实的值.
--//我查询aa.txt文本可以找到 0k8522rmdzg4k 5dqz0hqtp9fru 9mb61uqwwqkpj 945wb5h8yha2h 9mb61uqwwqkpj 8btyg0wy808ch
--//f0h5rpzmhju11 d9q2q82g1tuzx,共9条.2条重复.
--//剩下的3条来自sqlplus的执行:
$ strings $(which oracle) | grep 'BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END'
--//这条没有找到,估计来自sqlplus.
$ ldd $(which sqlplus)
linux-vdso.so.1 => (0x00007ffc73fa2000)
libsqlplus.so => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so (0x00007f370735a000)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
libclntsh.so.21.1 => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libclntsh.so.21.1 (0x00007f3702faf000)
libclntshcore.so.21.1 => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libclntshcore.so.21.1 (0x00007f37029ff000)
libnnz21.so => /u01/app/oracle/product/21.0.0/dbhome_1/lib/libnnz21.so (0x00007f3702251000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f3702024000)
libm.so.6 => /lib64/libm.so.6 (0x00007f3701d22000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3701b06000)
librt.so.1 => /lib64/librt.so.1 (0x00007f37018fd000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f37016fb000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f37014e1000)
libc.so.6 => /lib64/libc.so.6 (0x00007f370111f000)
/lib64/ld-linux-x86-64.so.2 (0x00007f370764f000)
$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so| grep "SELECT DECODE(USER, 'XS\$NULL', XS_SYS_CONTEXT('XS\$SESSION','USERNAME'), USER) FROM SYS.DUAL"
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
--//注:我这里外层使用双引号,如果外层使用单引号里面的单引号不知道如何转义.
$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libsqlplus.so| grep 'BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
--//最后补充sys用户执行的情况,测试前可以执行多次,避免递归.sys用户执行涉及到sql语句要少许多.
$ paste <(grep sqlid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5947.trc| awk '{print $NF}') <(/home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_5947.trc)
sqlid='945wb5h8yha2h' ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH24.MI.SSXFF TZH:TZM' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
sqlid='f0h5rpzmhju11' select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') from v$instance
sqlid='d9q2q82g1tuzx' select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2, 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 32, 1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1
--//以上在oracle执行文件里面找到.
sqlid='67276f8xf68cy' SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'
--//最后附上extractsql.sh的脚本:
$ cat /home/oracle/sqllaji/bin/extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
