Tablespace half filled but no objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tablespace half filled but no objects

  1. #1
    Join Date
    May 2002
    Posts
    193

    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

  2. #2
    Join Date
    May 2001
    Posts
    736
    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.

  3. #3
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    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)

  4. #4
    Join Date
    May 2002
    Posts
    193
    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

  5. #5
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304

    Smile

    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
  •  


Click Here to Expand Forum to Full Width