Oracle PL/SQL Tutorial/System Tables Data Dictionary/all views
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>