下面的测试来至于一位朋友，不小心将sys用户下面的所有的sequence全部删除了。下面测试仅限测试环境，如在生产环境，请提前备份。整个实验利用数据库的闪回查询功能使用，其它如果undo中数据不存在，那么我们也可以利用软件来抽取表已经delete的行记录，前提是块中空间没有被覆盖。
1，数据库版本与OS版本
|
www.htz.pw > !lsb_release -a LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch Distributor ID: RedHatEnterpriseAS Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8) Release: 4 Codename: NahantUpdate8
www.htz.pw > select * from v$version where rownum<2;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production |
2，DROP用户SYS序列
|
select ‘drop sequence ‘||sequence_owner||’.’||sequence_name||’;’ from dba_sequences where sequence_owner=’SYS’;
drop sequence SYS.APPLY$_DEST_OBJ_ID; drop sequence SYS.APPLY$_ERROR_HANDLER_SEQUENCE; drop sequence SYS.APPLY$_SOURCE_OBJ_ID; drop sequence SYS.AQ$_ALERT_QT_N; drop sequence SYS.AQ$_AQ$_MEM_MC_N; drop sequence SYS.AQ$_AQ_PROP_TABLE_N; drop sequence SYS.AQ$_CHAINSEQ; drop sequence SYS.AQ$_IOTENQTXID; drop sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N; drop sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_N; drop sequence SYS.AQ$_NONDURSUB_SEQUENCE; drop sequence SYS.AQ$_PROPAGATION_SEQUENCE; drop sequence SYS.AQ$_PUBLISHER_SEQUENCE; drop sequence SYS.AQ$_RULE_SEQUENCE; drop sequence SYS.AQ$_RULE_SET_SEQUENCE; drop sequence SYS.AQ$_SCHEDULER$_EVENT_QTAB_N; drop sequence SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_N; drop sequence SYS.AQ$_SCHEDULER_FILEWATCHER_QT_N; drop sequence SYS.AQ$_SYS$SERVICE_METRICS_TAB_N; drop sequence SYS.AQ$_TRANS_SEQUENCE; drop sequence SYS.AUDSES$; drop sequence SYS.AWCREATE10G_S$; drop sequence SYS.AWCREATE_S$; drop sequence SYS.AWLOGSEQ$; drop sequence SYS.AWMD_S$; drop sequence SYS.AWREPORT_S$; drop sequence SYS.AWSEQ$; drop sequence SYS.AWXML_S$; drop sequence SYS.CACHE_STATS_SEQ_0; drop sequence SYS.CACHE_STATS_SEQ_1; drop sequence SYS.CDC_RSID_SEQ$; drop sequence SYS.CDC_SUBSCRIBE_SEQ$; drop sequence SYS.CHNF$_CLAUSEID_SEQ; drop sequence SYS.CHNF$_QUERYID_SEQ; drop sequence SYS.COMPARISON_SCAN_SEQ$; drop sequence SYS.COMPARISON_SEQ$; drop sequence SYS.CONFLICT_HANDLER_ID_SEQ$; drop sequence SYS.DAM_CLEANUP_SEQ$; drop sequence SYS.DBFS_HS$_ARCHIVEREFIDSEQ; drop sequence SYS.DBFS_HS$_BACKUPFILEIDSEQ; drop sequence SYS.DBFS_HS$_POLICYIDSEQ; drop sequence SYS.DBFS_HS$_RSEQ; drop sequence SYS.DBFS_HS$_STOREIDSEQ; drop sequence SYS.DBFS_HS$_TARBALLSEQ; drop sequence SYS.DBFS_SFS$_FSSEQ; drop sequence SYS.DBMS_CUBE_ADVICE_SEQ$; drop sequence SYS.DBMS_LOCK_ID; drop sequence SYS.DBMS_PARALLEL_EXECUTE_SEQ$; drop sequence SYS.DEPTREE_SEQ; drop sequence SYS.DM$EXPIMP_ID_SEQ; drop sequence SYS.EXPRESS_S$; drop sequence SYS.FGR$_NAMES_S; drop sequence SYS.GENERATOR$_S; drop sequence SYS.GROUP_NUM_SEQ; drop sequence SYS.HS$_BASE_DD_S; drop sequence SYS.HS$_CLASS_CAPS_S; drop sequence SYS.HS$_CLASS_DD_S; drop sequence SYS.HS$_CLASS_INIT_S; drop sequence SYS.HS$_FDS_CLASS_S; drop sequence SYS.HS$_FDS_INST_S; drop sequence SYS.HS$_INST_CAPS_S; drop sequence SYS.HS$_INST_DD_S; drop sequence SYS.HS$_INST_INIT_S; drop sequence SYS.HS_BULK_SEQ; drop sequence SYS.IDGEN1$; drop sequence SYS.IDX_RB$JOBSEQ; drop sequence SYS.INVALIDATION_REG_ID$; drop sequence SYS.JAVA$POLICY$SEQUENCE$; drop sequence SYS.JAVA$PREFS$SEQ$; drop sequence SYS.JOBSEQ; drop sequence SYS.JOBSEQLSBY; drop sequence SYS.LOG$SEQUENCE; drop sequence SYS.MV_RF$JOBSEQ; drop sequence SYS.OBJECT_GRANT; drop sequence SYS.OLAP_ASSIGNMENTS_SEQ; drop sequence SYS.OLAP_ATTRIBUTES_SEQ; drop sequence SYS.OLAP_CALCULATED_MEMBERS_SEQ; drop sequence SYS.OLAP_DIMENSIONALITY_SEQ; drop sequence SYS.OLAP_DIM_LEVELS_SEQ; drop sequence SYS.OLAP_HIERARCHIES_SEQ; drop sequence SYS.OLAP_HIER_LEVELS_SEQ; drop sequence SYS.OLAP_MAPPINGS_SEQ; drop sequence SYS.OLAP_MEASURES_SEQ; drop sequence SYS.OLAP_MODELS_SEQ; drop sequence SYS.ORA_PLAN_ID_SEQ$; drop sequence SYS.ORA_TQ_BASE$; drop sequence SYS.PARTITION_NAME$; drop sequence SYS.PROFNUM$; drop sequence SYS.PSINDEX_SEQ$; drop sequence SYS.REDEF_SEQ$; drop sequence SYS.RGROUPSEQ; drop sequence SYS.SCHEDULER$_EVTSEQ; drop sequence SYS.SCHEDULER$_INSTANCE_S; drop sequence SYS.SCHEDULER$_JOBSUFFIX_S; drop sequence SYS.SCHEDULER$_LWJOB_OID_SEQ; drop sequence SYS.SCHEDULER$_RDB_SEQ; drop sequence SYS.SNAPSHOT_ID$; drop sequence SYS.SNAPSITE_ID$; drop sequence SYS.SQLLOG$_SEQ; drop sequence SYS.SQL_TK_CHK_ID; drop sequence SYS.SSCR_CAP_SEQ$; drop sequence SYS.STREAMS$_APPLY_SPILL_TXNKEY_S; drop sequence SYS.STREAMS$_CAPTURE_INST; drop sequence SYS.STREAMS$_CAP_SUB_INST; drop sequence SYS.STREAMS$_PROPAGATION_SEQNUM; drop sequence SYS.STREAMS$_RULE_NAME_S; drop sequence SYS.STREAMS$_SM_ID; drop sequence SYS.STREAMS$_STMT_HANDLER_SEQ; drop sequence SYS.SYNOPSIS_NUM_SEQ; drop sequence SYS.SYSTEM_GRANT; drop sequence SYS.TSM_MIG_SEQ$; drop sequence SYS.UGROUP_SEQUENCE; drop sequence SYS.UTL_RECOMP_SEQ; drop sequence SYS.WRI$_ADV_SEQ_DIR; drop sequence SYS.WRI$_ADV_SEQ_DIR_INST; drop sequence SYS.WRI$_ADV_SEQ_EXEC; drop sequence SYS.WRI$_ADV_SEQ_JOURNAL; drop sequence SYS.WRI$_ADV_SEQ_MSGGROUP; drop sequence SYS.WRI$_ADV_SEQ_SQLW_QUERY; drop sequence SYS.WRI$_ADV_SEQ_TASK; drop sequence SYS.WRI$_ADV_SQLT_PLAN_SEQ; drop sequence SYS.WRI$_ALERT_SEQUENCE; drop sequence SYS.WRI$_ALERT_THRSLOG_SEQUENCE; drop sequence SYS.WRI$_REPT_COMP_ID_SEQ; drop sequence SYS.WRI$_REPT_FILE_ID_SEQ; drop sequence SYS.WRI$_REPT_FORMAT_ID_SEQ; drop sequence SYS.WRI$_REPT_REPT_ID_SEQ; drop sequence SYS.WRI$_SQLSET_ID_SEQ; drop sequence SYS.WRI$_SQLSET_REF_ID_SEQ; drop sequence SYS.WRI$_SQLSET_STMT_ID_SEQ; drop sequence SYS.WRI$_SQLSET_WORKSPACE_PLAN_SEQ; drop sequence SYS.WRM$_DEEP_PURGE_EXTENT; drop sequence SYS.WRM$_DEEP_PURGE_INTERVAL; drop sequence SYS.WRR$_CAPTURE_ID; drop sequence SYS.WRR$_REPLAY_ID; drop sequence SYS.XSPARAM_REG_SEQUENCE$; |
3，恢复序列
3.1 生成dba_sequences的DDL语句
平时查询序列的时候都是查询dba_sequences这个视图，下面来看看dba_sequences由那几张底层表构成。
|
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_SEQUENCES" ("SEQUENCE_OWNER", "SEQUENCE_NAME", "MIN_VALUE", "MAX_VALUE", "INCREMENT_BY", "CYCLE_FLAG", "ORDER_FLAG", "CACHE_SIZE", "LAST_NUMBER") AS select u.name, o.name, s.minvalue, s.maxvalue, s.increment$, decode (s.cycle#, 0, ‘N’, 1, ‘Y’), decode (s.order$, 0, ‘N’, 1, ‘Y’), s.cache, s.highwater from sys.seq$ s, sys.obj$ o, sys.user$ u where u.user# = o.owner# and o.obj# = s.obj# |
这里看到由seq$,obj$,user$这几张表构成。
3.2 闪回查询delete数据
下面利用闪回查询来查找已经delete的数据
|
www.htz.pw > create table scott.seq as select * from seq$ as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);
Table created.
www.htz.pw > create table scott.obj as select * from obj$ as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);
Table created. users这张表是可心不需要的 www.htz.pw > create table scott.users as select * from user$ as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);
Table created. |
3.3 插入已经删除的值
下面的两条SQL写得性能不高，如果大量数据，可以使用merge来改写，性能高一些。
|
www.htz.pw > insert into sys.seq$ 2 select * 3 from scott.seq b 4 where b.obj# in (select s.obj# from scott.seq s, scott.obj o, scott.users u 6 where u.user# = o.owner# 7 and o.obj# = s.obj# 8 and u.name = ‘SYS’) 9 ;
136 rows created.
insert into sys.obj$ 2 select * 3 from scott.obj b 4 where b.obj# in (select s.obj# from scott.seq s, scott.obj o, scott.users u 6 where u.user# = o.owner# 7 and o.obj# = s.obj# 8 and u.name = ‘SYS’) 9 ;
136 rows created.
www.htz.pw > select count(*) from seq$; |
3.4 重新运行建库脚本
|
@?/rdbms/admin/catalog.sql 运行过程中会报很多错误，原因是由于sequence不存在，可心不用管
@?/rdbms/admin/catproc.sql @?/sqlplus/admin/pupbld.sql
www.htz.pw > @?/rdbms/admin/utlrp.sql www.htz.pw > Rem 无效对象
www.htz.pw > select status,count(*) from dba_objects group by status;
STATUS COUNT(*) ——- ———- VALID 74600 |
3.5 重启数据库，功能测试
|
www.htz.pw > startup force; ORACLE instance started.
Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 641731928 bytes Database Buffers 192937984 bytes Redo Buffers 2379776 bytes Database mounted. Database opened. |
测试这里就不写了
整个实现结束
SYS用户所有序列删除的恢复：等您坐沙发呢！