How to rebuild the table in oracle using alter table move

Today in this post, I will explain how to rebuild the oracle tables or move the different type of  oracle tables using alter table move command. we may want to rebuild the table for various reason like fragmentation removal ,moving to different tablespace etc.

General tables for rebuilding the oracle table is

• Rebuild the table using alter table move
• Rebuild the indexes
• Gather the statistics on them

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 sufficient 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>;

If we are using parallel, then the above statement permanent changes to the table for parallelism,so better to reset after the table rebuild is done

alter table <table name> parallel 1;

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 SCOTT.EMP move tablespace USERS parallel 10;  
Table altered.
SQL> alter index SCOTT.EMP_N1 rebuild tablespace USERS_IDX;
Index altered.
SQL> alter index SCOTT.EMP_N2 rebuild tablespace USERS_IDX ;
Index altered.

It is good to check the status of all the indexes on that table after table rebuild is completed. All should be valid not unusable

select index_name, status from dba_indexes where table_name='&table' ;

How to rebuild the table in oracle having long column

Tables which are having long column

Example

SQL> desc FND_VIEW_TAB

We can identify all the tables using the query.

select a.owner ,table_name from dba_tab_columns a,dba_objects b
where a.owner in ('APPS','APPLSYS') and a.table_name=b.object_name and b.object_type='TABLE' and a.owner=b.owner
and data_type in ('LONG','LONG RAW');

These type of table cannot be move by using move command or CTAS

SQL> alter table FND_VIEW_TAB move tablespace tools;
alter table FND_VIEW_TAB move tablespace tools
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

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 re-enable 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.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table FND_VIEW_TAB 211 rows exported
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.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter table scott.FND_VIEW_TAB_TL drop constraints FND_VIEW_TAB_FK ;
Table altered.
SQL> drop table scott.FND_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 ;

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$$ ;

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;

similarly index partition can be rebuild in the same way

select 'alter index '||index_owner||'.'|| index_name ||
' rebuild partition ' || partition_name || ' tablespace INTERIM_TEMP;'
from dba_ind_partitions
where index_owner = '&owner'
and index_name in ( select index_name
from dba_indexes
where table_owner = '&owner'
and table_name = '&table'
and partitioned = 'YES')
order by index_name, partition_name;

Hope you like the content on How to rebuild the table in oracle and how to rebuild various segments in Oracle.

Related Articles
oracle table size
Alter Table in Oracle
Truncate TABLE in Oracle
DROP TABLE ORACLE
Oracle Create table
Find indexes status and assigned columns for a table
ora-00942: table or view does not exist