How to rebuild the table in oracle using alter table move

Comment

Last updated on February 23rd, 2019 at 06:27 am

Today in this post, I will explain how to rebuild the oracle tables or move the different type of oracle tables. 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

1.move the table

Rebuild the indexes
Gather the statistics on them

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.
SQL> alter index SCOTTS.EMP_N2 rebuild tablespace USERS_IDX ;

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 queryThese type of table cannot be move by using move command or CTASSQL> 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

Export the table having column as LONG datatype using datapump or regular exp
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
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.
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 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.

Related Articles

Query to check table size in Oracle

How to Alter Table in Oracle

How to Truncate TABLE in Oracle