Oracle SQL Tracing

Tracing SQL in Oracle Database 10g

Reference:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html


Introduction:
This extract briefs you about how to enable tracing at a session level in oracle 10g.

Getting Started:
As with prior Oracle database releases, trace files are output to the directory specified by the user_dump_dest parameter of the server's initialization file. You can dynamically change this parameter by using the alter system command

SQL> alter system set user_dump_dest="c:\kflosstrace";

You can also add your own marker to the trace file names so you can more easily find the generated files. To do so, set the tracefile_identifier initialization parameter before starting a trace:

SQL> alter session set tracefile_identifier ="kfloss_test";

Trace files generated by this command have the string value you set appended to the filenames.

Setting Up Tracing with DBMS_MONITOR:
To trace the SQL based on the session ID query the V$SESSION view

SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
------ ------- ------------
133 4152 SYS
137 2418 SYSMAN
139 53 KIMBERLY
. . .
170

128 rows selected.

With the session ID (SID) and serial number, you can use DBMS_MONITOR to enable tracing for just this session:

SQL> exec dbms_monitor.session_trace_enable(139);

The serial number defaults to the current serial number for the SID (unless otherwise specified), so if that's the session and serial number you want to trace, you need not look any further. Also, by default, WAITS are set to true and BINDS to false, so the syntax above is effectively the same as the following:

SQL> exec dbms_monitor.session_trace_enable
(session_id=>139, serial_num=>53, waits=>true, binds=>false);

Analyzing Trace Results:
You can then run TKPROF against the consolidated trace file to generate a report.

C:\...\udump> tkprof kfloss.trc
output=kfloss_trace_report SORT=(EXEELA, PRSELA, FCHELA)

If you don't disable tracing, every session that runs that service and module will be traced. Thus, when you're finished, be sure to disable tracing by using either Oracle Enterprise Manager or the DBMS_MONITOR package.

Loading LOB Columns

This section shows on how to insert data into BLOB, CLOB, BFILE, XMLTYPE (Non-Schema Based) columns.

--Create the Directory Object used to access the File
--that contains the Binary or character data (Eg: pdf, xml)
CREATE OR REPLACE DIRECTORY
testdir AS
'C:\';


--Create the test table to where we are going to import the data
CREATE TABLE testdata
(
numcol NUMBER,
datecol DATE,
tscol TIMESTAMP(6),
varcol VARCHAR2(4000 BYTE),
chrcol CHAR(2000 BYTE),
clobcol CLOB,
blobcol BLOB,
bfilecol BFILE,
xmlcol SYS.XMLTYPE
);



--PLSQL Script that imports the File contents into Database Columns
DECLARE
bfil_ctnt BFILE;
dest_blob BLOB;
dest_clob CLOB;
BEGIN
--Storing the refernce of the XML file into the BFILE variable
bfil_ctnt := BFILENAME ('TESTDIR', 'SalesQuoteSample.xml');
--Opening the File contents for access in the plsql
DBMS_LOB.OPEN (bfil_ctnt, DBMS_LOB.lob_readonly);
-- Inserting a row in testdata tale and creating an empty instance of BLOB,CLOB
--and loading the lob locator in the dest_blob and dest_clob variables
INSERT INTO testdata
(blobcol, clobcol,
xmlcol,
bfilecol
)
VALUES (EMPTY_BLOB (), EMPTY_CLOB (),
XMLTYPE (BFILENAME ('TESTDIR', 'SalesQuoteSample.xml'),
NLS_CHARSET_ID ('AL32UTF8')
),
bfil_ctnt
)
RETURNING blobcol, clobcol
INTO dest_blob, dest_clob;
--Loading the LOB segment with the locator stored in dest_clob, dest_blob
DBMS_LOB.OPEN (dest_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.OPEN (dest_clob, DBMS_LOB.lob_readwrite);
--Loading the contents of the file from Bfil_ctnt to the lob columns
DBMS_LOB.loadfromfile (dest_lob => dest_blob,
src_lob => bfil_ctnt,
amount => DBMS_LOB.getlength (bfil_ctnt)
);
DBMS_LOB.loadfromfile (dest_lob => dest_clob,
src_lob => bfil_ctnt,
amount => DBMS_LOB.getlength (bfil_ctnt)
);
--Closing the LOB opened before exiting from the procedure
DBMS_LOB.CLOSE (dest_blob);
DBMS_LOB.CLOSE (dest_clob);
DBMS_LOB.CLOSE (bfil_ctnt);
COMMIT;
END;
/