Re: Tablespace, Diskspace or sql abuse..

Hi!

> If this is 8i or above, then the use of tempfile temporary tablespace can
> give rise to this error. When you create a tempfile datafile, Oracle only
> 'touches' the file, and doesn't actually map out and format the entire
> thing. If another file on the same disk grows into the space that the
> tempfile one day needs to properly format, then you get this sort of mess.
>
> Likely, you will have to drop and re-create the temporary tablespace to
fix
> the problem.

If this is a sparse file issue as you describe, then the file system should be (almost) full.
Otherwise OS should be able to handle expanding the file correctly.

ls -ls command in most unixes and linux should show you the *real* disk usage of a file,
it's helpful when dealing with sparse files such as temp files. Of course I would
either create a "permanent" file for temp tablespace, then reuse the file when creating
correct temp ts, to make the file to allocate space "correctly". Or as another solution, you just take the newly created sparse tempfile offline,
copy it to another name and rename it back to original name. OS copy copies sparse
blocks to real blocks.

2 cents,
Tanel. Received on Tue Jun 24 2003 - 12:45:05 CDT