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表空间是自动扩展，在确定没有业务进行的情况下，关掉自动扩展http://www.cndba.cn/hbhe0316/article/4966http://www.cndba.cn/hbhe0316/article/4966http://www.cndba.cn/hbhe0316/article/4966

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表空间http://www.cndba.cn/hbhe0316/article/4966http://www.cndba.cn/hbhe0316/article/4966

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表空间大小已改变http://www.cndba.cn/hbhe0316/article/4966

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