Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-08
21
Medium Priority
?
1,054 Views
Last Modified: 2014-01-15
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)
0
Comment
  • 11
  • 10
21 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39706091
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39706136
I ran the above on 11.2.0.1
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39708010
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39708472
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39708490
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39708664
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39708721
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39708754
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39708786
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39710802
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39711276
upgrade the tablespace to locally managed and try again?
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39711483
no. the error (and docs) specify:
this statement only works for temporary tablespaces that are locally managed.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39711570
sorry I wasn't paying attention enough.  I meant shrink the table, not the tablespace
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39711605
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39718964
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39719279
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39719662
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1980 total points
ID: 39719990
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
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
ID: 39725036
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39725233
>>> 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
 
LVL 143

Author Closing Comment

by:Guy Hengel [angelIII / a3]
ID: 39784616
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question