1) Table
- Normal
- IOT Tables starting with SYS_IOT_OVER
- Long Datatye column
2) Table Partition
3) Table Subpartition
4) Index
- Normal
- IOT Indexes starting with SYS_IOT_TOP
5) Index Partition
6) Index Subpartition
7) Lobsegment/LobIndex
1) Table
- Normal
Alter table
- IOT Tables starting with SYS_IOT_OVER
If we move the above table with normal command, we get the following error
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
To move the above table , use following method
a) Find the Iot_name
select OWNER,TABLE_NAME,TABLESPACE_NAME,IOT_NAME,IOT_TYPE
from dba_tables
where TABLE_NAME like ‘SYS_IOT_OVER_375778’;
OWNER TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
APPLSYS SYS_IOT_OVER_375778 APPS_TS_QUEUES AQ$_WF_CONTROL_G IOT_OVERFLOW
b) Find the queue table
Queue_table – remove the characters before the first underscore and last underscore of IOT_NAME
And then search for the queue table
So in our example IOT_NAME is AQ$_WF_CONTROL_G
So we are searching queue_table like ‘%WF_CONTROL%’
SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_CONTROL%';
QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_CONTROL APPLSYS
c) Install move_aqt package using Note 394713.1
d) exec move_aqt.move('APPLSYS',WF_CONTROL,'DUMMY');
e) Once this is done table SYS_IOT_OVER_375778 will be moved to DUMMY tablespace.
- Long Datatye column
While moving the tables with column of long datatype, we get the following error
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
These tables cannot be moved using normal move command.
We need to export, drop and import the tables.
2) Table Partiton
ALTER TABLE
MOVE PARTITION
TABLESPACE
Dba_tab_partitions -- to get the partition name
3) Table Subpartition
ALTER TABLE
MOVE SUBPARTITION
TABLESPACE
Dba_tab_subpartitions – to get the subpartition name
4) Index
- Normal
Alter index
- IOT Indexes starting with SYS_IOT_TOP
If we move the IOT indexes with normal commnad, we get the following error
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
To move the IOT indexes , use the following method
a) Find the table name on which index is created
SQL> select table_name,table_owner from dba_indexes where index_name like 'SYS_IOT_TOP_375805';
TABLE_NAME TABLE_OWNER
------------------------------ ------------------------------
AQ$_WF_WS_SAMPLE_G APPLSYS
b) If the table name is starting with AQ$
1)Find the queue table, in the same way as was done above for tables.
SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_WS_SAMPLE%';
QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_WS_SAMPLE APPLSYS
We have removed the characters before first underscore and after last underscore
When searching for queue table.
2) exec move_aqt.move('APPLSYS',’ WF_WS_SAMPLE’ ,'DUMMY');
Package move_aqt needs to be installed as mentioned above.
3) Index SYS_IOT_TOP_375805 will be moved to dummy tablespace.
If the table name is not starting with AQ$
1) Move the table_name , from step (a), this will move the index.
5) Index Partition
Alter index
tablespace
dba_ind_partitions -- to get the partition name
6) Index Subpartition
Alter index
tablespace
Dba_ind_subpartitions – to get the subpartition name
7) Lobsegment/LobIndex
alter table
store as (tablespace
To find the table_name and lob_column
select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME like ‘lobsegment_name’
SYS_LOB0000195332C00032$$ LOBSEGMENT
OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------
SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
IEO IEO_ICSM_QUEUE_TBL_1
USER_PROP
SYS_LOB0000195332C00032$$ SYS_IL0000195332C00032$$ APPS_TS_QUEUES
So from above output , following will be the command to move lobsegment
alter table IEO.IEO_ICSM_QUEUE_TBL_1 move lob (USER_PROP) store as (tablespace APPS_TS_QUEUES_NEW);
Note that , once the LOBSEGMENT is moved ,corresponding LOBINDEX will
Also move. You don’t need to run the command seperately for LOBINDEX.
Here is the dynamic query which will give the command to move LOBSEGMENT/INDEX
select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace APPS_TS_QUEUES_NEW);'
from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name like ‘
Solution for following issue implemented in SR 7067291.992
Follow this only for lobsegment on AQ$_WF_CONTROL_D.
SQL> select segment_name, segment_Type,owner from dba_segments WHERE tablespace_name
='APPS_TS_QUEUES';
SEGMENT_NAME
SEGMENT_TYPE OWNER
---------------------------------------------------------------------------------
------------------ ------------------------------
SYS_IL0000583185C00006$$
LOBINDEX APPLSYS
SYS_LOB0000583185C00006$$
LOBSEGMENT APPLSYS
SQL> alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
2 lobsegment (tablespace APPS_TS_QUEUES_NEW);
alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online
Found Note 418238.1
1> First stop WF related services like WF Mailer Agent, WF listener, etc.
2> Then run following SQL :
sqlplus
