Oracle 傻瓜式数据归档

时间：2023-12-29 17:40:32

推荐用方法二

方法一 【本库备份，分区表需要另写CREATE TABLE方法】

-----------------------------------------------

CREATE TABLE PH_PRESCRIPTION_T AS SELECT * FROM PH_PRESCRIPTION WHERE 1=0;

CREATE TABLE PH_PRESCRIPTION_ITEM_T AS SELECT * FROM PH_PRESCRIPTION_ITEM WHERE 1=0;

CREATE TABLE PH_AUDIT_PRESCRIPTION_T AS SELECT * FROM PH_AUDIT_PRESCRIPTION WHERE 1=0;

CREATE TABLE PH_AUDIT_PRESCRIPTION_ITEM_T AS SELECT * FROM PH_AUDIT_PRESCRIPTION_ITEM WHERE 1=0;

RENAME  PH_PRESCRIPTION TO PH_PRESCRIPTION_19;

RENAME  PH_PRESCRIPTION_T TO PH_PRESCRIPTION;

RENAME  PH_PRESCRIPTION_ITEM TO PH_PRESCRIPTION_ITEM_19;

RENAME  PH_PRESCRIPTION_ITEM_T TO PH_PRESCRIPTION_ITEM;

RENAME  PH_AUDIT_PRESCRIPTION TO PH_AUDIT_PRESCRIPTION_19;

RENAME  PH_AUDIT_PRESCRIPTION_T TO PH_AUDIT_PRESCRIPTION;

RENAME  PH_AUDIT_PRESCRIPTION_ITEM TO PH_AUDIT_PRESCRIPTION_ITEM_19;

RENAME  PH_AUDIT_PRESCRIPTION_ITEM_T TO PH_AUDIT_PRESCRIPTION_ITEM;

============================================================

方法二 先操作方法一，否则因为 数据量大，CREATE TABLE 时间长，造成数据丢失

-----------------------------------------------

CREATE TABLE PHARMACY_ARCHIVE.PH_PRESCRIPTION_19 AS SELECT * FROM PHARMACY.PH_PRESCRIPTION_19;

CREATE TABLE PHARMACY_ARCHIVE.PH_PRESCRIPTION_ITEM_19 AS SELECT * FROM PHARMACY.PH_PRESCRIPTION_ITEM_19;

CREATE TABLE PHARMACY_ARCHIVE.PH_AUDIT_PRESCRIPTION_19 AS SELECT * FROM PHARMACY.PH_AUDIT_PRESCRIPTION_19;

CREATE TABLE PHARMACY_ARCHIVE.PH_AUDIT_PRESCRIPTION_ITEM_19 AS SELECT * FROM PHARMACY.PH_AUDIT_PRESCRIPTION_ITEM_19;

DROP TABLE PHARMACY.PH_PRESCRIPTION_19;

DROP TABLE PHARMACY.PH_PRESCRIPTION_ITEM_19;

DROP TABLE PHARMACY.PH_AUDIT_PRESCRIPTION_19;

DROP TABLE PHARMACY.PH_AUDIT_PRESCRIPTION_ITEM_19;

============================================================

方法三，原理等同于方法二，性能高于方法二。再用 导出、导入 方法

-----------------------------------------------

expdp pharmacy_archive/pharmacy_archive directory=dump_dir dumpfile=LH_BAK_20190228.dmp tables=PH_PRESCRIPTION_19,PH_PRESCRIPTION_ITEM_19,PH_AUDIT_PRESCRIPTION_19,PH_AUDIT_PRESCRIPTION_ITEM_19 logfile=EXP_LH_BAK_20190228.log compression=all

impdp 导入

DROP TABLE PHARMACY.PH_PRESCRIPTION_19;

DROP TABLE PHARMACY.PH_PRESCRIPTION_ITEM_19;

DROP TABLE PHARMACY.PH_AUDIT_PRESCRIPTION_19;

DROP TABLE PHARMACY.PH_AUDIT_PRESCRIPTION_ITEM_19;

相关文章