靓靓小知识-oracle数据空间-增加数据文件

oracle数据空间

1.确定数据库所在服务器

tnsping orcl

2.查询目前表空间使用情况

select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%" 
from 
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace 
from dba_data_files 
group by tablespace_name) df, 
(select tablespace_name,round(sum(bytes)/1024/1024) freespace 
from dba_free_space 
group by tablespace_name) fs 
where df.tablespace_name=fs.tablespace_name;

3.查看表空间的数据文件使用情况

select * from dba_data_files where tablespace_name='E3_DATA'; 

select * from dba_data_files where tablespace_name='E3_INDX'; 

4.增加一个数据文件并设置自动增长

alter tablespace E3_DATA add datafile  
'/u01/app/oracle/oradata/orcl/e3_data32.dbf' 
size 10g autoextend on next 10m maxsize unlimited;


E3_INDX 增加时使用

alter tablespace E3_INDX add datafile  
'/u01/app/oracle/oradata/orcl/e3_indx08.dbf' 
size 10g autoextend on next 10m maxsize unlimited;

增加前确保数据库服务器磁盘空间充足，如果oracle有备库，确保主备库空间都充足

5.停止上一个数据文件的自动增长

alter database datafile  
'/u01/app/oracle/oradata/orcl/e3_data31.dbf' 
autoextend off; 

6.检查数据库表空间，数据文件，磁盘空间

7.查看表空间使用情况(含临时表空间)

SELECT * FROM (
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
             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
             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 4 DESC)

8.换盘增加数据文件

如果u01的磁盘不够时，需要在新加的磁盘u02上，去新增数据文件，第一次增加前需要先新建目录，授权！

mkdir -p /u02/app/oracle/oradata/orcl

chown -R oracle:oinstall /u02

chmod -R 775 /u02/app/oracle