• Oracle数据库相关操作(20220709)

    记录：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用户。

    1. create user HUB_SPRING 
    2. identified by ***自定义密码*** 
    3. default tablespace DATA_HUB
    4. temporary tablespace TEMP
    5. profile DEFAULT 
    6. quota unlimited on DATA_HUB ;
    7. grant connect to HUB_SPRING;
    8. grant resource to HUB_SPRING;
    9. grant dba to HUB_SPRING;
    10. grant unlimited tablespace to HUB_SPRING;

    1.1.2创建用户(普通用户)

    使用SQL命令创建Oracle用户。

    1. create user HUB_SUMMER
    2. identified by ***自定义密码*** 
    3.   default tablespace DATA_HUB_SUMMER
    4.   temporary tablespace TEMP;
    5. grant connect to HUB_SUMMER;
    6. grant resource to HUB_SUMMER;
    7. grant create any table to HUB_SUMMER;
    8. grant create any view to HUB_SUMMER;
    9. grant create session to HUB_SUMMER;
    10. grant drop any table to HUB_SUMMER;
    11. grant drop any view to HUB_SUMMER;
    12. grant execute any procedure to HUB_SUMMER;
    13. grant insert any table to HUB_SUMMER;
    14. grant select any table to HUB_SUMMER;
    15. grant update any table to HUB_SUMMER;
    16. grant delete any table to HUB_SUMMER;
    17. grant unlimited tablespace to HUB_SUMMER;

    1.2用户授权

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

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

    1. --授权
    2. grant connect to HUB_SUMMER;
    3. grant resource to HUB_SUMMER;
    4. grant create any table to HUB_SUMMER;
    5. grant create any view to HUB_SUMMER;
    6. grant create session to HUB_SUMMER;
    7. grant drop any table to HUB_SUMMER;
    8. grant drop any view to HUB_SUMMER;
    9. grant execute any procedure to HUB_SUMMER;
    10. grant insert any table to HUB_SUMMER;
    11. grant select any table to HUB_SUMMER;
    12. grant update any table to HUB_SUMMER;
    13. grant delete any table to HUB_SUMMER;
    14. grant unlimited tablespace to HUB_SUMMER;
    15. --撤销已经授予的权限
    16. revoke connect from hub_summer;
    17. revoke resource from hub_summer;
    18. revoke create any table from hub_summer;
    19. revoke create any view from hub_summer;
    20. revoke create session from hub_summer;
    21. revoke drop any table from hub_summer;
    22. revoke drop any view from hub_summer;
    23. revoke execute any procedure from hub_summer;
    24. revoke insert any table from hub_summer;
    25. revoke select any table from hub_summer;
    26. revoke update any table from hub_summer;
    27. revoke delete any table from hub_summer;
    28. revoke unlimited tablespace from hub_summer;

    1.2.2 授权访问表权限

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

    1. --授权SQL
    2. GRANT SELECT  ON T_USER_STAT TO HUB_SUMMER;
    3. --查询赋权结果
    4. SELECT *
    5.   FROM all_tab_privs aa
    6.  WHERE aa.grantor = 'HUB_SPRING'
    7.    AND aa.table_name = 'T_USER_STAT';
    8. --在HUB_SUMMER中使用
    9. SELECT * FROM HUB_SUMMER.T_USER_STAT;

    1.2.3 撤销访问表权限

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

    1. --撤销授权SQL
    2. REVOKE  SELECT ON T_USER_STAT FROM HUB_SUMMER;
    3. --查询赋权结果
    4. SELECT *
    5.   FROM all_tab_privs aa
    6.  WHERE aa.grantor = 'HUB_SPRING'
    7.    AND aa.table_name = 'T_USER_STAT';
    8. --在HUB_SUMMER中使用
    9. SELECT * FROM HUB_SUMMER.T_USER_STAT;

    1.3 删除用户

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

    1. --删除用户
    2. 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:

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

    1.5.2 查看实例

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

    查看数据库实例SQL:

    1. --查看数据库版本
    2. select name from v$database;
    3. select instance_name from v$instance;
    4. show parameter instance;

    1.5.3 查看数据库用户信息

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

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

    1. --查看用户信息(全量信息)
    2. select * from dba_users;
    3. --查看用户信息(部分信息)
    4. select * from all_users;
    5. --查看用户信息(当前用户信息)
    6. select * from user_users;

    1.5.4 查看数据库角色和权限

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

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

    1. --查看数据库角色
    2. select * from dba_roles;
    3. --查看数据库角色的特权
    4. select * from dba_sys_privs;
    5. --查看当前用户特权
    6. select * from user_sys_privs;

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

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

    1. select * from dba_tab_privs;
    2. select * from all_tab_privs;
    3. select * from user_tab_privs;

    1.5.6 查看用户拥有的角色

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

    1. select * from dba_role_privs;
    2. select * from user_role_privs;

    1.5.7 查看数据库对象信息

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

    1. --查看数据库中对象信息
    2. select * from dba_objects;
    3. --查看指定用户表信息
    4. select *
    5.   from dba_objects aa
    6.  where aa.owner = 'HUB_SPRING'
    7.    and aa.object_type = 'TABLE'
    8.  order by created desc;
    9. --查看指定用户视图信息
    10.  select *
    11.   from dba_objects aa
    12.  where aa.owner = 'HUB_SPRING'
    13.    and aa.object_type = 'VIEW'
    14.  order by created desc;
    15. --查看数据库对象类型
    16. select aa.object_type, count(1)
    17.   from dba_objects aa
    18.  group by aa.object_type;

    1.5.8 查看表的字段和注释

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

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

    1.5.9 查看数据库所有表

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

    1. --查看指定用户表信息
    2. select *
    3.   from dba_objects aa
    4.  where aa.owner = 'HUB_SPRING'
    5.    and aa.object_type = 'TABLE'
    6.  order by created desc;
    7. --所有用户的表
    8. select * from all_tables;
    9. --包括系统表
    10. select * from dba_tables;
    11. --当前用户的表
    12. select * from user_tables;

    1.5.10 查看数据库所有Jobs

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

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

    2.表空间和数据文件

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

    2.1 查看表空间和数据文件

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

    1. -- 查看表空间数据文件
    2. 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：

    1. -- 创建表空间数据文件
    2. 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：

    1. -- 拓展表空间数据文件
    2. 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：

    1. -- 创建表空间数据文件
    2. 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：

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

    3.序列

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

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

    4.表结构

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

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

    4.1创建普通表

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

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

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

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

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

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

    4.2创建分区表

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

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

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

    1. create table T_SENSOR_DATA_GLOBAL
    2. (
    3.   id            NUMBER(12) not null,
    4.   gather_date   DATE not null,
    5.   position_dir  NUMBER(2) not null,
    6.   gather_time   DATE,
    7.   t1        NUMBER(12,2),
    8.   t2        NUMBER(12,2),
    9. )
    10. partition by range (GATHER_DATE)
    11. (
    12.   partition T20210701 values less than 
    13.    (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    14.     tablespace DATA_HUB,
    15.   partition T20210702 values less than 
    16.    (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    17.     tablespace DATA_HUB
    18.   );
    19. comment on table T_SENSOR_DATA_GLOBAL is 'T_SENSOR_DATA_GLOBAL';
    20. comment on column T_SENSOR_DATA_GLOBAL.id is '唯一标识';
    21. comment on column T_SENSOR_DATA_GLOBAL.gather_date is '日期';
    22. comment on column T_SENSOR_DATA_GLOBAL.position_dir is '位置';
    23. comment on column T_SENSOR_DATA_GLOBAL.gather_time is '时间';
    24. comment on column T_SENSOR_DATA_GLOBAL.t1 is '值1';  
    25. comment on column T_SENSOR_DATA_GLOBAL.t2 is '值1';  
    26. --主键
    27. alter table T_SENSOR_DATA_GLOBAL
    28.   add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID,GATHER_DATE, POSITION_DIR) using index tablespace DATA_HUB;
    29. --索引
    30. create index IDX_T_SENSOR_DATA_GLOBAL_ID on T_SENSOR_DATA_GLOBAL (ID,GATHER_DATE, T1) tablespace DATA_HUB;

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

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

    1. create table T_SENSOR_DATA_LOCAL
    2. (
    3.   id            NUMBER(12) not null,
    4.   gather_date   DATE not null,
    5.   position_dir  NUMBER(2) not null,
    6.   gather_time   DATE,
    7.   t1        NUMBER(12,2),
    8.   t2        NUMBER(12,2),
    9. )
    10. partition by range (GATHER_DATE)
    11. (
    12.   partition T20210701 values less than 
    13.    (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    14.     tablespace DATA_HUB,
    15.   partition T20210702 values less than 
    16.    (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    17.     tablespace DATA_HUB
    18.   );
    19. comment on table T_SENSOR_DATA_LOCAL is 'T_SENSOR_DATA_LOCAL';
    20. comment on column T_SENSOR_DATA_LOCAL.id is '唯一标识';
    21. comment on column T_SENSOR_DATA_LOCAL.gather_date is '日期';
    22. comment on column T_SENSOR_DATA_LOCAL.position_dir is '位置';
    23. comment on column T_SENSOR_DATA_LOCAL.gather_time is '时间';
    24. comment on column T_SENSOR_DATA_LOCAL.t1 is '值1';  
    25. comment on column T_SENSOR_DATA_LOCAL.t2 is '值1';  
    26. --主键
    27. alter table T_SENSOR_DATA_LOCAL
    28.   add constraint PK_T_SENSOR_DATA_LOCAL primary key (ID, GATHER_DATE, POSITION_DIR)
    29.   using index 
    30.   local ;
    31. --索引
    32. create index IDX_T_SENSOR_DATA_LOCAL_ID on T_SENSOR_DATA_LOCAL (ID, GATHER_DATE, T1)
    33.   local ;

    4.3删除表

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

    drop table t_sensor_data_local;

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

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

    1. -- 单字段主键
    2. alter table T_SENSOR_DATA_GLOBAL
    3.   add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID) using index tablespace DATA_HUB;
    4. -- 联合主键
    5. alter table T_SENSOR_DATA_GLOBAL
    6.   add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID,GATHER_DATE, POSITION_DIR) using index tablespace DATA_HUB;

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

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

    1. -- 联合索引
    2. create index IDX_T_SENSOR_DATA_GLOBAL_ID on 
    3. T_SENSOR_DATA_GLOBAL (ID,GATHER_DATE, T1) tablespace DATA_HUB;
    4. -- 单字段索引
    5. create index IDX_T_SENSOR_DATA_GLOBAL_ID on T_SENSOR_DATA_GLOBAL (ID) tablespace DATA_HUB;

    4.6全局索引局部索引

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

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

    1. --查看索引信息(全量)
    2. select * from dba_indexes;
    3. --查看索引信息(根据表名)
    4. select * from dba_indexes where table_name = 'T_SENSOR_DATA_LOCAL';
    5. --查看分区表索引信息(根据用户)
    6. select * from dba_ind_partitions  where index_owner='HUB_SPRING';
    7. --查看分区表索引信息(根据用户)
    8. select * from dba_part_indexes  where owner='HUB_SPRING';
    9. --查看当前用户的分区索引
    10. select * from user_part_indexes;

    4.7查看表分区

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

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

    5.操作表

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

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

    6.视图

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

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

    1. --创建视图,前提t_sensor_data表要存在
    2. create or replace force view view_t_sensor_data as
    3. select * from t_sensor_data;
    4. --使用视图
    5. select * from view_t_sensor_data;
    6. --删除视图
    7. drop view view_t_sensor_data;

    7.同义词(synonym)

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

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

    1. --创建同义词(在用户hub_spring中操作)
    2. --用户hub_spring的表,以同义词的方式,给用户hub_summer使用
    3. create  synonym hub_summer.t_sensor_data for hub_spring.t_sensor_data;
    4. --删除同义词(在hub_spring中操作)
    5. drop synonym hub_summer.t_sensor_data;
    6. --使用同义词(在用户中hub_summer使用)
    7. select * from t_sensor_data;
    8. --查看同义词
    9. select *
    10.   from dba_synonyms
    11.  where table_owner = 'HUB_SPRING'
    12.    AND table_name = 'T_SENSOR_DATA';
    13. --如果一个用户没有创建同义词权限,可以高权限用户创建比如
    14. --hub_summer用户的表t_sensor_data在hub_spring用户创建同义词
    15. create  synonym hub_spring.t_sensor_data for hub_summer.t_sensor_data;  
    16. --赋予可以创建同义词权限
    17. --给hub_summer用户赋权可以创建同义词
    18. grant create any synonym to hub_summer;
    19. --撤销hub_summer用户创建同义词的权限
    20. revoke create any synonym from hub_summer;

    8.触发器

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

    1. --1.创建一个表
    2. create table t_sensor_data
    3. (
    4.   data_id     NUMBER(20) not null,
    5.   value1 NUMBER(16,2),
    6.   value2 NUMBER(16,2)
    7. );
    8. --2.创建一个序列
    9. create sequence seq_log_id
    10. minvalue 1
    11. maxvalue 9999999999999999
    12. start with 20220708
    13. increment by 1
    14. cache 1000;
    15. --3.创建触发器
    16. create or replace trigger t_sensor_data_insert_data_id
    17.   before insert on t_sensor_data
    18.   for each row
    19. begin
    20.   select seq_log_id.Nextval into :new.data_id from dual;
    21. end;
    22. --4.测试验证,data_id会自动赋值
    23. insert into t_sensor_data(value1,value2) VALUES(22.51,23.52);
    24. --删除触发器
    25. 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。

    1. create  database link DEMO_02
    2.   connect to HUB_SUMMER IDENTIFIED BY "***自定义的密码****"
    3.   using '(DESCRIPTION =
    4. (ADDRESS_LIST =
    5. (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.60)(PORT = 1521))
    6. )
    7. (CONNECT_DATA =
    8. (SERVICE_NAME = hubdb))
    9. )';

    9.2创建DB Link(多节点)

    场景：多节点创建Database Link。

    1. create database link DEMO_01
    2.   connect to HUB_SUMMER  IDENTIFIED BY "***自定义的密码****"
    3.   using '(DESCRIPTION =
    4.     (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.61)(PORT = 1521))
    5.     (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.62)(PORT = 1521))
    6.     (ADDRESS = (PROTOCOL = TCP)(HOST = 168.59.80.63)(PORT = 1521))
    7.     (LOAD_BALANCE = yes)
    8.     (CONNECT_DATA =
    9.       (SERVER = DEDICATED)
    10.       (SERVICE_NAME = hubdb)
    11.       (FAILOVER_MODE =
    12.         (TYPE = SELECT)
    13.         (METHOD = BASIC)
    14.         (RETRIES = 180)
    15.         (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创建存储过程

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

    1. --存储过程(不带入参)
    2. CREATE OR REPLACE PROCEDURE PRO_INSERT_INFO IS
    3. BEGIN
    4.   INSERT INTO B_LOG_INFO
    5.     SELECT SEQ_DEMO_ID.NEXTVAL,
    6.            SYSDATE,
    7.            SYSDATE,
    8.            '1',
    9.            '执行成功',
    10.            50,
    11.            'DUAL'
    12.       FROM DUAL;
    13.   COMMIT;
    14. EXCEPTION
    15.   WHEN OTHERS THEN
    16.     ROLLBACK;
    17. END PRO_INSERT_INFO;
    18. --存储过程(带入参)
    19. CREATE OR REPLACE PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL) IS
    20. BEGIN
    21.   -- 1.声明变量
    22.   DECLARE
    23.     V_DATE DATE := TRUNC(SYSDATE - 1);
    24.   BEGIN
    25.     -- 2.记录一条日志
    26.     IF (IN_DATE IS NOT NULL) THEN
    27.       V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
    28.     END IF;
    29. 	-- 3.处理业务逻辑
    30.     INSERT INTO B_LOG_INFO
    31.       SELECT SEQ_DEMO_ID.NEXTVAL,
    32.              V_DATE,
    33.              SYSDATE,
    34.              '1',
    35.              '执行成功',
    36.              50,
    37.              'DUAL'
    38.         FROM DUAL;
    39.     COMMIT;
    40. 	-- 4.更新日志(记录业务成功状态)
    41.   EXCEPTION
    42.     WHEN OTHERS THEN
    43. 	-- 5.更新日志(记录业务异常状态)
    44.     ROLLBACK;
    45.   END;
    46. END PRO_INSERT_INFO_ARGS;

    10.2 Packages和Packages bodies

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

    10.2.1创建Packages和Packages bodies

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

    10.2.2 Packages

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

    1. CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
    2.   PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL,
    3.         FLAG OUT NUMBER,
    4.         FAIL_INFO OUT VARCHAR2);
    5. END PKG_ZBZ_PORTAL;

    10.2.3 Packages bodies

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

    1. CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS
    2.   PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE   IN VARCHAR2 := NULL,
    3.         FLAG  OUT NUMBER,
    4.         FAIL_INFO OUT VARCHAR2) IS
    5.   BEGIN
    6.     DECLARE
    7.       V_DATE DATE := TRUNC(SYSDATE - 1);
    8.     BEGIN
    9.       FLAG      := 1;
    10.       FAIL_INFO := '';
    11.       IF (IN_DATE IS NOT NULL) THEN
    12.         V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
    13.       END IF;
    14.       INSERT INTO B_LOG_INFO
    15.         SELECT SEQ_DEMO_ID.NEXTVAL,
    16.                V_DATE,
    17.                SYSDATE,
    18.                '1',
    19.                '执行成功',
    20.                50,
    21.                'DUAL'
    22.           FROM DUAL;
    23.       COMMIT;
    24.     EXCEPTION
    25.       WHEN OTHERS THEN
    26.         FLAG      := 0;
    27.         FAIL_INFO := '此次执行: ' || IN_DATE || ' 输出结果: FLAG = ' || FLAG ||
    28.                      '异常信息:  ' || SQLERRM;
    29.         ROLLBACK;
    30.     END;
    31.   END PRO_INSERT_INFO_ARGS;
    32. 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：

    1. DECLARE
    2.   FLAG VARCHAR2(64);
    3.   FAIL_INFO  VARCHAR2(4096);
    4. BEGIN
    5.   PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS(
    6.     TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD'),
    7.     FLAG,
    8.     FAIL_INFO);
    9. END;

    其它配置如图：

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

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

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

    11.2配置Job的Action几种配置

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

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

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

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

    情况五：直接写SQL。

    1. --情况一：存储过程，不使用包管理，无参数
    2. BEGIN PRO_INSERT_INFO; END;
    3. --情况二：存储过程，不使用包管理，有参数
    4. BEGIN PRO_INSERT_INFO_ARGS(TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD')); END;
    5. --情况三：存储过程，使用包管理，无参数
    6. BEGIN PKG_INSERT.PRO_INSERT_INFO; END;
    7. --情况四：存储过程，使用包管理，有参数
    8. DECLARE
    9.   FLAG VARCHAR2(64);
    10.   FAIL_INFO  VARCHAR2(4096);
    11. BEGIN
    12.   PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS(
    13.     TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD'),
    14.     FLAG,
    15.     FAIL_INFO);
    16. END;
    17. --情况五：直接写SQL
    18. BEGIN
    19. INSERT INTO B_LOG_INFO_A 
    20. SELECT * FROM B_LOG_INFO_B
    21. COMMIT;
    22. END;

    12.表分析

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

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

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

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

    1. --表分析(非分区表)
    2. begin
    3. dbms_stats.gather_table_stats(
    4.     ownname          => 'HUB_SPRING',
    5.     tabname          => 'T_SENSOR_DATA',
    6.     estimate_percent => 60,
    7.     degree           => 30,
    8.     cascade          => true,
    9. 	 no_invalidate    => false);
    10. end;
    11. --表分析(分区表)
    12. begin
    13. dbms_stats.gather_table_stats(
    14.     ownname          => 'HUB_SPRING',
    15.     tabname          => 'T_SENSOR_DATA_GLOBAL',
    16.     partname         => 'T20210701',
    17.     estimate_percent => 60,
    18.     degree           => 30,
    19.     granularity      => 'all',
    20.     cascade          => true,
    21. 	no_invalidate    => false
    22. 	);
    23. end;

    以上，感谢

    2022年7月9日

  • 相关阅读:
    计算机图形学 实验三:二维图形变换
    想知道“照片识别文字”的技巧吗？快看这几个方法
    【亚马逊云科技产品测评】活动征文｜10分钟拥有一台AWS Linux系统
    ES写入数据报错：retrying failed action with response code: 429
    [附源码]SSM计算机毕业设计在线文献查阅系统JAVA
    虹科干货 | 虹科带你了解车载以太网-SOME/IP协议
    【重识云原生】第六章容器6.3.3节——Kube-Scheduler使用篇
    视频生成模型1
    一文搞懂RepVGG网络
    认识 Express
  • 原文地址：https://blog.csdn.net/zhangbeizhen18/article/details/125697102