Oracle PL/SQL Tutorial/System Tables Data Dictionary/all views — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Get information on all the indexes you have access to using all_views
SQL>
SQL> desc all_views;
Name Null? Type
---------------------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
SQL>
SQL> --select * from all_views;
SQL>
Get the script of a certain view
SQL> set long
SQL>
SQL>
SQL> select text
2 from all_views
3 where view_name = "USER_TABLES";
set pagesize 1000
TEXT
--------------------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, "YES", "NO"), null),
decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5),
decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, "YES", "NO"),
decode(bitand(t.property, 64), 64, "IOT",
decode(bitand(t.property, 512), 512, "IOT_OVERFLOW",
decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"),
decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"),
decode(bitand(t.property, 8192), 8192, "YES",
decode(bitand(t.property, 1), 0, "NO", "YES")),
decode(bitand(o.flags, 2), 2, "DEFAULT",
decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)),
decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"),
decode(bitand(t.flags, 512), 0, "NO", "YES"),
decode(bitand(t.flags, 256), 0, "NO", "YES"),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
"SYS$SESSION", "SYS$TRANSACTION")),
decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"),
decode(bitand(o.flags, 2), 2, "NO",
decode(bitand(t.property, 2147483648), 2147483648, "NO",
decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")),
decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = "_dml_monitoring_enabled"
SQL> set long 10000
SQL> /
--
TEXT
--------------------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, "YES", "NO"), null),
decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5),
decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, "YES", "NO"),
decode(bitand(t.property, 64), 64, "IOT",
decode(bitand(t.property, 512), 512, "IOT_OVERFLOW",
decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"),
decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"),
decode(bitand(t.property, 8192), 8192, "YES",
decode(bitand(t.property, 1), 0, "NO", "YES")),
decode(bitand(o.flags, 2), 2, "DEFAULT",
decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)),
decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"),
decode(bitand(t.flags, 512), 0, "NO", "YES"),
decode(bitand(t.flags, 256), 0, "NO", "YES"),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
"SYS$SESSION", "SYS$TRANSACTION")),
decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"),
decode(bitand(o.flags, 2), 2, "NO",
decode(bitand(t.property, 2147483648), 2147483648, "NO",
decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")),
decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = "_dml_monitoring_enabled"
SQL>