This discussion is archived
11 Replies Latest reply: Mar 1, 2013 10:50 AM by jgarry RSS

How to defragment the tablespace which is size of 3Tb?

906790 Newbie
Currently Being Moderated
Dear Experts,

1. In my database i have one tablespace with total size of 3TB, recently we have purged the around 1.5TB data and its end up with fragmentation in tablespace EMP_DATA.could you please provide the possible method to reclaim space in that tablespace.

Oracle Version : 11gR2

Ex:

TABLESPACE_NAME Total Space Allocated GB
------------------------------ ------------------------
EMP_DATA 3019.1497

TABLESPACE_NAME UTILIZED SPACE GB
------------------------------ -----------------
EMP_DATA 1629.14972

TABLESPACE_NAME FREE SPACE GB
------------------------------ -------------
EMP_DATA 1412.29852

2. alter tablespace EMP_DATA coalesce; ==> Will reclaim space in it ?

Thanks
Bals
  • 1. Re: How to defragment the tablespace which is size of 3Tb?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Define "fragmented". What, exactly, is the problem that you are trying to solve? For most definitions of "fragmented" a locally managed tablespace effectively cannot be fragmented.

    Is your intention to reduce the size of the data files associated with the tablespace? Or is there some other problem that you are trying to solve?

    Justin
  • 2. Re: How to defragment the tablespace which is size of 3Tb?
    sb92075 Guru
    Currently Being Moderated
    903787 wrote:
    Dear Experts,

    1. In my database i have one tablespace with total size of 3TB, recently we have purged the around 1.5TB data and its end up with fragmentation in tablespace EMP_DATA.could you please provide the possible method to reclaim space in that tablespace.

    Oracle Version : 11gR2

    Ex:

    TABLESPACE_NAME Total Space Allocated GB
    ------------------------------ ------------------------
    EMP_DATA 3019.1497

    TABLESPACE_NAME UTILIZED SPACE GB
    ------------------------------ -----------------
    EMP_DATA 1629.14972

    TABLESPACE_NAME FREE SPACE GB
    ------------------------------ -------------
    EMP_DATA 1412.29852

    2. alter tablespace EMP_DATA coalesce; ==> Will reclaim space in it ?
    reclaim to where?

    disk space remains constant; except when a disk volume is added to or removed from the OS
  • 3. Re: How to defragment the tablespace which is size of 3Tb?
    906790 Newbie
    Currently Being Moderated
    Hi Justin,

    Yes, i like to reduce the size of datafiles related to tablespace EMP_DATA which have 1.5TB of empty space. I tried to resize the datafiles but its not allowed me to do. Please tell me what is the best way to do it.

    Note: we have only 200gb of free space in the server for database growth, so if we reclaim this 1.5Tb space it will be help full for data load.

    By Bals
  • 4. Re: How to defragment the tablespace which is size of 3Tb?
    sb92075 Guru
    Currently Being Moderated
    http://www.oracle.com/pls/db112/search?remark=quick_search&word=shrink&partno=
  • 5. Re: How to defragment the tablespace which is size of 3Tb?
    jgarry Guru
    Currently Being Moderated
    When you say "purged" do you mean rows were deleted from tables or tables were deleted? The common issue is deleting rows from tables, Oracle is very good about reusing space in tables and indices, it only becomes an issue if you aren't going to reuse that space any time soon (or some rare bugs or application misfeatures that use up too much space). Depending on how the free space is distributed in the tablespace, it may just be a matter of alter table (or partition) move, or perhaps moving among tablespaces, then you can reduce data file size and rebuild indices.

    You can find debates about fragmentation here and elsewhere, it's probably a word you want to avoid due to different interpretations of what it means.

    http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php is fairly decent, though unfortunately the nice pictures enterprise manager gives you take too long to generate when you start getting more than 100G or so. You can use scripts to tell you where the empty space is, and perhaps parse the output of those to figure out what, if anything, to move. I've used some Jonathan Lewis script for that, see http://jonathanlewis.wordpress.com/tablespace-hwm/

    You have to also watch out for reorganizing tables that are very compact, they could wind up expanding by honoring pctfree.
  • 6. Re: How to defragment the tablespace which is size of 3Tb?
    906790 Newbie
    Currently Being Moderated
    Hi All,

    My schema EMP is 3T.B in size, the default tablespace of EMP is EMP_DATA. Recently App Dev team purged old data from EMP schema and its around 1.5Tb size. But after completion of purge also i cant able to reduce the tablespace size by resizing datafiles.

    And i come to know the data's are fragments and i need to tablespace reorg. Please let me know possible way to reclaim space from EMP_DATA tablespace.


    Thanks
    Bals
  • 7. Re: How to defragment the tablespace which is size of 3Tb?
    Justin Cave Oracle ACE
    Currently Being Moderated
    if we reclaim this 1.5Tb space it will be help full for data load.
    That seems to imply that you are trying to do a lot of work moving data around so that you can resize the data files only to turn around and load a bunch of data into the same tablespace. If that is what you're suggesting, that seems like an awful lot of work for no benefit. You have 1.4 TB of free space in the tablespace (assuming whatever query you ran to generate the output you posted is correct)-- you can happily load a ton of data into the tablespace. Moving the existing data, resizing the data files, doing the load, and letting the data files grow in size again doesn't offer any benefits over simply loading the data now.

    Justin
  • 8. Re: How to defragment the tablespace which is size of 3Tb?
    906790 Newbie
    Currently Being Moderated
    Hi Justin,

    You are correct if i am doing a dataload in EMP schema(Using Emp_DATA) but there are other production schema which required space to load data . And i already mention in above post that server have only 200gb of space and need space for future dataload.

    Method i though to reclaim space is:

    Method 1: Exp/IMP of EMP schema & resize the tablepsaace before import.

    Method 2 : Alter table <table_name> move <New_tablespace>; & alter index <Ind_name> rebuild tablespace <New tablespace>;


    Doubt : alter tablespace emp_data coalesce; ==> will it reclaim space ?


    Thanks
    Bals
  • 9. Re: How to defragment the tablespace which is size of 3Tb?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Coalescing won't do anything useful, no. It doesn't move data segments around.

    Justin
  • 10. Re: How to defragment the tablespace which is size of 3Tb?
    sb92075 Guru
    Currently Being Moderated
    903787 wrote:
    Hi Justin,

    You are correct if i am doing a dataload in EMP schema(Using Emp_DATA) but there are other production schema which required space to load data . And i already mention in above post that server have only 200gb of space and need space for future dataload.
    having tablespace dedicated to single schema is silly & unproductive.
  • 11. Re: How to defragment the tablespace which is size of 3Tb?
    jgarry Guru
    Currently Being Moderated
    sb92075 wrote:
    903787 wrote:
    Hi Justin,

    You are correct if i am doing a dataload in EMP schema(Using Emp_DATA) but there are other production schema which required space to load data . And i already mention in above post that server have only 200gb of space and need space for future dataload.
    having tablespace dedicated to single schema is silly & unproductive.
    It might, or it might not. Application design, data volatility and recovery requirements might have some things to say about it.

Legend

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