Table Reorganize - partition refuses to move
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Table Reorganize - partition refuses to move

  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Table Reorganize - partition refuses to move

    Hello

    I am trying to manually reorganise a tablespace by using the following sql commands.

    SQL> Alter table myTable move;
    SQL> Alter table myTable move partition myPartition;
    SQL> Alter index myIndex rebuild;
    SQL> Alter index myIndex rebuild partition myPartition;
    SQL> Alter table myTable move lob(lobColumn) store as (tablespace sameTableSpace);

    However some objects are refusing to move nearer the start of the tablespace. For example see the highlighted table partition in the tablespace map below. When moving this it stays in the exact same location. Even if I move it to another tablespace and back to the original it still ends up in the same place. I am refreshing the window and checking view DBA_EXTENTS.

    SQL> Alter table myTable move partition myPartition tablespace b;
    SQL> Alter table myTable move partition myPartition tablespace a;

    So my question is how I force the table partition to be moved as close to the start of the tablespace as possible?

    I am using Oracle 9.2.0.7 with locally managed extents set to automatic allocation. Segment space management is disabled.

    Any help is much appreciated.
    Attached Images Attached Images

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    The question here is why you want the object to move close to the beginning of the tablespace?

    Remember Oracle does not store objects on tablespaces, tablespaces have no physical existence, tablespaces are a conceptualization -nothing but a collection of physical datafiles. Actually you have as many physical "beginnings" as datafiles you have in your tablespace.

    If your goal is to reorg a tablespace you have to look at how fragmented your tablespace is and also at the size and number of free sections in it; no matter where objects are sitting.

    Could you please answer question below for us?

    1- How fragmented was your target tablespace before reorg?
    2- How fragmented it is after reorg?
    Last edited by PAVB; 10-23-2008 at 04:38 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    I've never seen that chart (picture) before but reorging according to a GUI makes me skeptical.

    Why are you reorging and what is the goal?
    I remember when this place was cool.

  4. #4
    Join Date
    Jan 2004
    Posts
    17
    The DB previously was about 15GB larger. After deleting quite a lot of data I want to resize my data files so that I free up the space back to the OS. I am not planning to load much (if any) new data in. I have 2 tablespaces 1 for data and 1 for indexes each has 1 datafile.

    With regard to the fragmentation. Yes before I started the reorganize it was pretty fragmented (see attached). The data file was 22GB in size then. It is looking better now (attachment from 1st post) and I have managed to get it down to 14GB. Most of the free space is contiguous except for the very slim lines which are data. I am using single extent sizes 65536 for all tables with pctfree = 10 pctused =40. I have already run coalesce.

    So I want to fill in the big space near the start of the tablespace with the data from the end. Then I should be able to resize down to around 5GB for this data file reclaiming 9GB.

    In answer to Mr Hanky the screenshots are from the tablespace map feature in OEM. It is just a visual representation of the DBA_EXTENTS views for tablespace. Plus it makes it a lot easier for me to describe the problem
    Attached Images Attached Images

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by cheesyhoward
    I am using single extent sizes 65536 for all tables with pctfree = 10 pctused =40.
    It looks like you are expecting very little updating, is that correct?

    On the other hand, how many datafiles do you have in each tablespace?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jan 2004
    Posts
    17
    I have found a couple of tables that have lots of chained rows. So this points to an incorrect pctfree setting. However future updates to these tables are unlikely as the DB will not have as many updates performed in the future.

    There is 1 data file per tablespace. 1 tablespace for data, another for the indexes, 1 for undo, 1 for temp.

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