oracle日常操作

一：日志操作 ：
1：清理redo
alter database clear unarchived logfile group 16;
alter database clear logfile group 4;

2：添加删除日志
alter database add standby logfile group 4 ('/oradata/t2posb/redo04_01.log','/oradata/t2posb/redo04_02.log') size 1024m;
alter database drop logfile group 4;

3：查看表空间使用情况脚本
select b.tablespace_name,
       nvl((100 - round(a.free_mb / b.total_mb * 100, 0)), 100) || '%' pct_used,
       nvl((b.total_mb - a.free_mb), b.total_mb) use_mb,
       b.total_mb
  from (select tablespace_name, sum(bytes) / 1024 / 1024 free_mb
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 total_mb
          from dba_data_files
         group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
 order by 1;

4：kill特定session脚本
select 'alter system kill session '||''''|| a.SID||','||a.SERIAL#||'''',A.STATUS from v$session a where a.SQL_ID='drkcf2d0z26a4' and a.STATUS='ACTIVE';

5：创建自增长分区模板
CREATE TABLE OMSDWDATA.T_CLBS_REPAY_MERGE
(
ID               VARCHAR2(32 BYTE)    NOT NULL,
CREATE_DATE      TIMESTAMP(6),
UPDATE_DATE      TIMESTAMP(6),
)
PARTITION BY RANGE(CREATE_DATE)
interval (numtoyminterval(1,'month'))
store in (OMSDWDATA)
(
PARTITION P201601 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00'),
PARTITION P201602 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00'),
)
;

6: 分区表信息查询
select seg.owner,seg.segment_name,seg.partition_name,part.high_value,bytes/1024/1024 from dba_segments seg,dba_tab_partitions part
where seg.segment_name = part.table_name and seg.segment_name='T_UTPP_UEMS_SEND' and seg.partition_name=part.partition_name
order by PARTITION_NAME;

7：查看执行计划
explain plan for select * from testdb.myuser
select * from table(dbms_xplan.display);
select /* PAPAY */* from table(dbms_xplan.display_cursor('bfu2xbtgzshu2'));
select * from table(dbms_xplan.display(null,null,'outline'));



sftp -oPort=2222 oracle@192.168.36.35


sftp -oPort=2222 oracle@192.168.36.35
get /oraarch/t2pay/arch/1_42964_905081609.dbf /oraarch/t2pay/arch/
alter database register physical logfile '/oraarch/t2pay/arch/1_42964_905081609.dbf';

sudo semanage port -a -t ssh_port_t -p tcp 2222

8：归档日志处理
crosscheck archivelog all;
delete expired archivelog all;
delete noprompt expired archivelog all;
delete noprompt force archivelog all;

delete noprompt archivelog SCN between $low_scn and $high_scn;
delete noprompt archivelog until SCN $scn;

delete noprompt archivelog SEQUENCE $sequence thread 1;
delete noprompt archivelog SEQUENCE between $low_sequence and $high_sequence thread 1;
delete archivelog until SEQUENCE 115249 thread 1;

delete noprompt archivelog TIME between "to_date('20161126 00:00:00','yyyymmdd hh24:mi:ss')" and "to_date('20161127 00:00:00','yyyymmdd hh24:mi:ss')";
delete noprompt archivelog until TIME "to_date('20161126 00:00:00','yyyymmdd hh24:mi:ss')";

delete noprompt archivelog all completed before 'sysdate-1';
delete noprompt archivelog all completed before 'sysdate-1/24';
delete noprompt archivelog all completed before "to_date('20161126 00:00:00','yyyymmdd hh24:mi:ss')";
mysql -u root -p
mysql --login-path=goods

delete from db_wlt_goods.wlt_mgm_activity_statistics;
load data infile '/tmp/txn_mgm_rebate_summary_ft0.csv' replace into table db_wlt_goods.wlt_mgm_activity_statistics  FIELDS  TERMINATED BY  ',' (mgm_id,member_id,barley_id,c_id,rebate_num,rebate_type,rebate_total,is_valid,raw_add_time,raw_add_user_id,raw_update_time,raw_update_user_id);  

tee =/tmp/daihao/mysql-fis-20170901-DML-1.log



------待验证
当某个table的column超过32个字段的时候，使用add trandata就会报错：
WARNING OGG-00706 Failed to add supplemental log group on table user.table due to ORA-02257: maximum number of columns exceeded ......
这个时候，就要使用
alter table user.table add  supplemental log .....这种方式才可。
这个时候，要分两种情况：
第一情况是，列超过32个，但是有主键或唯一建
第二情况是，列超过32个，没有主键或唯一建

对于第一张情况，如下解决：

alter table user.table add  supplemental log group ggs_table_1 (PK or UK) always;

对于第二种情况，要复杂一些：

alter table user.table add  supplemental log group ggs_table_1 (all column <32) always;

alter table user.table add  supplemental log group ggs_table_2 (all colum >32) always;


--------查看表大小情况
set linesize 200
set pagesize 100
select t.owner, t.table_name, t.mb table_size, i.mb index_size,t.mb+i.mb total_mb,decode(NVL(C.TABLE_NAME,0),'0','N','P') PAR
  from (select t.owner, t.table_name, s.mb
          from (select owner, table_name
                  from dba_tables
                 where owner in
                       ('OMSDWDATA')) T
          left join (select owner,segment_name,round(sum(bytes) / 1024 / 1024) MB
                      from dba_segments
                     where owner in ('OMSDWDATA')
                       and segment_type like 'TABLE%'
                     group by owner, segment_name) S
            on t.owner = s.owner  and t.table_name =s.segment_name) T
  left join (select i.owner, i.table_name, sum(s.mb) mb
               from (select owner, TABLE_NAME, INDEX_NAME
                       from dba_indexes
                      where owner in ('OMSDWDATA')) I
               left join (select owner,segment_name,
                                round(sum(bytes) / 1024 / 1024) MB
                           from dba_segments
                          where owner in
                                ('OMSDWDATA')
                            and segment_type like 'INDEX%'
                          group by owner, segment_name) s
                on i.owner =s.owner and i.index_name = s.segment_name
              group by i.owner, i.table_name) I
  on t.owner = i.owner and t.table_name =i.table_name
  left join dba_part_tables c
  on t.owner = c.owner and t.table_name =c.table_name
 order by total_mb desc nulls last;
 

 
oracle查看版本信息
/opt/oracle/11.2.0/grid/OPatch/opatch lsinventory


select gmt_create,
       sql_id,
       executions,
       pre_executions,
       cur_executions,
       avg_executions,
       round(cur_executions / (avg_executions+0.00001), 1) times_exec
  from (select gmt_create,
               sql_id,
               executions,
               pre_executions,
               cur_executions,
               round(avg(cur_executions)
                     over(partition by db_name,
                          sql_id order by gmt_create asc rows between 30
                          preceding  AND 0 FOLLOWING)) avg_executions
          from (select gmt_create,sql_id,
                       executions,
                       db_name,
                       lag(executions, 1, 0) over(partition by db_name, sql_id order by gmt_create) pre_executions,
                       executions - lag(executions, 1, 0) over(partition by db_name, sql_id  order by gmt_create) cur_executions
                  from stats_user.SCAN_MODIFY_SQL_STATS R
                 where lower(db_name) = 'utpp1'
                    --  and (gmt_create)>=sysdate-240/1440
                     -- and (gmt_create)<=sysdate
                   and R.gmt_create >=to_date('2017-08-30 00:01:00', 'yyyy-mm-dd hh24:mi:ss')
                   and R.gmt_create <=to_date('2017-08-30 00:12:00', 'yyyy-mm-dd hh24:mi:ss')
                --and sql_id='dn9v3r6zr5fns'
                 order by 1)
         where pre_executions <> 0)
where round(cur_executions /(avg_executions+0.00001), 1) >3
   and avg_executions>100
order by 1;

chown oracle:asmadmin /opt/oracle/utpp/products/11.2.0.4/bin/oracle
chmod 6755 /opt/oracle/utpp/products/11.2.0.4/bin/oracle



select * from information_schema.processlist where info is not null;

----查看表的sql
select sql_id,
       plan_hash_value,
       s.PARSING_SCHEMA_NAME,
       s.SQL_PLAN_BASELINE,
       max(LAST_ACTIVE_TIME) LAST_ACTIVE_TIME,
       decode(sum(EXECUTIONS), 0, 0.0000001, sum(EXECUTIONS)) exe,
       sum(BUFFER_GETS) buffer,
       sum(DISK_READS) DISK,
       decode(sum(ROWS_PROCESSED), 0, 0.0000001, sum(ROWS_PROCESSED)) rows_p
  from v$sqlarea s
 where  upper(s.SQL_FULLTEXT) like '%YTCO_T_TRANSCORE_ERROR_LOG%'
   --  and s.PARSING_SCHEMA_NAME like '%OPR'
 group by sql_id, plan_hash_value,s.PARSING_SCHEMA_NAME,SQL_PLAN_BASELINE
 order by LAST_ACTIVE_TIME desc;
 

-----添加表空间脚本
set lines 3000
set serveroutput on buffer 1000000
declare
     db_name      varchar2(30);
     asm_dg       varchar2(30);
     tbs_name     varchar2(30);
     file_size    number;
     extend_size  number;
     files        number;
     created_time varchar2(8);
file_name    varchar2(200);  9  
     alter_stmt   varchar2(32767);
   begin
     --修改变量值
     db_name      := 'cstc';
     asm_dg       := '/data/oradata2';
     tbs_name     := 'cstcdata';
     file_size    := 16;
     extend_size  := 2;
     files        := 125;
     created_time := to_char(sysdate, 'yyyymmdd');
     for i in 1 .. files loop
       file_name  := ''''|| asm_dg || '/' || db_name ||'/' || tbs_name || '_' || file_size || 'g_' || created_time || '_' || i || '.dbf''';
        alter_stmt := 'alter tablespace ' || tbs_name || ' add datafile ' || file_name || ' size 512M autoextend off;' || chr(10) || '! sleep 3' || chr(10);
         for j in 1 .. file_size / extend_size loop
           alter_stmt := alter_stmt || 'alter database datafile ' || file_name || ' resize ' || j * extend_size || 'G;' || chr(10) || '! sleep 3' || chr(10);
        end loop;
        dbms_output.put_line(alter_stmt);
      end loop;
    end;
/