| 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 -> Cannot shrink (seeming) emtpty tablespace
Marcel,
If this is Oracle 7.3.4 or before, and the file has been resized bigger in the past, then resizing it down will fail due to a bug in Oracle. This is fixed in a patch set. I can't remember if the datafile in question has had to have been (cold copied) to clone a database after it was resized bigger, or not before the bug hits, but I have had it happen to me in the past.
If that didn't make sense, try this :
in database A resize datafile F bigger. - Everything is fine.
close database A.
Clone it to database B.
Open database B and resize F smaller - bug hits.
I'm not sure if you get the bug only on cloned databases or if you will get it on the original as well. (I have the memory span of a goldfish I'm afraid !)
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 !
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;
(A slightly amended version of one of Howard's scripts) will display
everything in that tablespace, and all the free space. Now that you have
coalesced it, the free space should be in big enough chunks.
Hope this helps.
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.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.comReceived on Fri Jun 14 2002 - 05:01:08 CDT
-------------------------------------
 |
 |