Tablespace size releasing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tablespace size releasing

  1. #1
    Join Date
    May 2002
    Posts
    163

    Tablespace size releasing

    Hey Guys,

    I have a production table which is a cluster and residing in one tablespace size of 57GB as on today. I run the process to delete records from that table, which is fine but oracle is not releasing space from the tablespce. Can you give me the possible causes of not releasing the space from the tablespace even deleting records from tables.

    For example: 1 reason is re verify the PCTUSED value.

    Any other cause, which will help me to learn and implement?


    Thanks and Regards
    Nwcomer
    Student

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Deleting records wil *never* release any space from the tablespace, i.e. used space and free space in the tablespace will remain unchanged.

    Oracle is releasing the space within the table you are deleting from, that is it is making room for new records or updates of the existing records *in the table you are deleting from*, but physicaly that table remains unchanged, it is not shrinking during the delete.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2002
    Posts
    163
    Thanks you very much.

    This table is growing rapidly and that's how it acts. In every 3 months or 6 months I would like to release some space in my database
    for that tablespace. But I "CAN NOT TRUNCATE" that table.

    I will appreciate if you please suggest me the best solution for that or any other work around.

    Regards
    Nwcomer
    Student

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    copy to another temporary table with records you want, truncate the original table and insert back the good data

  5. #5
    Join Date
    May 2002
    Posts
    163
    Pando,

    I also thought about that. But truncating a table of size 53 GB of 100,00,00,000 will be a real pain. And also restoring back too.

    What do u think?

    One more clarification from you. Which one do you think will be faster.

    1. Copy to another temp table.
    2. Rename original table and restore it to original.
    3. Using sqlplus "Copy as" command.

    Regards
    Nwcomer
    Student

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if that table has 1000 million of rows the proper way to deal with is range partition it using the criteria you use to delete your rows, then instead of deleting you drop partitions

    what I do before 9iR2 is

    1. insert append to a temp table with data I need
    2. disbale the constraints
    3. truncate the table
    4. set indexes to unusable
    5. change table to nologging and run ALTER SESSION SET SKIP_UNUSABLE_INDEXES = true
    6. insert the data back to the original table
    7. change table to logging
    8. drop the temp table
    9. enable novalidate PK/UK
    10. enable novalidate FKs
    11. rebuild the indexes nologging parallel
    12. change indexes back to logging and noparallel

  7. #7
    Join Date
    Dec 2001
    Posts
    203
    Pando,

    Thanks a lot again. But I have few queries and info for you.

    if that table has 1000 million of rows the proper way to deal with is range partition it using the criteria you use to delete your rows, then instead of deleting you drop partitions
    (Table is not partitioned)

    what I do before 9iR2 is

    1. insert append to a temp table with data I need
    (Which Method you would follow)
    ================================
    2. disbale the constraints
    3. truncate the table
    4. set indexes to unusable
    5. change table to nologging and run ALTER SESSION SET SKIP_UNUSABLE_INDEXES = true
    6. insert the data back to the original table
    7. change table to logging
    8. drop the temp table
    9. enable novalidate PK/UK
    10. enable novalidate FKs
    11. rebuild the indexes nologging parallel
    12. change indexes back to logging and noparallel
    sumit

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    huh what are you saying, you just cut and paste my post....?

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