SELECT tablespace_name,
file_id,
file_name,
ROUND(bytes/1024/1024, 0) AS total_mb
FROM dba_data_files
ORDER BY tablespace_name, file_id;
ALTER TABLESPACE users
ADD DATAFILE '/u01/oracle/oradata/umail/users02.dbf'
SIZE 4000M
AUTOEXTEND OFF;
ALTER TABLESPACE users
ADD DATAFILE '/u01/oracle/oradata/umail/users03.dbf'
SIZE 1000M
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
-- 關閉現有檔案自動擴展
ALTER DATABASE DATAFILE '/u01/oracle/oradata/umail/user01.dbf' AUTOEXTEND OFF;
-- 手動調整大小
ALTER DATABASE DATAFILE '/u01/oracle/oradata/umail/user01.dbf' RESIZE 5000M;
SELECT a.tablespace_name,
ROUND(a.bytes/1024/1024, 2) AS total_mb,
ROUND(b.bytes/1024/1024, 2) AS used_mb,
ROUND(c.bytes/1024/1024, 2) AS free_mb,
ROUND(b.bytes*100/a.bytes, 2) AS pct_used,
ROUND(c.bytes*100/a.bytes, 2) AS pct_free
FROM sys.sm$ts_available a,
sys.sm$ts_used b,
sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
ORDER BY a.tablespace_name;
注意: sm$ts_* 為 OEM/Enterprise Manager 維護的摘要表,若無權限可改查 dba_free_space、dba_data_files 自行彙總。
SELECT df.tablespace_name,
ROUND(SUM(df.bytes)/1024/1024, 2) AS total_mb,
ROUND(SUM(df.bytes - NVL(fs.bytes,0))/1024/1024, 2) AS used_mb,
ROUND(SUM(NVL(fs.bytes,0))/1024/1024, 2) AS free_mb,
ROUND(100 * SUM(NVL(fs.bytes,0)) / SUM(df.bytes), 2) AS pct_free
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
GROUP BY df.tablespace_name
ORDER BY pct_free;
{tablespace}_{seq}.dbf(如 users_01.dbf、users_02.dbf)ALTER TABLESPACE、ALTER DATABASE DATAFILE 權限
-- 建立 Bigfile 表空間(單一檔案可達 128TB,適合超大資料倉儲)
CREATE BIGFILE TABLESPACE big_data
DATAFILE '/u02/oradata/big_data01.dbf'
SIZE 100G AUTOEXTEND ON NEXT 10G MAXSIZE 50T;