| # 요약 | |
| # Export : Tablespace Level # Import : Tablespace Level # 논리적 디렉터리 CREATE DIRECTORY pump_dir AS '/home/oracle/data_pump'; GRANT READ, WRITE ON DIRECTORY pump_dir TO hr; DROP DIRECTORY pump_dir; ■ Data Pump : expdp, impdp # Export : 특정 테이블 추출 # Export : Schema Level, 특정 개체 제외 # Export : Schema Level, 특정 개체 다중 제외 # Export : schema에서 테이블만 가져오기 # Export : 소유자 및 테이블스페이스 변경(remap) # Export : 특정 테이블의 데이터 조건부 추출(query) # Export : 원격DB에서 특정 소유자의 모든 객체 가져오기(DB Link) # Import : 특정 테이블만 가져오기 # Import : 테이블이 있는 상태에서 데이터만 가져오기 # Import : 테이블구조만 가져오기 # Import : 다른 테이블로 데이터 가져오기(remap) # Import : 테이블스페이스 변경, 권한제외 # Import : 모든 객체의 소유자 및 테이블스페이스 변경, 권한제외 # Import : 외부 DB에서 동일한 유저명의 모든 객체를 가져오기. 권한, 트리거 제외(DB Link) |
|
| DROP TABLE hr.emp CASCADE CONSTRAINT PURGE; CREATE TABLESPACE insa_tbs DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M; CREATE TABLE hr.emp TABLESPACE insa_tbs AS SELECT * FROM hr.employees; SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ---------- ------------ ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1207350 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1207350 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1207350 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1207350 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1207350 6 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf INSA_TBS ONLINE 1212873 SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP'; SEGMENT_NAME TABLESPACE_NAME FILE_NAME ---------------------- --------------- -------------------------------------------------- EMP INSA_TBS /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf |
|
| # Export : Tablespace Level | |
| - 테이블스페이스 내 세그먼트를 전부 내보내기 /home/oracle [oracle@oracle ~]$ exp system/oracle file=insa_tbs.dmp tablespaces=insa_tbs ... About to export selected tablespaces ... For tablespace INSA_TBS ... . exporting cluster definitions . exporting table definitions . . exporting table EMP 107 rows exported . exporting referential integrity constraints . exporting triggers Export terminated successfully without warnings. [oracle@oracle ~]$ ls arch1 backup database hr_emp.dmp hr_owner.dmp insa_emp.dmp insa_tbs.dmp michael_owner.dmp new_control.sql p13390677_112040_LINUX_1of7.zip p13390677_112040_LINUX_2of7.zip |
|
| # Import : Tablespace Level | |
| DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES; SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP'; no rows selected > Export -> Import 시 세그먼트만 있기 때문에 테이블스페이스는 직접 생성해야한다. CREATE TABLESPACE insa_tbs DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M; SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ---------- --------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1207350 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1207350 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1207350 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1207350 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1207350 6 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf INSA_TBS ONLINE 1213598 # Import : 해당 테이블스페이스의 세그먼트 모두 가져오기 [oracle@oracle ~]$ imp system/oracle file=insa_tbs.dmp tablespaces=insa_tbs full=y ... Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SYSTEM's objects into SYSTEM . importing HR's objects into HR . . importing table "EMP" 107 rows imported Import terminated successfully without warnings. SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP'; SEGMENT_NAME TABLESPACE_NAME FILE_NAME ---------------------- --------------- -------------------------------------------------- EMP INSA_TBS /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES; |
|
| # 논리적 디렉터리 생성 | |
| [oracle@oracle ~]$ mkdir /home/oracle/data_pump [oracle@oracle ~]$ ls arch1 backup database data_pump hr_emp.dmp hr_owner.dmp insa_emp.dmp insa_tbs.dmp michael_owner.dmp new_control.sql p13390677_112040_LINUX_1of7.zip p13390677_112040_LINUX_2of7.zip # 논리적 디렉터리 생성 CREATE DIRECTORY pump_dir AS '/home/oracle/data_pump'; SELECT * FROM dba_directories WHERE directory_name = 'PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ---------------------------------------- SYS PUMP_DIR /home/oracle/data_pump |
|
| # 논리적 디렉터리를 읽고 쓰는 권한 부여 : 객체권한 | |
| GRANT READ, WRITE ON DIRECTORY pump_dir TO hr; SELECT GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE FROM dba_tab_privs WHERE grantee = 'HR'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE ---------- ---------- ------------------------------ ---------- ---------- HR SYS DBMS_STATS SYS EXECUTE HR SYS PUMP_DIR SYS WRITE HR SYS PUMP_DIR SYS READ |
|
| # 논리적 디렉터리 삭제 | |
| DROP DIRECTORY pump_dir; |
|
■ Data Pump
- 고속 데이터/메타 데이터 이동(export,import)을 위한 유틸리티
- dbms_datapump
- expdp, impdp
| # Export : 특정 테이블 추출 | |
| expdp [user/password] [directory=저장위치] [dumpfile=파일명.dmp] [tables=소유자.테이블명, 소유자.테이블명, ...] [oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.employees,hr.departments,hr.job_history ... Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.employees,hr.departments,hr.job_history Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /home/oracle/data_pump/hr_emp_dept_job.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 23 10:19:50 2024 elapsed 0 00:00:07 [oracle@oracle ~]$ ls /home/oracle/data_pump export.log hr_emp_dept_job.dmp - export.log : 화면에 출력된 진행 Log |
|
| SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 10 DROP TABLE hr.job_history PURGE; |
|
| # Import : 특정 테이블만 가져오기 | |
| impdp [user/password] [directory=파일위치] [dumpfile=파일명.dmp] [tables=소유자.테이블명 , 소유자.테이블명, ...] [oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history ... Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 23 10:25:47 2024 elapsed 0 00:00:03 SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 10 |
|
| TRUNCATE TABLE hr.job_history; SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 0 |
|
| # import : 테이블 있는 상태에서 데이터만 가져오기 | |
| impdp [user/password] [directory=파일위치] [dumpfile=파일명.dmp] [tables=소유자.테이블명] [content=data_only] [oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only ... Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 23 10:28:02 2024 elapsed 0 00:00:02 SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 10 |
|
| DROP TABLE hr.job_history PURGE; |
|
| # import : 테이블구조만 가져오기 | |
| impdp [user/password] [directory=파일위치] [dumpfile=파일명.dmp] [tables=소유자.테이블명] [content=metadata_only] [oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only ... Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 23 10:47:54 2024 elapsed 0 00:00:01 SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 0 # 데이터만 가져오기 [oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only SELECT COUNT(*) FROM hr.job_history; COUNT(*) ---------- 10 |
|
| << HR SESSION >> SQL> conn hr/hr SQL> SELECT * FROM tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- ... 12 rows selected. PURGE RECYCLEBIN; << SYS SESSION >> SQL> conn / as sysdba |
|
| # Export : Schema Level, 특정 개체 제외 | |
| expdp [user/password] [directory=저장위치] [dumpfile=파일명.dmp] [schemas=소유자] [exclude=table:"='제외테이블'"] - schema : 소유자의 모든 객체를 포함 [oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_schema.dmp schemas=hr exclude=table:\"\=\'TEST_20240122\'\" ... Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=pump_dir dumpfile=hr_schema.dmp schemas=hr exclude=table:"='TEST_20240122'" Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 576 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 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/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."COPY_EMP" 16.80 KB 107 rows . . exported "HR"."COUNTRIES" 6.367 KB 25 rows . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "HR"."INSA_EMP" 16.80 KB 107 rows . . exported "HR"."JOBS" 6.992 KB 19 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows . . exported "HR"."LOCATIONS" 8.273 KB 23 rows . . exported "HR"."REGIONS" 5.476 KB 4 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/data_pump/hr_schema.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 23 11:03:01 2024 elapsed 0 00:00:15 |
|
| # Export : Schema Level, 특정 개체 다중 제외 | |
| expdp [user/password] [directory=저장위치] [dumpfile=파일명.dmp] [schemas=소유자] [exclude=table:"in ('테이블명', '테이블명', '...')"] [oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_e_j_schema.dmp schemas=hr exclude=table:\"\in \(\'TEST_20240122\',\'JOBS\',\'REGIONS\'\)\" ... Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=pump_dir dumpfile=hr_e_j_schema.dmp schemas=hr exclude=table:"in ('TEST_20240122','JOBS','REGIONS')" Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 448 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 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/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."COPY_EMP" 16.80 KB 107 rows . . exported "HR"."COUNTRIES" 6.367 KB 25 rows . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "HR"."INSA_EMP" 16.80 KB 107 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows . . exported "HR"."LOCATIONS" 8.273 KB 23 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/data_pump/hr_e_j_schema.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 23 11:08:09 2024 elapsed 0 00:00:11 |
|
| # export : schema에서 테이블만 가져오기 | |
| expdp [user/password] [directory=파일위치] [dumpfile=파일명.dmp] [schemas=소유자] [include=table] - include = 가져올 객체 [oracle@oracle ~]$ expdp system/oracle schemas=hr directory=pump_dir dumpfile=hr_table.dmp include=table ... Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=hr directory=pump_dir dumpfile=hr_table.dmp include=table Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 640 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."COPY_EMP" 16.80 KB 107 rows . . exported "HR"."COUNTRIES" 6.367 KB 25 rows . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "HR"."INSA_EMP" 16.80 KB 107 rows . . exported "HR"."JOBS" 6.992 KB 19 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows . . exported "HR"."LOCATIONS" 8.273 KB 23 rows . . exported "HR"."REGIONS" 5.476 KB 4 rows . . exported "HR"."TEST_20240122" 5.414 KB 1 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/data_pump/hr_table.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 23 11:13:34 2024 elapsed 0 00:00:10 |
|
CREATE USER henry IDENTIFIED BY oracle DEFAULT TABLESPACE USERS QUOTA 10M ON USERS;
GRANT CREATE SESSION TO henry;
SELECT table_name, tablespace_name FROM dba_tables where owner = 'HR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------
COPY_EMP USERS
TEST_20240122 USERS
INSA_EMP USERS
LOCATIONS EXAMPLE
DEPARTMENTS EXAMPLE
JOBS EXAMPLE
EMPLOYEES EXAMPLE
JOB_HISTORY EXAMPLE
COUNTRIES
REGIONS EXAMPLE
>> 헨리는 EXAMPLE 테이블스페이스 권한이없다
# Export : 소유자 및 테이블스페이스 변경(remap)
[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp remap_schema='hr':'henry' remap_tablespace='EXAMPLE':'USERS'
...
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=pump_dir dumpfile=hr_table.dmp remap_schema=hr:henry remap_tablespace=EXAMPLE:USERS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HENRY"."COPY_EMP" 16.80 KB 107 rows
. . imported "HENRY"."COUNTRIES" 6.367 KB 25 rows
. . imported "HENRY"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HENRY"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HENRY"."INSA_EMP" 16.80 KB 107 rows
. . imported "HENRY"."JOBS" 6.992 KB 19 rows
. . imported "HENRY"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HENRY"."LOCATIONS" 8.273 KB 23 rows
. . imported "HENRY"."REGIONS" 5.476 KB 4 rows
. . imported "HENRY"."TEST_20240122" 5.414 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HENRY"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HENRY"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HENRY"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HENRY"."SECURE_EMPLOYEES" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Tue Jan 23 11:24:13 2024 elapsed 0 00:00:03
SQL> conn henry/oracle
Connected.
SELECT table_name, tablespace_name FROM user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------
EMPLOYEES USERS
JOBS USERS
DEPARTMENTS USERS
LOCATIONS USERS
REGIONS USERS
JOB_HISTORY USERS
COPY_EMP USERS
TEST_20240122 USERS
INSA_EMP USERS
COUNTRIES
SELECT * FROM user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
OE HENRY COUNTRIES HENRY REFERENCES NO NO
OE HENRY COUNTRIES HENRY SELECT NO NO
OE HENRY DEPARTMENTS HENRY SELECT NO NO
OE HENRY EMPLOYEES HENRY SELECT NO NO
OE HENRY EMPLOYEES HENRY REFERENCES NO NO
OE HENRY JOBS HENRY SELECT NO NO
OE HENRY JOB_HISTORY HENRY SELECT NO NO
OE HENRY LOCATIONS HENRY SELECT NO NO
OE HENRY LOCATIONS HENRY REFERENCES NO NO
SELECT COUNT(*) FROM hr.employees WHERE department_id = 50 AND job_id = 'ST_MAN';
COUNT(*)
----------
5
# EXPORT : 특정 테이블의 데이터 조건부 추출(query)
[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp tables=hr.employees query=hr.employees:\"where department_id \= 50 and job_id \= \'ST_MAN\'\"
...
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=pump_dir dumpfile=hr_50_st_man.dmp tables=hr.employees query=hr.employees:"where department_id = 50 and job_id = 'ST_MAN'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 9.585 KB 5 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/data_pump/hr_50_st_man.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 23 11:58:34 2024 elapsed 0 00:00:04
CREATE TABLE hr.emp_50_stman AS SELECT * FROM hr.employees WHERE 1 = 2;
SELECT COUNT(*) FROM hr.emp_50_stman;
COUNT(*)
----------
0
# Import : 다른 테이블로 데이터 가져오기(remap)
[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp remap_table=employees:emp_50_stman content=data_only
SELECT COUNT(*) FROM hr.emp_50_stman;
COUNT(*)
----------
5
### listener L01 - TNS 상태 체크
vi listener.ora 확인
[oracle@oracle ~]$ lsnrctl start L01
[oracle@oracle ~]$ lsnrctl status L01
윈도우 : tnsnames.ora 확인
# XE에서 ora11g DB의 system 계정으로 접속
C:/> sqlplus system/oracle@ora11g
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
------------------
ORA11G
SQL> SELECT status, instance_name FROM v$instance;
STATUS INSTANCE_NAME
------------------------ --------------------------------
OPEN ora11g
### XE - system 계정 체크
C:\>sqlplus system/oracle
ORA-28000: the account is locked
SELECT username,account_status FROM dba_users WHERE username='SYSTEM';
USERNAME ACCOUNT_STATUS
------------------------------------------------------------ ----------------------------------------------------------------
SYSTEM LOCKED(TIMED)
ALTER USER system ACCOUNT UNLOCK;
SQL> SELECT username,account_status FROM dba_users WHERE username='SYSTEM';
USERNAME ACCOUNT_STATUS
------------------------------------------------------------ ----------------------------------------------------------------
SYSTEM OPEN
ALTER USER system IDENTIFIED BY oracle;
<< 터미널 XE DB >>
C:\> sqlplus / as sysdba
SQL> SELECT name FROM v$database;
NAME
-----------------------------------------------
XE
SQL> SELECT * FROM dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ------------------------- --------
PUBLIC DBMS_CLRDBLINK ORACLR_CONNECTION_DATA 23/10/05
HR ORA11G_HR HR ora11g 24/01/05
# Public Database Link 생성
SQL> CREATE PUBLIC DATABASE LINK ora11g_link CONNECT TO system IDENTIFIED BY oracle USING 'ora11g';
USING 'ora11g' : 원격접속하려는 서비스명
CONNECT TO system : 계정정보
PUBLIC : 생성자 외 모든 유저가 사용 가능
SQL> SELECT * FROM dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ------------------------- --------
PUBLIC ORA11G_LINK SYSTEM ora11g 24/01/23
PUBLIC DBMS_CLRDBLINK ORACLR_CONNECTION_DATA 23/10/05
HR ORA11G_HR HR ora11g 24/01/05
# Public Database Link 삭제
DROP PUBLIC DATABASE LINK ora11g_link;
DROP PUBLIC DATABASE LINK ora11g_hr;
>> hr 계정에서 삭제 완료
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------- --------------------------------------------------
SYS ORACLECLRDIR C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle/admin/xe/dpdump/ v
SYS XMLDIR C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
SYS ORACLE_OCM_CONFIG_DIR C:\ADE\aime_xe28\oracle/ccr/state
SQL> host ->> OS로 나가기 = Linux에서 !
- XE에서 xe의 system 계정으로 진행
- chemas=hr network_link=ORA11G_LINK -> DB링크를 통한 ora11g db 의 hr schema
# Export : 원격DB에서 특정 소유자의 모든 객체 가져오기(DB Link)
C:\Users> expdp system/oracle directory=DATA_PUMP_DIR dumpfile=hr.dmp schemas=hr network_link=ora11g_link
...
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=DATA_PUMP_DIR dumpfile=hr.dmp schemas=hr network_link=ORA11G_LINK
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 704 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COPY_EMP" 16.80 KB 107 rows
. . exported "HR"."COUNTRIES" 6.273 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."EMP_50_STMAN" 9.593 KB 5 rows
. . exported "HR"."INSA_EMP" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
. . exported "HR"."TEST_20240122" 5.414 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\HR.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:13:26
C:\oraclexe\app\oracle/admin/xe/dpdump/
진행과정 출력 log 확인 가능
SQL> SELECT status, instance_name FROM v$instance;
STATUS INSTANCE_NAME
------------------------ --------------------------------
OPEN xe
SQL> conn hr/hr
Connected.
SQL> SELECT * FROM tab;
SQL> SELECT table_name, tablespace_name FROM user_tables;
36 rows selected.
> 테이블 전부 users 테이블스페이스
DROP TABLE hr.job_history PURGE;
SELECT COUNT(*) FROM hr.job_history;
ORA-00942: table or view does not exist
> host
- dump 파일 > example 테이블스페이스로 되어있다
# Import : 테이블스페이스 변경, 권한제외
C:\Users> impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp tables=hr.job_history remap_tablespace=example:users exclude=grant
...
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=hr.dmp tables=hr.job_history remap_tablespace=example:users exclude=grant
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 15:15:46
SQL> SELECT COUNT(*) FROM hr.job_history;
COUNT(*)
----------
10
conn / as sysdba
SELECT name FROM v$database;
NAME
------------------
XE
SQL> SELECT tablespace_name, file_name FROM dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
SYSAUX C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
UNDOTBS1 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
SYSTEM C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
CREATE USER james IDENTIFIED BY oracle DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO james;
# import : 모든 객체의 소유자 및 테이블스페이스 변경, 권한제외
C:\Users> impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp remap_schema=hr:james remap_tablespace=example:users exclude=grant
...
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=hr.dmp remap_schema=hr:james remap_tablespace=example:users exclude=grant
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JAMES" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JAMES"."COPY_EMP" 16.80 KB 107 rows
. . imported "JAMES"."COUNTRIES" 6.273 KB 25 rows
. . imported "JAMES"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "JAMES"."EMPLOYEES" 16.80 KB 107 rows
. . imported "JAMES"."EMP_50_STMAN" 9.593 KB 5 rows
. . imported "JAMES"."INSA_EMP" 16.80 KB 107 rows
. . imported "JAMES"."JOBS" 6.992 KB 19 rows
. . imported "JAMES"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "JAMES"."LOCATIONS" 8.273 KB 23 rows
. . imported "JAMES"."REGIONS" 5.476 KB 4 rows
. . imported "JAMES"."TEST_20240122" 5.414 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 15:18:01
conn james/oracle
SELECT * FROM tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
COPY_EMP TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_50_STMAN TABLE
EMP_DETAILS_VIEW VIEW
INSA_EMP TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
TEST_20240122 TABLE
# ora11g 데이터베이스 있는 henry 유저가 소유한 테이블을 XE db의 henry 유저로 import하기
<< ora11g >>
SELECT table_name, tablespace_name FROM dba_tabls WHERE ornwer = 'HR';
<< XE sys >>
CREATE USER henry IDENTIFIED BY oracle DEFAULT TABLESPACE USERS unlimited on users;
GRANT CREATE SESSION TO henry;
> host
# Import : 외부 DB에서 동일한 유저명의 모든 객체를 가져오기. 권한, 트리거 제외(DB Link)
import system/oracle network_link=ora11g_link schemas=henry exclude=grant,trigger
conn henry/oracle
SELECT * FROM tab;
_USER : 접속한 유저
_CONNECT_IDENTIFIER : 접속한 db 정보
# session
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
as is -> to be
(현재) -> (신규)
legacy db ->
DROP USER hrm CASCADE;
==============================================================================================================
1. tablespace 생성한 후 확인 하세요.
tablespace 이름 : hrm_tbs
datafile 위치 및 이름 : /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
datafile 사이즈 : 10m
datafile 자동 확장 활성화
extent 관리 : local uniform size 1m
segment space management: auto
CREATE TABLESPACE hrm_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf'
SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- --------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1237662
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1237662
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1237662
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1237662
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1237662
6 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf HRM_TBS ONLINE 2137204
SELECT * FROM dba_tablespaces WHERE = 'TABLESPACE_NAME';
2. user 생성하세요.
user 이름 : hrm
user 비밀번호 : oracle
default tablespace : hrm_tbs
temporary tablespace : temp
default tablespace quota : unlimited
CREATE USER hrm IDENTIFIED BY oracle
DEFAULT TABLESPACE hrm_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON hrm_tbs;
SELECT username,account_status,default_tablespace,temporary_tablespace,PROFILE
FROM dba_users WHERE username = 'HRM';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
---------- -------------------------------- ------------------------------ ------------------------------ ------------------------------
HRM OPEN HRM_TBS TEMP DEFAULT
3. hrm 유저에게 시스템 권한 부여한 후 확인 하세요.
시스템 권한 : create session, create table, create view, create procedure
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO hrm;
SELECT * FROM dba_sys_privs WHERE grantee = 'HRM';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HRM CREATE TABLE NO
HRM CREATE PROCEDURE NO
HRM CREATE SESSION NO
HRM CREATE VIEW NO
4. /home/oracle/hr_pump 물리적인 디렉토리를 생성 한 후 dump file이 생성될 수 있는 물지적인 home/oracle/hr_pump
위치에 따른 논리적인 디렉토리는 hr_dir 이름으로 생성한 후 확인 하세요.
[oracle@oracle ~]$ mkdir /home/oracle/hr_pump
create directory hr_dir as '/home/oracle/hr_pump';
SELECT * FROM dba_directories WHERE directory_name = 'HR_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS HR_DIR /home/oracle/hr_pump
5. hr_dir 논리적인 디렉토리에 대한 read, write 권한은 hr에게 부여 한 후 확인해주세요.
GRANT READ,WRITE ON DIRECTORY hr_dir TO hr;
SELECT grantee,owner,table_name,grantor,privilege
FROM dba_tab_privs WHERE grantee = 'HR';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
HR SYS DBMS_STATS SYS EXECUTE
HR SYS PUMP_DIR SYS WRITE
HR SYS PUMP_DIR SYS READ
HR SYS HR_DIR SYS WRITE
HR SYS HR_DIR SYS READ
6. data pump를 이용해서 hr유저가 소유한 모든 object를 hr_dir 디렉토리에 hr_object.dump 이름으로 생성해 주세요.
[oracle@oracle ~]$ expdp system/oracle directory=hr_dir dumpfile=hr_object.dump schemas=hr
[oracle@oracle ~]$ cd /home/oracle/hr_pump
[oracle@oracle hr_pump]$ ls
export.log hr_object.demp
7. hr_object.dump에 있는 employees 테이블에 대해서 hrm유저에게 import에 해주세요
단 trigger, grant, constraint, ref_constraint, index는 제외 시켜 주세요.
SELECT owner,table_name,tablespace_name FROM dba_tables WHERE owner = 'HR' AND table_name = 'EMPLOYEES';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
HR EMPLOYEES EXAMPLE
SELECT username,default_tablespace FROM dba_users WHERE username IN ('HRM','HR');
USERNAME DEFAULT_TABLESPACE
---------- ------------------------------
HR USERS
HRM HRM_TBS
[oracle@oracle ~]$
impdp system/oracle directory=hr_dir dumpfile=hr_object.dump remap_schema=hr:hrm
remap_tablespace=example:hrm_tbs tables=hr.employees exclude=trigger,grant,constraint,index
SQL> conn hrm/oracle
SQL> SELECT COUNT(*) FROM hrm.employees;
COUNT(*)
----------
107
SELECT * FROM dba_constraints WHERE owner = 'HRM' AND table_name = 'EMPLOYEES';
> not null 제약조건은 포함되어있다? -> 열단위라서
8. hr_object.dump에 있는 departments, locations 테이블에 대해서 hrm유저에게 import에 해주세요
단 trigger, grant, constraint, ref_constraint, index는 제외 시켜 주세요.
SELECT owner,table_name,tablespace_name FROM dba_tables
WHERE owner = 'HR' AND table_name IN ('DEPARTMENTS','LOCATIONS');
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
HR LOCATIONS EXAMPLE
HR DEPARTMENTS EXAMPLE
SELECT username,default_tablespace FROM dba_users WHERE username IN ('HRM','HR');
USERNAME DEFAULT_TABLESPACE
---------- ------------------------------
HR USERS
HRM HRM_TBS
[oracle@oracle ~]$
impdp system/oracle directory=hr_dir dumpfile=hr_object.dump remap_schema='hr':'hrm'
remap_tablespace='example':'hrm_tbs' tables=hr.departments,hr.locations exclude=trigger,grant,constraint,ref_constraint,index
오브젝트 확인하기
SELECT * FROM dba_tabs;
9. hrm.employees employee_id 컬럼에 primary key 생성하세요. 제약조건 이름은 hrm_emp_id_pk로 생성하세요.
ALTER TABLE hrm.employees ADD CONSTRAINT hrm_emp_id_pk PRIMARY KEY(employee_id);
10. hrm.departments department_id 컬럼에 primary key 생성하세요. 제약조건 이름은 hrm_dept_id_pk로 생성하세요.
ALTER TABLE hrm.departments ADD CONSTRAINT hrm_dept_id_pk PRIMARY KEY(department_id);
11. hrm.employees department_id에 foreign key를 생성하세요. references는 departments department_id를 참조하세요.
제약조건 이름은 emp_dept_id_fk로 생성하세요.
ALTER TABLE hrm.employees ADD CONSTRAINT emp_dept_id_fk FOREIGN KEY(department_id)
REFERENCES hrm.departments(department_id);
SELECT constraint_name,constraint_type,search_condition,r_constraint_name,status,validated,index_name
FROM dba_constraints WHERE owner = 'HRM';
SELECT * FROM dba_constraints WHERE owner = 'HRM';
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 73 Backup&Recovery, RMAN (0) | 2024.01.25 |
|---|---|
| 72 Backup&Recovery, RMAN (0) | 2024.01.24 |
| 70 Backup&Recovery, Log Miner, Clone DB, Export & Import (0) | 2024.01.22 |
| 69 Backup&Recovery, Archive Log Mode (0) | 2024.01.19 |
| 68 Backup&Recovery, Archive Log Mode, control file 손상 (0) | 2024.01.18 |