一.环境介绍

目前是用户有几个大的log表需要做归档，最大的一个表大概4亿笔数据，打算只保留最近180天的数据，其他的历史数据迁移至归档库

我这里给出的方案是如下

1.以CTAS创建一个新的log_new表，这个表只保留最近180天的数据。

2.expdp 或者dblink的方式将这个大表迁移到归档库

3.truncate log，然后drop log表

4.将log_new rename to log 

5.参照原表创建索引

以上流程有问题吗？是不是觉得这样切一下 就实现了历史数据归档，相比delete 数据，有明显的优势，速度快，归档产生少，迁移用时少，需要的停机窗口短

但是,不要盲目自信，这样做归档切换需要注意如下几个问题

二.可能忽略的几个关键问题

2.1  CTAS 不会自动复制索引

解决方法：重新创建索引

-- 查询原表索引
SELECT index_name, column_name FROM all_ind_columns WHERE table_name = 'TABLE1';

-- 示例：手动创建索引（按原索引重建）
CREATE INDEX idx_table1_col1 ON table1(col1);

2.2 CTAS 不会复制约束（主键、唯一键、外键、检查约束等）

解决方法：查看并重建原表约束

-- 查询原表约束（除外键）
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'TABLE1' AND constraint_type IN ('P', 'U', 'C');

-- 示例：重建主键
ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY (id);

2.3  CTAS 不会复制栏位默认值

解决方法：查询原表默认值，手动添加

-- 查询默认值定义
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'TABLE1' AND data_default IS NOT NULL;

-- 示例：添加默认值
ALTER TABLE table1 MODIFY status DEFAULT 'N';

2.4  如果有使用 sequence 的自增列，需手动处理

解决方法：

• 检查是否有 sequence

• 检查触发器是否使用了 table1

-- 检查 sequence
SELECT sequence_name FROM user_sequences;

-- 示例：重新创建 trigger
CREATE OR REPLACE TRIGGER trg_table1_id
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  :new.id := seq_table1_id.NEXTVAL;
END;

oracle 12C后可以直接使用sequence.nextval 而不需要依赖trigger

ALTER TABLE table1  MODIFY (id DEFAULT sequence1.NEXTVAL);

2.5  检查是否有 trigger、view、procedure、function 使用旧表名

解决方法：
旧表名的对象

SELECT name, type 
FROM all_source 
WHERE UPPER(text) LIKE '%TABLE1%';

-- 重新编译依赖对象
ALTER PROCEDURE proc_name COMPILE;
ALTER VIEW view_name COMPILE;

2.6  收集统计信息，避免执行计划偏移

解决方法：

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TABLE1');

2.7 表注释、字段注释不会自动复制

-- 查询原表注释
SELECT * FROM user_tab_comments WHERE table_name = 'TABLE1';
SELECT * FROM user_col_comments WHERE table_name = 'TABLE1';

-- 示例：添加注释
COMMENT ON TABLE table1 IS '这是归档后的 table1';
COMMENT ON COLUMN table1.date_column IS '创建日期';

2.8 表分区信息不会复制（如果 table1 是分区表）

CTAS 创建的是非分区表，需要显式指定分区结构：

-- 示例：如果需要创建分区表（例如按月分区）
CREATE TABLE table2 (
  id NUMBER,
  name VARCHAR2(100),
  date_column DATE
)
PARTITION BY RANGE (date_column)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD'))
)
AS SELECT * FROM table1 WHERE date_column > SYSDATE - 180;

2.9 查询all_source 找到使用到该表的OBJ

确认找到使用到该表的所有对象 如PROCEDURE,PACKAGE,function 检查这些obj的状态，因为涉及到DDL操作后，可能会有对象状态为invalid；

 SELECT * FROM  ALL_source WHERE lower(text) LIKE '%tablename%';

如果出现invalid状态 需要重新编译,或者直接结束后跑一遍utlrp.sql

@?/rdbms/admin/utlrp.sql

 总结建议

在执行 CTAS + DROP + RENAME 的归档方案后，请务必按照下列顺序：

1. 重新建索引

2. 补回约束

3. 设置默认值

4. 重新配置 sequence 和 trigger

5. 检查依赖对象（如存储过程、视图、触发器）

6. 收集统计信息

7. 补回注释、分区等元数据
   

8. 重新编译invalid对象

看似简单的归档迁移，如果是使用ctas+rename来实现，在时间上确实会比delete+shrink要快很多，停机窗口也会比较短，
但是因为涉及到较多的ddl操作，以上的这些注意点，还是要谨慎，稍有遗漏都有可能造成应用故障。