Today in this post, I will explain how to rebuild the oracle tables or move the different types of oracle tables using alter table move command. we may want to rebuild the table for various reasons like fragmentation removal, moving to the 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;
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' ;
How to rebuild the table in oracle having long column
Tables which are having a long column
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
- 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 re-enable the constraints
exp system/pass file=table.dump tables=scott.FND_VIEW_TAB Export: Release 18.104.22.168.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 22.214.171.124.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.
sqlplus / as sysdba SQL*Plus: Release 126.96.36.199.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 188.8.131.52.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.
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';
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' ;
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;
Hope you like the content on How to rebuild the table in oracle and how to rebuild various segments in Oracle.
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 column, drop and add constraints,change the datatype 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