刪除重複行

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點鐘以前的