Oracle DUL_PRM_再構造オブジェクトテストレポート

PRM-DULでデータを抽出する

 

つまり、EASKINGDEEユーザーだけに導入して、”T_GL”で始まるテーブル:

七十枚のテーブルが導入されたが、二十八枚のテーブルにデータがある。各テーブルの行数もdulの結果の同じようになる。。

prmz21

SQL> exec

dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>’test4′);

select table_name,num_rows from user_tables order by num_rows desc;

结果如下:

prmz22

database linkを再構造する

 

SELECT ‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)

||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を再構造する

 

SELECT ‘create or replace ‘ || decode(o.owner#, 1, ‘ public ‘) ||

‘ 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を再構造する

 

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(‘EASKINGDEE’);

 

 

结果は以下の通り

 

prmz23

 

jobを再構造する

 

select job,LOWNER,INTERVAL#,next_date,WHAT,SCHEDULER_FLAGS from job$

 

prmz24

indexを再構造する

 

 

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 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;

结果は以下の通り:

prmz25

triggerを再構造する

 

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(‘EASKINGDEE’)

order by owner, trigger_name

 

行が返されていない、テーブル名はユーザーEASKINGDEEのテーブルにトリガーがないと意味している

4.8 sequenceを再構造する

 

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(‘EASKINGDEE’))

 

行が返されていない、テーブル名がユーザーEASKINGDEEのテーブルにシーケンスがないと意味している

4.9 procedurceを再構造する

 

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 =’EASKINGDEE’

ORDER BY S.OBJ#, S.LINE;

结果は以下の通り:

prmz26

 


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *