Oracle SQL statement tracking

Oracle SQL statement tracking

1. SQL statement tracking

The execution of SQL statements must be performed on the Oracle server. The Oracle server detects and records all SQL statements executed by the access process. The commands used below are all executed in command line mode. Therefore, we need to use command line to log on to the Oracle database as an administrator. The following describes how to use the tool.

First, we need to catch up with the SQL statement. We need to determine the session ID of the tracing client process. The following commands enumerate all the access process session IDs and serial ports on the current server, then select the session ID and serial port to be detected.

Select username, sid, serial # from v $ session where username is not null;

Then, you can trace the selected session ID and serial port SQL statements, as shown in the following command.

EXECUTE sys. dbms_system.set_ SQL _trace_in_session (10,264 2, TRUE );

After tracing SQL statements, we can perform operations on the client, and the SQL statements used in these operations will be recorded until we end SQL statement tracing.

After the client completes the operation, use the following command to end SQL statement tracing.

EXECUTE sys. dbms_system.set_ SQL _trace_in_session (10,264 2, FALSE );

After the synchronization ends, an latest file is generated in the Oracle server folder. for files with the trc suffix, we can use the Search Everything tool to find the most recently produced files. trc file, and then use the tkprof tool to parse the file.

2 TRC File Parsing

Parses the generated. in the trc file, you must use the tkprof tool. This tool has a lot of complex parameters for use. If you do not have complex requirements, you can use the following simple command. the trc file is output as a text file.

The parsing command is as follows:

Tkprof sqlplus_007.trc out.txt

Sqlplus_007.trc is the data Tracing file

Out.txt format the output file

Open the out.txt file to view the SQL statement execution status.