Re: Cannot shrink (seeming) emtpty tablespace

Try to create an empty table. Allocate to it extents so that the tablespace is really used up. Then drop the table. Maybe you clean out that corruption that way.

Martin

Marcel Kraupp wrote:
>
> 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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> 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 Mon Jul 01 2002 - 06:42:03 CDT