PRM-DULでデータを抽出する
つまり、EASKINGDEEユーザーだけに導入して、”T_GL”で始まるテーブル:
七十枚のテーブルが導入されたが、二十八枚のテーブルにデータがある。各テーブルの行数もdulの結果の同じようになる。。
SQL> exec
dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>’test4′);
select table_name,num_rows from user_tables order by num_rows desc;
结果如下:
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’); |
结果は以下の通り
jobを再構造する
select job,LOWNER,INTERVAL#,next_date,WHAT,SCHEDULER_FLAGS from job$ |
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; |
结果は以下の通り:
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; |
结果は以下の通り:
Comment