Last week I have faced
the fragmentation issue.
I did below steps and
know while import, impdp will create the indexes very fast compare as Manual
index creation.
Steps:
1. Export the table
2. Truncate the table
3. Drop the indexes -
Table size was very huge. So while import it causing perf issue. So I dropped
the indexes.
4. Import the table
After table imported,
i have checked the index status and it was not created.
So I manually created
the indexes and it took some times.
Why impdp didn’t
import the indexes. Why?
I did some test cases
and found the root cause.
===========================================================================
-- Create some test
table and index
SQL> create table
AM_TEST as select * from dba_objects;
Table created.
SQL> select count(*)
from AM_TEST; --60935 rows
SQL> create index
obj_idx_AM_test on AM_TEST(object_id);
Index created.
--To take the test
table Export
$ cat exp_Manoj_AM_TEST_Jun22.par
userid=Manoj/Manoj
DIRECTORY=DP
DUMPFILE=exp_Manoj_tables_AM_TEST%u.dmp
LOGFILE=exp_Manoj_tables_AM_TEST.log
PARALLEL=4
ESTIMATE=STATISTICS
JOB_NAME=EXP_AM_Manoj
compression=ALL
TABLES=(Manoj.AM_TEST)
Take the table backup
$ expdp
parfile=exp_Manoj_AM_TEST_Jun22.par
Export: Release 11.2.0.2.0
- Production on Fri Jun 22 14:17:19 2013
Copyright (c) 1982,
2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
Starting
"Manoj"."EXP_AM_Manoj": Manoj/********
parfile=exp_Manoj_AM_TEST_Jun22.par
Estimate in progress
using STATISTICS method...
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
. estimated
"Manoj"."AM_TEST"
7 MB
Total estimation using
STATISTICS method: 7 MB
. . exported "Manoj"."AM_TEST"
647.9 KB 60935 rows
Processing object type
TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table
"Manoj"."EXP_AM_Manoj" successfully loaded/unloaded
******************************************************************************
Dump file set for Manoj.EXP_AM_Manoj
is:
/U01/backup/exp_Manoj_tables_AM_TEST01.dmp
/U01/backup/exp_Manoj_tables_AM_TEST02.dmp
Job
"Manoj"."EXP_AM_Manoj" successfully completed at 14:17:29
--Truncated the table
and dropped the indexes
--Import the table
$cat
imp_467302_AM_TEST_Jun22.par
userid=Manoj/Manoj
DIRECTORY=DP
DUMPFILE=exp_Manoj_tables_AM_TEST%u.dmp
LOGFILE=imp_Manoj_tables_AM_TEST.log
PARALLEL=4
JOB_NAME=EXP_AM_Manoj
TABLES=(Manoj.AM_TEST)
TABLE_EXISTS_ACTION=APPEND
After imported the table,
data was loaded and but index not there and I reproduced the same issue.
SQL> select count(*)
from Manoj.AM_test;
--60935 rows
SQL> select
owner,index_name,table_name from dba_indexes where table_name='AM_TEST';
no rows selected
So again I have truncated
the table and imported again ( just change the TABLE_EXISTS_ACTION=REPLACE
parameter instead of APPEND)
$cat
imp_467302_AM_TEST_Jun22.par
userid=Manoj/Manoj
DIRECTORY=DP
DUMPFILE=exp_Manoj_tables_AM_TEST%u.dmp
LOGFILE=imp_Manoj_tables_AM_TEST.log
PARALLEL=4
JOB_NAME=EXP_AM_Manoj
TABLES=(Manoj.AM_TEST)
TABLE_EXISTS_ACTION=REPLACE
--Import the table
$ impdp
parfile=imp_467302_AM_TEST_Jun22.par
Import: Release
11.2.0.2.0 - Production on Fri Jun 22 15:51:22 2013
Copyright (c) 1982,
2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
Master table "Manoj"."EXP_AM_Manoj"
successfully loaded/unloaded
Starting " Manoj "."EXP_AM_
Manoj ": Manoj /******** parfile=imp_467302_AM_TEST_Jun22.par
Processing object type
TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
. . imported " Manoj
"."AM_TEST"
647.9 KB 60935 rows
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job " Manoj"."EXP_AM_
Manoj" successfully completed at 15:51:23
SQL> select
owner,index_name,table_name from dba_indexes where table_name='AM_TEST';
OWNER
INDEX_NAME
TABLE_NAME
------------------------------
------------------------------ ------------------------------
Manoj
OBJ_IDX_AM_TEST
AM_TEST
Cause:
While import we used TABLE_EXIST_ACTION=APPEND parameter.
So this parameter only importing data on that table and its skipped the
indexes.
Solution:
1. Instead of truncate we can drop
the table and import the table. (or)
2. TABLE_EXIST_ACTION=REPLACE – use this command
while import. (Internally it will drop and recreate the table)
I am not sure …I
hope TABLE_EXIST_ACTION=APPEND
doesn’t import the any DDL commands like (indexes, constraints statements),
Statistics and grants…
Need to dig some more
:-)
No comments:
Post a Comment