创建一个新的 UNDO 表空间，并使用它。

来源：互联网发布：官职知编辑：程序博客网时间：2024/06/11 00:17

SQL> shoparameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> create undo tablespace undo1 datafile 'D:\APP\RACHEL\ORADATA\ORCL\ORCL\DATAFILE\UNDO1.DBF'size 2m;

表空间已创建。

SQL>set pagesize 50;

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024M from dba_data_files;

--查看表空间名以及大小

FILE_ID FILE_NAME TABLESPACE_NAME M

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

4D:\APP\RACHEL\ORADAT USERS 5

A\ORCL\USERS01.DBF

3 D:\APP\RACHEL\ORADAT UNDOTBS1 100

A\ORCL\UNDOTBS01.DBF

2 D:\APP\RACHEL\ORADAT SYSAUX 730

A\ORCL\SYSAUX01.DBF

1 D:\APP\RACHEL\ORADAT SYSTEM 740

A\ORCL\SYSTEM01.DBF

5 D:\APP\RACHEL\ORADAT EXAMPLE 100

A\ORCL\EXAMPLE01.DBF

6 D:\APP\RACHEL\ORADAT EXAMPLE02 5

A\ORCL\EXAMPLE02.DBF

7 D:\APP\RACHEL\ORADAT UNDO1 2

A\ORCL\ORCL\DATAFILE

\UNDO1.DBF

8 D:\APP\RACHEL\ORADAT T1_MSSM 100

A\ORCL\ORCL\DATAFILE

\O1_MF_T1_MSSM_9J117

8Z7_.DBF

9 D:\APP\RACHEL\ORADAT T2_ASSM 100

A\ORCL\ORCL\DATAFILE

\O1_MF_T2_ASSM_9J118

B34_.DBF

已选择9行。

SQL> select tablespace_name,block_size,status,contents fromdba_tablespaces;

--查看表空间的状态

TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS

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

SYSTEM 8192 ONLINE PERMANENT

SYSAUX 8192 ONLINE PERMANENT

UNDOTBS1 8192 ONLINE UNDO

TEMP 8192ONLINE TEMPORARY

USERS 8192ONLINE PERMANENT

EXAMPLE 8192 ONLINE PERMANENT

EXAMPLE02 8192 ONLINE PERMANENT

T1_MSSM 8192 ONLINE PERMANENT

T2_ASSM 8192 ONLINE PERMANENT

UNDO1 8192 ONLINE UNDO

已选择10行。

SQL> alter system set undo_tablespace=undo1;--修改undo表

系统已更改。

SQL>sho parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDO1

SQL> alter system set undo_tablespace=undotbs1;

系统已更改。

SQL>sho parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> drop tablespace undo1 including contents;--删除表空间

表空间已删除。

--EOF--

0 0