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>