创建并切换UNDO表空间

长平狐 发布于 2013/09/17 15:12
阅读 2K+
收藏 0

ORACLE数据库只有一个当前UNDO有空间。

1.查看当前UNDO表空间

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

查询还原段数据的视图

V$rollname

v$rollstat

v$session

v$transaction

dba_rollback_segs

SQL> select  to_char(begin_time,'HH:MM:SS')  begin_time,to_char(end_time,'hh🇲🇲s')  end_time,undoblks,txncount,maxquerylen   from v$undostat;

BEGIN_TI END_TIME   UNDOBLKS  TXNCOUNT MAXQUERYLEN

-------- -------- ---------- ---------------------

02:01:41 03:01:29         78        112         681

02:01:41 02:01:41         12        113         376

02:01:41 02:01:41         11        120         973

02:01:41 02:01:41          6         72         364

02:01:41 02:01:41         10         98         959

02:01:41 02:01:41          9         67         349

01:01:41 02:01:41         26        129         944

01:01:41 01:01:41          6         67         336

01:01:41 01:01:41         58        511         927

01:01:41 01:01:41         20        142         319

2.创建UNDO表空间

SQL> create undo tablespace test1_undo datafile 'f:\sysdata\test1_undo.dbf' size 2m;
Tablespace created
SQL> col file_name for a30
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files;
 
   FILE_ID FILE_NAME                      TABLESPACE_NAME                        MB
---------- ------------------------------ ------------------------------ ----------
         5 F:\SYSDATA\TEST1_UNDO.DBF      TEST1_UNDO                              2
         4 F:\USERDATA\USERS01.DBF        USERS                                6.25
         3 F:\SYSDATA\UNDOTBS01.DBF       UNDOTBS1                               85
         2 F:\SYSDATA\SYSAUX01.DBF        SYSAUX                                760
         1 F:\SYSDATA\SYSTEM01.DBF        SYSTEM                                770
SQL>  select tablespace_name,block_size,status,contents,segment_space_management from dba_tablespaces;
TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS  SEGMENT_SPACE_MANAGEMENT
------------------------------ ---------- --------- --------- ------------------------
SYSTEM                               8192 ONLINE    PERMANENT MANUAL
SYSAUX                               8192 ONLINE    PERMANENT AUTO
UNDOTBS1                             8192 ONLINE    UNDO      MANUAL
TEMP                                 8192 ONLINE    TEMPORARY MANUAL
USERS                                8192 ONLINE    PERMANENT AUTO

TEST1_UNDO                           8192 ONLINE    UNDO      MANUAL


3.切换UNDO表空间并验证

SQL> alter system set undo_tablespace=test1_undo;
System altered
SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      TEST1_UNDO


删除:--需要切换回原UNDO表空间后。
10:44:14 SQL> drop tablespace test1_undo including contents;



原文链接:http://blog.csdn.net/q947817003/article/details/11353841
加载中
返回顶部
顶部