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 types of oracle tables using the “alter table move” command. we may want to rebuild the table for various reasons like fragmentation removal, moving to a different tablespace, etc.

General Steps for rebuilding the Oracle table is

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

How to rebuild the tables with no long column and LOB column using alter table move

A 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 another tablespace
• The other tablespace contains sufficient space to have the table
• If time is a constraint and the database server has multiple CPU, then we can run this command in 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 permanently changes to the table for parallelism, so better to reset after the table rebuild is done

alter table <table name> parallel 1;

Y

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 the table rebuild is completed. All should be valid, not unusable

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

With 12cR1, we can rebuild indexes with the move command itself

alter table <owner>.<table_name> move tablespace <tablespace name> update indexes;

With 12cR2, we can move the table online also

alter table <owner>.<table_name> move online tablespace <tablespace name> ;

This automatically rebuilds the indexes also

We also need to Generate the statistics on the table after the rebuild. we can generate statistics using DBMS_STATS or FND_STATS(in case of EBS)

How to rebuild the table in Oracle having Long column

Tables which are having a 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');

This type of table cannot be moved by using the 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 of tables have CLOB, LOB columns. 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 partitions are rebuilt 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 partitions 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 contains 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, the index partition can be rebuilt 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;

With 12cR1, we can move the table partition online also

alter table SCOTT.TEST_CON_NEW_LOT  move partition online  E1 tablespace INTERIM_TEMP;   
alter table SCOTT.TEST_CON_NEW_LOT  move partition online E2 tablespace INTERIM_TEMP; 
alter table SCOTT.TEST_CON_NEW_LOT  move partition online E3 tablespace INTERIM_TEMP; 
alter table SCOTT.TEST_CON_NEW_LOT  move partition online E4 tablespace INTERIM_TEMP; 
alter table SCOTT.TEST_CON_NEW_LOT  move partition online E5 tablespace INTERIM_TEMP;

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

Related Articles
oracle table size: Find out the Query to check table size in Oracle database, top ten big tables in particular schema or particular tablespace in Oracle
Alter Table in Oracle: Alter table in Oracle is used to modify columns, drop and add constraints, change the data type of the table column, change the table storage parameters
Truncate TABLE in Oracle: Truncate TABLE in Oracle is faster than delete from table in oracle. It is DDL statement and it does not fire the on delete triggers
DROP TABLE ORACLE:Learn about drop table in Oracle, Drop table if exists in Oracle, drop multiple tables in one command, drop table cascade constraints
Oracle Create table : Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with foreign key /primary key
ora-00942: table or view does not exist : Learn to troubleshoot ORA-00942 table or view does not exist in Oracle database.
ORA-01652 : ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle : This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile, find the temp usage by Session
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9542632500346189789