temp临时空间的重建和undo表空间的重建

查看目前的temp空间
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+MYTESTDISC/mytest/tempfile/temp.267.705151407

 

 

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP
SYS                            TEMP

 

 

新建temp1
SQL> CREATE  TEMPORARY TABLESPACE "TEMP1" TEMPFILE '+MYTESTDISC' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

Tablespace created

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+MYTESTDISC/mytest/tempfile/temp.267.705151407
+MYTESTDISC/mytest/tempfile/temp1.313.719856285

 

 

切换默认temp

SQL> alter database default temporary tablespace temp1;

Database altered

 

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP1
SYS                            TEMP1
SYSTEM                         TEMP1

 

drop掉以前的temp

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+MYTESTDISC/mytest/tempfile/temp1.313.719856285

 

 

完毕

 

 

查看当前undo空间

SQL> show parameter undo_tablespace;                    
                                                        
NAME                                 TYPE        VALUE  
------------------------------------ ----------- --------
undo_tablespace                      string      UNDOTBS1
                                                        
SQL> select * from dba_tablespaces;                     
                                                        
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
SYSTEM                               8192          65536
UNDOTBS1                             8192          65536
SYSAUX                               8192          65536
USERS                                8192          65536
MYTBS                                8192          65536
MYINDEXTBS                           8192          65536
TEMP1                                8192        1048576
                                                        
7 rows selected                                         

查看一下当前undo的一些具体信息

SQL> select * from dba_data_files where tablespace_name = 'UNDOTBS1';                                      
                                                                                                           
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- ----------------
+MYTESTDISC/mytest/datafile/undotbs1.265.705151307                                        2 UNDOTBS1       
                                                                                                           
SQL>                                                                                                       
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
                                                                                                           
       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS                              
---------- ---------- --------------------- ---------------------- ----------                              
         0          0   0.00035858154296875    0.00035858154296875          0                              
         6          0   0.00109100341796875    0.00109100341796875          0                              
         5          0   0.00206756591796875    0.00206756591796875          0                              
         8          0   0.00206756591796875    0.00206756591796875          0                              
        10          0   0.00304412841796875    0.00304412841796875          0                              
         1          0   0.00304412841796875    0.00304412841796875          0                              
         3          0   0.00304412841796875    0.00304412841796875          0                              
         2          0   0.00304412841796875    0.00304412841796875          0                              
         4          0   0.00402069091796875    0.00402069091796875          0                              
         7          0   0.00402069091796875    0.00402069091796875          0                              
         9          0   0.00402069091796875    0.00402069091796875          0                              
                                                                                                           
11 rows selected  

 

 

新建一个undo空间

SQL> CREATE  UNDO TABLESPACE "UNDOTBS2" DATAFILE'+MYTESTDISC' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
                                                                                                                
Tablespace created                                                                                              
                                                                                                                
SQL> select * from dba_data_files where tablespace_name in( 'UNDOTBS1','UNDOTBS2');                             
                                                                                                                
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME     
-------------------------------------------------------------------------------- ---------- ---------------------
+MYTESTDISC/mytest/datafile/undotbs1.265.705151307                                        2 UNDOTBS1            
+MYTESTDISC/mytest/datafile/undotbs2.267.719858429                                        8 UNDOTBS2            

切换undo空间

SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;

System altered

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------------- ---------------------- ----------
        15          0   0.00011444091796875    0.00011444091796875          0
        11          0   0.00011444091796875    0.00011444091796875          0
        12          0   0.00011444091796875    0.00011444091796875          0
        13          0   0.00011444091796875    0.00011444091796875          0
        14          0   0.00011444091796875    0.00011444091796875          0
        20          0   0.00011444091796875    0.00011444091796875          0
        16          0   0.00011444091796875    0.00011444091796875          0
        17          0   0.00011444091796875    0.00011444091796875          0
        18          0   0.00011444091796875    0.00011444091796875          0
        19          0   0.00011444091796875    0.00011444091796875          0
         0          0   0.00035858154296875    0.00035858154296875          0

11 rows selected

 

删除旧的

 

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped

 

完毕