-
Tablespace half filled but no objects
Dear All,
Iam describing a problem as described below:
I have a tablespace that is 1.5gb in size and I find it half filled. But I dont find any objects in the tablespace. I have checked for the presence of tables,indexes etc in that table but not have found any. Also none of the users have it as their default tablespace. I need to move the contents of this tablespace to some other tablespace and then drop it. Could someone throw light on how to go ahead. Also could someone throw light on how to find out what the tablespace contains.
Regards,
K.Diwakar
-
It is half filled means definitely it consists of objects.If check all the users or the table in their schema it will tell which tables are using this tablespace.
If u want to move any users to this tablespace u can use Alter command.But be careful that other than Oracle 9i when u will move the objects to other tablespace u need to rebuild the indexes.
-
Check DBA_SEGMENTS view:
select owner, segment_name, sum(bytes), sum(blocks) from dba_Segments where tablespace_name = 'yourtabelspace' group by owner, segment_name;
This will give you all the segments and their owners sharing slice of the specific tablespace.
Agasimani
OCP(10g/9i/8i/8)
-
Dear Agasimani,
Thank you for your timely and usefull suggestion. I could locate two LOB indexes in those tablespace and could be located by querying dba_segments with your query. My mind was completely locked and to be frank, I queried all other things except dba_segments. I finally recreated those two tables containing lob indexes to the tablespace required.
Thanks again and also cheers to akhadar.
Regards,
K.Diwakar
-
Dear Diwakar,
You are most welcome.
Agasimani
OCP(10g/9i/8i/8)
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
|