oracle19c 重建temp表空间

mahan · 发表于 2023-12-10 15:36:47

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

登录/注册后可看大图

4363965756a7d52499.png (149.92 KB, 下载次数: 314)

下载附件

2023-12-10 15:36 上传

可以重启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>

帐号		自动登录	找回密码
密码			注册

[Oracle] oracle19c 重建temp表空间