daily pastebin goal
75%
SHARE
TWEET

export/import data pump mixed case username

ExaGridDba Jul 30th, 2016 (edited) 83 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. $ cat expdp.schema.par
  2. directory=dpump
  3. logfile=logdir:expdp.schema.log
  4. dumpfile=expdp.schema.dat
  5. reuse_dumpfiles=true
  6. include=
  7. schemas:"='Brian'"
  8.  
  9. $ expdp u/u@//stormking:1521/pdbm parfile=expdp.schema.par
  10.  
  11. Export: Release 12.1.0.2.0 - Production on Sat Jul 30 19:06:47 2016
  12.  
  13. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
  14.  
  15. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  16. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  17. and Real Application Testing options
  18. ORA-39001: invalid argument value
  19. ORA-39038: Object path "SCHEMAS" is not supported for SCHEMA jobs.
  20.  
  21. $ cat expdp.dbms.datapump.sql
  22. set serveroutput on
  23. set linesize 32767
  24. set trimspool on
  25. whenever sqlerror exit 1
  26. column username format a30
  27.  
  28. define schemaname=&&1
  29. variable schemaname varchar2(30)
  30. exec :schemaname := '&&schemaname'
  31.  
  32. whenever sqlerror continue
  33. drop user "&&schemaname" cascade;
  34. whenever sqlerror exit 1
  35. create user "&&schemaname" identified by pw;
  36.  
  37. select username, created from dba_users where username = :schemaname;
  38.  
  39. declare
  40.         l_dph           number;
  41.         l_jobstate      varchar2(20);
  42.         l_schemaname    varchar2(30) := :schemaname;
  43.         l_filter        varchar2(100);
  44.  
  45. begin
  46.         l_filter := out_pkg.sprintf( q'{='%s'}', l_schemaname );
  47.         out_pkg.printf( 'filter=%s', l_filter );
  48.  
  49.         l_dph := dbms_datapump.open (
  50.                 operation       => 'EXPORT',
  51.                 job_mode        => 'SCHEMA'
  52.         );
  53.  
  54.         dbms_datapump.add_file (
  55.                 handle          => l_dph,
  56.                 filename        => 'expdp.schema.dat',
  57.                 directory       =>'DPUMP',
  58.                 reusefile       => 1
  59.         );
  60.  
  61.         dbms_datapump.add_file(
  62.                 handle          => l_dph,
  63.                 filename        => 'expdp.schema.log',
  64.                 directory       => 'LOGDIR',
  65.                 filetype        => dbms_datapump.ku$_file_type_log_file,
  66.                 reusefile       => 1
  67.         );
  68.  
  69.         dbms_datapump.metadata_filter (
  70.                 handle          => l_dph,
  71.                 name            => 'SCHEMA_EXPR',
  72.                 value           => l_filter
  73.         );
  74.  
  75.         dbms_datapump.start_job (
  76.                 handle          => l_dph
  77.         );
  78.  
  79.         dbms_datapump.wait_for_job (
  80.                 handle          => l_dph,
  81.                 job_state       => l_jobstate
  82.         );
  83.         out_pkg.printf( 'job state=%s', l_jobstate );
  84. end;
  85. /
  86.  
  87. drop user "&&schemaname" cascade;
  88.  
  89.  
  90. declare
  91.         l_dph           number;
  92.         l_jobstate      varchar2(20);
  93.         l_schemaname    varchar2(30) := :schemaname;
  94.         l_filter        varchar2(100);
  95.  
  96. begin
  97.         l_filter := out_pkg.sprintf( q'{='%s'}', l_schemaname );
  98.         out_pkg.printf( 'filter=%s', l_filter );
  99.  
  100.         l_dph := dbms_datapump.open (
  101.                 operation       => 'IMPORT',
  102.                 job_mode        => 'SCHEMA'
  103.         );
  104.  
  105.         dbms_datapump.add_file (
  106.                 handle          => l_dph,
  107.                 filename        => 'expdp.schema.dat',
  108.                 directory       =>'DPUMP'
  109.         );
  110.  
  111.         dbms_datapump.add_file(
  112.                 handle          => l_dph,
  113.                 filename        => 'impdp.schema.log',
  114.                 directory       => 'LOGDIR',
  115.                 filetype        => dbms_datapump.ku$_file_type_log_file,
  116.                 reusefile       => 1
  117.         );
  118.  
  119.         dbms_datapump.metadata_filter (
  120.                 handle          => l_dph,
  121.                 name            => 'SCHEMA_EXPR',
  122.                 value           => l_filter
  123.         );
  124.  
  125.         dbms_datapump.start_job (
  126.                 handle          => l_dph
  127.         );
  128.  
  129.         dbms_datapump.wait_for_job (
  130.                 handle          => l_dph,
  131.                 job_state       => l_jobstate
  132.         );
  133.         out_pkg.printf( 'job state=%s', l_jobstate );
  134. end;
  135. /
  136.  
  137. select username, created from dba_users where username = :schemaname;
  138. quit
  139.  
  140. $ sqlplus u/u@//stormking:1521/pdbm @ expdp.dbms.datapump.sql Brian
  141.  
  142. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 30 19:03:24 2016
  143.  
  144. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
  145.  
  146. Last Successful login time: Sat Jul 30 2016 19:01:43 -04:00
  147.  
  148. Connected to:
  149. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  150. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  151. and Real Application Testing options
  152.  
  153.  
  154. PL/SQL procedure successfully completed.
  155.  
  156. old   1: drop user "&&schemaname" cascade
  157. new   1: drop user "Brian" cascade
  158.  
  159. User dropped.
  160.  
  161. old   1: create user "&&schemaname" identified by pw
  162. new   1: create user "Brian" identified by pw
  163.  
  164. User created.
  165.  
  166.  
  167. USERNAME                       CREATED
  168. ------------------------------ --------------
  169. Brian                          20160730 19:03
  170.  
  171. filter=='Brian'
  172. job state=COMPLETED
  173.  
  174. PL/SQL procedure successfully completed.
  175.  
  176. old   1: drop user "&&schemaname" cascade
  177. new   1: drop user "Brian" cascade
  178.  
  179. User dropped.
  180.  
  181. filter=='Brian'
  182. job state=COMPLETED
  183.  
  184. PL/SQL procedure successfully completed.
  185.  
  186.  
  187. USERNAME                       CREATED
  188. ------------------------------ --------------
  189. Brian                          20160730 19:03
  190.  
  191. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  192. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  193. and Real Application Testing options
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top