oracle19c 重建temp表空间 - 重庆思庄Oracle、Redhat认证学习论坛

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

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、Redhat认证学习论坛 (http://bbs.cqsztech.com/)