ITPub博客

首页 > 数据库 > Oracle > [20210401]跟踪sqlplus登录执行了什么.txt

[20210401]跟踪sqlplus登录执行了什么.txt

原创 Oracle 作者:lfree 时间:2021-04-01 08:53:53 0 删除 编辑

[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'




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2766041/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2907
  • 访问量
    6680902