RAC环境处理undo表空间过大的问题
RAC环境处理undo表空间过大的问题
RAC环境处理undo表空间过大的问题:
- SQL> conn /as sysdba
- Connected.
- SQL> select instance_number,instance_name from gv$instance;
- INSTANCE_NUMBER INSTANCE_NAME
- --------------- ----------------
- 1 unipsms1
- 2 unipsms2
- SQL> select instance_number,instance_name from v$instance;
- INSTANCE_NUMBER INSTANCE_NAME
- --------------- ----------------
- 2 unipsms2
- SQL> show parameter undo_tablespace
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- undo_tablespace string UNDOTBS2
- SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';
- TS# NAME
- ---------- ------------------------------
- 4 UNDOTBS2
- SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;
- TS# NAME BYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024
- ---------- ------------------------------------------------------------ -------------------- ---------------------------
- 4 +DG01R10/unipsms/datafile/undotbs2.267.746473789 7887.5791 4
- SQL> create bigfile undo tablespace UNDOTBS3 datafile '+DG01R10' size 500g autoextend on next 1g maxsize unlimited;
- 表空间已创建。
- SQL> alter system set undo_tablespace=UNDOTBS3 scope=both sid='unipsms2';
- 系统已更改。
- SQL> show parameter undo_tablespace;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- undo_tablespace string UNDOTBS3
- SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME = 'UNDOTBS2' and status = 'ONLINE';
- no rows selected
- SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';
- TS# NAME
- ---------- ------------------------------------------------------------
- 4 UNDOTBS2
- SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;
- TS# NAME BYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024
- ---------- ------------------------------------------------------------ -------------------- ---------------------------
- 4 +DG01R10/unipsms/datafile/undotbs2.267.746473789 7887.5791 4
- SQL> drop tablespace UNDOTBS2 including contents and datafiles;
- Tablespace dropped.
- SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';
- no rows selected
- SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;
- no rows selected
评论暂时关闭