Reorganize a table in Oracle database with CTAS method

Reorganize a table in Oracle database with CTAS method

In Oracle we have different method for reorg the table in database.
1. CTAS Method (create table as select)
2. Export and Import
3. Move the table
4. Shrink Segment

We are using the CTAS method with example for reorganize of table.
For CTAS you should stop the DML operation on the table, otherwise it difficult to manage. Means you need to downtime for it to do for 100% accuracy or consistency in data.

CREATE TABLE AS SELECT(CTAS)
In this first we export the structure of table with EXPORT utility. Then create a new table with CTAS method. Then drop the old table and rename the new created table to old one. Then import the structure which include indexes, constraints etc.

1. Export table structure

exp scott/tiger file=emp.dmp tables=emp rows=n

(Or)

expdp scott/tiger directory= dumpfile=emp.dmp tables=emp

Example:
> exp file=C:\test\test.dmp tables=test1 rows=n
Export: Release 18.0.0.0.0 - Production on Tue Sep 17 14:45:18 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table TEST1
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Note: Export backup take all tables constraints and index associated with it.

2. Create a copy of the original table

create table new_emp as select * from emp;
Example:
SQL> create table test11 as select * from test1;
Table created.

3. Drop the original table and all indexes/constraints

drop table emp cascade constraints;
Example:
SQL> drop table test1 cascade constraints;
Table dropped.

Note: This statement drops all constraints and indexes

4. Rename new table

alter table new_emp rename to emp;
Example:
SQL> alter table test11 rename to test1;
Table altered.

5. Recreate all indexes and constraints

imp scott/tiger file=emp.dmp ignore=y

Note: EMP already exists. Ignore this error message by specifying the ignore=Y parameter in export command.

6. Verify the result.

SQL> select index_name from dba_indexes where table_name='TEST1';
INDEX_NAME
-----------
TEST1_IDX

SQL> select * from test1;
ID BIRTHDATE
---- ---------
1 29-SEP-84

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.