--查詢存儲過程、觸發(fā)器、函數(shù)、包、包體、類型、類型體的內(nèi)容

SELECT * FROM DBA_SOURCE;

--表空間查看

SELECT A.TABLESPACE_NAME, --表空間

? ? ? ALL_SPACE, --總共空間大?。℅）

? ? ? 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;

--數(shù)據(jù)準(zhǔn)備（求季末余額）

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);

--利用窗口函數(shù)計算出季末余額

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;