It is currently Fri, 30 Sep 2022 10:07:06 GMT

Author Message
 Wrong size of Oracle file in 4.3.2

        Our DBA has reduced a datafile that had grown to fill a FS.  "ls -l"
still shows the file as 2.8 GB, even though the DBA's view of the file (using
sqlplus) shows that it's only 2.2 GB.  This is even after the database has been
brought down and the FS remounted.  "fsck" on the mounted FS doesn't report any
problems (I know it should be unmounted for fsck, but it wasn't done at the time,
and it can't be done now as the DB is in production)
        We have Oracle 7.3.4, and sqlplus 3.3.4 on AIX 4.3.2_02
        Anyone run into this before, or have any ideas on what to try?


 Fri, 08 Aug 2003 23:56:19 GMT   
 Wrong size of Oracle file in 4.3.2
Sorry about the wrong return address in the prvious post.  Please don't reply
directly to it, as it _will_ bounce.  It has been corrected for this post.

 Sat, 09 Aug 2003 00:01:55 GMT   
 Wrong size of Oracle file in 4.3.2

How did the DBA reduce the tablespace size? Is it possible that
the tablespace was only defragmented or tables were coalesced?

When the tablespace is initialized it grabs the space requested, never
to return that space to the OS (extents may be another matter.)

I think that the only way to return unused file space is to
export objects -> drop tablespace -> recreate smaller tablespace ->
import objects.

 Sat, 09 Aug 2003 07:49:49 GMT   
 Wrong size of Oracle file in 4.3.2
Hi, and thanks for the reply.

        I watched him use an "alter" command using sqlplus.  He upped the size of
a different datafile from 1.2GB to 1.4GB, then we looked at df output for the FS
it resides on ("used" went up).  Then he used the same "alter command to reduce
it back to 1.2GB, and df showed that "used" had gone back down.
        I thought that perhaps the datafile that we're having trouble with had a
"hole" in it, ie 2.1GB of contiguous data, then empty space (according to Oracle)
due perhaps to record deletions, then .1GB at the "end" of the file, but the DBA
said the failed resize was tried after the database was reorg'ed (which is
supposed to remove these typese of "holes").
        I will say that we (the DBA and I) seem to remember something like this
happening before (file size was changed) but the space didn't "appear" until
after the FS was remounted.  That's why we tried it this time.

This was also suggested in direct email, claiming an Oracle bug.  Thanks for
your input so far.

 Sun, 10 Aug 2003 00:01:20 GMT   
   [ 4 post ] 

Similar Threads

1. File size wrong, can't copy large file

2. Oracle for Linux: downloaded file size

3. max size for a file (UNIX-ORACLE)

4. file size added to HTTP requests, goes wrong

5. Wrong file system size info

6. Mount size wrong with Partition size.

7. change /oracle to alter /oracle in a file with :%s issues

8. Shrinking Veritas QIO files for Oracle backups or migrating QIO files to reg files

9. Solaris/Oracle Sizing Question

10. Raid5 & Oracle stripe size question, please help

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by ST Software