Reorganize the table with Export and Import in Oracle

Reorganize the table with Export and Import in Oracle

Reorganization of table is done with export and import in Oracle. Downtime is required for this operation.
Need to take place this activity in RESTRICT mode of Oracle. So that consistency of data is remained.

Advantages:
1. Faster compared to other re-org options.
2. Extra space is not required if source table is dropped before import.
3. Recommended method if the entire tablespace re-org is required.
4. Easier to use when there are too many objects for reorganization.

Following are the steps to reorg the table in Oracle
1. Export the table with following commands.
It include all things related to employee table including indexes, constraints, data , structure. Make sure it successfully done.
exp scott/tiger file=emp.dmp tables=emp
(Or)
expdp scott/tiger directory= dumpfile=emp.dmp tables=emp

Example:
C:\Users\Oracle>exp file=C:\test\emp.dmp tables=test1
Export: Release 18.0.0.0.0 - Production on Tue Sep 17 14:22:56 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)
About to export specified tables via Conventional Path ...
. . exporting table TEST1 1 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

2. Drop the table with all indexes.

drop table emp CASCADE CONSTRAINTS;

Example:
SQL> drop table test1 CASCADE CONSTRAINTS;
Table dropped.

Note: This statement drops all constraints and indexes

3. Import the table again.

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

Example:
C:\Users\Oracle>imp file=c:\test\emp.dmp full=y
Import: Release 18.0.0.0.0 - Production on Tue Sep 17 14:26:13 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 file created by EXPORT:V18.00.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403: Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "TEST1" 1 rows imported
Import terminated successfully with warnings.

4. Verify the Status of table.

SQL> select * from test1;

ID BIRTHDATE
---------- ---------
1 29-SEP-84

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

INDEX_NAME
-------------
TEST1_IDX

1 thought on “Reorganize the table with Export and Import in Oracle

  1. Pingback: Reorganize a table in Oracle database with CTAS method | Smart way of Technology

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.