临时表空间的管理与受损恢复

      Oracle 临时表空间是Oracle数据库的重要组成部分,尽管该部分并没有cont体系结构上得以展现,但其重要地位也是不容忽视的。尤其是 对于大型的频繁操作,如创建索引,排序等等都需要在临时表空间完成来减少内存的开销。当然对于查询性能要求较高的应尽可能的避免在磁盘 上完成这些操作。本文主要描述的是临时表空间的管理与受损恢复。

一、临时表空间的特性与注意事项   1.特性        用户存储临时数据的表空间        临时数据通常只在一个数据库会话期间内存在的数据,分为两种形式,排序数据和全局临时表        临时数据不会被写入存储永久对象的普通表空间内,而是存储在临时表空间的临时段中        临时表空间临时性导致不需要备份该类型的表空间,RMAN也不支持对临时表空间的备份        对于临时数据的处理,不会生成重做,也不会生成撤销数据        临时表空间的数据文件不能置为只读、不能重命名        监时表空间的数据文件的日志方式总是NOLOGGING        使用临时表空间的主要操作         CREATE INDEX,ALTER INDEX ...REBUILD,ORDER BY,GROUP BY,DISTINCT,UNION,INTERSECT,MINUS,SORT-MERGER,JOIN,ANALYZE   2.临时表空间使用的注意事项        a. 每个用户都有一个缺省的临时表空间,对于临时表空间使用较高的系统,建议将临时表空间数据文件分布到不同的磁盘        b. 对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理        c. 分配用户单独临时表空间,一般是针对大型产品数据库,OLTP数据库,数据库仓库        d. 对于小型产品不需要单独制定临时表空间,使用默认临时表空间        e. 对于临时表空间建议关闭自动扩展功能,避免过度扩展所致的空间压力    关于临时表空间的创建与管理请参考:Oracle 表空间与数据文件 二、临时表空间的管理   1. 查看缺省的临时表空间

SQL> select property_name,property_value from database_properties       
  2  where property_name like 'DEFAULT%';                               
                                                                        
PROPERTY_NAME                  PROPERTY_VALUE                           
------------------------------ --------------------------------         
DEFAULT_TEMP_TABLESPACE        TEMP                                     
DEFAULT_PERMANENT_TABLESPACE   USERS                                    
DEFAULT_TBS_TYPE               SMALLFILE                                

  2.查看临时表空间的大小及位置    

SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) bytes,status                      
  2  from v$tablespace s,v$tempfile t                                                   
  3  where s.ts# = t.ts#;                                                               
                                                                                        
TBSNAME    NAME                                               BYTES   STATUS            
---------- --------------------------------------------- ----------   -------           
TEMP       /u01/app/oracle/oradata/orcl/temp01.dbf               30   ONLINE            
                                                                                        
SQL> select tablespace_name,file_name,bytes/1024/1024 Size_MB from dba_temp_files;      
                                                                                        
TABLESPACE FILE_NAME                                        SIZE_MB                     
---------- --------------------------------------------- ----------                     
TEMP       /u01/app/oracle/oradata/orcl/temp01.dbf               30                     
                                                                                        
SQL> select tablespace_name,logging,allocation_type                                     
  2  from dba_tablespaces where tablespace_name='TEMP';                                 
                                                                                        
TABLESPACE LOGGING   ALLOCATIO                                                          
---------- --------- ---------                                                          
TEMP       NOLOGGING UNIFORM                                                            

  3. 临时表文件大小和已使用空间    

SELECT t1."Tablespace" "Tablespace",                                                        
       t1."Total (G)" "Total (G)",                                                          
       nvl(t2."Used (G)", 0) "Used(G)",                                                     
       t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"                                    
FROM   (SELECT tablespace_name "Tablespace",                                                
               to_char((SUM(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total (G)"     
        FROM   dba_temp_files                                                               
        GROUP  BY tablespace_name                                                           
        UNION                                                                               
        SELECT tablespace_name "Tablespace",                                                
               to_char((SUM(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total (G)"     
        FROM   dba_data_files                                                               
        WHERE  tablespace_name LIKE 'TEMP%'                                                 
        GROUP  BY tablespace_name) t1,                                                      
       (SELECT tablespace, round(SUM(blocks) * 8 / 1024 /1024) "Used (G)"                         
        FROM   v$sort_usage                                                                 
        GROUP  BY tablespace) t2                                                            
WHERE  t1."Tablespace" = t2.tablespace(+);                                                  
                                                                                            
                                                                                            
Tablespace                     Total (G)          Used(G)   Free (G)                        
------------------------------ --------------- ---------- ----------                        
GOEX_TEMP                               31.999          1     30.999                        
FIX_TEMP                                 0.098          0       .098                        
TEMP                                     0.195          0       .195                        

  4. 查看当前临时表使用空间大小与正在占用临时表空间的sql语句  

-->使用临时段的SQL语句                                                        
	SELECT sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text                  
	FROM   v$sort_usage sort, v$session sess, v$sql SQL                         
	WHERE  sort.SESSION_ADDR = sess.SADDR                                       
	       AND SQL.ADDRESS = sess.SQL_ADDRESS                                   
	ORDER  BY blocks DESC;                                                      
                                                                              
-->下面的查询也可以查询谁正在使用临时段                                       
	col username format a15                                                     
	col machine format a15                                                      
	col program format a30                                                      
	col tablespace format a15                                                   
	set linesize 160                                                            
	SELECT s.username                                                           
	      ,s.sid                                                                
	      ,s.serial#                                                            
	      ,s.sql_address                                                        
	      ,s.machine                                                            
	      ,s.program                                                            
	      ,su.tablespace                                                        
	      ,su.segtype                                                           
	      ,su.contents                                                          
	FROM   v$session s, v$sort_usage su                                         
	WHERE  s.saddr = su.session_addr;                                           
                                                                              
SELECT 'the ' || NAME || ' temp tablespaces ' || tablespace_name ||           
       ' idle ' ||                                                            
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||          
       '% at ' || to_char(SYSDATE, 'yyyymmddhh24miss')                        
FROM   (SELECT d.tablespace_name tablespace_name,                             
               nvl(SUM(used_blocks), 0) tot_used_blocks,                      
               SUM(blocks) total_blocks                                       
        FROM   v$sort_segment v, dba_temp_files d                             
        WHERE  d.tablespace_name = v.tablespace_name(+)                       
        GROUP  BY d.tablespace_name) s,                                       
       v$database;                                                            

   3. 修改默认的临时表空间            alter database default temporary tablespace tablespace_name;    4. 对于过度使用临时表空间,在允许的情况下,可以杀掉其session            alter system kill session 'sid,serial#';   三、临时表空间故障   前面已提到,临时表空间不需要备份,如果对临时表空间备份将收到错误提示  

SQL> alter tablespace temp begin backup;                        
alter tablespace temp begin backup                              
*                                                               
ERROR at line 1:                                                
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE     
                                                                
                                                                
SQL> alter temporary tablespace temp begin backup;              
alter temporary tablespace temp begin backup                    
      *                                                         
ERROR at line 1:                                                
ORA-00940: invalid ALTER command                                

  临时表空间出错的错误提示(位于告警日志中alert_orcl.log)    Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_2230.trc:    ORA-01186:file 3 failed verification tests    ORA-01157:cannot identify/lock data file 3 - see DBWR trace file    ORA-01110:data file 3:'/u01/app/oracle/oradata/orcl/temp01.dbf' 四、还原受损的临时表空间   1. 在session 1中删除临时表空间的数据文件

SQL> show user;                                    
USER is "SYS"                                      
SQL> ho rm /u01/app/oracle/oradata/orcl/temp01.dbf 
                                                   
SQL> alter system checkpoint;  -->执行检查点进程   
                                                   
System altered.                                    

  2. 在session 2中执行排序查询,提示临时数据文件错误   

SQL> show user;                                                                   
USER is "SCOTT"                                                                   
SQL> set autotrace traceonly;                                                     
SQL> select owner,object_name from big_table order by id,owner,object_name;       
select owner,object_name from big_table order by id,owner,object_name             
                              *                                                   
ERROR at line 1:                                                                  
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf'    
ORA-27037: unable to obtain file status                                           
Linux Error: 2: No such file or directory                                         
Additional information: 3                                                         

  3. 在session 1中为临时表空间增加数据文件  

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf'      
  2  size 30m autoextend on next 1m maxsize 2g;                                        
                                                                                       
Tablespace altered.                                                                    

  4. 在session 2中继续执行排序操作,此次执行成功  

SQL> select owner,object_name from big_table order by id,owner,object_name;        
                                                                                   
1000000 rows selected.                                                             

  5. 查询临时数据文件状态及文件存在性 

SQL> set linesize 160                            --> session 1中查看临时数据文件状态,大小为0,但仍然为online状态   
SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) bytes,t.status                                               
  2  from v$tablespace s,v$tempfile t                                                                              
  3  where s.ts# = t.ts#;                                                                                          
                                                                                                                   
TBSNAME              NAME                                               BYTES STATUS                               
-------------------- --------------------------------------------- ---------- -------                              
TEMP                 /u01/app/oracle/oradata/orcl/temp01.dbf                0 ONLINE                               
TEMP                 /u01/app/oracle/oradata/orcl/temp02.dbf               30 ONLINE                               
                                                                                                                   
SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf                                                                 
ls: /u01/app/oracle/oradata/orcl/temp01.dbf: No such file or directory                                             

  6. 清除控制文件内记录的不存在的临时文件信息 

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';   
                                                                                      
Tablespace altered.                                                                   

  7. 基于表空间级别的恢复操作(下面给出主要步骤,不在演示)        a. 创建一个新的临时表空间        b. 使用alter database 将用户切换到新的临时表空间,即设置新的默认的临时表空间        c. 删除受损的临时表空间

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

编程面试题:编写一个会造成数据库死锁的应用

相信对于"开发一个会产生死锁的Java应用”这类需求,大家都能顺利完成。但是如果题目要求得更具体一些,要求这个死锁发生在数据库层面,应该怎样完成呢?

12610
来自专栏杨建荣的学习笔记

MySQL在RR隔离级别下的unique失效和死锁模拟

今天在测试MySQL事务隔离级别的时候,发现了一个有趣的问题,也参考了杨一之前总结的一篇。http://blog.itpub.net/22664653/view...

38260
来自专栏沃趣科技

会话和锁信息查询视图 | 全方位认识 sys 系统库

在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中的等待事件统计视图,本期的内容先给大家介绍会话信息和锁等待信息查询视图...

17900
来自专栏杨建荣的学习笔记

迁移式升级的测试(二)(r10笔记第35天)

在之前写的一篇博文中,自己是打算对一台数据库使用Data Guard+TTS的方式来完成数据迁移和升级的工作,迁移式升级的新方案测试 (r10笔记第30天) 整...

35840
来自专栏lgp20151222

mysql explain 的extra中using index ,using where,using index condition,using index & using where理解

using where:查找使用了索引,不需要回表去查询所需的数据,查询结果是索引的一部分

22810
来自专栏数据和云

深入解析:你听说过Oracle数据库的更新重启动吗?

杨廷琨 云和恩墨高级咨询顾问, ITPUB Oracle 数据库管理版版主 ,人称 “杨长老”,十数年如一日坚持进行 Oracle 技术研究与写作,号称 ...

10240
来自专栏Clive的技术分享

Mysql锁相关锁的分类锁的适用场景MyISAM表锁MyISAM写阻塞读的例子MyISAM读阻塞写例子MyISAM并发插入MyISAM读写并发MyISAM锁调度调节MyISAM锁调度行为解决读写冲突的

锁类型/引擎 行锁 表锁 页锁 MyISAM 有 InnoDB 有 有 BDB(被InnoDB取代) 有 有 锁的分类 表...

48150
来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

42780
来自专栏用户2442861的专栏

MySQL详解--锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如...

10530
来自专栏文渊之博

比较列存储索引与行索引

原因:     之前已经写过一篇关于列存储索引的简介https://cloud.tencent.com/developer/article/1032222,很粗...

24560

扫码关注云+社区

领取腾讯云代金券