8 Replies Latest reply: Jul 30, 2013 12:50 PM by Dean Gagne-Oracle RSS

    IMPDP - Tables not getting truncated

    d97258a2-85b8-4d30-b5c5-ab38aa4ea07e

      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

          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-Oracle

            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

              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

                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-Oracle

                  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-Oracle

                    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

                      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-Oracle

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

                         

                        Dean