This discussion is archived
8 Replies Latest reply: Jul 30, 2013 10:50 AM by Dean Gagne RSS

IMPDP - Tables not getting truncated

d97258a2-85b8-4d30-b5c5-ab38aa4ea07e Newbie
Currently Being Moderated

Hello,

 

I am trying to import a set of tables from a dump file, and we are using tables=<table list> and table_exists_action=truncate options both and while doing so some of the tables are not getting truncated.

 

The impdp syntax that we are using is below :

 

Import: Release 11.2.0.2.0 - Production on Fri Jul 26 06:30:34 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "OTIS_PA_SUPT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OTIS_PA_SUPT"."SYS_IMPORT_TABLE_01":  otis_pa_supt/********@qus directory=PA_OTIS_DIR dumpfile=tbaadm_data.dmp logfile=tbaadm_
data_import.log tables=tbaadm.CCS_LIMIT_DETAILS_TBL,tbaadm.CCS_ORDER_DETAIL_TABLE,tbaadm.CCS_ORDER_HEADER_TABLE,tbaadm.CDC_TC_DENOM_TABLE,tbaadm.CDEP_CHRG_TB
L,tbaadm.CHANNEL_LEVEL_CODE_TABLE,tbaadm.CHANNEL_TRAN_GRID_TABLE,tbaadm.CHARGES_HISTORY_TBL,tbaadm.CHARGE_ASSESSMENT_TABLE,tbaadm.CHARGE_ASSESS_MOD_TABLE,tba
adm.CHARGE_LEVEL_CODE_TABLE,tbaadm.CHARGE_OFF_MOD_TABLE,tbaadm.CHARGE_OFF_TABLE,tbaadm.CHG_VAL_DATE_MOD_TABLE,tbaadm.CHQ_BOOK_MOD_TABLE,tbaadm.CHQ_BOOK_TABLE
,tbaadm.CHQ_DETAILS_TABLE,tbaadm.CHQ_REFUSED_TABLE,tbaadm.CHQ_REOD_PARM_TBL,tbaadm.CHRG_AMORT_DETAILS_TBL,tbaadm.CHRG_GROUP_ID_TABLE,tbaadm.CHRG_LVL_CODE_MOD
_TABLE,tbaadm.CHRG_REV_PROCESSING_TBL,tbaadm.CHRG_TRAN_REV_TEMP_TBL,tbaadm.CITY_TABLE,tbaadm.CLEARING_REJ_CODE_TABLE,tbaadm.CLG_ENCD_STS_TABLE,tbaadm.CLG_RUL
E_MAINT_TBL,tbaadm.CLG_SOL,tbaadm.CLG_TRAN_CODE_TABLE,tbaadm.CLG_UPLD_HEADER_TABLE,tbaadm.CLIENT_COND_CHRG_MOD_TABLE,tbaadm.CLIENT_COND_CHRG_TABLE,tbaadm.CL_
ARS_TABLE,tbaadm.CL_CRS_TABLE,tbaadm.CL_CUST_HEADER_TABLE,tbaadm.CL_CUST_TRAN_TABLE,tbaadm.CL_DDIT_REV_TABLE,tbaadm.CL_DRAW_DOWN_DETAIL_TABLE,tbaadm.CL_DRAW_
DOWN_HEADER_TABLE,tbaadm.CL_DRAW_DOWN_INFO_TABLE,tbaadm.CL_DRAW_DOWN_NOTICE_TBL,tbaadm.CL_DRAW_DOWN_SCHEDULE_TBL,tbaadm.CL_DRAW_DOWN_SHDL_MOD_TBL,tbaadm.CMB_
CSIS_GEN_ACCT_TABLE,tbaadm.CMS_CARD_SUB_TYPE_TABLE,tbaadm.CMS_CRD_ACCT_LINK_HT_TABLE,tbaadm.CMS_DOWNLOAD_DRIVER_TABLE,tbaadm.CMS_HOT_CARD_TABLE,tbaadm.CMS_PR
EPRINTED_CARD_TABLE CONTENT=data_only table_exists_action=truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TBAADM"."CMS_DOWNLOAD_DRIVER_TABLE"        551.3 KB    9040 rows
. . imported "TBAADM"."CMS_HOT_CARD_TABLE"               219.7 KB    2849 rows
. . imported "TBAADM"."CLEARING_REJ_CODE_TABLE"          10.96 KB      21 rows
. . imported "TBAADM"."CLG_TRAN_CODE_TABLE"              12.91 KB      10 rows
. . imported "TBAADM"."CMS_CARD_SUB_TYPE_TABLE"          17.46 KB       6 rows
. . imported "TBAADM"."CCS_LIMIT_DETAILS_TBL"                0 KB       0 rows
. . imported "TBAADM"."CCS_ORDER_DETAIL_TABLE"               0 KB       0 rows
. . imported "TBAADM"."CCS_ORDER_HEADER_TABLE"               0 KB       0 rows
. . imported "TBAADM"."CDC_TC_DENOM_TABLE"                   0 KB       0 rows

 

 

 

From the above log, it is clear that the 0 rows are getting imported which is true but the table in the destincation database is not getting truncated.

 

Please help. Many thanks in advance for the help.

 

Thanks.

  • 1. Re: IMPDP - Tables not getting truncated
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    In my  test case  below its working fine, can you show your result set here

    [oracle@vnode MYSHELL]$ expdp me/** directory=DATA_DD_DIR dumpfile=test_2.dmp logfile=test_2.log tables=ME.T
    Export: Release 11.2.0.2.0 - Production on Tue Jul 30 03:13:10 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 - Production
    With the Partitioning, Data Mining and Real Application Testing options
    Starting "ME"."SYS_EXPORT_TABLE_01":  me/******** directory=DATA_DD_DIR dumpfile=test_2.dmp logfile=test_2.log tables=ME.T
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 0 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    . . exported "ME"."T"                                        0 KB       0 rows
    Master table "ME"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for ME.SYS_EXPORT_TABLE_01 is:
      /home/oracle/MYSHELL/test_2.dmp
    Job "ME"."SYS_EXPORT_TABLE_01" successfully completed at 03:13:27
    SQL> select * from t;
    no rows selected
    SQL> insert into t values ('233207332711');
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> select * from t;
    ID
    --------------------
    233207332711
    SQL> host impdp directory=DATA_DD_DIR dumpfile=test_2.dmp logfile=test_2.log tables=ME.T CONTENT=data_only table_exists_action=truncate
    Import: Release 11.2.0.2.0 - Production on Tue Jul 30 03:17:27 2013
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    Username: me/me
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, Data Mining and Real Application Testing options
    Master table "ME"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
    Starting "ME"."SYS_IMPORT_TABLE_01":  me/******** directory=DATA_DD_DIR dumpfile=test_2.dmp logfile=test_2.log tables=ME.T CONTENT=data_only table_exists_action=truncate
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "ME"."T"                                        0 KB       0 rows
    Job "ME"."SYS_IMPORT_TABLE_01" successfully completed at 03:17:36
    SQL>  select * from t;
    no rows selected
    
  • 2. Re: IMPDP - Tables not getting truncated
    Dean Gagne Expert
    Currently Being Moderated

    It looks like your dump file does not have any metadata in it.  I wonder if this is what is confusing the Data Pump job.  You are not telling Data Pump that this is data only so I wonder if there could be a bug in the code.   The reason I say there isn't any metadata is because I don't see anything like:

     

    Processing object type SCHEMA_EXPORT/TABLE/TABLE


    I would expect this line and then a bunch of table failures saying the table exists and will be truncated.   If this is really a DATA_ONLY job, can you add:

     

    content=data_only

     

    and rerun your command.  If it works with this, then I would say that this is a bug and you could contact Oracle Support to see if there is a fix for it.

     

    Dean

  • 3. Re: IMPDP - Tables not getting truncated
    d97258a2-85b8-4d30-b5c5-ab38aa4ea07e Newbie
    Currently Being Moderated

    Hi,

     

    Actually what is hapenning is..

    say there are three tables and out of the them the first has data in souce and the other two does not..   8:50 AM

    in that case what is hapenning is.. the first table with data is getting loaded properly and the second table just gets truncated and any table followed by it does not get truncated at all...

     

    Sorry... but I do not have the test cases to give here... as a work around we have truncated all the destincation db tables and then done the import.. which has worked... but the table_exists action=truncate should also have worked ! ??

  • 4. Re: IMPDP - Tables not getting truncated
    d97258a2-85b8-4d30-b5c5-ab38aa4ea07e Newbie
    Currently Being Moderated

    Hi Dean,

     

    Here is part of the log... which has both Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA and CONTENT=data_only part in it......

     

    Import: Release 11.2.0.2.0 - Production on Tue Jul 30 06:34:37 2013

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    Master table "OTIS_PA_SUPT"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
    Starting "OTIS_PA_SUPT"."SYS_IMPORT_TABLE_03":  otis_pa_supt/********@q_corebnk_situat3.dfs.us directory=PA_OTIS_DIR dumpfile=tbaadm_data.dmp CONTENT=data_only TABLE_EXISTS_ACTION=truncate logfile=tbaadm_data_import.log tables=tbaadm.AAD_HIST_TABLE,tbaadm.AAD_TABLE,tbaadm.ABNRML_TRAN_AUDIT_TABLE,tbaadm.ACCOUNT_DO
    CUMENT_MOD_TABLE,tbaadm.ACCOUNT_DOCUMENT_TABLE,tbaadm.ACCOUNT_FACILITY_MOD_TABLE,tbaadm.ACCOUNT_FACILITY_TABLE,tbaadm.ACCOUNT_FILTER_TABLE,tbaadm.ACCOUNT_LBL
    _CODE_MOD_TABLE,tbaadm.ACCOUNT_LBL_CODE_TABLE,tbaadm.ACCOUNT_LBL_RELTN_MOD_TBL,tbaadm.ACCOUNT_LBL_RELTN_TBL,tbaadm.ACCOUNT_LIEN_MOD_TABLE,tbaadm.ACCTS_FOR_RE
    GUL_TABLE,tbaadm.ACCT_ACCESS_CODE_TABLE,tbaadm.ACCT_ASSMPTN_HIST_TABLE,tbaadm.ACCT_AUTH_SIGN_MOD_TABLE,tbaadm.ACCT_AUTH_SIGN_STMNT_TABLE,tbaadm.ACCT_CHNL_REL
    N,tbaadm.ACCT_DOC_DETAILS_TABLE,tbaadm.ACCT_ENTRY_TABLE,tbaadm.ACCT_MODIFIED_HIST_TABLE,tbaadm.ACCT_NOMINATION_HDR_TABLE,tbaadm.ACCT_NOMINATION_MOD_TABLE,tba
    adm.ACCT_OPN_MATRIX_TABLE,tbaadm.ACCT_PARTITION_TABLE,tbaadm.ACCT_PLACEHOLDER_TABLE,tbaadm.ACCT_STATEMENT_MOD_TABLE,tbaadm.ACCT_TAX_STATUS_HIST_TBL,tbaadm.AC
    CT_TRANSFER_TABLE,tbaadm.ACCT_TURN_OVER_TABLE,tbaadm.ACCT_ZAKAT_DET_TABLE,tbaadm.ACH_ADDTNL_RMKS_HIST_TBL,tbaadm.ACH_ADDTNL_RMKS_TBL,tbaadm.ACH_ADVICE_HEADER
    _TABLE,tbaadm.ACH_ADVICE_TABLE,tbaadm.ACH_ENTRY_DETAIL_TMP_TABLE,tbaadm.ACH_ENT_DTL_HIST_TBL,tbaadm.ACH_FILE_RECALL_RQST_TBL,tbaadm.ACH_HDR_DTL_HIST_TBL,tbaa
    dm.ACH_HEADER_DETAIL_TABLE,tbaadm.ACH_PAYSYS_REASON_TABLE,tbaadm.ACH_RECORD_RECALL_REQUEST,tbaadm.ACH_REVOKE_STOP_PAY_TABLE,tbaadm.ADDNL_TRAN_DETAIL_TABLE,tb
    aadm.ADHOC_PROV_DETAIL_MOD_TBL,tbaadm.ADHOC_PROV_DETAIL_TBL,tbaadm.ADHOC_PROV_HISTORY_TBL,tbaadm.AGENT_MASTER_TABLE,tbaadm.AHD_TMP_TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "TBAADM"."ABNRML_TRAN_AUDIT_TABLE"          1023. KB   13405 rows
    . . imported "TBAADM"."ACCT_OPN_MATRIX_TABLE"            36.62 KB     133 rows
    . . imported "TBAADM"."ACH_PAYSYS_REASON_TABLE"          71.57 KB     603 rows
    . . imported "TBAADM"."ACCOUNT_LBL_CODE_TABLE"           10.39 KB      10 rows
    . . imported "TBAADM"."ACCT_PLACEHOLDER_TABLE"           31.62 KB     269 rows
    . . imported "TBAADM"."AAD_HIST_TABLE"                       0 KB       0 rows
    . . imported "TBAADM"."AAD_TABLE"                            0 KB       0 rows
    . . imported "TBAADM"."ACCOUNT_DOCUMENT_MOD_TABLE"           0 KB       0 rows
    . . imported "TBAADM"."ACCOUNT_DOCUMENT_TABLE"               0 KB       0 rows
    . . imported "TBAADM"."ACCOUNT_FACILITY_MOD_TABLE"           0 KB       0 rows
    . . imported "TBAADM"."ACCOUNT_FACILITY_TABLE"               0 KB       0 rows
    . . imported "TBAADM"."ACCOUNT_FILTER_TABLE"                 0 KB       0 rows

  • 5. Re: IMPDP - Tables not getting truncated
    Dean Gagne Expert
    Currently Being Moderated

    I'm pretty sure i have seen this and I think there may be a patch available.  Check with Oracle Support for a patch for your particular version of Oracle.

     

    Dean

  • 6. Re: IMPDP - Tables not getting truncated
    Dean Gagne Expert
    Currently Being Moderated

    Did everything get truncated properly?  I'm going to guess yes, but I can't tell from the log file.

     

    Dean

  • 7. Re: IMPDP - Tables not getting truncated
    d97258a2-85b8-4d30-b5c5-ab38aa4ea07e Newbie
    Currently Being Moderated

    No.. everything is not getting truncated properly...... but there are no errors in the log...

     

    Let me give you some more details on this....  Say I am importing two tables using the below syntax....

     

    impdp otis_pa_supt/supt20121030@q_corebnk_situat3.dfs.us directory=PA_OTIS_DIR dumpfile=pabnk_20130730.dmp TABLE_EXISTS_ACTION=TRUNCATE CONTENT=data_only logfile=imp_data_import.log tables=tbaadm.ACCT_AUTH_SIGN_MOD_TABLE,tbaadm.ACCTS_FOR_REGUL_TABLE,tbaadm.ACCT_PLACEHOLDER_TABLE

     

    Here... the two tables are tbaadm.ACCT_AUTH_SIGN_MOD_TABLE, and tbaadm.ACCTS_FOR_REGUL_TABLE

     

    The dump file has got no date for both the tables and the destination data base has got the data in them so they have to get truncated and then the data has to be loaded in the destination DB.... but what is hapenning is.. the first table is getting truncated and the second one is not getting truncated.. but in the logs there are no erros and it says..... 0 rows imported.... which is fine but the previous data is present in it....

  • 8. Re: IMPDP - Tables not getting truncated
    Dean Gagne Expert
    Currently Being Moderated

    Check with Oracle Support to see if there is a patch available for your version.

     

    Dean

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points