Oracle扩展Undo表空间

原创

wx62e28ac222a20 2022-08-04 09:47:28 ©著作权

文章标签 python java oracle linux 算法 文章分类 后端开发

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.查看数据文件位置

set linesize 200
col file_name for a50
col tablespace_name for a20
select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
   FILE_ID FILE_NAME                          TABLESPACE_NAME         TOTAL_MB AUT
---------- -------------------------------------------------- -------------------- ---------- ---
     1 /oradata/ORCL/system01.dbf               SYSTEM              980 YES
     2 /oradata/ORCL/rmants.dbf                  RMAN_TS             1024 NO
     3 /oradata/ORCL/sysaux01.dbf               SYSAUX             1160 YES
     4 /oradata/ORCL/undotbs01.dbf              UNDOTBS1             8715 YES
     5 /oradata/ORCL/ogg_tbs.dbf                  OGG_TBS             5120 YES
     7 /oradata/ORCL/users01.dbf                  USERS              1871.25 YES

2.如果UNDO表空间是自动扩展，在确定没有业务进行的情况下，关掉自动扩展

alter database datafile '/oradata/ORCL/undotbs01.dbf' autoextend off;

select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
   FILE_ID FILE_NAME                          TABLESPACE_NAME         TOTAL_MB AUT
---------- -------------------------------------------------- -------------------- ---------- ---
     1 /oradata/ORCL/system01.dbf               SYSTEM              980 YES
     2 /oradata/ORCL/rmants.dbf                  RMAN_TS             1024 NO
     3 /oradata/ORCL/sysaux01.dbf               SYSAUX             1160 YES
     4 /oradata/ORCL/undotbs01.dbf              UNDOTBS1             8715 NO
     5 /oradata/ORCL/ogg_tbs.dbf                  OGG_TBS             5120 YES
     7 /oradata/ORCL/users01.dbf                  USERS              1871.25 YES

6 rows selected.

3.添加数据文件，扩展UNDO表空间

alter tablespace UNDOTBS1 add datafile '/oradata/ORCL/undotbs02.dbf' size 1024M;

查询数据文件，显示已添加成功

select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;

   FILE_ID FILE_NAME                          TABLESPACE_NAME         TOTAL_MB AUT
---------- -------------------------------------------------- -------------------- ---------- ---
     1 /oradata/ORCL/system01.dbf               SYSTEM              980 YES
     2 /oradata/ORCL/rmants.dbf                  RMAN_TS             1024 NO
     3 /oradata/ORCL/sysaux01.dbf               SYSAUX             1160 YES
     4 /oradata/ORCL/undotbs01.dbf              UNDOTBS1             8715 NO
     5 /oradata/ORCL/ogg_tbs.dbf                  OGG_TBS             5120 YES
     7 /oradata/ORCL/users01.dbf                  USERS              1871.25 YES
     8 /oradata/ORCL/undotbs02.dbf              UNDOTBS1             1024 NO

4.查询表空间大小，UNDO表空间大小已改变

select tablespace_name,autoextensible,sum(bytes)/1024/1024 total_mb from dba_data_files group by tablespace_name,autoextensible;

TABLESPACE_NAME      AUT   TOTAL_MB
-------------------- --- ----------
RMAN_TS          NO        1024
SYSTEM             YES    980
SYSAUX             YES       1160
USERS             YES    1871.25
OGG_TBS          YES       5120
UNDOTBS1         NO        9739

oracle,linux