IOTable, Partitioned. Is it possible to MOVE only the OVERFLOW segments to another tablespace?

I wonder if I overlook something.
I would like to only move the overflow segment of a index-organized partitioned table.
actually, I had them in a tablespace with plenty of other objects, which have been dropped/moved, and now those segments are in the middle of the (huge) data files.
I would like to move them to the beginning of the files, so I could shrink the data files.

the ALTER TABLE ... MOVE statement allows to move BOTH the table/partition and the overflow, but I don't need to move the table/partition itself. just the overflow.

what would be the syntax, if any?


the version is 11.2.0.2 (and will be patched to 11.2.0.4 soon)
LVL 143
Guy Hengel [angelIII / a3]Billing EngineerAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
It doesn't seem like it should need to do that; but if it does, it does.

We just get to use the syntax; we don't get to decide how that syntax behaves behind the scenes.  

I'm going to guess that in moving the overflow the index leafs are undergoing block splits which is why they need more space.  That's the only index action that I can think of that would make it grow like that.  Again it doesn't seem like an action that should be needed but I'm sure it serves some purpose internally.
0
 
sdstuberCommented:
yes, you can..

alter table YOURTABLE_NAME move partition PARTITION_NAME_TO_BE_MOVED  overflow tablespace NEW_TABLESPACE_NAME;

example...  Moving one overflow partition from tablespace USERS to tablespace DATA


SQL> CREATE TABLE angeliii_test
  2  (
  3      word      VARCHAR2(20 BYTE),
  4      junk_data VARCHAR2(4000 BYTE),
  5      CONSTRAINT pk_angeliii_test PRIMARY KEY(word) ENABLE VALIDATE
  6  )
  7  ORGANIZATION INDEX
  8  PCTTHRESHOLD 50
  9  OVERFLOW
 10  PARTITION BY HASH (word)
 11      PARTITIONS 4;

Table created.

SQL> INSERT INTO angeliii_test
  2      SELECT *
  3        FROM wordlist
  4       WHERE LENGTH(word) <= 20;

213809 rows created.

SQL> SELECT segment_name,partition_name,segment_type,tablespace_name
  2    FROM user_segments
  3   WHERE segment_name IN ('PK_ANGELIII_TEST',
  4                          (SELECT table_name
  5                             FROM user_tables
  6                            WHERE iot_name = 'ANGELIII_TEST'));

SEGMENT_NAME                                                                      PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------------
SYS_IOT_OVER_207005                                                               SYS_P193               TABLE PARTITION    USERS
SYS_IOT_OVER_207005                                                               SYS_P194               TABLE PARTITION    USERS
SYS_IOT_OVER_207005                                                               SYS_P195               TABLE PARTITION    USERS
SYS_IOT_OVER_207005                                                               SYS_P196               TABLE PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P195               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P196               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P193               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P194               INDEX PARTITION    USERS

8 rows selected.

SQL> alter table ANGELIII_TEST move partition SYS_P193  overflow tablespace DATA;

Table altered.

SQL> SELECT segment_name,partition_name,segment_type,tablespace_name
  2    FROM user_segments
  3   WHERE segment_name IN ('PK_ANGELIII_TEST',
  4                          (SELECT table_name
  5                             FROM user_tables
  6                            WHERE iot_name = 'ANGELIII_TEST'));

SEGMENT_NAME                                                                      PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------------
SYS_IOT_OVER_207005                                                               SYS_P193               TABLE PARTITION    DATA
SYS_IOT_OVER_207005                                                               SYS_P194               TABLE PARTITION    USERS
SYS_IOT_OVER_207005                                                               SYS_P195               TABLE PARTITION    USERS
SYS_IOT_OVER_207005                                                               SYS_P196               TABLE PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P195               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P196               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P193               INDEX PARTITION    USERS
PK_ANGELIII_TEST                                                                  SYS_P194               INDEX PARTITION    USERS

8 rows selected.

SQL>

Open in new window

0
 
sdstuberCommented:
I ran the above on 11.2.0.1
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
yes, but this also tries to move the partition itself...
even if you don't specify a new tablespace or the same one, Oracle still tries to move the partition also.
which I don't want to do, obviously.
0
 
sdstuberCommented:
I don't understand the request then.

The partitions are the segments, so, if you move the segment, you are moving the partition.

If that's not what you intended, what is the final result you're looking for?
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
each partition has the segments for the data AND a segment for the overflow.
and I want to move ONLY the overflow segment.
the data segments are well located.
0
 
sdstuberCommented:
hmmm,  so you're getting different results than I did with my test case?

In my example the only segment that moved was the overflow.  
     One overflow segment moved from USERS to DATA.
     Partition  SYS_P193   of the overflow did move.

The key data (which is in the index because it's an IOT) didn't move.
    The other segments all stayed in USERS.
    Partition  SYS_P193  of the index did not move.


Is that not what you get?
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
I agree that the key data does not actually MOVE, but it does read the entire partition / segment data, which is not needed at all.

in other words: I found the statement should do the job in a couple of seconds, because the SYS_IOT_OVER_xxxx segments are "empty". just move those.

instead, it takes hours, as it does read the full partition table segments  (PK_ANGELIII_TEST)...
0
 
sdstuberCommented:
I don't think I can do anything about the time it takes, only provide the syntax to accomplish the move.

However, it does make sense that both the index and the overflow would be read.
The index segment must be read and each row/leaf must be updated to point to the new location of the overflow data.

If the overflow is empty though, it does seem odd that it would take a long time since there isn't anything to actually update.  Then again, it is an IOT, so all access is driven through the index first.  In this case, maybe it doesn't make sense to do that, but my guess is the operation doesn't know the overflow is empty until after it has gone through the index to point to the overflow and then find nothing.

Another thing to consider: is there any other activity on the system that might have locks on one or more rows thus making the move wait?
0
 
sdstuberCommented:
One more thing, if the only reason you want to move the segments is so they won't be in the middle of an otherwise empty tablespace,  could you just do a shrink space compact  or coalesce?
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
thanks for the replies so far.

the coalesce did not help:
SQL> alter tablespace users coalesce;
Tablespace altered.
Elapsed: 00:00:20.98

Open in new window


the shrink one gave me this error:
alter tablespace users shrink space;
alter tablespace users shrink space
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace 

Open in new window

so, I cannot apply that one on my permanent tablespace...

anything else as ideas?
0
 
sdstuberCommented:
upgrade the tablespace to locally managed and try again?
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
no. the error (and docs) specify:
this statement only works for temporary tablespaces that are locally managed.
0
 
sdstuberCommented:
sorry I wasn't paying attention enough.  I meant shrink the table, not the tablespace
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
but I don't want to actually shrink the table (aka data segment), the size is just fine (and increasing) ...

the overflow segment is exactly 1 block, so shrinking alone won't do anything. ..

turning in circles ... :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
well, there is no syntax, from what I can see, to ONLY move the overflow segment.
moving the data partition with the overflow works, but just takes ( unnecessary long ) time.
0
 
sdstuberCommented:
The syntax I provided above does move only the overflow.  It might take a long time but it does work.  It worked in my example.  I moved one partition of the overflow, but the data partition (the index partition) did not move.

If you're not seeing the same thing can you provide the structure and syntax you're using so I can try to replicate.
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
I am using the same thing as you, except that each partition is on it's own tablespace.

the detail which confirms what I am saying is this:
the table partition data segments is using 26GB, on a tablespace of 40GB (with nothing else in the tablespace)
=> after 2 hours of work, the statement fails with the error that no more temporary segment can be created in the tablespace where the table partition is located.

why would it need to create temporary segments? only to "move" the data segments!
I tried this: increased the tablespace to 60GB, and the statement completed (after 3 hours)
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
well, I will push oracle support to check for the behavior.
in regards to the syntax, the answer is: there is no other syntax.
thanks for looking
0
 
sdstuberCommented:
>>> there is no other syntax.

right, the syntax presented above is it.  It works (if you have space), but it does some funny stuff behind the scenes that doesn't seem to be necessary (like requiring more space.)
0
 
Guy Hengel [angelIII / a3]Billing EngineerAuthor Commented:
Indeed this was the explanation.
This move of the io table requests internally a full review of the index.
Simply moved two times to have a clean situation.
Thanks for the feedback
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.