Here is a nice trick to work with files larger than 2GB on Unix/Linux using pipe. First case - TKPROF When trying to execute TKPROF on a trace file larger than 2 GB I got this error: [udump]$ ll test_ora_21769.trc -rw-r----- 1 oratest dba 2736108204 Jun 23 11:04 test_ora_21769.trc [udump]$ tkprof test_ora_21769.trc test_ora_21769.out TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:05:10 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. could not open trace file test_ora_21769.trc In order to successfully execute TKPROF on this trace file you can use the mkfifo command to create named pipe as follow: - Open a new unix/linux session (1st), change directory where the trace file exists and execute:
[udump]$ mkfifo mytracepipe
[udump]$ tkprof mytracepipe test_ora_21769.out
TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:07:35 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. - Open another session (2nd), change directory where the trace file exists and execute:
[udump]$ cat test_ora_21769.trc > mytracepipe This way you'll successfully get the output file. Second case - spool Similar issue with spool to file larger than 2GB can be treat similarly. $ mkfifo myspoolpipe.out --> Create new named pipe called 'myspoolpipe.out' $ dd if=myspoolpipe.out of=aviad.out & --> What you read from 'myspoolpipe.out' write to 'aviad.out' $ sqlplus user/pwd@dbname SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 24 12:05:37 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production SQL> spool myspoolpipe.out --> Spool to the pipe
SQL> select ..... SQL> spool off
SQL> 5225309+294082 records in
5367174+1 records out SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
[1]+ Done dd if=myspoolpipe.out of=aviad.out $ ls -ltr prw-r--r-- 1 oratest dba 0 Jun 24 12:22 myspoolpipe.out
-rw-r--r-- 1 oratest dba 2747993487 Jun 24 12:22 aviad.out Related Notes: Note 62427.1 - 2Gb or Not 2Gb - File limits in Oracle
Note 94486.1 - How to Create a SQL*Plus Spool File Larger Than 2 GB on UNIX Aviad | 
