How to know your Session Trace file Name

-

How to know your Session’s Trace file Name

Oracle server has been provided with the power of Instrumented Code. With this power you can get diagnostic as well as performance related information out of the server as required. One of the ways to get that information is by the use of trace files.

Naming Convention

The trace file naming convention changes from time to time in Oracle, but if you have an example trace file name from your system, it is easy to see the template in use. For example, a trace file name looks like:

Sample Trace File Names

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 broken down as follows:
• The first part of the file name is the ORACLE_SID (with the exception of Oracle9i Release 1, where Oracle decided to leave that off).
• The next bit of the file name is just ora.
• The number in the trace file name is the process ID of your dedicated server (SPID), available to you from the V$PROCESS view.

Assuming you are using dedicated server mode) you need access to four views to determine your trace file name:
• V$PARAMETER, which is used to locate the trace file for user_dump_dest and to find the optional tracefile_identifier that might be used in your trace file name.
• V$PROCESS, which is used to find the process ID.
• V$SESSION, which is used to correctly identify your session’s information in the other views.
• V$INSTANCE, which is used to get the ORACLE_SID.

Often you simply “know” the ORACLE_SID and USER_DUMP_DEST, so technically you might only need access to V$SESSION and V$PROCESS, but for ease of use you’d want access to all four.

A query, then, to generate your trace file name could be:

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.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier'
/

TRACE
-------------------------------------------------------
…..\trace\mynewdb_ora_192.trc

And this just shows that if the file exists, you’ll be able to access it via that name (assuming you have the permissions to read the trace directory!).

Note:

You will not see the trace file with that name in the trace directory until you enable tracing in that session with one of the tracing method enabled. Till then no file will exist.
As soon as tracing is enabled, however, you will be able to see it.

You can check the contents of the trace file to make sure that you are looking at correct trace file. Like the file will contain the statements/queries for which you had enabled the tracing or Oracle instance name.

It should be obvious that on Windows you’d replace the / with \. If you are using 9i Release 1, instead of adding the instance name into the trace file name, you’d simply issue the following:
select c.value || 'ora_' || a.spid || '.trc'

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more