Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- $ cat expdp.schema.par
- directory=dpump
- logfile=logdir:expdp.schema.log
- dumpfile=expdp.schema.dat
- reuse_dumpfiles=true
- include=
- schemas:"='Brian'"
- $ expdp u/u@//stormking:1521/pdbm parfile=expdp.schema.par
- Export: Release 12.1.0.2.0 - Production on Sat Jul 30 19:06:47 2016
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
- and Real Application Testing options
- ORA-39001: invalid argument value
- ORA-39038: Object path "SCHEMAS" is not supported for SCHEMA jobs.
- $ cat expdp.dbms.datapump.sql
- set serveroutput on
- set linesize 32767
- set trimspool on
- whenever sqlerror exit 1
- column username format a30
- define schemaname=&&1
- variable schemaname varchar2(30)
- exec :schemaname := '&&schemaname'
- whenever sqlerror continue
- drop user "&&schemaname" cascade;
- whenever sqlerror exit 1
- create user "&&schemaname" identified by pw;
- select username, created from dba_users where username = :schemaname;
- declare
- l_dph number;
- l_jobstate varchar2(20);
- l_schemaname varchar2(30) := :schemaname;
- l_filter varchar2(100);
- begin
- l_filter := out_pkg.sprintf( q'{='%s'}', l_schemaname );
- out_pkg.printf( 'filter=%s', l_filter );
- l_dph := dbms_datapump.open (
- operation => 'EXPORT',
- job_mode => 'SCHEMA'
- );
- dbms_datapump.add_file (
- handle => l_dph,
- filename => 'expdp.schema.dat',
- directory =>'DPUMP',
- reusefile => 1
- );
- dbms_datapump.add_file(
- handle => l_dph,
- filename => 'expdp.schema.log',
- directory => 'LOGDIR',
- filetype => dbms_datapump.ku$_file_type_log_file,
- reusefile => 1
- );
- dbms_datapump.metadata_filter (
- handle => l_dph,
- name => 'SCHEMA_EXPR',
- value => l_filter
- );
- dbms_datapump.start_job (
- handle => l_dph
- );
- dbms_datapump.wait_for_job (
- handle => l_dph,
- job_state => l_jobstate
- );
- out_pkg.printf( 'job state=%s', l_jobstate );
- end;
- /
- drop user "&&schemaname" cascade;
- declare
- l_dph number;
- l_jobstate varchar2(20);
- l_schemaname varchar2(30) := :schemaname;
- l_filter varchar2(100);
- begin
- l_filter := out_pkg.sprintf( q'{='%s'}', l_schemaname );
- out_pkg.printf( 'filter=%s', l_filter );
- l_dph := dbms_datapump.open (
- operation => 'IMPORT',
- job_mode => 'SCHEMA'
- );
- dbms_datapump.add_file (
- handle => l_dph,
- filename => 'expdp.schema.dat',
- directory =>'DPUMP'
- );
- dbms_datapump.add_file(
- handle => l_dph,
- filename => 'impdp.schema.log',
- directory => 'LOGDIR',
- filetype => dbms_datapump.ku$_file_type_log_file,
- reusefile => 1
- );
- dbms_datapump.metadata_filter (
- handle => l_dph,
- name => 'SCHEMA_EXPR',
- value => l_filter
- );
- dbms_datapump.start_job (
- handle => l_dph
- );
- dbms_datapump.wait_for_job (
- handle => l_dph,
- job_state => l_jobstate
- );
- out_pkg.printf( 'job state=%s', l_jobstate );
- end;
- /
- select username, created from dba_users where username = :schemaname;
- quit
- $ sqlplus u/u@//stormking:1521/pdbm @ expdp.dbms.datapump.sql Brian
- SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 30 19:03:24 2016
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- Last Successful login time: Sat Jul 30 2016 19:01:43 -04:00
- Connected to:
- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
- and Real Application Testing options
- PL/SQL procedure successfully completed.
- old 1: drop user "&&schemaname" cascade
- new 1: drop user "Brian" cascade
- User dropped.
- old 1: create user "&&schemaname" identified by pw
- new 1: create user "Brian" identified by pw
- User created.
- USERNAME CREATED
- ------------------------------ --------------
- Brian 20160730 19:03
- filter=='Brian'
- job state=COMPLETED
- PL/SQL procedure successfully completed.
- old 1: drop user "&&schemaname" cascade
- new 1: drop user "Brian" cascade
- User dropped.
- filter=='Brian'
- job state=COMPLETED
- PL/SQL procedure successfully completed.
- USERNAME CREATED
- ------------------------------ --------------
- Brian 20160730 19:03
- Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
- and Real Application Testing options
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement