Oracle数据库学习.docx
《Oracle数据库学习.docx》由会员分享，可在线阅读，更多相关《Oracle数据库学习.docx（22页珍藏版）》请在冰豆网上搜索。
Oracle数据库学习
Oracleserver由例程和数据库两部分组成，例程是一组内存结构和后台进程的集合，内存结构包括数据高速缓存、重做日志缓冲区和共享池3部分，这些内存结构总称为SGA；后台进程包括SMON,PMON,DBWR,CKPT,LGWR.ARCH等。
数据库是一组ＯＳ文件的集合，由数据文件、控制文件和重做日志组成。
共享池：
用于存放最近执行的ＳＱＬ语句和数据字典信息（库高速缓存和数据字典高速缓存组成）。
库高速缓存—用于存放最近执行的ＳＱＬ语句，包括ＳＱＬ语句文本、解析代码值及其执行计划，它还包含了许多上下文区ContextArea，也是共享游标。
完全相同的SQL语句标准------语句文本相同—大小写相同—赋值变量相同。
数据字典高速缓存---存放数据字典的信息，包括表、列的定义以及权限信息。
数据高速缓存：
用于存放最近访问的数据库信息。
LRU，类似队列访问机制。
重做日志缓冲区：
用于记载例程变化。
由很多中作记录组成，并且，每条重做记录记载了被修改数据块的位置以及变化后的数据。
当执行update操作时，新数据2000将被存放到emp段锁对应的缓冲区B1中，旧数据1000将被存放到undo段所对应的缓冲区U1中，在修改这两个缓冲区之前，首相将它们的变化写到重做日志缓冲区，然后再修改它们的数据。
PGA和SGA的总和应小于系统总内存减去操作系统和其它应用程序所需的内存空间后剩余的内存空间。
数据库物理文件
临时表空间—临时文件：
重做日志（RedoLog）:
用于记录数据库变化，当进行例程恢复或介质恢复时需要使用重做日志。
执行DDL和DML操作时，事务变化会被写到重做日志缓冲区，而在特定时刻LGWR会将重做日志缓冲区内容写入重做日志。
控制文件（ControlFile）：
用于记录和维护数据库的物理结构。
当装载数据库时，oracle会根据控制文件所记载的信息打开所有数据文件和重做日志，其记录的信息----数据文件的位置及大小、重做日志文件的位置及大小、数据库名称及创建时间、日志序列号。
文本参数文件-----init.ora,服务器参数文件spfile.ora
------------------------------------------------------
用户进程和服务器进程：
当在客户端运行应用程序时，会启动相应的应用进程，该进程成为用户进程。
当连接到oracleserver时，oracle在服务器端会为用户派生一个新的进程，该进程为服务器进程。
连接和会话：
连接是用户进程到服务器进程的网络通信通道，连接和用户进程是一一对应的；会话是特定用户的连接，连接和会话是一对多的关系，但同一时刻，同一连接对应一个会话。
当使用PFILE是，如果要修改初始化参数，只要手工编辑参数文件就可以，规则如下
1、在修改或增加参数，若没有，则在文件尾部增加一行
2、每行只能设置一个参数
3、字符型参数的值可以用引号引住，数值型不用
4、如果一个初始化参数有多个值，则用逗号隔开
5、屏蔽参数用#
SPFILE为二进制形式存放，修改其参数值只能用altersystem
SPFILE和PFILE之间的转换管理
---CREATEPFILE[=’PFILE_NAME’]FROMSPFILE[=’SPFILE_NAME’]
在mount状态下移动数据文件
用于移动任何表空间数据文件。
1、关闭并装载数据库
将数据库为mount状态
2、移动数据文件到目标位置
HOSTMOVED:
\DEMO\SYSTEM01.DBFE:
\DEM\SYSTEM01.DBF
3、执行ALTERDATABASE命令
物理移动完数据文件之后，还要使用
ALTERDATABASERENAMEFILE
‘D:
\DEMO\SYSTEM01.DBF’
TO‘E:
\DEM\SYSTEM01.DBF’
4、打开数据库ALTERDATABASEOPEN;
方案：
用户所拥有的数据库对象的集合。
在oracle数据库中，对象是以用户来组织的，用户与方案是一一对应的关系，并且二者名称相同
管理权限
权限，是指执行特定类型ＳＱＬ命令的权利。
系统权限：
它用于控制用户可以执行的一个或一组数据库操作。
对象权限：
是指范文其他方案对象的权利，它用于控制用户对其他方案对象的访问。
GRANT|REVOKEsystem_priv[,system_priv…]
TO{user|role|public[,user|role|public]]…
[WITHADMINOPTION]
系统权限不仅可以被授予用户和角色，也可以被授予用户组public，当将系统权限授予public后，所有用户具有该系统权限。
另外，在授予系统权限时，可以带有WITHADMINOPTION选项，带有该选项，被授予权限的将用户，角色还可以将相应系统权限授予其他角色，用户。
系统权限UNLIMITEDTABLESPACE不能授予角色。
在必要的时候，调整这个参数的大小，可以提高数据缓存的命中率，从而大幅度的提高数据库的性能。
第二个参数是DB_BLOCK_SIZE。
这个参数主要是用来指定数据库在建立时所默认的块大小。
如果块设置的比较小，则有可能会发生行链化现象，从而降低数据库的性能;也有可能增加数据库的碎片，浪费表空间的存储空间以及降低数据库的查询性能等等。
所以根据需要有时候也要调整这个块大小来改善数据库的性能。
第三个参数是SHARED_POOL_SIZE参数。
这个参数主要是为那些数据字典缓存和共享SQL语句指定了在SGA里所分配的内存。
简单的来说，合理配置这个参数，能够同等的共享SQL语句。
调整这个参数，也是一种优化数据库性能的常用手段。
第四个参数为SGA_MAX_SIZE参数，这个参数指定了SGA可以动态增长的最大内存。
在SQLServer数据库中也有类似的参数。
一般来说，在同台数据库服务器中若部署有多个应用服务的话，则往往需要合理配置这个参数来避免多个应用服务之间争用内存。
如果这个参数设置的不合适的话，则有时候会导致另外一种应用服务由于没有足够的内存而停止服务。
第五个参数为LOG_CHECKPOINT_INTERVAL。
这个参数主要用来设置检查点的频率。
在每个检查点中，数据库系统执行数据写出，将所有脏块（已经修改还没有保存到硬盘中的数据）写入到数据库中对应的数据文件中。
默认情况下，如果在数据库缓存中有1/4的数据缓冲区是脏缓冲区，则数据库系统会自动执行检查点。
另外一个强制的原则就是在进行日志切换时，也会执行检查点。
在一些特定的情况下，如建立数据仓库，需要调整这个参数以满足特定场合的需要。
========================
六分钟学会创建Oracle表空间的步骤
1、先查询空闲空间
1.select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;
2、增加Oracle表空间
先查询数据文件名称、大小和路径的信息，语句如下：
1.select tablespace_name,file_id,bytes,file_name from dba_data_files;
3、修改文件大小语句如下
1.alter database datafile
2.'需要增加的数据文件路径，即上面查询出来的路径
3.'resize 800M;
4、创建Oracle表空间
1.create tablespace test
2.datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M
3.autoextend on
4.next 5M
5.maxsize 10M;
6.
7.create tablespace sales
8.datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
9.autoextend on
10.next 50M
11.maxsize unlimited
12.maxsize unlimited 是大小不受限制
13.
14.create tablespace sales
15.datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
16.autoextend on
17.next 50M
18.maxsize 1000M
19.extent management local uniform;
20.unform表示区的大小相同，默认为1M
21.
22.create tablespace sales
23.datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
24.autoextend on
25.next 50M
26.maxsize 1000M
27.extent management local uniform size 500K;
28.unform size 500K表示区的大小相同，为500K
29.
30.create tablespace sales
31.datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
32.autoextend on
33.next 50M
34.maxsize 1000M
35.extent management local autoallocate;
36.autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区
37.
38.create tablespace sales
39.datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
40.autoextend on
41.next 50M
42.maxsize 1000M
43.temporary;
44.temporary创建字典管理临时表空间
45.
46.create temporary tablespace sales
47.tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M
48.autoextend on
49.next 50M
50.maxsize 1000M
51.创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile
52.
53.8i系统默认创建字典管理临时表空间，要创建本地管理临时表空间要加temporary tablespace关键字
54.创建本地管理临时表空间时，不得使用atuoallocate参数，系统默认创建uniform管理方式
55.
56.为表空间增加数据文件：
57.alter tablespace sales add
58.datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M
59.autoextend on next 50M
60.maxsize 1000M;
创建本地管理临时Oracle表空间，如果是临时表空间，所有语句中的datafile都换为tempfile8i系统默认创建字典管理临时表空间，要创建本地管理临时表空间要加temporarytablespace关键字创建本地管理临时表空间时，不得使用atuoallocate参数，系统默认创建uniform管理方式
为表空间增加数据文件：
1.alter tablespace sales add
2.datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M
3.autoextend on next 50M
4.maxsize 1000M;
5、更改自动扩展属性：
1.alter database datafile
2.'/home/app/oracle/oradata/oracle8i/sales01.dbf',
3.'/home/app/oracle/oradata/oracle8i/sales02.dbf'
4.'/home/app/oracle/oradata/oracle8i/sales01.dbf
5.autoextend off;
**********************************************************************
1TB=1024GB（吉字节）
1GB=1024MB（兆字节）
1MB=1024KB（千字节）
1KB=1024byte（字节）
1byte=8bit（位）
=================
/*
sys和system用户的区别
【system】用户只能用normal身份登陆em。
【sys】用户具有“SYSDBA”或者“SYSOPER”权限，登陆em也只能用这两个身份，不能用normal。
“SYSOPER”权限，即数据库操作员权限，权限包括：
打开数据库服务器关闭数据库服务器
备份数据库恢复数据库
日志归档会话限制
“SYSDBA”权限，即数据库管理员权限，权限包括：
打开数据库服务器关闭数据库服务器
备份数据库恢复数据库
日志归档会话限制
管理功能创建数据库
normal、sysdba、sysoper有什么区别
normal是普通用户
另外两个，你考察他们所具有的权限就知道了
sysdba拥有最高的系统权限
sysoper主要用来启动、关闭数据库，sysoper登陆后用户是public
sysdba登陆后是sys
SQL>conn/assysdba
已连接。
SQL>grantsysopertotest;
授权成功。
SQL>conntest/testassysoper;
已连接。
SQL>showuser
USER为"PUBLIC"
SQL>conntest/testassysdba
已连接。
SQL>showuser
USER为"SYS"
SQL>
dba和sysdba的区别
dba、sysdba这两个系统角色有什么区别呢
在说明这一点之前我需要说一下oracle服务的创建过程
·创建实例
·启动实例
·创建数据库（system表空间是必须的）
启动过程
·实例启动
·装载数据库
·打开数据库
sysdba，是管理oracle实例的，它的存在不依赖于整个数据库完全启动，
只要实例启动了，他就已经存在，以sysdba身份登陆，装载数据库、打开数据库
只有数据库打开了，或者说整个数据库完全启动后，dba角色才有了存在的基础！
*/
1）
2）PL/SQL块的结构
PROCEDUREgive_raiseIS
DECLARE
Cutoff_dateDATE
BEGIN
Cutoff_date:
=SYSDATE-90
UPDATEemp
SETsal=sal*1.1
WHEREhiredateEXCEPTION
WHENNO_DATA_FOUNDTHENNULL;
END;
块头：
除了匿名块，所有的PL/SQL块都由一个块头开始
{{PROCEDURE|FUNCTION}nameIS|}
声明部分：
声明在块中使用的变量，定义使用在块中的游标、类型、本地过程和函数。
变量的使用范围是当前块以及当前块包括的部分。
3）Oracle的日期函数
TO_DATE（‘STR’）TO_CHAR（‘DATE’）
ADD_MONTHS（datetime,n）
LAST_DAY（D）：
返回日期d中月份中的最后一天的日期。
MONTHS_BETWEEN（D1，D2）:
返回两个日期指间的月数。
如果两个日期代表月的同一天，或者代表他们各自月的最后一天，则返回一个整数，否则，MONTHS_BETWEEN将返回一个分数值，且月天数按31天计算。
NEXT_DAY（D,STRING）;计算特定天下一次出现的日期
NVL（exp1,exp2）:
判断exp1是否为空，若为true，返回exp2.
4）两阶段提交协议：
1、系统A的程序为当前的分布式事务发布一个commit。
这更新了系统B和系统C中的表。
系统A将作为提交过程的协调者，协调系统B和系统C上的DBMS软件的活动。
2、系统A向系统B和系统C发送getready消息，并在自己的事务日志中记录下这条消息。
3、当系统B或系统C上的DBMS收到getready消息，必须做好提交或者撤销当前事务的准备。
如果DBMS进入到“准备提交”状态，就会向系统A回答yes,并记录在其本地事务日志中，如果不能进入，就回答no
4、系统A等待对getready消息的回答。
如果所有回答都是yes,系统A向系统B和系统C发送commit消息，并在其事务日志中记录这个决定。
如果回答当中任何一个是no,或者如果不是所有的回答都在某个规定时间内收到，系统A会给两个系统发送撤销消息，并在其事务日志中记录这个决定。
5、当系统B或系统C上的DBMS收到commit或者rollback消息时，就必须按照要求进行处理。
当DBMS在第三步想getready消息回答yes时，它就放弃自主决定事务命令的功能。
DBMS提交或者撤销被请求的部分事务，在事务日志中写下commit或rollback消息，并向系统A返回ok消息。
6、当系统A收到所有ok消息，它知道事务已经被提交或撤销，并向程序返回适当的sqlcode值，
5）应用程序服务器，为执行应用逻辑提供了一个平台，网站的internet端组件和it端组件之间的中介者
6）数据仓库的组成---装载性能和运行时间性能之间的平衡
数据仓库装载工具：
一组程序，用来从公司事务处理系统（关系数据库、小型机、小型计算机的文件和遗留数据库）中抽取数据，处理数据并把数据装载进数据仓库。
这个处理过程一般涉及到事务数据的清理、过滤、重新格式化及把数据批量装进数据仓库。
数据仓库：
经过最优化处理的关系数据库，用于存储大量数据、高速装载批量数据和支持负责商业分析查询。
数据分析工具：
用于执行统计和时间序列分析，进行“如果……将会怎么样”的分析。
并以表格的形式提交结果。
7）存储过程procedure
8）约束的描述
9）共享锁：
当事务想要从数据库中读取数据的时候，DBMS使用这种锁，另一种并发事务也可能在同一数据上获得共享锁，从而允许事务读取数据
独占锁：
当事务想要更新数据库中的数据时，DBMS使用这种锁。
当事务在某一数据上使用独占锁时候，其他事务不能在该数据上获得任何类型的锁。
10）事务模型：
Sybase事务模型
BEGINTRANSACTION
COMMITTRANSACTION
SAVETRANSACTION：
在事务的中间建立一个保存点，该保存点名在语句中指定。
ROLLBACKTRANSACTION
11）Transaction：
一条或多条SQL语句序列，它们组合在一起形成一个逻辑工作单元。
形成事务的SQL语句通常紧密相关并执行相互依赖的一组操作。
事务中的每条语句通常执行任务的一部分，它们全部组合起来才能完成任务。
12）大容量装载：
sql语句块|exists
13）多行insert语句：
insertintotable（col1,col2……）
Selectexpression
14）查询表达式：
允许的操作链接（join）、并（union）、差（difference）,取两表中，返回第一个表减去第二个表记录之后所剩记录、交（intersect）
列出其订单超过$30000或现货量超过#30000的所有产品（union）
列出其订单超过$30000且现货量超过#30000的所有产品（intersect）
列出其订单超过$30000但销售价格小于$1000的所有产品（differenceexpect）
15）记录值比较：
对两个结构化记录的比较，从首个字段值开始比较。
16）记录值子查询：
记录值子查询不返回一个数据项，而是一行数据项，可以在SQL2表达式中使用这行数据项并与其他记录相比较。
；列出最高单价产品的所有订单的订单号和取得日期
SELECTorder_num,order_date
FROMORDERS
WERE（MFR,PRODUCT）=（SELECTMFR_ID,PRODUCT_ID
FROMPRODUCTS
WHEREPrice=
（SELECTMAX（PRICE）FROM
PRODUCTS））
17）NULLIF（value,newValue）
18）COLAESCE表达式：
和case功能类似，但更简洁，处理null值coalesce（value1,value2,0000）
19）CASE表达式：
当DBMS遇到一个CASE表达式时，它计算第一个搜索条件，如果为true,那么CASE表达式的值是第一个结果表达式的值。
如果第一个搜索条件的结果不是true，DBMS继续第二个搜索条件，依次递归。
20）CAST表达式：
可显示的要求DBMS使用CAST表达式在数据类型中进行转换，CAST语法：
CAST值表达式AS数据类型
Selectname,castrep_officeasvarchar,hier_dataasvarchar
Fromtable1.
21）ＳＱＬ查询处理原则为了生成select语句的查询结果：
1、如果语句是select语句的一个union，把第二步到第七步应用到每条语句中，以生成它们的各个查询结果。
2、形成在from子句中命名的表的积。
如果from子句命名了一个表，积就是那个表。
3、如果有一条where子句，应用它的搜索条件到积表的每一条记录，保留搜索条件为true的那些记录（去掉结果为false或null的那些结果）。
如果having子句包含一个子查询，对于要测试的每一条记录，执行这个子查询。
4、如果有一条groupby子句，安排积表中剩余的记录进到记录组中，以便在每一组中的记录在所有分组的字段中有同样的值。
5、如果有一条having子句，应用它的搜索条件到每一组中，保留其搜索条件为true的那些组，如果having子句包含一个子查询，对于正被测试
升级会员 