expdp_procedure

create or replace procedure expdp_clt_perf_p_mod_out(p_start_time in date,
                                                   p_end_time   in date) is
  v_int_sql    varchar2(3000);
  v_table_name varchar2(100);
  v_tab_name   varchar2(100);
begin
  for i in (select distinct table_name
              from user_tab_columns
             where column_name in ('STAMPTIME')
               AND TABLE_NAME LIKE 'CLT%') loop
    v_table_name := i.table_name;
    --
 
    for k in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_clt
                from dual) loop
     --
     --
      insert into wt_expdp_sel(expdp_text,table_name) values( k.expdp_select_clt, v_table_name ) ;
      commit;
    end loop;
  end loop;
  -----
  -----
  for f in (select distinct table_name
              from user_tab_columns
             where column_name in ('START_TIME')
               AND (TABLE_NAME LIKE 'PERF%'
               or TABLE_NAME LIKE 'P_%'
               or TABLE_NAME LIKE 'MOD_%')) loop
    v_tab_name := f.table_name;
 
      for m in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_more
                   
                from dual) loop
      insert into wt_expdp_sel(expdp_text,table_name) values( m.expdp_select_more, v_tab_name ) ;
      commit;
    end loop;
  end loop;
end expdp_clt_perf_p_mod_out;

------------------------------------

2.wt_expdp_sel表：

-- Create table
create table WT_EXPDP_SEL
(
  expdp_text   VARCHAR2(3000),
  table_name VARCHAR2(40)
)
tablespace WCDMA ;
------------------------------

3.results:

1    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_2GNCELL.dmp query=CLT_CM_W_HW_2GNCELL:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_2GNCELL   
2    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_ADJNODE.dmp query=CLT_CM_W_HW_ADJNODE:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_ADJNODE   
3    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_AICH.dmp query=CLT_CM_W_HW_AICH:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_AICH  

expdp_query_where

1.USING 'QUERY' parameter on Command line

Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue

$ expdp query=employees:"where salary>10000" tables=employees

This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):

$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary>10000\ order by salary" tables=employees 
--------------------------------------------------------------
success sentence:
1.expdp scott/tiger directory=scottdump dumpfile=scott.dmp logfile=expdpscott.log query=emp:\"where deptno>20\"  tables=emp
--
2.expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_2GNCELL.dmp query=CLT_CM_W_HW_2GNCELL:\"where STAMPTIME
  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS') \" tables=CLT_CM_W_HW_2GNCELL
--------------------------------------------------------------
2.oracle`s answer:
expdp scott/tiger@db10g directory=TEST_DIR dumpfile=scott.dmp logfile=expdpSCOTT.log query=emp:\"where department_id>10\" tables=emp
--------------------------------------------------------------
3.Oracle Data Pump Export/Import

Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.
Oracle Datapump utility introduced in Oracle 10g Release1 and this utility can be invoked using expdp(export) and impdb(import) commands. User need to specify the export/import parameters to determine the operation and you can specify the parameters on the command line or in a parameter file.
The expdp and impdp uses the procedures provided in the DBMS_DATAPUMP package to execute the commands and DBMS_METADATA package is used to move the data.
Please note that it is not possible to start or restart data pump jobs on one instance in Oracle RAC if jobs currently running on other instances.
---------------------------------------------------------------------------
Oracle Data Pump Export :

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export
Click here to see Roles/privileges required for Export modes.
Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log
If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log
You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log
You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log
Click here to learn more on Transportable Tablespace with examples.

Oracle Data Pump Import :
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.
Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.
Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log
From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log
Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log
Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-
1. ORA-31631: privileges are required
   ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
    ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
    Cause: User does not have privileges to allocate an extent in the specified tablespace.
   Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.
Click here to learn Roles/ privileges required for Data pump Export and Import.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    IMP-00017: following statement failed with ORACLE error 3113:
    "BEGIN "
    "SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE   SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"
Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

5. Import failed with below errors
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
Cause: The user that you are importing does not have privileages on CTXSYS.DRIIMP package or CTXSYS user does not exists
Action: Create CTXSYS user or grant required permissions
Please see the Data pump Export and Import related documents:
Click here for Data Pump Export modes and Interfaces.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Posted by Satishbabu Gunukula at 6:40 PM

exp_procedure

create or replace procedure exp_clt_perf_p_mod_out(p_start_time in date,
                                                   p_end_time   in date) is
  v_int_sql    varchar2(3000);
  v_table_name varchar2(100);
  v_tab_name   varchar2(100);
  --v_sel_sql varchar2(3000) ;
begin
  for i in (select distinct table_name
              from user_tab_columns
             where column_name in ('STAMPTIME')
               AND TABLE_NAME LIKE 'CLT%') loop
 
    v_table_name := i.table_name;
    for k in (select 'exp wcdma/uwaysoft2010@db122 file = ' || v_table_name ||
                     '.dmp tables = ' || v_table_name ||
                     ' query = \"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'') '||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \"'  exp_select_clt
                from dual) loop
     /* v_int_sql := 'insert into wt_exp_sel(exp_text,table_name) values('''||k.exp_select_clt||''','''||v_table_name||''')';
      execute immediate v_int_sql
              USING p_start_time , p_end_time ;
      commit ;*/
      insert into wt_exp_sel(exp_text,table_name) values( k.exp_select_clt, v_table_name ) ;
      commit;
    end loop;
  end loop;
  -----
  -----
  for f in (select distinct table_name
              from user_tab_columns
             where column_name in ('START_TIME')
               AND (TABLE_NAME LIKE 'PERF%'
               or TABLE_NAME LIKE 'P_%'
               or TABLE_NAME LIKE 'MOD_%')) loop
 
    v_tab_name := f.table_name;
    for m in (select 'exp wcdma/uwaysoft2010@db122 file = ' || v_tab_name ||
                     '.dmp tables = ' || v_tab_name ||
                     ' query = \"where START_TIME between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'') '||
                     ' and ' || 'to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')   \" ' exp_select_more
                from dual) loop
      insert into wt_exp_sel(exp_text,table_name) values( m.exp_select_more, v_tab_name ) ;
      commit;
    end loop;
  end loop;
end exp_clt_perf_p_mod_out;
----------------------------------
----------------------------------
1    exp wcdma/uwaysoft2010@db122 file = CLT_CM_W_AL_ALWAYSONTIMER.dmp tables = CLT_CM_W_AL_ALWAYSONTIMER query = \"where STAMPTIME  between to_date('2012-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS')  and to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS')  \"    CLT_CM_W_AL_ALWAYSONTIMER
2    exp wcdma/uwaysoft2010@db122 file = CLT_CM_W_AL_AOONDCHPARAM.dmp tables = CLT_CM_W_AL_AOONDCHPARAM query = \"where STAMPTIME  between to_date('2012-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS')  and to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS')  \"    CLT_CM_W_AL_AOONDCHPARAM