Shulou(Shulou.com)06/02 Report--

Trace files are usually generated as a result of trace enabled through DBMS_MONITOR (in Oracle Database 9i Relese 2) and earlier, or by using an extended trace tool through the 10046 event, as follows

These trace files contain diagnostic and performance-related information. They are of great significance for understanding the internal work of database applications. In a working database, you will often see these trace files, and far more than other types of trace files.

1 File location

Whether you use DBMS_MONITOR, SQL_TRACE, or an extended trace tool, Oracle generates a trace file in the following two locations on the database server host.

If you use a dedicated server connection, a trace file is generated in the directory specified by the user_dump_dest parameter.

If you use a shared server connection, a trace file is generated in the directory specified by the background_dump_dest parameter.

Note: the background_dump_dest and user_dump_dest parameters will be deprecated in the 11g version, and although you can still use these two parameters for queries, you can use new parameters or views for queries.

Use the show parameter dump_dest command to view the catalog, or you can query the V$PARAMETER view directly, or query the new V$DIAG_INFO view.

V$DIAG_INFO is a new view of Oracle Database 11g, which was not available in earlier versions. It is an easier interface to access the trace information used by the new ADR tools.

Oracle Database 11g adjusts the default storage location of many files to make them better organized, making it easier to record service requests to Oracle. The most important lines include the following two:

Diag Trace: this is the location of the trace files (including background and user dump targets) in Oracle Database 11g.

Default Trace File: this is the trace file name for the current session. In earlier versions, this file name might have been hard to get. In Oracle Database 11g, you can return the fully qualified filename of the file by simply querying V$DIAG_INFO.

2 naming convention

The naming convention for trace files in Oracle is always changing, as shown in the following example:

Trace file name

Database version

Ora_10583.trc

9i Release 1

Ora9ir2_ora_1905.trc

9i Release 2

Ora10gr2_ora_6793.trc

10g Release 2

Ora11gr2_ora_1990.trc

11g Release 2

The trace file name can be divided into the following sections.

The first part of the file name is ORACLE_SID (except 9i Release 1)

The next part of the file name has only one ora.

The number in the trace file name is the process ID of the dedicated server, which can be obtained from the V$PROCESS view.

Oracle Database 11g makes it easy to use V$DIAG_INFO views, and prior to this release, actually (assuming a dedicated server mode) required access to four views.

V$PARAMETER: locate the trace file location specified by USER_DUMP_DEST and find the optional tracefile_identifier that may be used in the trace file name.

V$PROCESS: find the process ID.

V$SESSION: correctly identify session information in other views.

V$INSTALCE: get ORACLE_SID.

Use the following query to generate trace file names:

SELECT C.VALUE | |'/'| | D.INSTANCE_NAME | |'_ ora_' | | A.SPID | | CASE

WHEN E.VALUE IS NOT NULL THEN

'_' | | E.VALUE

END | | '.trc' TRACE

FROM V$PROCESS A, V$SESSION B, V$PARAMETER C, V$INSTANCE D, V$PARAMETER E

WHERE A.ADDR = B.PADDR

AND B.SID = USERENV ('sid')

AND C.NAME = 'user_dump_dest'

AND E.NAME = 'tracefile_identifier'

If the file exists, it can be accessed by name. Trace files can appear only if tracing is enabled. Replace / with\ on the Windows platform.

3 tag the trace file

There is a way to "tag" trace files so that they can be found even if you do not have access to V$PROCESS and V$SESSION. Assuming you can read the user_dump_dest directory, you can use the session parameter tracefile_identifier. Using this method, you can add a string that can be uniquely identified to the trace file name:

As you can see, the trace file is still named in the standard _ ora_ format, but there is a unique string assigned to it so that the trace file name can be easily found.

Refer to "Oracle 9i 10g 11g programming Art in depth Database Architecture"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.