Oracle DUL_PRM_重建对象实验报告
PRM-DUL抽数据
也是就只导入EASKINGDEE用户下，以”T_GL”开头的表：
有70张表被导入，但是其中也就28个表有数据，各个有数据的表的行数和dul的结果是一样的。
SQL> exec
dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>’test4′);
select table_name,num_rows from user_tables order by num_rows desc;
结果如下：
4.2 重建database link
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by ‘
||L.PASSWORD||’ using ”’ || L.host || ””
||chr(10)||’;’ TEXT
FROM link$ L, user$ U
WHERE L.OWNER# = U.USER#;
没有返回行，说明没有database link
4.3 重建synonym
‘ synonym ‘ || decode(o.owner#, 1, ”, u.name || ‘.’) || o.name ||
‘ for ‘ || s.owner || ‘.’ || s.name|| NVL2(S.NODE,’@’,”)||S.NODE|| ‘;’
FROM SYN$ S, OBj$ o, USER$ U
where s.obj# = o.obj#
AND o.dataobj# is null
and s.owner=upper(‘EASKINGDEE’) and u.user# = o.owner#
没有返回行，说明没有synonym
4.4 重建 view
‘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(‘EASKINGDEE’);
结果如下
重建job
select job,LOWNER,INTERVAL#,next_date,WHAT,SCHEDULER_FLAGS from job$
重建index
‘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 nocycle PRIOR I.OBJ# = I.OBJ#
AND prior C.POS# = C.POS# ) 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(‘EASKINGDEE’)
ORDER BY T.NAME, I.NAME;
结果如下：
重建trigger
‘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(‘EASKINGDEE’)
order by owner, trigger_name
没有返回行，表名在用户EASKINGDEE下的表上没有触发器
4.8 重建sequence
‘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(‘EASKINGDEE’))
没有返回行，表名在用户EASKINGDEE下的表上没有序列
4.9 重建procedurce
FROM
USER$ U, OBJ$ O, SOURCE$ S
WHERE
U.USER# = O.OWNER# AND
O.OBJ# = S.OBJ# and U.NAME =’EASKINGDEE’
ORDER BY S.OBJ#, S.LINE;
结果如下：
关注dbDao.com的新浪微博
扫码关注dbDao.com 微信公众号：
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
