That's a very good idea. I will try it once my query that i kicked off in the morning
(at 10:40am) comes back. It is still spooling data. Currently, file size is approx.
1.8 gig.

ThankYou,
Radhika.


-----Original Message-----
From: Lloyd Ruskin <oracledba-ezmlmshield-x67944126.[Email address protected]
Sent: Jul 7, 2005 1:36 PM
To: LazyDBA Discussion <[Email address protected]
Subject: RE: Oracle sql query not returning.

Hi,

If on UNIX you make a pipe file with the mknod utility:

mknod spool.pipe p

Then you can set up a background job to compress the streaming data to
an actual file:

#cat spool.pipe | compress > outputfile.lst.Z &

With the ampersand (&) at the end, it sits as a background job waiting
for data.

Then run the SQL spooled to spool.pipe. This sends the output to
spool.pipe which is piped to compress which then outputs the data to the
compressed file. Then the physical file can receive more data before it
hits the 2 GB limit and the query might finish OK.

When the query finishes the background job detects an end of file and
finishes as well.

To view the file, without uncompressing it, use zcat. For example:

# zcat filename | grep 'ORA-' | more

This will check for errors:

RTM for UNIX online: Use man to check up on mknod, compress and zcat.

Hope this helps,
Lloyd


-----Original Message-----
From: Mulpuri family
[mailto:oracledba-ezmlmshield-x48896011.[Email address protected]
Sent: 07 July 2005 16:14
To: LazyDBA Discussion
Subject: RE: Oracle sql query not returning.

My problem ticket was just solved by the unix admins. Yes, that was
correct. There was a file size limit on the file system where my output
files are getting stored. I have just kicked off the script one more
time. I am crossing my fingers and we will see what happens.

Thanks all for your valuable contributions. Appreciate it.
Radhika.

-----Original Message-----
From: Riley David <oracledba-ezmlmshield-x20923549.[Email address
protected]
Sent: Jul 7, 2005 11:10 AM
To: LazyDBA Discussion <[Email address protected]
Subject: RE: Oracle sql query not returning.

There's no reason not to have unlimited for all. You may be hitting the
stack limit. If it's AIX you will need to set the stack_hard to -1.
Also, as mentioned below be sure you filesystem can handle a file larger
that 2GB.

David Riley
IKON The Way Business Gets Communicated
IKON Data Center
1738 Bass Rd.
Macon, GA 31210
Voice: 478-238-7560
Fax: (912)238-7676
Email; [Email address protected]



-----Original Message-----
From: Mulpuri family
[mailto:oracledba-ezmlmshield-x40491090.[Email address protected]
Sent: Thursday, July 07, 2005 10:11 AM
To: LazyDBA Discussion
Subject: Re: Oracle sql query not returning.


ulimit -a gave the following results:

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 1024
vmemory(kbytes) unlimited

looks like file size is unlimited. Then I don't understand why the
query is not returning.



Oracle LazyDBA home page