Table Reorganize - partition refuses to move

[cheesyhoward]

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.

[PAVB]

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?

[Mr.Hanky]

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?

[cheesyhoward]

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

[PAVB]

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?

[cheesyhoward]

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.