Oracle SQL statement Tracking

Oracle SQL statement Tracking

1 SQL statement Tracking

The execution of the trace SQL statement takes place on the Oracle server side, and the Oracle server detects and records all SQL statements executed by the access process. The commands used below are executed in command-line mode, so we need to use the command line to log in to the Oracle database as an administrator. Here are the detailed steps that the tool uses.

To catch the SQL statement first, we need to determine the session ID of the tracing client process, the following command enumerates the current server all Access process session ID and serial port, and then selects the session ID and serial port that need to be detected.

 from where  is null;

Then follow the SQL statement for the selected session ID and serial port, as shown in the following command.

EXECUTE sys.dbms_system.set_sql_trace_in_session (ten,  2642, TRUE);

After we start tracing the SQL statements, we can operate on the client, and the SQL statements used by these operations will be recorded until we end the SQL statement tracking.

After the final client operation is complete, the following command can be used to end the SQL statement trace.

EXECUTE sys.dbms_system.set_sql_trace_in_session (ten,  2642, FALSE);

After the end of the Oracle server folder generates an up-to-date. TRC suffix file, we can use the search everything tool to find the. trc file that was produced in the most recent time, and then use the Tkprof tool to parse the file.

2 TRC File parsing

Parsing the generated. trc file requires the use of the Tkprof tool, which has a lot of complex parameters to use, and if you do not make complex demands, you can use the following simple command to output the. trc file as a text file.

The parse command is as follows:

out. txt

SQLPLUS_007.TRC for data tracking files

OUT.txt for output format file

Open the OUT.txt file to view the execution of the SQL statement.

Oracle SQL statement Tracking