停止空想

老张的空中之家

9i 修改临时表空间

作者：admin 发表时间：三月 - 27 - 2007

Oracle9i引入了全局缺省临时表空间,缺省的如果不指定用户临时表空间,Oracle会为用户指定这个缺省临时表空间.

首先查询用户的缺省临时表空间:

[oracle@jumper oracle]$ sqlplus “/ as sysdba” 

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 11:11:43

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
—————————— ——————————
SYS                                 TEMP2
SYSTEM                         TEMP2
OUTLN                           TEMP2
EYGLE                            TEMP2
CSMIG                            TEMP2
TEST                               TEMP2
REPADMIN                    TEMP2
……

13 rows selected.

SQL> select name from v$tempfile;

NAME
———————————————————————
/opt/oracle/oradata/conner/temp02.dbf
/opt/oracle/oradata/conner/temp03.dbf

重建新的临时表空间并进行切换:

SQL> create temporary tablespace temp tempfile ‘/opt/oracle/oradata/conner/temp1.dbf’ size 10M; 

Tablespace created.

SQL> alter tablespace temp add tempfile ‘/opt/oracle/oradata/conner/temp2.dbf’ size 20M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
—————————— ——————————
SYS                                 TEMP
SYSTEM                         TEMP
OUTLN                           TEMP
EYGLE                            TEMP
CSMIG                           TEMP
TEST                               TEMP
REPADMIN                    TEMP
…….

13 rows selected.

如果原临时表空间无用户使用,我们可以删除该表空间:

SQL> drop tablespace temp2; 

Tablespace dropped.

SQL>
SQL> select name from v$tempfile;

NAME
—————————————————————
/opt/oracle/oradata/conner/temp1.dbf
/opt/oracle/oradata/conner/temp2.dbf

SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible
2  from dba_temp_files
3  /

FILE_NAME                              TABLESPACE_NAME              MB AUTOEXTENSIBLE
————————————– ——————– ———- ————–
/opt/oracle/oradata/conner/temp2.dbf   TEMP                         20 NO
/opt/oracle/oradata/conner/temp1.dbf   TEMP                         10 NO

以上转自eygle

9i不能删除表空间里没有分配extent的数据文件，但是10g可以 。

但9i可以删除临时表空间的数据文件，前提是该数据文件没有被使用。所以可以offline 然后drop

alter database tempfile ‘/u01/oradata/temp01.dbf’ drop including datafiles;

类别：Oracle

发表评论