Solved

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

Posted on 2013-12-08
21
821 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 73

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 73

Expert Comment

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

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
 
LVL 73

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 142

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 73

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 142

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 73

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 73

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

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

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 73

Expert Comment

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

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 142

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 73

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 142

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 73

Accepted Solution

by:
sdstuber earned 495 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 142

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 73

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now