Raw Oracle Tracing

Oracle

When Oracle performance becomes a mystery and you need to get under the hood of the car, falling upon raw oracle tracing is usuablly a good start. From SQLPLUS or your favorite Oracle Client tool, run through the following steps. What you end up doing is to dump a raw trace of what Oracle is doing. I find this to be very useful information in addition to using an Explain Plan.

alter session
set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';

SQL STATEMENT

alter session set events '10046 trace name context off';

From the raw trace file is generated in your oracle instance path to UDUMP. “C:\oracle\product\10.1.0\admin\[INSTANCE NAME]\udump” From there open up DOS PROMPT and run,

tkprof [dump file name]

Open command line and tkprof.

Comments

Returning a table from a PL/SQL function

Oracle, PLSQL

With collections, it is possible to return a table from a pl/sql function. First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object ( i number, n varchar2(30) );

Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;

Now, we’re ready to actually create the function:

create or replace function
return_table return t_nested_table as v_ret
t_nested_table;
begin
v_ret  := t_nested_table();
v_ret.extend;
v_ret(v_ret.count) := t_col(1, 'one');
v_ret.extend;
v_ret(v_ret.count) := t_col(2, 'two');
v_ret.extend;
v_ret(v_ret.count) := t_col(3, 'three');
return v_ret;
end;

Here’s how the function is used:

select * from table(return_table);

Comments

Dynamic search of all user tables, PL/SQL, Oracle

Oracle, PLSQL

This script returns the column and table names for a value input by a user. For example, if we know that there is a character value of ‘US-15017094′ with no other details available, this routine will search the user schema and list both table and column name for that value.

Declare searchtxt VARCHAR2(30):='US-15017094';
sqltxt VARCHAR2(255);
resultcount pls_integer:=0;
Begin
    for c in (select table_name, column_name from USER_tab_columns
        where table_name in ('ES_ALERT','AM_REGION','ES_ALERT_CACHE_PO')
    and data_type in ('CHAR','VARCHAR2'))
    loop
    resultcount:=0;
    sqltxt := 'select count(1) from '|| c.table_name||'  where '|| c.column_name||' ='||chr(39)||searchtxt||chr(39);
    execute immediate sqltxt into resultcount;
    IF resultcount=1 THEN
        dbms_output.put_line(c.table_name || '.' || c.column_name);
    END IF;
end loop;
End;

Example from Oracle Tech Network

Comments