Oracle复习重点参考模板

1、第三、四、六章1、四个服务:数据控制台服务（OracleDBCconsoleorcl）管理服务（OracleoraDb10g_homeliSQL*Plus）网络监听服务（OracleoraDb10g_homelTNSListener）实例服务（OracleServiceORCL）2、全局数据库命名方式：数据库名.域名 3、编辑缓冲区 当缓冲区存有内容后，可以使用编辑命令对缓冲区进行修改。常用的编辑命令包括： A【PPEND】将指定的文本追加到缓冲区内当前行的末尾； C【HANGE】修改缓冲区当前行的文本； C/修改前/修改后 DEL删除缓冲区中的当前行文本； N用数值定位缓冲区中的当前行； I

2、【NPUT】在缓冲区当前行的后面新增加一行文本； ED【IT】以文本编辑器方式打开缓冲区，进行编辑。第七章:物理存储结构7.3数据文件及其管理1、数据库与表空间的关系： （1）、一个表空间可以包含几个数据文件，（2）、一个数据文件只能从属于一个表空间2、创建数据文件： （永久性）向表空间添加数据文件：Alter tablespaceadd datafile （临时性）向临时表空间添加临时数据文件：Alter tablespaceadd tempfile4、修改数据文件的大小手工改变数据文件的大小:alter database datafile设置数据文件为自动增长方式:alter databa

3、se 1 / 285、在下面几种情况下需要改变数据文件的可用性：（1）、要进行数据文件的脱机备份时，需要先将数据文件脱机；（2）、需要重命名数据文件或改变数据文件的位置时，需要先将数据文件脱机；（3）、如果Oracle在写入某个数据文件时发生错误，会自动将该数据文件设置为脱机状态，并且记录在警告文件中。排除故障后，需要以手动方式重新将该数据文件恢复为联机状态。（4）、数据文件丢失或损坏，需要在启动数据库之前将数据文件脱机。 6、删除某个临时表空间中的某个临时数据文件： Alter tablespacedrop tempfile7、备份控制文件备份控制文件：alter database back

4、up controlfile (1)、将控制文件备份为二进制文件 Alter database backup controlfile to trace; (2)、将控制文件备份为文本文件 Alter database backup controlfile to trace;7.5、重做日志文件的管理每个数据库至少需要两个重做日志文件，采用循环写的方式进行工作1、添加重做日志文件组成员注意事项： （1）、同一个重做日志文件组中的成员文件存储位置应尽量分散（保证安全） （2）、不需要指定文件大小。新成员文件大小由组中已有成员大小决定。重做日志文件3重做日志文件2重做日志文件1重做日志文件的工作过程

5、：LGWR重做日志缓冲区日志切换日志切换 （自动运行的小文件） 日志切换2、添加重建日志文件组 例：alter database add logfile group 4Alter database add logfile member2、 添加重做日志文件成员例：select group#,member,status from V$log;Select group#,member,status from V$log4、删除重建日志文件组：例：Select group#,status from v$log;Alter database drop logfile group 3;Alter dat

6、abase drop logfile group 4;5、删除重做日志文件成员：例：select group#,member,status from v$logAlter database drop logfile member;Alter database drop logfile member c:21.log;6、删除重做日志文件组成员语法n ALTER DATABASE DROP LOGFILE MEMBERp 删除注意事项n 只能删除状态为INACTIVE或UNUSED的重做日志文件组中的成员；若要删除状态为CURRENT的重做日志文件组中的成员，则需执行一次手动日志切换。n 如果数

7、据库处于归档模式下，则在删除重做日志文件之前要保证该文件所在的重做日志文件组已归档。n 每个重做日志文件组中至少要有一个可用的成员文件，即VALID状态的成员文件。如果要删除的重做日志文件是所在组中最后一个可用的成员文件，则无法删除。 7、 设置归档/非归档模式(1、2、3只能选一个设置，一般设置归档)1、 关闭数据库Shutdown immediate2、 启动数据库到MOUNT状态Startup mount 3、 使用alter database archivelog语句将数据库设置为归档模式。Alter database archivelog ;4、 或使用Alter database

8、noarchivelog语句将数据库设置为非归档模式。Alter database archivelog5、 打开数据库Alter database open;控制文件相关截图一个数据库至少有一个控制文件，但一般一个数据库中都有2或3个控制文件；1、 将控制文件备份到跟踪文件（文本文件）：Alter database backup controlfile to trace2、 将控制文件备份为二进制文件：Alter database backup controlfile to c:1.bkp3、 查询跟踪文件的位置:Show parameter user_dump_dest4、 关闭数据库，权

9、限不足时解决办法：Shutdown immediate(会提示权限不足)Conn sys/zhuyuli as sysdbaShutdown immediate5、 数据库分为三个状态：Nomount:创建实例;Mount: 加载控制文件;Open: 打开数据文件和重做日志文件; 第八章：逻辑存储结构数据库逻辑存储结构之间的关系如图：数据库段区表空间数据块 Block extent segment tablespace逻辑结构单元类型:数据块:block区:extent段:tegment表空间:tablespace(由小及大的顺序)表空间概念一个表空间对应一个或多个数据库文件，数据文件的大小决

10、定了表空间的大小，一个数据文件只能从属于一个表空间。数据库表空间分为：系统表空间、非系统表空间系统表空间分为：1、system表空间 2、sysaux表空间非系统表空间分为：1、撤销表空间（当初始化参数undo_management=auto时，系统使用撤销表空间自动进行回滚信息管理） 2、临时表空间 3、用户表空间 表空间的管理永久性表空间：表空间名称不能超过30个字符，必须以字母开头；表空间的类型包括普通表空间、临时表空间、撤销表空间；表空间中区的分配方式包括：自动扩展（autoallocate）和定制(uniform)；段的管理包括：自动管理(auto)和手动管理(manual)；1、创

11、建永久性表空间：临时性表空间：1、使用creattemporary tablespace语句创建临时表空间，用tempfile子句设置临时数据文件；2、临时表空间中区的分配方式只能是定制（Uniform）,而不能是Autoallocate；例题：为Orcl数据库创建一个临时表空间orcltemp1 Create temporary tablespace orcltemp1 tempfileD:orcaleproduct10.2.0oradataorclorcltemp1_1.dbfsize 20M Extent management local uniform size 15M;修改表空间扩展

12、表空间的三种方式:1、为表空间添加数据文件；2、改变数据文件大小；3、允许数据文件自动扩展；例题：修改user01.dbf数据文件的自动增长方式，每次增长5m,最大50m.Alter database datafileC:user01.dbfextent on next 5mMaxsize 50m;一些表空间必须处于联机状态：1、System表空间；2、正在进行事物回滚的撤销表空间；3、临时表空间；2、查看数据库是否处于存档模式：Archive log list3、 查询数据库中有哪些表空间：Select name from V$tablespace4、 设置上面表空间（sysaux）为联/脱

13、机状态：Alter tablespace sysaux onlineAlter tablespace sysaux offline表空间的读写性：1、表空间只有在满足下列要求后才可以转为只读状态：（1）、表空间处于联机状态；（2）、表空间中不能存在任何活动的回滚段；（3）、如果表空间正在进行联机数据库备份，则不能将它设置为只读状态；查询表空间名字和表空间数据文件名：Select file_name,tablespace_name from dba_data_files;备份表空间（tbs2）：Alter tablespace tbs2 begin backup;停止表空间的备份：Alter t

14、ablespace tbs2 end backup;删除表空间：语法：Drop tablespace tablespace_name；删除参照完整性约束，应带有子句Cascade constraints；例题：删除orcl数据库的ORCL TBS1表空间及其所有内容 Drop tablespace orcltbs1 including contents; 数据块数据块包括：块头部、存储区块头部分包括标题、表目录、行目录三部分；存储区包括：行数据区、空闲区 段段的类型：1、数据段；2、索引段；3、临时段；4、回滚段；回滚信息包括:1、修改前；2、修改后；回滚段的作用： 1、事物回滚；2、数据库恢

15、复3、数据的读一致性；4、闪回查询； （见书P144）回滚段存储的是：1、事物修改的数据原值；2、新值；例题1、创建一个回滚表空间UNDOTBS,并作为数据库的撤销表空间 Create undo tablespace undotbs datafileC:undo.dbfsize 5m;Alter system set UNDO_DATASPACE=undotbs;例题2、创建一个本地管理方式下的临时表空间TEMPTBS，并将该表空间作为数据库实例默认临时表空间 Create temparory tablespace temptbs tempfile C:temptbs.dbfsize 5m E

16、xtent management local uniform size 5m; Alter database default emporary tablespace temptbs;例题3、创建一个本地管理方式下的表空间USERTBS2,要求每个区分大小为20m Create tablespace userstbs1 datafile C:userstbs.dbfsize 20m Extent management local autoallocate;例题4、创建一个本地管理方式下的表空间USERTBS2，要求每个区分大小为512KB Create tablespace usertbs2 d

17、atafile C:usertbs2.dbfsize 10m Extent management local uniform size 512KB例题5、删除表空间usertbs2，同时删除该表空间的内容以及对应的操作系统文件 Drop tablespace usertbs2 including contents; 第九章、数据库实例1、SGA数据高速缓存区作用: 1、存储从数据文件中检索出来的数据拷贝。应用程序要访问的数据必须从磁盘的数据文件读到数据缓冲区中处理。2、在数据缓冲区中被修改后的数据由数据写入进程写到硬盘的数据文件中永久保存。3、提高获取和更新数据的性能。重做日志缓冲区作用:1、

18、 重做日志缓冲区用于缓存用户对数据库进行修改操作时生成的重做记录。 2、 为了提高工作效率，重做记录并不是直接写入重做日志文件中，而是首先被服务器进程写入重做日志缓冲区中，在一定条件下，再由日志写入进程（LGWR）把重做日志缓冲区的内容写入重做日志文件中做永久性保存。在归档模式下，当重做日志切换时，由归档进程（ARCH）将重做日志文件的内容写入归档文件中 共享池功能：1、共享池用于缓存最近执行过的SQL语句、PL/SQL程序和数据字典信息，是对SQL语句、PL/SQL程序进行语法分析、编译、执行的区域。 2、 PGAPGA功能：PGA 是为每一个与Oracle数据库连接的用户保留的内存区，主要

19、存储该连接使用的变量信息和与用户进程交换的信息。是非共享的，只有服务进程本身才能访问它自己的PGA区PGA是由排序区、会话区、游标区、堆栈区组成 9.4、Oracle后台进程1、DBWR（数据库写入进程）功能：数据库写入进程负责把数据高速缓冲区中已经被修改过的数据成批写入数据文件中永久保存，同时使数据高速缓冲区有更多的空闲缓存块。 2、LGWR（日志写入进程）功能： 日志写入进程负责把重做日志缓冲区的重做记录写入重做日志文件中永久保存。3、SMON（系统监控进程）功能：（选择题）1）、在实例启动时负责对数据库进行恢复；2）、回收不再使用的临时空间。3）、将各个表空间的空闲碎片合并4、PMON（

20、进程监控进程）功能：1）、负责恢复失败的用户进程或服务器进程，并且释放进程所占用的资源；2）、清除非正常中断的用户进程留下的孤儿会话，回退未提交的事务，释放会话所占用的锁、SGA、PGA等资源；3）、监控调度进程和服务器进程的状态，如果它们失败，则尝试重新启动它们，并释放它们所占用的各种资源。5、ARCH（归档进程）功能：归档进程负责在日志切换后将已经写满的重做日志文件复制到归档目标中，防止写满的重做日志文件被覆盖p 注：最多可启动10个归档进程（ ARC0 ARC9） 第十章、模式对象例题1、在当前模式下创建一个名为emp的表 Create table emp( Emp_id number(

21、6,0) primary key, Frist_name varchar2(20), Last_name varchar2(25) not null, email varchar2(25) not null, job_id varchar2(25) not null, salary number(8,2) check(salary>0), commission_pct number(2,2), manager_id number(6,0), department_id number(4,0) ) tablespace users;1、创建临时表：1、临时表中的数据在特定条件下自动释放，但

22、其结构将一直存在。2、根据临时表中数据被释放的时间不同，临时表分为事务级别的临时表和会话级别的临时表两类。n ON COMMIT DELETE ROWS /事务级临时表n ON COMMIT PRESERVE ROWS /会话级临时表语法：n 使用CREATE GLOBAL TEMPORARY TABLE语句创建临时表n 使用ON COMMIT子句说明临时表的类型，默认为事务级别的临时表。2、利用子查询创建表例题：创建一个会话级临时表，保存部门号、部门人数和部门的平均工资。 Create global temporary table dept_temp On commit preserve r

23、ows As Select department_id,count(*)num,avg(salary) avgsal From emp Group by department_id;10.3.2 表约束2、主键约束(primary key):特点: 建立主键约束的同时,在该列上建立一个唯一索引,可以为它指定存储位置和存储参数; 3、唯一性约束(unique):特点: Oracle 自动在唯一性约束列上建立一个唯一性约束,可以为它指定存储位置和存储参数;4、Null和not null 的特点：n 在同一个表中可以定义多个NOT NULL约束；n 只能是列级约束。列的添加、删除、修改5、添加列:n

24、 语法ALTER TABLE table_name ADD(new_column_name datatypeNOT NULLDEFAULT value); n 示例ALTER TABLE emp ADD(phone_number VARCHAR2(20),hiredate DATE DEFAULT SYSDATE NOT NULL); （Default默认值 sysdate当前系统时间）例题：创建一个Student23表，属性为（Sno,Sage,Cno,score） 创建一个Student23表，属性为（Cno,Cname）1、 为Student23表添加一个列，列名为cno，同时为该列添加

25、外键约束，参照course23表中的cno。create table student23(sno number primary key,sage number,score number)create table course23(cno varchar(20) ,cname number)alter table student23add constraint S_FK2 foregin key(cno) references course23(cno);将列标记为UNUSED，然后进行删除:ALTER TABLE table_name SET UNUSED COLUMN column_name

26、|(column1_name,column2_name,)CASCADE CONSTRAINTS; 示例:ALTER TABLE player SET UNUSED COLUMN sage;ALTER TABLE player SET UNUSED (sname,resume);ALTER TABLE player DROP UNUSED COLUMNS;10.4、索引1、索引使用的原则： （1） 对于取值范围很大的列应当创建B树索引； （2）对于取值范围很小的列应当创建位图索引；2、创建索引的语法：n create unique|bitmap index index_namen ON tab

27、le_name(column_nameASC|DESC,|n expression) n REVERSEn parameter_list；n 具体说明：n UNIQUE表示建立惟一性索引；n BITMAP表示建立位图索引；n ASC/DESC用于指定索引值的排列顺序，ASC表示按升序排序，DESC表示按降序排序，缺省值为ASC；n REVERSE表示建立反键索引；n parameter_list用于指定索引的存放位置、存储空间分配和数据块参数设置。10.6.1 创建分区表例题1： 创建一个表Student，属性分别为姓名、学号、年龄、性别，学号为主键，数据类型自定义，创建后在学号列上创建一个反

28、键索引。 Create table Student(sname varchar(10),Sno varchar(10) primary key,Sage varchar(10),Sex varchar(10) create Index student_sno_index on student(sno) reverse tablespace users;例题2、创建一个分区表，将学生信息根据其出生日期进行分区，将1980年1月1日前出生的学生信息保存在orcltbs1表空间中，将1980年1月1日到1990年1月1日出生的学生信息保存在orcltbs2表空间中，将其他学生信息保存在orcltbs

29、3表空间中。 Create table student_range( Sno number(6) primary key, Sname varchar2(10), Sage int, Birthday date) Partition by range(birthday) ( partition p1 values less than (to_date(1980-1-1,YYYY-MM-DD)tablespace orcltbs1, Partition p2 values less than (to_date(1990-1-1,YYYY-MM-DD)tablespace orcltbs2, Pa

30、rtition p3 values less than(maxvalue) Tablespace orcltbs3 storage(initial 10M next 20M) Storage(initial20m next 10m maxextents 10);例题3、创建一个分区表，将学生信息按照性别不同进行分区，男生信息保存在表空间ORCLTBS1中，而女生信息保存在ORCLTBS2中。 Create table student_list( Sno number(6) primary key, Sname varchar2(10), Sex char(2) check(sex in (M,

31、F) ） Partition by list(sex) ( partition student_male values(M)tablespace orcltbs1, Partition student_female values(F)tablespace orcltbs2);例题4、创建一个分区表，根据学号将学生信息均匀分布到ORCLTBS1和ORCLTBS2两个表空间中。 Create table student_hash( Sno number(6) primary key, Sname varchar2(10), ) Partition by hash(sno) ( partition

32、p1 tablespace orcltbs1, Partition p2 tablespace orcltbs2); 第14章、数据库启动与关闭1、 启动数据库Startup nomount读取数据库初始化参数文件，创建并启动数据库实例。 在NOMOUNT模式下可以执行下列操作：n 创建一个新的数据库;n 重建数据库的控制文件; 第11章、SQL语言基础事务里面的操作，要么全做要么全不做。Begin transaction 表示事务的开始；Commit 表示事务的结束；Distinct 消除重复列WHERE condition中的where是对表中所有记录进行筛选；HAVING group_c

33、ondition中的having是对分组后的记录进行筛选；GROUP BY column_name1,column_name2,分组查询1、无条件查询p 查询所有列SELECT * FROM employees; p 查询指定列SELECT department_id,department_name FROM departments; p 使用算术表达式SELECT employee_id,salary*0.8 FROM .employees;p 使用字符常量SELECT employee_id, 'salary is: ', salary FROM employees; p

34、使用函数SELECT employee_id,UPPER(first_name) FROM employees; p 改变列标题SELECT employee_id empno,salary sal FROM employees;（在旧的列名后添加一个新列名）2、有条件查询查询满足条件的元组可以通过WHERE 子句实现。WHERE条件中常用的运算符号p 字符匹配n %（百分号）代表任意长（或长度为0）的字符串。n _（下划线）代表任意单个字符。n ESCAPE：转义字符举例：1、SELECT * FROM employees WHERE last_name LIKE '%S%'

35、 翻译：从员工表中查询名字中含有S的员工名2、SELECT * FROM employees WHERE first_name LIKE '_a%'3、SELECT * FROM employees WHERE first_name LIKE '%x_%'ESCAPE 'x'p 逻辑操作n 用逻辑运算符NOT、AND和OR来联结多个查询条件。n 优先级：NOT、AND、OR（用户可以用括号改变优先级）。 n IN谓词是集合,实际上是多个OR运算的缩写。p 升序、降序排序n ASC: 升序 （默认值）； DESC: 降序3、查询统计注意事项：如果对

36、查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。3、 外连接( )左外连接、右外连接、全外连接左外连接：左外连接是指在内连接的基础上，将连接操作符左侧表中不符合链接条件的记录加入结果集中，与之对应的操作链接符右侧表列用Null填充。u 左外连接语法：标准SQL语句的连接方式n SELECT table1.column, table2.column, n FROM table1 LEFT JOIN table2, n ON table1.column <operator> table2.column,;例题：查询10号部门的部门名、员工号、员工名和所

37、有其他部门的名称，语句为SELECT department_name,employee_id,first_name,last_name FROM departments d LEFT JOIN employees eON d.department_id=e.department_id AND d.department_id=10;右外连接：右外连接是指在内连接的基础上，将连接操作符右侧表中不符合链接条件的记录加入结果集中，与之对应的操作链接符左侧表列用Null填充。u 左外连接语法：标准SQL语句的连接方式n SELECT table1.column, table2.column, n FRO

38、M table1 LEFT JOIN table2, n ON table1.column <operator> table2.column,;例题：查询20号部门的部门名称及其员工号、员工名，和所有其他部门的员工名、员工号，语句为SELECT employee_id,first_name,department_name FROM departments d RIGHT JOIN employees eON d.department_id=e.department_id AND d.department_id=20;p 全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条

39、件的记录加入结果集中。p 在Oracle数据库中，全外连接的表示方式为n SELECT table1.column, table2.column, n FROM table1 FULL JOIN table2, n ON table1.column1 = table2.column2;p 查询所有的部门名和员工名，语句为n SELECT department_name,first_name,n last_namen FROM employees e FULL JOIN n departments d ON n e.department_id=d.department_id; 11.3.5、子查

40、询1、子查询作用（1）、在INSERT或CREATE TABLE语句中使用子查询，可以将子查询的结果写入到目标表中；（2）、在UPDATE语句中使用子查询可以修改一个或多个记录的数据；（3）、在DELETE语句中使用子查询可以删除一个或多个记录在WHERE和HAVING子句中使用子查询可以返回的一个或多个值。（2）、单行单列子查询例题：查询比105号员工工资高的员工的员工号、员工名、员工工资信息。n SELECT employee_id,first_name,last_name,salary n FROM employeesn WHERE salary>(SELECT salary FR

41、OM n employees WHERE employee_id=105); （3）、多行单列子查询例题1、查询与50号部门某个员工工资相等的员工信息。SELECT employee_id,first_name,last_name,salary FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id=50); 例题2、查询比50号部门某个员工工资高的员工信息。SELECT employee_id,first_name,last_name,salary FROM employees WHER

42、E salary>ANY(SELECT salary FROM employeesWHERE department_id=50);例题3、查询比50号部门所有员工工资高的员工信息。SELECT employee_id,first_name,last_name,salary FROM employees WHERE salary>ALL(SELECT salary FROM employees WHERE department_id=50);（4）、单行多列子查询例题1、查询与169号员工的工资、工种都相同的员工的信息。SELECT employee_id,first_name,last_name,salary,job_idFROM employees WHERE (salary,job_id)=(SELECT salary,job_id FROMemployees WHERE employee_