oracle 还原被覆盖的视图

1.现在的视图

 select to_lob(text) from SYS.DBA_views where view_name='XXX';

2.查旧数据

--as of timestamp to_date('2024-05-28 10:30:00','yyyy-mm-dd hh24:mi:ss')

 select to_lob(text) from SYS.DBA_views as of timestamp to_date('2024-05-28 10:30:00','yyyy-mm-dd hh24:mi:ss') where view_name='XXX';

3.long字段

text是long字段，用to_lob(text)转成clob

4.建新表存数据或入库某张表

--记得表前加模式/用户名 
create table a1.test_a_20240528 as select to_lob(text) text from SYS.DBA_views as of timestamp to_date('2024-05-28 10:30:00','yyyy-mm-dd hh24:mi:ss') where view_name='XXX';
 
 
 
insert into a1.test_a_20240528  select to_lob(text) from SYS.DBA_views as of timestamp to_date('2024-05-28 10:30:00','yyyy-mm-dd hh24:mi:ss') where view_name='XXX';
--用入库记得提交
commit;