Oracle9i重建與切換臨時表空間
2024-08-29 13:40:33
供稿：網友
Oracle9i引入了全局缺省臨時表空間,缺省的假如不指定用戶臨時表空間,Oracle會為用戶指定這個缺省臨時表空間.首先查詢用戶的缺省臨時表空間: [oracle@jumper oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - PRodUCtion on Wed Apr 12 11:11:43 2006Copyright (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 - ProductionSQL> 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.dbfSQL> 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
