Oracle表空间扩容

• 如果表空间满了后，会出现各种问题，比如会使expdp进行定时备份的任务失败，线上所关联业务无法正常使用等

一、表空间容量指标查询

[[email protected] ~]# su - oracle
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL> SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'SYS%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

二、表空间文件使用信息

SQL> select mAX(B.file_id) id,
B.TABLESPACE_NAME 表空间,
B.FILE_NAME 物理文件名,
B.BYTES / 1024 / 1024 大小M,
(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME;

三、查看用户表空间信息

• 注意：用户名称一定大写

SQL> select username,default_tablespace from dba_users where username='SYSTEM';

USERNAME		       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM			       SYSTEM

四、查看表空间物理文件

SQL> select file#,name from v$datafile;

• 第一步的SQL语句中已经可以看到对应用户表空的详细信息了，这里再次查询确认下;接下来针对要扩容的表空间进行扩容

五、进行表空间扩容操作

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/system01.dbf' RESIZE 2048M;

• 注意：system01.dbf为已经存在数据库中的文件，现在是在该文件的基础上改变文件的大小,路径一定写成自己查询出来的,大小根据自己服务器的实际情况添加

• 然后通过上面的查询语句再次查看表空间大小是否更改成功

SQL> alter tablespace SYSTEM add datafile '/u01/app/oracle/oradata/orcl/system02.dbf' size 128M;

• 注意：system02.dbf为新添加的数据文件，SYSTEM是要扩容的用户表空间名称; 路径一定写成自己查询出来的
• 大小单位一定要写对

1、查看表空间是否为自动扩容

SQL> select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'SYSTEM';

• 注意：SYSTEM为查看的用户表空间名称

2、开启对应表空间文件自动扩容

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/system02.dbf' autoextend on;

• 注意：这里的system02.dbf文件为需要开启自动扩容的用户表空间名称,根据自己的实际查询到的情况更改为自己的

3、关闭对应表空间文件自动扩容

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/system02.dbf' autoextend off;

• 注意：这里的system02.dbf文件为需要关闭自动扩容的用户表空间名称,根据自己的实际查询到的情况更改为自己的