в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:07:59Z

1 версия

Новая страница

== Get information on all the indexes you have access to using all_views==



<source lang="sql">
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></source>

== Get the script of a certain view==



<source lang="sql">
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></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Tables Data Dictionary/all views - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия