SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
2 RESIZE 200M;Database altered.

SQL> ALTER TABLESPACE users
2 ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/users02.dbf’ SIZE 100M
3 AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;Tablespace altered.

— 1. 查看数据文件使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
) GROUP BY tablespace_name;– 2. 查看单个数据文件的使用情况
SELECT file_name, bytes/1024/1024 AS size_mb,
(bytes – (SELECT SUM(bytes) FROM dba_free_space WHERE file_id = df.file_id))/1024/1024 AS used_mb,
(SELECT SUM(bytes) FROM dba_free_space WHERE file_id = df.file_id)/1024/1024 AS free_mb
FROM dba_data_files df
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 3. 收缩数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
RESIZE 150M;– 4. 收缩临时数据文件
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
RESIZE 50M;– 5. 收缩表空间（仅适用于临时表空间）
ALTER TABLESPACE temp SHRINK SPACE;– 6. 收缩表空间到指定大小（仅适用于临时表空间）
ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;– 7. 收缩临时数据文件
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SHRINK SPACE;

— 1. 查看数据文件自动扩展设置
SELECT file_name, autoextensible, next_extent/1024/1024 AS next_mb,
maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files;– 2. 启用数据文件自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;– 3. 调整数据文件自动扩展参数
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND ON NEXT 20M MAXSIZE 2000M;– 4. 禁用数据文件自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND OFF;– 5. 启用临时数据文件自动扩展
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;– 6. 调整临时数据文件自动扩展参数
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;– 7. 禁用临时数据文件自动扩展
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
AUTOEXTEND OFF;

— 案例：为企业应用系统调整数据文件

— 1. 查看数据文件使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
) GROUP BY tablespace_name
ORDER BY used_percent DESC;– 2. 扩展用户表空间的数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data01.dbf’
RESIZE 1500M;– 3. 为用户表空间添加新的数据文件
ALTER TABLESPACE user_data
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data03.dbf’ SIZE 1000M
AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;– 4. 调整数据文件自动扩展设置
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data01.dbf’
AUTOEXTEND ON NEXT 150M MAXSIZE 15000M;– 5. 收缩临时表空间
ALTER TABLESPACE temp SHRINK SPACE KEEP 100M;– 6. 调整临时数据文件自动扩展设置
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
AUTOEXTEND ON NEXT 50M MAXSIZE 1000M;– 7. 验证数据文件调整结果
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
) GROUP BY tablespace_name;– 8. 验证临时数据文件调整结果
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_temp_files
) GROUP BY tablespace_name;

— 1. 数据文件扩展失败问题
— 检查数据文件信息
SELECT file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 解决方案：检查文件系统空间，确保有足够的空间进行扩展

— 2. 数据文件收缩失败问题
— 查看数据文件使用情况
SELECT file_name, bytes/1024/1024 AS size_mb,
(bytes – (SELECT SUM(bytes) FROM dba_free_space WHERE file_id = df.file_id))/1024/1024 AS used_mb,
(SELECT SUM(bytes) FROM dba_free_space WHERE file_id = df.file_id)/1024/1024 AS free_mb
FROM dba_data_files df
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 解决方案：确保数据文件中有足够的空闲空间，或使用其他方法回收空间

— 3. 数据文件自动扩展不生效问题
— 检查数据文件自动扩展设置
SELECT file_name, autoextensible, next_extent/1024/1024 AS next_mb,
maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 解决方案：检查文件系统空间，确保自动扩展设置正确

— 4. 临时表空间收缩失败问题
— 查看临时表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_temp_files
) GROUP BY tablespace_name;– 解决方案：确保临时表空间中有足够的空闲空间，或等待临时段释放

— 1. 数据文件扩展失败
— 检查文件系统空间
— df -h /u01/app/oracle/oradata/ORCL/

— 解决方案：增加文件系统空间，或使用其他存储位置

— 2. 数据文件收缩失败
— 检查数据文件中的段分布
SELECT segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = ‘USERS’
ORDER BY size_mb DESC;– 解决方案：使用ALTER TABLE … MOVE命令移动段，或重建表空间

— 3. 数据文件自动扩展不生效
— 检查数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 解决方案：确保数据文件在线，检查文件系统权限

— 4. 临时表空间收缩失败
— 查看正在使用临时表空间的会话
SELECT s.sid, s.serial#, s.username, t.tablespace,
t.blocks * 8/1024 AS used_mb
FROM v$session s, v$sort_usage t
WHERE s.saddr = t.session_addr;– 解决方案：等待会话完成，或终止长时间运行的会话

— 5. 数据文件调整后数据库性能下降
— 检查I/O性能
SELECT file_name, phyrds, phywrts,
readtim/100 AS read_time, writetim/100 AS write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY read_time + write_time DESC;– 解决方案：优化存储设备，考虑使用SSD等高性能存储