DBA

A Higher-Level Perspective on SQL Tuning, Part 2, Listing 4

Listing 4: The SELECT * FROM LOOKS_SO_INNOCENT query expanded via DBMS_UTILITY
SQL> variable c clob
SQL> begin
  2        dbms_utility.expand_sql_text
  3         ( 'select * from LOOKS_SO_INNOCENT',:c);
  4      end;
  5      /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
select
  "A1"."OWNER"             "OWNER",
  "A1"."CREATED"           "CREATED",
  "A1"."BYTES"             "BYTES",
  "A1"."TABLESPACE_NAME"   "TABLESPACE_NAME"
from
  (
    select
      "A2"."OWNER"             "OWNER",
      "A2"."CREATED"           "CREATED",
      "A3"."BYTES"             "BYTES",
      "A3"."TABLESPACE_NAME"   "TABLESPACE_NAME"
    from
      (
        select
          "A4"."OWNER"             "OWNER",
          "A4"."SEGMENT_NAME"      "SEGMENT_NAME",
          "A4"."PARTITION_NAME"    "PARTITION_NAME",
          "A4"."SEGMENT_TYPE"      "SEGMENT_TYPE",
          "A4"."SEGMENT_SUBTYPE"   "SEGMENT_SUBTYPE",
          "A4"."TABLESPACE_NAME"   "TABLESPACE_NAME",
          "A4"."HEADER_FILE"       "HEADER_FILE",
          "A4"."HEADER_BLOCK" "HEADER_BLOCK",
          decode(bitand("A4"."SEGMENT_FLAGS",131072),131072,"A4"."BLOCKS",decode(bitand("A4"."SEGMENT_FLAGS",1),1,"SYS"."DBMS_SPACE_ADMIN"
          ."SEGMENT_NUMBER_BLOCKS"("A4"."TABLESPACE_ID","A4"."RELATIVE_FNO","A4"."HEADER_BLOCK","A4"."SEGMENT_TYPE_ID","A4"."BUFFER_POOL_ID"
          ,"A4"."SEGMENT_FLAGS","A4"."SEGMENT_OBJD","A4"."BLOCKS"),"A4"."BLOCKS"))* "A4"."BLOCKSIZE" "BYTES",
          decode(bitand("A4"."SEGMENT_FLAGS",131072),131072,"A4"."BLOCKS",decode(bitand("A4"."SEGMENT_FLAGS",1),1,"SYS"."DBMS_SPACE_ADMIN"
          ."SEGMENT_NUMBER_BLOCKS"("A4"."TABLESPACE_ID","A4"."RELATIVE_FNO","A4"."HEADER_BLOCK","A4"."SEGMENT_TYPE_ID","A4"."BUFFER_POOL_ID"
          ,"A4"."SEGMENT_FLAGS","A4"."SEGMENT_OBJD","A4"."BLOCKS"),"A4"."BLOCKS"))"BLOCKS",
          decode(bitand("A4"."SEGMENT_FLAGS",131072),131072,"A4"."EXTENTS",decode(bitand("A4"."SEGMENT_FLAGS",1),1,"SYS"."DBMS_SPACE_ADMIN"
          ."SEGMENT_NUMBER_EXTENTS"("A4"."TABLESPACE_ID","A4"."RELATIVE_FNO","A4"."HEADER_BLOCK","A4"."SEGMENT_TYPE_ID","A4"."BUFFER_POOL_ID"
          ,"A4"."SEGMENT_FLAGS","A4"."SEGMENT_OBJD","A4"."EXTENTS"),"A4"."EXTENTS"))"EXTENTS",
          "A4"."INITIAL_EXTENT"    "INITIAL_EXTENT",
          "A4"."NEXT_EXTENT"       "NEXT_EXTENT",
          "A4"."MIN_EXTENTS"       "MIN_EXTENTS",
          "A4"."MAX_EXTENTS"       "MAX_EXTENTS",
          "A4"."MAX_SIZE"          "MAX_SIZE",
          "A4"."RETENTION"         "RETENTION",
          "A4"."MINRETENTION"      "MINRETENTION",
          "A4"."PCT_INCREASE"      "PCT_INCREASE",
          "A4"."FREELISTS"         "FREELISTS",
          "A4"."FREELIST_GROUPS"   "FREELIST_GROUPS",
          "A4"."RELATIVE_FNO"      "RELATIVE_FNO",
          decode("A4"."BUFFER_POOL_ID",1,'KEEP',2,'RECYCLE','DEFAULT')"BUFFER_POOL",
          decode("A4"."FLASH_CACHE",1,'KEEP',2,'NONE','DEFAULT')"FLASH_CACHE",
          decode("A4"."CELL_FLASH_CACHE",1,'KEEP',2,'NONE','DEFAULT')"CELL_FLASH_CACHE",
          decode(bitand("A4"."SEGMENT_FLAGS",4294967296),4294967296,'ENABLED','DISABLED')"INMEMORY",
          decode(bitand("A4"."SEGMENT_FLAGS",4294967296),4294967296,decode(bitand("A4"."SEGMENT_FLAGS",34359738368),34359738368,decode
          (bitand("A4"."SEGMENT_FLAGS",61572651155456),8796093022208,'LOW',17592186044416,'MEDIUM',35184372088832,'HIGH',52776558133248
          ,'CRITICAL','NONE'),'NONE'),null)"INMEMORY_PRIORITY",
          decode(bitand("A4"."SEGMENT_FLAGS",4294967296),4294967296,decode(bitand("A4"."SEGMENT_FLAGS",8589934592),8589934592,decode
          (bitand("A4"."SEGMENT_FLAGS",206158430208),68719476736,'BY ROWID RANGE',137438953472,'BY PARTITION',206158430208,'BY SUBPARTITION'
          ,0,'AUTO'),'UNKNOWN'),null)"INMEMORY_DISTRIBUTE",
          decode(bitand("A4"."SEGMENT_FLAGS",4294967296),4294967296,decode(bitand("A4"."SEGMENT_FLAGS",6597069766656),2199023255552
          ,'NO DUPLICATE',4398046511104,'DUPLICATE',6597069766656,'DUPLICATE ALL','UNKNOWN'),null)"INMEMORY_DUPLICATE",
          decode(bitand("A4"."SEGMENT_FLAGS",4294967296),4294967296,decode(bitand("A4"."SEGMENT_FLAGS",841813590016),17179869184,
          'NO MEMCOMPRESS',274877906944,'FOR DML',292057776128,'FOR QUERY LOW',549755813888,'FOR QUERY HIGH',566935683072,'FOR CAPACITY LOW'
          ,824633720832,'FOR CAPACITY HIGH','UNKNOWN'),null)"INMEMORY_COMPRESSION",
          decode(bitand("A4"."SEGMENT_FLAGS",4362862139015168),281474976710656,'DISABLED',703687441776640,'NO MEMCOMPRESS',1266637395197952
          ,'MEMCOMPRESS FOR QUERY',2392537302040576,'MEMCOMPRESS FOR CAPACITY',null)"CELLMEMORY"
        from
          (
            (select
              nvl("A112"."NAME",'SYS')"OWNER",
              "A111"."NAME"             "SEGMENT_NAME",
              "A111"."SUBNAME"          "PARTITION_NAME",
              "A109"."OBJECT_TYPE"      "SEGMENT_TYPE",
              "A109"."OBJECT_TYPE_ID"   "OBJECT_TYPE_ID",
              "A108"."TYPE#"            "SEGMENT_TYPE_ID",
              decode(bitand("A108"."SPARE1",2097408),2097152,'SECUREFILE',256,'ASSM','MSSM')"SEGMENT_SUBTYPE",
              "A110"."TS#"              "TABLESPACE_ID",
              "A110"."NAME"             "TABLESPACE_NAME",
              "A110"."BLOCKSIZE"        "BLOCKSIZE",
              "A107"."FILE#"            "HEADER_FILE",
              "A108"."BLOCK#"           "HEADER_BLOCK",
              nvl("A108"."BLOCKS",0)* "A110"."BLOCKSIZE" "BYTES",
              nvl("A108"."BLOCKS",0)"BLOCKS",
              "A108"."EXTENTS"          "EXTENTS",
              "A108"."INIEXTS" * "A110"."BLOCKSIZE" "INITIAL_EXTENT",
              "A108"."EXTSIZE" * "A110"."BLOCKSIZE" "NEXT_EXTENT",
              "A108"."MINEXTS"          "MIN_EXTENTS",
              "A108"."MAXEXTS"          "MAX_EXTENTS",
              decode(bitand("A108"."SPARE1",4194304),4194304,"A108"."BITMAPRANGES",null)"MAX_SIZE",
              to_char(decode(bitand("A108"."SPARE1",2097152),2097152,decode("A108"."LISTS",0,'NONE',1,'AUTO',2,'MIN',3,'MAX',4,'DEFAULT'
              ,'INVALID'),null))"RETENTION",
              decode(bitand("A108"."SPARE1",2097152),2097152,"A108"."GROUPS",null)"MINRETENTION",
              decode(bitand("A110"."FLAGS",3),1,to_number(null),"A108"."EXTPCT")"PCT_INCREASE",
              decode(bitand("A110"."FLAGS",32),32,to_number(null),decode("A108"."LISTS",0,1,"A108"."LISTS"))"FREELISTS",
              decode(bitand("A110"."FLAGS",32),32,to_number(null),decode("A108"."GROUPS",0,1,"A108"."GROUPS"))"FREELIST_GROUPS",
              "A108"."FILE#"            "RELATIVE_FNO",
              bitand("A108"."CACHEHINT",3)"BUFFER_POOL_ID",
              bitand("A108"."CACHEHINT",12)/ 4 "FLASH_CACHE",
              bitand("A108"."CACHEHINT",48)/ 16 "CELL_FLASH_CACHE",
              nvl("A108"."SPARE1",0)"SEGMENT_FLAGS",
              decode(bitand("A108"."SPARE1",1),1,"A108"."HWMINCR","A111"."DATAOBJ#")"SEGMENT_OBJD",
              "A109"."OBJECT_ID"        "SEGMENT_OBJN"
            from
              "SYS"."USER$"   "A112",
              "SYS"."OBJ$"    "A111",
              "SYS"."TS$"     "A110",
              (
                (select
                  decode(bitand("A93"."PROPERTY",8192),8192,'NESTED TABLE','TABLE')"OBJECT_TYPE",
                  2 "OBJECT_TYPE_ID",
                  5 "SEGMENT_TYPE_ID",
                  "A93"."OBJ#"     "OBJECT_ID",
                  "A93"."FILE#"    "HEADER_FILE",
                  "A93"."BLOCK#"   "HEADER_BLOCK",
                  "A93"."TS#"      "TS_NUMBER"
                from
                  "SYS"."TAB$" "A93"
                where
                  bitand("A93"."PROPERTY",1024)= 0
                )
                union all
                (select
                  'TABLE PARTITION' "OBJECT_TYPE",
                  19 "OBJECT_TYPE_ID",
                  5 "SEGMENT_TYPE_ID",
                  "A92"."OBJ#"     "OBJECT_ID",
                  "A92"."FILE#"    "HEADER_FILE",
                  "A92"."BLOCK#"   "HEADER_BLOCK",
                  "A92"."TS#"      "TS_NUMBER"
                from
                  "SYS"."TABPART$" "A92"
                )
                union all
                (select
                  'CLUSTER' "OBJECT_TYPE",
                  3 "OBJECT_TYPE_ID",
                  5 "SEGMENT_TYPE_ID",
                  "A91"."OBJ#"     "OBJECT_ID",
                  "A91"."FILE#"    "HEADER_FILE",
                  "A91"."BLOCK#"   "HEADER_BLOCK",
                  "A91"."TS#"      "TS_NUMBER"
                from
                  "SYS"."CLU$" "A91"
                )
                union all
                (select
                  decode("A90"."TYPE#",8,'LOBINDEX','INDEX')"OBJECT_TYPE",
                  1 "OBJECT_TYPE_ID",
                  6 "SEGMENT_TYPE_ID",
                  "A90"."OBJ#"     "OBJECT_ID",
                  "A90"."FILE#"    "HEADER_FILE",
                  "A90"."BLOCK#"   "HEADER_BLOCK",
                  "A90"."TS#"      "TS_NUMBER"
                from
                  "SYS"."IND$" "A90"
                where
                  "A90"."TYPE#" = 1
                  or "A90"."TYPE#" = 2
                  or "A90"."TYPE#" = 3
                  or "A90"."TYPE#" = 4
                  or "A90"."TYPE#" = 6
                  or "A90"."TYPE#" = 7
                  or "A90"."TYPE#" = 8
                  or "A90"."TYPE#" = 9
                )
                union all
                (select
                  'INDEX PARTITION' "OBJECT_TYPE",
                  20 "OBJECT_TYPE_ID",
                  6 "SEGMENT_TYPE_ID",
                  "A89"."OBJ#"     "OBJECT_ID",
                  "A89"."FILE#"    "HEADER_FILE",
                  "A89"."BLOCK#"   "HEADER_BLOCK",
                  "A89"."TS#"      "TS_NUMBER"
                from
                  "SYS"."INDPART$" "A89"
                )
                union all
                (select
                  'LOBSEGMENT' "OBJECT_TYPE",
                  21 "OBJECT_TYPE_ID",
                  8 "SEGMENT_TYPE_ID",
                  "A88"."LOBJ#"    "OBJECT_ID",
                  "A88"."FILE#"    "HEADER_FILE",
                  "A88"."BLOCK#"   "HEADER_BLOCK",
                  "A88"."TS#"      "TS_NUMBER"
                from
                  "SYS"."LOB$" "A88"
                where
                  bitand("A88"."PROPERTY",64)= 0
                  or bitand("A88"."PROPERTY",128)= 128
                )
                union all
                (select
                  'TABLE SUBPARTITION' "OBJECT_TYPE",
                  34 "OBJECT_TYPE_ID",
                  5 "SEGMENT_TYPE_ID",
                  "A87"."OBJ#"     "OBJECT_ID",
                  "A87"."FILE#"    "HEADER_FILE",
                  "A87"."BLOCK#"   "HEADER_BLOCK",
                  "A87"."TS#"      "TS_NUMBER"
                from
                  "SYS"."TABSUBPART$" "A87"
                )
                union all
                (select
                  'INDEX SUBPARTITION' "OBJECT_TYPE",
                  35 "OBJECT_TYPE_ID",
                  6 "SEGMENT_TYPE_ID",
                  "A86"."OBJ#"     "OBJECT_ID",
                  "A86"."FILE#"    "HEADER_FILE",
                  "A86"."BLOCK#"   "HEADER_BLOCK",
                  "A86"."TS#"      "TS_NUMBER"
                from
                  "SYS"."INDSUBPART$" "A86"
                )
                union all
                (select
                  decode("A85"."FRAGTYPE$",'P','LOB PARTITION','LOB SUBPARTITION')"OBJECT_TYPE",
                  decode("A85"."FRAGTYPE$",'P',40,41)"OBJECT_TYPE_ID",
                  8 "SEGMENT_TYPE_ID",
                  "A85"."FRAGOBJ#"   "OBJECT_ID",
                  "A85"."FILE#"      "HEADER_FILE",
                  "A85"."BLOCK#"     "HEADER_BLOCK",
                  "A85"."TS#"        "TS_NUMBER"
                from
                  "SYS"."LOBFRAG$" "A85"
                )
              )"A109",
              "SYS"."SEG$"    "A108",
              "SYS"."FILE$"   "A107"
            where
              "A108"."FILE#" = "A109"."HEADER_FILE"
              and "A108"."BLOCK#" = "A109"."HEADER_BLOCK"
              and "A108"."TS#" = "A109"."TS_NUMBER"
              and "A108"."TS#" = "A110"."TS#"
              and "A111"."OBJ#" = "A109"."OBJECT_ID"
              and "A111"."OWNER#" = "A112"."USER#"(+)
              and "A108"."TYPE#" = "A109"."SEGMENT_TYPE_ID"
              and "A111"."TYPE#" = "A109"."OBJECT_TYPE_ID"
              and "A108"."TS#" = "A107"."TS#"
              and "A108"."FILE#" = "A107"."RELFILE#"
              and("A111"."TYPE#" <> 1
                  and "A111"."TYPE#" <> 2
                  and "A111"."TYPE#" <> 6
                  or "A111"."TYPE#" = 1
                  and not exists(
                select
                  1 "1"
                from
                  "SYS"."IND$"   "A117",
                  "SYS"."TAB$"   "A116",
                  "SYS"."OBJ$"   "A115"
                where
                  "A117"."OBJ#" = "A111"."OBJ#"
                  and "A115"."OBJ#" = "A117"."BO#"
                  and "A115"."TYPE#" = 2
                  and "A117"."BO#" = "A116"."OBJ#"
                  and bitand("A116"."PROPERTY",power(2,65))= power(2,65)
              )
                  or "A111"."TYPE#" = 2
                  and 1 =(
                select
                  1 "1"
                from
                  "SYS"."TAB$" "A114"
                where
                  "A114"."OBJ#" = "A111"."OBJ#"
                  and bitand("A114"."PROPERTY",power(2,65))= 0
              )
                  or "A111"."TYPE#" = 6
                  and 1 =(
                select
                  1 "1"
                from
                  "SYS"."SEQ$" "A113"
                where
                  "A113"."OBJ#" = "A111"."OBJ#"
                  and bitand("A113"."FLAGS",1024)= 0
              ))
            )
            union all
            (select
              nvl("A106"."NAME",'SYS')"OWNER",
              "A104"."NAME"        "SEGMENT_NAME",
              null "PARTITION_NAME",
              decode("A103"."TYPE#",1,'ROLLBACK',10,'TYPE2 UNDO')"SEGMENT_TYPE",
              0 "OBJECT_TYPE_ID",
              "A103"."TYPE#"       "SEGMENT_TYPE_ID",
              null "SEGMENT_SUBTYPE",
              "A105"."TS#"         "TABLESPACE_ID",
              "A105"."NAME"        "TABLESPACE_NAME",
              "A105"."BLOCKSIZE"   "BLOCKSIZE",
              "A102"."FILE#"       "HEADER_FILE",
              "A103"."BLOCK#"      "HEADER_BLOCK",
              nvl("A103"."BLOCKS",0)* "A105"."BLOCKSIZE" "BYTES",
              nvl("A103"."BLOCKS",0)"BLOCKS",
              "A103"."EXTENTS"     "EXTENTS",
              "A103"."INIEXTS" * "A105"."BLOCKSIZE" "INITIAL_EXTENT",
              "A103"."EXTSIZE" * "A105"."BLOCKSIZE" "NEXT_EXTENT",
              "A103"."MINEXTS"     "MIN_EXTENTS",
              "A103"."MAXEXTS"     "MAX_EXTENTS",
              decode(bitand("A103"."SPARE1",4194304),4194304,"A103"."BITMAPRANGES",null)"MAX_SIZE",
              null "RETENTION",
              null "MINRETENTION",
              "A103"."EXTPCT"      "PCT_INCREASE",
              decode(bitand("A105"."FLAGS",32),32,to_number(null),decode("A103"."LISTS",0,1,"A103"."LISTS"))"FREELISTS",
              decode(bitand("A105"."FLAGS",32),32,to_number(null),decode("A103"."GROUPS",0,1,"A103"."GROUPS"))"FREELIST_GROUPS",
              "A103"."FILE#"       "RELATIVE_FNO",
              bitand("A103"."CACHEHINT",3)"BUFFER_POOL_ID",
              bitand("A103"."CACHEHINT",12)/ 4 "FLASH_CACHE",
              bitand("A103"."CACHEHINT",48)/ 16 "CELL_FLASH_CACHE",
              nvl("A103"."SPARE1",0)"SEGMENT_FLAGS",
              "A104"."US#"         "SEGMENT_OBJD",
              0 "SEGMENT_OBJN"
            from
              "SYS"."USER$"   "A106",
              "SYS"."TS$"     "A105",
              "SYS"."UNDO$"   "A104",
              "SYS"."SEG$"    "A103",
              "SYS"."FILE$"   "A102"
            where
              "A103"."FILE#" = "A104"."FILE#"
              and "A103"."BLOCK#" = "A104"."BLOCK#"
              and "A103"."TS#" = "A104"."TS#"
              and "A103"."TS#" = "A105"."TS#"
              and "A103"."USER#" = "A106"."USER#"(+)
              and("A103"."TYPE#" = 1
                  or "A103"."TYPE#" = 10)
              and "A104"."STATUS$" <> 1
              and "A104"."TS#" = "A102"."TS#"
              and "A104"."FILE#" = "A102"."RELFILE#"
            )
            union all
            (select
              nvl("A101"."NAME",'SYS')"OWNER",
              to_char("A98"."FILE#")
              || '.'
              || to_char("A99"."BLOCK#")"SEGMENT_NAME",
              null "PARTITION_NAME",
              decode("A99"."TYPE#",2,'DEFERRED ROLLBACK',3,'TEMPORARY',4,'CACHE',9,'SPACE HEADER','UNDEFINED')"SEGMENT_TYPE",
              0 "OBJECT_TYPE_ID",
              "A99"."TYPE#"        "SEGMENT_TYPE_ID",
              null "SEGMENT_SUBTYPE",
              "A100"."TS#"         "TABLESPACE_ID",
              "A100"."NAME"        "TABLESPACE_NAME",
              "A100"."BLOCKSIZE"   "BLOCKSIZE",
              "A98"."FILE#"        "HEADER_FILE",
              "A99"."BLOCK#"       "HEADER_BLOCK",
              nvl("A99"."BLOCKS",0)* "A100"."BLOCKSIZE" "BYTES",
              nvl("A99"."BLOCKS",0)"BLOCKS",
              "A99"."EXTENTS"      "EXTENTS",
              "A99"."INIEXTS" * "A100"."BLOCKSIZE" "INITIAL_EXTENT",
              "A99"."EXTSIZE" * "A100"."BLOCKSIZE" "NEXT_EXTENT",
              "A99"."MINEXTS"      "MIN_EXTENTS",
              "A99"."MAXEXTS"      "MAX_EXTENTS",
              decode(bitand("A99"."SPARE1",4194304),4194304,"A99"."BITMAPRANGES",null)"MAX_SIZE",
              null "RETENTION",
              null "MINRETENTION",
              decode(bitand("A100"."FLAGS",3),1,to_number(null),"A99"."EXTPCT")"PCT_INCREASE",
              decode(bitand("A100"."FLAGS",32),32,to_number(null),decode("A99"."LISTS",0,1,"A99"."LISTS"))"FREELISTS",
              decode(bitand("A100"."FLAGS",32),32,to_number(null),decode("A99"."GROUPS",0,1,"A99"."GROUPS"))"FREELIST_GROUPS",
              "A99"."FILE#"        "RELATIVE_FNO",
              bitand("A99"."CACHEHINT",3)"BUFFER_POOL_ID",
              bitand("A99"."CACHEHINT",12)/ 4 "FLASH_CACHE",
              bitand("A99"."CACHEHINT",48)/ 16 "CELL_FLASH_CACHE",
              nvl("A99"."SPARE1",0)"SEGMENT_FLAGS",
              "A99"."HWMINCR"      "SEGMENT_OBJD",
              0 "SEGMENT_OBJN"
            from
              "SYS"."USER$"   "A101",
              "SYS"."TS$"     "A100",
              "SYS"."SEG$"    "A99",
              "SYS"."FILE$"   "A98"
            where
              "A99"."TS#" = "A100"."TS#"
              and "A99"."USER#" = "A101"."USER#"(+)
              and "A99"."TYPE#" <> 1
              and "A99"."TYPE#" <> 5
              and "A99"."TYPE#" <> 6
              and "A99"."TYPE#" <> 8
              and "A99"."TYPE#" <> 10
              and "A99"."TYPE#" <> 11
              and "A99"."TS#" = "A98"."TS#"
              and "A99"."FILE#" = "A98"."RELFILE#"
            )
            union all
            (select
              nvl("A97"."NAME",'SYS')"OWNER",
              'HEATMAP' "SEGMENT_NAME",
              null "PARTITION_NAME",
              'SYSTEM STATISTICS' "SEGMENT_TYPE",
              0 "OBJECT_TYPE_ID",
              "A95"."TYPE#"       "SEGMENT_TYPE_ID",
              null "SEGMENT_SUBTYPE",
              "A96"."TS#"         "TABLESPACE_ID",
              "A96"."NAME"        "TABLESPACE_NAME",
              "A96"."BLOCKSIZE"   "BLOCKSIZE",
              "A94"."FILE#"       "HEADER_FILE",
              "A95"."BLOCK#"      "HEADER_BLOCK",
              nvl("A95"."BLOCKS",0)* "A96"."BLOCKSIZE" "BYTES",
              nvl("A95"."BLOCKS",0)"BLOCKS",
              "A95"."EXTENTS"     "EXTENTS",
              "A95"."INIEXTS" * "A96"."BLOCKSIZE" "INITIAL_EXTENT",
              "A95"."EXTSIZE" * "A96"."BLOCKSIZE" "NEXT_EXTENT",
              "A95"."MINEXTS"     "MIN_EXTENTS",
              "A95"."MAXEXTS"     "MAX_EXTENTS",
              decode(bitand("A95"."SPARE1",4194304),4194304,"A95"."BITMAPRANGES",null)"MAX_SIZE",
              null "RETENTION",
              null "MINRETENTION",
              decode(bitand("A96"."FLAGS",3),1,to_number(null),"A95"."EXTPCT")"PCT_INCREASE",
              decode(bitand("A96"."FLAGS",32),32,to_number(null),decode("A95"."LISTS",0,1,"A95"."LISTS"))"FREELISTS",
              decode(bitand("A96"."FLAGS",32),32,to_number(null),decode("A95"."GROUPS",0,1,"A95"."GROUPS"))"FREELIST_GROUPS",
              "A95"."FILE#"       "RELATIVE_FNO",
              bitand("A95"."CACHEHINT",3)"BUFFER_POOL_ID",
              bitand("A95"."CACHEHINT",12)/ 4 "FLASH_CACHE",
              bitand("A95"."CACHEHINT",48)/ 16 "CELL_FLASH_CACHE",
              nvl("A95"."SPARE1",0)"SEGMENT_FLAGS",
              "A95"."HWMINCR"     "SEGMENT_OBJD",
              0 "SEGMENT_OBJN"
            from
              "SYS"."USER$"   "A97",
              "SYS"."TS$"     "A96",
              "SYS"."SEG$"    "A95",
              "SYS"."FILE$"   "A94"
            where
              "A95"."TS#" = "A96"."TS#"
              and "A95"."USER#" = "A97"."USER#"(+)
              and "A95"."TYPE#" = 11
              and "A95"."TS#" = "A94"."TS#"
              and "A95"."FILE#" = "A94"."RELFILE#"
            )
          )"A4"
      )"A3",
      (
        select
          "A7"."NAME"               "OWNER",
          "A8"."NAME"               "OBJECT_NAME",
          "A8"."SUBNAME"            "SUBOBJECT_NAME",
          "A8"."OBJ#"               "OBJECT_ID",
          "A8"."DATAOBJ#"           "DATA_OBJECT_ID",
          decode("A8"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION'
          ,9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB'
          ,22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR'
          ,34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,
                 case(
                   select
                     bitand("A68"."XPFLAGS",8388608 + 34359738368)"BITAND(S.XPFLAGS,8388608+34359738368)"
                   from
                     "SYS"."SUM$" "A68"
                   where
                     "A68"."OBJ#" = "A8"."OBJ#"
                 )
                   when 8388608       then
                     'REWRITE EQUIVALENCE'
                   when 34359738368   then
                     'MATERIALIZED ZONEMAP'
                   else
                     'MATERIALIZED VIEW'
                 end,43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',55,'XML SCHEMA',56,'JAVA DATA'
                 ,57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,
                 'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL'
                 ,92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION',
                 114,'SQL TRANSLATION PROFILE',115,'UNIFIED AUDIT POLICY',144,'MINING MODEL PARTITION',148,'LOCKDOWN PROFILE',150
                 ,'HIERARCHY',151,'ATTRIBUTE DIMENSION',152,'ANALYTIC VIEW','UNDEFINED')"OBJECT_TYPE",
          "A8"."CTIME"              "CREATED",
          "A8"."MTIME"              "LAST_DDL_TIME",
          to_char("A8"."STIME",'YYYY-MM-DD:HH24:MI:SS')"TIMESTAMP",
          decode("A8"."STATUS",0,'N/A',1,'VALID','INVALID')"STATUS",
          decode(bitand("A8"."FLAGS",2),0,'N',2,'Y','N')"TEMPORARY",
          decode(bitand("A8"."FLAGS",4),0,'N',4,'Y','N')"GENERATED",
          decode(bitand("A8"."FLAGS",16),0,'N',16,'Y','N')"SECONDARY",
          "A8"."NAMESPACE"          "NAMESPACE",
          "A8"."DEFINING_EDITION"   "EDITION_NAME",
          decode(bitand("A8"."FLAGS",65536 + 131072 + 4294967296),4294967296 + 65536,'EXTENDED DATA LINK',65536,'METADATA LINK',131072
          ,'DATA LINK','NONE')"SHARING",
          case
            when("A8"."TYPE#" = 4
                 or "A8"."TYPE#" = 5
                 or "A8"."TYPE#" = 7
                 or "A8"."TYPE#" = 8
                 or "A8"."TYPE#" = 9
                 or "A8"."TYPE#" = 11
                 or "A8"."TYPE#" = 12
                 or "A8"."TYPE#" = 13
                 or "A8"."TYPE#" = 14
                 or "A8"."TYPE#" = 22
                 or "A8"."TYPE#" = 87
                 or "A8"."TYPE#" = 114)then
              decode(bitand("A8"."FLAGS",1048576),0,'Y',1048576,'N','Y')
            else
              null
          end "EDITIONABLE",
          decode(bitand("A8"."FLAGS",4194304),4194304,'Y','N')"ORACLE_MAINTAINED",
          decode(bitand("A8"."FLAGS",134217728),134217728,'Y','N')"APPLICATION",
          case
            when("A8"."TYPE#" = 2
                 or "A8"."TYPE#" = 4
                 or "A8"."TYPE#" = 7
                 or "A8"."TYPE#" = 8
                 or "A8"."TYPE#" = 9
                 or "A8"."TYPE#" = 12
                 or "A8"."TYPE#" = 13)then
              nls_collation_name(nvl("A8"."DFLCOLLID",16382))
            when("A8"."TYPE#" = 42
                 and exists(
              select
                1 "1"
              from
                "SYS"."SUM$" "A67"
              where
                "A67"."OBJ#" = "A8"."OBJ#"
                and bitand("A67"."XPFLAGS",8388608 + 34359738368)= 0
            ))then
              nls_collation_name(nvl("A8"."DFLCOLLID",16382))
            else
              null
          end "DEFAULT_COLLATION",
          decode(bitand("A8"."FLAGS",2684354560),0,'N',2147483648,'Y',536870912,'Y',2684354560,'Y','N')"DUPLICATED",
          decode(bitand("A8"."FLAGS",1073741824),0,'N',1073741824,'Y','N')"SHARDED",
          "A8"."CREAPPID"           "CREATED_APPID",
          "A8"."CREVERID"           "CREATED_VSNID",
          "A8"."MODAPPID"           "MODIFIED_APPID",
          "A8"."MODVERID"           "MODIFIED_VSNID"
        from
          (
            select
              "A32"."OBJ#"          "OBJ#",
              "A32"."DATAOBJ#"      "DATAOBJ#",
              "A32"."OWNER#"        "DEFINING_OWNER#",
              "A32"."NAME"          "NAME",
              "A32"."NAMESPACE"     "NAMESPACE",
              "A32"."SUBNAME"       "SUBNAME",
              "A32"."TYPE#"         "TYPE#",
              "A32"."CTIME"         "CTIME",
              "A32"."MTIME"         "MTIME",
              "A32"."STIME"         "STIME",
              "A32"."STATUS"        "STATUS",
              "A32"."REMOTEOWNER"   "REMOTEOWNER",
              "A32"."LINKNAME"      "LINKNAME",
              "A32"."FLAGS"         "FLAGS",
              "A32"."OID$"          "OID$",
              "A32"."SPARE1"        "SPARE1",
              "A32"."SPARE2"        "SPARE2",
              "A32"."SPARE3"        "SPARE3",
              "A32"."SPARE4"        "SPARE4",
              "A32"."SPARE5"        "SPARE5",
              "A32"."SPARE6"        "SPARE6",
              "A32"."SIGNATURE"     "SIGNATURE",
              "A32"."SPARE7"        "SPARE7",
              "A32"."SPARE8"        "SPARE8",
              "A32"."SPARE9"        "SPARE9",
              "A32"."DFLCOLLID"     "DFLCOLLID",
              "A32"."CREAPPID"      "CREAPPID",
              "A32"."CREVERID"      "CREVERID",
              "A32"."CREPATCHID"    "CREPATCHID",
              "A32"."MODAPPID"      "MODAPPID",
              "A32"."MODVERID"      "MODVERID",
              "A32"."MODPATCHID"    "MODPATCHID",
              "A32"."SPARE10"       "SPARE10",
              "A32"."SPARE11"       "SPARE11",
              "A32"."SPARE12"       "SPARE12",
              "A32"."SPARE13"       "SPARE13",
              "A32"."SPARE14"       "SPARE14",
              "A32"."SPARE3"        "OWNER#",
              case
                when("A32"."TYPE#" <> all(
                  select
                    "A38"."TYPE#" "TYPE#"
                  from
                    "SYS"."USER_EDITIONING$" "A38"
                  where
                    "A38"."USER#" = "A32"."SPARE3"
                )
                     or bitand("A32"."FLAGS",1048576)= 1048576
                     or bitand("A31"."SPARE1",16)= 0)then
                  null
                when "A31"."TYPE#" = 2 then
                  (
                    select
                      "A37"."NAME" "NAME"
                    from
                      "SYS"."OBJ$" "A37"
                    where
                      "A37"."OBJ#" = "A31"."SPARE2"
                  )
                else
                  'ORA$BASE'
              end "DEFINING_EDITION"
            from
              "SYS"."OBJ$"    "A32",
              "SYS"."USER$"   "A31"
            where
              "A32"."OWNER#" = "A31"."USER#"
              and("A32"."TYPE#" <> all(
                select
                  "A36"."TYPE#" "TYPE#"
                from
                  "SYS"."USER_EDITIONING$" "A36"
                where
                  "A36"."USER#" = "A32"."SPARE3"
              )
                  and "A32"."TYPE#" <> 88
                  or bitand("A32"."FLAGS",1048576)= 1048576
                  or bitand("A31"."SPARE1",16)= 0
                  or "A32"."TYPE#" = any(
                select
                  "A35"."TYPE#" "TYPE#"
                from
                  "SYS"."USER_EDITIONING$" "A35"
                where
                  "A35"."USER#" = "A32"."SPARE3"
              )
                  and("A31"."TYPE#" <> 2
                      and sys_context('userenv','current_edition_name')= 'ORA$BASE'
                      or "A31"."TYPE#" = 2
                      and "A31"."SPARE2" = sys_context('userenv','current_edition_id')
                      or exists(
                select
                  1 "1"
                from
                  "SYS"."OBJ$"    "A34",
                  "SYS"."USER$"   "A33"
                where
                  "A34"."TYPE#" = 88
                  and "A34"."DATAOBJ#" = "A32"."OBJ#"
                  and "A34"."OWNER#" = "A33"."USER#"
                  and "A33"."TYPE#" = 2
                  and "A33"."SPARE2" = sys_context('userenv','current_edition_id')
              )))
          )"A8",
          "SYS"."USER$" "A7"
        where
          "A8"."OWNER#" = "A7"."USER#"
          and "A8"."LINKNAME" is null
          and("A8"."TYPE#" <> 1
              and "A8"."TYPE#" <> 10
              or "A8"."TYPE#" = 1
              and 1 =(
            select
              1 "1"
            from
              "SYS"."IND$" "A66"
            where
              "A66"."OBJ#" = "A8"."OBJ#"
              and("A66"."TYPE#" = 1
                  or "A66"."TYPE#" = 2
                  or "A66"."TYPE#" = 3
                  or "A66"."TYPE#" = 4
                  or "A66"."TYPE#" = 6
                  or "A66"."TYPE#" = 7
                  or "A66"."TYPE#" = 9)
          ))
          and "A8"."NAME" <> '_NEXT_OBJECT'
          and "A8"."NAME" <> '_default_auditing_options_'
          and bitand("A8"."FLAGS",128)= 0
          and("A8"."TYPE#" <> 1
              and "A8"."TYPE#" <> 2
              and "A8"."TYPE#" <> 6
              or "A8"."TYPE#" = 1
              and not exists(
            select
              1 "1"
            from
              "SYS"."IND$"   "A65",
              "SYS"."TAB$"   "A64",
              "SYS"."OBJ$"   "A63"
            where
              "A65"."OBJ#" = "A8"."OBJ#"
              and "A63"."OBJ#" = "A65"."BO#"
              and "A63"."TYPE#" = 2
              and "A65"."BO#" = "A64"."OBJ#"
              and bitand("A64"."PROPERTY",power(2,65))= power(2,65)
          )
              or "A8"."TYPE#" = 2
              and 1 =(
            select
              1 "1"
            from
              "SYS"."TAB$" "A62"
            where
              "A62"."OBJ#" = "A8"."OBJ#"
              and(bitand("A62"."PROPERTY",power(2,65))= 0
                  or "A62"."PROPERTY" is null)
          )
              or "A8"."TYPE#" = 6
              and 1 =(
            select
              1 "1"
            from
              "SYS"."SEQ$" "A61"
            where
              "A61"."OBJ#" = "A8"."OBJ#"
              and(bitand("A61"."FLAGS",1024)= 0
                  or "A61"."FLAGS" is null)
          ))
          and("A8"."OWNER#" = userenv('SCHEMAID')
              or "A8"."OWNER#" = 1
              or "A8"."TYPE#" <> 7
              and "A8"."TYPE#" <> 8
              and "A8"."TYPE#" <> 9
              and "A8"."TYPE#" <> 11
              and "A8"."TYPE#" <> 12
              and "A8"."TYPE#" <> 13
              and "A8"."TYPE#" <> 14
              and "A8"."TYPE#" <> 28
              and "A8"."TYPE#" <> 29
              and "A8"."TYPE#" <> 30
              and "A8"."TYPE#" <> 56
              and "A8"."TYPE#" <> 93
              and "A8"."OBJ#" = any(
            select
              "A60"."OBJ#" "OBJ#"
            from
              "SYS"."OBJAUTH$" "A60"
            where
              "A60"."GRANTEE#" = any(
                select
                  "A69"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A30"."ADDR"       "ADDR",
                      "A30"."INDX"       "INDX",
                      "A30"."INST_ID"    "INST_ID",
                      "A30"."CON_ID"     "CON_ID",
                      "A30"."KZSROROL"   "KZSROROL",
                      "A30"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A30"
                    where
                      "A30"."CON_ID" = 0
                      or "A30"."CON_ID" = 3
                  )"A69"
              )
              and("A60"."PRIVILEGE#" = 3
                  or "A60"."PRIVILEGE#" = 6
                  or "A60"."PRIVILEGE#" = 7
                  or "A60"."PRIVILEGE#" = 9
                  or "A60"."PRIVILEGE#" = 10
                  or "A60"."PRIVILEGE#" = 12
                  or "A60"."PRIVILEGE#" = 11
                  or "A60"."PRIVILEGE#" = 16
                  or "A60"."PRIVILEGE#" = 17
                  or "A60"."PRIVILEGE#" = 18)
          )
              or("A8"."TYPE#" = 7
                 or "A8"."TYPE#" = 8
                 or "A8"."TYPE#" = 9
                 or "A8"."TYPE#" = 28
                 or "A8"."TYPE#" = 29
                 or "A8"."TYPE#" = 30
                 or "A8"."TYPE#" = 56)
              and(exists(
            select
              null "NULL"
            from
              "SYS"."OBJAUTH$" "A59"
            where
              "A59"."OBJ#" = "A8"."OBJ#"
              and "A59"."GRANTEE#" = any(
                select
                  "A70"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A29"."ADDR"       "ADDR",
                      "A29"."INDX"       "INDX",
                      "A29"."INST_ID"    "INST_ID",
                      "A29"."CON_ID"     "CON_ID",
                      "A29"."KZSROROL"   "KZSROROL",
                      "A29"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A29"
                    where
                      "A29"."CON_ID" = 0
                      or "A29"."CON_ID" = 3
                  )"A70"
              )
              and("A59"."PRIVILEGE#" = 12
                  or "A59"."PRIVILEGE#" = 26)
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or "A8"."TYPE#" = 19
              and(exists(
            select
              "A58"."BO#" "BO#"
            from
              "SYS"."TABPART$" "A58"
            where
              "A58"."OBJ#" = "A8"."OBJ#"
              and "A58"."BO#" = any(
                select
                  "A71"."OBJ#" "OBJ#"
                from
                  "SYS"."OBJAUTH$" "A71"
                where
                  "A71"."GRANTEE#" = any(
                    select
                      "A72"."KZSROROL" "KZSROROL"
                    from
                      (
                        select
                          "A28"."ADDR"       "ADDR",
                          "A28"."INDX"       "INDX",
                          "A28"."INST_ID"    "INST_ID",
                          "A28"."CON_ID"     "CON_ID",
                          "A28"."KZSROROL"   "KZSROROL",
                          "A28"."KZSROFLG"   "KZSROFLG"
                        from
                          "SYS"."X$KZSRO" "A28"
                        where
                          "A28"."CON_ID" = 0
                          or "A28"."CON_ID" = 3
                      )"A72"
                  )
                  and("A71"."PRIVILEGE#" = 9
                      or "A71"."PRIVILEGE#" = 17)
              )
          )
                  or exists(
            select
              "A57"."BO#" "BO#"
            from
              "SYS"."TABCOMPART$" "A57"
            where
              "A57"."OBJ#" = "A8"."OBJ#"
              and "A57"."BO#" = any(
                select
                  "A73"."OBJ#" "OBJ#"
                from
                  "SYS"."OBJAUTH$" "A73"
                where
                  "A73"."GRANTEE#" = any(
                    select
                      "A74"."KZSROROL" "KZSROROL"
                    from
                      (
                        select
                          "A27"."ADDR"       "ADDR",
                          "A27"."INDX"       "INDX",
                          "A27"."INST_ID"    "INST_ID",
                          "A27"."CON_ID"     "CON_ID",
                          "A27"."KZSROROL"   "KZSROROL",
                          "A27"."KZSROFLG"   "KZSROFLG"
                        from
                          "SYS"."X$KZSRO" "A27"
                        where
                          "A27"."CON_ID" = 0
                          or "A27"."CON_ID" = 3
                      )"A74"
                  )
                  and("A73"."PRIVILEGE#" = 9
                      or "A73"."PRIVILEGE#" = 17)
              )
          ))
              or "A8"."TYPE#" = 34
              and exists(
            select
              "A55"."BO#" "BO#"
            from
              "SYS"."TABSUBPART$"   "A56",
              "SYS"."TABCOMPART$"   "A55"
            where
              "A56"."OBJ#" = "A8"."OBJ#"
              and "A56"."POBJ#" = "A55"."OBJ#"
              and "A55"."BO#" = any(
                select
                  "A75"."OBJ#" "OBJ#"
                from
                  "SYS"."OBJAUTH$" "A75"
                where
                  "A75"."GRANTEE#" = any(
                    select
                      "A76"."KZSROROL" "KZSROROL"
                    from
                      (
                        select
                          "A26"."ADDR"       "ADDR",
                          "A26"."INDX"       "INDX",
                          "A26"."INST_ID"    "INST_ID",
                          "A26"."CON_ID"     "CON_ID",
                          "A26"."KZSROROL"   "KZSROROL",
                          "A26"."KZSROFLG"   "KZSROFLG"
                        from
                          "SYS"."X$KZSRO" "A26"
                        where
                          "A26"."CON_ID" = 0
                          or "A26"."CON_ID" = 3
                      )"A76"
                  )
                  and("A75"."PRIVILEGE#" = 9
                      or "A75"."PRIVILEGE#" = 17)
              )
          )
              or "A8"."TYPE#" = 12
              and(exists(
            select
              null "NULL"
            from
              "SYS"."TRIGGER$"   "A54",
              "SYS"."OBJAUTH$"   "A53"
            where
              bitand("A54"."PROPERTY",24)= 0
              and "A54"."OBJ#" = "A8"."OBJ#"
              and "A53"."OBJ#" = "A54"."BASEOBJECT"
              and "A53"."GRANTEE#" = any(
                select
                  "A77"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A25"."ADDR"       "ADDR",
                      "A25"."INDX"       "INDX",
                      "A25"."INST_ID"    "INST_ID",
                      "A25"."CON_ID"     "CON_ID",
                      "A25"."KZSROROL"   "KZSROROL",
                      "A25"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A25"
                    where
                      "A25"."CON_ID" = 0
                      or "A25"."CON_ID" = 3
                  )"A77"
              )
              and "A53"."PRIVILEGE#" = 26
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or "A8"."TYPE#" = 11
              and(exists(
            select
              null "NULL"
            from
              (
                select
                  "A24"."OBJ#"     "OBJ#",
                  "A24"."NAME"     "NAME",
                  "A24"."TYPE#"    "TYPE#",
                  "A24"."SPARE3"   "OWNER#"
                from
                  "SYS"."OBJ$"    "A24",
                  "SYS"."USER$"   "A23"
                where
                  "A24"."OWNER#" = "A23"."USER#"
                  and "A24"."TYPE#" <> 88
              )"A52",
              "SYS"."DEPENDENCY$"   "A51",
              "SYS"."OBJAUTH$"      "A50"
            where
              "A52"."OWNER#" = "A8"."OWNER#"
              and "A52"."NAME" = "A8"."NAME"
              and "A52"."TYPE#" = 9
              and "A51"."D_OBJ#" = "A8"."OBJ#"
              and "A51"."P_OBJ#" = "A52"."OBJ#"
              and "A50"."OBJ#" = "A52"."OBJ#"
              and "A50"."GRANTEE#" = any(
                select
                  "A78"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A22"."ADDR"       "ADDR",
                      "A22"."INDX"       "INDX",
                      "A22"."INST_ID"    "INST_ID",
                      "A22"."CON_ID"     "CON_ID",
                      "A22"."KZSROROL"   "KZSROROL",
                      "A22"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A22"
                    where
                      "A22"."CON_ID" = 0
                      or "A22"."CON_ID" = 3
                  )"A78"
              )
              and "A50"."PRIVILEGE#" = 26
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or "A8"."TYPE#" = 1
              and exists(
            select
              "A49"."OBJ#" "OBJ#"
            from
              "SYS"."IND$" "A49"
            where
              "A49"."OBJ#" = "A8"."OBJ#"
              and exists(
                select
                  null "NULL"
                from
                  "SYS"."OBJAUTH$" "A79"
                where
                  "A79"."OBJ#" = "A49"."BO#"
                  and "A79"."GRANTEE#" = any(
                    select
                      "A80"."KZSROROL" "KZSROROL"
                    from
                      (
                        select
                          "A21"."ADDR"       "ADDR",
                          "A21"."INDX"       "INDX",
                          "A21"."INST_ID"    "INST_ID",
                          "A21"."CON_ID"     "CON_ID",
                          "A21"."KZSROROL"   "KZSROROL",
                          "A21"."KZSROFLG"   "KZSROFLG"
                        from
                          "SYS"."X$KZSRO" "A21"
                        where
                          "A21"."CON_ID" = 0
                          or "A21"."CON_ID" = 3
                      )"A80"
                  )
              )
          )
              or "A8"."TYPE#" = 13
              and(exists(
            select
              null "NULL"
            from
              "SYS"."OBJAUTH$" "A48"
            where
              "A48"."OBJ#" = "A8"."OBJ#"
              and "A48"."GRANTEE#" = any(
                select
                  "A81"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A20"."ADDR"       "ADDR",
                      "A20"."INDX"       "INDX",
                      "A20"."INST_ID"    "INST_ID",
                      "A20"."CON_ID"     "CON_ID",
                      "A20"."KZSROROL"   "KZSROROL",
                      "A20"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A20"
                    where
                      "A20"."CON_ID" = 0
                      or "A20"."CON_ID" = 3
                  )"A81"
              )
              and("A48"."PRIVILEGE#" = 12
                  or "A48"."PRIVILEGE#" = 26)
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or "A8"."TYPE#" = 14
              and(exists(
            select
              null "NULL"
            from
              (
                select
                  "A19"."OBJ#"     "OBJ#",
                  "A19"."NAME"     "NAME",
                  "A19"."TYPE#"    "TYPE#",
                  "A19"."SPARE3"   "OWNER#"
                from
                  "SYS"."OBJ$"    "A19",
                  "SYS"."USER$"   "A18"
                where
                  "A19"."OWNER#" = "A18"."USER#"
                  and "A19"."TYPE#" <> 88
              )"A47",
              "SYS"."DEPENDENCY$"   "A46",
              "SYS"."OBJAUTH$"      "A45"
            where
              "A47"."OWNER#" = "A8"."OWNER#"
              and "A47"."NAME" = "A8"."NAME"
              and "A47"."TYPE#" = 13
              and "A46"."D_OBJ#" = "A8"."OBJ#"
              and "A46"."P_OBJ#" = "A47"."OBJ#"
              and "A45"."OBJ#" = "A47"."OBJ#"
              and "A45"."GRANTEE#" = any(
                select
                  "A82"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A17"."ADDR"       "ADDR",
                      "A17"."INDX"       "INDX",
                      "A17"."INST_ID"    "INST_ID",
                      "A17"."CON_ID"     "CON_ID",
                      "A17"."KZSROROL"   "KZSROROL",
                      "A17"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A17"
                    where
                      "A17"."CON_ID" = 0
                      or "A17"."CON_ID" = 3
                  )"A82"
              )
              and "A45"."PRIVILEGE#" = 26
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or("A8"."TYPE#" = 1
                 or "A8"."TYPE#" = 2
                 or "A8"."TYPE#" = 3
                 or "A8"."TYPE#" = 4
                 or "A8"."TYPE#" = 5
                 or "A8"."TYPE#" = 6
                 or "A8"."TYPE#" = 19
                 or "A8"."TYPE#" = 20
                 or "A8"."TYPE#" = 22
                 or "A8"."TYPE#" = 23
                 or "A8"."TYPE#" = 32
                 or "A8"."TYPE#" = 33
                 or "A8"."TYPE#" = 34
                 or "A8"."TYPE#" = 35
                 or "A8"."TYPE#" = 42
                 or "A8"."TYPE#" = 44
                 or "A8"."TYPE#" = 46
                 or "A8"."TYPE#" = 48
                 or "A8"."TYPE#" = 59
                 or "A8"."TYPE#" = 62
                 or "A8"."TYPE#" = 66
                 or "A8"."TYPE#" = 67
                 or "A8"."TYPE#" = 68
                 or "A8"."TYPE#" = 79
                 or "A8"."TYPE#" = 81
                 or "A8"."TYPE#" = 82
                 or "A8"."TYPE#" = 87
                 or "A8"."TYPE#" = 92
                 or "A8"."TYPE#" = 94
                 or "A8"."TYPE#" = 95
                 or "A8"."TYPE#" = 100)
              and ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1
              or "A8"."TYPE#" = 55
              and 1 =(
            select /*+ NO_MERGE */
              "SYS"."XML_SCHEMA_NAME_PRESENT"."IS_SCHEMA_PRESENT"("A8"."NAME","A44"."ID2")"ID1"
            from
              (
                select /*+ NO_MERGE */
                  userenv('SCHEMAID')"ID2"
                from
                  "SYS"."DUAL" "A16"
              )"A44"
          )
              or "A8"."TYPE#" = 69
              or "A8"."TYPE#" = 72
              or "A8"."TYPE#" = 74
              or "A8"."TYPE#" = 101
              or "A8"."TYPE#" = 57
              or "A8"."TYPE#" = 93
              and("A8"."OBJ#" = any(
            select
              "A43"."OBJ#" "OBJ#"
            from
              "SYS"."OBJAUTH$" "A43"
            where
              "A43"."GRANTEE#" = any(
                select
                  "A83"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A15"."ADDR"       "ADDR",
                      "A15"."INDX"       "INDX",
                      "A15"."INST_ID"    "INST_ID",
                      "A15"."CON_ID"     "CON_ID",
                      "A15"."KZSROROL"   "KZSROROL",
                      "A15"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A15"
                    where
                      "A15"."CON_ID" = 0
                      or "A15"."CON_ID" = 3
                  )"A83"
              )
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              and 1 =(
            select
              decode("A42"."HAVE_ALL_DIM_ACCESS",null,1,"A42"."HAVE_ALL_DIM_ACCESS")"DECODE(HAVE_ALL_DIM_ACCESS,NULL,1,HAVE_ALL_DIM_ACCESS)"
            from
              (
                select
                  "A10"."OBJ#" "OBJ#",
                  min("A10"."HAVE_DIM_ACCESS")"HAVE_ALL_DIM_ACCESS"
                from
                  (
                    select
                      "A13"."OBJ#" "OBJ#",
                      case
                        when("A11"."OWNER#" = userenv('SCHEMAID')
                             or "A11"."OWNER#" = 1
                             or "A11"."OBJ#" = any(
                          select
                            "A39"."OBJ#" "OBJ#"
                          from
                            "SYS"."OBJAUTH$" "A39"
                          where
                            "A39"."GRANTEE#" = any(
                              select
                                "A40"."KZSROROL" "KZSROROL"
                              from
                                (
                                  select
                                    "A14"."ADDR"       "ADDR",
                                    "A14"."INDX"       "INDX",
                                    "A14"."INST_ID"    "INST_ID",
                                    "A14"."CON_ID"     "CON_ID",
                                    "A14"."KZSROROL"   "KZSROROL",
                                    "A14"."KZSROFLG"   "KZSROFLG"
                                  from
                                    "SYS"."X$KZSRO" "A14"
                                  where
                                    "A14"."CON_ID" = 0
                                    or "A14"."CON_ID" = 3
                                )"A40"
                            )
                        )
                             or ora_check_sys_privilege("A11"."OWNER#","A11"."TYPE#")= 1)then
                          1
                        else
                          0
                      end "HAVE_DIM_ACCESS"
                    from
                      "SYS"."OLAP_CUBES$"   "A13",
                      "SYS"."DEPENDENCY$"   "A12",
                      "SYS"."OBJ$"          "A11"
                    where
                      "A11"."OBJ#" = "A12"."P_OBJ#"
                      and "A11"."TYPE#" = 92
                      and "A13"."OBJ#" = "A12"."D_OBJ#"
                  )"A10"
                group by
                  "A10"."OBJ#"
              )"A42"
            where
              "A8"."OBJ#" = "A42"."OBJ#"(+)
          )
              or "A8"."TYPE#" = 114
              and(exists(
            select
              null "NULL"
            from
              "SYS"."OBJAUTH$" "A41"
            where
              "A41"."OBJ#" = "A8"."OBJ#"
              and "A41"."GRANTEE#" = any(
                select
                  "A84"."KZSROROL" "KZSROROL"
                from
                  (
                    select
                      "A9"."ADDR"       "ADDR",
                      "A9"."INDX"       "INDX",
                      "A9"."INST_ID"    "INST_ID",
                      "A9"."CON_ID"     "CON_ID",
                      "A9"."KZSROROL"   "KZSROROL",
                      "A9"."KZSROFLG"   "KZSROFLG"
                    from
                      "SYS"."X$KZSRO" "A9"
                    where
                      "A9"."CON_ID" = 0
                      or "A9"."CON_ID" = 3
                  )"A84"
              )
              and("A41"."PRIVILEGE#" = 0
                  or "A41"."PRIVILEGE#" = 29)
          )
                  or ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
              or("A8"."TYPE#" = 150
                 or "A8"."TYPE#" = 151
                 or "A8"."TYPE#" = 152)
              and ora_check_sys_privilege("A8"."OWNER#","A8"."TYPE#")= 1)
      )"A2"
    where
      "A2"."OWNER" = "A3"."OWNER"
      and "A2"."OBJECT_NAME" = "A3"."SEGMENT_NAME"
  )"A1"

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.