22.Oracle表空间扩容

1.查看当前表空间状态，使用百分比

SELECT SYS_CONTEXT ('USERENV', 'DB_UNIQUE_NAME') DB_UNIQUE_NAME,
D.TABLESPACE_NAME,
MAX_SPACE "MAX_SIZE(GB)",
SPACE "TOTAL_SIZE(GB)",
SPACE - NVL(FREE_SPACE, 0) "USED_SIZE(GB)",
ROUND(((SPACE - NVL(FREE_SPACE, 0)) /decode(MAX_SPACE,0,1,SPACE)) * 100, 2) "EXTEND_RATE(%)",
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / decode(SPACE,0,1,SPACE)) * 100, 2) "PCT_USED_RATE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(MAX_SPACE) MAX_SPACE,
SUM(SPACE) SPACE,
SUM(BLOCKS) BLOCKS
FROM (SELECT TABLESPACE_NAME,
ROUND(decode(AUTOEXTENSIBLE,
'YES',
SUM(MAXBYTES) / (1024 * 1024 * 1024),
SUM(BYTES) / (1024 * 1024 * 1024)),
2) MAX_SPACE,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE)
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
dba_tablespaces G
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = G.TABLESPACE_NAME
and G.TABLESPACE_NAME='TBSP_MGR' ---指定表空间名。
order by 2 ;

2.查看数据文件。

set line 200
col file_name for a50
col tablespace_name for a30
col status for a10
select /*+ rule */ FILE_NAME,tablespace_name,status,round(bytes/1024/1024/1024,2) CUR_GB,AUTOEXTENSIBLE AUTOEXT,round(MAXBYTES/1024/1024/1024,2) MAX_GB
from dba_data_files where tablespace_name='TBSP_DS_ODS_FISP' ORDER BY 1;

3.修改或者增加数据文件。
--修改数据文件大小。默认最大可增加到32G。创建文件时不要指定边界值32G ,否则无法创建成功，可以比边界值稍微小些。
alter database datafile '/u02/oradata/orcl/tbsp_ds_ods_fisp.dbf' resize 10g ;

--增加数据文件。
alter database add datafile '/u02/oradata/orcl/tbsp_ds_ods_fisp02.dbf' size 10g autoextend on;