Oracle 表空间管理

DBA随笔记 2024-12-21

TBS 的大小等同它下的数据文件大小之和，当发生 TBS 不足的问题时常用的 3 个解决办法:

1.1 查看表空间大小

-- 查看大小
SELECT TABLESPACE_NAME "TablespaceName",
       To_char(Round(BYTES  1024, 2), '99990.00') || '' "Total",
       To_char(Round(FREE  1024, 2), '99990.00') || 'G' "Free",
       To_char(Round((BYTES - FREE) / 1024, 2), '99990.00') || 'G' "Used",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00') || '%' "Present"
  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)
 ORDER BY Floor(10000 * USED / BYTES) DESC;
-- ---结果如下---
TablespaceName					Total	Free		Used	Present
-----------------------------	-----	------		-----	---------
SYSTEM							0.87    0.00G		0.87G	99.89%
SYSAUX							0.61    0.03G		0.57G   94.19%
UNDOTBS2						0.02    0.01G		0.01G   52.00%
USERS							0.00    0.00G		0.00G   40.00%

1.2 调整表空间大小的三种方式

-- 增加原数据文件大小(resize)
alter database datafile '/u01/app/oracle/oradata/orcl/bdc01.dbf' resize 100m;

-- 增加数据文件(add datafile)
alter tablespace klaus add datafile '/u01/app/oracle/oradata/orcl/abc02.dbf' size 200m;

-- 设置表空间数据文件自动增长(autoextend)
alter database datafile '/u01/app/oracle/oradata/orcl/cdb01.dbf' autoextend on next 10m maxsize 500m;

1.3 实验

-- 例:
-- 使表空间不足
SQL> select tablespace_name from user_tablespaces;
SQL> create tablespace klaus datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' size 5m;
SQL> create table scott.test1 (id int) tablespace klaus;
SQL> insert into scott.test1 values(1);
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
SQL> /
131072 rows created.
SQL> /
insert into scott.test1 select * from scott.test1
ERROR at line 1:
ORA-01653:   unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS

-- 方法一:用resize方法扩充TBS
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' resize 10m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
262144 rows created.
SQL> /
insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653:   unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS

-- 方法二:用add datafile方法扩充TBS
SQL> alter tablespace klaus add datafile '/u01/app/oracle/oradata/orcl/klaus02.dbf' size 20m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
1048576 rows created.
SQL> / insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653:   unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS

-- 方法三:用autoextend方法扩充TBS
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' autoextend on next 10m maxsize 500m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> drop tablespace klaus including contents and datafiles;

1.4 调整临时表空间

-- 查询临时表空间
select c.tablespace_name,
       to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb,
       to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb,
       to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb,
       to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         GROUP by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;
-- 临时表空间文件位置
select * from dba_temp_files;
-- 加临时表空间
ALTER TABLESPACE "TEMPSEG" ADD TEMPFILE '/u01/app/oracle/oradata/orcl/TEMPSEG02.DBF' SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE 8g;

1.5 扩展 RAC 表空间

-- 1、查看大小
SELECT TABLESPACE_NAME "TablespaceName",
       To_char(Round(BYTES / 1024, 2), '99990.00') || '' "Total",
       To_char(Round(FREE / 1024, 2), '99990.00') || 'G' "Free",
       To_char(Round((BYTES - FREE) / 1024, 2), '99990.00') || 'G' "Used",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00') || '%' "Present"
  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)
 ORDER BY Floor(10000 * USED / BYTES) DESC;
-- ---结果如下---
TablespaceName					Total	Free		Used	Present
-----------------------------	-----	------		-----	---------
SYSTEM							0.87    0.00G		0.87G	99.89%
SYSAUX							0.61    0.03G		0.57G   94.19%
UNDOTBS2						0.02    0.01G		0.01G   52.00%
USERS							0.00    0.00G		0.00G   40.00%
-- 2、数据文件
col tablespace_name format a15
col file_name format a45
col size format a5
SQL> select a.tablespace_name,a.FILE_NAME,bytes/1024/1024||'M' "size"  from  dba_data_files a order by a.FILE_NAME;
-- 查看表空间对应的数据文件
TABLESPACE_NAME FILE_NAME                                     size
--------------- --------------------------------------------- -----
SYSAUX          +DATA/ORCL/DATAFILE/sysaux.258.1084324879     620M
SYSTEM          +DATA/ORCL/DATAFILE/system.257.1084324813     890M
UNDOTBS1        +DATA/ORCL/DATAFILE/undotbs1.259.1084324903   340M
UNDOTBS2        +DATA/ORCL/DATAFILE/undotbs2.265.1084325345   25M
USERS           +DATA/ORCL/DATAFILE/users.260.1084324905      5M
-- 3、ASM查看
SQL> select name,total_mb, free_mb from v$asm_diskgroup;
-- 查看相应ASM空间是否足够
NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
ARCH                                29280      28056
DATA                                58560      55624
FRA                                117184     117028
MGMT                                39040      15380
OCR                                  9760       9424
REDO                                19520      19388
-- 4、扩展表空间
SQL> alter tablespace users add datafile '+XXX/XXX/XXXX09.dbf' size 5m;
Tablespace altered.
SQL>  select a.tablespace_name,a.FILE_NAME,bytes/1024/1024||'M' "size"  from  dba_data_files a order by a.FILE_NAME;


TABLESPACE_NAME FILE_NAME                                     size
--------------- --------------------------------------------- --------
SYSAUX          +DATA/ORCL/DATAFILE/sysaux.258.1084324879     620M
SYSTEM          +DATA/ORCL/DATAFILE/system.257.1084324813     890M
UNDOTBS1        +DATA/ORCL/DATAFILE/undotbs1.259.1084324903   340M
UNDOTBS2        +DATA/ORCL/DATAFILE/undotbs2.265.1084325345   25M
USERS           +DATA/ORCL/DATAFILE/users.260.1084324905      5M
USERS           +DATA/ORCL/DATAFILE/users.269.1109324799      5M

2 可恢复空间分配功能 Resumable

2.1 Resumable 概述

当我们往一个表里面插入大量数据时，如果某条insert语句因TBS的空间不足 (没有开启自动扩展)，会报ORA-01653: 无法扩展空间的错误，该条 SQL 语句会中断，白白浪费了时间及数据库资源。为防范这个问题，Oracle 设计了一个功能：resumable
。
在resumable
开启的情况下，如果 Oracle 执行某条 SQL 申请不到空间了，比如数据TBS，undob TBS， temporary
空间等，则会将该事务的语句挂起(suspended
)，等空间扩后，Oracle 又会使该insert语句继续进行。

2.2 Resumable 参数设置

可以通过两个级别设置resumable
。

system
级别：初始化参数RESUMABLE_TIMEOUT
非 0，这将使数据库中所有session
使用可恢复的空间分配。

session级别

alter session enable|disable resumable [TIMEOUT];

这将为当前session
设置可恢复的空间分配。因为resumable
是有资源消耗代价的，所以session
级的resumable
是比较实际的：

注意TIMEOUT
的用法，单位为秒，进一步要理解初始化参数RESUMABLE_TIMEOUT
的含义。

RESUMABLE_TIMEOUT=0
，enable session
时应该指定TIMEOUT
，否则使用缺省值7200
秒。

RESUMABLE_TIMEOUT<>0
，enable session
时可以省略TIMEOUT
，此时指定TIMEOUT
会覆盖掉参数RESUMABLE_TIMEOUT
值

2.3 实验

2.3.1 session 1

2.3.1.1 建表和表空间

-- 建个固定大小2m的 TBS，再建立属于该 TBS的表
SQL> create tablespace small datafile '/u01/app/oracle/oradata/orcl/small01.dbf' size 2m;
SQL> create table scott.test(n1 char(1000)) tablespace small;

2.3.1.2 插入数据失败

-- 向这个表插入数据， TBS满了，使for语句没有完成循环，2000条语句整体失败.
SQL>
begin
for i in 1..2000 loop
insert into scott.test values('this is test');
end loop;
commit;
end;
/
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST by 128 in tablespace SMALL	
-- 表 SCOTT.TEST 无法通过 128 (在 TBS SMALL 中) 扩展
ORA-06512: at line 3
SQL> select count(*) from scott.test;
COUNT(*)
----------
0

2.3.1.3 使能 resumable

SQL> alter session enable resumable;

2.3.1.4 再重复第2步，会话被挂起

2.3.2 session 2

2.3.2.1 查看视图的有关信息

SQL> col sql_text for a45
SQL> select session_id,sql_text,error_number from dba_resumable;
SESSION_ID	SQL_TEXT										ERROR_NUMBER
----------	-----------------------------------------------	----------------------
24			INSERT INTO SCOTT.TEST VALUES('this is test')	1653
SQL> select sid,event,seconds_in_wait from v$session_wait where sid=24;
SID			EVENT											SECONDS_IN_WAIT
---------	----------------------------------------------	----------------------
24			statement 	suspended, wait 	error 			to be cleared 	0

2.3.2.2 加扩 TBS

-- SQL> alter tablespace small add datafile '/u01/app/oracle/oradata/orcl/small02.dbf' size 10m;

2.3.2.3 看到 session1 里挂起的会话成功完成

SQL> select count(*) from scott.test;
COUNT(*)
--------
2000

2.3.2.4 删除数据文件

-- 验证结束后可以disable resumable， 并删除small  TBS及数据文件。
session 1:
SQL> alter session disable resumable;
SQL> drop tablespace small including contents and datafiles;

2.4 NOTE

1. 下列三种情况可引起resumable

a).TBS上限超出

b).Extents到达最大值

c).Quota超出

2.enable resumable可以在一个session中多次挂起执行的语句，直到disable resumable。

3.DBMS_RESUMABLE.SET_SESSION_TIMEOUT可以延长当前session的TIMEOUT，并立即有效。

文章转载自DBA随笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。