1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
public function bigTablespace()
{
$sql = "
WITH
TABLESPACES (INSTANCE,DATABASE,TABLESPACE,TABLESPACE_TYPE,CONTENT_TYPE,PAGESIZE,USED_SPACE) AS
( SELECT
DAT.T_INSTANCE_NAME AS INSTANCE,
DAT.T_NAME AS DATABASE,
TBLS.T_NAME AS TABLESPACE ,
TBLS.T_I_TBSPACETYPE as TABLESPACE_TYPE,
case TBLS.T_I_CONTENT_TYPE WHEN 'ANY' THEN 'REGULAR' ELSE TBLS.T_I_CONTENT_TYPE END as CONTENT_TYPE,
TBLS.N_I_PAGESIZE AS PAGESIZE,
TBLSU.N_I_MAX_USED_SIZE_GBYTES AS USED_SPACE
FROM RDC.DATABASE AS DAT
INNER JOIN RDC.TABLESPACE AS TBLS
ON DAT.O_ID = TBLS.O_DATABASE_ID
INNER JOIN RDC.TABLESPACE_USAGE AS TBLSU
ON TBLS.O_ID = TBLSU.O_TABLESPACE_ID
WHERE TBLSU.S_I_INSERT_TIMESTAMP = (
SELECT MAX(S_I_INSERT_TIMESTAMP)
FROM RDC.TABLE_USAGE
)
AND TBLSU.N_I_MAX_USED_SIZE_GBYTES <> 0
AND TBLS.T_I_TBSPACETYPE = 'DMS' )
,
LIMITS (TABLESPACE_TYPE,PAGESIZE,CONTENT_TYPE,MAX_SPACE) AS
( VALUES ('DMS',4096,'REGULAR',64),
('DMS',8192,'REGULAR',128),
('DMS',16384,'REGULAR',256),
('DMS',32768,'REGULAR',512),
('DMS',4096,'LARGE',2048),
('DMS',8192,'LARGE',4096),
('DMS',16384,'LARGE',8192),
('DMS',32768,'LARGE',16384) )
SELECT A.INSTANCE,
A.DATABASE,
A.TABLESPACE,
A.TABLESPACE_TYPE,
A.CONTENT_TYPE,
A.PAGESIZE,
A.USED_SPACE,
B.MAX_SPACE,
(A.USED_SPACE*100)/B.MAX_SPACE as PCT_USED
FROM TABLESPACES A,
LIMITS B
WHERE A.TABLESPACE_TYPE = B.TABLESPACE_TYPE
AND A.PAGESIZE = B.PAGESIZE
AND A.CONTENT_TYPE = B.CONTENT_TYPE
AND A.USED_SPACE > 0.5*B.MAX_SPACE
ORDER BY PCT_USED DESC, A.PAGESIZE
";
return $this->_db->fetchAll($sql);
} |
Partager