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 TABLESPACE app_data
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/app_data01.dbf’ SIZE 500M
3 AUTOEXTEND ON NEXT 50M MAXSIZE 5000M
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO
6 DEFAULT STORAGE (
7 INITIAL 10M
8 NEXT 10M
9 MINEXTENTS 1
10 MAXEXTENTS UNLIMITED
11 PCTINCREASE 0
12 );Tablespace created.

— 1. 查看永久表空间信息
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
WHERE contents = ‘PERMANENT’;– 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;– 9. 更改表空间的默认表空间
ALTER USER scott DEFAULT TABLESPACE users;– 10. 更改表空间的临时表空间
ALTER USER scott TEMPORARY TABLESPACE temp;

— 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, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = ‘USERS’
ORDER BY size_mb DESC;– 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
WHERE tablespace_name = ‘USERS’
GROUP BY tablespace_name;– 4. 整理永久表空间碎片
ALTER TABLESPACE users COALESCE;– 5. 检查永久表空间数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files
WHERE tablespace_name = ‘USERS’;– 6. 检查表空间是否有坏块
SELECT tablespace_name, segment_name, segment_type
FROM dba_extents
WHERE file_id IN (
SELECT file_id
FROM v$database_block_corruption
) AND tablespace_name = ‘USERS’;

— 案例：为企业应用系统创建永久表空间

— 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. 创建LOB表空间
CREATE TABLESPACE lob_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/lob_data01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;– 5. 创建应用表空间
CREATE TABLESPACE app_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/app_data01.dbf’ SIZE 1000M
AUTOEXTEND ON NEXT 100M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;– 6. 设置默认表空间
ALTER DATABASE DEFAULT TABLESPACE user_data;– 7. 为用户分配表空间
CREATE USER app_user IDENTIFIED BY password
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON app_data
QUOTA 100M ON user_data;– 8. 为表指定表空间
CREATE TABLE app_user.employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
) TABLESPACE app_data;– 9. 为索引指定表空间
CREATE INDEX app_user.emp_last_name_idx
ON app_user.employees(last_name)
TABLESPACE index_data;

— 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,
COUNT(*) AS extent_count,
MAX(blocks) AS max_blocks,
MIN(blocks) AS min_blocks,
AVG(blocks) AS avg_blocks
FROM dba_extents
WHERE tablespace_name = ‘USERS’
GROUP BY tablespace_name;– 解决方案：整理碎片
ALTER TABLESPACE users COALESCE;– 3. 表空间离线问题
— 查看表空间状态
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = ‘USERS’;– 解决方案：使表空间在线
ALTER TABLESPACE users ONLINE;– 4. 数据文件损坏问题
— 检查数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files
WHERE tablespace_name = ‘USERS’;– 解决方案：恢复损坏的数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ OFFLINE;– 从备份恢复数据文件
— 恢复后使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/users01.dbf’ ONLINE;

— 1. 表空间无法扩展问题
— 检查表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = ‘USERS’;– 解决方案：检查文件系统空间，添加数据文件

— 2. 表空间配额不足问题
— 查看用户配额
SELECT username, tablespace_name, max_bytes/1024/1024 AS max_mb,
used_bytes/1024/1024 AS used_mb
FROM dba_ts_quotas
WHERE username = ‘SCOTT’;– 解决方案：增加用户配额
ALTER USER scott QUOTA 500M ON users;– 3. 表空间权限问题
— 查看用户权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = ‘SCOTT’
AND privilege LIKE ‘%TABLESPACE%’;– 解决方案：授予用户表空间权限
GRANT UNLIMITED TABLESPACE TO scott;– 4. 表空间迁移问题
— 移动表到新表空间
ALTER TABLE scott.employees MOVE TABLESPACE new_tablespace;– 移动索引到新表空间
ALTER INDEX scott.emp_last_name_idx REBUILD TABLESPACE new_index_tablespace;