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

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--