1、查看数据文件使用率
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
2、查看数据文件已经扩展性
SELECT
file_id,
file_name,
tablespace_name,autoextensible as 自动扩展性,status,
round(bytes / (1024 * 1024 ), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
3、表空间扩容 (一个数据文件最大只能32G)
3.1 手工改变已经存在数据文件的大小
格式:alter database datafile ‘数据文件位置’ resize 数值
SQL> alter database datafile '/zhu/test1.dbf' resize 1000M;
3.2 增加、删除数据文件
(1)增加
alter database datafile add datafile '数据文件位置.dbf'
alter database datafile drop datafile '数据文件位置.dbf'
删除数据文件时候要把数据文件置于离线状态,特殊的数据文件不能删除,入redo、undo等
示例:
alter tablespace LUAN_ZXK add datafile '/inspur/app/oracle/oradata/orcl/luan_zxk2.dbf' size 100M;
(2)允许已存在的数据文件自动增长
alter database datafile '数据文件位置' autoextend on next 每次增长的数值 maxsize 最大可以增长到的数值。
示例:
alter database datafile '/inspur/app/oracle/oradata/orcl/luan_zxk2.dbf' autoextend on next 100 M maxsize 32479 M
(3)添加一个数据文件并设置自动增长(一步到位)
alter tablespace L_AUG add datafile '/inspur/app/oracle/oradata/orcl/L_AUG_2.dbf'
size 100M autoextend on next 10M maxsize 32479 M;
4、临时表空间调整
(1)为临时表空间添加数据文件
alter tablespace temp_name add tempfile '/oradata/temp02.dbf' size 2G autoextend on;
(2)修改临时表空间数据文件大小
alter database tempfile '/oradata/temp02.dbf' resize 4G;