[20170703]pivot与order by字段.txt
--//11G开始支持pivot,上午写一个脚本,来自链接http://blog.itpub.net/267265/viewspace-1063539/
--//做了一点点改写.
select * from
(
WITH pivot_stats
AS (SELECT owner,
object_name,
statistic_name,
VALUE
FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
FOR statistic_name
IN ('logical reads',
'buffer busy waits',
'gc buffer busy',
'db block changes',
'physical reads',
'physical writes',
'physical read requests',
'physical write requests',
'physical reads direct',
'physical writes direct',
'optimized physical reads',
'gc cr blocks received',
'gc current blocks received',
'ITL waits',
'row lock waits',
'space used',
'space allocated',
'segment scans'))
where owner =upper('&&1') order by &&2
)
where rownum<=20;
--//我发现这样的输出字段命名如下:
SCOTT@book> @ &r/seg_stat1 scott 3
old 30: where owner =upper('&&1') order by &&2
new 30: where owner =upper('scott') order by 3
OWNER OBJECT_NAME 'logical reads' 'buffer busy waits' 'gc buffer busy' 'db block changes' 'physical reads' 'physical writes' 'physical read requests' 'physical write requests' 'physical reads direct' 'physical writes direct'
------ -------------------- --------------- ------------------- ---------------- ------------------ ---------------- ----------------- ------------------------ ------------------------- ----------------------- ------------------------
'optimized physical reads' 'gc cr blocks received' 'gc current blocks received' 'ITL waits' 'row lock waits' 'space used' 'space allocated' 'segment scans'
-------------------------- ----------------------- ---------------------------- ----------- ---------------- ------------ ----------------- ---------------
SCOTT DEPT 32 0 0 0 6 0 3 0 0 0
0 0 0 0 0 0 0 0
SCOTT PK_DEPT 48 0 0 0 2 0 2 0 0 0
0 0 0 0 0 0 0 0
--//我这里指定第3个字段排序.如何使用字段名呢?尝试才发现使用单引号写才ok.做一个记录:
select * from
(
WITH pivot_stats
AS (SELECT owner,
object_name,
statistic_name,
VALUE
FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
FOR statistic_name
IN ('logical reads',
'buffer busy waits',
'gc buffer busy',
'db block changes',
'physical reads',
'physical writes',
'physical read requests',
'physical write requests',
'physical reads direct',
'physical writes direct',
'optimized physical reads',
'gc cr blocks received',
'gc current blocks received',
'ITL waits',
'row lock waits',
'space used',
'space allocated',
'segment scans'))
where owner =upper('&&1') order by 'logical reads'
)
where rownum<=20;