Re: Cannot shrink (seeming) emtpty tablespace

From: Marcel Kraupp <marcel.kraupp_at_gmx.ch>
Date: 19 Jun 2002 00:20:09 -0700
Message-ID: <332bb004.0206182320.2e97e6d8@posting.google.com>

Thanks for replying

However I am still unsure what's going on in my database:

I am running 8i (and am sorry for not stating this in the first place)

BTW:
select owner,object_type,object_name
from dba_objects
where tablespace_name = 'BOVWALLS001';

> Having dealt with all that, what is in your tablespace ?
>
> select owner,object_type,object_name
> from dba_objects
> where tablespace_name = 'BOVWALLS001';
>
> might give you something, where dba_extents does not, but if not, then
> you might be looking at a corrupted tablespace !

Doesn't work, there is no column tablespace_name in dba_objects.

> Where is it ?
>
> SELECT tablespace_name, file_id, owner, segment_name, block_id
> start_block, blocks num_blocks, block_id + blocks -1 end_block,
> bytes/1024 kb, '' free
> FROM dba_extents
> WHERE tablespace_name = 'BOVWALLS001'
> UNION
> SELECT tablespace_name, file_id, '' owner, '' segment_name, block_id
> start_block, blocks num_blocks, block_id + blocks -1 end_block,
> bytes/1024 kb, 'Free' free
> FROM dba_free_space
> WHERE tablespace_name = 'BOVWALLS001'
> ORDER BY 1,2,5 DESC;
This returns

TABLESPACE_ FILE_ID OWN SEGME START_BLOCK NUM_BLOCKS END_BLOCK KB FREE
----------- ---------- --- ----- ----------- ---------- ---------- ---------- ----

BOVWALLS001          4                  9477        100       9576    
   400 Free
BOVWALLS001          4                  9297        160       9456    
   640 Free
BOVWALLS001          4                  8937        340       9276    
  1360 Free
BOVWALLS001          4                  6857       2060       8916    
  8240 Free
BOVWALLS001          4                  2062       4775       6836    
 19100 Free
BOVWALLS001          4                  1982         60       2041    
   240 Free
BOVWALLS001          4                  1122        840       1961    
  3360 Free
BOVWALLS001          4                     2       1100       1101

4400 Free

No owner, no names.

> However, if the tablespace *is* thought to be empty, and the above
> returns no entries of note, why not 'drop tablespace BOVWALLS001
> including contents' and recreate with the reuse option to use the same
> datafile again - if you want it.

I tried, but no success, either:

system_at_imsmare.world>drop tablespace bovwalls001 including contents; drop tablespace bovwalls001 including contents *
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

Of course, the documentation says:

Cause: Failed to remove all objects when dropping a tablespace.

Action: Retry the drop tablespace until all objects are dropped.

Then, I tried to use dbverify:

[ 147 /opt/oracle/server/8.1.7.0.0p0/bin ] > dbv FILE=/fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf BLOCKSIZE=4096

DBVERIFY: Release 8.1.7.0.0 - Production on Wed Jun 19 09:16:39 2002

DBVERIFY - Verification starting : FILE = /fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf

DBVERIFY - Verification complete

Total Pages Examined         : 9616
Total Pages Processed (Data) : 4027
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 489
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 261
Total Pages Empty            : 4839
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0

[ 148 /opt/oracle/server/8.1.7.0.0p0/bin ] >

Look good?

And still, I can't drop the tablespace.

MK Received on Wed Jun 19 2002 - 02:20:09 CDT