ORACLE笔记

--查询存储过程、触发器、函数、包、包体、类型、类型体的内容

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;