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.

SQL> CREATE BIGFILE TABLESPACE data_warehouse
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/data_warehouse01.dbf’ SIZE 5G
3 AUTOEXTEND ON NEXT 500M MAXSIZE 50G
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 16M
6 SEGMENT SPACE MANAGEMENT AUTO;Tablespace created.

— 1. 查看大文件表空间信息
SELECT tablespace_name, bigfile, status, contents, extent_management
FROM dba_tablespaces
WHERE bigfile = ‘YES’;– 2. 查看大文件表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024/1024 AS size_gb,
autoextensible, maxbytes/1024/1024/1024 AS max_size_gb
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 3. 修改大文件表空间大小
ALTER TABLESPACE big_data
RESIZE 2G;– 4. 调整大文件表空间自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/big_data01.dbf’
AUTOEXTEND ON NEXT 200M MAXSIZE 20G;– 5. 使大文件表空间离线
ALTER TABLESPACE big_data OFFLINE;– 6. 使大文件表空间在线
ALTER TABLESPACE big_data ONLINE;– 7. 重命名大文件表空间
ALTER TABLESPACE big_data RENAME TO large_data;– 8. 删除大文件表空间
DROP TABLESPACE large_data INCLUDING CONTENTS AND DATAFILES;

— 1. 监控大文件表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 AS total_gb,
SUM(bytes – free_bytes)/1024/1024/1024 AS used_gb,
SUM(free_bytes)/1024/1024/1024 AS free_gb,
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
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
) GROUP BY tablespace_name;– 2. 监控大文件表空间中的段
SELECT tablespace_name, segment_name, segment_type, bytes/1024/1024/1024 AS size_gb
FROM dba_segments
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
ORDER BY size_gb 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 IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
GROUP BY tablespace_name;– 4. 整理大文件表空间碎片
ALTER TABLESPACE big_data COALESCE;– 5. 检查大文件表空间数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 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 IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);

— 案例：为数据仓库系统创建大文件表空间

— 1. 创建数据仓库大文件表空间
CREATE BIGFILE TABLESPACE dw_data
DATAFILE ‘/u01/app/oracle/oradata/ORCL/dw_data01.dbf’ SIZE 20G
AUTOEXTEND ON NEXT 1G MAXSIZE 100G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 32M
SEGMENT SPACE MANAGEMENT AUTO;– 2. 创建索引大文件表空间
CREATE BIGFILE TABLESPACE dw_index
DATAFILE ‘/u01/app/oracle/oradata/ORCL/dw_index01.dbf’ SIZE 10G
AUTOEXTEND ON NEXT 500M MAXSIZE 50G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 16M
SEGMENT SPACE MANAGEMENT AUTO;– 3. 创建LOB大文件表空间
CREATE BIGFILE TABLESPACE dw_lob
DATAFILE ‘/u01/app/oracle/oradata/ORCL/dw_lob01.dbf’ SIZE 5G
AUTOEXTEND ON NEXT 250M MAXSIZE 25G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 8M
SEGMENT SPACE MANAGEMENT AUTO;– 4. 为数据仓库用户分配表空间
CREATE USER dw_user IDENTIFIED BY password
DEFAULT TABLESPACE dw_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON dw_data
QUOTA UNLIMITED ON dw_index
QUOTA UNLIMITED ON dw_lob;– 5. 创建数据仓库表
CREATE TABLE dw_user.sales_fact (
sale_id NUMBER(10),
product_id NUMBER(10),
customer_id NUMBER(10),
sale_date DATE,
quantity NUMBER(10),
amount NUMBER(12,2),
notes CLOB
) TABLESPACE dw_data
LOB (notes) STORE AS (TABLESPACE dw_lob);– 6. 创建索引
CREATE INDEX dw_user.sales_fact_product_idx
ON dw_user.sales_fact(product_id)
TABLESPACE dw_index;CREATE INDEX dw_user.sales_fact_date_idx
ON dw_user.sales_fact(sale_date)
TABLESPACE dw_index;– 7. 监控大文件表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 AS total_gb,
SUM(bytes – free_bytes)/1024/1024/1024 AS used_gb,
SUM(free_bytes)/1024/1024/1024 AS free_gb,
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
WHERE tablespace_name IN (‘DW_DATA’, ‘DW_INDEX’, ‘DW_LOB’)
) GROUP BY tablespace_name;

— 1. 大文件表空间不足问题
— 查看大文件表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 AS total_gb,
SUM(bytes – free_bytes)/1024/1024/1024 AS used_gb,
SUM(free_bytes)/1024/1024/1024 AS free_gb,
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
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
) GROUP BY tablespace_name
ORDER BY used_percent DESC;– 解决方案：调整大文件表空间大小
ALTER TABLESPACE dw_data
RESIZE 30G;– 2. 大文件表空间自动扩展问题
— 检查大文件表空间自动扩展设置
SELECT tablespace_name, file_name, bytes/1024/1024/1024 AS size_gb,
autoextensible, maxbytes/1024/1024/1024 AS max_size_gb
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 解决方案：调整自动扩展设置
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/dw_data01.dbf’
AUTOEXTEND ON NEXT 2G MAXSIZE 150G;– 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 IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
GROUP BY tablespace_name;– 解决方案：整理碎片
ALTER TABLESPACE dw_data COALESCE;– 4. 大文件表空间性能问题
— 检查表空间中的大段
SELECT tablespace_name, segment_name, segment_type, bytes/1024/1024/1024 AS size_gb
FROM dba_segments
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
)
ORDER BY size_gb DESC;– 解决方案：优化大段存储，考虑分区表等技术

— 1. 大文件表空间无法扩展问题
— 检查大文件表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024/1024 AS size_gb,
autoextensible, maxbytes/1024/1024/1024 AS max_size_gb
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 解决方案：检查文件系统空间，调整表空间大小

— 2. 大文件表空间数据文件损坏问题
— 检查数据文件状态
SELECT file_name, status, online_status
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 解决方案：从备份恢复数据文件
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/big_data01.dbf’ OFFLINE;– 从备份恢复数据文件
— 恢复后使数据文件在线
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/big_data01.dbf’ ONLINE;– 3. 大文件表空间重命名问题
— 重命名大文件表空间
ALTER TABLESPACE old_big_tablespace RENAME TO new_big_tablespace;– 4. 大文件表空间删除问题
— 删除大文件表空间
DROP TABLESPACE big_data INCLUDING CONTENTS AND DATAFILES;– 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
AND d.tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE bigfile = ‘YES’
);– 解决方案：优化存储设备，考虑使用SSD等高性能存储