-
can't shrink the tablespaces
I have one tablespace which contains 4 data files. The usage is about 0.2% but its size is 2G. I failed to shrink it. Here is the error message from oracle
ORA-03297: file contains used data beyond requested RESIZE value.
I wanted to know a save way to shrink this tablespace.
-
You need to look at a map of the tablespace (datafile in question). Just because it is only x% full, doesn't mean that the space is at the end of the tablespace. Say a datafile contained 100 Oracle blocks. Say 1 of the tables in this tablespace was located on the 99th block, but the other 99 blocks were available. You could only shrink it down 1 block.
Find out where the objects are in the tablespace. Ensure the freespace blocks are together (coalesce). If it's an index, then you can rebuild it. If it's a table, you may be able to deallocate the unused space of the table (up to highwater mark), or you may have to rebuild the table (COPY or EXPORT/IMPORT) to get the table(s) where you want them. ]
HTH
Gregg
-
If it is empty, offline it, drop it, remove the datafiles and (if neccassary) re-create it.
No?
MH
I remember when this place was cool.
-
thanks guys. It seems there is no easy way to set it free.
-
Originally posted by gbrabham
Say 1 of the tables in this tablespace was located on the 99th block, but the other 99 blocks were available. You could only shrink it down 1 block. HTH
Gregg
Can you help us how to find which table is sitting at the 99 th block.
Thanks
Giri
-
The system view DBA_EXTENTS has this information.
Code:
SQL> desc dba_extents
Name Null? Type
----------------------------------------- -------- -------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
Most of the columns should be pretty obvious -- block_id is the starting block for the extent, and blocks tells you how big it is.
You can find the segments that occupy the last space in the files on a tablespace with ...
Code:
Select
segment_name,
partition_name, -- exclude if you don't partition
segment_type,
block_id,
blocks
From
(
Select
segment_name,
partition_name, -- exclude if you don't partition
segment_type,
block_id,
blocks,
max(block_id)
over (partition by file_id) max_block_id
From
dba_extents
where
tablespace_name ='MY_TABLESPACE'
)
Where
block_id = max_block_id
For inconveniently placed tables, you might try "ALTER TABLE MY_TABLE MOVE", and for indexes try "ALTER INDEX MY_INDEX REBUILD" to get them to shiftsomewhereelse. For locally managed tablespaces with uniform extent sizes, this will move them to the beginning of the files -- for other TS types you might not be so lucky.
-
This info is very useful to us.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|