EBS_DBA_问题:关于不小心drop了表,进行恢复

问题起因:

想迁移一张表到测试环境,结果导入的时候没看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;

 完工