PRM DUL ORACLE 誤操作でDROP TABLESPACEしたデータをリカバリする

TABLESPACE SEGMENT_TYPE      COUNT(*)

———- ————— ———-

USERS      TABLE                  126

USERS      INDEX                  136

SQL> select count(*) from obj$;

COUNT(*)

———-

75698

SQL> select current_scn, systimestamp from v$database;

CURRENT_SCN

———–

SYSTIMESTAMP

—————————————————————————

1895940

25-4月 -14 09.18.00.628000 下午 +08:00

SQL> select file_name from dba_data_files where tablespace_name=’USERS';

FILE_NAME

——————————————————————————–

H:\PP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

SQL> drop tablespace users including contents;

テーブルスペースもう削除した

C:\Users\maclean>dir H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

ドライブの巻はentertainment

巻のシリアルナンバーはentertainment

フィルタが見つからない

drop tablespaceした後、TABLESPACEに該当するフィルタがOSで削除されたから。

COUNT(*)

———-

75436

SQL> select count(*) from sys.obj$ as of scn 1895940;

select count(*) from sys.obj$ as of scn 1895940

*

第一行がエラになり

ORA-01555:スナップショットが古すぎて、ロールバックセグメント番号（名は”SYSTEM”)が小さすぎる。

初めてはFLASHBACK QUERYでOBJ$の記録を見つけ出したいが、SYSTEM ROLLBACK SEGMENTを使ったことによって、ORA-01555になる。

この時にAWRビューDBA_HIST_SQL_PLANを使ってください。七天以内でそのテーブルにアクセスしたら実行計画からOBJECT#とOBJECT_NAMEのつながりが得られる。

SQL> desc DBA_HIST_SQL_PLAN

名称                                      是否为空? 类型

名前　　　　　　　　　　　　　　　　　　ブランクかいなか　　タイプ

—————————————– ——– ———————–

DBID                                      NOT NULL NUMBER

SQL_ID                                    NOT NULL VARCHAR2(13)

PLAN_HASH_VALUE                           NOT NULL NUMBER

ID                                        NOT NULL NUMBER

OPERATION                                          VARCHAR2(30)

OPTIONS                                            VARCHAR2(30)

OBJECT_NODE                                        VARCHAR2(128)

OBJECT#                                            NUMBER

OBJECT_OWNER                                       VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(31)

OBJECT_ALIAS                                       VARCHAR2(65)

OBJECT_TYPE                                        VARCHAR2(20)

OPTIMIZER                                          VARCHAR2(20)

PARENT_ID                                          NUMBER

DEPTH                                              NUMBER

POSITION                                           NUMBER

SEARCH_COLUMNS                                     NUMBER

COST                                               NUMBER

CARDINALITY                                        NUMBER

BYTES                                              NUMBER

OTHER_TAG                                          VARCHAR2(35)

PARTITION_START                                    VARCHAR2(64)

PARTITION_STOP                                     VARCHAR2(64)

PARTITION_ID                                       NUMBER

OTHER                                              VARCHAR2(4000)

DISTRIBUTION                                       VARCHAR2(20)

CPU_COST                                           NUMBER

IO_COST                                            NUMBER

TEMP_SPACE                                         NUMBER

ACCESS_PREDICATES                                  VARCHAR2(4000)

FILTER_PREDICATES                                  VARCHAR2(4000)

PROJECTION                                         VARCHAR2(4000)

TIME                                               NUMBER

QBLOCK_NAME                                        VARCHAR2(31)

REMARKS                                            VARCHAR2(4000)

TIMESTAMP                                          DATE

OTHER_XML                                          CLOB

例えば

select object_owner,object_name,object# from DBA_HIST_SQL_PLAN where sql_id=’avwjc02vb10j4′

OBJECT_OWNER         OBJECT_NAME                                 OBJECT#

——————– —————————————- ———-

PARNASSUSDATA        TORDERDETAIL_HIS                              78688

Select * from

(select object_name,object# from DBA_HIST_SQL_PLAN

UNION select object_name,object# from GV$SQL_PLAN) V1 where V1.OBJECT# IS NOT NULL minus select name,obj# from sys.obj$;

select obj#,dataobj#, object_name from WRH$_SEG_STAT_OBJ where object_name not in (select name from sys.obJ$) order by object_name desc;

もう一つの例

SELECT tab1.SQL_ID,

current_obj#,

tab2.sql_text

FROM DBA_HIST_ACTIVE_SESS_HISTORY tab1,

dba_hist_sqltext tab2

WHERE tab1.current_obj# NOT IN

(SELECT obj# FROM sys.obj$

)

AND current_obj#!=-1

AND tab1.sql_id  =tab2.sql_id(+);