Oracle datapump export và import dữ liệu expdp impdp

— DATAPUMP: export và import dữ liệu…

— kiểm tra thông tin hiện tại có tiến trình impdp expdp nào đang chạy không

ps -ef |grep expdp

ps -ef |grep impdp

— kiểm tra thông tin trong db có job dump nào đang chạy không

select * from DBA_DATAPUMP_JOBS;

— kiểm tra session

select * from dba_datapump_sessions;

— để export/import được thì chúng ta phải tạo đường dẫn trên os và db dùng để chứa file dump khi exp hoặc import

mkdir /home/oracle/for_dump

— vào db kiểm tra xem có đường dẫn chưa

sqlplus / as sysdba

select DIRECTORY_NAME, directory_path from dba_directories;

— tạo đường dẫn mới

create directory for_dump as '/home/oracle/for_dump';

select DIRECTORY_NAME, directory_path from dba_directories where directory_name='FOR_DUMP';

— Cấu trúc export dữ liệu

expdp "'username/password@connect_string'"
DIRECTORY=directory_object
DUMPFILE=dumpfile_name_%U.dmp
LOGFILE=log_file_name.log
FILESIZE=1G [kích thước file tối đa, nếu dung lượng lớn hơn hệ thống sẽ tách thành file mới, yêu cầu là tên dumpfile phải có tham số %U.dmp nếu không sẽ lỗi]
COMPRESSION=ALL [DATA_ONLY, [METADATA_ONLY] and NONE]
TABLESPACES=users
SCHEMAS=schema_name
TABLES=table_name
TABLES=table_name:PARTITION
QUERY=your_query
CONTENT=DATA_ONLY hoặc CONTENT=ALL hoặc METADATA_ONLY
FULL=y (export 100% DB)
table_exists_action=replace (nếu bảng tồn tại)
EXCLUDE=STATISTICS (ko export statis)
EXCLUDE=INDEX (ko export index)

— Ghi chú khi Export

— Chạy Gather dictionary trước khi export

— Tiến trình export cần truy vấn các thông tin về cấu trúc của đối tượng export (VD: Các cột của table, các index, constraint liên quan, số dòng dữ liệu…). Các thông tin này sẽ được lưu tại data dictionary. Do đó, chúng ta cần gather lại dictionary để giúp export chạy nhanh hơn.

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (NULL);

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

— Cấu trúc impdp dữ liệu

impdp "'username/password@connect_string'"
DIRECTORY=directory_object
DUMPFILE=dumpfile_name_%U.dmp
LOGFILE=log_file_name.log
FILESIZE=1G [kích thước file tối đa, nếu dung lượng lớn hơn hệ thống sẽ tách thành file mới, yêu cầu là tên dumpfile phải có tham số %U.dmp nếu không sẽ lỗi]
TABLESPACES=users
SCHEMAS=schema_name
TABLES=table_name
TABLES=table_name:PARTITION
table_exists_action=replace (nếu bảng tồn tại)
REMAP_TABLE=c##duong.tab:tabnew; (đổi tên table)

— Ví dụ cụ thể:

— Export 1 schema: duong

— Trước khi thực hiện bằng user nào thì gán quyền cho user đó mới exp được, nếu sys thì đã có sẵn full quyền

GRANT EXP_FULL_DATABASE TO duong;
select count(*) from duong.EMPLOYEES;
expdp "'duong/oracle'" DIRECTORY=for_dump DUMPFILE=duong_%U.dmp logfile=duong_schema.log SCHEMAS=duong COMPRESSION=ALL CONTENT=ALL PARALLEL=10;

— tên file dump thêm %U để chia nhỏ file theo parallel

— login vao user schema hoặc là user sys

— Export 1 TABLESPACE: users

expdp "' sys/oracle as sysdba'" DIRECTORY=for_dump DUMPFILE=duong_tbs_%U.dmp logfile=duong_tab.log TABLESPACE=users COMPRESSION=ALL CONTENT=ALL PARALLEL=10;

— nếu có nhiều tbs thì cách nhau bởi dấu ,

— Export 1 table: duong.tab22

expdp "' sys/oracle as sysdba'" DIRECTORY=for_dump DUMPFILE=duong_table_%U.dmp logfile=duong_table22.log TABLES=duong.table22 COMPRESSION=ALL CONTENT=ALL PARALLEL=10;

— nếu có nhiều bảng thì cách nhau bởi dấu ,

— Export full DB

expdp "' sys/oracle as sysdba'" DIRECTORY=for_dump DUMPFILE=fulldb_%U.dmp logfile=fuldb.log COMPRESSION=ALL CONTENT=ALL FULL=y PARALLEL=10;

— Chỉ tính toán dung lượng sau khi export mà không thực sự exp

expdp "'/ as sysdba'" ESTIMATE_ONLY=y TABLES=duong.tab22 COMPRESSION=ALL PARALLEL=20;

expdp "'/ as sysdba'" ESTIMATE_ONLY=y FULL=Y COMPRESSION=ALL PARALLEL=20;

— kiểm tra dung lượng datafile và so sánh với dumpfile export xong xem có khác biệt nhiều không

select sum(bytes)/1024/1024 "MB" from dba_data_files;

— Xoá bảng đi và thực hiện import từ file

drop table duong.table22;

select * from dba_recyclebin;

purge dba_recyclebin;

— thực hiện khôi phục nhung ko dc vì đã purge rồi, data ko còn trong recyclebin nữa

flashback table duong.table22 to before drop;

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

— Dùng dữ liệu export bên trên để import lại

impdp "' / as sysdba'" DIRECTORY=for_dump DUMPFILE=duong_table_%U.dmp logfile=duong_table22.log TABLES=duong.table22;

— Import và đổi tên bảng

impdp "' sys/oracle as sysdba'" DIRECTORY=for_dump DUMPFILE=duong_table_%U.dmp logfile=duong_table22.log TABLES=duong.table22 REMAP_TABLE=duong.table22:table22new;

— Check dữ liệu sau khi impdp

select count(*) from duong.table22;

select count(*) from duong.table22new;