增加自动扩展临时表空间及改变默认表空间

增加自动扩展临时表空间及改变默认表空间

1、查看临时表空间的大小 SQL> l   1  select a.name "tablespace",b.name "name",b.bytes/1024/1024 "MB"   2  from v$tablespace a,v$tempfile b   3* where a.ts#=b.ts# SQL> /

table name                                                       MB             ----- -------------------------------------------------- ----------             TEMP  D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF           20            

                                     NUMBER

2、查看临时表空间大小、是否自动扩展

SQL> l   1* select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files SQL> /

FILE_NAME                                                  MB AUT               -------------------------------------------------- ---------- ---               TABLESPACE_NAME                                                                 ------------------------------                                                  D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF           20 YES               TEMP

3、缩小临时表空间文件的大小，把20M缩小成5M SQL> alter database tempfile   2  'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF' resize 5M;

数据库 已更改。

 

SQL> select name,bytes/1024/1024 "MB" from v$tempfile;

NAME                                                       MB                   -------------------------------------------------- ----------                   D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF            5                  

 

4、新创建一个自动扩展的临时表空间 SQL> create temporary tablespace temp02   2  tempfile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP02.DBF'   3  size 4M autoextend on;

表空间已创建。

 

创建表空间时设置数据文件的最大限制值：

SQL> create temporary tablespace temp02   2  tempfile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP02.DBF'   3  size 4M autoextend on maxsize 10M;

表空间已创建。

5、查询目前数据库中默认的临时表空间

SQL> l   1  select * from database_properties   2* where property_name like 'DEFAULT%' SQL> /

PROPERTY_NAM PROPERTY_VAL DESCRIPTION                                           ------------ ------------ --------------------                                  DEFAULT_TEMP TEMP         Name of default temp                                  _TABLESPACE               orary tablespace                                                                                                                      DEFAULT_PERM USERS        Name of default perm                                  ANENT_TABLES              anent tablespace                                      PACE                                                                                                                                                            DEFAULT_TBS_ SMALLFILE    Default tablespace t                                  TYPE                      ype                                                                                                                                                                          6、查询所有的临时表空间                                                                        SQL> l   1* select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files SQL> /

FILE_NAME                      TABLESPA         MB AUT                          ------------------------------ -------- ---------- ---                          D:/ORACLE/PRODUCT/10.2.0/ORADA TEMP              5 YES                          TA/ORCL/TEMP01.DBF                                                                                                                                              D:/ORACLE/PRODUCT/10.2.0/ORADA TEMP02            4 YES                          TA/ORCL/TEMP02.DBF                                                                                                                                              7、更改数据库的默认表空间 SQL> alter database default temporary tablespace temp02;

数据库已更改。

8、重新查看数据库的默认表空间 SQL> select * from database_properties   2  where property_name like 'DEFAULT%';

PROPERTY_NAM PROPERTY_VAL DESCRIPTION                                           ------------ ------------ --------------------                                  DEFAULT_TEMP TEMP02       Name of default temp                                  _TABLESPACE               orary tablespace                                                                                                                      DEFAULT_PERM USERS        Name of default perm                                  ANENT_TABLES              anent tablespace                                      PACE                                                                                                                                                            DEFAULT_TBS_ SMALLFILE    Default tablespace t                                  TYPE                      ype                                                                                                                                   9、查看临时表空间的数据文件的状态 SQL> select file#,status,bytes/1024/1024 "MB",name from v$tempfile;

     FILE# STATUS          MB NAME                                              ---------- ------- ---------- --------------------------------------------------          1 ONLINE           5 D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF           2 ONLINE           4 D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP02.DBF 

 

10、删除临时表空间,不能是数据库的默认临时表空间

SQL> drop tablespace temp;

表空间已删除。

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPA STATUS    CONTENTS                                                     -------- --------- ---------                                                    SYSTEM   ONLINE    PERMANENT                                                    UNDOTBS1 ONLINE    UNDO                                                         SYSAUX   ONLINE    PERMANENT                                                    USERS    ONLINE    PERMANENT                                                    EXAMPLE  ONLINE    PERMANENT                                                    TEMP02   ONLINE    TEMPORARY                                                   

已选择6行。

这样就可以在操作系统上手工删除临时表空间的数据文件。