Results 1 to 3 of 3

Thread: expdp help

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: expdp help

    I am trying to get some records out of a table which has a CLOB field, and move that data into a table in another instance.

    I do not have the syntax right on the QUERY param, but I'm following the instructions that I located in the ORacle docs (http://download-west.oracle.com/docs....htm#i1009903). ANyone see something I'm doing wrong?

    Code:
    [oracle@ora4 dmp]$ vi expdptest_knbase_doc_20070213.par
    
    TABLES=knbase.knb_document
    QUERY=knbase.knb_document:'"WHERE mod_date > SYSDATE"'
    LOGFILE=test.log
    DIRECTORY=dpump_dir_test
    DUMPFILE=test.dmp
    
    [oracle@ora4 dmp]$ expdp knbase@test parfile=expdptest_knbase_doc_20070213.par  
    Export: Release 10.1.0.3.0 - Production on Tuesday, 13 February, 2007 15:12
    
    Copyright (c) 2003, Oracle.  All rights reserved.
    Password: 
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Starting "KNBASE"."SYS_EXPORT_TABLE_01":  knbase/************@test parfile=expdptest_knbase_doc_20070213.par 
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 960 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/TBL_OWNER_OBJGRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/TRIGGER
    ORA-31693: Table data object "KNBASE"."KNB_DOCUMENT" failed to load/unload and is being skipped due to error:
    ORA-06502: PL/SQL: numeric or value error
    ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
    LPX-314: an internal failure occurred
    Master table "KNBASE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for KNBASE.SYS_EXPORT_TABLE_01 is:
      /u03/flash_recovery_area/TEST/expdp/dmp/test.dmp
    
    Job "KNBASE"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 15:13
    If I remove the QUERY line, the expdp works fine.
    -Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Note:277010.1

    This document demonstrates how the QUERY parameter can be used with Export DataPump and Import DataPump. It also shows where quotes must be used in the WHERE clause.

    Incorrect usage of single or double quotes for the QUERY parameter can result in errors such as:

    LRM-00101: unknown parameter name 'empno'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Although I didn't figure out the problems with my QUERY parameter, I did get a subset of the table loaded. I used EXPDP to extract the full table, IMPDP to import that full table into another schema, deleted the records I didn't need, EXPDP'ed that table, and then IMPDP'ed that smaller recordset into the table I originally needed to update.

    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •