아래는 예제로 scott계정의 모든 데이터를 expdp로 백업 후 새로운 유저를 생성하여
impdp를 이용하여 밀어넣는 예입니다.
해당경로에 디렉토리를 생성 후 오라클에서 디렉토리를 지정.
datapump 기능은 디렉토리를 간접적으로 경유해서 사용할수 있음.
# mkdir /home/oracle/exp_dir
SQL> create or replace directory exp_dir as '/home/oracle/exp_dir';
Directory created.
expdp를 실행하는 계정의 권한 부여.
SQL> grant read,write on directory exp_dir to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
scott 계정 expdp로 백업실행
[oracle@localhost exp_dir]$ expdp scott/tiger directory=exp_dir dumpfile=expdp.dmp log=expdp.log;
Export: Release 11.2.0.4.0 - Production on Mon Apr 25 11:11:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=expdp.log" Location: Command Line, Replaced with: "logfile=expdp.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=exp_dir dumpfile=expdp.dmp logfile=expdp.log reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/exp_dir/expdp.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 25 11:11:41 2016 elapsed 0 00:00:17
[oracle@localhost exp_dir]$ ll
total 240
-rw-r----- 1 oracle oinstall 241664 Apr 25 11:11 expdp.dmp
-rw-r--r-- 1 oracle oinstall 1901 Apr 25 11:11 expdp.log
새로운 유저 생성 및 권한 부여
[oracle@localhost exp_dir]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 25 11:11:54 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user hhh identified by hhh;
User created.
SQL> grant connect ,resource to hhh;
Grant succeeded.
SQL> grant read,write on directory exp_dir to hhh;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
새로운 유저로 impdp 실행하여 데이터 import
[oracle@localhost exp_dir]$ impdp hhh/hhh directory=exp_dir dumpfile=expdp.dmp log=impdp.log remap_schma=scott:hhh
LRM-00101: unknown parameter name 'remap_schma'
[oracle@localhost exp_dir]$ impdp hhh/hhh directory=exp_dir dumpfile=expdp.dmp log=impdp.log remap_schema=scott:hhh
Import: Release 11.2.0.4.0 - Production on Mon Apr 25 11:13:44 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=impdp.log" Location: Command Line, Replaced with: "logfile=impdp.log"
Master table "HHH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HHH"."SYS_IMPORT_FULL_01": hhh/******** directory=exp_dir dumpfile=expdp.dmp logfile=impdp.log remap_schema=scott:hhh
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HHH"."DEPT" 5.929 KB 4 rows
. . imported "HHH"."EMP" 8.562 KB 14 rows
. . imported "HHH"."SALGRADE" 5.859 KB 5 rows
. . imported "HHH"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "HHH"."SYS_IMPORT_FULL_01" successfully completed at Mon Apr 25 11:13:47 2016 elapsed 0 00:00:02
확인
[oracle@localhost exp_dir]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 25 11:13:53 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn hhh/hhh
Connected.
SQL> select * from tab ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
※ ORA-39213:Metadata processing is not available
Step 1. sysdba login.
Setp 2. execute dbms_metadata_util.load_stylesheets
ORA-39181 :only partial table data may be exported due to fine grain access control
Grant the privilege EXEMPT ACCESS POLICY to the exporting user
'Oracle > admin' 카테고리의 다른 글
| Oracle11gR2 RAC Background Processes (0) | 2016.04.27 |
|---|---|
| RMAN CONFIGURE LIST (0) | 2016.04.25 |
| Shrink Temporary Tablespace (0) | 2016.04.22 |
| Shrink UNDO tablespace (0) | 2016.04.22 |
| XDB,JVM install/unInstall (0) | 2016.04.20 |