This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 6, 2012 8:26 AM by rp0428 Go to original post RSS
  • 15. Re: Purging table which is having 98 M rows
    748829 Newbie
    Currently Being Moderated
    hi,

    there are two tablespace attached to main table. one to hold data and another is for index. now when i drop that table, tablespace which is holding data is releases its space however tablespace related to index was not getting released fully, some space are showing occupied. example:

    Tablespace Name for Index: abc_98M_index
    Before Dropping table abc_98M usage is showing 78%
    After Dropping table abc_98M usage is showing 14%

    why its not showing 0% usage!!

    Edited by: saanp on Dec 5, 2012 10:23 PM
  • 16. Re: Purging table which is having 98 M rows
    rp0428 Guru
    Currently Being Moderated
    >
    there are two tablespace attached to main table. one to hold data and another is for index. now when i drop that table, tablespace which is holding data is releases its space however tablespace related to index was not getting released fully, some space are showing occupied. example:

    Tablespace Name for Index: abc_98M_index
    Before Dropping table abc_98M usage is showing 78%
    After Dropping table abc_98M usage is showing 14%

    why its not showing 0% usage!!
    >
    My bad - forget about the recycle bin. If the recycle bin is enable when you drop a table Oracle doesn't really drop it but moves it to the recycle bin by renaming it. So the indexes and constraints still exist and will still show the space being used even though the space will be reused if Oracle needs it.

    Check the space after purging the recycle bin.

    See the PURGE statement in the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9018.htm
    >
    PURGE Purpose
    Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points