[20210401]跟蹤sqlplus登入執行了什麼.txt

[20210401]跟蹤sqlplus登入執行了什麼.txt

--//昨天做了測試，連結http://blog.itpub.net/267265/viewspace-2765851/，順便記錄一下登入過程執行了什麼。

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

alter system set events 'sql_trace off';
alter system set events 'sql_trace [sql:cm5vu20fhtnq1|3nkd3g3ju5ph1|459f3z9u4fb3u|0ws7ahf1d78qa|5ur69atw3vfhj|0k8522rmdzg4k] bind=true, wait=true';
alter system set events '10046 trace name context forever, level 12';
--//使用sqlplus登入。
alter system set events 'sql_trace off';

2.測試結果如下：
$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51309.trc
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
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= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
select value$ from props$ where name = 'GLOBAL_DB_NAME'
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT',  4, 1), failover_retries, failover_delay, flags from service$ where name = :1
--//前面6條執行的sqlplus來之oracle執行檔案內部，獲取連線使用者的許可權。
SELECT USER FROM DUAL
BEGIN DBMS_OUTPUT.DISABLE; END;
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND   ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPER(ATTRIBUTE) = 'ROLES')
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
SELECT DECODE('A','A','1','2') FROM DUAL

3.繼續測試toad的情況是否一樣。

$ extractsql.sh book_ora_51333.trc
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
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= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
select value$ from props$ where name = 'GLOBAL_DB_NAME'
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT',  4, 1), failover_retries, failover_delay, flags from service$ where name = :1
--//前面6條執行的sqlplus來之oracle執行檔案內部，獲取連線使用者的許可權。
declare  cursor NlsParamsCursor is    SELECT * FROM nls_session_parameters;begin  SELECT Nvl(Lengthb(Chr(16777216)), Nvl(Lengthb(Chr(65536)), Nvl(Lengthb(Chr(256)), 1))), Nvl(Lengthb(Chr(1)), 1)    INTO :MaxCharLength, :MinCharLength FROM dual;  for NlsRecord in NlsParamsCursor loop    if NlsRecord.parameter = 'NLS_DATE_LANGUAGE' then      :NlsDateLanguage := NlsRecord.value;    elsif NlsRecord.parameter = 'NLS_DATE_FORMAT' then      :NlsDateFormat := NlsRecord.value;    elsif NlsRecord.parameter = 'NLS_NUMERIC_CHARACTERS' then      :NlsNumericCharacters := NlsRecord.value;    elsif NlsRecord.parameter = 'NLS_TIMESTAMP_FORMAT' then      :NlsTimeStampFormat := NlsRecord.value;    elsif NlsRecord.parameter = 'NLS_TIMESTAMP_TZ_FORMAT' then      :NlsTimeStampTZFormat := NlsRecord.value;    end if;  end loop;end;
SELECT NVL(LENGTHB(CHR(16777216)), NVL(LENGTHB(CHR(65536)), NVL(LENGTHB(CHR(256)), 1))), NVL(LENGTHB(CHR(1)), 1) FROM DUAL
SELECT * FROM NLS_SESSION_PARAMETERS
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
begin sys.dbms_application_info.set_module('TOAD background query session', null); end;
select text from view$ where rowid=:1
select object_name nam, Decode(object_type, 'TABLE', 1, 'VIEW', 2, 3) typ
from   sys.user_objects
where  object_type in ('TABLE','PROCEDURE', 'PACKAGE', 'FUNCTION')

--//正如連線http://blog.itpub.net/4227/viewspace-708276測試awr報表那樣，如果一個應該不停的登入退出，沒有什麼負載的話，估
--//計看到的awr報表就是那樣。

4.收尾：
SYS@book> alter system set events '10046 trace name context off';
System altered.

SYS@book> alter system set events 'sql_trace off';
System altered.

5.附上extractsql.sh指令碼：
$ cat ~/bin/extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'