4 Replies Latest reply: Feb 6, 2013 6:00 PM by Dean Gagne RSS

    query option length in expdp ?

    871594
      Hi Friends,

      We are importing data from one DB to other.Schema contains 200 tables and each table contains 1million rows. We are using query option to subset the data using different conditions instead of loading full table data .

      We want to subset from different tables. But query option in expdp only taking 1800 characters only.

      why it is taking upto 1800 characters only. Any restriction imposed on query option.

      Anyone suggest me how to increase query clause length?

      belwo is the example of export command.

      otispa/********@otisua1 schemas=tbaadm directory=PA_OTIS_DIR dumpfile=tbaadm
      data.dmp CONTENT=all tableexists_action=replace query='tbaadm.ACCOUNT_LIEN_HISTORY_TABLE:"where ACID in(select FINACLE_INT_BNK_ACC
      T_ID from INIT_ACCT_LD)"','tbaadm.DISCRET_ADVN_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.TEMP
      DISCRETADVN_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.PYMNT_RCPT_DET_TABLE:"where ACID in(s*
      *elect FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.STOP_PAYMENT_ADDTNL_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID f*
      *rom INIT_ACCT_LD)"','tbaadm.STOP_PAYMENT_REG_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.GEN_AC*
      CT_CLASS_MOD_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.SCHM_CHANGE_TABLE:"where ACID in(selec
      t FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.INT_RECALC_PARAMETER_TBL:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from I
      NIT_ACCT_LD)"','tbaadm.INT_ADMINISTRATION_TABLE:"where ACID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.MAND_MNT_
      DOC_MAST_TBL:"where CIF_ID in(select CIF_ID from INIT_ACCT_LD)"','tbaadm.SD_LOCK_CUSTOMER_MASTER:"where CUST_ID in(select CUST_ID fr
      om INIT_ACCT_LD)"','tbaadm.SI_HEADER_TABLE:"where CUST_ID in(select CUST_ID from INIT_ACCT_LD)"','tbaadm.US_CTR_AGGREGATE_TBL:"where
      ENTITY_ID in(select FINACLE_INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.INTEREST_DETAILS_TABLE:"where ENTITY_ID in(select FINACLE_
      INT_BNK_ACCT_ID from INIT_ACCT_LD)"','tbaadm.INT_ADJUSTMENT_RGTR_TABLE:"where ENTITY_ID in(select FINACLE_INT_BNK_ACCT_ID from INIT_
      ACCT_LD)"','tbaadm.ACM_UPL:"where FORACID in(select BNK_ACCT_NBR from INIT_ACCT_LD)"'

      Thanks,
      venkat Vadlamudi.
        • 1. Re: query option length in expdp ?
          BluShadow
          Question moved to Oracle Discussion Forums » Oracle Database » General Questions
          as it is not an SQL or PL/SQL question.
          • 2. Re: query option length in expdp ?
            sb92075
            868591 wrote:
            Hi Friends,

            We are importing data from one DB to other.Schema contains 200 tables and each table contains 1million rows. We are using query option to subset the data using different conditions instead of loading full table data .

            We want to subset from different tables. But query option in expdp only taking 1800 characters only.

            why it is taking upto 1800 characters only. Any restriction imposed on query option.

            Anyone suggest me how to increase query clause length?
            place query in control file
            • 3. Re: query option length in expdp ?
              Max Seleznev
              According to Oracle documentation the maximum length of the the QUERY string is 3998 bytes + 2 quotation marks.

              You might want to pre-build some views in your database that would satisfy the same conditions you would otherwise put in your QUERY string. This way you can use these views in your new QUERY condition and significantly reduce its length.

              Hope it helps.
              • 4. Re: query option length in expdp ?
                Dean Gagne
                Remember, it is 3998 bytes, if you are using multibyte characters, this will reduce he number of characters you can specify.

                Dean