读《Oracle从入门到精通》笔记--持续更新中

第一章 Oracle 11g概述 1.6节 启动与关闭数据库实例 启动数据库： Startup [nomount|mount|open|force][resetrict][pfile=filename] 解释：nomount --- 表示启动实例不加载数据库 mount --- 表示启动实例、加载数据库并保持数据库的关闭状态 open --- 表示启

第一章 Oracle 11g概述
1.6节 启动与关闭数据库实例
启动数据库：
Startup [nomount|mount|open|force][resetrict][pfile=filename]
解释：nomount ---> 表示启动实例不加载数据库
mount ---> 表示启动实例、加载数据库并保持数据库的关闭状态
open ---> 表示启动实例、加载并打开数据库（默认选项）

force ---> 表示终止实例并重新启动数据库
resetrict --> 用于指定受限制的会话方式启动数据库
pfile ---> 用于指定启动实例是所使用的文本参数文件，filename 就是文件名。
关闭数据库
shutdown [normal|transactional|immediate|abort]
解释：normal ---> 表示以正常方式关闭数据库（阻止新建连接，等待正在连接的用户主动断开）
transactional ---> 表示在当前所有的活动事务被提交完毕之后，关闭数据库（阻止新建连接和开始新事务，等到有活动的事务提交后再断开用户连接）
immediate ---> 表示在尽可能短的时间内立即关闭数据库（阻止新建连接和开始新事务，回退未提交的事务，关闭数据库）
abort ---> 表示以终止方式来关闭数据库（具有一定的强制性和破坏性）
第二章 Oracle 11g 体系
2.2 逻辑存储结构
Oracle 数据库 -> 表空间 -> 表 -> 数据段 -> 数据区 -> 数据块
索引 -> 索引段 ...
视图 -> ...
回滚段
临时段
总之，Oracle数据库由多个表空间组成（数据库自身也属于逻辑概念），而表空间由多个段组成，段由多个数据区组成，数据区由多个数据块组成。

2.2.1 数据块（Data Blocks）
数据块：Oracle数据库逻辑存储结构中最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位。
在v$parameter数据词典中查询标准数据块的大小：
select name,value from v$parameter where name = 'db_block_size';
数据块结构：块头（基本信息，物理地址、段的类型），表目录（表相关信息），行目录，空余空间（未使用），行数据（已使用）。
2.2.2 数据区（Extent）
数据区：Oracle的最小的分配单位。数据区是表中数据增长的基本单位，在Oracle中，分配存储空间就是以数据区位单位的。
一个Oracle对象包含至少一个数据区，设置一个表或索引的存储参数包含设置它的数据区大小。
2.2.3 段（Segment）
段是一个独立的逻辑存储结构，用于存储表、索引或簇等占用空间的数据对象。一个段只属于一种特定的数据对象，每当创建一个独立的数据对象时，都将为它创建一个段。
段是为特定的数据对象（如表、索引、回滚等）分配的一些列数据区。段内包含的数据区可以不连续，并且可以跨越多个文件，使用段的目的是用来保存特定的数据对象。
段：数据段（存表的数据，通过添加新的数据区来实现表的增大）、索引段、回滚段（保存修改前的旧值）、临时段。
2.2.4 表空间（TableSpace）
表空间是Oracle中最大的逻辑划分区域，通常用来存放数据表、索引、回滚段等数据对象，任何数据对象在创建时都必须指定存储在摸个表空间中。表空间（属于逻辑存储结构）
与数据文件（属于物理存储结构）相对应，一个表空间由一个或或多个数据文件组成，一个数据文件只属于一个表空间；Oracle数据的存储空间在逻辑上表现为表空间，而在物理
上表现为数据文件。举个例子来说，表空间相当于操作系统中的文件夹，而数据文件就相当于文件夹中的文件。每个数据库至少有一个表空间（即SYSTEM表空间），表空间的大小等于所有
从属于他的数据文件的大小。
由于表空间在物理上（即磁盘）包括操作系统中的一个或多个数据文件，因此在表空间中创建的数据对象就存在以下两种情况：
（1）若表空间只包含一个数据文件，则该表空间中的所有对象都存储在这个数据文件中。
（2）若表空间包含多个数据文件，则Oracle即可将数据对象存储在该表空间中的任意一个数据文件中，也可以将同一个数据对象中的数据分布在表空间的多个数据文件中。

系统默认的表空间：
SYSTEM表空间 --- 系统表空间 ： 用于存放Oracle系统内部表和数据词典的数据，如表明、列名、用户名等。
SYSAUX表空间 --- SYSTEM表空间的辅助表空间，降低SYSTEM的负荷。
UNDO表空间 --- 撤销表空间，用于存储撤销信息的表空间。一般在Oracle实例后，Oracle系统自动创建一个名字为“UNDOTBS1”的撤销表空间，对应的数据文件是“UNDOTBS01.DBF”.
USERS表空间 --- 用户表空间，是Oracle建议用户使用的表空间，可以在这个表空间上创建各种数据对象，比如创建表、索引、用户等数据对象。Oracle系统的样例用户SCOTT对象就存放在USRS表空间中。
除了Oracle系统默认创建的表空间外，用户可根据应用系统的实际情况及其所要存放对象的类型创建多个自定义的表空间，以区分用户数据与系统数据。此外，不同应用系统的数据应存放在不同的表空间上，
而不同的表空间的文件应存放在不同的硬盘上，从而减少I/O冲突，提高应用系统的操作性能。




2.3 物理存储结构
Oracle数据库的物理存储结构由多个物理文件组成，主要有数据文件、控制文件、日志文件、重做文件、归档日志文件、参数文件、口令文件和警告文件等。
2.3.1 数据文件
用于保存用户应用程序数据和Oracle系统内部数据文件。在创建表空间是，Oracle会创建表空间的数据文件。在表空间中创建数据对象时，用户无法指定使用 哪个数据文件来进行存储，只能由Oracle系统负责为数据对象选择具体的数据文件存储。
读取数据： 判断是否存在高速缓存中，若不存在，从数据文件读取数据，并将数据保存在高速数据缓冲区；若存在，从高速缓存取数。
修改、插入数据：先报数据保存在高速缓冲区，然后由Oracle的后台进程DBWR决定如何将其写入响应的数据文件。
查看系统数据文件：
select * from dba_data_files;
select * from v$datafile;
大致分为：系统数据文件、撤销数据文件、用户数据文件
查看临时数据文件：
select * from dba_temp_files;
2.3.2 控制文件
控制文件是一个二进制文件，它记录了数据库的物理结构，其中包含数据库名、数据文件与日志文件的名字和位置、数据库创建日期等信息。控制文件一般在Oracle系统安装时或创建数据库实例时自动创建，控制文件所存放的路径由服务器参数文件
spileorcl.ora的control_files参数值来指定。
Oracle的启动：访问spfile文件 --> 分配Oracle系统全局区（SGA） ，此时Oracle实例处于安装状态，日志文件处于打开状态 --> 自动读取“控制文件”中的数据文件和日志文件中的信息 --> 打开数据文件和日志文件供用户使用。
数据库与控制文件关系：一对多。
查询控制文件信息：
select * from v$controlfile;
2.3.3 日志文件
分为：重做日志（Redo Log File）和归档日志（Archive Log File）。
重做日志：在数据库运行期间，当用户执行COMMIT命令时，数据库首先将每笔操作的原始记录写入到日志文件总，成功后，才把新的记录传递给应用程序。
日志信息查询：
select * from v$logfile;
Oracle系统在运行过程中产生的日志信息，首先被临时存放在系统全局区的重做日志缓冲区中，Commit命令（或日志缓冲区信息满1/3）时，LGWR进程（日志写入进程）将日志信息从一个日志组写满后接着写另一个日志组。当LGWR进程将所有的
能用的日志文件都使用过一遍将再次转向第一个日志组重新覆盖。

归档文件：
非归档模式：...
归档模式：当所有日志文件被写入一遍后，LGWR进程将转向第一个日志组进行重新覆写前，先由归档进程（ARCH）将日志文件中的信息读出写入归档文件。归档操作中，日志文件不能写入，这样会影响效率，而且会占用大量磁盘空间，影响性能。
归档文件查询是否采用归档：
select * from v$database;
查询归档文件所在路径：
show parameter log_archive_dest;
2.3.4 服务器参数文件
。。。（待完成）
2.2.5 密码文件、警告文件和跟踪文件
。。。（待完成）
2.4 Oracle 11g 服务器机构
。。。（待完成）
2.5 数据词典
数据词典是Oracle存放关于数据库内部信息的地方，用来描述数据库内部的运行和管理情况。
Oracle字典视图包括四个层次，分别为X$内部表、基础数据字典表、数据字典视图和动态性能视图。
2.5.1 Oracle数据词典概述
dba_ : 包含数据库实例的所有对象信息。
v$_ : 当前实例的动态视图，包含系统管理和系统优化等所使用的视图。
V_$ ：是V$的同义词。
user_ ：记录用户的对象信息。
gv_ : 分布式环境下所有实例的动态视图，包含系统管理和系统优化使用的视图。
GV_$ ：是GV$的同义词。
all_ ：记录用户的对象信息机被授权访问的对象信息。
X$ ：是GV$视图的数据来源，oracle内部表。
2.5.2 Oracle 常用的数据词典
(1) 基本数据词典
Oracle对这些数据字典都分别建立了用户视图视图，不仅有更容易接受的名字，还隐藏了数据字典表表之间的关系，让我们字节通过视图来进行查询，
简单而形象，Oracle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX


user_对象视图：描述了当前用户schema下的对象；
all_对象视图：描述了当前用户有权限访问到的所有对象的信息；
dba_对象视图：包括了所有数据库对象的信息；


注 意： 在创建数据库是会运行两个脚本。先运行catalog.sql，该脚本用来创建数据库的内部字典表。然后再运行catrpoc.sql，
该脚本用来创 建数据库内建的存储过程、包等pl\sql对象。如果我们是使用dbca来创建数据库，则dbca会自动调用这两个脚本。
否则在执行create database命令来创建时，则需要手工运行这两个脚本。
(2)动态性能视图概述
所谓动态性能视图，就是指将内存里的数据或控制文件里的数据以表的形式展现出来。他们实际都是需表，并不是真正的表。
只不过是为了能够让我们更好的管理数据库的性能，所以将内存里的活动情况以表的形式展现出来。
只要数据库还在运行，就会不断更新动态性能视图。一旦数据库关闭或崩溃，动态性能视图里的数据就会全部丢失，下次重启时则会重新计算。
就像所有的数据字典的名词都存在dictionary里一样，v$fixed_table里存放了所有的动态性能视图的名称。
所有的动态性能的属主都是sys，且都以v$_开头，创建同名词时则都以v$开头。












SQL的基本执行顺序及优化：

from子句--执行顺序为从后往前、从右到左表名（最后面的那个表名为驱动表，执行顺序为从后往前， 所以数据量较少的表尽量放后）

where子句--执行顺序为自下而上、从右到左（表之间的连接必须写在其他Where 条件之前， 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾）
数据库在处理SQL以前，都会去分析，发现查询的条件中是否有索引，如果有，县更具索引查找。那么优先根据姓名的条件，去检索，然后再去把通过索引得到的数据，
验证Oracle查询条件是从左向右还是从右向左：
方法一：（第1条语句执行不会出错，第2条语句会提示除数不能为零。说明从右 --> 左）
Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2;
Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1;
select * from temp where to_number(t2)>1 and t1='sz';
select * from temp where t1='sz' and to_number(t2)>1;
方法二：（Output中可以清楚的看到结果）
Create Or Replace Function F1(v_In Varchar2) Return Varchar2 Is
Begin
Dbms_Output.Put_Line('exec F1');
Return v_In;
End F1;
/
Create Or Replace Function F2(v_In Varchar2) Return Varchar2 Is
Begin
Dbms_Output.Put_Line('exec F2');
Return v_In;
End F2;
/
select 1 from dual where f1('1')='1' and f2('1')='1';
select 1 from dual where f2('1')='1' and f1('1')='1';
groupby--执行顺序从左往右分组

select子句--少用*号，尽量取字段名称。
ORACLE在解析的过程中， 会将依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 使用列名意味着将减少消耗时间。

sql 语句用大写的；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行

order by子句--执行顺序为从左到右排序，很耗资源


oracle 语句提高查询效率的方法：


　　1:...where column in （select * from ... where ...）；


　　 2:...where exists （select 'X' from ... where ...）；


　 　第二种格式要远比第一种格式的效率高。
在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
使用EXIST,Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。


　　避免使用having子句。HAVING 只会在检索出所有记录之后才对结果集进行过滤。


　　这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。




分组：排序去组内最大：
select deptno,ename,sal from
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from scott.emp) where sal_order




decode函数：
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
原理：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF



Oracle常用系统函数：
字符类型：select ASCII('c') from dual; ---90
select CHR(90) from dual; ---z
select concat('hello ','world') from dual; select 'hello ' | 'world' from dual; ---hello world
select initcap('oh my god!') from dual; ---Oh My God!
select instr('oracle 11g','1',3,2) from dual; ---9 从第3个字符开始查询字符串“1”第2次出现的位置。
select length('123') from dual; --- 3
select lower('HELLO'),upper('hello') from dual; ---hello,HELLO
select ltrim('####hello###','#'),rtim('Hello '),trim('#' from '##Hello###') from dual;
--- hello###,Hello,Hello
select replace('Bad Luck Bad Gril','Good') from dual; ---Good Luck Good Gril
select substr('MessageBox',8,3) from dual; ---Box
数字类函数：ABS(n) --> 绝对值
CELL(n) --> >= n 的最小整数
COS(n) --> n 的余弦值，n为弧度
EXP(n) --> e 的n 次幂
FLORR(n) --> LOG(n1, n2) --> 以n1为低n2的对数
MOD(n1,n2) --> n1 除以 n2 的余数
POWER(n1,n2)--> n1 的 n2 次方
ROUND(n1,n2)--> 返回小数点最接近的整数
SIGN(n) --> 负数返回-1，整数返回1,0返回0
SIN(n) --> 返回正弦值
SQRT(n) --> n 的平方根
TRUNC(n1,n2)--> ....
日期、时间类函数：
select sysdate from dual;
select add_months(sysdate,6) from dual;-- 返回sysdate加上6个月的结果
last_day(d) --- 返回日期d的最后一天，d为时间类型
months_between(d1,d2) ---d1,d2 为时间类型
new_time(d1,t1,t2) ---d1 为日期类型，t1,t2 为字符串
转换类函数：
to_char(x) 或者 to_char(x,'YYYY-MM-DD')
to_date(x) 或者 to_date(x,'YYYY-MM-DD')
to_number(x)



数据表的维护：
1.维护表中的字段：
增加：alter table table_name add(column_name type);
删除：alter table table_name drop column column_name;
alter table table_name drop(column_name1,column_name2);
修改属性：alter table table_name modify column_name columntype;
重命名表：alter table table_old_name rename to table_new_name;
删除表：drop table table_name [cascade constraints];
修改表的状态：alter table table_name read only;
alter table table_name read write;
数据的完整性和约束性：
非空约束：alter table table_name modify column_name [not] null;
主键约束：表中没有定义主键：alter table talbe_name add constraint 主键名 primary key(column_name); --自己定义主键名
alter table talbe_name add primary key(column_name); --系统自动分配名称的主键约束
表中存在主键时，先删除主键：alter table table_name drop constraint 主键名；
唯一性约束：alter table table_name add constraint 约束名 unique(column_name)；
alter table table_name drop constraint 约束名;
外键约束：alter table table_name add constraint 外键约束名 foreign key(column_name) references table_name2(column_name) -- table_name2(外键所在的表);
alter table table_name add constraint 外键约束名 foreign key(column_name) references table_name2; -- table_name2(外键所在的表);





闪回功能恢复被删除的表：
1.确认表是否被删除：select * from student;
2.确认删除后，去回收站查询看看：select object_name,original_name from recyclebin where original_name = 'STEDENT';
3.使用FLASHBACK TABLE 语句恢复被删除的表：flashback table student to before drop;





第十章 索引对象