1.设置sql trace
(1)实例级
初始化参数据SQL_TRACE=TRUE
(2)对话级
ALTER SESSION SET
SQL_TRACE=TRUE
DBMS_SESSION.SQL_TRACE(SQL_TRACE) ----很少用
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,SQL_TRACE);
示例：
SQL> conn / as sysdba
Connected.
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
144
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/ECOAL/test1.dbf
8 TEST1 20971520 2560 AVAILABLE
8 NO 0 0 0 20905984 2552
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/tempundotbs.dbf
6 TEMPUNDOTBS 104857600 12800 AVAILABLE
6 NO 0 0 0 104792064 12792
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/test_undo.dbf
5 TEST_UNDO 41943040 5120 AVAILABLE
5 NO 0 0 0 41877504 5112
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/users01.dbf
4 USERS 5242880 640 AVAILABLE
4 YES 3.4360E+10 4194302 160 5177344 632
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/sysaux01.dbf
3 SYSAUX 304087040 37120 AVAILABLE
3 YES 3.4360E+10 4194302 1280 304021504 37112
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/undotbs01.dbf
2 UNDOTBS1 377487360 46080 AVAILABLE
2 YES 3.4360E+10 4194302 640 377421824 46072
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/system01.dbf
1 SYSTEM 922746880 112640 AVAILABLE
1 YES 3.4360E+10 4194302 1280 922681344 112632
SYSTEM
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/ECOAL/test.dbf
7 TEST 20971520 2560 AVAILABLE
7 NO 0 0 0 20905984 2552
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/catalog.dbf
9 CATALOG_TBS 20971520 2560 AVAILABLE
9 NO 0 0 0 20905984 2552
ONLINE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/dat/DBData/oradata/NACEC/liusm.dbf
10 LIUSM 10485760 1280 AVAILABLE
10 NO 0 0 0 10420224 1272
ONLINE
10 rows selected.
SQL> alter session set sql_trace=false;
Session altered.
最后查看跟踪文件即可。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31448824/viewspace-2137548/ ，如需转载，请注明出处，否则将追究法律责任。
