Oracle数据库相关操作(20220709)

zhangbeizhen18

已于 2022-07-11 22:46:47 修改

阅读量2.8k

点赞数 1

分类专栏： L02-数据库文章标签：数据库开发

于 2022-07-09 19:01:12 首次发布

本文链接：https://blog.csdn.net/zhangbeizhen18/article/details/125697102

版权

L02-数据库专栏收录该内容

4 篇文章 3 订阅

订阅专栏

记录：283

场景：Oracle数据库相关操作。比如：创建用户，用户授权、查看对象、表、注释等；创建表空间和数据文件以及应用；创建序列以及应用；建表（普通表和分区表）以及应用，全局索引和局部索引应用、创建视图以及应用、创建同义词以及应用、创建Database Link以及应用、创建存储过程以及应用、创建包和包体管理存储过程以及应用、创建Job以及应用、表分析应用。

版本：Oracle Database 11g

工具：PL/SQL Developer

一、案例场景

基础约定:

数据库1：HUB_SPRING，具备DBA权限。

数据库2：HUB_SUMMER，具备非DBA权限，即普通权限。

表空间：DATA_HUB、DATA_HUB_SUMMER。

1.数据库用户

1.1创建用户

场景：在启动新项目时，一般会把相关的数据库对象（表、索引、存储过程等）建立在一个独立用户下，便于权限管理、项目之间数据隔离，因此不影响在运项目。

1.1.1创建用户(DBA)

使用SQL命令创建Oracle用户。

create user HUB_SPRING 
identified by ***自定义密码*** 
default tablespace DATA_HUB
temporary tablespace TEMP
profile DEFAULT 
quota unlimited on DATA_HUB ;
grant connect to HUB_SPRING;
grant resource to HUB_SPRING;
grant dba to HUB_SPRING;
grant unlimited tablespace to HUB_SPRING;

1.1.2创建用户(普通用户)

使用SQL命令创建Oracle用户。

create user HUB_SUMMER
identified by ***自定义密码*** 
  default tablespace DATA_HUB_SUMMER
  temporary tablespace TEMP;
grant connect to HUB_SUMMER;
grant resource to HUB_SUMMER;
grant create any table to HUB_SUMMER;
grant create any view to HUB_SUMMER;
grant create session to HUB_SUMMER;
grant drop any table to HUB_SUMMER;
grant drop any view to HUB_SUMMER;
grant execute any procedure to HUB_SUMMER;
grant insert any table to HUB_SUMMER;
grant select any table to HUB_SUMMER;
grant update any table to HUB_SUMMER;
grant delete any table to HUB_SUMMER;
grant unlimited tablespace to HUB_SUMMER;

1.2用户授权

1.2.1 用户授权和撤销用户权限

场景：具备DBA权限的用户HUB_SPRING，给普通用户HUB_SUMMER分配指定权限。具备DBA权限的用户HUB_SPRING，给撤销分配给普通用户HUB_SUMMER权限。

--授权
grant connect to HUB_SUMMER;
grant resource to HUB_SUMMER;
grant create any table to HUB_SUMMER;
grant create any view to HUB_SUMMER;
grant create session to HUB_SUMMER;
grant drop any table to HUB_SUMMER;
grant drop any view to HUB_SUMMER;
grant execute any procedure to HUB_SUMMER;
grant insert any table to HUB_SUMMER;
grant select any table to HUB_SUMMER;
grant update any table to HUB_SUMMER;
grant delete any table to HUB_SUMMER;
grant unlimited tablespace to HUB_SUMMER;
--撤销已经授予的权限
revoke connect from hub_summer;
revoke resource from hub_summer;
revoke create any table from hub_summer;
revoke create any view from hub_summer;
revoke create session from hub_summer;
revoke drop any table from hub_summer;
revoke drop any view from hub_summer;
revoke execute any procedure from hub_summer;
revoke insert any table from hub_summer;
revoke select any table from hub_summer;
revoke update any table from hub_summer;
revoke delete any table from hub_summer;
revoke unlimited tablespace from hub_summer;

1.2.2 授权访问表权限

场景：用户HUB_SPRING的表T_USER_STAT，分配SELECT 权限给用户HUB_SUMMER。

--授权SQL
GRANT SELECT  ON T_USER_STAT TO HUB_SUMMER;
--查询赋权结果
SELECT *
  FROM all_tab_privs aa
 WHERE aa.grantor = 'HUB_SPRING'
   AND aa.table_name = 'T_USER_STAT';
--在HUB_SUMMER中使用
SELECT * FROM HUB_SUMMER.T_USER_STAT;

1.2.3 撤销访问表权限

场景：用户HUB_SPRING的表T_USER_STAT，撤销已经分配给用户HUB_SUMMER的SELECT 权限。

--撤销授权SQL
REVOKE  SELECT ON T_USER_STAT FROM HUB_SUMMER;
--查询赋权结果
SELECT *
  FROM all_tab_privs aa
 WHERE aa.grantor = 'HUB_SPRING'
   AND aa.table_name = 'T_USER_STAT';
--在HUB_SUMMER中使用
SELECT * FROM HUB_SUMMER.T_USER_STAT;

1.3 删除用户

场景：在项目下线，用户无需保留场景。

--删除用户
drop user HUB_SPRING cascade

1.4 Oracle的IP、VIP、SCAN IP

场景：在规划数据库时需要了解；在一些安全级别高的生产环境，系统之间相互访问有多重防火墙情况，因此，涉及系统之间开通防火墙和端口访问的权限事项，此刻就需搞清楚开防火墙清单中该提供哪些IP和端口。

在集群部署时，例如本例：

在使用时，组装连接信息如下即可。

使用PL/SQL工具：168.59.80.60:1521/hubdb

使用Java代码：jdbc:oracle:thin:@168.59.80.60:1521/hubdb

主机IP：Oracle数据库实际安装的物理机地址。

VIP：每个节点都有一个虚拟IP，即VIP。VIP是浮动的，而IP是固定的。例如，本例3个节点都正常运行时，每个节点都会有一个虚拟IP。当一个节点宕机时，这个节点的VIP会被转移到其它未宕机且可用的节点上。

SCAN IP：理解成负载均衡地址，在配置文件中，使用SCAN IP，就可以顺畅使用数据库。

以上，具体原来，需移步Oracle官网。

1.5 常规使用场景

场景：为高效使用Oracle，建议了解这些基础套路和招数。

1.5.1 查看版本

场景：在异构系统交互时，需提前调研清楚，双方系统版本是否满足需求，特别是有些在运系统不变的情况下，只能建设中的系统取适配这些在运系统。

查看数据库版本SQL:

--查看数据库版本
select * from v$version;
select banner from sys.v_$version;
select * from product_component_version;

1.5.2 查看实例

场景：在配置数据源时，需要调研清楚实例名称。

查看数据库实例SQL:

--查看数据库版本
select name from v$database;
select instance_name from v$instance;
show parameter instance;

1.5.3 查看数据库用户信息

场景：使用DBA权限账号，常看数据库用户信息，便于了解数据库整体情况。

查看数据库用户信息SQL，包括用户名称、用户创建时间、用户状态、用户默认表空间等信息。

--查看用户信息(全量信息)
select * from dba_users;
--查看用户信息(部分信息)
select * from all_users;
--查看用户信息(当前用户信息)
select * from user_users;

1.5.4 查看数据库角色和权限

场景：为了方便给指定用户分配权限。

查看查看数据库角色和权限。

--查看数据库角色
select * from dba_roles;
--查看数据库角色的特权
select * from dba_sys_privs;
--查看当前用户特权
select * from user_sys_privs;

1.5.5 查看用户对象(表)权限

场景：查看用户对象(表)权限。

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

1.5.6 查看用户拥有的角色

场景：查看用户拥有的角色。

select * from dba_role_privs;
select * from user_role_privs;

1.5.7 查看数据库对象信息

场景：查看数据库对象信息，包括表、视图、索引、分区、等。

--查看数据库中对象信息
select * from dba_objects;
--查看指定用户表信息
select *
  from dba_objects aa
 where aa.owner = 'HUB_SPRING'
   and aa.object_type = 'TABLE'
 order by created desc;
--查看指定用户视图信息
 select *
  from dba_objects aa
 where aa.owner = 'HUB_SPRING'
   and aa.object_type = 'VIEW'
 order by created desc;
--查看数据库对象类型
select aa.object_type, count(1)
  from dba_objects aa
 group by aa.object_type;

1.5.8 查看表的字段和注释

场景：查看数据库表字段和注释。

--查看表字段信息(字段名称,类型等)
select * from user_tab_columns where table_name = 'T_SENSOR_DATA_GLOBAL';
--查看表字段注释
select * from user_tab_comments where table_name='T_SENSOR_DATA_GLOBAL'

1.5.9 查看数据库所有表

场景：查看数据库所有表.

--查看指定用户表信息
select *
  from dba_objects aa
 where aa.owner = 'HUB_SPRING'
   and aa.object_type = 'TABLE'
 order by created desc;
--所有用户的表
select * from all_tables;
--包括系统表
select * from dba_tables;
--当前用户的表
select * from user_tables;

1.5.10 查看数据库所有Jobs

场景：查看数据库所有Job定时任务。

--查看Job定时任务
select * from dba_scheduler_jobs;
--查看Job定时任务(当前用户)
select * from user_scheduler_jobs;

2.表空间和数据文件

在Oracle数据库，根据业务的数据规模规划表空间，方便管理。创建一个表空间会对应一个数据文件，当表空间指向的数量大于表空间已有数据文件最大值时，则需拓展表空间对应的数据文件。否则会出现表空间不足报错，而且数据也无法写入。通常，一个表空间会对应多个数据文件，随着数据规模递增，表空间对应的数据文件数量也会成正比增加。

2.1 查看表空间和数据文件

场景：在创建表空间前，先查看Oracle数据库已经存在的表空间的数据文件路径，拷贝出这个路径。在创建表空间和数据文件时，就根据这个路径去规划表空间的数据文件。这样也可以确保路径正确和表空间名称不重复。

-- 查看表空间数据文件
SELECT * FROM DBA_DATA_FILES;

例如：USERS表空间数据文件路径如下。

单机部署Oracle：

/opt/oracle/datafile/users01.dbf

集群部署Oracle：

+DATA/oracle/datafile/users01.dbf

2.2 表空间和数据文件(单机版)

场景：在Oracle数据库单机部署情况下，创建表空间和扩展表空间的数据文件使用绝对路径。

2.2.1 创建表空间和数据文件

场景：

表空间名称：DATA_HUB

数据文件绝对路径：/opt/oracle/datafile/DATA_HUB01.dbf

执行SQL：

-- 创建表空间数据文件
CREATE TABLESPACE DATA_HUB DATAFILE '/opt/oracle/datafile/DATA_HUB01.dbf' SIZE 4096M AUTOEXTEND ON next 32M;

2.2.2 扩展表空间和数据文件

场景：

表空间名称：DATA_HUB

数据文件绝对路径：/opt/oracle/datafile/DATA_HUB02.dbf

执行SQL：

-- 拓展表空间数据文件
ALTER TABLESPACE DATA_HUB ADD DATAFILE '/opt/oracle/datafile/DATA_HUB02.dbf' SIZE 4096M AUTOEXTEND ON next 32M;

2.3 表空间和数据文件(集群版)

场景：在Oracle数据库集群部署情况下，创建表空间和扩展表空间的数据文件使用相对路径。

2.3.1 创建表空间和数据文件

表空间名称：DATA_HUB

数据文件路径：+DATA/oracle/datafile/DATA_HUB01.dbf

执行SQL：

-- 创建表空间数据文件
CREATE TABLESPACE DATA_HUB DATAFILE '+DATA/oracle/datafile/DATA_HUB01.dbf' SIZE 4096M AUTOEXTEND ON next 32M;

2.3.2 扩展表空间和数据文件

表空间名称：DATA_HUB

数据文件路径：+DATA/oracle/datafile/DATA_HUB02.dbf

执行SQL：

-- 拓展表空间数据文件
ALTER TABLESPACE DATA_HUB ADD DATAFILE '+DATA/oracle/datafile/DATA_HUB02.dbf' SIZE 4096M AUTOEXTEND ON next 32M;

3.序列

场景：使用Oracle的序列，生产一个不重复的唯一值。包括创建序列、使用序列、修改序列、删除序列。

-- 1.创建序列
create sequence SEQ_HUB_ID
minvalue 1
maxvalue 9999999999
start with 10000000
increment by 1
cache 1000;
-- 2.1使用序列-获取下一个序列值
select SEQ_HUB_ID.NEXTVAL from dual;
-- 2.2使用序列-获取当前序列值
select SEQ_HUB_ID.CURRVAL from dual;
-- 3.修改序列值
ALTER SEQUENCE SEQ_HUB_ID MAXVALUE 99999999998 MINVALUE 199;
-- 4.删除序列
DROP SEQUENCE SEQ_HUB_ID;

4.表结构

Oracle数据库表结构，主要包括建表关键字、表名、字段名称、字段类型、字段注释、表注释、主键约束、索引、分区等。

常见字段类型：NUMBER(12,6)，整数值；NUMBER(12,6)，带小时点的值；DATE，时间值；VARCHAR2(16),字符串值。

4.1创建普通表

4.1.1创建普通表(默认表空间)

场景：创建普通表，使用默认表空间。

create table T_SENSOR_DATA ( 
  ID  NUMBER(16) not null ,
  REGION  VARCHAR2(8),
  VALUE1  NUMBER(16,2),
  VALUE2  NUMBER(16,2)
 );
comment on table  T_SENSOR_DATA is '传感器数据';
comment on column T_SENSOR_DATA.ID is '实体唯一标识';
comment on column T_SENSOR_DATA.REGION is '区域';
comment on column T_SENSOR_DATA.VALUE1 is '取值1';
comment on column T_SENSOR_DATA.VALUE2 is '取值2';
alter table  T_SENSOR_DATA add constraint PK_T_SENSOR_DATA_ID primary key (ID);
create index  IDX_T_SENSOR_DATA_REGION on T_SENSOR_DATA (REGION);

4.1.2创建普通表(指定表空间)

场景：创建普通表-指定表空间。

create table T_SENSOR_DATA ( 
  ID  NUMBER(16) not null ,
  REGION  VARCHAR2(8),
  VALUE1  NUMBER(16,2),
  VALUE2  NUMBER(16,2)
 ) tablespace DATA_HUB;
comment on table  T_SENSOR_DATA is '传感器数据';
comment on column T_SENSOR_DATA.ID is '实体唯一标识';
comment on column T_SENSOR_DATA.REGION is '区域';
comment on column T_SENSOR_DATA.VALUE1 is '取值1';
comment on column T_SENSOR_DATA.VALUE2 is '取值2';
alter table  T_SENSOR_DATA add constraint PK_T_SENSOR_DATA_ID primary key (ID) using index 
  tablespace DATA_HUB;
create index  IDX_T_SENSOR_DATA_REGION on T_SENSOR_DATA (REGION) using index 
  tablespace DATA_HUB ;

4.2创建分区表

Oracle分区表的索引可以分为本地索引和全局索引两种。全局索引，指向指定的表空间。局部索引，指向分区表的分区。主要本地索引关键字local，只是针对分区表，才能使用。

4.2.1创建分区表(全局索引)

场景：创建分区表，使用全局索引。

create table T_SENSOR_DATA_GLOBAL
(
  id            NUMBER(12) not null,
  gather_date   DATE not null,
  position_dir  NUMBER(2) not null,
  gather_time   DATE,
  t1        NUMBER(12,2),
  t2        NUMBER(12,2),
)
partition by range (GATHER_DATE)
(
  partition T20210701 values less than 
   (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DATA_HUB,
  partition T20210702 values less than 
   (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DATA_HUB
  );
comment on table T_SENSOR_DATA_GLOBAL is 'T_SENSOR_DATA_GLOBAL';
comment on column T_SENSOR_DATA_GLOBAL.id is '唯一标识';
comment on column T_SENSOR_DATA_GLOBAL.gather_date is '日期';
comment on column T_SENSOR_DATA_GLOBAL.position_dir is '位置';
comment on column T_SENSOR_DATA_GLOBAL.gather_time is '时间';
comment on column T_SENSOR_DATA_GLOBAL.t1 is '值1';  
comment on column T_SENSOR_DATA_GLOBAL.t2 is '值1';  
--主键
alter table T_SENSOR_DATA_GLOBAL
  add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID,GATHER_DATE, POSITION_DIR) using index tablespace DATA_HUB;
--索引
create index IDX_T_SENSOR_DATA_GLOBAL_ID on T_SENSOR_DATA_GLOBAL (ID,GATHER_DATE, T1) tablespace DATA_HUB;

4.2.2创建分区表(本地索引)

场景：创建分区表，使用本地索引。

create table T_SENSOR_DATA_LOCAL
(
  id            NUMBER(12) not null,
  gather_date   DATE not null,
  position_dir  NUMBER(2) not null,
  gather_time   DATE,
  t1        NUMBER(12,2),
  t2        NUMBER(12,2),
)
partition by range (GATHER_DATE)
(
  partition T20210701 values less than 
   (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DATA_HUB,
  partition T20210702 values less than 
   (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DATA_HUB
  );
comment on table T_SENSOR_DATA_LOCAL is 'T_SENSOR_DATA_LOCAL';
comment on column T_SENSOR_DATA_LOCAL.id is '唯一标识';
comment on column T_SENSOR_DATA_LOCAL.gather_date is '日期';
comment on column T_SENSOR_DATA_LOCAL.position_dir is '位置';
comment on column T_SENSOR_DATA_LOCAL.gather_time is '时间';
comment on column T_SENSOR_DATA_LOCAL.t1 is '值1';  
comment on column T_SENSOR_DATA_LOCAL.t2 is '值1';  
--主键
alter table T_SENSOR_DATA_LOCAL
  add constraint PK_T_SENSOR_DATA_LOCAL primary key (ID, GATHER_DATE, POSITION_DIR)
  using index 
  local ;
--索引
create index IDX_T_SENSOR_DATA_LOCAL_ID on T_SENSOR_DATA_LOCAL (ID, GATHER_DATE, T1)
  local ;

4.3删除表

场景：删除表，即删除物理表。

drop table t_sensor_data_local;

4.4添加主键(单个字段和多个字段)

场景：单个字段添加主键约束和联合主键。

-- 单字段主键
alter table T_SENSOR_DATA_GLOBAL
  add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID) using index tablespace DATA_HUB;
-- 联合主键
alter table T_SENSOR_DATA_GLOBAL
  add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID,GATHER_DATE, POSITION_DIR) using index tablespace DATA_HUB;

4.5添加索引(单个字段和多个字段)

场景：单个字段添加索引和联合索引。

-- 联合索引
create index IDX_T_SENSOR_DATA_GLOBAL_ID on 
T_SENSOR_DATA_GLOBAL (ID,GATHER_DATE, T1) tablespace DATA_HUB;
-- 单字段索引
create index IDX_T_SENSOR_DATA_GLOBAL_ID on T_SENSOR_DATA_GLOBAL (ID) tablespace DATA_HUB;

4.6全局索引局部索引

场景：查看表索引信息、分区信息。

--查看索引信息(全量)
select * from dba_indexes;
--查看索引信息(根据表名)
select * from dba_indexes where table_name = 'T_SENSOR_DATA_LOCAL';
--查看分区表索引信息(根据用户)
select * from dba_ind_partitions  where index_owner='HUB_SPRING';
--查看分区表索引信息(根据用户)
select * from dba_part_indexes  where owner='HUB_SPRING';
--查看当前用户的分区索引
select * from user_part_indexes;

4.7查看表分区

场景：分区表，查看具体分区。

--查看表的分区
select * from dba_tab_partitions;
--查看表的分区(根据表名)
select * from dba_tab_partitions where table_name='T_SENSOR_DATA_LOCAL';

5.操作表

场景：常规操作表包括 insert、select、update、delete、truncate。truncate 整个表时，所有的索引都不会失效。truncate 某个分区时，全局索引都会失效，需要添加 update global indexes。

SELECT * FROM T_SENSOR_DATA;
--插入一条数据
INSERT INTO T_SENSOR_DATA (ID,REGION,VALUE1,VALUE2) VALUES(20220708,'A',22.78,8.59);
--更新一条数据
UPDATE T_SENSOR_DATA SET  VALUE1=23.78 WHERE ID=20220708;
--查询一条数据
SELECT * FROM T_SENSOR_DATA WHERE ID=20220708;
--删除一条数据
DELETE FROM T_SENSOR_DATA WHERE ID=20220708;
--清空表
TRUNCATE TABLE T_SENSOR_DATA;
--清空分区表数据,并更新全局索引
alter table T_SENSOR_DATA_GLOBAL truncate partition T20210702 update global indexes;
--清空分区表数据,局部索引的分区表无需更新
alter table T_SENSOR_DATA_LOCAL truncate partition T20210702;

6.视图

视图，可以理解成就是一条查询SQL语句，用于显示一个或多个表或其它视图中的相关数据。

场景：只需要查询数据时，把SQL创建为视图，即可以方便使用。

--创建视图,前提t_sensor_data表要存在
create or replace force view view_t_sensor_data as
select * from t_sensor_data;
--使用视图
select * from view_t_sensor_data;
--删除视图
drop view view_t_sensor_data;

7.同义词(synonym)

Oracle同义词，是其对象（例如表、实体、存储过程、函数、包、序列）的别名。

场景：用户HUB_SPRING有一张表t_sensor_data，对用户HUB_SUMMER针对这张表建立一个同义词，那么在用户HUB_SUMMER中可以像在用户HUB_SPRING中使用表t_sensor_data。

--创建同义词(在用户hub_spring中操作)
--用户hub_spring的表,以同义词的方式,给用户hub_summer使用
create  synonym hub_summer.t_sensor_data for hub_spring.t_sensor_data;
--删除同义词(在hub_spring中操作)
drop synonym hub_summer.t_sensor_data;
--使用同义词(在用户中hub_summer使用)
select * from t_sensor_data;
--查看同义词
select *
  from dba_synonyms
 where table_owner = 'HUB_SPRING'
   AND table_name = 'T_SENSOR_DATA';
--如果一个用户没有创建同义词权限,可以高权限用户创建比如
--hub_summer用户的表t_sensor_data在hub_spring用户创建同义词
create  synonym hub_spring.t_sensor_data for hub_summer.t_sensor_data;  
--赋予可以创建同义词权限
--给hub_summer用户赋权可以创建同义词
grant create any synonym to hub_summer;
--撤销hub_summer用户创建同义词的权限
revoke create any synonym from hub_summer;

8.触发器

场景：Oracle触发器，在执行某个操作时，触发另一个操作。比如，在表t_sensor_data插入一条数据时，此表的ID会自动写入一个值。这个值就是触发器生成的。

--1.创建一个表
create table t_sensor_data
(
  data_id     NUMBER(20) not null,
  value1 NUMBER(16,2),
  value2 NUMBER(16,2)
);
--2.创建一个序列
create sequence seq_log_id
minvalue 1
maxvalue 9999999999999999
start with 20220708
increment by 1
cache 1000;
--3.创建触发器
create or replace trigger t_sensor_data_insert_data_id
  before insert on t_sensor_data
  for each row
begin
  select seq_log_id.Nextval into :new.data_id from dual;
end;
--4.测试验证,data_id会自动赋值
insert into t_sensor_data(value1,value2) VALUES(22.51,23.52);
--删除触发器
drop trigger t_sensor_data_insert_data_id;

9.Database Link

Oracle的Database Link提供了一个非常有效的方案取访问另一个数据库的信息。正因为高效遍历，也被认为是一个不安全的方式。

场景：在HUB_SPRING中使用DB Link访问HUB_SUMMER，那么在HUB_SPRING中创建DB Link即可。

9.1创建DB Link(单节点)

场景：单节点创建Database Link。

create  database link DEMO_02
  connect to HUB_SUMMER IDENTIFIED BY "***自定义的密码****"
  using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.60)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hubdb))
)';

9.2创建DB Link(多节点)

场景：多节点创建Database Link。

create database link DEMO_01
  connect to HUB_SUMMER  IDENTIFIED BY "***自定义的密码****"
  using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.61)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.62)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.63)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hubdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5))))';

9.3使用DB Link

使用DB Link，在HUB_SPRING中使用DB Link访问HUB_SUMMER的表t_sensor_data。

select * from hub_summer.t_sensor_data@demo_01;

10.存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。也可以在Job进行配置成为定时任务，即在指定时间执行相应业务。

引用：https://blog.csdn.net/zhangbeizhen18/article/details/100088094

引用：https://blog.csdn.net/zhangbeizhen18/article/details/100088152

10.1创建存储过程

场景：创建存过程，带入参和不带入参。

--存储过程(不带入参)
CREATE OR REPLACE PROCEDURE PRO_INSERT_INFO IS
BEGIN
  INSERT INTO B_LOG_INFO
    SELECT SEQ_DEMO_ID.NEXTVAL,
           SYSDATE,
           SYSDATE,
           '1',
           '执行成功',
           50,
           'DUAL'
      FROM DUAL;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END PRO_INSERT_INFO;
--存储过程(带入参)
CREATE OR REPLACE PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL) IS
BEGIN
  -- 1.声明变量
  DECLARE
    V_DATE DATE := TRUNC(SYSDATE - 1);
  BEGIN
    -- 2.记录一条日志
    IF (IN_DATE IS NOT NULL) THEN
      V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
    END IF;
	-- 3.处理业务逻辑
    INSERT INTO B_LOG_INFO
      SELECT SEQ_DEMO_ID.NEXTVAL,
             V_DATE,
             SYSDATE,
             '1',
             '执行成功',
             50,
             'DUAL'
        FROM DUAL;
    COMMIT;
	-- 4.更新日志(记录业务成功状态)
  EXCEPTION
    WHEN OTHERS THEN
	-- 5.更新日志(记录业务异常状态)
    ROLLBACK;
  END;
END PRO_INSERT_INFO_ARGS;

10.2 Packages和Packages bodies

场景：使用Packages和Packages bodies管理存储过程，可以模块化管理存储过程和增加SQL代码的可读性。

10.2.1创建Packages和Packages bodies

--1.创建包(Packages)
CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
END PKG_ZBZ_PORTAL;
--2.创建包体(Packages bodies)
CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS		
END PKG_ZBZ_PORTAL;

10.2.2 Packages

把存储过程定义放在Packages中。

CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL,
        FLAG OUT NUMBER,
        FAIL_INFO OUT VARCHAR2);
END PKG_ZBZ_PORTAL;

10.2.3 Packages bodies

把存储过程实现放在Packages bodies中。

CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE   IN VARCHAR2 := NULL,
        FLAG  OUT NUMBER,
        FAIL_INFO OUT VARCHAR2) IS
  BEGIN
    DECLARE
      V_DATE DATE := TRUNC(SYSDATE - 1);
    BEGIN
      FLAG      := 1;
      FAIL_INFO := '';
      IF (IN_DATE IS NOT NULL) THEN
        V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
      END IF;
      INSERT INTO B_LOG_INFO
        SELECT SEQ_DEMO_ID.NEXTVAL,
               V_DATE,
               SYSDATE,
               '1',
               '执行成功',
               50,
               'DUAL'
          FROM DUAL;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        FLAG      := 0;
        FAIL_INFO := '此次执行: ' || IN_DATE || ' 输出结果: FLAG = ' || FLAG ||
                     '异常信息:  ' || SQLERRM;
        ROLLBACK;
    END;
  END PRO_INSERT_INFO_ARGS;
END PKG_ZBZ_PORTAL;

11.Job

场景：在Oracle数据库中，业务需要以定时生成数据时，可以把存储过程配置成定时任务，使用Job配置定时调度任务，即实现业务自动化处理。

11.1创建Job

在PL/SQL Developer客户端中，登录当前用户。在默认在左侧对象窗口中。

11.1.1找到Users菜单

11.1.2找到Jobs菜单

在当前用户，左侧对象对话框，依次Users->HUB_SPRING->Objects->Jobs，如图：

11.1.3右键选中Jobs，点击：新建

右键选中Jobs，选中弹出菜单，点击新建。如图窗口：

11.1.4配置Job举例

例如，配置任务场景：

要求一：从2022年7月9日开始，每天10点30分执行任务。

要求二：执行存储过程为包PKG_ZBZ_PORTAL中的存储过程PRO_INSERT_INFO_ARGS

要求三：业务名称：日志信息收集任务

Program/chain中的Action的SQL如下，其它配置如图：

Action的SQL：

DECLARE
  FLAG VARCHAR2(64);
  FAIL_INFO  VARCHAR2(4096);
BEGIN
  PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS(
    TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD'),
    FLAG,
    FAIL_INFO);
END;

其它配置如图：

以上，注意：Enabled必须勾选，生效后才能启动任务。

(4)点击：应用，即生效

生效后，在菜单：作业，中会有新建的Job任务。

11.2配置Job的Action几种配置

情况一：存储过程，不使用包管理，无参数。

情况二：存储过程，不使用包管理，有参数。

情况三：存储过程，使用包管理，无参数。

情况四：存储过程，使用包管理，有参数。

情况五：直接写SQL。

--情况一：存储过程，不使用包管理，无参数
BEGIN PRO_INSERT_INFO; END;
--情况二：存储过程，不使用包管理，有参数
BEGIN PRO_INSERT_INFO_ARGS(TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD')); END;
--情况三：存储过程，使用包管理，无参数
BEGIN PKG_INSERT.PRO_INSERT_INFO; END;
--情况四：存储过程，使用包管理，有参数
DECLARE
  FLAG VARCHAR2(64);
  FAIL_INFO  VARCHAR2(4096);
BEGIN
  PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS(
    TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD'),
    FLAG,
    FAIL_INFO);
END;
--情况五：直接写SQL
BEGIN
INSERT INTO B_LOG_INFO_A 
SELECT * FROM B_LOG_INFO_B
COMMIT;
END;

12.表分析

表分析，收集表和索引的信息，CBO(SQL优化器)根据这些信息决定SQL最佳的执行路径。对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以优化。

CBO: Cost-Based Optimization 基于代价的优化器.

场景一：对用户HUB_SPRING，非分区表T_SENSOR_DATA做表分析。

场景一：对用户HUB_SPRING，分区表T_SENSOR_DATA_GLOBAL，分区T20210701做表分析。

--表分析(非分区表)
begin
dbms_stats.gather_table_stats(
    ownname          => 'HUB_SPRING',
    tabname          => 'T_SENSOR_DATA',
    estimate_percent => 60,
    degree           => 30,
    cascade          => true,
	 no_invalidate    => false);
end;
--表分析(分区表)
begin
dbms_stats.gather_table_stats(
    ownname          => 'HUB_SPRING',
    tabname          => 'T_SENSOR_DATA_GLOBAL',
    partname         => 'T20210701',
    estimate_percent => 60,
    degree           => 30,
    granularity      => 'all',
    cascade          => true,
	no_invalidate    => false
	);
end;

以上，感谢

2022年7月9日

zhangbeizhen18

关注

1
点赞
踩
23

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库相关操作(20220709)

记录：283场景：Oracle数据库相关操作。比如：创建用户，用户授权、查看对象、表、注释等；创建表空间和数据文件以及应用；创建序列以及应用；建表（普通表和分区表）以及应用，全局索引和局部索引应用、创建视图以及应用、创建同义词以及应用、创建Database Link以及应用、创建存储过程以及应用、创建包和包体管理存储过程以及应用、创建Job以及应用、表分析应用。......
复制链接

扫一扫