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

转载 2012年03月21日 15:19:38

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


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行。


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

 

 

From:http://space.itpub.net/12778571/viewspace-448762

 

 

BW的表空间管理

  BW作为数据仓库产品,是需要非常大的表空间,当然DB2的极限值是512G。我们可以通过设置当前默认表空间的办法来实现不同的应用放在不同的表空间。  方法很简单,在SAP的DBACOCKPIT里面包...
  • Robinyou
  • Robinyou
  • 2007-12-13 15:40:00
  • 988

表空间查询和扩展

Sql代码 ----查询表空间使用情况---     SELECT UPPER(F.TABLESPACE_NAME) "表空间名",   D.TOT_GROOTTE_MB "表空间大小(M)",   ...
  • luobailian
  • luobailian
  • 2010-10-21 17:07:00
  • 691

Oracle在不同平台间表空间迁移

平台环境:   1、源库:         Windows Service 2003 SP2 x86         Oracle Service 10.2.0.4 x86    2、目标...
  • wuweilong
  • wuweilong
  • 2011-11-19 05:29:39
  • 1869

oracle表空间查询及临时表空间扩充语句

 1.所有的表空间信息查询: SELECT * FROM ( SELECT file_name , tablespace_name ,file_id,'datafile' AS TYPE ...
  • fnzinver
  • fnzinver
  • 2015-02-12 18:39:43
  • 660

Oracle表空间,用户

创建用户的标准语法(oracle9i)CREATE USER user IDENTIFIED { BY password | EXTERNALLY | GLOBALLY A...
  • owen_008
  • owen_008
  • 2009-09-27 07:21:00
  • 293

AIX用裸设备给表空间添加数据文件

1、查看表空间使用率的SQL语句 set pagesize 200 col tablespace_name for a16 col SUM_SIZE(M)  for a20 col USED_...
  • rivarola
  • rivarola
  • 2014-11-28 15:20:17
  • 1833

oracle 11g 解决临时表空间占满问题

oracle 11g 清理临时表空间 运维人员在查询亿级数据排序时,数据库报错,提示:ora-01652无法通过128(在表空间temp中)扩展temp段,排查流程如下: 1、查询表空间使用率: se...
  • u013050593
  • u013050593
  • 2017-09-05 14:20:35
  • 3368

查看临时表空间使用情况,如何扩展表空间

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)",ROUND(...
  • paluo
  • paluo
  • 2011-12-15 09:17:51
  • 15184

Aix系统下增加Oracle temp 表空间 (裸设备方式)

查询临时表空间使用情况:   SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, USED_SPACE "USED_SP...
  • xyy27
  • xyy27
  • 2013-09-07 13:03:00
  • 1149

oracle 备份导入、删除用户命令和…

远程导入备份命令:imp cpqlzf/cpqlzf@cpql20080505 file=D:\data\20080505xzfw_new.dmp fromuser=sa touser=cpqlzf ...
  • achang21
  • achang21
  • 2013-09-15 10:12:37
  • 455
收藏助手
不良信息举报
您举报文章:增加自动扩展临时表空间及改变默认表空间
举报原因:
原因补充:

(最多只允许输入30个字)