ORACLE 维护笔记

1. 查询磁盘读写IO比较大的SQL

select a.INST_ID,
       a.SQL_TEXT,
       a.EXECUTIONS,
       a.CPU_TIME,
       a.ELAPSED_TIME,
       trunc(a.ELAPSED_TIME / decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS)),
       a.DISK_READS,
       trunc(a.DISK_READS/decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS)),
       a.BUFFER_GETS,
       a.*
  from gv$sql a
 order by a.DISK_READS desc

2. 查询RAC各个NODES 磁盘读写

select to_char(sysdate,'hh24:mi:ss'),
       a.INST_ID,
       sum(a.DISK_READS)
  from gv$sql a
  group by a.INST_ID
  order by a.INST_ID;
 
 
3.查询跟踪读写IO比较大的

select to_char(sysdate,'hh24:mi:ss'),
       a.INST_ID,
       a.SQL_TEXT,
       a.SQL_ID,
       a.EXECUTIONS,
       a.ELAPSED_TIME,
       trunc(a.ELAPSED_TIME / a.EXECUTIONS) avg_e_time,
       a.CPU_TIME,
       a.DISK_READS,
       trunc(a.DISK_READS / a.EXECUTIONS) avg_d_g,
       a.BUFFER_GETS,
       trunc(a.BUFFER_GETS / a.EXECUTIONS) avg_b_g,
       a.ROWS_PROCESSED,
       a.MODULE,
       b.object_name,
       a.PROGRAM_LINE#,
       a.*
  from gv$sql a,dba_objects b
 where a.EXECUTIONS > 1
 and a.DISK_READS  > 100
 and a.PROGRAM_ID=b.object_id (+)
-- and upper(a.SQL_TEXT) like '%T_RESV_BASE %'
-- and a.SQL_TEXT not like '%CNFNUM=%'
 and a.SQL_ID not in ( '07vv7nwa3hg94','1gu8t96d0bdmu','1mjd9xp80vuqa','2q93zsrvbdw48','dymnjhn43j0vc','03q1bnfzb3kyc','9d50qtnk567p9','04xtrk7uyhknh','060mqswvbm05d','9nu4v7ckay1pb','8umkptbv43kfa')
 order by a.DISK_READS desc; 
 
 
 
4.建立统计分析
analyze table TABLENAME compute statistics;
analyze table TABLENAME compute statistics for all indexes;

select 'analyze table '||t.table_name|| ' compute statistics;' as asql,t.last_analyzed,t.num_rows,t.* from user_tables t  order by t.table_name

5. EM console 重建
emca -config dbcontrol db -repos recreate

6.参考
select * from dba_segments a where a.segment_name in (select b.segment_name from dba_lobs b where b.table_name='T_)  or a.segment_name = 'T_;
select * from dba_segments a where a.segment_name in (select b.index_name from user_indexes b where b.table_name='T_)  or a.segment_name = 'T_;
select * from user_tables a  where a.table_name='T_;
select * from user_indexes a where a.table_name='T_;
select * from user_histograms a where a.table_name='T_;
select * from v$sql_bind_capture a where a.SQL_ID='5a3xhwgfqn5sh';
select * from v$sql_plan_statistics_all a where a.SQL_ID='5a3xhwgfqn5sh';

7.导数据
-----------------------------------------------------------------------------------------------------------------------------

emctl start dbconsole
emctl status agent

nohup impdp system/thayer0449 DIRECTORY=dmp_dir DUMPFILE=full.dmp FULL=y &

nohup expdp system/******** DIRECTORY=TTS_TMP DUMPFILE=full.dmp FULL=Y parallel=4 logfile=expfull.log

nohup expdp system/manager DIRECTORY=ORA_DMP DUMPFILE=fog3.8_full.dmp FULL=Y parallel=2 logfile=expfull.log

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
------------------------------------------------------------------------------------------------------------------------------
create tablespace datafile
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;

Create tablespace a datafile '/u01/app/oracle/oradata/a.dbf'

 
8.删除表分区
ALTER TABLE t_avail_rate_validhotel DROP PARTITION AIRVH_DATE_200912 update global indexes;

 

9.DB Link 建立
create public database link DATAGUARD
  connect to xxx identified by ******
  using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.212)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = thayerdg)  (INSTANCE_NAME = thayerdg)   )  )';

 

10.查看session

select * from v$session;

select * from gv$session;