Advertisement
ExaGridDba

export/import data pump mixed case username

Jul 30th, 2016
186
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.51 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement