Databases Reference
In-Depth Information
define cia_app_user=cia_app_jul
define cia_app_user_pwd=cia_app_jul_pwd
define cia_sel_user=cia_sel_jul
define cia_sel_user_pwd=cia_sel_jul_pwd
--
create user &&star_user identified by &&star_user_pwd;
grant connect,resource to &&star_user;
alter user &&star_user default tablespace dim_data;
--
create user &&cia_app_user identified by &&cia_app_user_pwd;
grant connect,resource to &&cia_app_user;
alter user &&cia_app_user default tablespace cia_data;
--
create user &&cia_sel_user identified by &&cia_app_user_pwd;
grant connect,resource to &&cia_app_user;
alter user &&cia_sel_user default tablespace cia_data;
2.
In your testing database, create a database link that points to your production
database. The remote user referenced in the
CREATE DATABASE LINK
statement must
have the DBA role granted to it in the production database. Here is a sample
CREATE
DATABASE LINK
script:
create database link dk
connect to darl identified by foobar
using 'dwdb1:1522/dwrep1';
3.
In your testing database, create a directory object that points to the location where you
want your log file to go:
SQL> create or replace directory engdev as '/orahome/oracle/ddl/engdev';
4.
Run the import command on the testing box. This command references the remote
database via the
NETWORK_LINK
parameter. The command also instructs Data Pump to map
the production database user names to the newly created users in the testing database.
$ impdp darl/engdev directory=engdev network_link=dk \
schemas='STAR2,CIA_APP,CIA_SEL' \
remap_schema=STAR2:STAR_JUL,CIA_APP:CIA_APP_JUL,CIA_SEL:CIA_SEL_JUL
This technique allows you to move large amounts of data between disparate databases without having to create
or copy any dump files or data files. You can also rename schemas on the fly via the
REMAP_SCHEMA
parameter. This is a
very powerful Data Pump feature that lets you transfer data quickly and efficiently.
■
Tip
When replicating entire databases, also consider using the rmaN duplicate database functionality.
