Oracle PL/SQL Tutorial/System Tables Data Dictionary/all views

Материал из SQL эксперт
Перейти к: навигация, поиск

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>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Tables_Data_Dictionary/all_views&oldid=3830»