oracle随笔

--查数据库的空间使用情况
SELECT A.TABLESPACE_NAME "表空间名称",
       100 - ROUND ((NVL(B.BYTES_FREE, 0 ) / A.BYTES_ALLOC) * 100 , 2) "占用率(%)",
       ROUND(A.BYTES_ALLOC / 1024 / 1024 / 1024, 2 ) "容量(G)",
       ROUND((A.BYTES_ALLOC - NVL (B.BYTES_FREE, 0)) / 1024 / 1024 / 1024, 2 ) "使用(G)",
       ROUND(NVL (B.BYTES_FREE, 0) / 1024 / 1024 / 1024, 2 ) "空闲(G)",
       ROUND((NVL (B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100 , 2 ) "空闲率(%)",
       TO_CHAR( SYSDATE, ‘yyyy-mm-dd hh24:mi:ss‘ ) "采样时间"
  FROM ( SELECT F.TABLESPACE_NAME,
               SUM(F.BYTES) BYTES_ALLOC,
               SUM(DECODE (F.AUTOEXTENSIBLE, ‘YES‘ , F.MAXBYTES, ‘NO‘, F.BYTES)) MAXBYTES
          FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME) A,
       ( SELECT F.TABLESPACE_NAME, SUM (F.BYTES) BYTES_FREE
          FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY ROUND (NVL(B.BYTES_FREE, 0) / 1024 / 1024 / 1024, 2 ) DESC;

 -------------------------------2.生成临时表开始-----------------------------------------------------
  --1.首次欠费帐务周期
  ODS_WORK.PKG_COMM.P_DROP_TABLE(‘ODS_WORK‘,‘TMP_BIL_INS_OWE_PAY_MON1_‘||P_LAN_ID||‘‘,OUT_ERROR_ID,OUT_ERROR_MSG);
  OUT_ERROR_MSG :=‘创建临时表：TMP_BIL_INS_OWE_PAY_MON1_‘||P_LAN_ID||‘失败！ ‘;
  LS_SQL := ‘CREATE TABLE TMP_BIL_INS_OWE_PAY_MON1_‘||P_LAN_ID||‘ COMPRESS FOR QUERY HIGH  NOLOGGING TABLESPACE ‘||TABLE_SPACE||‘ AS
                   SELECT SERV_ID,OWE_BILL_CYCLE FROM (
                   SELECT A.SERV_ID,A.OWE_BILL_CYCLE,
                   ROW_NUMBER() OVER(PARTITION BY A.SERV_ID ORDER BY A.OWE_BILL_CYCLE ASC) RN
                     FROM ODS_WORK.MID_BIL_VW_OWE_MON A
                    WHERE A.ACCT_MONTH=‘||P_ACCT_MONTH||‘
                      AND A.LATN_ID=‘||P_LAN_ID||‘)
                    WHERE RN=1‘;
  EXECUTE IMMEDIATE LS_SQL;