SQL> CREATE TABLESPACE users
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ SIZE 100M
3 AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO;Tablespace created.

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 file_name, tablespace_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb,
status
FROM dba_data_files;– 2. 查看临时数据文件信息
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_temp_files;– 3. 修改数据文件大小
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
RESIZE 200M;– 4. 调整数据文件自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND ON NEXT 20M MAXSIZE 2000M;– 5. 禁用数据文件自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND OFF;– 6. 使数据文件离线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
OFFLINE;– 7. 使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
ONLINE;– 8. 重命名数据文件
— 首先使数据文件离线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
OFFLINE;– 使用操作系统命令重命名文件
— mv /u01/app/oracle/oradata/ORCL/users01.dbf /u01/app/oracle/oradata/ORCL/users01_old.dbf
— 修改数据库中的文件路径
ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
TO ‘/u01/app/oracle/oradata/ORCL/users01_old.dbf’;– 使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01_old.dbf’
ONLINE;– 9. 删除数据文件
— 注意：只能删除空的数据文件
ALTER TABLESPACE users
DROP DATAFILE ‘/u01/app/oracle/oradata/ORCL/users02.dbf’;

— 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 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;– 3. 检查数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files;– 4. 检查数据文件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;– 5. 检查数据文件是否有坏块
SELECT file_name, block_id, blocks, corruption_type
FROM v$database_block_corruption dbc, dba_data_files dbf
WHERE dbc.file# = dbf.file_id;– 6. 监控数据文件增长趋势
SELECT tablespace_name, file_name,
bytes/1024/1024 AS current_size_mb,
maxbytes/1024/1024 AS max_size_mb,
autoextensible
FROM dba_data_files
ORDER BY tablespace_name, file_name;

— 案例：为企业应用系统管理数据文件

— 1. 创建系统表空间的数据文件
CREATE TABLESPACE system_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/system_data01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;– 2. 创建用户表空间的数据文件
CREATE TABLESPACE user_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data01.dbf’ SIZE 1000M
AUTOEXTEND ON NEXT 100M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;– 3. 为用户表空间添加数据文件
ALTER TABLESPACE user_data
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data02.dbf’ SIZE 1000M
AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;– 4. 创建索引表空间的数据文件
CREATE TABLESPACE index_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/index_data01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;– 5. 创建临时表空间的数据文件
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;– 6. 创建回滚表空间的数据文件
CREATE UNDO TABLESPACE undo
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 300M
AUTOEXTEND ON NEXT 50M MAXSIZE 3000M;– 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. 调整数据文件自动扩展设置
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data01.dbf’
AUTOEXTEND ON NEXT 150M MAXSIZE 15000M;– 9. 收缩数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/user_data01.dbf’
RESIZE 1500M;

— 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;– 解决方案：添加数据文件或扩展现有数据文件
ALTER TABLESPACE users
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/users03.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;– 2. 数据文件自动扩展失败问题
— 检查数据文件自动扩展设置
SELECT file_name, autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE autoextensible = ‘YES’;– 解决方案：检查文件系统空间，调整自动扩展设置

— 3. 数据文件损坏问题
— 检查数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files;– 解决方案：从备份恢复数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ OFFLINE;– 从备份恢复数据文件
— 恢复后使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ ONLINE;– 4. 数据文件I/O性能问题
— 检查数据文件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等高性能存储

— 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, status, online_status
FROM dba_data_files
WHERE file_name = ‘/u01/app/oracle/oradata/ORCL/users01.dbf’;– 解决方案：使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ ONLINE;– 3. 数据文件重命名失败问题
— 步骤：
— 1. 使数据文件离线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ OFFLINE;– 2. 使用操作系统命令重命名文件
— mv /u01/app/oracle/oradata/ORCL/users01.dbf /u01/app/oracle/oradata/ORCL/users01_new.dbf
— 3. 修改数据库中的文件路径
ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
TO ‘/u01/app/oracle/oradata/ORCL/users01_new.dbf’;– 4. 使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01_new.dbf’ ONLINE;– 4. 数据文件坏块问题
— 检查坏块
SELECT file_name, block_id, blocks, corruption_type
FROM v$database_block_corruption dbc, dba_data_files dbf
WHERE dbc.file# = dbf.file_id;– 解决方案：使用RMAN修复坏块
RMAN>
CONNECT TARGET /;BLOCKRECOVER DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ BLOCK 1234;– 5. 数据文件迁移问题
— 步骤：
— 1. 使数据文件离线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ OFFLINE;– 2. 使用操作系统命令复制文件到新位置
— cp /u01/app/oracle/oradata/ORCL/users01.dbf /u02/app/oracle/oradata/ORCL/users01.dbf
— 3. 修改数据库中的文件路径
ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
TO ‘/u02/app/oracle/oradata/ORCL/users01.dbf’;– 4. 使数据文件在线
ALTER DATABASE DATAFILE ‘/u02/app/oracle/oradata/ORCL/users01.dbf’ ONLINE;