-- 查詢當(dāng)前數(shù)據(jù)文件路徑和大小
SELECT file_name, bytes/1024/1024 AS current_size_mb
FROM dba_data_files
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME';

-- 擴(kuò)展指定數(shù)據(jù)文件 (例如擴(kuò)大到 2GB)
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' RESIZE 2048M;

-- 向表空間添加新數(shù)據(jù)文件 (初始大小 500MB，自動增長 100MB，最大 10GB)
ALTER TABLESPACE YOUR_TABLESPACE_NAME
ADD DATAFILE '/u01/oradata/orcl/users02.dbf'
SIZE 500M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 10240M;

-- 為已有數(shù)據(jù)文件啟用自動擴(kuò)展
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf'
AUTOEXTEND ON
NEXT 50M  -- 每次增長量
MAXSIZE UNLIMITED;  -- 或指定上限 (如 MAXSIZE 2048M)

SELECT tablespace_name, status, contents FROM dba_tablespaces;

df -h /u01/oradata  # 檢查目標(biāo)掛載點(diǎn)空間

SELECT 
    a.tablespace_name,
    ROUND(a.bytes_alloc / 1024/1024, 2) AS total_mb,
    ROUND((a.bytes_alloc - nvl(b.bytes_free,0)) / 1024/1024, 2) AS used_mb,
    ROUND(nvl(b.bytes_free,0) / 1024/1024, 2) AS free_mb
FROM (SELECT tablespace_name, SUM(bytes) bytes_alloc
      FROM dba_data_files GROUP BY tablespace_name) a
LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes_free
           FROM dba_free_space GROUP BY tablespace_name) b
ON a.tablespace_name = b.tablespace_name
WHERE a.tablespace_name = 'YOUR_TABLESPACE_NAME';

select * from dba_data_files;
看文件路徑是否是“+DATA”開頭，如果是具體路徑，則是單機(jī)，+DATA開頭是RAC模式

-- 添加臨時(shí)文件 (非數(shù)據(jù)文件)
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/orcl/temp02.dbf' SIZE 1000M;

-- 檢查文件新尺寸
SELECT file_name, bytes/1024/1024 AS size_mb, autoextensible, maxbytes/1024/1024 AS max_mb
FROM dba_data_files
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME';

-- 確認(rèn)表空間可用空間
SELECT tablespace_name, sum(bytes)/1024/1024 AS free_space_mb
FROM dba_free_space
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME'
GROUP BY tablespace_name;

--確認(rèn)DG是否同步新增數(shù)據(jù)表空間

ALTER SYSTEM SET db_create_file_dest='/u01/oradata/orcl';
ALTER TABLESPACE users ADD DATAFILE SIZE 500M; -- 自動生成路徑