A trace operation writes specific SQL statements to the operating system file, as well as the corresponding information (query plans and event waits) that is executed while the script is running. You can trace any arbitrary session in the Oracle database.
Instructions
Step 1
Before starting tracing, you need to enable statistics collection, otherwise files with zero times will appear. To do this, you need to execute the query: alter system set timed_statistics = true If you need to start tracing in the current session, then the system parameter should be replaced with session.
Step 2
Verify that the maximum dump file size attribute is set to sufficient value. To do this, execute the corresponding SQL query: SELECT value FROM v $ param p WHERE name = 'max_dump_file_size' The value of $ param can be set both at the database level (alter system) and at the session level (alter session).
Step 3
Then identify the session that needs to be traced. To do this, find out the primary column values: SELECT sid, serial # from v $ system WHERE selection_criteria for_tracing
Step 4
To start tracing, you must set event 1046 in the corresponding session. Run the procedure sys.dbms_system.set_ev, and then pass the obtained sid and serial values as integer parameters: BEGIN sys.dbms_system.set_ev (sid, serial #, 10046, 8, ‘’); END
Step 5
To turn off tracing, change the value of the event level 10046 from 8 to 0.
Step 6
The trace file appears in the Oracle database dump directory (Oracle / admin / databaseSID / udump). The name of this file contains the identifier of the OS process in which the operation was performed, and the extension is.trc. In order to process the information in a readable form, process the trace file in the tkprof utility: cd C: ORACLEadmindatabaseSIDudump
tkprof file.trc output = my_file.prf The processed file will list all the commands that were executed during the session.
