Oracle10G如何重建及切换临时表空间

来源：互联网发布：云烟淘宝客助手编辑：程序博客网时间：2024/05/22 03:05

1.查询用户现在所使用的临时表空间

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW TEMP2

SYS TEMP2

SYSTEM TEMP2

DBSNMP TEMP2

SYSMAN TEMP2

SCOTT TEMP2

OUTLN TEMP2

MDSYS TEMP2

ORDSYS TEMP2

CTXSYS TEMP2

ANONYMOUS TEMP2

EXFSYS TEMP2

DMSYS TEMP2

WMSYS TEMP2

XDB TEMP2

ORDPLUGINS TEMP2

SI_INFORMTN_SCHEMA TEMP2

OLAPSYS TEMP2

MDDATA TEMP2

DIP TEMP2

2.查询临时表空间所使用的数据文件

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP02.DBF

3.创建新的临时表空间

SQL> create temporary tablespace temp tempfile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP01.DBF' size 20m;

Tablespace created

4.切换到新创建后的临时表空间

SQL> alter database default temporary tablespace temp;

Database altered

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW TEMP

SYS TEMP

SYSTEM TEMP

DBSNMP TEMP

SYSMAN TEMP

SCOTT TEMP

OUTLN TEMP

MDSYS TEMP

ORDSYS TEMP

CTXSYS TEMP

ANONYMOUS TEMP

EXFSYS TEMP

DMSYS TEMP

WMSYS TEMP

XDB TEMP

ORDPLUGINS TEMP

SI_INFORMTN_SCHEMA TEMP

OLAPSYS TEMP

MDDATA TEMP

DIP TEMP

5.删除原临时表空间

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped

SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible

2 from dba_temp_files

3 /

FILE_NAME TABLESPACE_NAME MB AUTOEXTENSIBLE

-------------------------------------------------------------------------------- ------------------------------ ---------- --------------

E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP01.DBF TEMP 20 NO