--查询存储过程、触发器、函数、包、包体、类型、类型体的内容
SELECT * FROM DBA_SOURCE;
--表空间查看
SELECT A.TABLESPACE_NAME, --表空间
ALL_SPACE, --总共空间大小（G）
ALL_SPACE - FREE_SPACE AS USE_SPACE, --使用空间大小（G）
(ALL_SPACE - FREE_SPACE) / ALL_SPACE * 100 AS USE_RATE --使用率（%）
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A
LEFT JOIN (SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS ALL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME;
--数据准备（求季末余额）
CREATE TABLE CCS2_DBA.T_SYF_20191213(ACCOUNT_ID NVARCHAR2(100),BAL_CHANGE_DT DATE, BAL NUMBER);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20160101','YYYYMMDD'),500);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20160121','YYYYMMDD'),600);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20160406','YYYYMMDD'),800);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20160505','YYYYMMDD'),400);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20160701','YYYYMMDD'),700);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20180808','YYYYMMDD'),900);
INSERT INTO CCS2_DBA.T_SYF_20191213(ACCOUNT_ID,BAL_CHANGE_DT,BAL)VALUES('001',TO_DATE('20190111','YYYYMMDD'),300);
--利用窗口函数计算出季末余额
SELECT A.DATE_OF_Q, NVL(B.BAL, 0)
FROM (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'Q') - 1, -LEVEL * 3) AS DATE_OF_Q
FROM DUAL
CONNECT BY LEVEL <= 5 * 4) A
LEFT JOIN (SELECT A.ACCOUNT_ID,
A.BAL_CHANGE_DT AS START_DT,
NVL(MAX(BAL_CHANGE_DT)
OVER(PARTITION BY ACCOUNT_ID ORDER BY BAL_CHANGE_DT
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
TRUNC(SYSDATE)) AS END_DT,
A.BAL
FROM CCS2_DBA.T_SYF_20191213 A) B
ON A.DATE_OF_Q >= B.START_DT
AND A.DATE_OF_Q < B.END_DT
ORDER BY A.DATE_OF_Q;
