Oracle一些问题以及注意事项

--null 值 3. 如果集合中含有null,不能使用not in; 但可以使用in;
       select * from emp where deptno  in(10,20,null); 
        条件返回true因为 in 表示 or的意思(or deptno=10 or deptno=20 or deptno = null)
         加入deptno 有一列为空的相当于就是 deptno=null  不会查询出数据
        
         select * from emp where deptno not  in(10,20,null);  //返回false
   (or deptno !=10 or deptno !=20 or deptno  != null

         因为oracle 中 null是不等于null的 所以条件为假!


物化视图
        
物化视图[1]  (Materialized View)在9i以前的版本叫做快照(SNAPSHOT),从9i开始改名叫做物化视图。它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。
其中物化视图有三种:聚集物化视图、包含连接物化视图、嵌套物化视图。但三种物化视图的快速刷新的限制条件有很大区别,而其他方面则区别不大。


undo 数据(还原数据)(重要)



 回滚段可以说是用来保持数据变化前映象而提供一致读和保障事务完整性的一段磁盘存储区域。当一个事务开始的时候,会首先把变化前的数据和变化后的数据先写入日志缓冲区,然后把变化前的数据写入回滚段,最后才在数据缓冲区中修改(日志缓冲区内容在满足一定的条件后可能被写入磁盘,但在事务提交的时候日志必须写入磁盘,而数据缓冲区中的数据依赖于检查点的发生和DBWR进程的活动)

    Rollback是一个代价昂贵的操作,如果一个系统的事务回退率过高,应该检查系统是否正常或者程序设计思路是否存在问题。查询数据库启动依赖的

事务回退率,如果发现太高,一定要引起重视。

--查询回退率的sql

SELECT NAME, VALUE
   FROM v$sysstat
WHERE NAME IN ('user commits', 'transaction rollbacks');

     关于回滚段的数据,如果是delete操作,则回滚段将回记录整个行的数据;如果是update,则只记录被修改了的字段的变化前的数据(前映像);如果是insert,则只记录插入记录的rowid。所以,假如commit,那么回滚段中简单标记该事务已经提交;假如rollback,则操作是 delete的话,把回滚段中的数据重新写回数据块,操作是update的话则把变化前的数据修改回去,操作是insert的话则根据rowid把该记录删除。这个过程是保障事务的完整性,保障数据不会丢失。

    一致性读(consisitent reads) Oralce的查询集是根据时间点来判定的。Oracle内部通过系统改变号SC作为相对时间点的标准,任何对数据库的改变都会产生SCN,对数据块的数据改变的时候会把该改变所对应的SCN记录在块中。假设查询开始的时候SCN为T,则在查询所扫描的数据块中,如果数据块的COMMIT SCN小鱼T,则查询接受该数据,如果COMMIT SCN大于T或者说还没有产生COMMIT SCN,则查询会尝试去回滚段中查找数据。这保证了数据的读取时间点的一致性。

在通过回滚段中获取数据的时候,本质上是把数据缓冲区中的数据块做一个拷贝,然后将回滚段中记录的内容恢复到该块中,然后查询使用这个块来进行读取。

系统回滚段和延迟回滚段

SYSTEM回滚段是创建在系统表空间中,主要用于系统级的事务和分配普通事务于其他回滚段上。当手工创建数据后需要创建普通回滚段之前必须首先创建系统回滚段。按oracle文档说,当普通事务异常多的事情可能会使用系统回滚段的情况。正常情况下,系统回滚段主要用于两个方面:一是系统事务,不如针对数据字典的操作的truncate table 和 drop table。如果truncate or drop table的过程中没有成果,则系统会根据系统回滚段中的数据字典操作信息对该DDL操作进行回退。另一个方面,就是延迟回滚段(Deferred Rollback Segment)。延迟回滚段表示,当我们使一个表空间OFFLINE之后,由于表空间不可用,这个时候若有事务数据位于该空间并执行回滚命令,在client看起来该事务已经回滚,但对于数据块来说回滚并没有真正完成,这个时候数据库将该回滚信息写入系统回滚段(这就是延迟回滚段),等表空间重新ONLINE的时候,数据块从系统回滚段中将回滚信息写入表空间。

回退段的设置和管理

Oracle9i之前需要手动设置回退段,我们需要考虑如下问题:

1.系统并发事务数有多少
2.系统是否存在大查询或者大事务,是否频繁
3.能提供给系统回滚段的表空间的磁盘空间是多少

 

9i的UNDO TABLESPACE

从9i开始,推荐使用UNDO TABLESPACE,系统自动管理回滚段

Sql> show parameter undo
undo_management                       string    AUTO
undo_retention                        integer   900
undo_tablespace                       string    UNDOTBS1

UNDO TABLESPACE变的很大,我们不能缩小,这个时候我们需要考虑创建新的UNDO TABLESPACE,然后换到新的表空间。这时即使UNDO表空间有事务也可以切换,只不过不能立即删除该表空间,切换之后等到原来的表空间中所有的事务出力完毕,并且达到undo_retention的时间后,就可以drop原来的UNDO表空间。

SQL> alter system set undo_tablespace = undotbs02;

        System altered.

切换了UNDO表空间后应该修改pfile或者spfile,使得下次启动应用新的UNDO表空间。

 

回滚段著名的ORA-01555问题

从应用角度来看ORA-01555

1.查询执行时间太长。首先是优化查询,然后考虑在数据块不繁忙的时候运行,最后考虑加大回滚段。

2.过渡频繁的提交。把能够成批提交的单条事务改成成批提交

3.exp的时候使用而来consistent = y. 这个参数主要是为了保证在exp的时候使得所有的到处的表在时间点上具有一致性,避免存在主外键关系的表由于不同的时间点的不一致而破坏了数据的完整性。建议该操作在系统空闲的时候进行。

4.由于回滚段回缩导致回滚段还没有循环使用的情况下就出现了回滚段中找不着数据的情况。只能加大回滚段增大optimal设置。

 

UNDO scripts

查询数据块当前某个session的事务所使用的回滚段大小

SELECT b.SID, a.xidusn, a.xidusn
   FROM v$transaction a, v$session b
WHERE a.addr = b.taddr

 

回滚段表空间中的一个数据文件丢失或者损害的恢复方法

…………….


由于对应的undo block/或者undo header slot信息被覆盖,导致consistent read不能完成, 系统会报出ora-01555的错误信息..

出现这种情况的原因可能有以下几种.

1. 对于早期的manual undo, 可能是由于系统设置了optimal size, rollback segment wrap 导致部分undo信息被丢弃..

2. 由于undo retention 设置的时间小于sql执行的长度,这样从这条sql开始时候其他session产生的部分undo由于retention的原因被覆盖,导致无法完成consistent read.

3. 由于commit over fetch loop. 导致transaction slot 被覆盖,无法完成consistent read ..


ORA-01555错误浅析-- http://www.hellodba.com/Doc/ORA-01555_analysis(1).htm

总结出以下方法来解决1555错误问题:

1、扩大回滚段

因为回滚段是循环使用的,如果回滚段足够大,那么那些被提交的数据信息就能保存足够长的时间是那些大事务完成一致性读取。

2、增加undo_retention时间

undo_retention规定的时间内,任何其他事务都不能覆盖这些数据。

3、优化相关查询语句,减少一致性读。

减少查询语句的一致性读,就降低读取不到回滚段数据的风险。这一点非常重要!

4、减少不必要的事务提交

提交的事务越少,产生的回滚段信息就越少。

5、对大事务指定回滚段

通过以下语句可以指定事务的回滚段:

SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment

给大事务指定回滚段,即降低大事务回滚信息覆盖其他事务的回滚信息的几率,又降低了他自身的回滚信息被覆盖的几率。大事务的存在,往往是1555错误产生的诱因。

6、使用游标时尽量使用显式游标,并且只在需要的时候打开游标,同时将所有可以在游标外做的操作从游标循环中拿出。

当游标打开时,查询就开始了,直到游标关闭。减少游标的打开时间,就减少了1555错误发生的几率。



碎片整理--移除碎片 move

10g之前

两种方法:

  • 导出表,删除表,再导入表
  • alter table move


一般选择第二种,需要重建索引。

10g

 10g 开始,提供一个 shrink 命令,需要表空间是基于自动段管理的。

可以分成两步操作:

-- 整理表,不影响DML操作

SQL> alter table TABLE_NAME shrink space compact;

-- 重置高水位,此时不能有DML操作

SQL> alter table TABLE_NAME shrink space;

也可以一步到位:

-- 整理表,并重置高水位

SQL> alter table TABLE_NAME shrink space;

shrink 的优势:

  • 不需要重建索引。
  • 可以在线操作。
不需要空闲空间,alter move需要跟当前表一样大小的空闲空间。

管理用户的安全

闪回技术  
  10G  6种

闪回恢复区(Flashback Recovery Area)




    11G 7 种

闪回技术组要是7个功能的总称:

1、闪回查询(闪回时间点查询,闪回版本查询)     

2、闪回数据归档

3、闪回事务查询、         

4、闪回事务、         

5、闪回表
6、闪回删表                                

7、闪回数据库



  




本地事务  全局事务



Orcal 集群


j2ee  13个组件


注意:


    删除的表放入回收站
    查询语句 大小写敏感
    不允许日期+日期
    delete和truncate的区别: 
  1.  在oracle里面 delete 性能比truncate 好一些 
  2. delete会产生碎片;truncate不会
  3. delete可以闪回  truncate不可以((flashback)
  4. delete不会释放空间 truncate会
    (可以回滚)    (不可以回滚)
    自然连接的查询(尽量使用层次查询)
    null 不等于null
    自动开启事物
    where和having的区别:where后面不能使用多行函数 having 可以
    order by 4 desc  (可以根据序号来排序,默认从一开始,表示select 语句中列出现在序号位置)
  •  如果排序在列存在null值,会排在最前面,因为null值最大,如果要将null值的数据弄到后面显示
    • select * from emp order by 6 desc nulls last;


> 原文链接 |
posted @   汉源魂  阅读(6)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示