oracle-使用技巧

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 字符串截取
substr(t.family_no,0,6)='433127'
# 日期转换
systimestamp 时间戳
sysdate 日期
to_date('20170101','yyyymmdd')
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
# 值判断
CASE WHEN A.STR7 IS NULL THEN '0' ELSE '0001' END  AS haha,
# 类似case when,v1就取r1,v2就取r2
DECODE(column_name, 'value1', 'result1', 'value2', 'result2', 'default_result')
# 长度判断
where length(a.id_card)>=14
# 在列表中
where D.IDENTITY IN('0001','17','20','19')
# 值为null就默认0
NVL(A.CIVIL_MONEY,0)
# 转数字
to_number(NVL(A.STR5,0)

1
2

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 批量删除.根据时间排序,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;

1
2
3
4
5

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

1
2
3
4

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

1
2
3
4
5
6
7
8
9

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; 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DECLARE
       my_task_name VARCHAR2(300);
       my_sqltext   CLOB;
     BEGIN
       my_sqltext := 'select
a.join_year,a."FAMILY_INFO_ID",a."FAMILY_NO",a."CARD_NO",a."FAMILY_NO" as BOOK_NO ,a."CENTER_NO",a."AREA_NO",a."COUNTRY_NO",
substr(a."FAMILY_NO",-4,4) as DOOR_NO,a."MASTER_NO",a."ADDRESS",a."POSTALCODE",a."PHONECODE",a."LINKMAN",a."EMAIL",a."POPULATION",
a."FARMER",a."FARMER_STAY",a."JOIN_PROP",a."DOOR_PROP",a."RPR_TYPE",a."INOUT_FLAG",a."INOUT_DATE",a."INOUT_REASON",a."SALVATION",
a."REGISTER",a."REGISTER_DATE",a."BOOK_STATE",a."CARD_STATE",a."FAMILY_STATE",a."AUDI_MAN",a."AUDI_STATE",a."AUDI_TIME",a."UPDATE_TIME",
a."UPDATE_MAN",a."CREATE_TIME",a."CREATE_MAN",a."COMMENTS",a."IS_DEL",a."FRONT_STATE_D301",a."STR1",a."STR2",a."STR3",a."STR4",a."STR5",
a."ZHEN_NO",a."CUN_NO",a."MASTER_NAME"

from T_NH_CANHE_FAMILYS a
where  a.is_del=1
and exists (select 1 from t_nh_dict_area x where a.country_no=x.countrycode and x.state=1)';
       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
               sql_text    => my_sqltext,
               user_name   => 'XIANGXI',   -- 必须大写
               scope       => 'COMPREHENSIVE',
               time_limit  => 20,
               task_name   => 'tuning_sql_test',
               description => 'Task to tune a query on a specified table');
       DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
     END;

1

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

1
2

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# 新建表空间
create tablespace xo datafile 'f:/xo.dbf' size 50m autoextend on;
# 新建临时表空间
create temporary tablespace tempfile 'f:/xo.dbf' size 50m autoextend on;
# 表空间添加文件
alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M autoextend on next 50M maxsize 1000M; 
# 数据库文件大小重置
alter database datafile ‘dir’ resize 1000m;

# 新建用户
create user test identified by test default tablespace xo temporary tablespace test_temp;
# 修改用户密码
alter user test identified by 123456;
# 删除用户
drop user test cascade;

# 授权角色
grant dba,connect,resource to test;
# 授权表操作
grant select on v$session to test;

1

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

1

NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

1
2
3
4
5
6
7

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;

1
2

alter trigger xx_trigger disable;
alter trigger xx_trigger enable;

1
2

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 启用闪回
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；

1
2

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

1
2
3
4
5
6
7
8

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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

select s.SID,  
       s.SERIAL#,  
       'kill -9 ' || p.SPID,  
       s.MACHINE,  
       s.OSUSER,  
       s.PROGRAM,  
       s.USERNAME,  
       s.last_call_et,  
       a.SQL_ID,  
       s.LOGON_TIME,  
       a.SQL_TEXT,  
       a.SQL_FULLTEXT,  
       w.EVENT,  
       a.DISK_READS,  
       a.BUFFER_GETS  
  from v$process p, v$session s, v$sqlarea a, v$session_wait w  
 where p.ADDR = s.PADDR  
   and s.SQL_ID = a.sql_id  
   and s.sid = w.SID  
   and s.STATUS = 'ACTIVE'  
 order by s.last_call_et desc;  

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

 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# ;

1
2
3
4

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