oracle学习笔记

27.Oracle深度学习笔记——ORACLE审计审计（Audit)用于监视用户所执行的数据库操作，并且Oracle会将审计跟踪结果存放到OS文件（默认位置为$ ORACLE_BASE/admin/$ORACLE_SID/adump/）或数据库（存储在system 表空间中的SYS.AUD$表中不管是否打开数据库的审计功能，用管理员权限连接Instance；启动数据库；关闭数据库都会被记录。

1.相关参数：audit_sys_operations12C默认TRUE当设置为true时，所有sys用户（包括以sysdba,sysoper身份登录的用户）的操作都会被记录，audit trail不会写在aud$表中，这个很好理解，如果数据库还未启动aud$不可用，那么像conn /as sysdba这样的连接信息，只能记录在其它地方。

如果是windows平台，audti trail会记录在windows的事件管理中，如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。

audit_trail12C默认DBDB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；None：不做审计；2.审计级别开启审计功能后，可在三个级别对数据库进行审计：Statement(语句)、Privilege（权限）、object（对象）。

Statement按语句审计，如audit table 会审计数据库中所有的create table,drop table,truncate table语句Privilege按权限来审计，当用户使用了该权限则被审计，如执行grant select any table to a，当执行了audit select any table语句后，当用户 a 访问了用户b的表时（如select * fromb.t）会用到select any table权限，故会被审计。

Oracle学习笔记：wm_concat函数合并字段 在Oracle中使⽤wm_concat(column)可以实现字段的分组合并，逗号分隔。

例如，现有表temp_cwh_test：-- 创建临时表create table temp_cwh_test(u_id varchar(10),goods varchar(32),num number(5))-- 插⼊数据insert into temp_cwh_test(u_id, goods, num)values('1','苹果',2);insert into temp_cwh_test(u_id, goods, num)values('2','梨⼦',5);insert into temp_cwh_test(u_id, goods, num)values('1','西⽠',4);insert into temp_cwh_test(u_id, goods, num)values('3','葡萄',1);insert into temp_cwh_test(u_id, goods, num)values('3','⾹蕉',1);insert into temp_cwh_test(u_id, goods, num)values('1','橘⼦',3);-- 查看表select*from temp_cwh_test; 1.想按u_id分组，进⾏goods合并-- 使⽤wm_concat函数实现字段合并select u_id,wm_concat(goods) goods_sumfrom temp_cwh_testgroup by u_id; 得到： 2.想按u_id分组，进⾏goods和num合并select u_id,wm_concat(goods ||'('|| num ||'⽄）') goods_sumfrom temp_cwh_testgroup by u_id; 得到： 3.以“|”进⾏分隔合并select u_id, replace(wm_concat(goods),',','|') as goods_sumfrom temp_cwh_testgroup by u_id 得到： 4.其他 Oracle 11g后，推荐使⽤listagg函数。

Oracle数据库学习笔记_Oracle添加主键primarykey的四种⽅法创建主键oracle主键添加语句通常紧跟在建表语句之后，也可以直接嵌在列声明⾥创建，oracle创建主键时会⾃动在该列上创建索引。

常见⽅法⼤概有以下5种：⽅法⼀、使⽤add constraint ⽅法添加主键约束alter table 表名 add constraint 主键名 primary key (列名1，列名2，...)⽅法⼆、使⽤索引创建主键（和⽅法⼀没有区别，可以将⽅法⼀理解为省略了using index）alter table 表名 add constraint 主键名 primary key (列名1，列名2，...)using index [index_name]；当省略using index后⾯的index_name时，创建主键的同时创建同名索引；当使⽤已有索引index_name创建主键时，注意索引列和主键列应该相同才能创建成功。

⽅法三、直接添加主键alter table 表名 add primary key (列名1，列名2，...) ;同样，创建主键的同时创建同名索引。

⽅法四、参数列内添加主键create table ALERT_RESULT_EVENT_C(data_date VARCHAR2(8) not null,object_id VARCHAR2(600) not null,event_id VARCHAR2(20) not null,ratio NUMBER(22,4),pairing_object_id VARCHAR2(128),index_value_1 VARCHAR2(128),index_value_2 VARCHAR2(128),constraint PK_ALERT_RESULT_EVENT_C primary key (DATA_DATE, OBJECT_ID, EVENT_ID));删除主键alter table 表名 drop primary key ;采⽤该语句删除主键时，同名索引也会被删掉。

1.PL/SQL综述本章学习目标，了解如下内容：PL/SQL的功能和作用PL/SQL 的优点和特征；Oracle 10g、Oracle9i 的PL/SQL新特征1.1.SQL简介1.1.1.SQL语言特点SQL语言采用集合操作方式1.1.2.SQL语言分类●数据查询语言（SELECT语句）：检索数据库数据。

●数据操纵语言（DML）：用于改变数据库数据。

包括insert，update和delete三条语句。

●事务控制语言（TCL）：用于维护数据库的一致性，包括commit，rollback和savepoint 三条语句●数据定义语言（DDL）：用户建立、修改和删除数据库对象。

●数据控制语言（DDL）：用于执行权限授予和收回操作。

包括grant 和revoke两条命令。

1.1.3.SQL 语句编写规则●SQL关键字不区分大小写●对象名和列名不区分大小写●字符值和日期值区分大小写●书写格式随意1.2.PL/SQL简介1.3.Oracle 10G PL/SQL 新特征2.PL/SQL开发工具本章学习目标：学会使用SQL*PLUS学会使用PL/SQL developer；学会使用Procedure Builder。

2.1.SQL*PLUS在命令行运行SQL*PlusSqlplus [username]/[password] [@server]3.PL/SQL 基础学习目标：●了解PL/SQL块的基本结构以及PL/SQL块的分类；●学会在PL/SQL块中定义和使用变量●学会在PL/SQL块中编写可执行语句；●了解编写PL/SQL代码的指导方针；●了解Oracle 10g的新特征——新数据类型BINARY_FLOAT 和BINARY_DOUBLE，以及指定字符串文本的新方法。

3.1.PL/SQL 块简介3.1.1.PL/SQL块结构3.1.2.PL/SQL 块分类匿名块命名块子程序触发器3.2. 定义并使用变量3.2.1.标量变量3.2.2.复合变量3.2.3.参照变量3.2.4.LOB 变量3.2.5.非PL/SQL 变量3.3.编写 PL/SQL 代码3.3.1.PL/SQL 词汇单元分隔符标识符文本（数字文本，字符文本，字符串文本，布尔文本，日期时间文本）注释3.3.2.PL/SQL 代码编码规则标识符命名规则大小写规则代码缩进嵌套块和变量范围PL/SQL中可以使用的SQL函数4.使用SQL语句学习目标：学会使用SELECT语句去完成基本查询功能学会使用INSERT，UPDA TE和DELETE语句去操作数据库数据学会使用COMMIT，ROLLBACK和SA VEPOINT语句去控制事务学会使用SELECT语句去实现各种复杂查询功能（数据分组、连接查询、子查询、层次查询、合并查询等）4.1.使用基本查询处理NULL：函数nvl(expr1,expr2)，nvl2(expr1,expr2,expr3)4.2.使用DML语句使用多表插入数据语法：INSERT ALL insert_into_clause [value_clause] subquery;INSERT conditional_insert_clause subquery;示例1：使用ALL 操作符执行多表插入INSERT ALLWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSelect * from emp;示例2：使用FIRST 操作符执行多表插入INSERT FIRSTWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSELECT * FROM emp;4.3.使用事务控制语句4.3.1.事务和锁4.3.2.提交事务4.3.3.回退事务设置保存点：savepoint a;或者exec dbms_transaction.savepoint(‘a’)取消部分事务Rollback to a;或者Exec dbms_transaction.rollback_savepoint(‘a’)取消全部事务：Rollback; 或者exec dbms_transaction.rollback() 4.3.4.只读事务4.3.5.顺序事务4.4.数据分组4.4.1.分组函数MaxMinAvgSumCountVarianceStddev使用分组函数注意事项：●当使用分组函数时，除了函数Count(*) 之外，其他分组函数都会忽略NULL行。

【Oracle学习笔记】定时任务（dbms_job）⼀、概述Oralce中的任务有2种：Job和Dbms_job，两者的区别有：1． jobs是oracle数据库的对象， dbms_jobs只是jobs对象的⼀个实例，就像对于tables， emp和dept都是表的实例。

2．创建⽅式也有差异，Job是通过调⽤dbms_scheduler.create_job包创建的，Dbms_job则是通过调⽤dbms_job.submit包创建的。

3．两种任务的查询视图都分为dba和普通⽤户的，Job对应的查询视图是dba_scheduler_jobs和user_scheduler_jobs，dbms_jobs对应的查询视图为dba_jobs和user_jobs。

这⾥主要是介绍Dbms_job。

⼆、使⽤1、创建job：1BEGIN2 DBMS_JOB.SUBMIT(3 JOB OUT BINARY_INTERGER,--输出变量，是此任务在任务队列中的编号，也可以⾃定义，⼀般不传4 WHAT IN VARCHAR2,--执⾏的任务的名称及其输⼊参数5 NEXT_DATE IN DATE DEFAULT SYSDATE,--任务执⾏的时间6 INTERVAL IN VARCHAR2DEFAULT NULL,--任务执⾏的时间间隔7 NO_PARSE IN BOOLEAN DEFAULT FALSE,--⽤于指定是否需要解析与作业相关的过程8 INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,--⽤于指定哪个例程可以运⾏作业9 FORCE IN BOOLEAN DEFAULT FALSE--⽤于指定是否强制运⾏与作业相关的例程10 );11END新⼿可以使⽤窗⼝创建：2、删除job: dbms_job.remove(jobno); -- jobno任务号3、修改要执⾏的操作: job:dbms_job.what(jobno, what); --指定任务号以及存储过程4、修改下次执⾏时间：dbms_job.next_date(jobno, next_date); --指定任务号的时间5、修改间隔时间：dbms_job.interval(jobno, interval); --指定任务号的间隔时间6、改变与作业相关的所有信息,包括作业操作,作业运⾏⽇期以及运⾏时间间隔等.1 dbms_job.change(2 job in binary_integer,3 what in varchar2,4 next_date in date,5 interval in varchar2,6 instance in binary_integer default null,7 force in boolean default false8 );例⼦：dbms_job.change(2,null,null,'sysdate+2');6、启动job: dbms_job.run(jobno); --指定任务号启动7、停⽌job: dbms.broken(jobno, broken, nextdate); –broken为boolean值 N代表启动，Y代表没启动（STOP）三、Interval 说明间隔/interval是指上⼀次执⾏结束到下⼀次开始执⾏的时间间隔，当interval设置为null时，该job执⾏结束后，就被从队列中删除。

12.81、冲销日记账1)原始借：租金10,000 本位币美元贷：现金10,000 本位币美元冲销借：现金10,000 本位币美元贷：租金10,000 本位币美元2)可以创建冲销日记账分录来冲销应计、估计、临时调整和重新分类，或更正错误。

3)方法：转换借贷项：通过转换借项和贷项金额来冲销日记账分录。

红字冲销：通过将原始日记账金额从正值改为负值来冲销日记账分录。

4)日记账> 输入> 复核日记账（B）其他活动日记账> 生成> 冲销2、自动冲销日记账1)可以自动冲销上一个月的应计日记账分录，并自动将其过账（如果需要）。

2)自动冲销日记账的前提条件：--日记账余额类型为“实际”--日记账类型已启用“自动冲销”选项--日记账已过帐，但尚未冲销--日记账冲销期间为“打开”或“将来可输入”3) 设置> 日记账> 自动冲销（AutoReverse）3、日记账分录报表提供：会计结算日期、类别、日记账名称、参考、日记账批4、与Oracle General Ledger 集成--在Excel中创建日记账--通过Web ADI 加载到GL_INTERFACE 表--从接口表中提交“日记账导入”-从Web ADI中与加载流程同时提交-从Web ADI 中作为独立的提交流程提交-从Oracle General Ledger中使用“导入日记账”窗口提交5、Web ADI 的核心功能1)布局功能--从布局中删除或向布局中添加字段--指定字段在布局中的位置--为布局中的字段分配默认值--保存布局，可以由具有适当责任的某个人来定义，然后由整个站点使用2) 文本导入功能--将文本文件数据导入到桌面文档中--创建可以修改和重复使用的映射模板，根据需要更改数据临时的移动目标。

3) 安全功能--将Web ADI 功能与菜单关联，创建用作安全配置文件的自定义访问点--将菜单附加到分配给用户的职责中，用用户级别限制Web ADI访问--为自助连接添加默认参数：强制用户在其桌面上生成文档时使用预定义的参数--将表单功能与用户的菜单关联，已授予其访问集成器的权限4) Internet 计算功能--集中部署，要操作词产品，客户机只需要浏览器和Excel--在Web 上运行6、通过Web ADI 进行桌面集成1) 集成器：Oracle General Ledger-日记账集成器、Oracle General Ledger-预算集成器、创建资产集成器、实地盘点集成器、HRMS集成器2)Oracle General Ledger-日记账集成器：--根据可定义的布局，自动生成基于电子表格的日记账分录工作表--允许通过复制和粘贴日记账行，然后进行增量式修改来快速的输入日记账--允许您定义可以反复修改和加载的日记账模板--使用安全措施和交叉验证规则以及其他引用字段全面验证账户--通过Oracle General Ledger开放接口将日记账加载到Oracle General Ledger7、导入日记账日记账> 导入> 运行1)将子分类帐和源系统数据导入Oracle General Ledger--定义分类账、币种、帐户、日记账来源和类别设置Oracle General Ledger,以接受日记账导入数据--运行“优化”程序，并定义并发程序控件。

韩顺平oracle学习笔记第0讲：如何学习oracle一、如何学习oracleOracle目前最流行的数据库之一，功能强大，性能卓越。

学习oracle需要具备一定基础：1.学习过一门编程语言（如：java ，c）2.最好学习过一门别的数据库（sql server，mysql ， access）教程推荐：oracle使用教程，深入浅出oracle记住：欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要成为一个oracle高手过程：理解小知识点->做小练习->把小的只是点连成线->做oracle项目->形成只是面->深刻理解Oracle基础部分：oracle基础使用； oracle用户管理； oracle表管理Oracle高级部分：oracle表的查询； oracle的函数； oracle数据库管理；oracle 的权角色； pl/sql 编程；索引，约束和事物。

期望目标：1 学会安装、启动、卸载oracle2 使用sql *plus工具3 掌握oracle用户管理4 学会在oracle中编写简单的select语句第1讲：基础语法内容介绍：1.为什么学习oracle2.介绍oracle及其公司的背景3.学会安装、启动、卸载oracle4.oracle开发工具5.Sql*plus的常用命令6.oracle用户管理一、主流数据库包括：●微软：sql server 和 access●瑞典：mysql AB公司●IBM公司：DB2●美国sybase公司：sybase●IBM公司：infromix●美国oracle公司：oracle(目前最流行的之一)二、oracle安装，启动及卸载1.系统要求：操作系统最好为windows2000内存最好在256M以上硬盘空间需要2G以上2.oracle安装会自动的生成sys和system两个用户说明：○1Sys用户是超级用户，具有最高权限，具有sysdba角色，create database 的权限，默认密码是manager○2System 用户是管理操作员，权限也很大，具有sysoper角色，没有create database权限，默认密码是 change_on_install○3一般讲，对数据库维护，使用system用户登录就可以了3.启动oracle右键单击我的电脑->服务和应用程序：服务->启动OracleServiceMYORA1(MYORA1是安装oracle时起的名字各有不同)和OracleOracleHome90TNSLlistener4.卸载oracle1、先关掉oralce，net stop OracleServiceORCL(ORCL是我的实例名字，换成你的)，或者去我的电脑服务中关闭2、开始－＞程序－＞Oracle - oracle的版本号，我的是10ghome－＞Oracle Installation Products－＞Universal Installer 卸载oracle3、进注册表，regedit，删除选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下所有的key。

Oracle数据库学习笔记Oracle数据库基础 orcale属于关系型数据库，适⽤于各类⼤，中，⼩，微机环境，是⼀种⾼效率、可靠性好的、适应⾼吞吐量的数据库⽅案。

学习，实验完全免费，商⽤需要⽀付相应费⽤。

Oracle 数据库包括数据库实例，和数据库，⼆者脱离谁都没有存在的价值。

实例是⽤来操作数据库的对象，数据库是⽤来存储数据使⽤的。

Oracle主要组件包含实例组件，数据库组件。

SGA（System Global Area）是Oracle Instance的基本组成部分，PGA（Process Global Area）是为每个连接到Oracle database的⽤户进程保留的内存。

每个实例只有⼀个SGA，所有的进程都能访SGA。

PGA是程序全局区，每个⼀个进程都⼀个PGA，PGA是私有的，只有对应进程才能访问对应的PGA。

数据库中包含：参数⽂件，⼝令⽂件，数据库⽂件，控制⽂件，⽇志⽂件以及归档⽇志⽂件。

Oracle实例进场包含⽤户进程，服务器进程和后台进程。

SGA：系统全局区 系统全局区包含共享池，数据缓冲区，⽇志缓冲区。

“共享池”：是对SQL，PL/SQL程序进⾏语法分析，编译，执⾏的内存区；由库缓存和数据字典缓存组成；其⼤⼩直接影响数据库性能。

“数据缓冲区”：临时存储从数据库读⼊的数据，所有⽤户共享，数据缓存区的⽬的是加快数据读写。

“⽇志缓冲区”：⽇志记录数据库所有修改信息，其先产⽣于⽇志缓冲区，当达到⼀定数量时，由后台进程将⽇志数据写到⽇志⽂件中。

PGA：程序全局区 PGA包含单个服务器进程所需要的数据和控制信息，在⽤户进程连接到数据库并创建⼀个会话时⾃动分配的，保存每个与数据库连接的⽤户进程所需要的信息。

PGA为⾮共享区，只能单个进程使⽤，当⼀个⽤户会话结束，PGA释放。

后台进程 后台进程中包含PMON（进程监视器(Process Monitor)），SMON（系统监视器(System Monitor)），DBWR（数据库书写器(Database Write)），LGWR（⽇志书写器(Log Write)），CKPT（检查点(Checkpoint)），以及其他。

学习oracle笔记一、临时表空间 (3)1.目标 (3)2.临时表空间的作用 (3)3.临时表空间组 (3)4.临时表空间的操作 (3)二、UNDO表空间（undo撤销，redo重做） (5)1.目标 (5)2.UNDO管理方式的改变 (5)3.Undo表空间概念 (5)4.Undo相关的重要的参数 (6)5.Undo表空间的操作 (7)6.Oracle11G undo表空间的新特性 (8)三、逻辑备份与恢复 (8)1.目标 (8)2.备份与恢复简介 (8)3.故障类型 (9)4.传统的导出与导入实用程序 (9)5.导出 (11)6.导入 (12)7.导出导入三种方式 (14)8.可传输表空间 (14)9.oracle11G的数据泵 (15)10.Expdp重要的参数 (15)11.inmdp的重要参数 (19)四、数据装载 (20)1.目标 (20)2.数据的装载 (20)3.SQL*LOADER (21)4.外部表 (23)五、闪回flashback (25)1.目标 (25)2.9I的闪回查询 (25)3.10G中的闪回版本查询 (26)4.10G的闪回事务查询 (27)5.10G的闪回表 (27)6.闪回删除 (28)7.10G的闪回数据库 (29)六、物化视图 (30)1.目标 (30)2.问题的提出 (30)3.物化视图的简介 (31)4.物化视图的作用 (32)5.创建物化视图时需要的权限 (33)6.创建物化视图时的选项 (33)7.基于主键的物化视图 (34)8.基于rowid的物化视图 (36)七、使用物化视图和exp实现生产库的逻辑备份的例子 (37)1.问题的提出 (37)2.问题的解决 (37)一、临时表空间1.目标2.临时表空间的作用临时表空间在硬盘上3.临时表空间组4.临时表空间的操作查看表空间：Select * from v$tablespace;Select * from dba_tablespaces;查看数据文件：Select * from dba_data_files;查看临时数据文件：Select * from dba_temp_files;Select * from v$tempfile;查看默认的临时表空间：Select * from database_propertieswhere property_name=’DEFAULT_TEMP_TABLESPACE’;创建临时表空间,不属于组：Create temporary tablespace temp2 tempfile’F:\data\orcl\tem2a.dbf’ size 10M autoextend on;创建临时表空间,属于组：Create temporary tablespace temp3tempfile’F:\data\orcl\tem3a.dbf’size 10M autoextend ontablespace group temp_grp;查看临时表空间组：Select * from dba_tablespace_groups;把temp2加入到temp_grp组内：Alter tablespace temp2 tablespace group temp_grp;把temp2移出temp_grp组：Alter tablespace temp2 tablespace group ’’;给temp2表空间添加一个临时文件：Alter tablespacetemp2 addtempfile’F:\data\orcl\tem2b.dbf’size 10m autoextend on;修改系统默认的临时表空间为另一个临时表空间：Alter database default temporary tablespace temp2;修改系统默认的临时表空间为一个临时表空间组：Alter database default temporary tablespacetemp_grp;二、UNDO表空间（undo撤销，redo重做）1.目标2.UNDO管理方式的改变3.Undo表空间概念4.Undo相关的重要的参数查看undo相关信息：Show parameter undo;5.Undo表空间的操作增加一个undo表空间：Create undo tablespace undotbs2 datafile’F:\DATA\ORCL\undotbs201.dbf’ size 10m autoextend on;给undotbs2表空间增加一个undo数据文件：Alter tablespace undotbs2 add datafile’F:\DATA\ORCL\undotbs202.dbf’ size 10m;查看系统默认undo表空间：Show parameter undo;切换undo表空间：Alter system set undo_tablespace=undotbs2;启用rententiongarentee:Alter tablespace undotbs1 retention guarantee;查看表空间是否启用了rententiongarentee：Select * from dba_tablespaces;取消启用rententiongarentee:Alter tablespace undotbs1 retention no guarantee;查看undo表空间使用情况：Select * from v$undostat;Select to_char(begin_time,’yyyymmdd hh24:mi:ss’),to_char(end_time,’yyyymmdd hh24:mi:ss’),undoblks,txncount from v$undostat;6.Oracle11G undo表空间的新特性三、逻辑备份与恢复1.目标2.备份与恢复简介3.故障类型语句故障：不需要人工处理。

备注：以下是个人学习笔记总结，其实是本人容易忘记，工作中碰到的一些知识点，记录下来整理成笔记了，序号之间并不是按照某种类型划分的，都是随意的标记一下，希望对大家有所帮助。

昵称：阿杜笑傲江湖（其实就是个名字而已，并不江湖…）name：杜立鸿（不要冒充，万一哪天中奖了呢？）sex:爷们---------------------------废话不多说，GO，GO，Go……1.允许修改分区建（有时候分区键更新不了，需要以下这样处理，当然了分区键本来是不允许更新的，都得根据实际情况）alter table t1 enable row movement；2. 获取某一时间最近的时间sqlselect *from t_datetime twhere t.f_time - to_date('2018-09-06 10:10:00','yyyy-mm-ddhh24:mi:ss') =(select min(t.f_time - to_date('2018-09-06 10:10:00','yyyy-mm-ddhh24:mi:ss'))from t_datetime t);3. 1.创建一个object类型的数据库类型对象。

表示学生实体类型。

（注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。

否则，函数外部代码是无法识别该类型的）。

CREATE OR REPLACE TYPE student_obj_type AS OBJECT(stu_no NUMBER, --学号stu_name VARCHAR2(255), --姓名stu_sex VARCHAR2(2),--性别score NUMBER--成绩);4.创建一个嵌套表类型的数据库类型对象。

表示学生实体集合类型。

该类型也将用作函数中定义的返回类型。

（注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。

Oracle学习笔记（⼗）分区索引失效的思考此处只说索引失效的场景（只会影响全局索引）：结论：全局索引truncate 分区和交换分区都会导致索引失效果局部索引truncate分区不会导致索引失效。

drop table part_tab_trunc purge;create table part_tab_trunc (id int,col2 int,col3 int,contents varchar2(4000))partition by range (id)(partition p1 values less than (10000),partition p2 values less than (20000),partition p3 values less than (maxvalue));insert into part_tab_trunc select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=50000;commit;create index idx_part_trunc_col2 on part_tab_trunc(col2) local;create index idx_part_trunc_col3 on part_tab_trunc(col3) ;---分区truncate前select index_name, partition_name, statusfrom user_ind_partitionswhere index_name = 'IDX_PART_TRUNC_COL2';INDEX_NAME PARTITION_NAME STATUS------------------------------ ------------------------------ --------IDX_PART_TRUNC_COL2 P1 USABLEIDX_PART_TRUNC_COL2 P2 USABLEIDX_PART_TRUNC_COL2 P3 USABLEselect index_name, statusfrom user_indexeswhere index_name = 'IDX_PART_TRUNC_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_TRUNC_COL3 VALIDalter table part_tab_trunc truncate partition p1 ;---分区truncate后select index_name, partition_name, statusfrom user_ind_partitionswhere index_name = 'IDX_PART_TRUNC_COL2';INDEX_NAME PARTITION_NAME STATUS------------------------------ ------------------------------ --------IDX_PART_TRUNC_COL2 P1 USABLEIDX_PART_TRUNC_COL2 P2 USABLEIDX_PART_TRUNC_COL2 P3 USABLEselect index_name, statusfrom user_indexeswhere index_name = 'IDX_PART_TRUNC_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_TRUNC_COL3 UNUSABLE此处只说索引失效的场景（也是只影响全局索引）：--试验1（未加Update GLOBAL indexes关键字）drop table part_tab_drop purge;create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))partition by range (id)(partition p1 values less than (10000),partition p2 values less than (20000),partition p3 values less than (maxvalue));insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;commit;create index idx_part_drop_col2 on part_tab_drop(col2) local;create index idx_part_drop_col3 on part_tab_drop(col3) ;--未drop分区之前select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_DROP_COL3 VALIDalter table part_tab_drop drop partition p1 ;--已drop分区之后select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_DROP_COL3 UNUSABLE--试验2（加Update GLOBAL indexes关键字）drop table part_tab_drop purge;create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))partition by range (id)(partition p1 values less than (10000),partition p2 values less than (20000),partition p3 values less than (maxvalue));insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;commit;create index idx_part_drop_col2 on part_tab_drop(col2) local;create index idx_part_drop_col3 on part_tab_drop(col3) ;--未drop分区之前INDEX_NAME STATUS------------------------------ --------IDX_PART_DROP_COL3 VALIDalter table part_tab_drop drop partition p1 Update GLOBAL indexes;--已drop分区之后select index_name,status from user_indexes where index_name='IDX_PART_DROP_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_DROP_COL3 VALID--此处只说索引失效的场景：--分区表SPLIT的时候，如果MAX区中已经有记录了，这个时候SPLIT就会导致有记录的新增分区的局部索引失效! drop table part_tab_split purge;create table part_tab_split (id int,col2 int ,col3 int ,contents varchar2(4000))partition by range (id)(partition p1 values less than (10000),partition p2 values less than (20000),partition p_max values less than (maxvalue));insert into part_tab_split select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=90000;commit;create index idx_part_split_col2 on part_tab_split (col2) local;create index idx_part_split_col3 on part_tab_split (col3) ;---分区split前select index_name, partition_name, statusfrom user_ind_partitionswhere index_name = 'IDX_PART_SPLIT_COL2';INDEX_NAME PARTITION_NAME STATUS------------------------------ ------------------------------ -------IDX_PART_SPLIT_COL2 P1 USABLEIDX_PART_SPLIT_COL2 P2 USABLEIDX_PART_SPLIT_COL2 P_MAX USABLEselect index_name, statusfrom user_indexeswhere index_name = 'IDX_PART_SPLIT_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_SPLIT_COL3 VALIDalter table part_tab_split SPLIT PARTITION P_MAX at (30000) into (PARTITION p3,PARTITION P_MAX);alter table part_tab_split SPLIT PARTITION P_MAX at (40000) into (PARTITION p4,PARTITION P_MAX);alter table part_tab_split SPLIT PARTITION P_MAX at (50000) into (PARTITION p5,PARTITION P_MAX);alter table part_tab_split SPLIT PARTITION P_MAX at (60000) into (PARTITION p6,PARTITION P_MAX);alter table part_tab_split SPLIT PARTITION P_MAX at (70000) into (PARTITION p7,PARTITION P_MAX);---分区split后select index_name, partition_name, statusfrom user_ind_partitionswhere index_name = 'IDX_PART_SPLIT_COL2';INDEX_NAME PARTITION_NAME STATUS------------------------------ ------------------------------ --------IDX_PART_SPLIT_COL2 P1 USABLEIDX_PART_SPLIT_COL2 P2 USABLEIDX_PART_SPLIT_COL2 P3 UNUSABLEIDX_PART_SPLIT_COL2 P4 UNUSABLEIDX_PART_SPLIT_COL2 P5 UNUSABLEIDX_PART_SPLIT_COL2 P6 UNUSABLEIDX_PART_SPLIT_COL2 P7 UNUSABLEIDX_PART_SPLIT_COL2 P_MAX UNUSABLEselect index_name, statusfrom user_indexeswhere index_name = 'IDX_PART_SPLIT_COL3';INDEX_NAME STATUS------------------------------ --------IDX_PART_SPLIT_COL3 UNUSABLE--结论是:split会导致全局索引失效，也会导致局部索引失效。

Oracle数据库学习笔记_oracle之addconstraint⽅法添加约束add constraint ⽅法在已经存在的列名添加约束，语法格式如下：alter table 表名 add constraint 约束名称约束类型(列名)具体⽤法如下：1.主键约束：列被约束为（⾮空、不重复）格式：alter table 表格名称 add constraint 约束名称 primary key （列名）例⼦：alter table emp add constraint ppp primary key (id);2.外键约束：列被约束为引⽤其他表的主键格式：alter table 表名 add constraint 约束名称 foreign key (列名) references 被引⽤的表名称（列名）例⼦：alter table emp add constraint jfkdsj foreign key (did) references dept (id);3.unique约束：列被约束为（不重复）格式：alter table 表名 add constraint 约束名称 unique（列名）例⼦：alter table emp add constraint qwe unique(ename);4.默认约束：让此列的数据默认为⼀定的数据格式：alter table 表名称 add constraint 约束名称 default 默认值 for 列名例⼦:alter table emp add constraint jfsd default 10000 for gongzi;5.check约束：列的数据范围被限制格式：alter table 表名称 add constraint 约束名称 check （列名）例⼦：alter table emp add constraint abcd check(age>20); --例如，年龄列的数据都要⼤于20的。

Oracle学习笔记：使⽤replace、regexp_replace实现字符替换、姓名脱敏 在数据库中难免会遇到需要对数据进⾏脱敏的操作，⽆论是姓名，还是⾝份证号。

最近遇到⼀个需求，需要对姓名进⾏脱敏：姓名长度为2，替换为姓+*；姓名长度为3，替换中间字符为*；姓名长度为4，替换第3个字符为*； 经过⼀番搜索之后，最终找到了3种⽅式的实现，具体如下。

⼀、先查找，再替换select replace('陈宏宏',substr('陈宏宏',2,1),'*') as name from dual;注意：此种⽅法通过对第2个字符进⾏替换，如果名字为叠名，则会发⽣上述误替换情况；⼆、拼接select substr('陈宏宏',1,1)||'*'||substr('陈宏宏',3,1) as name from dual;三、使⽤regexp_replace进⾏精准替换select regexp_replace('陈宏宏','(.)','*',2,1) as name from dual;注意：regexp_replace⽀持使⽤正则表达式对字符串进⾏替换，该语句解释为从第2个字符开始，取任意1个字符，替换为*；四、完整的替换代码create table temp_cwh_002 asselect a.acc_nbr,a.act_city,a.city_name,a.number1,a.number2,case when length(a.cust_name) =2then regexp_replace(cust_name,'(.)','*',2,1)when length(a.cust_name) =3then regexp_replace(cust_name,'(.)','*',2,1)when length(a.cust_name) =4then regexp_replace(cust_name,'(.)','*',3,1)else cust_name end cust_name,a.acc_nbr2,a.param_valuefrom temp_cwh_001 awhere length(a.cust_name) <=4END 2019-01-02 16:44:13。

Oracle学习笔记：判断表是否存在函数is_table_exists在 Oracle 中可以利⽤系统表 user_tables 和 all_talbes 判断表是否存在，但有时在存储过程中确认表是否存在并不⽅便，因此有必要封装⼀个函数，进⾏调⽤。

下⾯是函数的内容：-- 判断表是否存在create or replace function temp_is_table_exists(is_table_name varchar2, is_owner_name varchar2 default null)return boolean isvcproc_name varchar2(100) := 'TEMP_IS_TABLE_EXISTS';vncount number(10);vnerr_code number;vcerr_text varchar2(2000);vcowner_name varchar2(1000);vctable_name varchar2(1000);beginvncount := 0;vcowner_name := is_owner_name;vctable_name := is_table_name;if vcowner_name is null thenselect count(1) into vncountfrom user_tableswhere table_name = upper(vctable_name);elseselect count(1) into vncountfrom all_tableswhere owner = upper(vcowner_name)and table_name = upper(vctable_name);end if;if vncount > 0 thenreturn true;elsereturn false;end if;exceptionwhen others thenvnerr_code := sqlcode;vcerr_text := sqlerrm;-- 记录异常以备查pro_cwh_test(vcproc_name, vctable_name, vnerr_code, vcerr_text);rollback;commit;end temp_is_table_exists;其中，⼊参为：表名 + ⽤户名，⽤户名可缺省。

Oracle学习笔记：字段like多个条件（or关系）regexp_like的使⽤在Oracle中，有时候需要写like多条件的时候，总觉得很冗余，特别繁琐，例如：select * from table_testwhere col like '%abc%'or col like '%bcd%'or col like '%cde%'or col like '%ghi%';遂考虑，有没有简洁的解决⽅案。

最后找到 regexp_like 尝试⼀番。

-- 创建测试表create table temp_cwh_test(id varchar2(20),name varchar2(30));-- 插⼊数据insert into temp_cwh_test values (1,'黄盖');insert into temp_cwh_test values(2,'吕布');insert into temp_cwh_test values(3,'貂蝉');insert into temp_cwh_test values(4,'鲁智深');insert into temp_cwh_test values(5,'诸葛亮');-- 查询select * from temp_cwh_test;缩写为：select * from temp_cwh_testwhere name like '%布%'or name like '%亮%'or name like '%貂%'select * from temp_cwh_testwhere regexp_like(name, '(貂|布|亮)')如果要匹配以字符串开头，可以：select * from temp_cwh_testwhere regexp_like(name, '^(貂|布|亮)')-- 1 3 貂蝉如果要匹配以字符串结尾，可以：select * from temp_cwh_testwhere regexp_like(name, '(貂|布|亮)$')-- 1 2 吕布-- 2 5 诸葛亮总结全模糊匹配：regexp_like(字段名, '(匹配字符串1|匹配字符串2|匹配字符串3|...)')左模糊匹配：regexp_like(字段名, '(匹配字符串1|匹配字符串2|匹配字符串3|...)$')右模糊匹配：regexp_like(字段名, '^(匹配字符串1|匹配字符串2|匹配字符串3|...)')额外报错记录在插⼊数据的时候 Oracle 报了⼀个错：Oracle ORA-00984: column not allowed here ，⼀开始以为是建表的时候类型选得不对，int、varchar2(20) 改了⼜改，还是不⾏。

Oracle学习笔记系列（⼆）之数据库⽇期格式转换Oracle数据库⽇期格式转换select sysdate from dual;select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as mydate from dual;select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mydate from dual;select to_number(to_char(sysdate,'yyyymmddhh24miss')) as mydate from dual;转换函数与date操作关系最⼤的就是两个转换函数：to_date(),to_char() to_date()：作⽤将字符类型按⼀定格式转化为⽇期类型。

具体⽤法:to_date('2004-11-27','yyyy-mm-dd'),前者为字符串，后者为转换⽇期格式。

【注意，前后两者要以⼀对应】如;to_date('2004-11-27 13:34:43', 'yyyy-mm-dd hh24:mi:ss')多种⽇期格式：YYYY：四位表⽰的年份 YYY，YY，Y：年份的最后三位、两位或⼀位，缺省为当前世纪 MM：01~12的⽉份编号 MONTH：九个字符表⽰的⽉份，右边⽤空格填补 MON：三位字符的⽉份缩写 WW：⼀年中的星期 D 的第⼏个⼩时，取值为00~23 MI：⼀⼩时中的分钟 SS：⼀分钟中的秒 SSSS：从午夜开始过去的秒数to_char()：将⽇期转按⼀定格式换成字符类型即把当前时间按yyyy-mm-dd hh24:mi:ss格式转换成字符类型在oracle中处理⽇期⼤全 TO_DATE格式 Day:dd number 12dy abbreviated friday spelled out fridayddspth sp 12⼩时格式下时间范围为： 1:00:00 - 12:59:59 ....⽇期和时间函数汇总1.⽇期和字符转换函数⽤法（to_date,to_char）select to_char(to_date(222,'J'),'Jsp') from dual; --Two Hundred Twenty-Two2.求某天是星期⼏select to_char(to_date('2018-01-09','yyyy-mm-dd'),'day') from dual; --星期⼆select to_char(to_date('2018-01-09','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; --tuesday设置⽇期语⾔ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';也可以这样 TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')3.两⽇期间的天数select floor(sysdate - to_date('19921123','yyyymmdd')) from dual; --91794. 时间为null的⽤法select p.claimno, p.endcasedate from prplclaim pUNIONselect '1', TO_DATE(null) from dual;注意要⽤TO_DATE(null)5.取⽇期范围a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')这样12⽉31号中午12点之后和12⽉1号的12点之前是不包含在这个范围之内的。

Oracle数据库→表空间→用户→表表空间：包含表、视图、索引段：包含数据段、索引段、回退段、临时段数据块：是Oracle中最小的逻辑存储单元创建表空间：create tablespace rootspacedatafile 'rootfile' size 1000mautoextend on创建用户：create user root用户名rootidentified by root 密码rootdefault tablespace rootspace 默认表空间rootspacetemporary tablespace temp 临时表空间tempCREATE USER usernameIDENTIFIED BY password[DEFAULT TABLESPACE tablespace][TEMPORARY TABLESPACE tablespace];授予用户username【用户名】权限：·grant connect to username; CONNECT角色允许用户连接至数据库,并创建数据库对象·grant resource to username; RESOURCE角色允许用户使用数据库中的存储空间·grant create sequence to username; 此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中·grant select on test to username; 允许用户查询 TEST 表的记录·grant update on test to username; 允许用户更新 TEST 表中的记录·grant all on test to username; 允许用户插入、删除、更新和查询TEST 表中的记录·alter user username identified by newpassword;用于修改用户口令·drop user username cascade; 删除用户撤销用户权限：Revoke connect from username;设置显示行长度：Set linesize 12;伪列rownum:Select rownum,name from table;………………………………>Rownum name1 haha2 xxxx--创建表tb_stucreate table tb_stu(stu_id char(12) primary key,stu_name varchar(50) not null,sex varchar(5),birthday date)--查询表tb_stuselect * from tb_stu;--在表中插入tb_stu记录insert into tb_stu(stu_id,stu_name,sex,birthday) values('123456789012','李四','男',to_date('2009-9-9','yyyy-mm-dd'));--事务提交commit;--事务回滚rollback;--根据stu_name查询所有信息select * from tb_stu where stu_name='田七'--根据stu_id删除一条记录delete from tb_stu where stu_id=123456789015--根据stu_id修改一条记录update tb_stu set sex='女',stu_name='梁朝伟'where stu_id='123456789013';--to_date 修改存入数据库中日期的格式update tb_stu set birthday=to_date('2009-10-5','yyyy-mm-dd') wherestu_id='123456789014'--to_date 查询数据库中日期按指定格式输出select* from tb_stu where birthday between(to_date('2009-10-1','yyyy-mm-dd')) and (to_date('2009-12-1','yyyy-mm-dd'))--虚列 rownum 数据库中实际并不存在对符合条件的查询结果的编号select rownum,stu_id,stu_name,sex,birthday from tb_stu where sex='男';--在查询结果中进行查询select * from (select rownum rn,stu_id,stu_name,sex,birthday from tb_stu where sex='男') where rn<3;--创建表tb_employeecreate table tb_employee(em_id number primary key,em_name varchar(50) not null,sex varchar(2),birthday date,sal number(20,2))--向表tb_employee中插入数据insert into tb_employee values(1,'梁朝伟','男',sysdate,11000000);--按字段升序排列(默认的为升序)select * from tb_employee where sal>200order by sal asc--按字段升序排列select * from tb_employee where sal>200order by sal desc--取别名：将查询的字段按一个特定的字段名输出select em_name,((sal-2000)*0.2) 税收from tb_employee where sal>2000;--联合字段，将查询出的多个字段或者是字符串连接在一起，以一个字段输出，用“||”连接select em_name||'的应该缴税: '||((sal-2000)*0.2) as税收from tb_employee where sal>2000order by税收desc;--将em_name为“梁朝伟”的记录的birthday字段，按指定的日期格式进行修改update tb_employee set birthday=to_date('1969-1-1','yyyy-dd-mm') where em_name='梁朝伟';--查询birthday字段不为当前系统时间并且不为空的值--不等于的三种书写方式（!=,^=,<>）select * from tb_employee whereto_char(birthday,'yyyy')^=to_char(sysdate,'yyyy');-- or 连接的多条件“或”查询select * from tb_employee where birthday is null or em_id=1;--between 3 and 5 查询条件为：大于等于3同时小于等于5select * from tb_employee where em_id not between3and5;--查询条件为：大于其中任意一个（只要大于其中的某一个就为满足条件）select * from tb_employee where em_id > any(1,3,5);--查询条件为：小于其中所有的（只有比括号中所有的数字都小才为满足条件）select * from tb_employee where em_id < all(3,5);--下划线表示任意的以个字符select * from tb_employee where em_name like'周__';-- % 表示任意多个字符select * from tb_employee where birthday like'%';--快速创建和tb_stu相同的表结构的表tb_stu_temp1create table tb_stu_temp1as select * from tb_stu where1=2;--将表tb_stu按条件查询的结果插入表tb_stu_temp1中insert into tb_stu_temp1(select * from tb_stu);--查询所有的表select * from tab ;--根据表名查询表select * from tab where tname='tb_stu';如果2个表达式主键管理的：主表——子表先插入主表的数据，然后子表删除：先删从表数据，然后主表。