dba topics: 2020

How to Find out / Identify Trace File for the Current or Other Oracle User Session

In this post I am going to help you to identify the trace file name for the current oracle session.

Method 1. Using v$diag_info table


SQL> set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ; 

NAME       VALUE
-------------------- ------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041.trc


Method 2. Using user_dump_dest



SQL> select param.value || '/' || instance_name || '_ora_' || pro.spid || '.trc'
from   v$parameter param, v$process pro, v$session sess , v$instance
where  param.name = 'user_dump_dest'
and    sess.username = SYS_CONTEXT('USERENV','SESSION_USER')
and    sess.sid = SYS_CONTEXT('USERENV','SID')
and    pro.addr=sess.paddr ; 

PARAM.VALUE||'/'||INSTANCE_NAME||'_ORA_'||PRO.SPID||'.TRC'
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/cdb_ora_3041.trc


Method 3. You can set the trace file name using tracefile_identifier

parameter

More details :- TRACEFILE_IDENTIFIER Parameter



SQL> alter session set tracefile_identifier='MySess';

Session altered.

SQL> set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ;
SQL> SQL> SQL>   2    3  
NAME       VALUE
-------------------- ----------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc


SQL>  set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ;

NAME       VALUE
-------------------- ----------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc

The physical file get created when the sql_trace starts. 

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
ls: cannot access /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc: No such file or directory

SQL> alter session set sql_trace=true; 

Session altered.

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
ls: cannot access /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc: No such file or directory

SQL> 
SQL> select name from v$database; 

NAME
--------------------
CDB

SQL> select 1 from dual; 

  1
----------
  1

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
-rw-r-----. 1 oracle oinstall 2927 Jun 29 06:27 /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc

Trace Current Oracle Session – Different Methods

Method 1. Using alter session command

ALTER SESSION SET sql_trace=TRUE;

ALTER SESSION SET sql_trace=FALSE;

SQL Query to Find Blocking Sessions and SQL Text for Last One Day from History

set pages 1000 lines 222

col sql_id for a17

col inst_id for '99'

col sql_text for a60

col module format a10

SQL Query to Find SQLIDs Spend on CPU/Wait/IO

This query is used to find out 
the SQLIDs spend on the CPU, 
Wait and IO.

SQL:-

set pages 1000 lines 220

col SQL_ID for a 15

SQL Query to Find Most Active Session in Oracle Database for Last One Hour

This SQLs to identify most active sessions in the oracle database for last one hour. Two queries mentioned here for the background and foreground processes.

SQL1. Foreground

SET PAGES 10000 LINES 222

SQL Query to Find I/O Intensive SQL and SQLText in Last One Hour

This can be used for finding out most CPU intensive query in last one hour. There are 3 data dictionary views involved in this query.

gv$active_session_history

gv$event_name

SQL Query to Find Out Oracle Session Details for a Past Time Period from the History tables

There are 3 views can be used to achieve this goal. The columns are almost similar to the V$SESSION table except snap_id, sample_id, sample_time etc.. The AWR, ASH reports are getting populated from these DBA_HIST_ACTIVE_SESS_HISTORY views.

SQL Query to Find Out Pending Distributed Transaction in Oracle Database

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.

OCI-How to Generate SSH Keys for OCI instance connectivity

To connect OCI Unix instance using Putty from windows server or Unix server it requires public and private keys to establish the connection. To generate keys we can use Putty Keygen tool. Below are the steps to create the keys.

How to Setup Free Oracle Cloud Infrastructure (OCI) Account

Step 1

Go to the free account web link.

https://www.oracle.com/cloud/free/#free-cloud-trial

SQL Query to Get Oracle Database Server CPU, CPU CORE, CPU Sockets and Physical Memory

Some Definitions
CPU

– Number of Processing Units in the server which is a main component of a server.

CPU Core
– Brain of the CPU. Each core performs operations separately from others. Multiple cores works to together to perform a parallel operation.

RMAN / SQL query to Get Oracle Database Incarnation Details

What is Database Incarnation?

Database incarnation is effectively a new “version” of the database. A new version or Incarnation happens when you reset the online redo logs using “alter database open resetlogs;”.

SQL Query to Get Oracle DB TIMEZONE details

In this post I am adding some useful sql query to find out the oracle database TIMEZONE details.

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

How to Check, Disable and Enable Recyclebin in Oracle Database

Check whether recyclebin is enabled or not using below commands.

SQL> show parameter recyclebin

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string on

Step 1

Archive