2 Replies Latest reply: May 2, 2014 2:57 AM by Karthick_Arp RSS

    oracle export command help required

    supersen

      Hello gurus

      i need to export data from MBE table based on some condition  as below

      SELECT MBE.*
        FROM  MBE,
              MSI
      WHERE MBE.ID = MSI.ID
         AND MSI.NAME = 'MOS' 
        
         how  can i achive this?
        
         
      exp username/password@schemaname \
            tables=MBE query=\"WHERE EXISTS\(SELECT  NULL FROM  MSI WHERE MSI.ID =\ MBE.ID AND MSI.NAME= 'MOS'\) \)\" file=MV.dmp   log=exp.log
        
         will it work?

       

      Please reply

       

      s

        • 1. Re: oracle export command help required
          michaelrozar17

          There is a separate Oracle forum for export/import related stuffs, visit: https://community.oracle.com/community/developer/english/oracle_database/export_import_sql_loader_%26_external_tables/co…

          Yes we could export based on conditions, why don't you try yourself the code you have written? Or visit   https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:478275606639   for examples

          • 2. Re: oracle export command help required
            Karthick_Arp

            I have a simple table export/import api writern using DBMS_DATAPUMP. Hope this helps.

             

            create or replace procedure export_table
            (
                 pOwner          in     varchar2,
                 pTableName     in     varchar2,
                 pDirectory     in     varchar2,
                 pFilterStr     in     varchar2
            )
            is
                 lDPJobName     varchar2(100);
                 lFileName     varchar2(100);
                 lJobState     varchar2(50)     := 'EXECUTING';
                 lHandle          number;
                 lStatus          ku$_Status1010;
            begin
                 lDPJobName := 'TABLE_EXPORT_' || to_char(sysdate, 'YYYYMMDDHH24MISS');
                 lFileName  := upper(pTableName) || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS');
            
                 lHandle := dbms_datapump.open('EXPORT', 'TABLE', job_name => lDPJobName);
            
                 dbms_datapump.add_file          (handle => lHandle, filename => lFileName, directory => pDirectory, filetype => dbms_datapump.ku$_file_type_dump_file);
                 dbms_datapump.add_file          (handle => lHandle, filename => lFileName, directory => pDirectory, filetype => dbms_datapump.ku$_file_type_log_file);
                 
                 dbms_datapump.set_parameter     (handle => lHandle, name => 'KEEP_MASTER',     value => 0);
                 dbms_datapump.set_parameter     (handle => lHandle, name => 'INCLUDE_METADATA', value => 1);
                 dbms_datapump.metadata_filter     (handle => lHandle, name => 'SCHEMA_EXPR',     value => '= ''' || upper(trim(pOwner)) || '''');
                 dbms_datapump.metadata_filter     (handle => lHandle, name => 'NAME_EXPR',     value => '= ''' || upper(trim(pTableName)) || '''');
                 dbms_datapump.data_filter     (handle => lHandle, name => 'SUBQUERY',          value => pFilterStr, table_name => pTableName, schema_name => pOwner);
                 
                 dbms_datapump.start_job          (handle => lHandle);
                 dbms_datapump.wait_for_job     (handle => lHandle, job_state => lJobState);
                 
                 if trim(upper(lJobState)) = 'STOPPED' then
                      raise_application_error(-20001, 'Job returned status as STOPPED');
                 else
                      dbms_datapump.detach(handle => lHandle);
                 end if;
            
                 lHandle := null;
            end;
            /
            show err
            create or replace procedure import_table
            (
                   pDirName      in  varchar2
                 , pDmpFileName  in     varchar2
            )
            is
                 lHandle          integer;
                 lDPJobName     VARCHAR2(100);
                 lJobState     varchar2(50)     := 'EXECUTING';
            begin
                 lDPJobName := 'TABLE_EXPORT_' || to_char(sysdate, 'YYYYMMDDHH24MISS');
                 
            
                 lHandle := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', remote_link => '', job_name => lDPJobName);
            
                 dbms_datapump.add_file          (handle => lHandle, filename => pDmpFileName, directory => pDirName, filetype => dbms_datapump.ku$_file_type_dump_file);
                 dbms_datapump.add_file          (handle => lHandle, filename => pDmpFileName || '_IMP', directory => pDirName, filetype => dbms_datapump.ku$_file_type_log_file);
                 dbms_datapump.set_parameter     (handle => lHandle, name => 'TABLE_EXISTS_ACTION', value => 'APPEND');
                 dbms_datapump.start_job          (handle => lHandle);
            
                 if trim(upper(lJobState)) = 'STOPPED' then
                      raise_application_error(-20001, 'Job returned status as STOPPED');
                 else
                      dbms_datapump.detach(handle => lHandle);
                 end if;
            
                 lHandle := null;
            end;
            /
            show err
            

             

            Its executed like this. I am using a TEMP table for example.

             

            SQL> create table temp as select * from all_objects
              2  /
            
            Table created.
            
            SQL> begin
              2    export_table
              3    (
              4      'KARTHICK',
              5      'TEMP',
              6      'DP_KAR_DIR',
              7      'WHERE OBJECT_NAME IN (SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = ''KARTHICK'')'
              8    );
              9  end;
             10  /
            
            PL/SQL procedure successfully completed.
            
            SQL> drop table temp purge;
            
            Table dropped.
            
            SQL> begin
              2    import_table
              3    (
              4        'DP_KAR_DIR'
              5      , 'TEMP_20140502034806.dmp'
              6    );
              7  end;
              8  /
            
            PL/SQL procedure successfully completed.
            
            SQL> select count(*) from temp;
            
              COUNT(*)
            ----------
                200302
            

             

            Here is the log details

             

            Export log...
            
            arttvt04% cat TEMP_20140502034806.log
            Starting "KARTHICK"."TABLE_EXPORT_20140502034806":  
            Estimate in progress using BLOCKS method...
            Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
            Total estimation using BLOCKS method: 23 MB
            Processing object type TABLE_EXPORT/TABLE/TABLE
            . . exported "KARTHICK"."TEMP"                             18.41 MB  200302 rows
            Master table "KARTHICK"."TABLE_EXPORT_20140502034806" successfully loaded/unloaded
            ******************************************************************************
            Dump file set for KARTHICK.TABLE_EXPORT_20140502034806 is:
              /home/arbor/karthick/TEMP_20140502034806.dmp
            Job "KARTHICK"."TABLE_EXPORT_20140502034806" successfully completed at 03:48:16
            
            Import log...
            
            arttvt04% cat TEMP_20140502034806.dmp_IMP
            Master table "KARTHICK"."TABLE_EXPORT_20140502034956" successfully loaded/unloaded
            Starting "KARTHICK"."TABLE_EXPORT_20140502034956":  
            Processing object type TABLE_EXPORT/TABLE/TABLE
            Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
            . . imported "KARTHICK"."TEMP"                             18.41 MB  200302 rows
            Job "KARTHICK"."TABLE_EXPORT_20140502034956" successfully completed at 03:50:00
            arttvt04%