--查数据库的空间使用情况
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;