Rem this patch is release by eygle
Rem use to Oracle9i database to solve 10g export can not export Oracle9i database error
Rem this patch is only use to education purpose
Rem before you apply this patch to your product database
Rem rememeber Do a full test
Rem I have test this patch on Oracle9.2.0.3 and Oracle9.2.0.4.
CREATE OR REPLACE VIEW exu9rls (
objown, objnam, polgrp, policy, polown, polsch, polfun, stmt,
chkopt, enabled, spolicy) AS
SELECT u.name, o.name, r.gname, r.pname, r.pfschma, r.ppname,
r.pfname,
DECODE(BITAND(r.stmt_type, 1), 0, '', 'SELECT,') ||
DECODE(BITAND(r.stmt_type, 2), 0, '', 'INSERT,') ||
DECODE(BITAND(r.stmt_type, 4), 0, '', 'UPDATE,') ||
DECODE(BITAND(r.stmt_type, 8), 0, '', 'DELETE,'),
r.check_opt, r.enable_flag,
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
FROM sys.user$ u, sys.obj$ o, sys.rls$ r
WHERE u.user# = o.owner# AND
r.obj# = o.obj# AND r.ptype is null AND
(UID IN (o.owner#, 0) OR
EXISTS (
SELECT role
FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
UNION
SELECT u.name, o.name, r.gname, r.pname, r.pfschma, r.ppname,
r.pfname,
DECODE(BITAND(r.stmt_type, 1), 0, '', 'SELECT,') ||
DECODE(BITAND(r.stmt_type, 2), 0, '', 'INSERT,') ||
DECODE(BITAND(r.stmt_type, 4), 0, '', 'UPDATE,') ||
DECODE(BITAND(r.stmt_type, 8), 0, '', 'DELETE,'),
r.check_opt, r.enable_flag,
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
FROM sys.user$ u, sys.obj$ o, sys.rls$ r
WHERE u.user# = o.owner# AND
r.ptype is not null AND
r.ptype = o.obj# AND
(UID IN (o.owner#, 0) OR
EXISTS (
SELECT role
FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
/
CREATE OR REPLACE VIEW exu10adefpswitches (
nlslensem, optlevel, codetype, warnings ) AS
SELECT a.value, b.value, c.value, d.value
FROM sys.v$parameter a, sys.v$parameter b, sys.v$parameter c,
sys.v$parameter d
WHERE a.name = 'nls_length_semantics' AND
b.name = 'plsql_optimize_level' AND
c.name = 'plsql_code_type' AND
d.name = 'plsql_warnings'
/
GRANT SELECT ON sys.exu10adefpswitches TO PUBLIC;
CREATE OR REPLACE VIEW exu9typtu (
tname, towner, ownerid, toid, mtime, typobjno, tabobjno,
audit$, sqlver, property,
typobjstatus, tversion, thashcode, synobjno, colsynobjno) AS
SELECT o.name, u.name, o.owner#, t.toid,
TO_CHAR(o.mtime, 'YYYY-MM-DD:HH24:MI:SS'), o.obj#, c.obj#,
tm.audit$, sv.sql_version, t.properties,
o.status, t.version#, t.hashcode, sy.obj#, c.synobj#
FROM sys.coltype$ c, sys.user$ u, sys.obj$ o, sys.type$ t,
sys.type_misc$ tm, sys.exu816sqv sv, sys.obj$ ne, sys.obj$ sy
WHERE t.toid = c.toid AND
o.oid$ = c.toid AND
u.user# = o.owner# AND
o.obj# = tm.obj# AND
BITAND(t.properties, 2128) = 0 AND/* skip system gen'd types */
t.toid = t.tvoid AND /* Latest type only */
NVL(o.type#, -1) != 10 AND
(o.owner# = UID OR /* owned by current user */
/* current user or public role have execute access to type */
o.obj# IN (
SELECT oa.obj#
FROM sys.objauth$ oa
WHERE oa.obj# = o.obj# AND
oa.privilege# = 12 AND /* execute */
oa.grantee# IN (UID, 1)) OR
EXISTS ( /* current user or public role can execute any type */
SELECT NULL
FROM sys.sysauth$ sa
WHERE sa.grantee# IN (UID, 1) AND
sa.privilege# = -184)) AND
o.spare1 = sv.version# (+) AND
ne.obj# = c.synobj# AND /* non_exist (neg depend) */
sy.name = ne.name AND
sy.owner# = 1 AND /* PUBLIC */
sy.type# = 5 /* SYNONYM */
/
GRANT SELECT ON sys.exu9typtu TO PUBLIC;
CREATE OR REPLACE VIEW EXU9TYP
(tname, towner, ownerid, toid, mtime, objno, audit$, secondaryobj, sqlver, typobjstatus, tversion, thashcode, typeid, roottoid)
AS
SELECT o.name, u.name, o.owner#, t.toid,
TO_CHAR(o.mtime, 'YYYY-MM-DD:HH24:MI:SS'), o.obj#, tm.audit$,
DECODE(BITAND(o.flags, 16), 16, 1, 0), sv.sql_version,
o.status, t.version#, t.hashcode, t.typeid,
NVL(t.roottoid,HEXTORAW('00'))
FROM sys.obj$ o, sys.user$ u, sys.type$ t, sys.type_misc$ tm,
sys.exu816sqv sv
WHERE o.type# = 13 AND
o.oid$ = t.toid AND
u.user# = o.owner# AND
tm.obj# = o.obj# AND
t.toid = t.tvoid AND /* Only the latest rev */
BITAND(t.properties, 2128) = 0 AND /* skip system gen'd types*/
u.name NOT IN ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA',
'DIP', 'DMSYS', 'DBSNMP') AND
o.spare1 = sv.version# (+)
/
CREATE OR REPLACE VIEW EXU9TYPTU
(tname, towner, ownerid, toid, mtime, typobjno, tabobjno, audit$, sqlver, property, typobjstatus, tversion, thashcode, synobjno, colsynobjno)
AS
SELECT o.name, u.name, o.owner#, t.toid,
TO_CHAR(o.mtime, 'YYYY-MM-DD:HH24:MI:SS'), o.obj#, c.obj#,
tm.audit$, sv.sql_version, t.properties,
o.status, t.version#, t.hashcode, sy.obj#, c.synobj#
FROM sys.coltype$ c, sys.user$ u, sys.obj$ o, sys.type$ t,
sys.type_misc$ tm, sys.exu816sqv sv, sys.obj$ ne, sys.obj$ sy
WHERE t.toid = c.toid AND
o.oid$ = c.toid AND
u.user# = o.owner# AND
o.obj# = tm.obj# AND
BITAND(t.properties, 2128) = 0 AND/* skip system gen'd types */
t.toid = t.tvoid AND /* Latest type only */
NVL(o.type#, -1) != 10 AND
(o.owner# = UID OR /* owned by current user */
/* current user or public role have execute access to type */
o.obj# IN (
SELECT oa.obj#
FROM sys.objauth$ oa
WHERE oa.obj# = o.obj# AND
oa.privilege# = 12 AND /* execute */
oa.grantee# IN (UID, 1)) OR
EXISTS ( /* current user or public role can execute any type */
SELECT NULL
FROM sys.sysauth$ sa
WHERE sa.grantee# IN (UID, 1) AND
sa.privilege# = -184)) AND
o.spare1 = sv.version# (+) AND
ne.obj# = c.synobj# AND /* non_exist (neg depend) */
sy.name = ne.name AND
sy.owner# = 1 AND /* PUBLIC */
sy.type# = 5 /* SYNONYM */
/
CREATE OR REPLACE VIEW EXU9TYPU AS
SELECT "TNAME","TOWNER","OWNERID","TOID","MTIME","OBJNO","AUDIT$","SECONDARYOBJ","SQLVER","TYPOBJSTATUS","TVERSION","THASHCODE","TYPEID","ROOTTOID"
FROM sys.exu9typ
WHERE ownerid = UID
/
CREATE OR REPLACE VIEW EXU10TABS
(objid, dobjid, name, owner, ownerid, tablespace, tsno, fileno, blockno, audit$, comment$, clusterflag, mtime, modified, tabno, pctfree$, pctused$, initrans, maxtrans, degree, instances, cache, tempflags, property, deflog, tsdeflog, roid, recpblk, secondaryobj, rowcnt, blkcnt, avgrlen, tflags, trigflag, objstatus, xdbool)
AS
SELECT
o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
t$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
NVL(t$.bobj#, 0), o$.mtime,
DECODE(BITAND(t$.flags, 1), 1, 1, 0), NVL(t$.tab#, 0),
MOD(t$.pctfree$, 100), t$.pctused$, t$.initrans, t$.maxtrans,
NVL(t$.degree, 1), NVL(t$.instances, 1),
DECODE(BITAND(t$.flags, 8), 8, 1, 0),
MOD(TRUNC(o$.flags / 2), 2), t$.property,
DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
t$.flags, t$.trigflag, o$.status,
(SELECT COUNT(*)
FROM sys.opqtype$ opq$
WHERE opq$.obj# = o$.obj# AND
BITAND(opq$.flags, 32) = 32 )
FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
sys.com$ c$
WHERE t$.obj# = o$.obj# AND
t$.ts# = ts$.ts# AND
u$.user# = o$.owner# AND
o$.obj# = c$.obj#(+) AND
c$.col#(+) IS NULL AND
BITAND(o$.flags,128) != 128 AND /* Skip recycle bin */
BITAND(t$.property, 64+512) = 0 AND /*skip IOT and ovflw segs*/
BITAND(t$.flags, 536870912) = 0 /* skip IOT mapping table */
UNION ALL /* Index-only tables */
SELECT o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
i$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
NVL(t$.bobj#, 0), o$.mtime,
DECODE(BITAND(t$.flags, 1), 1, 1, 0),
NVL(t$.tab#, 0), 0, 0, 0, 0, 1, 1,
DECODE(BITAND(t$.flags, 8), 8, 1, 0),
MOD(TRUNC(o$.flags / 2), 2), t$.property,
DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
t$.flags, t$.trigflag, o$.status,
(SELECT COUNT(*)
FROM sys.opqtype$ opq$
WHERE opq$.obj# = o$.obj# AND
BITAND(opq$.flags, 32) = 32 )
FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
sys.com$ c$, sys.ind$ i$
WHERE t$.obj# = o$.obj# AND
u$.user# = o$.owner# AND
o$.obj# = c$.obj#(+) AND
c$.col#(+) IS NULL AND
BITAND(o$.flags,128) != 128 AND /* Skip recycle bin */
BITAND(t$.property, 64+512) = 64 AND /* IOT, but not overflow*/
t$.pctused$ = i$.obj# AND/* For IOTs, pctused has index obj# */
i$.ts# = ts$.ts#
/
CREATE OR REPLACE VIEW EXU10TAB AS
SELECT "OBJID","DOBJID","NAME","OWNER","OWNERID","TABLESPACE","TSNO","FILENO","BLOCKNO","AUDIT$","COMMENT$","CLUSTERFLAG","MTIME","MODIFIED","TABNO","PCTFREE$","PCTUSED$","INITRANS","MAXTRANS","DEGREE","INSTANCES","CACHE","TEMPFLAGS","PROPERTY","DEFLOG","TSDEFLOG","ROID","RECPBLK","SECONDARYOBJ","ROWCNT","BLKCNT","AVGRLEN","TFLAGS","TRIGFLAG","OBJSTATUS","XDBOOL"
FROM sys.exu10tabs t$
WHERE t$.secondaryobj = 0
/
CREATE OR REPLACE VIEW EXU10TABU AS
SELECT "OBJID","DOBJID","NAME","OWNER","OWNERID","TABLESPACE","TSNO","FILENO","BLOCKNO","AUDIT$","COMMENT$","CLUSTERFLAG","MTIME","MODIFIED","TABNO","PCTFREE$","PCTUSED$","INITRANS","MAXTRANS","DEGREE","INSTANCES","CACHE","TEMPFLAGS","PROPERTY","DEFLOG","TSDEFLOG","ROID","RECPBLK","SECONDARYOBJ","ROWCNT","BLKCNT","AVGRLEN","TFLAGS","TRIGFLAG","OBJSTATUS","XDBOOL"
FROM sys.exu10tab
WHERE ownerid = UID
/
CREATE OR REPLACE VIEW EXU9TYPU AS
SELECT "TNAME","TOWNER","OWNERID","TOID","MTIME","OBJNO","AUDIT$","SECONDARYOBJ","SQLVER","TYPOBJSTATUS","TVERSION","THASHCODE","TYPEID","ROOTTOID"
FROM sys.exu9typ
WHERE ownerid = UID
/
CREATE OR REPLACE VIEW EXU10CCL
(ownerid, ownername, cno, colname, colno, intcol, property, nolog)
AS
SELECT o.owner#, u.name, cc.con#,
DECODE(BITAND(c.property, 1), 1, at.name, c.name),
cc.pos#, c.intcol#, c.property,
DECODE(BITAND(cc.spare1, 1), 1, 1, 0)
FROM sys.obj$ o, sys.col$ c, sys.ccol$ cc, sys.attrcol$ at,
sys.user$ u
WHERE o.obj# = cc.obj# AND
o.owner# = u.user# AND
c.obj# = cc.obj# AND
c.intcol# = cc.intcol# AND
BITAND(c.property, 2097152) = 0 AND /* Not REA */
BITAND(c.property, 1024) = 0 AND /* Not SETID */
c.obj# = at.obj# (+) AND
c.intcol# = at.intcol# (+) AND
NOT EXISTS (
SELECT owner, name
FROM sys.noexp$ ne
WHERE ne.owner = u.name AND
ne.name = o.name AND
ne.obj_type = 2)
UNION /* Nested Tables - SETID column */
SELECT o.owner#, u.name, cc.con#,
DECODE(BITAND(c.property, 1), 1, at.name, c.name),
cc.pos#, c.intcol#, c.property,
DECODE(BITAND(cc.spare1, 1), 1, 1, 0)
FROM sys.obj$ o, sys.col$ c, sys.ccol$ cc, sys.attrcol$ at,
sys.user$ u, sys.col$ cn
WHERE o.obj# = cc.obj# AND
o.owner# = u.user# AND
cn.obj# = cc.obj# AND
cn.intcol# = cc.intcol# AND
BITAND(cn.property, 1024) = 1024 AND /* SETID */
c.obj# = cc.obj# AND
c.col# = cn.col# AND
c.intcol# = (cn.intcol# - 1) AND
c.segcol# = 0 AND
c.obj# = at.obj# (+) AND
c.intcol# = at.intcol# (+) AND
NOT EXISTS (
SELECT owner, name
FROM sys.noexp$ ne
WHERE ne.owner = u.name AND
ne.name = o.name AND
ne.obj_type = 2)
UNION /* REFs - REF attribute columns */
SELECT o.owner#, u.name, cc.con#,
DECODE(BITAND(rc.property, 1), 1, at.name, rc.name),
cc.pos#, rc.intcol#, rc.property,
DECODE(BITAND(cc.spare1, 1), 1, 1, 0)
FROM sys.obj$ o, sys.col$ c, sys.ccol$ cc, sys.attrcol$ at,
sys.user$ u, sys.coltype$ ct, sys.col$ rc
WHERE o.obj# = cc.obj# AND
o.owner# = u.user# AND
c.obj# = cc.obj# AND
c.intcol# = cc.intcol# AND
BITAND(c.property, 2097152) = 2097152 AND /* REA */
ct.obj# = cc.obj# AND
ct.col# = cc.col# AND
UTL_RAW.CAST_TO_BINARY_INTEGER(SUBSTRB(ct.intcol#s, 1,2), 3) =
cc.intcol# AND /* first list col# = constr col# */
rc.obj# = cc.obj# AND
rc.intcol# = ct.intcol# AND
rc.obj# = at.obj# (+) AND
rc.intcol# = at.intcol# (+) AND
NOT EXISTS (
SELECT owner, name
FROM sys.noexp$ ne
WHERE ne.owner = u.name AND
ne.name = o.name AND
ne.obj_type = 2)
/
CREATE OR REPLACE VIEW EXU10CCLU AS
SELECT "OWNERID","OWNERNAME","CNO","COLNAME","COLNO","INTCOL","PROPERTY","NOLOG"
FROM sys.exu10ccl
WHERE UID = ownerid
/
CREATE OR REPLACE VIEW EXU10ASC
(tobjid, pobjid, townerid, colname, intcol, distcount, lowval, hival, density, nullcount, avgcln, cflags, property)
AS
SELECT c$.obj#, hh$.obj#, o$.owner#, c$.name, hh$.intcol#,
hh$.distcnt, hh$.lowval, hh$.hival, hh$.density, hh$.null_cnt,
hh$.avgcln, hh$.spare2, c$.property
FROM sys.hist_head$ hh$, sys.obj$ o$, sys.obj$ ot$, sys.col$ c$
WHERE hh$.obj# = o$.obj# AND
c$.obj# = ot$.obj# AND
o$.owner# = ot$.owner# AND
hh$.intcol# = c$.intcol#
/
CREATE OR REPLACE VIEW EXU10ASCU AS
SELECT "TOBJID","POBJID","TOWNERID","COLNAME","INTCOL","DISTCOUNT","LOWVAL","HIVAL","DENSITY","NULLCOUNT","AVGCLN","CFLAGS","PROPERTY"
FROM sys.exu10asc
WHERE townerid = UID
/
CREATE OR REPLACE VIEW EXU10AOBJSWITCH
(objid, nlslensem, optlevel, codetype, debug, warnings)
AS
SELECT a.obj#, a.value, b.value, c.value, d.value, e.value
FROM sys.settings$ a, sys.settings$ b, sys.settings$ c,
sys.settings$ d, sys.settings$ e, sys.obj$ o
WHERE o.obj# = a.obj# AND
a.obj# = b.obj# AND
b.obj# = c.obj# AND
c.obj# = d.obj# AND
d.obj# = e.obj# AND
a.param = 'nls_length_semantics' AND
b.param = 'plsql_optimize_level' AND
c.param = 'plsql_code_type' AND
d.param = 'plsql_debug' AND
e.param = 'plsql_warnings' AND
(UID IN (o.owner#, 0) OR
EXISTS (
SELECT role
FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
/
CREATE OR REPLACE VIEW EXU8REF
(objid, owner, ownerid, tname, rowner, rtname, cname, cno, rcno, action, enabled, defer, property, robjid, rownerid, reftype, refconstr, type)
AS
SELECT o.obj#, u.name, c.owner#, o.name, ru.name, ro.name, c.name,
c.con#, cd.rcon#, NVL(cd.refact, 0), NVL(cd.enabled, 0),
NVL(cd.defer, 0), NVL(t.property, 0), cd.robj#, ro.owner#,
DECODE((SELECT COUNT (*)
FROM sys.refcon$ rf
WHERE rf.obj# = o.obj# AND
BITAND(rf.reftyp, 4) = 4),
1, 4, 0), /* if 1, EXURUID, else not a REF */
DECODE((SELECT COUNT (*)
FROM sys.ccol$ cc, sys.col$ c
WHERE cc.con# = cd.con# AND
c.obj# = cc.obj# AND
c.intcol# = cc.intcol# AND
BITAND(c.property, 2097152)= 2097152), /* REA */
0, 0, 1), /* if none, FALSE, else TRUE */
o.type#
FROM sys.user$ u, sys.user$ ru, sys.exu81obj o, sys.obj$ ro,
sys.con$ c, sys.cdef$ cd, sys.tab$ t
WHERE u.user# = c.owner# AND
o.obj# = cd.obj# AND
ro.obj# = cd.robj# AND
cd.con# = c.con# AND
cd.type# = 4 AND
ru.user# = ro.owner# AND
o.obj# = t.obj# (+) AND
u.name NOT IN ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA',
'DIP', 'DMSYS','DBSNMP') AND
NOT EXISTS (
SELECT name, owner, obj_type
FROM sys.noexp$ ne
WHERE ne.owner = u.name AND
ne.name = o.name AND
ne.obj_type = 2)
/
CREATE OR REPLACE VIEW EXU8REFU AS
SELECT "OBJID","OWNER","OWNERID","TNAME","ROWNER","RTNAME","CNAME","CNO","RCNO","ACTION","ENABLED","DEFER","PROPERTY","ROBJID","ROWNERID","REFTYPE","REFCONSTR","TYPE"
FROM sys.exu8ref
WHERE UID = ownerid
/
alter table snap$ add (ALIAS_TXT CLOB);
CREATE OR REPLACE VIEW EXU9SNAP
(owner, ownerid, name, table_name, master_view, master_link, mtime, can_use_log, error, type, query, flag, rowid_snap, primkey_snap, update_snap, update_trig, update_log, mastabs, masver, lob_vector, snapshot, snapid, instsite, flavor_id, rscn, objflag, flag2, status, sna_type_owner, sna_type_name, parent_sowner, parent_vname, file_ver, sql_ver, alias_txt, mview_comment)
AS
SELECT s.sowner, u.user#, s.vname, s.tname, s.mview, s.mlink, s.mtime,
DECODE(s.can_use_log, NULL, 'NO', 'YES'), NVL(s.error#, 0),
DECODE(s.auto_fast, 'C', 'COMPLETE', 'F', 'FAST', '?', 'FORCE',
NULL, 'FORCE', 'N', 'NEVER', 'ERROR'),
s.query_txt, NVL(s.flag, 0),
/*
** have a flag for each snapshot types: rowid, primary key and
** updatable for compatibility purpose
*/
DECODE(BITAND(NVL(s.flag, 0), 16), 16, 1, 0),
DECODE(BITAND(NVL(s.flag, 0), 32), 32, 1, 0),
DECODE(BITAND(NVL(s.flag, 0), 2), 2, 1, 0),
s.ustrg, s.uslog, s.tables, NVL(s.master_version, 0),
RAWTOHEX(s.lobmaskvec), s.snapshot, NVL(s.snapid, 0),
s.instsite, NVL(s.flavor_id, 0),
s.rscn, s.objflag, s.flag2, s.status, s.sna_type_owner,
s.sna_type_name, s.parent_sowner, s.parent_vname,
5, 0, s.alias_txt, c.comment$
FROM sys.snap$ s, sys.user$ u, sys.com$ c, sys.obj$ o, sys.tab$ t
WHERE u.name = s.sowner AND
o.owner# = u.user# AND
o.name = s.vname AND
o.type# = 2 AND
o.obj# = t.obj# AND
(bitand(t.property, 67108864) = 67108864) AND
o.obj# = c.obj#(+) AND c.col#(+) IS NULL
/
CREATE OR REPLACE VIEW EXU9SNAPU AS
SELECT "OWNER","OWNERID","NAME","TABLE_NAME","MASTER_VIEW","MASTER_LINK","MTIME","CAN_USE_LOG","ERROR","TYPE","QUERY","FLAG","ROWID_SNAP","PRIMKEY_SNAP","UPDATE_SNAP","UPDATE_TRIG","UPDATE_LOG","MASTABS","MASVER","LOB_VECTOR","SNAPSHOT","SNAPID","INSTSITE","FLAVOR_ID","RSCN","OBJFLAG","FLAG2","STATUS","SNA_TYPE_OWNER","SNA_TYPE_NAME","PARENT_SOWNER","PARENT_VNAME","FILE_VER","SQL_VER","ALIAS_TXT","MVIEW_COMMENT"
FROM exu9snap
WHERE ownerid = UID
/
alter table mlog$ add (OLDEST_SEQ date);
CREATE OR REPLACE VIEW EXU10SNAPL
(log_owner, log_ownerid, master, log_table, log_trigger, flag, youngest, oldest, oldest_pk, mtime, rowid_snapl, primkey_snapl, oid_snapl, seq_snapl, inv_snapl, file_ver, temp_log, oldest_oid, oldest_new, oldest_seq)
AS
SELECT m.mowner, u.user#, m.master, m.log, m.trig, NVL(m.flag, 0),
m.youngest, m.oldest, m.oldest_pk, m.mtime,
/* have a flag for each snapshot log types: rowid, primary key
** for compatibility purpose */
DECODE(BITAND(NVL(m.flag, 0), 1), 1, 1, 0),
DECODE(BITAND(NVL(m.flag, 0), 2), 2, 1, 0),
DECODE(BITAND(NVL(m.flag, 0), 512), 512, 1, 0),
DECODE(BITAND(NVL(m.flag, 0), 1024), 1024, 1, 0),
DECODE(BITAND(NVL(m.flag, 0), 16), 16, 1, 0),
7, m.temp_log, m.oldest_oid, m.oldest_new, m.oldest_seq
FROM sys.mlog$ m, sys.user$ u
WHERE m.mowner = u.name
/
CREATE OR REPLACE VIEW EXU10SNAPLU AS
SELECT "LOG_OWNER","LOG_OWNERID","MASTER","LOG_TABLE","LOG_TRIGGER","FLAG","YOUNGEST","OLDEST","OLDEST_PK","MTIME","ROWID_SNAPL","PRIMKEY_SNAPL","OID_SNAPL","SEQ_SNAPL","INV_SNAPL","FILE_VER","TEMP_LOG","OLDEST_OID","OLDEST_NEW","OLDEST_SEQ"
FROM sys.exu10snapl
WHERE log_ownerid = UID
/
alter table cdc_change_tables$ add (MVL_OLDEST_SEQ number,MVL_OLDEST_SEQ_TIME date);
CREATE OR REPLACE VIEW EXU10MVL
(ctobj#, log_owner, log_name, log_ownerid, created_time, file_version, using_rowid_flag, using_primarykey_flag, using_oid_flag, using_sequence_flag, change_set_name, source_schema_name, source_table_name, created_scn, mvl_flag, captured_values, mvl_temp_log, mvl_v7trigger, last_altered, lowest_scn, mvl_oldest_rid, mvl_oldest_pk, mvl_oldest_oid, mvl_oldest_new, mvl_oldest_rid_time, mvl_oldest_pk_time, mvl_oldest_oid_time, mvl_oldest_new_time, mvl_backcompat_view, mvl_physmvl, highest_scn, highest_timestamp, mvl_oldest_seq, mvl_oldest_seq_time)
AS
SELECT ct.obj#, ct.change_table_schema, ct.change_table_name, u.user#,
ct.created, 7, DECODE(BITAND(ct.mvl_flag, 1), 1, 1, 0),
DECODE(BITAND(ct.mvl_flag, 2), 2, 1, 0),
DECODE(BITAND(ct.mvl_flag, 512), 512, 1, 0),
DECODE(BITAND(ct.mvl_flag, 1024), 1024, 1, 0),
ct.change_set_name, ct.source_schema_name,
ct.source_table_name, ct.created_scn, ct.mvl_flag,
ct.captured_values, ct.mvl_temp_log, ct.mvl_v7trigger,
ct.last_altered, ct.lowest_scn, ct.mvl_oldest_rid,
ct.mvl_oldest_pk, ct.mvl_oldest_oid, ct.mvl_oldest_new,
ct.mvl_oldest_rid_time, ct.mvl_oldest_pk_time,
ct.mvl_oldest_oid_time, ct.mvl_oldest_new_time,
ct.mvl_backcompat_view, ct.mvl_physmvl, ct.highest_scn,
ct.highest_timestamp, ct.mvl_oldest_seq, ct.mvl_oldest_seq_time
FROM sys.cdc_change_tables$ ct, sys.user$ u
WHERE ct.change_table_schema = u.name AND
ct.mvl_flag IS NOT NULL AND
BITAND(ct.mvl_flag, 128) = 128 AND
(UID IN (0, u.user#) OR
EXISTS (
SELECT role
FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
/
CREATE OR REPLACE VIEW EXU10MVLU AS
SELECT "CTOBJ#","LOG_OWNER","LOG_NAME","LOG_OWNERID","CREATED_TIME","FILE_VERSION","USING_ROWID_FLAG","USING_PRIMARYKEY_FLAG","USING_OID_FLAG","USING_SEQUENCE_FLAG","CHANGE_SET_NAME","SOURCE_SCHEMA_NAME","SOURCE_TABLE_NAME","CREATED_SCN","MVL_FLAG","CAPTURED_VALUES","MVL_TEMP_LOG","MVL_V7TRIGGER","LAST_ALTERED","LOWEST_SCN","MVL_OLDEST_RID","MVL_OLDEST_PK","MVL_OLDEST_OID","MVL_OLDEST_NEW","MVL_OLDEST_RID_TIME","MVL_OLDEST_PK_TIME","MVL_OLDEST_OID_TIME","MVL_OLDEST_NEW_TIME","MVL_BACKCOMPAT_VIEW","MVL_PHYSMVL","HIGHEST_SCN","HIGHEST_TIMESTAMP","MVL_OLDEST_SEQ","MVL_OLDEST_SEQ_TIME"
FROM sys.exu10mvl
WHERE log_ownerid = UID
/
扫一扫
664
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
