별도 실습) http://cafe.naver.com/ocmkorea/3221
SYS@orcl>create tablespace udata01 datafile
2 '/u01/app/oracle/oradata/orcl/udata01.dbf' size 4m;
Tablespace created.
SYS@orcl>create tablespace udata02 datafile
2 '/u01/app/oracle/oradata/orcl/udata02.dbf' size 4m;
Tablespace created.
SYS@orcl>create table hr.emp_99 tablespace udata01
2 as select * from hr.employees;
Table created.
SYS@orcl>create table hr.dept_99 tablespace udata02
2 as select * from hr.departments;
Table created.
SYS@orcl>alter table hr.dept_99 add primary key(department_id);
Table altered.
1 alter table hr.emp_99 add constraint fk_dept99 foreign key (department_id)
2* references hr.dept_99(department_id)
SYS@orcl>/
Table altered.
SYS@orcl>select * from transport_set_violations;
no rows selected
SYS@orcl>begin
2 dbms_tts.transport_set_check(
3 ts_list=>'udata01, udata02',
4 incl_constraints=>TRUE);
5 end;
6 /
PL/SQL procedure successfully completed.
SYS@orcl>select * from transport_set_violations;
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index HR.SYS_C0011696 in tablespace USERS enforces primary constraints of table HR.DEPT_99 in tablespace UDATA02.
SYS@orcl>alter index hr.SYS_C0011696 rebuild tablespace udata02;
Index altered.
1) read only 모드 변경
SYS@orcl>alter tablespace udata01 read only;
Tablespace altered.
SYS@orcl>alter tablespace udata02 read only;
Tablespace altered.
SYS@orcl>create directory ext_dir as '/home/oracle/files';
Directory created.
SYS@orcl>grant read, write on directory ext_dir to system;
Grant succeeded.
[oracle@lnx04 orcl]$ expdp system/oracle directory=ext_dir TRANSPORT_FULL_CHECK=y transport_tablespaces=udata01, udata02 dumpfile=tts1.dmp
Export: Release 11.2.0.1.0 - Production on Fri Oct 16 13:29:31 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=ext_dir TRANSPORT_FULL_CHECK=y transport_tablespaces=udata01, udata02 dumpfile=tts1.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/files/tts1.dmp
******************************************************************************
Datafiles required for transportable tablespace UDATA01:
/u01/app/oracle/oradata/orcl/udata01.dbf
Datafiles required for transportable tablespace UDATA02:
/u01/app/oracle/oradata/orcl/udata02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:30:00
[oracle@lnx04 orcl]$ cp udata0* /u01/app/oracle/oradata/prod/
-------------------------------Target DB-----------------------------------
SYS@prod>create directory ext_dir as '/home/oracle/files';
Directory created.
SYS@prod>grant read, write on directory ext_dir to system;
Grant succeeded.
[oracle@lnx04 files]$ impdp system/oracle directory=ext_dir dumpfile=tts1.dmp trans port_datafiles=/u01/app/oracle/oradata/prod/udata01.dbf,/u01/app/oracle/oradata/pro d/udata02.dbf
Import: Release 11.2.0.1.0 - Production on Fri Oct 16 15:56:35 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Productio n
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=ext_dir dumpfile=tts1.dmp transport_datafiles=/u01/app/oracle/oradata/prod/udata01.dbf,/u0 1/app/oracle/oradata/prod/udata02.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user HR does not exist in the database
HR 계정이 없어서 error가 발생했다
SYS@prod>create user hr identified by hr;
User created.
SYS@prod>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 files]$ impdp directory=ext_dir dumpfile=tts1.dmp transport_datafiles=/u01/app/oracle/oradata/prod/udata01.dbf,/u01/app/oracle/oradata/prod/udata02.dbf
Import: Release 11.2.0.1.0 - Production on Fri Oct 16 15:59:01 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA directory=ext_dir dumpfile=tts1.dmp transport_datafiles=/u01/app/oracle/oradata/prod/udata01.dbf,/u01/app/oracle/oradata/prod/udata02.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:59:08
'스터디북' 카테고리의 다른 글
| [10/17] 정리 (0) | 2015.10.17 |
|---|---|
| [10/17] Sol -> LNX TTS (0) | 2015.10.17 |
| [10/16] 정리 (0) | 2015.10.16 |
| [10/16] 실습 - 3 resumable space allocation (0) | 2015.10.16 |
| [10/16] 실습 - 2 Shrink Space (0) | 2015.10.16 |