69 lines
1.9 KiB
SQL
69 lines
1.9 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
COL imseg_owner FOR A20
|
|
COL imseg_segment_name FOR A30
|
|
COL imseg_partition_name FOR A30
|
|
COL imseg_pct_done HEAD '%POP' FOR A5 JUST RIGHT
|
|
COL tablespace_name FOR A30
|
|
|
|
COMPUTE SUM LABEL 'totseg' OF seg_mb ON seg_mb REPORT
|
|
COMPUTE SUM LABEL 'totmem' OF inmem_mb ON inmem_mb REPORT
|
|
COMPUTE SUM LABEL 'totnot' OF mb_notpop ON mb_notpop REPORT
|
|
|
|
BREAK ON REPORT
|
|
|
|
SELECT
|
|
ROUND(SUM(bytes)/1048576) seg_MB -- dont want to double count the segment size from gv$
|
|
, ROUND(SUM(inmemory_size)/1048576) inmem_MB
|
|
, LPAD(ROUND((1-(SUM(bytes_not_populated)/NULLIF(SUM(bytes),0)))*100)||'%',5) imseg_pct_done
|
|
-- , LPAD(ROUND(SUM(inmemory_size)/SUM(bytes)*100)||'%',5) compr_pct
|
|
, owner imseg_owner
|
|
, segment_name imseg_segment_name
|
|
-- , partition_name imseg_partition_name
|
|
, segment_type
|
|
, COUNT(DISTINCT partition_name) partitions
|
|
, tablespace_name
|
|
, inst_id
|
|
, populate_status pop_status
|
|
, inmemory_priority im_priority
|
|
, inmemory_distribute im_distribute
|
|
, inmemory_compression im_compression
|
|
, con_id
|
|
, inst_id
|
|
FROM
|
|
gv$im_segments
|
|
WHERE
|
|
upper(segment_name) LIKE
|
|
upper(CASE
|
|
WHEN INSTR('&1','.') > 0 THEN
|
|
SUBSTR('&1',INSTR('&1','.')+1)
|
|
ELSE
|
|
'&1'
|
|
END
|
|
)
|
|
AND owner LIKE
|
|
CASE WHEN INSTR('&1','.') > 0 THEN
|
|
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
|
|
ELSE
|
|
user
|
|
END
|
|
GROUP BY
|
|
owner -- imseg_owner
|
|
, segment_name -- imseg_segment_name
|
|
-- , partition_name -- imseg_partition_name
|
|
, segment_type
|
|
, tablespace_name
|
|
, inst_id
|
|
, populate_status
|
|
, inmemory_priority
|
|
, inmemory_distribute
|
|
, inmemory_compression
|
|
, con_id
|
|
, inst_id
|
|
ORDER BY
|
|
inmem_mb DESC
|
|
/
|
|
|
|
CLEAR BREAKS
|