| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Cannot shrink (seeming) emtpty tablespace
Marcel,
it looks horribly like you have database corruption and will need to
raise a TRA with Oracle.
However, see my comments embedded below ....
regards,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------
>> -----Original Message-----
>> From: marcel.kraupp_at_gmx.ch (Marcel Kraupp)
[mailto:marcel.kraupp_at_gmx.ch]
>> Posted At: Wednesday, June 19, 2002 8:20 AM
>> Posted To: server
>> Conversation: Cannot shrink (seeming) emtpty tablespace
>> Subject: Re: Cannot shrink (seeming) emtpty tablespace
>> 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';
>> Doesn't work, there is no column tablespace_name in dba_objects.
Whoops, sorry, my mistake. I should have put a script to look in dba_extents or dba_segments instead of the one I pasted.
>> > Where is it ?
>> >
>> <SNIP SCRIPT>
>> This returns
<Slightly snipped results>
>> TABLESPACE_ FILE_ID OWN SEGME START_BLOCK NUM_BLOCKS END_BLOCK
>> ----------- ---------- --- ----- ----------- ---------- ----------
>> BOVWALLS001 4 9477 100 9576
>> BOVWALLS001 4 9297 160 9456
>> BOVWALLS001 4 8937 340 9276
>> BOVWALLS001 4 6857 2060 8916
>> BOVWALLS001 4 2062 4775 6836
>> BOVWALLS001 4 1982 60 2041
>> BOVWALLS001 4 1122 840 1961
>> BOVWALLS001 4 2 1100 1101
>> No owner, no names.
True, however, it does show up that there are 'missing' chunks of space in your tablespace. take the first two lines of output. The first line shows a chunk of free space starting at block 9477 for 100 blocks. Howebver, the previous chuck of free space ended at block 9456, so whet is using the 20 blocks between these two free chunks ? You *must* have something in the tablespace using up those 20 blocks of space. Working through the above results shows that you have 20 blocks of 'something' between *each* free bit.
I suspect the tablespace is corrupt. The fact that the script should also have produced the names and owners of segments in the tablespace as well as the free space noted above points to a problem - because although there were no actual used segments listed, there *are* used segments in the tablespace.
<SNIP>
>> 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
I had a look on Metalink and found this reply to a similar problem (ora-1561 on drop tablespace) :
From: Oracle, Rowena Serna 15-Mar-02 04:30 Subject: Re : ora-01561 failed to remove all objects in the tablespace specified
This does not look good. You may have some form of mismatch in the data
dictionary, potential corruption.
Please log an iTAR as this is outside the scope of this forum.
Regards,
Rowena Serna
Oracle Corporation
>> Then, I tried to use dbverify:
<SNIP>
>> Look good?
I'm afraid I've never had to dbverify anything so I am unable to comment :o(
>> And still, I can't drop the tablespace.
It looks like a TAR is probably required at this point. I wouldn't want to go mucking around in UET$ and FET$ etc myself.
>> MK
Received on Wed Jun 19 2002 - 05:02:42 CDT
 |
 |