Solve the problem that normal users cannot execute SQL _trace to trace other sessions.

1. Set parameter files

Set three parametersTimed_statistics,User_dump_dest,Max_dump_file_size.

Timed_staticstices

Used to start or disable timed statistics (for exampleCupTime and time used), as well as the collection of multiple statistical information in the dynamic performance table

Alter session set timed_statistics = true;

Alter system set timed_statistics = true;

Max_dump_file_size

When the instance layer is enabledSQLTraceIn each requestServerWill generate a text line in the trace file, the maximum size of these files is limited by the initialization parameter settings. Default Value:500 (blocks). If the data in it is truncated, it will increaseSize. IfUnlimitedIt means there is no upper limit.

User_dump_dest

Set the storage location of the trace file. Default Value:Admin/User/Udump;

Alter system set user_dump_dest = 'newdir ';

2. Start SQL trace Utility

Start a sessionSQL trace

Alter session set SQL _trace = true;

Alter session set SQL _trace = false;

SYS. dbms_system.set_ SQL _trace_in_session =(Sid,Serial #,True );

SYS. dbms_system.set_ SQL _trace_in_session =(Sid,Serial #,False );

 Start a user instanceSQL trace

Alter system set SQL _trace = true;

Alter system set SQL _trace = false;

3. Use Tkprof Format Trace File

Usage

Tkprof tracefile outputfile [explain =] [Table =] [Print =] [insert =] [sys =] [sort =]

Example: 1. tkprof tracefile OUTFILE [explain = user/password] [Options...];

Example: 2. tkprof uat_ora_14936.trc trace.txt sort = (prsdsk, exedsk, fchdsk) print = 10 explain = apps/apps table = apps. temp_plan_table_a insert = storea. SQL sys = No

TkprofParameter Introduction

Table = schema. tablename use 'schema. tablename' with 'explain = 'option.

Before writing an execution plan to an output file,TkprofThe owner and name of the table used to temporarily store the execution plan, which must beExplainParameters are used together.

Explain = user/password connectOracleAnd issue explain Plan.

Print = integer list only the first 'integer' SQL statements.

Only list the first one in the output fileIntegerOfSQLStatement. If this parameter is ignoredTkprofTheSQLStatement

Aggregate = Yes | No

If you specifyAggregate = No,TkprofWill not be the sameSQLThe text is summarized by multiple users.

Insert = filename list SQL statements andDataInside insert statements.

SQLA script used to store the motivation information of the tracking fileDatabase.

Sys = No tkprof does not list SQL statements run as user SYS.

To enable or disable the userSysReleasedSQLStatement List to the output file, including RecursionSQL (ForSQLStatement,OracleYou must also execute some additional statements.)Statement. Default Value:Yes.

Record = filename record non-recursive statements found in the trace file.

Non-recursive trace filesSQLStatement,TkprofCreatesSQLScript. Used to replay the user time in the tracking file.

Waits = Yes | no record summary for any wait events found in the trace file.

RecordTraceSummary of all wait events in the file.

Sort = option set of zero or more of the following sort options:

InSQLBefore the Statement List is output to the trace file, sort it by the descending order of the specified sorting options;

If multiple sorting options are specified, they are sorted Based on the descending relationship between the values indicated by the sorting options;

If this parameter is ignoredTkprofThe statements are listed in the output file in the order of use. The specific options are described as follows:

Prscnt number of times parse was calledNumber of statement resolutions.

Prscpu CPU time ParsingTheCPUTime.

Prsela elapsed time ParsingThe time (always greater than or equalCPUTime );

Prsdsk Number of disk reads during parseNumber of physical reads from the disk during statement parsing.

Prsqry Number of buffers for consistent read during parseDuring statement parsing, consistent mode block reads (Consistent mode block read.

Prscu Number of buffers for current read during parseDuring statement parsing, the current mode reads (Current Mode block read.

Prsmis Number of misses in library Cache during parseNumber of database cache failures during statement Parsing

.

Execnt Number of execute was calledNumber of statement executions.

Execpu CPU time spent executingTheCPUTime.

Exeela elapsed time executingStatement execution time (always greater than or equalCPUTime ).

Exedsk Number of disk reads during ExecuteNumber of physical reads from the disk during statement execution.

Exeqry Number of buffers for consistent read during ExecuteDuring statement execution, consistent mode block read (Consistent mode block read.

Execu Number of buffers for current read during ExecuteDuring statement execution, the current mode reads (Current Mode block read.

Exerow number of rows processed during ExecuteNumber of statements processed during statement execution.

Exemis number of library cache misses during ExecuteNumber of database cache failures during statement execution.

Fchcnt number of times fetch was calledNumber of retrieved data.

Fchcpu CPU time spent fetchingTheCPUTime.

Fchela elapsed time fetchingThe time (always greater than or equalCPUTime ).

Fchdsk Number of disk reads during fetchThe number of physical reads from the disk during data retrieval.

Fchqry Number of buffers for consistent read during fetchDuring data retrieval, consistent mode blocks read (Consistent mode block read.

Fchcu Number of buffers for current read during fetchThe current mode reads (Current Mode block read.

Fchrow number of rows fetchedThe number of rows obtained.

4. Example :

TraceOthersSession

SQL> exec dbms_system.set_ SQL _trace_in_session (12, 73, true );

Waiting for trackingSessionActivity period

SQL> exec dbms_system.set_ SQL _trace_in_session (12, 73, false );

--Query the generated. TRCFile number

Select D. Value | '/' | Lower (rtrim (I. instance, CHR (0) | '_ ora _' |

P. spid | '. trc' trace_file_name

From (select P. spid

From v $ session S, V $ PROCESS p

Where S. Sid = & SID

And S. Serial # = & serial #

And P. ADDR = S. paddr) P,

(Select T. Instance

From v $ thread t, V $ parameter V

Where v. Name = 'thread'

And (V. value = 0 or T. Thread # = to_number (V. Value) I,

(Select value from V $ parameter where name = 'user _ dump_dest ') D;

--UseTkprofGenerate analysis File

OS> tkprof E: \ oracle \ admin \ hunter \ hunter_ora_4188.trc c: \ test. PRF aggregate = Yes sys = No sort = fchela (In this example, the most time-consumingSQLAt the beginning of the analysis File)

 

TraceBenSession

SQL> alter session set SQL _trace = true;

SQL> # SQL statements #

SQL> alter session set SQL _trace = false;

Query the generatedTraceFile Name

Select D. Value | '/' | Lower (rtrim (I. instance, CHR (0) | '_ ora _' |

P. spid | '. trc' trace_file_name

From (select P. spid

From v $ mystat M, V $ session S, V $ PROCESS p

Where M. Statistic # = 1

And S. Sid = M. Sid

And P. ADDR = S. paddr) P,

(Select T. Instance

From v $ thread t, V $ parameter V

Where v. Name = 'thread'

And (V. value = 0 or T. Thread # = to_number (V. Value) I,

(Select value from V $ parameter where name = 'user _ dump_dest ') D;

TkprofFormatting

OS> tkprof standdb_ora_770326.trc standdb_ora_770326.txt

 

If you want to completely open the access permission to the SYS. dbms_system package, you can directly grant its execution permission to public
Method:
Grant execute on dbms_system to public;

Run:

Exec dbms_system.set_ SQL _trace_in_session (532,2204, true );