ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql

oracle sql

原创 Linux操作系统 作者:carlos。zh 时间:2011-06-03 11:54:41 0 删除 编辑

刪除重複行

delete from emp e where e.rowid > ( select min(x.rowid)  from emp x where x.empno = e.empno  )

低效sql

/* Formatted on 2011/4/12 14:28:08 (QP5 v5.115.810.9015) */
  SELECT   EXECUTIONS,
           DISK_READS,
           BUFFER_GETS,
           ROUND ( (BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,
           ROUND (DISK_READS / EXECUTIONS, 2) Reads_per_run,
           SQL_TEXT
    FROM   V$SQLAREA
   WHERE       EXECUTIONS > 0
           AND BUFFER_GETS > 0
           AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
ORDER BY   4 DESC;

 

 

/* Formatted on 2011/6/1 08:46:14 (QP5 v5.115.810.9015) */
  SELECT   b.username,
           a.EXECUTIONS,
           a.DISK_READS,
           a.BUFFER_GETS,
           ROUND ( (a.BUFFER_GETS - a.DISK_READS) / BUFFER_GETS, 2) Hit_radio,
           ROUND (a.DISK_READS / a.EXECUTIONS, 2) Reads_per_run,
           a.sql_text
    FROM   V$SQLAREA a, dba_users b
   WHERE       EXECUTIONS > 0
           AND BUFFER_GETS > 0
           AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
           AND b.user_id = a.parsing_schema_id
ORDER BY   5  DESC;

flash back

利用flashback 查詢過去某個時間點的數據,也可以利用此,來恢復部份數據

select ename,sal from scott.emp as of timestamp to_timestamp('2011-3-30 09:30:00','yyyy-mm-dd hh24:mi:ss')
     where ename='BLAKE'


SQL > SELECT * FROM XS_JSJ AS OF TIMESTAMP  TO_TIMESTAMP('2006-5-31 16:12:25','YYYY-MM-DD HH24:MI:SS');

SQL>insert into t1  select * from t1 as  of timestamp to_timestamp('2006-5-31 16:12:25', 'YYYY-MM-DD HH24:MI:SS');


sql>select * from tomas.emp as of timestamp(trunc(sysdate-1)+9/24)  ---->昨天9點以前的數據

sql>select * from emp as of timestamp(trunc(sysdate)+8/24)  今天8點鐘以前的

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25072296/viewspace-697061/,如需转载,请注明出处,否则将追究法律责任。

上一篇: linux_vnc
下一篇: unix
请登录后发表评论 登录
全部评论

注册时间:2010-12-12

  • 博文量
    21
  • 访问量
    24581