Trick to work with files larger than 2GB on Unix/Linux using pipe

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:


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
[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

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