Data Pump_expdp, impdp

# data pump
<sys sess>

select * from dba_dircetories;

OWNER                DIRECTORY_NAME         DIRECTORY_PATH
-------------------- ---------------------- ---------------------------------------------
SYS                  HR_DIR                 /home/oracle1/hr_pump
SYS                  SUBDIR                 /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/order_entry//2002/Sep
SYS                  SS_OE_XMLDIR           /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/order_entry/
SYS                  PUMP_DIR               /home/oracle1/data_pump
SYS                  LOG_FILE_DIR           /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/log/
SYS                  MEDIA_DIR              /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/product_media/
SYS                  DATA_FILE_DIR          /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/sales_history/
SYS                  XMLDIR                 /u01/app/oracle/product/11.2.0.4/db_1/rdbms/xml
SYS                  ORACLE_OCM_CONFIG_DIR  /u01/app/oracle/product/11.2.0.4/db_1/ccr/hosts/oracle/state
SYS                  DATA_PUMP_DIR          /u01/app/oracle/admin/ora11g/dpdump/ ★
SYS                  ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.4/db_1/ccr/state

/u01/app/oracle/admin/ora11g/dpdump/ : 여기에 저장된다.

#) data pump 만들기

SYS@ora11g> !
[oracle1@oracle ~]$ expdp system/oracle directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump

  /u01/app/oracle/admin/ora11g/dpdump/hr_sal_emp.pump <- 생성 확인

#) 생성 확인
[oracle1@oracle ~]$ ls -l /u01/app/oracle/admin/ora11g/dpdump/hr_sal_emp.pump

#) oracle 접속(sys sess)

drop table hr.sal_emp purge;

=> Table dropped.

#) 삭제 되어있는지 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len 
from dba_tab_partitions 
where table_name = 'SAL_EMP';

=> no rows selected

#) os 나오기
SYS@ora11g> !
[oracle1@oracle ~]$  impdp system/oracle directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump

#) oracle 접속 

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len 
from dba_tab_partitions 
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE TABLESPACE_NAME                  NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ---------- ------------------------------ ---------- ---------- -----------
P1              5000       USERS                                  49         19          17
P2              15000      USERS                                  55         19          17
P3              20000      USERS                                   2         46          19
P4              30000      USERS                                   2         19          17
PMAX            MAXVALUE   USERS                                   1         19          16

#) 확인

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

 

---

#) 삭제

alter table hr.sal_emp drop partition(p4);

=> Table altered.

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len 
from dba_tab_partitions 
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE TABLESPACE_NAME                  NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ---------- ------------------------------ ---------- ---------- -----------
P1              5000       USERS                                  49         19          17
P2              15000      USERS                                  55         19          17
P3              20000      USERS                                   2         46          19
PMAX            MAXVALUE   USERS                                   1         19          16

#) p4 확인 

select * from hr.sal_emp partition(p4);

=> 오류발생
=> ORA-02149: Specified partition does not exist

#) import 하기, SQL로 확인해보기.
SYS@ora11g> !
[oracle1@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump sqlfile=sql_emp.sql

#) SQL 스크립트 확인
[oracle1@oracle ~]$  ls -l /u01/app/oracle/admin/ora11g/dpdump/sql_emp.sql

[oracle1@oracle ~]$  vi /u01/app/oracle/admin/ora11g/dpdump/sql_emp.sql

#) p4 만들기 위해 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE TABLESPACE_NAME                  NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ---------- ------------------------------ ---------- ---------- -----------
P1              5000       USERS                                  49         19          17
P2              15000      USERS                                  55         19          17
P3              20000      USERS                                   2         46          19
PMAX            MAXVALUE   USERS                                   1         19          16

#) p4 생성 후 확인

alter table hr.sal_emp split partition pmax at (30000) into (partition p4, partition pmax);

=> Table altered.

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE TABLESPACE_NAME                  NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ---------- ------------------------------ ---------- ---------- -----------
P1              5000       USERS                                  49         19          17
P2              15000      USERS                                  55         19          17
P3              20000      USERS                                   2         46          19
P4              30000      USERS
PMAX            MAXVALUE   USERS                                   1         19          16

 

 

select * from hr.sal_emp partition(p4);

=> no rows selected

#) os로 나와서 import 하기
[oracle1@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p4 content=data_only

#) p4 확인: data import 완료

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

---

#) p3 확인

select * from hr.sal_emp partition(p3);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
        102 De Haan                        17000            90

#) p3 삭제

alter table hr.sal_emp truncate partition p3;

=> Table truncated.

select * from hr.sal_emp partition(p3);

=> no rows selected

#) 해결방안
SYS@ora11g> !
[oracle1@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p3 content=data_only

#) 복구 완료

select * from hr.sal_emp partition(p3);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
        102 De Haan                        17000            90

 

---

# 특정 partition table 통계수집
#) 통계 수집: partname=>'p4', granularity=>'partition' 지정해주기

exec dbms_stats.gather_table_stats('hr', 'sal_emp', partname=>'p4', granularity=>'partition')

=> PL/SQL procedure successfully completed.

#) 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE TABLESPACE_NAME                  NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ---------- ------------------------------ ---------- ---------- -----------
P1              5000       USERS                                  49         19          17
P2              15000      USERS                                  55         19          17
P3              20000      USERS                                   2         46          19
P4              30000      USERS                                   2         19          17
PMAX            MAXVALUE   USERS                                   1         19          16

# 특정 partition만 export 
SYS@ora11g> !
[oracle1@oracle ~]$ expdp system/oracle directory=data_pump_dir tables=hr.sal_emp:p4 dumpfile=hr_sal_emp_p4.pump

#) 확인
[oracle1@oracle ~]$ ls -l /u01/app/oracle/admin/ora11g/dpdump/hr_sal_emp_p4.pump

#) oracle 접속

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

#) P4 truncate 하기

alter table hr.sal_emp truncate partition p4;

=> Table truncated.

#) P4 확인하기 

select * from hr.sal_emp partition(p4);

=> no rows selected

#) import 하기
SYS@ora11g> !
[oracle1@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp_p4.pump tables=hr.sal_emp:p4 content=data_only

#) p4 확인

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20