Make trace files with large sql statements available to developers
- From: "Eric Buddelmeijer" <Eric.Buddelmeijer@xxxxxxxxxx>
- To: "'Oracle Mailing List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2005 20:45:12 +0100
Hi All,
I am working on a sql script to make trace files (10046 traces) available to
developers through sqlplus (mixed sun/win environment, oracle 9.2.0.5). I
have found several options of which the one from Jared Still
(http://www.dbazine.com/oracle/or-articles/still1) was the most elegant
(what's in a name) in my opinion.
However (there always is one) I have trace files that contain sql statements
that are more than 4000 characters in length (statements are generated by
Oracle Warehouse Builder). And external tables cannot be defined using LOB's
or LONG's.
One workaround is the approach from asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:6858521250456814988::NO::F4950_P
8_DISPLAYID,F4950_P8_CRITERIA:1112673450743 where the file is loaded into a
table and then selecting from that table. To solve the problem of statements
of more than 4000 characters I would have to cut up the long line into parts
before inserting and, after selecting, glue them together again. But I do
not find that very elegant :-). Does anybody have better ideas than I have
at the moment?
I stripped the external table approach of Jared for some testing. I have
tried to attach a relatively small trace file to this message, also for
testing. No luck uploading it.
create or replace directory udump
as '/opt/oracle/admin/NLD2/udump';
create table nld1_ora_13989 ( text varchar2(4000) )
organization external (
type oracle_loader
default directory UDUMP
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location('nld2_ora_13989.trc')
) reject limit unlimited
;
select *
from nld1_ora_13989;
drop directory udump;
drop table nld1_ora_13989;
Kind regards,
Eric Buddelmeijer
DBA
Elegant Application Services
P.J. Oudweg 15
1314 CH Almere
tel. +31 (36) 547 7105
gsm +31 (6) 5356 0396
e-mail Eric.Buddelmeijer@xxxxxxxxxx
web www.elegant.nl
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this e-mail in error, please delete the message and
notify us. This e-mail message has been swept by MIMEsweeper for the
presence of computer viruses
.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
