Last updated on September 18th, 2016 at 10:16 am
How to rebuild the table in oracle using alter table move oracle
Today in this post, I will explain how to rebuild or move the different type of tables
1) How to rebuild the tables with no long column and LOB column
Table having no long column can be moved using the syntax
| alter table <owner>.<table_name> move tablespace <tablespace name> |
We need to make user owner has storage capacity on the another tablespace
The another tablespace contain sufficent space to have the table
If time is constraint and database server has multiple cpu ,then we can run this command with parallel to reduce the move time
| alter table <owner>.<table_name> move tablespace <tablespace name> parallel < no>; |
We would also need to rebuild all the table indexes after the table rebuild.
| select ‘alter index ‘|| owner || ‘.’ || index_name || ‘ rebuild ;’ from dba_indexes where table_owner = ‘&owner’ and table_name = ‘&table_name’ and partitioned = ‘NO’ and index_type != ‘LOB’ order by owner, index_name; |
Example
| SQL> alter table SCOTTS.EMP move tablespace USERS parallel 10;
Table altered. SQL> alter index SCOTTS.EMP_N1 rebuild tablespace USERS_IDX; Index altered. Index altered. |
2) How to rebuild the table in oracle having long column
Tables which are having long column
Example
| SQL> desc FND_VIEW_TAB Name Null? Type —————————————– ——– —————————- APPLICATION_ID NOT NULL NUMBER(15) VIEW_ID NOT NULL NUMBER(15) VIEW_NAME NOT NULL VARCHAR2(30) LAST_UPDATE_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER(15) CREATION_DATE NOT NULL DATE CREATED_BY NOT NULL NUMBER(15) LAST_UPDATE_LOGIN NOT NULL NUMBER(15) DESCRIPTION VARCHAR2(240) IREP_COMMENTS VARCHAR2(4000) IREP_ANNOTATIONS VARCHAR2(4000) TEXT LONG We can identify all the tables using the query These type of table cannot be move by using move command or CTAS SQL> alter table FND_VIEW_TAB move tablespace tools;
|
We need to use the exp/imp to overcome this issue
1. Export the table having column as LONG datatype using datapump or regular exp
2. drop the table: You may have constraints issues while dropping the table. You have to disable the foreign key constraint and then drop the table
3. Import the table again with remap_tablespace parameter using impd or regular imp And then again reenable the constraints
Example
step 1
| exp system/pass file=table.dump tables=scott.FND_VIEW_TAB
Export: Release 11.2.0.4.0 – Production on Tue Jun 16 11:22:30 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. About to export specified tables via Conventional Path … Export terminated successfully without warnings. |
Step 2
| sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 11:22:44 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> alter table scott.FND_VIEW_TAB_TL drop constraints FND_VIEW_TAB_FK ; Table altered. SQL> drop table scottFND_VIEW_TAB; Table dropped. Step 3 imp system/pass file=table.dump full=Y alter table scott.FND_VIEW_TAB_TL add constraints FND_VIEW_TAB _FK ;
|
3)How to rebuild the table in oracle having LOB column.
Many types tables have CLOB,LOB column . Those can be moved using the command
| select ‘alter table ‘||owner||’.’||table_name||’ move lob(‘||column_name||’) STORE AS ‘||SEGMENT_NAME||’ ;’ from dba_lobs where owner =’&table_owner’ and segment_name =’&table_name’; |
Examples
| alter table SCOTT.TEST_CON_NEW_LOT_IMAGE move lob(IMAGE) STORE AS SYS_LOB00006252725875003$$ ; |
4) How to rebuild the table partition
Table partition are rebuild or move using the same technique as table move,but we just need to use table partition case of table
The below query will rebuild or move all the partition of the table to different tablespace INTERIM_TEMP
| select ‘alter table ‘||table_owner||’.’||table_name||’ move partition ‘||PARTITION_NAME||’ tablespace INTERIM_TEMP;’ from dba_tab_partitions where table_name=’&table_name’ and table_owner=’&table_owner’ ; |
Example
Suppose the table SCOTT.TEST_CON_NEW_LOT contain five partitions
| alter table SCOTT.TEST_CON_NEW_LOT move partition E1 tablespace INTERIM_TEMP;
alter table SCOTT.TEST_CON_NEW_LOT move partition E2 tablespace INTERIM_TEMP; alter table SCOTT.TEST_CON_NEW_LOT move partition E3 tablespace INTERIM_TEMP; alter table SCOTT.TEST_CON_NEW_LOT move partition E4 tablespace INTERIM_TEMP; alter table SCOTT.TEST_CON_NEW_LOT move partition E5 tablespace INTERIM_TEMP; |
Hope you like the content on How to rebuild the table in oracle and how to rebuild various segments in Oracle.