Empty Tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Empty Tables

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    178
    Hi All,

    I recently joined a small company running Oracle 8.1.6 on NT.
    All tables are partitioned to 4 tablespaces. I am not sure whether these are hash or range partitions. The data in these tables are relatively small and the tablespaces are still using the first extent. The tablespaces are however 90% full but I think the space was taken up by the first extent right from the start. These empty spaces are taking a lot of space during backup and the CEO wants me to to something about it.
    1. Should I remove 3 of these 4 tablespaces from backup?
    2. How can I claim back these empty tablespaces?
    3. How can I find out the type of partition used for these tables.

    Please help!

    Ac

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    To find out if the partitions are hash or range :

    Select * From SYS.DBA_PART_TABLES

    (look at the PARITION_TYPE column)

    If 3 of the 4 tablespaces are empty, why don't you drop them from the database? The command is 'Drop Tablespace <tablespace_name>'. (then go and delete the corresponding datafiles at the OS level, because Oracle does not do this for you.)

    Then when you need the space you can create the tablespace again.

    This way you don't have to worry about if it is safe or not to exclude them from the backups. (By the way. you can just exclude them from backups, but it makes recovery a little trickier.)

    -John

  3. #3
    Join Date
    Nov 2000
    Posts
    178
    Thanks John,

    I know I can "DROP TABLESPACE INCLUDING CONTENTS" but we are going to run Oracle Parallel Server soon so I'm not sure if this is a wis edecision. The recovery issue is another thing and I've not done it before.

    Is it possible to claim back the space taken by the empty tables in these tablespaces? Please advice.

    Ac

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may be you can use alter table xxx deallocate unused?

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    If there are tables in the tablespace, then the tablespace is not empty even though the tables are. If you need your tablespaces to be smaller, you need to make your tables smaller. The 'deallocate unused' might help but I suspect it will not get you as much as you want.

    You could drop all of your empty tables, coalesce the tablespaces, and then build your empty tables again except with a small initial extent (and normal sized next extent). Then you should be able to resize your datafiles so they are not quite so big. When the time comes that you need the space then you can make your datafiles big again.

    -John
    John Dorlon
    www.ezsql.net
    john@ezsql.net

  6. #6
    Join Date
    Nov 2000
    Posts
    178
    Thanks John,

    You are right, I have tried the ALTER TABLE XXX DEALLOCATE UNUSED but it didn't solve the problem.

    I will try your suggestion and see how it goes but I think it will resolve the problem.

    -Ac

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    deallocate unused will deallocate unused space from second extent, if you only have one extent try

    ALTER TABLE XXX DEALLOCATE UNUSED KEEP 1K;


  8. #8
    Join Date
    Nov 2000
    Posts
    178
    Hi Pando,

    I've used the command "ALTER TABLESPACE ...." and it worked. My tablespaces are heavily fragmented at the moment.

    After I've done this, the backup is still taking the same amount of space as before. Please advice.

    Ac

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    which command did you issue...? alter tablespace...?
    if you have extents at end of datafile then I recogn the best way now would be export the schema/table and import

  10. #10
    Join Date
    Jan 2001
    Posts
    1
    Hi AC,
    Before removing your tablespace, be sure they are no more used by your clients. Set them "Offline" before....

    Happy new year .
    ;-))

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