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> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SIZE 50M
3 AUTOEXTEND ON NEXT 10M MAXSIZE 500M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M;Tablespace created.

SQL> CREATE UNDO TABLESPACE undo
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 100M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;Tablespace created.

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

— 1. 查看表空间信息
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces;– 2. 查看表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files;– 3. 修改表空间大小
ALTER TABLESPACE users
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/users02.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;– 4. 调整表空间自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’
AUTOEXTEND ON NEXT 20M MAXSIZE 2000M;– 5. 使表空间离线
ALTER TABLESPACE users OFFLINE;– 6. 使表空间在线
ALTER TABLESPACE users ONLINE;– 7. 重命名表空间
ALTER TABLESPACE old_tablespace RENAME TO new_tablespace;– 8. 删除表空间
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

— 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 tablespace_name,
COUNT(*) AS extent_count,
MAX(blocks) AS max_blocks,
MIN(blocks) AS min_blocks,
AVG(blocks) AS avg_blocks
FROM dba_extents
GROUP BY tablespace_name;– 4. 整理表空间碎片
ALTER TABLESPACE users COALESCE;– 5. 检查表空间数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files;– 6. 检查表空间是否有坏块
SELECT tablespace_name, segment_name, segment_type
FROM dba_extents
WHERE file_id IN (
SELECT file_id
FROM v$database_block_corruption
);

— 案例：为大型应用系统创建表空间

— 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. 创建索引表空间
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;– 4. 创建临时表空间
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;– 5. 创建回滚表空间
CREATE UNDO TABLESPACE undo
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 300M
AUTOEXTEND ON NEXT 50M MAXSIZE 3000M;– 6. 设置默认表空间
ALTER DATABASE DEFAULT TABLESPACE user_data;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;ALTER SYSTEM SET undo_tablespace = ‘UNDO’ SCOPE=BOTH;

— 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/users02.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M;– 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;– 解决方案：添加临时数据文件
ALTER TABLESPACE temp
ADD TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp02.dbf’ SIZE 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;– 3. 回滚表空间不足问题
— 查看回滚表空间使用情况
SELECT tablespace_name, status, retention
FROM dba_tablespaces
WHERE contents = ‘UNDO’;– 查看回滚段使用情况
SELECT segment_name, tablespace_name, status, bytes/1024/1024 AS size_mb
FROM dba_rollback_segs
WHERE tablespace_name = ‘UNDO’;– 解决方案：调整回滚表空间大小
ALTER TABLESPACE undo
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo02.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;

— 1. 表空间离线问题
— 查看表空间状态
SELECT tablespace_name, status
FROM dba_tablespaces;– 使表空间在线
ALTER TABLESPACE users ONLINE;– 2. 数据文件损坏问题
— 检查数据文件状态
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;– 3. 表空间重命名问题
— 重命名表空间
ALTER TABLESPACE old_tablespace RENAME TO new_tablespace;– 4. 表空间删除问题
— 删除表空间
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;– 5. 表空间碎片问题
— 检查碎片
SELECT tablespace_name,
COUNT(*) AS extent_count,
MAX(blocks) AS max_blocks,
MIN(blocks) AS min_blocks,
AVG(blocks) AS avg_blocks
FROM dba_extents
GROUP BY tablespace_name;– 整理碎片
ALTER TABLESPACE users COALESCE;