Oracle数据库学习.docx

1、Oracle数据库学习Oracle server由例程和数据库两部分组成，例程是一组内存结构和后台进程的集合，内存结构包括数据高速缓存、重做日志缓冲区和共享池3部分，这些内存结构总称为SGA；后台进程包括SMON,PMON,DBWR,CKPT,LGWR.ARCH等。数据库是一组文件的集合，由数据文件、控制文件和重做日志组成。共享池：用于存放最近执行的语句和数据字典信息（库高速缓存和数据字典高速缓存组成）。库高速缓存用于存放最近执行的语句，包括语句文本、解析代码值及其执行计划，它还包含了许多上下文区Context Area，也是共享游标。完全相同的SQL语句标准-语句文本相同大小写相同赋值变量相

2、同。数据字典高速缓存-存放数据字典的信息，包括表、列的定义以及权限信息。数据高速缓存：用于存放最近访问的数据库信息。LRU，类似队列访问机制。重做日志缓冲区：用于记载例程变化。由很多中作记录组成，并且，每条重做记录记载了被修改数据块的位置以及变化后的数据。当执行update操作时，新数据2000将被存放到emp段锁对应的缓冲区B1中，旧数据1000将被存放到undo段所对应的缓冲区U1中，在修改这两个缓冲区之前，首相将它们的变化写到重做日志缓冲区，然后再修改它们的数据。PGA 和 SGA 的总和应小于系统总内存减去操作系统和其它应用程序所需的内存空间后剩余的内存空间。数据库物理文件临时表空间临

3、时文件：重做日志(Redo Log):用于记录数据库变化，当进行例程恢复或介质恢复时需要使用重做日志。执行DDL和DML操作时，事务变化会被写到重做日志缓冲区，而在特定时刻LGWR会将重做日志缓冲区内容写入重做日志。控制文件(Control File)：用于记录和维护数据库的物理结构。当装载数据库时，oracle会根据控制文件所记载的信息打开所有数据文件和重做日志，其记录的信息-数据文件的位置及大小、重做日志文件的位置及大小、数据库名称及创建时间、日志序列号。文本参数文件-init.ora,服务器参数文件spfile.ora-用户进程和服务器进程：当在客户端运行应用程序时，会启动相应的应用进程

4、，该进程成为用户进程。当连接到oracle server时，oracle在服务器端会为用户派生一个新的进程，该进程为服务器进程。连接和会话：连接是用户进程到服务器进程的网络通信通道，连接和用户进程是一一对应的；会话是特定用户的连接，连接和会话是一对多的关系，但同一时刻，同一连接对应一个会话。当使用PFILE是，如果要修改初始化参数，只要手工编辑参数文件就可以，规则如下1、 在修改或增加参数，若没有，则在文件尾部增加一行2、 每行只能设置一个参数3、 字符型参数的值可以用引号引住，数值型不用4、 如果一个初始化参数有多个值，则用逗号隔开5、 屏蔽参数用#SPFILE为二进制形式存放，修改其参数值

5、只能用alter systemSPFILE和PFILE之间的转换管理-CREATE PFILE =PFILE_NAME FROM SPFILE=SPFILE_NAME在mount状态下移动数据文件用于移动任何表空间数据文件。1、 关闭并装载数据库将数据库为mount状态2、 移动数据文件到目标位置 HOST MOVE D:DEMOSYSTEM01.DBF E:DEMSYSTEM01.DBF3、 执行ALTER DATABASE命令 物理移动完数据文件之后，还要使用 ALTER DATABASE RENAME FILE D:DEMOSYSTEM01.DBF TO E:DEMSYSTEM01.DB

6、F4、 打开数据库 ALTER DATABASE OPEN;方案：用户所拥有的数据库对象的集合。在oracle数据库中，对象是以用户来组织的，用户与方案是一一对应的关系，并且二者名称相同管理权限权限，是指执行特定类型命令的权利。系统权限：它用于控制用户可以执行的一个或一组数据库操作。对象权限：是指范文其他方案对象的权利，它用于控制用户对其他方案对象的访问。 GRANT| REVOKE system_priv,system_priv TO user|role|public ,user|role|public WITH ADMIN OPTION系统权限不仅可以被授予用户和角色，也可以被授予用户组p

7、ublic，当将系统权限授予public后，所有用户具有该系统权限。另外，在授予系统权限时，可以带有WITH ADMIN OPTION选项，带有该选项，被授予权限的将用户，角色还可以将相应系统权限授予其他角色，用户。系统权限UNLIMITED TABLESPACE不能授予角色。在必要的时候，调整这个参数的大小，可以提高数据缓存的命中率，从而大幅度的提高数据库的性能。第二个参数是DB_BLOCK_SIZE。这个参数主要是用来指定数据库在建立时所默认的块大小。如果块设置的比较小，则有可能会发生行链化现象，从而降低数据库的性能;也有可能增加数据库的碎片，浪费表空间的存储空间以及降低数据库的查询性能等

8、等。所以根据需要有时候也要调整这个块大小来改善数据库的性能。第三个参数是SHARED_POOL_SIZE参数。这个参数主要是为那些数据字典缓存和共享SQL语句指定了在SGA里所分配的内存。简单的来说，合理配置这个参数，能够同等的共享SQL语句。调整这个参数，也是一种优化数据库性能的常用手段。第四个参数为SGA_MAX_SIZE参数，这个参数指定了SGA可以动态增长的最大内存。在SQLServer数据库中也有类似的参数。一般来说，在同台数据库服务器中若部署有多个应用服务的话，则往往需要合理配置这个参数来避免多个应用服务之间争用内存。如果这个参数设置的不合适的话，则有时候会导致另外一种应用服务由于

9、没有足够的内存而停止服务。第五个参数为LOG_CHECKPOINT_INTERVAL。这个参数主要用来设置检查点的频率。在每个检查点中，数据库系统执行数据写出，将所有脏块(已经修改还没有保存到硬盘中的数据)写入到数据库中对应的数据文件中。默认情况下，如果在数据库缓存中有1/4的数据缓冲区是脏缓冲区，则数据库系统会自动执行检查点。另外一个强制的原则就是在进行日志切换时，也会执行检查点。在一些特定的情况下，如建立数据仓库，需要调整这个参数以满足特定场合的需要。=六分钟学会创建Oracle表空间的步骤1、先查询空闲空间1. selecttablespace_name,file_id,block_id

10、,bytes,blocksfromdba_free_space;2、增加Oracle表空间先查询数据文件名称、大小和路径的信息，语句如下：1. selecttablespace_name,file_id,bytes,file_namefromdba_data_files;3、修改文件大小语句如下1. alterdatabasedatafile 2. 需要增加的数据文件路径，即上面查询出来的路径 3. resize800M;4、创建Oracle表空间1. createtablespacetest 2. datafile/home/app/oracle/oradata/oracle8i/test0

11、1.dbfsize8M 3. autoextendon 4. next5M 5. maxsize10M; 6. 7. createtablespacesales 8. datafile/home/app/oracle/oradata/oracle8i/sales01.dbfsize800M 9. autoextendon 10. next50M 11. maxsizeunlimited 12. maxsizeunlimited是大小不受限制 13. 14. createtablespacesales 15. datafile/home/app/oracle/oradata/oracle8i/s

12、ales01.dbfsize800M 16. autoextendon 17. next50M 18. maxsize1000M 19. extentmanagementlocaluniform; 20. unform表示区的大小相同，默认为1M 21. 22. createtablespacesales 23. datafile/home/app/oracle/oradata/oracle8i/sales01.dbfsize800M 24. autoextendon 25. next50M 26. maxsize1000M 27. extentmanagementlocaluniformsi

13、ze500K; 28. unformsize500K表示区的大小相同，为500K 29. 30. createtablespacesales 31. datafile/home/app/oracle/oradata/oracle8i/sales01.dbfsize800M 32. autoextendon 33. next50M 34. maxsize1000M 35. extentmanagementlocalautoallocate; 36. autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区 37. 38. createtablespacesales

14、39. datafile/home/app/oracle/oradata/oracle8i/sales01.dbfsize800M 40. autoextendon 41. next50M 42. maxsize1000M 43. temporary; 44. temporary创建字典管理临时表空间 45. 46. createtemporarytablespacesales 47. tempfile/home/app/oracle/oradata/oracle8i/sales01.dbfsize800M 48. autoextendon 49. next50M 50. maxsize100

15、0M 51. 创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile 52. 53. 8i系统默认创建字典管理临时表空间，要创建本地管理临时表空间要加temporarytablespace关键字 54. 创建本地管理临时表空间时，不得使用atuoallocate参数，系统默认创建uniform管理方式 55. 56. 为表空间增加数据文件： 57. altertablespacesalesadd 58. datafile/home/app/oracle/oradata/oracle8i/sales02.dbfsize800M 59. autoextendo

16、nnext50M 60. maxsize1000M;创建本地管理临时Oracle表空间，如果是临时表空间，所有语句中的datafile都换为tempfile8i系统默认创建字典管理临时表空间，要创建本地管理临时表空间要加temporary tablespace关键字创建本地管理临时表空间时，不得使用atuoallocate参数，系统默认创建uniform管理方式为表空间增加数据文件：1. altertablespacesalesadd 2. datafile/home/app/oracle/oradata/oracle8i/sales02.dbfsize800M 3. autoextendon

17、next50M 4. maxsize1000M;5、更改自动扩展属性：1. alterdatabasedatafile 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. autoextendoff;*1TB=1024GB(吉字节)1GB=1024MB(兆字节）1MB=1024KB（千字节）1KB=1024byte(字节)1byte=8bit(位)

18、=/*sys和system用户的区别【system】用户只能用normal身份登陆em。【sys】用户具有“SYSDBA”或者“SYSOPER”权限，登陆em也只能用这两个身份，不能用normal。“SYSOPER”权限，即数据库操作员权限，权限包括：打开数据库服务器 关闭数据库服务器备份数据库 恢复数据库日志归档 会话限制“SYSDBA”权限，即数据库管理员权限，权限包括：打开数据库服务器 关闭数据库服务器备份数据库 恢复数据库日志归档 会话限制管理功能 创建数据库normal 、sysdba、 sysoper有什么区别normal 是普通用户 另外两个，你考察他们所具有的权限就知道了sys

19、dba拥有最高的系统权限sysoper主要用来启动、关闭数据库，sysoper 登陆后用户是 publicsysdba登陆后是 sysSQL conn / as sysdba已连接。SQL grant sysoper to test;授权成功。SQL conn test/test as sysoper;已连接。SQL show userUSER 为PUBLICSQL conn test/test as sysdba已连接。SQL show userUSER 为SYSSQLdba和sysdba的区别dba、sysdba这两个系统角色有什么区别呢 在说明这一点之前我需要说一下oracle服务的创建

20、过程创建实例启动实例创建数据库(system表空间是必须的)启动过程实例启动装载数据库打开数据库sysdba，是管理oracle实例的，它的存在不依赖于整个数据库完全启动，只要实例启动了，他就已经存在，以sysdba身份登陆，装载数据库、打开数据库只有数据库打开了，或者说整个数据库完全启动后，dba角色才有了存在的基础！*/1） 2） PL/SQL块的结构PROCEDURE give_raise ISDECLARECutoff_date DATE BEGIN Cutoff_date := SYSDATE-90 UPDATE emp SET sal = sal * 1.1 WHERE hired

21、ate cutoff_date;EXCEPTION WHEN NO_DATA_FOUND THEN NULL;END;块头：除了匿名块，所有的PL/SQL块都由一个块头开始 PROCEDURE | FUNCTION name IS | 声明部分：声明在块中使用的变量，定义使用在块中的游标、类型、本地过程和函数。变量的使用范围是当前块以及当前块包括的部分。3） Oracle的日期函数TO_DATE(STR) TO_CHAR(DATE)ADD_MONTHS(date time, n)LAST_DAY(D)：返回日期d中月份中的最后一天的日期。MONTHS_BETWEEN(D1，D2):返回两个日期

22、指间的月数。如果两个日期代表月的同一天，或者代表他们各自月的最后一天，则返回一个整数，否则，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发送get ready消息，并在自己的事务日志中记录下这条消息。3、 当系统B或系

23、统C上的DBMS收到get ready消息，必须做好提交或者撤销当前事务的准备。如果DBMS进入到“准备提交”状态，就会向系统A回答yes,并记录在其本地事务日志中，如果不能进入，就回答no4、 系统A等待对get ready消息的回答。如果所有回答都是yes,系统A向系统B和系统C发送commit消息，并在其事务日志中记录这个决定。如果回答当中任何一个是no,或者如果不是所有的回答都在某个规定时间内收到，系统A会给两个系统发送撤销消息，并在其事务日志中记录这个决定。5、 当系统B或系统C上的DBMS收到commit或者rollback消息时，就必须按照要求进行处理。当DBMS在第三步想get

24、 ready消息回答yes时，它就放弃自主决定事务命令的功能。DBMS提交或者撤销被请求的部分事务，在事务日志中写下commit或rollback消息，并向系统A返回ok消息。6、 当系统A收到所有ok消息，它知道事务已经被提交或撤销，并向程序返回适当的sqlcode值，5） 应用程序服务器，为执行应用逻辑提供了一个平台，网站的internet端组件和it端组件之间的中介者6） 数据仓库的组成-装载性能和运行时间性能之间的平衡数据仓库装载工具：一组程序，用来从公司事务处理系统(关系数据库、小型机、小型计算机的文件和遗留数据库)中抽取数据，处理数据并把数据装载进数据仓库。这个处理过程一般涉及到事

25、务数据的清理、过滤、重新格式化及把数据批量装进数据仓库。数据仓库：经过最优化处理的关系数据库，用于存储大量数据、高速装载批量数据和支持负责商业分析查询。数据分析工具：用于执行统计和时间序列分析，进行“如果将会怎么样”的分析。并以表格的形式提交结果。7） 存储过程procedure8） 约束的描述9） 共享锁：当事务想要从数据库中读取数据的时候，DBMS使用这种锁，另一种并发事务也可能在同一数据上获得共享锁，从而允许事务读取数据独占锁：当事务想要更新数据库中的数据时，DBMS使用这种锁。当事务在某一数据上使用独占锁时候，其他事务不能在该数据上获得任何类型的锁。10） 事务模型： Sybase事务

26、模型 BEGIN TRANSACTIONCOMMIT TRANSACTIONSAVE TRANSACTION：在事务的中间建立一个保存点，该保存点名在语句中指定。ROLLBACK TRANSACTION11） Transaction：一条或多条SQL语句序列，它们组合在一起形成一个逻辑工作单元。形成事务的SQL语句通常紧密相关并执行相互依赖的一组操作。事务中的每条语句通常执行任务的一部分，它们全部组合起来才能完成任务。12） 大容量装载：sql语句块 | exists13） 多行insert语句：insert into table (col1, col2) Select expression1

27、4） 查询表达式：允许的操作链接(join)、并(union)、差(difference),取两表中，返回第一个表减去第二个表记录之后所剩记录、交(intersect)列出其订单超过$30000或现货量超过#30000的所有产品(union)列出其订单超过$30000且现货量超过#30000的所有产品(intersect)列出其订单超过$30000但销售价格小于$1000的所有产品(difference expect)15） 记录值比较：对两个结构化记录的比较，从首个字段值开始比较。16） 记录值子查询：记录值子查询不返回一个数据项，而是一行数据项，可以在SQL2表达式中使用这行数据项并与其他

28、记录相比较。 ；列出最高单价产品的所有订单的订单号和取得日期SELECT order_num, order_date FROM ORDERSWERE (MFR, PRODUCT) = (SELECT MFR_ID, PRODUCT_IDFROM PRODUCTSWHERE Price = (SELECT MAX(PRICE) FROM PRODUCTS)17） NULLIF (value, newValue)18） COLAESCE表达式：和case功能类似，但更简洁，处理null值 coalesce (value1, value2, 0000)19） CASE表达式：当DBMS遇到一个CAS

29、E表达式时，它计算第一个搜索条件，如果为true,那么CASE表达式的值是第一个结果表达式的值。如果第一个搜索条件的结果不是true，DBMS继续第二个搜索条件，依次递归。20） CAST表达式：可显示的要求DBMS使用CAST表达式在数据类型中进行转换，CAST语法：CAST 值表达式 AS 数据类型Select name, cast rep_office as varchar, hier_data as varcharFrom table1.21） 查询处理原则为了生成select语句的查询结果：1、 如果语句是select语句的一个union，把第二步到第七步应用到每条语句中，以生成它们的各个查询结果。2、 形成在from子句中命名的表的积。如果from子句命名了一个表，积就是那个表。3、 如果有一条where子句，应用它的搜索条件到积表的每一条记录，保留搜索条件为true的那些记录(去掉结果为false或null的那些结果)。如果having子句包含一个子查询，对于要测试的每一条记录，执行这个子查询。4、 如果有一条group by子句，安排积表中剩余的记录进到记录组中，以便在每一组中的记录在所有分组的字段中有同样的值。5、 如果有一条having子句，应用它的搜索条件到每一组中，保留其搜索条件为true的那些组，如果having子句包含一个子查询，对于正被测试