--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
SELECT *
FROM (SELECT TS.TABLESPACE_NAME,
NVL(ROUND(TOTAL_D.TOTAL_BYTES / POWER(2, 20), 0), 0) TOTAL_M,
NVL(ROUND(FREE_D.FREE_BYTES / POWER(2, 20), 0), 0) FREE_M,
ROUND(NVL(FREE_D.FREE_BYTES, 0) /
(NVL(TOTAL_D.TOTAL_BYTES, 0) + 0.000001),
4) * 100 "Free%"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_BYTES
FROM DBA_DATA_FILES DDF
GROUP BY DDF.TABLESPACE_NAME) TOTAL_D,
(SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES
FROM DBA_FREE_SPACE DFS
GROUP BY DFS.TABLESPACE_NAME) FREE_D,
DBA_TABLESPACES TS
WHERE TS.TABLESPACE_NAME = TOTAL_D.TABLESPACE_NAME(+)
AND TS.TABLESPACE_NAME = FREE_D.TABLESPACE_NAME(+)) TS_FREE_SPACE
WHERE 1 = 1
--AND TS_FREE_SPACE.TABLESPACE_NAME != 'TEMP'
--AND TS_FREE_SPACE.TABLESPACE_NAME = 'HPTS_DATA'
ORDER BY "Free%";
SELECT ddf.file_name
FROM Dba_Data_Files ddf
where ddf.tablespace_name = 'APPS_TS_TX_IDX';
/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_.dbf
--如果小于4G。直接RESIZEM
ALTER DATABASE DATAFILE '/data/orauat/oradata/uat/hpts_data_01.dbf' RESIZE 4096M;
--如果大于4G。则增加数据文件
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_01.dbf' SIZE 1000M;--(增加100M到APPS_TS_TX_DATA表空间)
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_02.dbf' SIZE 1000M;--(增加100M到APPS_TS_TX_DATA表空间)