在dul进行数据库挖掘恢复的时候，我们可以通过unload table/user等方式来恢复表数据，但是对于一些view,index,trigger,source,seq,Dblink等不能直接通过unload来实现,但是可以通过挖基表来实现相关操作,这里提供了一些处理思路,在实际操作中根据需求,分析数据字典灵活应用
一．view
导出对象
USER$ OBJ$ COL$ VIEW$
执行sql语句
Set pages 10000
Set long 1000
Spool d:\create_view.sql
select
'CREATE OR REPLACE VIEW '||O.NAME||' ('||
replace(c.cols,',',','||chr(10))||')'||CHR(10)||
'as'||chr(10), v.text
from
user$ u, obj$ o, view$ v,
( SELECT COL.OBJ#, COL.COLS
FROM
(SELECT
OBJ#, COL#, substr(SYS_CONNECT_BY_PATH(NAME,','),2) COLS
FROM COL$
WHERE COL# > 0
START WITH COL# = 1
CONNECT BY PRIOR OBJ# = OBJ# AND PRIOR COL# = COL# - 1 ) COL,
(SELECT OBJ#, COUNT(*) COLCNT FROM COL$
WHERE COL# > 0 GROUP BY OBJ#) CN
WHERE COL.OBJ# = CN.OBJ# AND COL.COL# = CN.COLCNT
) C
where u.user#=o.owner# and o.obj# = c.obj#
and v.obj# = o.obj# and u.name=upper('&username');
说明
1） 分布执行，不能放置一个脚本文件中执行
2） 每条as后面的select语句可能需要重新格式化
3） Create view 语句最后需要增加”;”
二．source
导出对象
USER$ SOURCE$ OBJ$
执行sql语句
Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_source.sql
SELECT DECODE(S.LINE,1,'CREATE OR REPLACE ','')||SOURCE SOURCE
FROM
USER$ U, OBJ$ O, SOURCE$ S
WHERE
U.USER# = O.OWNER# AND
O.OBJ# = S.OBJ# AND
U.NAME = UPPER('&username')
-- AND O.NAME = UPPER('&SOURCE_NAME')
ORDER BY S.OBJ#, S.LINE;
说明
1） 注意SOURCE中的用户名,如果导入不是相同用户,需要修改该脚本用户名
2） 修改完用户名后,直接执行生成脚本即可
三．Index
导出对象
USER$ OBJ$ COL$ IND$ ICOL$
执行sql
Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_index.sql
SELECT
'CREATE '||decode(bitand(IDX.property, 1), 1, 'UNIQUE', '')||
' INDEX '||I.NAME||' ON '||T.NAME||'('||IDX.PATH||');' INDEX_DDL
FROM
USER$ U, OBJ$ T, OBJ$ I,
(
select I.PROPERTY, I.BO#, I.OBJ#, C.POS#,
SUBSTR(sys_connect_by_path(CN.NAME,','),2) path
from IND$ I, ICOL$ C, COL$ CN
WHERE I.OBJ# = C.OBJ# AND I.BO# = C.BO#
AND I.BO# = CN.OBJ# AND C.COL# = CN.INTCOL#
start with C.POS#=1
connect by PRIOR I.OBJ# = I.OBJ#
AND prior C.POS# = C.POS# - 1 ) IDX,
(SELECT I.BO#, I.OBJ#, COUNT(*) COLCNT
FROM ICOL$ I GROUP BY I.BO#, I.OBJ#) IDXC
WHERE
U.USER# = T.OWNER# AND
IDX.BO# = T.OBJ# AND
IDX.OBJ# = I.OBJ# AND
IDX.BO# = IDXC.BO# AND
IDX.OBJ# = IDXC.OBJ# AND
IDX.POS# = IDXC.COLCNT AND
U.NAME = upper('&username')
ORDER BY T.NAME, I.NAME;
说明
1） 因为SYS_CONNECT_BY_PATH所以需要10g及其以上版本
2） SQL中没有分区唯一性索引
3） 注意检查sql是否因为行长度不够导致异常
四．Sequence
导出对象
USER$ OBJ$ SEQ$
执行sql语句
Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_sequence.sql
SELECT
'CREATE SEQUENCE '|| SEQ_NAME ||
' MINVALUE '||minval ||
' MAXVALUE '||MAXVAL ||
' START WITH '||LASTVAL ||
' ' || CYC || ' ' || ORD ||
DECODE(SIGN(CACHE), 1,' CACHE '|| CACHE, 'NOCACHE') ||
';' SEQ_DDL
from
(select u.name OWNER, o.name SEQ_NAME,
s.minvalue MINVAL, s.maxvalue MAXVAL,
s.increment$ INC,
decode (s.cycle#, 0, 'NOCYCLE', 1, 'CYCLE ') CYC,
decode (s.order$, 0, 'NOORDER', 1, 'ORDER') ORD,
s.cache, s.highwater LASTVAL
from seq$ s, obj$ o, user$ u
where u.user# = o.owner#
and o.obj# = s.obj#
and u.name=upper('&username'));
五．TRIGGER
导出对象
OBJ$ USER$ TRIGGER$
执行sql语句
Set pages 10000
Set long 1000
set linse 1000
Spool d:\create_trigger.sql
select
'CREATE OR REPLACE TRIGGER '|| trigger_name || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER ', 'B', 'BEFORE ', 'I',
'INSTEAD OF ' ) ||
triggering_event || ' ON ' || table_owner || '.' ||
table_name || chr(10) || REF_CLAUSE || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ), trigger_body
from (
select trigusr.name owner, trigobj.name trigger_name,
decode(t.type#, 0, 'BEFORE STATEMENT',
1, 'BEFORE EACH ROW', 2, 'AFTER STATEMENT',
3, 'AFTER EACH ROW', 4, 'INSTEAD OF',
'UNDEFINED') trigger_type,
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, 'INSERT', 010, 'UPDATE', 001, 'DELETE',
110, 'INSERT OR UPDATE', 101, 'INSERT OR DELETE',
011, 'UPDATE OR DELETE',
111, 'INSERT OR UPDATE OR DELETE',
'ERROR') triggering_event,
tabusr.name table_owner, tabobj.name table_name,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname REF_CLAUSE,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR') STATUS,
t.definition , t.action# trigger_body
from obj$ trigobj, obj$ tabobj, trigger$ t,
user$ tabusr, user$ trigusr
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) < 8 ))
where table_owner=upper('&username')
order by owner, trigger_name;
六. Dblink
导出对象
Sys.link$ sys.user$
执行查询sql
SELECT U.NAME USERNAME,
L.NAME DBLINK_NAME,
L.USERID LOGIN_USER,
L.HOST TNS,
L.CTIME
FROM LINK$ L, USER$ U
WHERE L.OWNER# = U.USER#
AND U.NAME = UPPER('&USERNAME');
