oracle19c重建temp表空间

版本oracle19c rac

2.create temporary tablespace  TEMP2 TEMPFILE '+DATADG01' SIZE 1g AUTOEXTEND off;  --创建中转临时表空间

3.alter database default  temporary tablespace  TEMP2; --改变缺省临时表空间 为刚刚创建的新临时表空间temp2

4.drop tablespace temp including contents and datafiles;--删除原来临时表空间

如果删除这步报错：

On : 12.2.0.1 version,

Not able to drop Temporary tablespace

The issue can be reproduced at will with the following steps:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Getting Error 

ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is blocked due to sort segment

可以重启oracle数据库，然后再执行删除命令。

5.create temporary tablespace  TEMP TEMPFILE '+DATADG01' SIZE 20g AUTOEXTEND off;   --重新创建临时表空间

6.alter database default  temporary tablespace  temp; --重置缺省临时表空间为新建的temp表空间

7.drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间

8.alter user roll temporary tablespace  temp;   --重新指定用户表空间为重建的临时表空间

创建SOE表空间
create tablespace SOE datafile '+DATADG01'  size 30g AUTOEXTEND off;
循环增加SOE表空间至1.5T
alter tablespace SOE add datafile '+DATADG01' size 30g AUTOEXTEND off;
循环增加TEMP表空间大于180G
alter tablespace TEMP add tempfile '+DG_DATA' size 30G;
 

如果要在原来的temp表空间中删除某个临时数据文件可以如下处理：

oracle@rac1[/home/oracle]$export ORACLE_SID=rb1
oracle@rac1[/home/oracle]$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 14 09:52:31 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> desc dba_temp_files ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(7)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files ;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
+DATADG/rb/tempfile/temp.274.1080845037
TEMP
           .01953125

SQL> alter tablespace temp add tempfile '+DATADG' size 100M autoextend off ;

Tablespace altered.

SQL> alter database tempfile '+DATADG/rb/tempfile/temp.274.1080845037' offline ;

Database altered.

SQL> alter tablespace temp drop tempfile '+DATADG/rb/tempfile/temp.274.1080845037';

Tablespace altered.

SQL>
SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files ;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
+DATADG/rb/tempfile/temp.291.1085911021
TEMP
           .09765625

SQL>