问题起因:
想迁移一张表到测试环境,结果导入的时候没看sid导致导入的时候,将生产环境的表drop了.
于是进行恢复:
1.首先在回收站看看在不在了
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;
2.利用flashback进行一下恢复(前提是要打开回收站,怎么打开下一篇帖子写一下)
FLASHBACK TABLE "BIN$PYvfBIbp6+/gU2AKCgoO+w==$0" TO BEFORE DROP RENAME TO CUX_HRSC_HEADERS_T_1;
在这之前先看看原来的表在不在了.在的话删除了,免得重建索引的时候不方便.
表曾经删除过,后来又将其闪回了。原来Oracle在做表格闪回时,默认将其索引也闪回,但是名字还保留回收站里的名字。 于是可以用以下语法将索引名改名:
先查看原来索引的名字:
Select Object_Name,
Original_Name,
Type
from User_Recyclebin
where Base_Object In (Select Base_Object
From User_Recyclebin
where Original_Name = 'CUX_HRSC_HEADERS_T')
and Original_Name != 'CUX_HRSC_HEADERS_T';
SQL>
alter index "BIN$PYvfBIbn6+/gU2AKCgoO+w==$0" rename to CUX_HRSC_HEADERS_PK;
alter index "BIN$PYvfBIbo6+/gU2AKCgoO+w==$0" rename to CUX_HRSC_HEADERS_U1;
最好重建一下索引:
alter index CUX_HRSC_HEADERS_PK REBUILD tablespace CUX_IDX;
alter index CUX_HRSC_HEADERS_U1 REBUILD tablespace CUX_IDX;
ps.索引重命名
ALTER INDEX indx1 RENAME TO CUX_HRSC_HEADERS_PK;
----------
重命名键:
alter table CUX_HRSC_HEADERS_T rename constraint "BIN$PYvfBIbl6+/gU2AKCgoO+w==$0" to CUX_HRSC_HEADERS_PK;
alter table CUX_HRSC_HEADERS_T rename constraint "BIN$PYvfBIbl6+/gU2AKCgoO+w==$0" to CUX_HRSC_HEADERS_PK;
删掉检查:
-- Drop check constraints
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbg6+/gU2AKCgoO+w==$0";
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbh6+/gU2AKCgoO+w==$0";
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbi6+/gU2AKCgoO+w==$0";
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbj6+/gU2AKCgoO+w==$0";
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbk6+/gU2AKCgoO+w==$0";
alter table CUX_HRSC_HEADERS_T drop constraint "BIN$PYvfBIbf6+/gU2AKCgoO+w==$0";
-----查看相关信息
select table_name,constraint_name,constraint_type from user_constraints
where table_name='CUX_HRSC_LINES_T'
select * from user_indexes
Where INDEX_NAME = 'CUX_HRPE_MEND_T_PK'
select * from all_constraints
Where CONSTRAINT_NAME = 'CUX_HRPE_MEND_T_PK'
这是恢复后的一种方法,优点:效率快,缺点步骤繁琐
另一种方法,恢复之后重建表,然后重建索引.
Create Table CUX_HRSC_HEADERS_T As
Select * from
CUX_HRSC_HEADERS_T_1--恢复的表名
tablespace CUX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
);
重建索引和键
-- Create/Recreate primary, unique and foreign key constraints
alter table CUX_HRSC_HEADERS_T
add constraint CUX_HRSC_HEADERS_PK primary key (HEADER_ID)
using index
tablespace CUX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table CUX_HRSC_HEADERS_T
add constraint CUX_HRSC_HEADERS_U1 unique (CARD_YEAR, CARD_PERIOD, CARD_TYPE, ORGANIZATION_ID, DEPARTMENT_ID)
using index
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
最好重建一下索引:
alter index CUX_HRSC_HEADERS_PK REBUILD tablespace CUX_IDX;
alter index CUX_HRSC_HEADERS_U1 REBUILD tablespace CUX_IDX;
完工