oracle数据库使用

1. oracle 问题排查

1.1 linux 这样看 1\先切换到 oracle 用户下面，命令：su - oracle

1.2 看查看 1521 端口 在不在

netstat -utnalp |grep 1521

1.3 监听，端口 都没问题。看看数据库错误日志

• echo $ORACLE_HOME --查询到oracle 安装配置的位置

• 步骤一：查看数据库归档是否开启，通过sqlplus / as sysdba登录数据库

select * from v$diag_info;

• 告警日志位置

cd/opt/oracle/diag/rdbms/fjncdb/fjncdb/alert --查询相应的日志信息

日志信息如下：

1.4 排查是否是最大连接数问题

plsql 执行

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

修改最大连接数 – 在SQLplus.exe中执行

select count(*) from v$process;--查询当前连接数 select value from v$parameter where name = 'processes' ;--查询最大连接数 alter system set processes = 1000 scope = spfile;--修改最大连接数 shutdown immediate;--关闭数据库 startup;--启动数据库

1.5 oralce 异常标识

异常标识链接

https://blog.csdn.net/cuibaolin2011/article/details/100459642

1.6 oralce 数据 客户端链不上问题

2.blob字段操作

oracle blob转成字符

blob长度有三种：小于2000、大于2000，小于4000、blob长度大于4000，不同的长度，对应不同的编写方式，以下就对其进行简单讲解：

首先，blob长度小于2000： SELECT ID, utl_raw.cast_to_varchar2 (dbms_lob.substr (clume_name)) FROM A

其次，blob长度大于2000，小于4000：

SELECT ID, utl_raw.cast_to_varchar2 (DBMS_LOB.SUBSTR (colume_name, 2000, 1)) || utl_raw.cast_to_varchar2 (DBMS_LOB.SUBSTR (colume_name, 2000, 2001)) FROM A

在次，blob长度大于4000：

SELECT ID ,utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(colume_name,2000,1)) colume_name1 ,utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(colume_name,2000,2001)) colume_name2 ,utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(colume_name,2000,4001)) colume_name3 FROM A

2.1 blob字段字符集转换

在 Oracle 中，Convert() 函数可以将字符串从一个字符集转换为另一个字符集。本文要为大家带来的就是 Convert() 函数的使用方法。

CONVERT( string1, char_set_to [, char_set_from] )

string1：要转换的字符串。

char_set_to：要转换为的字符集。

char_set_from：可选的，要从中转换的字符集。

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(SMARTMODEL, 2000, 1))|| --|| utl_raw.cast_to_varchar2(dbms_lob.substr(SMARTMODEL, 2000, 2001)) from bi_smart_def where PK_DEF ='1001ZZ10000000002F7B'; ;

2.2 查看数据编码格式

NLS_CHARACTERSET是数据库字符集，NLS_NCHAR_CHARACTERSET是国家字符集
ORACLE中有两大类字符型数据，VARCHAR2是按照数据库字符集来存储数据。而NVARCHAR2是按照国家字符集存储数据的。同样，CHAR和NCHAR也一样，一是数据库字符符，一是国家字GBK：中国国家标准字符集，是GB2312扩展
ZHS16GBK：是oracle数据库中支持GBK的字符集命名方式。

主要需要关心的就是 NLS_NCHAR_CHARACTERSET 和 NLS_CHARACTERSET。
NLS_CHARACTERSET 是 CHAR, VARCHAR2, CLOB 等列的编码。NLS_NCHAR_CHARACTERSET 是NCHAR, NVARCHAR2, NCLOB等列的编码。

​

select * from nls_instance_parameters where parameter='NLS_LANGUAGE'; select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'; ----执行查询语句截图如下截图 select * from nls_database_parameters;

oracle 导出为chm

3.1 oracle 语句性能相关问题

3.2 数据库空间扩容

select b.file_name 物理文件名, b.tablespace_name 表空间名称, b.bytes/1024/1024 大小m, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用m, substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 使用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;

3.2.1 表空间扩容方式

扩容有三种方式

• 方法一：增大所需表空间大小
• 方法二：增加数据文件的个数
• 方法三：设置表空间自动扩展

3.3 系统表查询

3.31 查询所有表和注释

oracle 库中的模式，相当于mysql 数据库中的数据库

select t.TABLE_NAME, f.COMMENTS from all_tables t left join ALL_TAB_COMMENTS f on t.TABLE_NAME = f.TABLE_NAME where f.OWNER = '模式名称'

3.3.2 当前用户 查询数据库所有表和注释

select t.table_name tableName, f.comments comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name

3.3.3 查询系统当前用户 所有表和表字段 以及字段的注释

SELECT t.TABLE_NAME tableName, t.COLUMN_NAME columnName, t.DATA_TYPE dataType, a.COMMENTS FROM USER_TAB_COLUMNS t LEFT JOIN USER_COL_COMMENTS a ON t.table_name = a.table_NAME AND t.COLUMN_NAME = a.COLUMN_NAME where t.TABLE_NAME='PUB_SYSTEMPLATE_BASE';

3.3.4 查询用户所属模块

3.3.4 查询用户表、索引、视图 的创建语句

注意表名称要大写否则查询不出来

1、查询当前用户下表的创建语句 select dbms_metadata.get_ddl('TABLE','ux_future') from dual; 2、查询其他用户下表的创建语句 select dbms_metadata.get_ddl('TABLE','ux_future','Admin') from dual; 3、查询表的index select index_name,index_type,table_name from user_indexes where table_name = 'ux_future'; 4、查询当前用户下索引的创建语句 select dbms_metadata.get_ddl('INDEX','ux_future_xx') from dual; 5、查询其他用户下索引的创建语句 select dbms_metadata.get_ddl('INDEX','ux_future_xx','Admin') from dual; 6、查询当前用户下视图的创建语句 select dbms_metadata.get_ddl('VIEW','ux_future') from dual; 7、查询其他用户下视图的创建语句 select dbms_metadata.get_ddl('VIEW','ux_future','Admin') from dual; 8、查询表的约束 select owner,table_name,constraint_name,constraint_tpye from user_cinstraints where table_name = 'ux_future'; 9、查询创建主键的语句 select dbms_metadata.get_ddl('CONSTRAINT','ux_future_xp') from dual; 10、查询创建外键的语句 select dbms_metadata.get_ddl('REF_CONSTRAINT','ux_future_kk') from dual; 11、查询一个用户下所有表、索引、存储过程、函数的ddl select dbms_metadata.get_ddl(u.object_type,u.object_name) from user_objects u where u.object_type in ('TABLE','INDEX','PROCEDURE','FUNCTION'); 12、查询所有表空间的ddl select dbms_metadata.get_ddl('TABLESPACE',t.tablespace_name) from dba_tablespaces t; 13、查询创建用户的语句 select dbms_metadata.get_ddl('USER',u.username) from dba_users u;

3.5 oracle 修改数据库密码

4 oracle外链接方式 应用

5. 慢查询sql查询语句

• 1 .查询执行最慢的sql’

select * from(select sa.sql_text, sa.sql_fulltext,sa.executions "执行次数", round(sa.elaosed_time/1000000,2) "总执行时间", round(sa.elapsed_time/1000000/sa.executions,2) "平均执行时间", sa.command_type,sa.parsing_user_id "用户id", u.username "用户名", sa.hash_value from v$sqlarea sa left join all_users u on sa.parsing_user_id = u.user_id where sa.executions >0 order by (sa.elapsed_time/sa.executions) desc) where rownum <=50;

• 2.查询次数最多的sql’

select * from (select s.sql_text,s.executions "执行次数", s.parsing_user_id "用户id", u.username "用户名", rank() over(order by executions desc) exec_rank from v$sql s left join all_users u on u.user_id = s.parsing_user_id) t where exec_rank <=100;

5.1 oracle 优化

5.1.1 oracle 语法树解析过程

5.1.2oralce 组合索引优化使用的场景

5.2 oracle 内存优化参数

6.基础语句使用

6.1 merger into 语句

--Merge into方法 merge into a t1 using(select id, name from b) t2 on (t2.id = t1.id) --using(副表字段); on(匹配条件) when matched then --如果找到匹配条件, 则表示需要更新数据 update set t1.name = t2.name when not matched then --如果找不到匹配条件, 则说明主表不存在数据, 需要插入数据 insert(t1.id, t1.name) values(t2.id, t2.name); commit;

6.2 for update 语句

下面案例中总结解决并发的案例，是为了更好的理解 for update 目前处理并发的方式有多种解决方案，oralce +redis（缓存） + nginx（多实例负载均衡） 的方式

这个月的主要工作任务是语音外呼，由于是面向客户的支撑平台，所以对数据并发的处理需要慎重思考。 语音外呼支撑平台中并发的问题： 自动获取一个导入的回访电话数据，然后更改获取到的这一条数据状态，其他人不能再获取这一条数据，存在的问题是，如果两个人同时查询到了一条数据，第二个的修改就会造成第一个人获取失效。为了避免这种情况的发生，我们使用了select for update对获取到的行进行了加锁。下面简单介绍一下select for update的使用方法。 先说明一下会用到的名词概念： statement: 一个SQL语句。 session: 一个由ORACLE用户产生的连接，一个用户能产生多个SESSION ，但相互之间是独立的。 transaction:所有的改动都能划分到transaction里，一个transaction包含一个或多个SQL。当一个SESSION建立的时候就是个TRANSACTION开始的时刻，此后transaction的开始和结束由DCL控制，也就是每个COMMIT/ROLLBACK都标示着一个transaction的结束。 consistency：是对于statement级别而不是transaction级别来说的。sql statement 得到的数据都是以sql statement开始的IMAGE。 用法介绍： update, insert ,delete, select ... for update会LOCK相应的ROW 。 只有一个TRANSACTION可以LOCK相应的行，也就是说如果一个ROW已经LOCKED了，那就不能被其他TRANSACTION所LOCK了。 LOCK由statement产生但却由TRANSACTION结尾（commit，rollback），也就是说一个SQL完成后LOCK还会存在，只有在COMMIT/ROLLBACK后才会师释放。 简单举例： transaction A下面 select * from connector a where a.MOBILE='13937134399' for updata 如上，这个将会对查询出来的行加上一个行锁，如果在 transaction B下面对这一行数据进行增删改都将会等待，普通的查询可以，使用 select for update 查询也需要等待。 需要在transaction A下面执行需要的操作之后commit或者rollback之后，在其他transaction 下面才可以对此表此行进行操作。 使用这个行锁的情况一般是对并发的情况要求比较高的时候，需要锁住某行进行一些更新语句之后进行释放，再让其他transaction 去操作。在这次外呼系统中很好的利用了这点解决了并发的问题。 for update 后面还可以跟着[OF cols] [NOWAIT] of 的使用主要是针对多表关联的时候，如果不使用of，对两个表涉及到的行都将锁住，使用of可以指定锁定哪个表， 例如：select a.MOBILE,b.NAME from connector a,student b where a.STU_ID=b.ID and a.MOBILE='13937134399' for updata of a.MOBILE 这样的话student表中对应的行是不加锁的，对connector一个表中行加锁 不使用两个表都加锁。 [NOWAIT]的使用是当锁冲突的时候提示的情况： 当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待.返回错误是"ORA-00054: resource busy and acquire with NOWAIT specified" ，如果不使用就会一直等待，直到锁释放之后执行。 在页面上调试的时候由于异常处理不好，把数据锁住了没有提交，也没有rollback，遇到这样的情况的时候可以通过以下方式解决： -----查看被锁对象的序列号、sid SELECT o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC / ------利用sid 和序列号删除 alter system kill session '243,10265'; 243是sid 10265是序列号 所以在使用锁的时候一定要做好页面的异常控制，不然很容易出问题。 ******************************************************* 数据库中锁类型的介绍： 有两种基本的锁类型，排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。 其中DML锁（data locks，数据锁），用于保护数据的完整性 DML锁的目的在于保证并发情况下的数据完整性，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

​

扩展mysql for update 使用

6.3 递归查询

6.3 触发器

6.3.1 触发器的创建

2.1 创建触发器 CREATE [OR REPLACE] TRIGGER 触发器名 --一般格式 tr_* {BEFORE | AFTER} --触发时间 view中是instead of {insert | update | delete} --触发事件 dml，ddl，datebase [of 列名] --特定列触发 on 触发对象 --table,view,schema,datebase {for each row} --触发频率 默认为：语句级触发 [follows 其他触发器名] --多个触发器执行的前后顺序 [when 触发条件] [declare] --可选 变量申明; begin pl/sql 语句; end;

关键字说明： 1. 触发器名：一般格式 tr_* 2. 触发时间：在 '触发事件' 发生之前（before）还是之后（after） Before 触发器可以防止修改发生, after触发器则为事后校验; 如果是做权限检查, 用BEFORE触发器更为高效，因为可以减少昂贵的回滚处理。 BEFORE 行触发器可以修改:NEW的值，AFTER行触发器不可以。 BEFORE 行触发器看到的不是最终的值，而且如果有多个BEFORE行触发器， 它们的触发是无序的、随机的，在一个触发器中看到的数据可能随后被另一个改动， 所以不能用于数据校验，应该改用AFTER行触发器。 如果你需要修改:NEW的值就用BEFORE行触发器，如果你需要校验:NEW的值就用AFTER行触发器。此外AFTER触发器会产生较少的REDO。 3. 触发事件：根据不同的 '触发事件'，可以分为不同的 '类型' 多个触发事件用 or 连接 4. 触发对象：table、view、schema、database 5. 触发频率：'语句级触发器'（默认）指触发一次，'行级触发器' 每一行触发一次 6. 触发条件：仅当 '触发条件' 为 True 时，才执行 pl/sql 语句 7. 条件判断：inserting,updating,deleting 对应相应的事件，返回值为bool值

----触发器 create or replace trigger modify_stu before insert or update or delete on '表名称' begin if deleting then raise_application_error(-20001 ,'该表不允许删除数据'); elsif updating then raise_application_error(-20002, '该表不允许删除数据'); elsif inserting then raise_application_error(-20003,'该表不允许插入数据'); end if; end;

create or replace trigger so_saleorder_trigger_20231106 after update on so_saleorder for each row begin if updating then ---修改 if :OLD.dr=1 then raise_application_error(-20018,''||:OLD.VBILLCODE||'该行的已经设置为1 不能再更新了'); end if; end if; end;

6.3.2触发器的差值

--1. 依据表名称查询表存在的触发器 select trigger_name from all_triggers where table_name='tUsers'; -- 2. 查询触发器的创建语句 select text from all_source where type='TRIGGER' AND name='REPCATLOGTRIG';

6.3.3 触发器 通过触发器 记录 更新的表的记录

创建目标表

CREATE TABLE audit_table ( operation_type VARCHAR2(10), operation_time TIMESTAMP, primary_key_column NUMBER, -- 原始表中的主键列 other_columns VARCHAR2(255) -- 如果需要记录其他信息 );

创建触发器

CREATE OR REPLACE TRIGGER audit_trigger AFTER INSERT OR UPDATE OR DELETE ON original_table FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO audit_table (operation_type, operation_time, primary_key_column, other_columns) VALUES ('INSERT', SYSTIMESTAMP, :new.primary_key, :new.other_column); ELSIF UPDATING THEN INSERT INTO audit_table (operation_type, operation_time, primary_key_column, other_columns) VALUES ('UPDATE', SYSTIMESTAMP, :new.primary_key, :new.other_column); ELSIF DELETING THEN INSERT INTO audit_table (operation_type, operation_time, primary_key_column, other_columns) VALUES ('DELETE', SYSTIMESTAMP, :old.primary_key, :old.other_column); END IF; END;

• 触发器校验下游单据有单据时，删除上游单据的校验

create or replace trigger ic_saleout_h_del_check_trigger before delete on ic_generalout_b for each row declare v_child_exists number; begin -- 检查子表中是否存在关联的记录 select count(*) into v_child_exists from ia_i7bill_b where ia_i7bill_b.csrcbid=:old.cgeneralbid; if v_child_exists>0 then --- raise_application_error(-20001, 'cannot delete parent record because child records exist.'); raise_application_error(-20001, '不能删除 库存其他出库单 下游单据有 其它出库 单据'); end if; end;

触发器校验数据完成性

CREATE OR REPLACE TRIGGER check_date_format_trigger BEFORE INSERT OR UPDATE ON MED_LOTNO_148 FOR EACH ROW DECLARE -- 定义一个用于存储日期格式是否正确的变量 valid_date_format BOOLEAN := FALSE; BEGIN -- 检查日期格式是否符合预期的格式，这里以'YYYY-MM-DD'为例 IF LENGTH(:NEW.VINVALIDDATE) >10 THEN valid_date_format :=TRUE ; END IF; -- 如果日期格式不正确，则抛出异常 IF valid_date_format THEN RAISE_APPLICATION_ERROR(-20001, '日期格式不正确，请使用YYYY-MM-DD 或者YY-MM 格式。'); END IF; END;

6.4 函数

• 函数结构

CREATE OR REPLACE FUNCTION 函数名(参数1[IN] 数据类型，参数2 [IN]数据类型……) RETURN 返回的数据类型 --不要带精度 IS|AS ---IS 或者 AS 随便写一个都可以 声明变量 BEGIN 函数的具体逻辑; RETURN 声明变量;--里面必须要有一个RETURN子句 ---异常处理 EXCEPTION WHEN OTHERS THEN ROLLBACK;---如果涉及到了对数据的 增 删 改 DBMS_OUTPUT.put_line(SQLERRM); --将报错信息打印 END;

CREATE OR REPLACE FUNCTION is_valid_date( date_string IN VARCHAR2, format_mask IN VARCHAR2 ) RETURN BOOLEAN IS test_date DATE; BEGIN BEGIN test_date := TO_DATE(date_string, format_mask); RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; END;

6.5 oracle 锁使用

1.3 相关视图 select * from v$locked_object; – 查询 dml 锁信息 select * from dba_ddl_locks; – 查询 ddl 锁信息 (3) select * from v$lock; – 查询所有锁信息

7. oracle explain for 解析执行计划

7.1 oracle 执行的 口诀

最右最上先执行

方法

1. 从上往下，第一个没有子节点的步骤先执行

2. 对于兄弟节点，即靠上的节点先执行

3. 所有兄弟节点执行完以后，执行父节点

4. 缩进最深的，最先执行

5. 缩进深度相同的，先上后下

由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

explain plan for ... select * from table(DBMS_XPLAN.DISPLAY());

推荐阅读的！！！

7.2 检索的方式

1.TABLE ACCESS BY … 即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式（非全部）: a.TABLE ACCESS FULL（全表扫描）: Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件； 全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量； 使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上 b.TABLE ACCESS BY ROWID（通过ROWID的表存取）: ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值； 你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作； 一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。 让我们再回到 TABLE ACCESS BY ROWID 来：行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法； c.TABLE ACCESS BY INDEX SCAN（索引扫描）: 在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。 所以索引扫描其实分为两步： Ⅰ：扫描索引得到对应的ROWID Ⅱ：通过ROWID定位到具体的行读取数据 d.TABLE ACCESS BY INDEX ROWID BATCHED: The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block. 这句话的意思是说，该操作是数据库为了从索引中获取一些rowid，接着，试着按照块顺序存取块中的数据行， 以便用来改善聚集效果和减少对一个数据块存取的次数。 官方解释的意思就是这样，但怎么理解呢？之前，当我们通过索引获取的rowid回表获取相应数据行时， 都是读一个rowid回表获取一次相应数据行，然后，再读一个rowid，再回表获取一次相应数据行。。。， 这样一直读取完所有所需数据。当不同rowid对应的数据行存储在一个数据块中时，就可能会发生对 同一表数据块的多次读取，当一个索引的聚集因子比较低时，这也是一个必然结果，从而浪费了系统 资源。Oracle 12c中该新特性，通过对rowid对应的数据块号进行排序，然后回表读取相应数据行， 从而避免了对同一表数据块的多次重复读取，从而改善了SQL语句的性能，降低了资源消耗。 该特性通过隐藏参数“_optimizer_batch_table_access_by_rowid”控制，默认值为true，即为开启。 ----------------索引扫描延伸------------------- 索引扫描又分五种： (a)INDEX UNIQUE SCAN（索引唯一扫描） 针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录； 表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描； (b)INDEX RANGE SCAN（索引范围扫描） 使用一个索引存取多行数据； 发生索引范围扫描的三种情况： 在唯一索引列(unique索引)上使用了范围操作符（如：> < <> >= <= between） 在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描） 对非唯一索引列(非unique)上进行的任何查询 (c)INDEX FULL SCAN（索引全扫描） 进行全索引扫描时，查询出的数据都必须从索引中可以直接得到 （注意全索引扫描只有在CBO模式下才有效） (d)INDEX FAST FULL SCAN（索引快速扫描） 扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对 查询出的数据进行排序（即数据不是以排序顺序被返回） (e)INDEX SKIP SCAN（索引跳跃扫描） Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现， oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;什么时候会触发 INDEX SKIP SCAN 呢？ 前提条件：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作 为条件，并且优化器模式为CBO时当Oracle发现前导列的唯一值个数很少时，会将每个唯 一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询； 例如： 假设表emp有ename（雇员名称）、job（职位名）、sex（性别）三个字段，并且建立 了如 create index idx_emp on emp (sex, ename, job) 的复合索引；因为性别只有 '男' 和 '女' 两个值，所以为了提高索引的利用率，Oracle可将这个复合索引拆成 ('男', ename, job)，('女', ename, job) 这两个复合索引；当查询 select * from emp where job = 'Programmer' 时，该查询发出后：Oracle先进入sex为'男'的入口，这时候使用到了 ('男', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目；再进入sex为'女'的入口，这时候使用到了 ('女', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目； 最后合并查询到的来自两个入口的结果集。 ----------------分区表扫描方式----------------------- PARTITION RANGE ALL 扫描所有分区 PARTITION RANGE ITERATOR 扫描部分分区 PARTITION RANGE SINGLE 扫描单个分区

7.3表连接的几种方式

left join right 属于逻辑上的链接，下面的几种链接方式为数据库物理的链接方式

注：这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2

(1)SORT MERGE JOIN（排序-合并连接） 假设有查询：select a.name, b.name from table_A a join table_B b on (a.id = b.id) 内部连接过程： a) 生成 row source 1 需要的数据，按照连接操作关联列（如示例中的a.id）对这些数据进行排序 b) 生成 row source 2 需要的数据，按照与 a) 中对应的连接操作关联列（b.id）对数据进行排序 c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接） 延伸： 如果示例中的连接操作关联列 a.id，b.id 之前就已经被排过序了的话，连接速度便可大大提 高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。故可以考虑在 a.id，b.id 上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通 常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处 理（如仅仅只查询有索引的列等）。 排序-合并连接的表无驱动顺序，谁在前面都可以； 排序-合并连接适用的连接条件有： < <= = > >= ，不适用的连接条件有： <> like (2)NESTED LOOPS（嵌套循环） JOIN 关键字用于将两张表作连接，一次只能连接两张表，JOIN 操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）；表（row source）之间的连接顺序对于查询效率有很大的影响，对首先存取的表（驱动表）先应用某些限制条件（Where过滤条件）以得到一个较小的row source，可以使得连接效率提高。 -------------------------延伸阅读：驱动表（Driving Table）与匹配表（Probed Table）------------------------- 驱动表（Driving Table）：表连接时首先存取的表，又称外层表（Outer Table），这个概念用于 NESTED LOOPS（嵌套循环） 与 HASH JOIN（哈希连接）中；如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，故一般选择小表（应用Where限制条件后返回较少行数的表）作为驱动表。 匹配表（Probed Table）：又称为内层表（Inner Table），从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（应用Where限制条件后返回较多行数的表）。 内部连接过程： a) 取出 row source 1 的 row 1（第一行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中 b) 取出 row source 1 的 row 2（第二行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中 c) 若 row source 1 （即驱动表）中返回了 N 行数据，则 row source 2 也相应的会被全表遍历 N 次。 因为 row source 1 的每一行都会去匹配 row source 2 的所有行，所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。 延伸： 嵌套循环的表有驱动顺序，注意选择合适的驱动表。嵌套循环连接有一个其他连接方式没有的好处是： 可以先返回已经连接的行，而不必等所有的连接操作处理完才返回数据，这样可以实现快速响应。 应尽可能使用限制条件（Where过滤条件）使驱动表（row source 1）返回的行数尽可能少，同时在 匹配表（row source 2）的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的 非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接 操作关联列上存在索引，连接效率也不会很高。 (3)HASH JOIN（哈希连接） 哈希连接只适用于等值连接（即连接条件为 = ） HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式； 内部连接过程简述： a) 取出 row source 1（驱动表，在HAS H JOIN中又称为Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap） b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据 ----------------延伸阅读：Hash Table相关---------------- 来自Wiki的解释：In computing, a hash table (hash map) is a data structure used to implement an associative array, a structure that can map keys to values. A hash table uses a hash function to compute an index into an array of buckets or slots, from which the desired value can be found.散列（hash）技术：在记录的存储位置和记录具有的 关键字key之间建立一个对应关系 f ，使得输入key后，可以得到对应的存储位置 f(key)，这个对应关系 f 就是散列 （哈希）函数； 采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）； 不同的key经同一散列函数散列后得到的散列值理论上应该不同，但是实际中有可能相同，相同时即是发生了散列 （哈希）冲突，解决散列冲突的办法有很多，比如HashMap中就是用链地址法来解决哈希冲突； 哈希表是一种面向查找的数据结构，在输入给定值后查找给定值对应的记录在表中的位置以获取特定记录这个过程的 速度很快。 -------------------------------------------------------- HASH JOIN的三种模式： a.OPTIMAL HASH JOIN OPTIMAL 模式是从驱动表（也称Build Table）上获取的结果集比较小，可以把根据结果集构建的整个Hash Table都建立在用户可以使用的内存区域里。 连接过程简述： Ⅰ：首先对Build Table内各行数据的连接操作关联列使用Hash函数，把Build Table的结果集构建成内存中的Hash Table。如图所示，可以把Hash Table看作内存中的一块大的方形区域，里面有很多的小格 子，Build Table里的数据就分散分布在这些小格子中，而这些小格子就是Hash Bucket（见上面Wiki的 定义）。 Ⅱ：开始读取匹配表（Probed Table）的数据，对其中每行数据的连接操作关联列都使用同上的Hash函数，定位Build Table里使用Hash函数后具有相同值数据所在的Hash Bucket。 Ⅲ：定位到具体的Hash Bucket后，先检查Bucket里是否有数据，没有的话就马上丢掉匹配表（Probed Table）的这一行。如果里面有数据，则继续检查里面的数据（驱动表的数据）是否和匹配表的数据相匹配。 b.ONEPASS HASH JOIN 从驱动表（也称Build Table）上获取的结果集较大，无法将根据结果集构建的Hash Table全部放入内存中时，会使用 ONEPASS 模式。 连接过程简述： Ⅰ：对Build Table内各行数据的连接操作关联列使用Hash函数，根据Build Table的结果集构建Hash Table后，由于内存无法放下所有的Hash Table内容，将导致有的Hash Bucket放在内存里，有的Hash Bucket放在磁盘上，无论放在内存里还是磁盘里，Oracle都使用一个Bitmap结构来反映这些Hash Bucket的状态（包括其位置和是否有数据）。 Ⅱ：读取匹配表数据并对每行的连接操作关联列使用同上的Hash函数，定位Bitmap上Build Table里使用Hash函数后具有相同值数据所在的Bucket。如果该Bucket为空，则丢弃匹配表的这条数据。如果不为空，则需要看该Bucket是在内存里还是在磁盘上。 如果在内存中，就直接访问这个Bucket并检查其中的数据是否匹配，有匹配的话就返回这条查询结果。如果在磁盘上，就先把这条待匹配数据放到一边，将其先暂存在内存里，等以后积累了一定量的这样的待匹配数据后，再批量的把这些数据写入到磁盘上（上图中的 Dump probe partitions to disk）。 Ⅲ：当把匹配表完整的扫描了一遍后，可能已经返回了一部分匹配的数据了。接下来还有Hash Table中一部分在磁盘上的Hash Bucket数据以及匹配表中部分被写入到磁盘上的待匹配数据未处理，现在Oracle会把磁盘上的这两部分数据重新匹配一次，然后返回最终的查询结果。 c.MULTIPASS HASH JOIN 当内存特别小或者相对而言Hash Table的数据特别大时，会使用 MULTIPASS 模式。MULTIPASS会多次读取磁盘数据，应尽量避免使用该模式。 (4)CARTESIAN PRODUCT（笛卡尔积） 不做描述，尽量避免笛卡尔积的发生。

8.存储过程

CREATE OR REPLACE PROCEDURE procedure_name ( parameter1 IN type, parameter2 OUT type, ... ) IS -- 声明变量 variable_name type; BEGIN -- 存储过程体 ... EXCEPTION WHEN others THEN -- 异常处理 ... END procedure_name;

8.1 Oracle存储过程中的游标（Cursor）

游标是一种数据库处理技术，它允许我们在查询结果集出来之前对其进行处理和操作。游标可以分为静态游标和动态游标两种类型。静态游标在查询结果集出来之后不会改变，而动态游标则会随着数据库的变化而变化。

在Oracle中，有两种类型的游标：显式游标和隐式游标。显式游标是程序员明确声明的游标，它可以允许程序员控制如何处理查询结果集。隐式游标则是在执行查询时自动创建的游标，它由Oracle数据库引擎自动处理。

存储过程实例

create or replace procedure zzda_ty is begin update MOQ_ENTLIC_148 set BOPERANGECTRL='N',BISVALID='N',ENABLESTATE='3' where PK_ORG='对应的组织' and dr=0 and PK_ENTLICSOURCE <> '~' and nvl(ENABLESTATE,2) not in (3) and PK_SYSUPORCUS ='0000YC00000000000000' and to_date(DENDDATE,'yyyy-mm-dd hh24:mi:ss') <sysdate end;

存储过程定时任务执行

9.问题处理

9.1 去年同期月份查询

select distinct EXTRACT(month from to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD') ),EXTRACT(year from to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD')), to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD'),substr(dbilldate, 0, 10),add_months(to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD'), -12),dbilldate from po_order where add_months(to_date(substr('2023-10-10', 0, 10), 'YYYY-MM-DD'), -12)<= to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD') and to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD') <= to_date(substr('2023-10-10', 0, 10), 'YYYY-MM-DD') and EXTRACT(month from to_date(substr(dbilldate, 0, 10), 'YYYY-MM-DD') ) = EXTRACT( month from to_date(substr('2023-10-10', 0, 10), 'YYYY-MM-DD'));

9.2 项目上问题处理

9.2.1 Oracle错误——ORA-03113:通信通道的文件结尾 解决办法

db_recovery_file_dest_size参数用于指定Oracle Flash Recovery Area的最大大小。如果需要查询当前的db_recovery_file_dest_size设置，可以使用以下SQL查询：

SELECT * FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest_size';

这条SQL语句会返回一个结果，显示db_recovery_file_dest_size参数的当前值和其它相关信息。

如果你想查看更详细的信息，比如使用率，可以使用以下查询：

SELECT * FROM V$RECOVERY_FILE_DEST;

9.2.1 oracle 密码过期处理

• 查询密码过期时间 ORA-28001: the password has expired

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'

• 在密码将要过期或已经过期时可通过如下语句进行修改密码，密码修改后该用户可正常连接数据库。

ALTER USER 用户名 IDENTIFIED BY 密码 ;

• 如果想去除180天的密码生存周期的限制可通过如下SQL语句将其关闭

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED 如上SQL语句将口令有效期默认值180天修改成了“无限制”。

9.3 oracle 环境问题总结