trace 파일은 메모리, 블럭 등에서 쓰이는 자세한 정보들을 볼 수 있다.
iniSID.ora 내에 '_trace_filespublic = true;
UNIX> conn /as sysdba
show parameter spfile
show parameter pfile
show parameter user_dump_dest
위에 show 를 검색하여 tace 파일이 떨어지는 경로를 확인 할수 있다.
/DBA2/dba06/admin/dba06/udump
--alter session set events '10046 trace name context forever, level 12'; //로즈 정보 까지 볼수있다.
alter session set tracefile_identifier = 'kyn';
//trace 파일에 kyn 이름을 붙이겠다
트레이스 다른파일과 분별하기 힘드므로 이름 붙인다
alter session set sql_trace = true;
alter session set timed_statistics = true;
//trace를 남기겠다.
분석할 셀렉트 문을 날리면 tace 파일이 생성된다
alter session set sql_trace = false;
alter session set timed_statistics = false;
//trace 남기는 부분 끝
dba06@sun04-zone:/DBA2/dba06/admin/dba06/udump> tkprof ./dba06_ora_15448_kyn.trc ./a.txt sys=no explain=ecampus/ecampus
TKPROF: Release 9.2.0.1.0 - Production on Mon Jun 25 14:44:37 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
dba06@sun04-zone:/DBA2/dba06/admin/dba06/udump> ls
a.txt dba06_ora_15448_kyn.trc
실습 결과
dba06@sun04-zone:/DBA2/dba06/admin/dba06> sqlplus "/as sysdba";
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jun 25 14:33:59 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> connn ecampus/ecampus
SP2-0734: unknown command beginning "connn ecam..." - rest of line ignored.
SQL> conn ecampus/ecampus
Connected.
SQL> alter session set tracefile_identifier = 'kyn';
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> alter session set timed_statistics = true;
Session altered.
SQL> SQL> select ename, job, sal, dname
from emp, dept
where emp.deptno = dept.depno
and emp.empno > 7800
and exists (select * from salgrade where emp.sal between losal and hisal);
2 3 4 5 where emp.deptno = dept.depno
*
ERROR at line 3:
ORA-00904: "DEPT"."DEPNO": invalid identifier
SQL> select ename, job, sal, dname
from emp, dept
where emp.deptno = dept.deptno
and emp.empno > 7800
and exists (select * from salgrade where emp.sal between losal and hisal);
2 3 4 5
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
KING PRESIDENT 5000 ACCOUNTING
TURNER SALESMAN 1500 SALES
ADAMS CLERK 1100 RESEARCH
JAMES CLERK 950 SALES
FORD ANALYST 3000 RESEARCH
MILLER CLERK 1300 ACCOUNTING
6 rows selected.
SQL> SQL>
SQL> alter session set sql_trace = false;
Session altered.
SQL> alter session set timed_statistics = false;
Session altered.
SQL> !ls /DBA2/dba06/admin/dba06/udump
dba06_ora_15448_kyn.trc
댓글