[20131128]12c的dbms_utility.expand_sql_text.txt
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
12C的dbms_utility提供了新函数expand_sql_text可以看sql容易。
例子：
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
variable x clob;
begin
dbms_utility.expand_sql_text ( input_sql_text => 'select * from dba_tablespaces', output_sql_text => :x );
end;
/
SCOTT@ztest> column x format a200
SCOTT@ztest> print :x;
X
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."TABLESPACE_NAME" "TABLESPACE_NAME","A1"."BLOCK_SIZE" "BLOCK_SIZE","A1"."INITIAL_EXTENT" "INITIAL_EXTENT","A1"."NEXT_EXTENT" "NEXT_EXTENT","A1"."MIN_EXTENTS" "MIN_EXTENTS","A1"."MAX_EXTE
NTS" "MAX_EXTENTS","A1"."MAX_SIZE" "MAX_SIZE","A1"."PCT_INCREASE" "PCT_INCREASE","A1"."MIN_EXTLEN" "MIN_EXTLEN","A1"."STATUS" "STATUS","A1"."CONTENTS" "CONTENTS","A1"."LOGGING" "LOGGING","A1"."FORCE
_LOGGING" "FORCE_LOGGING","A1"."EXTENT_MANAGEMENT" "EXTENT_MANAGEMENT","A1"."ALLOCATION_TYPE" "ALLOCATION_TYPE","A1"."PLUGGED_IN" "PLUGGED_IN","A1"."SEGMENT_SPACE_MANAGEMENT" "SEGMENT_SPACE_MANAGEME
NT","A1"."DEF_TAB_COMPRESSION" "DEF_TAB_COMPRESSION","A1"."RETENTION" "RETENTION","A1"."BIGFILE" "BIGFILE","A1"."PREDICATE_EVALUATION" "PREDICATE_EVALUATION","A1"."ENCRYPTED" "ENCRYPTED","A1"."COMPR
ESS_FOR" "COMPRESS_FOR" FROM (SELECT "A3"."NAME" "TABLESPACE_NAME","A3"."BLOCKSIZE" "BLOCK_SIZE","A3"."BLOCKSIZE"*"A3"."DFLINIT" "INITIAL_EXTENT",DECODE(BITAND("A3"."FLAGS",3),1,TO_NUMBER(NULL),"A3
"."BLOCKSIZE"*"A3"."DFLINCR") "NEXT_EXTENT","A3"."DFLMINEXT" "MIN_EXTENTS",DECODE("A3"."CONTENTS$",1,TO_NUMBER(NULL),"A3"."DFLMAXEXT") "MAX_EXTENTS",DECODE(BITAND("A3"."FLAGS",4096),4096,"A3"."AFFST
RENGTH",NULL) "MAX_SIZE",DECODE(BITAND("A3"."FLAGS",3),1,TO_NUMBER(NULL),"A3"."DFLEXTPCT") "PCT_INCREASE","A3"."BLOCKSIZE"*"A3"."DFLMINLEN" "MIN_EXTLEN",DECODE("A3"."ONLINE$",1,'ONLINE',2,'OFFLINE',
4,'READ ONLY','UNDEFINED') "STATUS",DECODE("A3"."CONTENTS$",0,DECODE(BITAND("A3"."FLAGS",16),16,'UNDO','PERMANENT'),1,'TEMPORARY') "CONTENTS",DECODE(BITAND("A3"."DFLOGGING",1),0,'NOLOGGING',1,'LOGGI
NG') "LOGGING",DECODE(BITAND("A3"."DFLOGGING",2),0,'NO',2,'YES') "FORCE_LOGGING",DECODE("A3"."BITMAPPED",0,'DICTIONARY','LOCAL') "EXTENT_MANAGEMENT",DECODE(BITAND("A3"."FLAGS",3),0,'USER',1,'SYSTEM'
,2,'UNIFORM','UNDEFINED') "ALLOCATION_TYPE",DECODE("A3"."PLUGGED",0,'NO','YES') "PLUGGED_IN",DECODE(BITAND("A3"."FLAGS",32),32,'AUTO','MANUAL') "SEGMENT_SPACE_MANAGEMENT",DECODE(BITAND("A3"."FLAGS",
64),64,'ENABLED','DISABLED') "DEF_TAB_COMPRESSION",DECODE(BITAND("A3"."FLAGS",16),16,DECODE(BITAND("A3"."FLAGS",512),512,'GUARANTEE','NOGUARANTEE'),'NOT APPLY') "RETENTION",DECODE(BITAND("A3"."FLAGS
",256),256,'YES','NO') "BIGFILE",DECODE("A2"."STORATTR",1,'STORAGE','HOST') "PREDICATE_EVALUATION",DECODE(BITAND("A3"."FLAGS",16384),16384,'YES','NO') "ENCRYPTED",DECODE(BITAND("A3"."FLAGS",64),0,NU
LL,CASE WHEN BITAND("A3"."FLAGS",65536)=65536 THEN 'OLTP' WHEN BITAND("A3"."FLAGS",131072+262144)=131072 THEN 'QUERY LOW'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN
BITAND("A3"."FLAGS",131072+262144)=262144 THEN 'QUERY HIGH'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN BITAND("A3"."FLAGS",131072+262144)=131072+262144 THEN 'ARCHIVE
LOW'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN BITAND("A3"."FLAGS",524288)=524288 THEN 'ARCHIVE HIGH'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKIN
G','') ELSE 'BASIC' END ) "COMPRESS_FOR" FROM "SYS"."TS$" "A3","SYS"."X$KCFISTSA" "A2" WHERE "A3"."ONLINE$"3 AND BITAND("A3"."FLAGS",2048)2048 AND "A3"."TS#"="A2"."TSID") "A1"
SCOTT@ztest> select text from dba_views where view_name='DBA_TABLESPACES';
TEXT
------------------------------------------------------------------------------
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
4, 'READ ONLY', 'UNDEFINED'),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
'PERMANENT')), 1, 'TEMPORARY'),
decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
'UNDEFINED'),
decode(ts.plugged, 0, 'NO', 'YES'),
decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
decode(bitand(ts.flags,256), 256, 'YES', 'NO'),
decode(tsattr.storattr, 1, 'STORAGE', 'HOST'),
decode(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
decode(bitand(ts.flags,64), 0, null,
(case when bitand(ts.flags, 65536) = 65536
then 'OLTP'
when bitand(ts.flags, (131072+262144)) = 131072
then concat('QUERY LOW',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, (131072+262144)) = 262144
then concat('QUERY HIGH',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, (131072+262144)) = (131072+262144)
then concat('ARCHIVE LOW',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, 524288) = 524288
then concat('ARCHIVE HIGH',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
else 'BASIC' end))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
12C的dbms_utility提供了新函数expand_sql_text可以看sql容易。
例子：
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
variable x clob;
begin
dbms_utility.expand_sql_text ( input_sql_text => 'select * from dba_tablespaces', output_sql_text => :x );
end;
/
SCOTT@ztest> column x format a200
SCOTT@ztest> print :x;
X
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."TABLESPACE_NAME" "TABLESPACE_NAME","A1"."BLOCK_SIZE" "BLOCK_SIZE","A1"."INITIAL_EXTENT" "INITIAL_EXTENT","A1"."NEXT_EXTENT" "NEXT_EXTENT","A1"."MIN_EXTENTS" "MIN_EXTENTS","A1"."MAX_EXTE
NTS" "MAX_EXTENTS","A1"."MAX_SIZE" "MAX_SIZE","A1"."PCT_INCREASE" "PCT_INCREASE","A1"."MIN_EXTLEN" "MIN_EXTLEN","A1"."STATUS" "STATUS","A1"."CONTENTS" "CONTENTS","A1"."LOGGING" "LOGGING","A1"."FORCE
_LOGGING" "FORCE_LOGGING","A1"."EXTENT_MANAGEMENT" "EXTENT_MANAGEMENT","A1"."ALLOCATION_TYPE" "ALLOCATION_TYPE","A1"."PLUGGED_IN" "PLUGGED_IN","A1"."SEGMENT_SPACE_MANAGEMENT" "SEGMENT_SPACE_MANAGEME
NT","A1"."DEF_TAB_COMPRESSION" "DEF_TAB_COMPRESSION","A1"."RETENTION" "RETENTION","A1"."BIGFILE" "BIGFILE","A1"."PREDICATE_EVALUATION" "PREDICATE_EVALUATION","A1"."ENCRYPTED" "ENCRYPTED","A1"."COMPR
ESS_FOR" "COMPRESS_FOR" FROM (SELECT "A3"."NAME" "TABLESPACE_NAME","A3"."BLOCKSIZE" "BLOCK_SIZE","A3"."BLOCKSIZE"*"A3"."DFLINIT" "INITIAL_EXTENT",DECODE(BITAND("A3"."FLAGS",3),1,TO_NUMBER(NULL),"A3
"."BLOCKSIZE"*"A3"."DFLINCR") "NEXT_EXTENT","A3"."DFLMINEXT" "MIN_EXTENTS",DECODE("A3"."CONTENTS$",1,TO_NUMBER(NULL),"A3"."DFLMAXEXT") "MAX_EXTENTS",DECODE(BITAND("A3"."FLAGS",4096),4096,"A3"."AFFST
RENGTH",NULL) "MAX_SIZE",DECODE(BITAND("A3"."FLAGS",3),1,TO_NUMBER(NULL),"A3"."DFLEXTPCT") "PCT_INCREASE","A3"."BLOCKSIZE"*"A3"."DFLMINLEN" "MIN_EXTLEN",DECODE("A3"."ONLINE$",1,'ONLINE',2,'OFFLINE',
4,'READ ONLY','UNDEFINED') "STATUS",DECODE("A3"."CONTENTS$",0,DECODE(BITAND("A3"."FLAGS",16),16,'UNDO','PERMANENT'),1,'TEMPORARY') "CONTENTS",DECODE(BITAND("A3"."DFLOGGING",1),0,'NOLOGGING',1,'LOGGI
NG') "LOGGING",DECODE(BITAND("A3"."DFLOGGING",2),0,'NO',2,'YES') "FORCE_LOGGING",DECODE("A3"."BITMAPPED",0,'DICTIONARY','LOCAL') "EXTENT_MANAGEMENT",DECODE(BITAND("A3"."FLAGS",3),0,'USER',1,'SYSTEM'
,2,'UNIFORM','UNDEFINED') "ALLOCATION_TYPE",DECODE("A3"."PLUGGED",0,'NO','YES') "PLUGGED_IN",DECODE(BITAND("A3"."FLAGS",32),32,'AUTO','MANUAL') "SEGMENT_SPACE_MANAGEMENT",DECODE(BITAND("A3"."FLAGS",
64),64,'ENABLED','DISABLED') "DEF_TAB_COMPRESSION",DECODE(BITAND("A3"."FLAGS",16),16,DECODE(BITAND("A3"."FLAGS",512),512,'GUARANTEE','NOGUARANTEE'),'NOT APPLY') "RETENTION",DECODE(BITAND("A3"."FLAGS
",256),256,'YES','NO') "BIGFILE",DECODE("A2"."STORATTR",1,'STORAGE','HOST') "PREDICATE_EVALUATION",DECODE(BITAND("A3"."FLAGS",16384),16384,'YES','NO') "ENCRYPTED",DECODE(BITAND("A3"."FLAGS",64),0,NU
LL,CASE WHEN BITAND("A3"."FLAGS",65536)=65536 THEN 'OLTP' WHEN BITAND("A3"."FLAGS",131072+262144)=131072 THEN 'QUERY LOW'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN
BITAND("A3"."FLAGS",131072+262144)=262144 THEN 'QUERY HIGH'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN BITAND("A3"."FLAGS",131072+262144)=131072+262144 THEN 'ARCHIVE
LOW'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKING','') WHEN BITAND("A3"."FLAGS",524288)=524288 THEN 'ARCHIVE HIGH'||DECODE(BITAND("A3"."FLAGS",4194304),4194304,' ROW LEVEL LOCKIN
G','') ELSE 'BASIC' END ) "COMPRESS_FOR" FROM "SYS"."TS$" "A3","SYS"."X$KCFISTSA" "A2" WHERE "A3"."ONLINE$"3 AND BITAND("A3"."FLAGS",2048)2048 AND "A3"."TS#"="A2"."TSID") "A1"
SCOTT@ztest> select text from dba_views where view_name='DBA_TABLESPACES';
TEXT
------------------------------------------------------------------------------
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
4, 'READ ONLY', 'UNDEFINED'),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
'PERMANENT')), 1, 'TEMPORARY'),
decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
'UNDEFINED'),
decode(ts.plugged, 0, 'NO', 'YES'),
decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
decode(bitand(ts.flags,256), 256, 'YES', 'NO'),
decode(tsattr.storattr, 1, 'STORAGE', 'HOST'),
decode(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
decode(bitand(ts.flags,64), 0, null,
(case when bitand(ts.flags, 65536) = 65536
then 'OLTP'
when bitand(ts.flags, (131072+262144)) = 131072
then concat('QUERY LOW',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, (131072+262144)) = 262144
then concat('QUERY HIGH',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, (131072+262144)) = (131072+262144)
then concat('ARCHIVE LOW',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
when bitand(ts.flags, 524288) = 524288
then concat('ARCHIVE HIGH',
decode(bitand(ts.flags, 4194304), 4194304,
' ROW LEVEL LOCKING', ''))
else 'BASIC' end))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid
