Oracle 50일차

by

[원격 expdp / 로컬 impdp]

db1의 schema를 copydb에서 원격 expdp후 로컬 impdp 진행

1. 유저랑 테이블 생성 – oel7 db1에서

-- 만든 유저가 너무 많아서 좀 삭제함. copydb 쪽도
select * from dba_users;
drop user itwill2 cascade;
drop user itwill3 cascade;
drop user itwill4 cascade;
drop user hhh cascade;

-- 불필요한 tablespace 삭제. copydb 쪽도
select * from dba_tablespaces;
drop tablespace test01 including contents and datafiles;
drop tablespace test02 including contents and datafiles;
drop tablespace test03 including contents and datafiles;

-- 새 유저 생성
create user itwill2 identified by oracle
quota unlimited on users;
grant create session to itwill2;

-- 테이블 생성
create table itwill2.expdp_test4(no number);
insert into itwill2.expdp_test4 values(10);
insert into itwill2.expdp_test4 values(20);
insert into itwill2.expdp_test4 values(30);
commit;

2. 원격 expdp – copy copydb에서

schema mode로

expdp system/oracle@oel7 dumpfile=expdp_test4.dmp logfile=expdp_test4.log directory=datapump schemas=itwill2

** 근데 exp명령어랑 다르게 원격 expdp를 copy에서 입력하면 oel7쪽에 덤프파일이 생긴다.
그래서 tns alias를 사용하는 방식을 사용하지 않는다.

** 따라서 다른 dblink(network_link)를 사용하는 방식을 사용해야 한다.
copydb에서 db1으로 연결해놓은 dblink 이름을 사용해야됨.

expdp system/oracle dumpfile=expdp_test4.dmp logfile=expdp_test4.log directory=datapump schemas=itwill2 NETWORK_LINK=DBLINK_DB1

3. 로컬 impdp

impdp system/oracle dumpfile=expdp_test4.dmp logfile=expdp_test4.log directory=datapump

4. 데이터 조회

select * from itwill2.expdp_test4;

[원격 impdp]

expdp를 하지 않고 os를 거치지 않고 DB수준에서 원격 impdp가 가능하다.
-> dump file을 만들지 않기 때문에 보안수준이 높아진다.

1. 유저랑 테이블 생성 – oel7 db1에서

-- 새 유저 생성
create user itwill3 identified by oracle
quota unlimited on users;

-- 테이블 생성
create table itwill3.impdp_test1(no number);
insert into itwill3.impdp_test1 values(10);
insert into itwill3.impdp_test1 values(20);
insert into itwill3.impdp_test1 values(30);
commit;

2. 원격 impdp – copy쪽에서 진행

copy->db1쪽으로 뚫린 dblink를 이용해서 DB레벨에서 데이터가 바로 들어오는 느낌.

impdp system/oracle logfile=impdp_test1.log directory=datapump network_link=dblink_db1 schemas=itwill3
-- logfile옵션 생략 시 자동생성 : import.log

3. 데이터 조회

select * from itwill3.impdp_test1;

[한쪽에만 존재하는 tablespace 원격 impdp]

** constraints, indexes, 권한만 impdp된다. – 권한의 경우에는 table descript에 명시가 되어있어서 옮겨짐.
** synonyms, view는 같은 해당 테이블스페이스에 저장공간(segment) 할당받지 않는 객체라 impdp가 안됨.

1. tablespace 생성 (test01) – oel7 db1에서

CREATE TABLESPACE test01
DATAFILE '/home/oracle/oradata/db1/test01.dbf' 
SIZE 50M AUTOEXTEND ON MAXSIZE 1G;

2. scott user의 test01 tablespace에 대한 quota 부여

ALTER USER scott QUOTA UNLIMITED ON test01;

3. table 생성 (scott.impdp_test2) 및 데이터 입력

create table scott.impdp_test2(no number, name varchar(10)) tablespace test01;
insert into scott.impdp_test2 values(1, 'aa');
insert into scott.impdp_test2 values(2, 'bb');
insert into scott.impdp_test2 values(3, 'cc');
commit;

4. 기타 오브젝트 생성

-- 1. pk (no) 생성
alter table scott.impdp_test2 add constraint impdp_test2_pk_no primary key (no);

-- 2. not null (name) 생성
ALTER TABLE scott.impdp_test2 MODIFY name NOT NULL;

-- 3. index 생성 (name)
create index scott.impdp_test2 on scott.impdp_test2 (name);

-- 4. view 생성
create view scott.view_impdp_test2
as
select * from scott.impdp_test2;

-- 5. synonym 생성
-- public synonym
create public synonym public_impdp_test2 for scott.impdp_test2 ;
-- private synonym : scott계정에서 수행해야됨.
create synonym private_impdp_test2 for scott.impdp_test2;

5. 권한 부여

grant select on scott.impdp_test2 to hr;

6. 원격 impdp 수행 – copy에서

impdp system/oracle tablespaces=test01 network_link=dblink_db1 directory=datapump
--
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."IMPDP_TEST2" failed to create with error:
ORA-00959: tablespace 'TEST01' does not exist

** copydb쪽에 tablespace가 없어서 아무것도 impdp 안됨.
** 해결방법 2가지
1. test01 tablespace 만들기.
2. remap_tablespace 옵션 사용하기.

impdp system/oracle tablespaces=test01 remap_tablespace=test01:users network_link=dblink_db1 directory=datapump
--
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01":  system/******** tablespaces=test01 remap_tablespace=test01:users network_link=dblink_db1 directory=datapump
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
. . imported "SCOTT"."IMPDP_TEST2"                            3 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at Fri Dec 20 12:53:55 2024 elapsed 0 00:00:15

7. 데이터 조회

select * from scott.impdp_test2;  -- 됨
select * from dba_constraints where table_name='IMPDP_TEST2';  -- 됨
select * from dba_indexes where table_name='IMPDP_TEST2';  -- 됨
select * from dba_synonyms where table_name='IMPDP_TEST2';  -- 안됨
select * from dba_views where view_name='VIEW_IMPDP_TEST2';  -- 안됨
select * from dba_tab_privs where table_name='IMPDP_TEST2';  -- 됨

** constraints, indexes, 권한만 impdp된다. – 권한의 경우에는 table descript에 명시가 되어있어서 옮겨짐.
** synonyms, view는 같은 해당 테이블스페이스에 저장공간(segment) 할당받지 않는 객체라 impdp가 안됨.

** 결론
– exp/imp는 target DB에 적재 대상의 tbs가 없을 경우 테이블 소유자의 default tbs에 저장. 에러X.
– datapump는 target DB에 적재 대상의 tbs가 없을 경우 에러 발생.
해결법 1) target DB에 똑같은 이름의 tbs 생성.
해결법 2) remap_tablespace 옵션을 사용하여 다른 tbs에 적재.

[parallel을 사용한 datapump 적재]

1. parallel 없이 expdp 수행

time expdp system/oracle directory=datapump dumpfile=full.dmp full=y job_name=full
--
real    1m14.057s
user    0m0.027s
sys     0m0.048s

2. job 모니터링

select sid, serial#, target_desc as 작업명,
       sofar as "경과시간(s)",
       totalwork as "총 수행시간(s)"
from v$session_longops
where lower(target_desc) like '%full%';

3. parallel 사용 expdp 수행

time expdp system/oracle directory=datapump dumpfile=full2.dmp full=y job_name=full2 parallel=4
--
real    1m13.382s
user    0m0.043s
sys     0m0.185s

Leave a Comment