悦书阁
liufei379
2022-06-07
目录

Oracle笔记

# 1.表空间/用户

创建表空间
	CREATE SMALLFILE TABLESPACE "space_name" DATAFILE '/u01/app/oracle/oradata/orcl/SPACE_NAME.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
删除表空间
	DROP TABLESPACE space_name INCLUDING CONTENTS AND DATAFILES;
创建用户:
	create user username identified by password default tablespace space_name temporary tablespace TEMP profile DEFAULT;
授权:
    grant select on SYS.DBA_CONS_COLUMNS to username ;
    grant execute on SYS.DBMS_RLS to username ;
    grant connect to username ;
    grant dba to username ;
    grant resource to username ;
    grant unlimited tablespace to username ;
删除用户
	drop user username cascade;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 2.导入导出

# 2.1 使用exp/imp

导出格式:
        exp 账号/密码@数据库链接 file=导出地址 owner=导出用户 buffer缓存池提升性能
例子:
        exp user/password@192.168.2.100:1521/xydb file=D:/xxx.dmp owner=xxx buffer=819200
导入格式：
        imp 账号/密码 file文件地址 fromuser从哪个用户导入 touser导入到哪个用户
buffer缓存池提升性能
例子:
       imp user/passsword file=D:xxx.dmp fromuser=xxx touser=xxx buffer=819200
1
2
3
4
5
6
7
8
9

# 2.2使用expdp/impdp

先创建导出地址:
	create directory dump_url as '/home/oracle';
导出格式:
	expdp 用户名/密码@数据库链接 schemas导出的用户 dumpfile导出的文件名 directory导出地址
例子:
	expdp user/password@192.168.2.100:1521/xydb schemas=xxx dumpfile=xxx.dmp directory=dump_dir;
导入格式:
	imp 用户名/密码 directory导出地址 dumpfile 导入的文件名 remap_schema从a用户导入到b用户
例子:
	mpdp user/passsword directory=dump dumpfile=xxx.dmp remap_schema=a:b
1
2
3
4
5
6
7
8
9
10

# 3.死锁处理

-- 查询锁表信息
SELECT 'alter system kill session'''||l.session_id||','||s.serial#||''';', 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# ;
-- 杀死进程
alter system kill session 'sid,serial#'

-- 查询引起死锁的语句
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A, V$SQLTEXT C WHERE A.SID IN (SELECT DISTINCT T2.SID FROM V$LOCKED_OBJECT T1, V$SESSION T2  WHERE T1.SESSION_ID = T2.SID)  AND A.SQL_ADDRESS = C.ADDRESS(+) ORDER BY C.PIECE;
1
2
3
4
5
6
7
8

# 4.创建同义词和授权

-- 格式说明 create synonym 目标用户.目标表/视图等信息 for 来源表.来源表/视图等信息
create synonym toUser.table for forUser.table;
-- 格式说明 GRANT SELECT ON 同义词 to 目标用户
GRANT SELECT ON table to toUser;
1
2
3
4

# 5.联表更新

update (select a.字段1,b.字段2 from table a,table b where a.条件字段 = b.条件字段) c set 字段1 = 字段2;
1

# 6.根据表名反查job和存储过程

select * from user_dependencies where referenced_name=upper('表名')
1

# 7. 定时器触发存储过程调用

# 7.1存储过程

create or replace procedure P_XXX(
                                                      pi_date in date,
                                                      pi_execute_id in nvarchar2,
                                                      v_execute_time in date,
                                                      p_type in number
                                                     )
is
  v_begindate date;
  v_enddate   date;
  v_id nvarchar2(50);
  v_failed_reason nvarchar2(2000);
begin

  v_begindate := trunc(pi_date);
  v_enddate   := v_begindate+1-1/86400;

  v_id := sys_guid();
  -- 执行日志
  insert into 日志
     (id, execute_id, job_name, job_procedure, begin_time, end_time, execute_time, finish_time, failed_reason, create_time)
   values
     (v_id, nvl(pi_execute_id,v_id), 'JOB命名', '存储过程名称', v_begindate, v_enddate, v_execute_time, null, null, sysdate);
  -- 提交
  commit;
  begin
    -- 先删除原日汇总数据
    delete from 日汇总 WHERE summary_time = trunc(pi_date) and summary_type = p_type;
    -- 插入数据
    insert into 日汇总
            (
             id,
             sums
            )
      select /*+no_index(t)*/
             sys_guid(),
             SUMS
        from XXX t
             WHERE t.create_time BETWEEN v_begindate AND v_enddate ;

    -- 提交
    commit;
    -- 更新日志结果
    update 日志
      set finish_time=sysdate
    where id=v_id;
    --提交
    commit;
  exception
    when others then
      v_failed_reason := substr(sqlerrm,1,1000);
      begin
       -- 更新异常信息
        update 日志
          set finish_time=sysdate,
              failed_reason=v_failed_reason
        where id=v_id;
        --提交
        commit;
      exception
        when others then
          null;
      end;
  end;
end P_xxx;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

# 7.2触发规则

CREATE OR REPLACE PROCEDURE P_XXX_JOB
is
  v_id nvarchar2(50);
  v_count number;
  v_date date;
  v_execute_time date;
begin
  v_id := sys_guid()||'-1';
  v_execute_time := sysdate;
  --30分钟跑当天数据
  P_xxx(trunc(sysdate),v_id,v_execute_time,1);
  --每2个小时跑一次前2到7天数据
  for i in 1..6 loop
    v_date := trunc(sysdate)-i;
    select count(1) into v_count from 日志
     where BEGIN_TIME=v_date and END_TIME=v_date+1-1/86400 and EXECUTE_TIME>=sysdate-2/24
       and JOB_PROCEDURE='存储过程名称' and FINISH_TIME is not null;
    if v_count=0 then
      P_xxx(v_date,v_id,v_execute_time,1);
    end if;
  end loop;
  --每6小时跑一次前8到15天数据
  for i in 7..14 loop
    v_date := trunc(sysdate)-i;
    select count(1) into v_count from 日志
     where BEGIN_TIME=v_date and END_TIME=v_date+1-1/86400 and EXECUTE_TIME>=sysdate-6/24
       and JOB_PROCEDURE='存储过程名称' and FINISH_TIME is not null;
    if v_count=0 then
      P_xxx(v_date,v_id,v_execute_time,1);
    end if;
  end loop;
--每12小时跑一次前16到31天数据
 for i in 15..30 loop
    v_date := trunc(sysdate)-i;
    select count(1) into v_count from 日志
     where BEGIN_TIME=v_date and END_TIME=v_date+1-1/86400 and EXECUTE_TIME>=sysdate-12/24
       and JOB_PROCEDURE='存储过程名称' and FINISH_TIME is not null;
    if v_count=0 then
      P_OMS_DISPATCH_SUMMARY(v_date,v_id,v_execute_time,1);
    end if;
  end loop;
--每24小时跑一次前32到62天数据
 for i in 31..61 loop
    v_date := trunc(sysdate)-i;
    select count(1) into v_count from 日志
     where BEGIN_TIME=v_date and END_TIME=v_date+1-1/86400 and EXECUTE_TIME>=sysdate-24/24
       and JOB_PROCEDURE='存储过程名称' and FINISH_TIME is not null;
    if v_count=0 then
      P_xxx(v_date,v_id,v_execute_time,1);
    end if;
  end loop;
end P_xxx_JOB;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

# 7.3定时器

declare
  job number;
begin
  sys.dbms_job.submit(job => job,
                      what => 'p_xxx_job;',
                      next_date => to_date('00:00:00 17-12-2020', 'ss:mi:HH24 dd-mm-yyyy'),
                      interval => 'sysdate+30/1440');
  commit;
end;
/

oracle 19C 创建方式
DECLARE
   job number;
begin
DBMS_SCHEDULER.CREATE_JOB(
  job_name => 'DBMS_JOB$_124',
  job_type => 'PLSQL_BLOCK',
  job_action => 'XXX(sysdate);',
  start_date => TO_TIMESTAMP_TZ('2022-06-21 11:00:00.0 -5:00', 'yyyy-mm-dd hh24:mi:ss.ff tzr'),
  repeat_interval => 'trunc(sysdate+1)+2/24',
  enabled => TRUE
);
commit;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 8.强制索引

select /*+leading(表别名) index(表别名 索引名) index(表别名 索引名) */ from  table
/*+LEADING(TABLE)*/  将指定的表作为连接次序中的首表.
/*+USE_NL(TABLE1, TABLE2, TABLE3...)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
/*+parallel(4)*/ 开启多线程
1
2
3
4

# 9.存储过程返回游标调用

# 9.1plsql测试

DECLARE v_result sys_refcursor;
        code NVARCHAR2(20);
		name NVARCHAR2(100);
BEGIN 
	P_xxx('1','2',v_result);
    LOOP
    FETCH v_result INTO code, name;
    EXIT WHEN v_result%notfound;
    dbms_output.put_line(code || '-' || name);
    END LOOP;
END;
1
2
3
4
5
6
7
8
9
10
11

# 9.2 XML使用

<select id="xxx" parameterType="map" statementType="CALLABLE">
  <![CDATA[
			{CALL P_XXX(
                 #{code,jdbcType=VARCHAR,mode=IN},
                 #{name,jdbcType=VARCHAR,mode=IN},
                 #{p_rf,jdbcType=CURSOR,mode=OUT,resultMap=Map,javaType=java.sql.ResultSet}
   		 	)}
		]]>
</select>

<resultMap type="BaseData" id="BaseDataMap">
  <result property="code" column="CODE" />
  <result property="name" column="NAME" />
</resultMap>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 10.ROW_NUMBER() OVER()函数

原理：

row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。

语法： row_number() over( partition by 分组列 order by 排序列 desc

row_number() over(partition by classid order by score desc)
-- 先根据班级ID分组，然后每个组再按分数排序
1
2

# 11.查询任务信息

当前用户定时任务
select  count (*)  from  user_jobs;
select * f rom user_job where what like upper('%xxxxxxxxx%')
有权限访问的定时任务
select  count (*)  from  all_jobs;
整个数据库所有定时任务
select  count (*)  from  dba_jobs;
Oracle 19C查询定时任务
select  *  from  dba_scheduler_jobs where job_action like ('%xxxxx%')
1
2
3
4
5
6
7
8
9

# 12.Oracle分区表创建

PARTITION BY RANGE (分区字段) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition SYS_001 values less than(to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
1
2

# 13.临时表

with temp(定义临时表名) as 
(select 1 from dual (查询语句))
select * from temp 拿到临时表结果
1
2
3

# 14.单引号，双引号转义

--- 单引号
select 'I''m a String!' from dual
select q'[I'm a String!]' from dual;
select q'[I'''''m a String!]' from dual;
--- 双引号
select q'[I"m a String!]' from dual;
select q'[I"""""m a """"String!""""]' from dual;
1
2
3
4
5
6
7

# 15.参考文档

基于oracle的sql优化 (opens new window)

Oracle索引优化 (opens new window)

Oracle触发器 (opens new window)

上次更新: 2026/3/11 21:47:04
stream使用
AES加解密

← AES加解密→

最近更新
01
实现idea开发的关键步骤
10-05
02
Redis高可用架构
09-09
03
Zookeeper高可用
08-31
更多文章>
Theme by Vdoing | Copyright © 2022-2026 Felix | 粤ICP备17101757号-1