数据库oracle 11g钱一慎版

笔记
1.数据字典 是数据库系统自动生成的一些表和视图，存放表空间、 用户、 权限和建表的一些信息
2.用户完整性，即字段可以通过check设置限制范围
3.数据库由多个数据文件组成，表空间是多个物理存储的数据文件的逻辑统一
4.Oracle中的文件类型：数据文件、控制文件（.ctl）、日志文件（重做日志文件，出差可回滚）、参数文件和备份文件等构成数据库物理载体
5.数据库一般有三个默认表空间，SYSTEM、SYSAUX、TEMP；表空间类型：系统表空间、临时表空间、永久表空间、撤销表空间（平时做数据撤销回滚就用到此表空间）
6.表空间可设置成只读状态，保证大量静态表数据不被更改
7.结果集缓存的使用？？http://blog.itpub.net/26015009/viewspace-765600/
8.SID和全局数据库域名，数据库名区别 https://blog.csdn.net/yes_is_ok/article/details/80016522
sid是数据库与本地操作系统进行通信的系统标识，若一个系统装了多个oracle 数据库 则有多个sid，全局数据库名是在分布式架构中，用库名+结点名表示全系统中唯一的数据库
9.安装数据库后若出现无法连接，则考虑防火墙的设置
10.DDL:create、alter、drop，DML:insert、update、delete，DCL：grant、commit、rollback、revoke
11.对权限的最佳讲解(见书中权限的讲解章）
12.SQL关键字、字段、对象名的不区分大小写，但sql中的字符值或者日期等，表中的值是区分大小写的
13.权限：系统权限（一共有206种，见章节）和实体权限 ，授权方式：直接授权；先授权给角色，再将角色授予用户 ，如果是间接授权的话，通过user_sys_privs仍然查不出用户的权限，因为用户本身不拥有权限
14.connect 角色拥有的只有创建会话权限（包括查看表），而RESOURCE角色拥有以下权限，其中create也包含更改和删除的权限
15.一个用户想操作或查看别的方案对象，表名前加方案名
16.对象与对象权限的关系，和对象授权的格式 grant select on tablename to username
15.系统权限create table 给了用户后，如果revoke的话，只能撤销建表权限，而增删改数据仍可以
16.unlimited tablespace 这个权限只能给用户，表示用户可以在任意表空间建表，一般dba会回收这个权限，限制用户在某个表空间用以下语句
alter user zsx quota unlimited on sofa；
.删除数据库用户时，一要保证用户退出连接，二如果用户有对象，则drop user zsx cascade ，否则无法删除
17.rowid详解 https://blog.csdn.net/wxwpxh/article/details/50532464
18.表空间由一个或多个dbf文件组成
19.方案（schema）是数据对象的集合，与用户一一对应，用户建立之后，一旦用户有了数据对象，则自动会拥有方案 https://rsy.iteye.com/blog/2148619
20.oracle模糊查询 用like 通配符 %和_
21.NOT的用法，where not name like ‘%张%’ 或者 where not 1=1
22.group by 分类查询需要注意：select后只能是分组列名（或）和聚合函数，其他列名不能添加；where子句会优先满足，然后再对结果进行分类，而having则是先分类，再对结果进行筛选；
23，group by 可以组内再分组 group by column1，column2
24.简单连接产生笛卡尔积，查询的结果就是两个基表行数乘积，通过where条件避免笛卡尔积冗余
24.如果指定了别名，则所有子句中只能使用别名，真实表名将被覆盖
25.子句的执行顺序，先from，再where，最后在select，所以，当from中指定别名时，真实表名将被覆盖
26.join连接分为（1）外连接会显示满足where子句条件的行，而不管另一张表是否有匹配值
27.not in后面有null值出现则不会返回值，原因是，not in相当于not(表达式) ，null表示未知，任何值与null比较结果都未知，未知情况下not不返回值，但是in(表达式)有null不受影响 https://blog.csdn.net/longcccvv/article/details/54376015
28.in和exists区别：in会返回行，而exists只会判断是否有返回行，如果有则返回true，否则返回false，exists外层每一行from的数据都要由子查询进行评估，当为true则返回这一行，否则不返回
29.truncate用于删除表中所有数据，由于不会产生回滚信息，所以要比delete快，但是也不能撤销，delete之后可以使用rollback恢复，但是truncate不能
30.verchar2()可以存储null值，nverchar2（20）中20表示字符数，即中英文都存20
31.日期型date类型存储精度为年月日时分秒，而timestamp可以显示年月日时分秒到纳秒
32.关于基本数据类型的讲解 https://www.cnblogs.com/HDK2016/p/7071708.html
33.使用pl/sql 进行数据库DML时，要显式的写commit，否则不会执行到表上
34.显式游标，需要声明，打开和关闭，隐式不需要，隐式只能接受一行
35.游标是pl/sql中在执行crud时分配的缓存区中的指针，关闭游标即关闭了该指针和缓存区
oracle的基本 操作
–
1.例程是逻辑内存结构和一系列的oracle后台进程
2.oracle启动的三个步骤，启动例程，此时并没有与特定数据库连接，读取参数文件，startup nomount；装载数据库，此时，将打开数据库控制文件，在控制文件中找数据文件和重做日志文件的位置 startup mount；打开数据库（此时用户才能链接会话），start open如果某个数据文件或者重做日志文件没有找到，则需要数据恢复
3.启动时调用的文件顺序：参数文件 ----》控制文件-----》数据文件和日志文件
4.三种启动过程，一般并不是直接完成的，dba根据管理的需要，可能会在某个过程停留做操作
5.快速登录到数据库，在sqlplus.exe 文件快捷方式放到桌面后，点击其属性，在目标栏后面添加 sofa/sofa这样的用户名密码
6.运行sql命令 三种方式，命令行，sql缓冲区方式、命令文件方式
7.sql缓冲区,记录了sqlplus中最近一次执行的命令，ed可以直接打开查看，类似于Linux中的shell脚本，run直接运行一次缓冲区命令并输出结果
8.show命令，可以查看SGA（dba），show error，也可以查看当前用户show user
9.autocommit命令，默认关闭，set ~ on 打开的情况下 在sqlplus中执行dml自动提交
10.跟oracle启动相关的服务有 oracle ora db11g_homeListener ，应该先启动监听；OracleServiceSID(ORACL),数据库例程服务，oraceldbconsoleSID（orcl）OEM服务
11.关闭数据库也有三种模式，shutdown 该模式关闭时，数据库会等待所有用户主动退出之后再关闭，shutdown transactional 阻止所有新事务和新会话，等当前的事务提交后，马上关闭数据库；shutdown immediate，立刻关闭（一般断电等才会用）
12.表名必须以字母开头，<30,字下美英数等字符
13.不同方案中的数据对象名称可以相同
14.列约束中的unique设置之后，不允许重复的值，但是如果列未设置not null，则可以多个null重复
15.ROWID是一个伪列，当数据存储到物理磁盘上时，伪列会唯一的记录一行数据的物理地址，当表的物理位置发生变化时，每一行的ROWid也会因为物理位置变化而变化 https://www.cnblogs.com/xqzt/p/4449184.html
16.OEM中也可以进行表的DML，并且可以生成sql，同时对数据库性能进行监督
17.如果要删除一个大表中的一列，数据库会执行的非常慢，高峰时期占据过多资源，且删除会锁表，可以使用UNUSED alter table demovo set unused（columnName），这样使得整个列不可见，甚至可以重新添加名字相同的列，等到空闲期再删除；物理上并没有删除列
18.索引的原理：oracle中的索引将创建jie列的键值和对应的物理记录号rowID排序存储，，将索引直接加载到内存，对物理位置进行检索，提高检索效率，表中记录越多，索引效果越明显，索引对象存储在索引段上，独立于表的物理位置
19.一般而言，DML/DDL等操作都可以通过两种方式，第一,sql语句，第二OEM来完成
20.索引的分类，逻辑分类：unique and nonunique，物理分类：bitmap和b-tree（包括normal和反转索引 ）；b-tree跟节点和子节点记录数据位置，叶子结点记录索引入口即rowid等信息； https://www.jb51.net/article/50703.htm
21.视图是一种虚表，不占用物理空间，只用将其定义的信息保存到数据字典中即可，视图作用可以简化查询，将复杂的查询语句创建成视图之后，再次查询视图就只是简单查询了
22.视图的更改，即重新创建并代替之前的视图 用create or replace view
23.oracle中的插入 只能用values而不能使用value
24.对视图做DML操作时，第一，基表必须是单表查询，第二，执行的列，必须是视图中子查询所包括的，不能对视图中没有的列进行操作
25.pl/sql中的事务只能在其语句中commit，而不能在外部执行commit，后者不起作用

数据库管理操作
1.视图可以让用户把对表的复杂查询变为对视图的简单查询
2.数据完整性，域完整性指字段值域，类型等；实体完整性指主键约束，即表中记录的唯一性；参照完整性，指每一个外键值对应其他表的主键值，必须能找到。
3.域完整性，通过not null，unique和check实现
4.引用完整性（参照完整性）设置外键时中cascade级联，当主表中被引用数据删除，对应外键数据也删除，set null则是当被引用数据被删除，外键数据为null，no action 即有外键关系时，被引用数据如果仍被引用，则不能删除，默认的外键引用类型
5.同义词 相当于给对象取别名，只是这个别名在以后基表名未改动的情况下都可以使用，create （public）synonym dv ，注意，一，要授权；二，不加public的话，这个同义词默认只能当前用户使用；同义词在分布式数据库中用处很大，可以将一个地址+表名的对象名用同义词代替。
6.表空间分system表空间和其他新建表空间 ，system表空间存储数据字典等，其初始化大小在数据库建立时设定，一定要尽量大点；一般在创建表空间时都将其设置的较大比如实际需要的1.5-2倍，可以datafile后面跟多个数据文件，避免后期因为表空间大小不够带来的问题
7. 表空间不足时，一般采取两种方式，第一种是新建表空间，第二种是增加表空间的数据文件;第二种动态增加表空间的方式常用， alter tablespace sofa add file ‘E:/***.dbf’ size 300MB
8.行链接 当第一次插入的行占用的空间大于一个块的大小，oracle会使用多个块存储行，如行中含有long、blob等；行迁移，指修改行时导致的行存储空间超出块大小，则oracle会换大一些的块保存行数据，之前的块只保留之后块的id，其中其余空间被回收，即产生了碎片化，但是行rowid并没有改变，表性能会下降（先找块id，才能找到新块）
9.pctfree表示数据块保留空闲空间比例，当剩余空间小于该比例，则需要开辟新数据块去保存新增行，留出来的空间是用于数据的增加update，即便是随后又删除行，导致有了更多的空闲空间，仍然不能新增行，必须等剩余的空间比例小于pctused才能执行插入操作；在建表时，若pctfree设置太小，则当已经存在的一行有大量数据增加进来时，会是的留出来的空间不够，从而只能产生行迁移
10.pctused表示数据块中已经使用的空间比例低于多少时可以重新执行插入操作，主要是回收释放的空间 https://www.cnblogs.com/linjiqin/archive/2012/01/16/2323320.html
11.定期回收无用的表空间、数据文件（.dbf）和数据对象eg：表，视图等，释放空间
12.备份与恢复的方式：逻辑备份与恢复，用imp，exp，数据泵impdp、expdp和装载器；物理备份恢复，即在数据库关闭情况下对数据文件的备份和恢复；联机（不停机）备份
13.数据泵导出expdp 必须始终cmd窗口而不是在sqlplus下面执行否则会出这个错误sp2-0734:未知的命令开头’imp 忽略了剩余行;第二，给用户赋予权限：grant exp_full_database,imp_full_database to zsx，第三，命令中将directory放在用户密码后面，否则会报各种错误；具体步骤，https://www.cnblogs.com/oracle-dba/p/3344230.html
14.数据泵导入impdp impdp zsx/zsx directory=dir dumpfile=ZSX.DMP 注意在导入时 命令行结束不用加; 如果加了会被默认成文件名，imp和exp命令对应，impdp和expdp方法对应；https://blog.csdn.net/zengmingen/article/details/51657900
15.导入imp和impdp区别 https://www.cnblogs.com/zhengcheng/p/4201567.html
16.脱机备份 shutdown之后完全导出控制，数据，参数，重做日志等文件，查看文件位置 分别是v$controllerfile,db{a}_{d}at{a}_{f}iles,v$logfile等，最后如果继续使用则open命令；如果要恢复使用，则所有文件的位置应该跟原来一样
17.自动备份的闪回技术？？
18.控制文件：用于支持数据库正常启动和运行的二进制文件，数据库运行过程中控制文件需要一直处于可读写状态，在数据库启动的第一个阶段，加载例程阶段，在解读参数文件时，其中有控制文件的路径信息；控制文件在启动的第二个阶段，装载数据库阶段读取，其中有数据文件和日志文件的信息
19.控制文件的管理策略：多路复用控制文件，控制文件的路径信息都保存在参数control_files中，新添加一个路径信息到该参数，当原控制文件损坏，只需要将备份的控制文件放到新的路径下面，重新启动即可
20**.事务保持原子性的方法，**在写的数据上保留旧值，一旦事务没有完成，就恢复旧值
22.事务的五种状态活动状态：事务执行时的状态；部分提交状态：最后一句sql被执行完之后的状态，此时若发生故障，则进入失败状态；失败状态：无法正常执行的事务，该状态必须回滚到中止状态；中止状态：事务回滚，恢复到之前的状态； 提交状态：部分提交状态的事务将会往硬盘上写数据，最后一条信息被写到硬盘之后的状态即为提交状态。
23.事务的两种模式只读事务和读写事务：将事务设置为只读 set transaction read only，不建立回滚段，可以减少数据库冗余信息，适合只有查询的事务，另外，在只读事务中无法执行DML语句，如果要将事务重新设置为读写 set transaction read write，则必须将之前事务结束；
24.oracle中认定一个事务结束的方式，commit/rollback（显式提交）、执行DDL/DCL语句（隐式提交）和数据库关闭等情况，或者设置自动提交事务（set autocommit on）
25.已经commit的事务不能rollback
26.当执行DML命令如果没有提交，将不会被其他会话看到。eg：在一个sql窗口中执行DML之后，如果没有commit，在其他sql窗口仍看不到，当前窗口、当前程序关直接关闭都属于隐式提交
27.oracle用锁来解决并发问题，在并发时能够通过不同类型的锁使得不同事务能够同时访问或者操作统一资源，这些资源包括表、数据字典和内存等等，如果没有各种类型的锁，解决并发问题只剩下了串行操作，这样大大影响使用效率
25.事务执行前首先要获得对应资源的锁，例如表的锁和行的锁，如果无法获得锁那事务就无法执行，执行完之后，自动释放锁
26.锁的不同分类：TM锁：表级锁：包括了6种锁级别**；TX锁**：行级锁只有**排他锁（写锁）**这一种级别，即当一个事务DML一行数据时，会获得该行的锁，其他事务只能查看该行，不能同时DML该行，如果并发只能等待前一个事务释放锁（rollback或者commit），排他锁容易引起夯库，对生产产生危害，应该及时避免
27共享锁也叫读锁为表级锁，允许多个事务同时使用对象，且无法阻止其他事务修改表中的数据
28.视图v美元符号lock视图保存有关锁的信息，select * from vlock where type in (‘TM’,‘TX’); 其中SID是会话id，type表示的是锁类型，一般就看TM和TX,lmode为锁等级，Lmode只要不是0或1,就表明进程获得了锁，TX只有排他锁，block数字代表当前事务阻塞的其他事务的个数，一般两个或以上的会话同时DML同一行时，会发生锁竞争，
29表级锁的mode等级的说明，1。等级越高，影响的数据量越大；2.select from 是小于2的锁，DML是等于3的锁，创建索引等是3和4的锁；主外键约束有可能会产生4和5 的锁；DDL是6的锁；2/3、4锁不会阻止DML（只是阻塞），但是DDL会报错如下的错误
30.00054资源正忙, 但指定以 NOWAIT 方式获取资源，该错误即为当对表进行DDL时，由于表处于锁（共享行2，行排他3,或共享锁4之中，因此报错，需要解锁
31 解锁的方式，在加锁的事务汇总commit或者rollback
32.SELECT * from demovo for update （这是一种悲观锁）会使得查询变成排他锁（Lmode=3和6）的一种语句，执行会使得事务获得排他锁，其他事务无法再以排他锁的方式进行操作，，但是当执行select * from demovo for update nowait 时，会返回00054的错误
33 乐观锁和悲观锁， oracle默认乐观锁，在读数据时默认不上锁，在更新数据时再判断是否有其他事务改变过 https://blog.csdn.net/zdp072/article/details/25008873

.