Oracle DBA Place

Since in Oracle RDS environment, we don't have a direct access to the operating system I have recently been asked to provide a workaround from the database. A procedure that: 1. Write to the alertlog 2. Generate a trace file (no matter its content). 3. Let the user know how to query a. The alertlog b. The trace file Oracle RDS implementation enable us this functionality without a need to create objects or have a special grants. SET SERVEROUTPUT ON VERIFY OFF FEED OFF LINES 300 PAGES 200 DECLARE     full_file_name   VARCHAR2 (512);     file_name        VARCHAR2 (512);     MESSAGE          VARCHAR2 (1024);     ALERT_TABLE      VARCHAR2 (30);     rds_exists       NUMBER;     TABLE_MISSING    EXCEPTION;     PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);     FUNCTION FIND_VIEW (object_name ...

ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions. Sometimes these errors are unique for your specific problem and cannot be found via search engines. Using "ORA-600 lookup tool" may point your specific problem, faster and accurate. The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1 The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1

ADRCI & alert file In Oracle version 11, no need to look for the location of alert log file, just use: adrci exec="set home orcl ;show alert -tail -f" detailed practical usage can be found here

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |...

Using the same technique I mentioned at " Tracing Commands " here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction. CREATE OR REPLACE TRIGGER SYS. LOGON_TRACE_CRYSTAL_TRG AFTER LOGON ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET max_dump_file_size = unlimited'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET tracefile_identifier = ''10046'''; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET statistics_level = ALL'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12'''; EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN --NULL; RAISE; END ; / optional: CREATE OR REPLACE TRIGGER  SYS. LOGOFF_TRACE_CRYSTAL_TRG BEFORE LOGOFF ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET EVENTS ''10046...

The trcsess utility consolidates trace output from selected trace files based on several criteria: trcsess &nbsp[output=output_file_name]         &nbsp[session=session_id]         &nbsp[clientid=client_id]         &nbsp[service=service_name]         &nbsp[action=action_name]         &nbsp[module=module_name]         &nbsp[trace_files] trcsess output=main.trc service=psdwh *trc After the consolidate trace file had been generated you can execute tkprof on it. For example: tkprof main.trc main.prf sys=no sort=exeela TKPROF: Release 10.2.0.3.0 - Production on Thu Feb 28 13:38:23 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. for more information: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm

==================================== Trace Specific ORA-XXXXX - Current Session ==================================== Activate: SQL> alter session set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter session set events ' 3113 trace name context off'   ==================================== Trace Specific ORA-XXXXX – for all system ==================================== Activate: SQL> alter system set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter system set events ' 3113 trace name context off'   Or at init.ora   event=" 3113 trace name ERRORSTACK level 3"   You'll need to bounce the database.   ============================= 10046 Trace - Current Session ============================= Open new session is SQL*Plus ALTER SESSION SET max_dump_file_size = unlimited; ALTER SESSION SET tracefile_identifier = '10046'; ALTER SESSION SET statistics_level...

I discovered that oracle has a utility to analyze EVENT 10046 trace files. It seems to arrange it in HTML and collect some more relevant information from the database. To create the trace file see tracing commands . The TRCANLZR can be found in Metalink Note: 224270.1 Enjoy it :)

SELECT p1.value'/'p2.value'_ora_'p.spid'.trc' filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID');