oracle-使用技巧

alter table x nologging;
insert /*+append*/ into x (a,b,c) as select a,b,c from xxx;

# 批量删除.根据时间排序,1000条commmit一次
declare
    cursor [del_cursor] is select a.*, a.rowid row_id from [table_name] a order by a.rowid;
begin
    for v_cusor in [del_cursor] loop
        if v_cusor.[time_stamp] < to_date('2014-01-01','yyyy-mm-dd') then
            delete from [table_name] where rowid = v_cusor.row_id;
        end if;
        if mod([del_cursor]%rowcount,1000)=0 then
            commit;
        end if;
    end loop;
    commit;
end;

MERGE INTO t_canhe_family t1 USING(select a1.family_id,a1.account_money,a1.balance,a1.remaining_money from t_canhe_family_bak20161121 a1) tt ON (tt.family_id=t1.family_id)
when matched then
update set t1.account_money=tt.account_money,
t1.balance=tt.balance,
t1.remaining_money=tt.remaining_money

select /*+ gather_plan_statistics */
        * 
        from 
table(dbms_xplan.display_cursor(NVL('ajkqn4733r2qx',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline'));

SELECT 
    c.spid,
    b.sql_text, 
    a.sid, 
    a.serial#, 
    osuser, 
    machine  
FROM v$session a, v$sqlarea b ,v$process c
WHERE a.sql_address = b.address and a.paddr=c.addr and spid=&pid; 

select dbms_sqltune.report_tuning_task('tuning_sql_test') from dual;

//sys用户删除
delete from dba_advisor_tasks where task_name ='tuning_sql_test'

execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true)

NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

select OWNER, 
t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
from dba_segments t
where t.owner = 'XIANGXI' 
and t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type
order by mmm desc;

alter trigger xx_trigger disable;
alter trigger xx_trigger enable;

# 快速创建索引
create index idx_table_a on table_a(字段a,字段b) nologging parallel  4;

declare
  STR VARCHAR2(400);
begin
  -- 重建Oracle索引
  FOR TMP_IDX IN (SELECT TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME
                    FROM ALL_INDEXES
                   WHERE OWNER = 'HNACMS'
                     AND temporary = 'N'     
                     --AND TABLE_NAME = 'K_TASK'              
                     --AND TABLESPACE_NAME <> 'HNACMS_INDX'
                   ORDER BY TABLESPACE_NAME, TABLE_NAME) LOOP
    STR := 'ALTER INDEX ' || TMP_IDX.OWNER || '.' || TMP_IDX.INDEX_NAME ||
           ' Rebuild Tablespace HNACMS_INDX';
    EXECUTE IMMEDIATE STR;
  END LOOP;
end;

# 启用闪回
startup mount;
alter database archivelog;
alter database flashback on;
alter database open;

# 开启行移动后,才能执行闪回
alter table xx enable movement;
# 闪回表到5分钟前
flashback table xx as of timestamp sysdate-5/1440;
flashback table t_canhe_family to timestamp (systimestamp-interval '5' minute);

# 查询5分钟前
select * from table as of timestamp sysdate-5/1440;

# 还原表
flashback table xx to before drop；

alter table my_objects enable row movement;
alter table my_objects shrink space;

select  b.SEQUENCE#, 
    b.FIRST_TIME,a.SEQUENCE#,
    a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) 
from v$log_history a, 
     v$log_history b 
where a.SEQUENCE#=b.SEQUENCE#+1 
    and b.THREAD#=1 
order by a.SEQUENCE# desc;

 SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,

　　l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time

　　FROM v$locked_object l, all_objects o, v$session s

　　WHERE l.object_id = o.object_id

　　AND l.session_id = s.sid

　　ORDER BY sid, s.serial# ;

sqlplus sys/oracle as sysdba /nolog
@oracle_home/rdbms/admin/awrrpt.sql
# 按照提示输入即可
# 其中文件名，可以填好路径。方便后面使用