本周工作总结：
无
本周学习总结：
目录
2.3 Database Configuration Assistant
一、环境配置及常用工具
1.Oracle 11g基础配置
可以参考以下博文。
oracle 11g安装后的配置及使用_jrliling的博客-CSDN博客_oracle安装完成后怎么使用
https://blog.csdn.net/qq_37273784/article/details/81699872我在实际操作中遇到的问题是新建并删除一个示例数据库后，之前的数据库仍然无法打开，这个情况出现的原因是注册表中"KEY_OraDb11g_home1"里面"ORALCE_SID"参数仍然是原本的数据库名，改为需要使用的数据库名即可。
这部分主要是设置，遵照网络上的攻略，遇到问题查找即可解决。
2.Oracle 数据库管理工具
2.1 SQL * PLUS
利用sql*plus与oracle进行交互。注意利用exit/quit 退出，不要直接关闭窗口。
Oracle数据库实例的启动过程分为3步骤，分别是启动实例、加载数据库、打开数据库。sql*plus中命令为：
STARTUP [nomount | mount | open | force] [resetrict] [pfile=filename]关闭数据库实例也分为3个步骤，分别是关闭数据库、卸载数据库、关闭Oracle实例。sql*plus中命令为：
SHUTDOWN [normal | transactional | immediate | abort]2.2 Oracle Enterprise Manager
OEM是采用直观而且方便的图像化界面来控制和管理ORACLE数据库的工具集，它包含大量对DBA有用的工具。在oracle中，数据库管理和操作都可以使用OEM来完成，如创建用户和角色，权限分配，创建方案对象，进行存储管理等。
详细操作和设置可见：
OEM(Oracle Enterprise Manager)配置及使用_鲨鱼胃的博客-CSDN博客_oem oraclehttps://blog.csdn.net/shayuwei/article/details/90168100个人体会是该工具可以以可视化的方式直观的显示数据库的各项状态，并提供了一定的图形化的管理功能，可以方便使用。
2.3 Database Configuration Assistant
利用此工具可以实现便捷的对数据库的创建和修改。具体操作详见此博文：
3.Oracle IDE
Oralce SQL Developer 简化了Oracle 数据库的开发和管理，提供完整的端到端的 PL/SQL 应用开发。
我的感受是使用了之后，不用一条一条在命令行中执行语句，同时可以更方便，直观的随时查阅表结构、视图、索引、程序包等数据库中的很多信息，方便了开发。
4. 备份与恢复工具
数据是极为宝贵的财富，因此备份和恢复功能几乎是必要的功能。Oracle提供了丰富的工具实现全数据库的备份和恢复以及对部分数据的备份和恢复功能。
4.1 RMAN工具
RMAN（Recovery Manager）是随Oracle服务器软件一同安装的工具软件，它可以用来备份和恢复数据库文件、归档日志和控制文件，用来执行完全或不完全的数据库备份和恢复。
具体操作方式和相关注意事项可以参考此博文：
Oracle RMAN备份与还原 - Latiny - 博客园 (cnblogs.com)https://www.cnblogs.com/Latiny/p/6920428.html我的使用感受是，该工具的主要优点是支持增量备份，仅备份有效文件，操作相对简单。但缺点是备份过程不透明，只有使用Rman才能恢复。
同时备份信息会纪录在控制文件中，因而控制文件非常重要，必须多端备份。
4.2 数据泵导出和导入工具（EXPDP和IMPDP）
数据泵导出使用工具EXPDP将数据库对象的元数据（对象结构）或数据导出到转储文件中。而数据泵导入则是使用工具IMPDP将转储元件中的元数据及其数据导入到Oracle数据库中。假设EMP表被意外删除，那么可以使用IMPDP工具导入EMP的结构信息和数据。
使用数据泵导出或导入数据时，可以获得如下好处：
- 数据泵导出与导入可以实现逻辑备份和逻辑恢复。通过使用EXPDP，可以将数据库对象备份到转储文件中；当表被意外删除或其他误操作时，可以使用IMPDP将转储文件中的对象和数据导入到数据库中。
- 数据泵导出和导入可以在数据库用户之间移动对象。例如，使用EXPDP可以将SCOTT模式中的对象导出并存储在转储文件中，然后再使用IMPDP将转储文件中的对象导入到其他数据库模式中。
- 使用数据泵导入可以在数据库之间移动对象。
- 数据泵可以实现表空间的转移，即将一个数据库的表空间转移到另一个数据库中。
具体命令和使用方法可以参照此博文。
expdp / impdp 用法详解_荡剑江湖-CSDN博客_impdphttps://blog.csdn.net/qq_25034619/article/details/81382084
4.3 SQL loader工具
Oracle提供的数据加载工具SQL*Loader可以将外部文件中的数据加载到Oracle数据库，SQL*Loader支持多种数据类型（如日期型、字符型、数据字符等），即可以将多种数据类型加载到数据库。
使用SQL*Loader导入数据时，必须编辑一个控制文件（.CTL）和一个数据文件（.DAT）。控制文件用于描述要加载的数据信息，包括数据文件名、数据文件中数据的存储格式、文件中的数据要存储到哪一个字段、哪些表和列要加载数据、数据的加载方式等。
根据数据的存储格式，SQL*Loader所使用的数据文件可以分为两种：
- 自由格式加载：如果要加载的数据没有一定格式，则可以使用自由格式加载，控制文件将用分隔符将数据分割为不同字段中的数据。
- 固定格式加载数据 ：如果数据文件中的数据是按一定规律排列的，可以使用固定格式加载，控制文件通过数据的固定长度将数据分割。
控制文件的书写格式可见此博文：
SQL*Loader使用方法_anjichan4261的博客-CSDN博客https://blog.csdn.net/anjichan4261/article/details/101321483示例如下：
LOAD DATA
INFILE 'C:\Users\Shen_potato\Desktop\emps.csv'
into table emp10
FIELDS TERMINATED BY ","
(
virtual_column FILLER,
EMPNO "seq_eseq.nextval",
ENAME,
JOB,
MGR,
HIREDATE date 'yyyy-mm-dd',
SAL,
COMM,
DEPTNO
)
三、Oracle编程基础
1. SQL
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL语言类似于脚本语言，在数据库这一领域有着显著的优势：
- 集合性
- 统一性
- 易于移植性
SQL语言主要分为以下几个类别：
- 数据查询语言（DQL）
- 数据操纵语言（DML）
- 事务控制语言（TCL）
- 数据定义语言（DDL）
- 数据控制语言（DCL）
2. 数据查询
数据查询使用DQL进行，也是最常用的命令，用来对数据库中的数据进行查阅。
具体语法如下：
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
select语句可以实现非常复杂的查找和统计，实现多表关联的查找和统计。语法的全面解读和详细用法可见此篇博文：
1.在Oracle中，GROUP BY主要与聚合函数一起使用，用于统计组中的数据情况。示例如下：
该语句实现了统计各职业的平均工资并分组输出的作用。
select JOB,avg(SAL)
from emp
group by JOB;
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000同时，GROUP BY之后必须包括语句中所有查阅的列，否则就会报错。
select JOB,SAL
from emp
group by JOB;
SQL 错误: ORA-00979: 不是 GROUP BY 表达式
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:我的理解是，如果查找到的数据复杂度多于被分组的数据，那么就意味着无法分组，分组必然会强行损失信息。
2.使用子查询和连接查询可以实现单命令对多个表实现复杂的查询，子查阅自身也可以嵌套子查阅。
例如本例中利用一条语句实现了联动DEPT、EMP、SALGRADE三个表的复杂子查阅。
select LOC,ENAME,SAl
from DEPT
join EMP
on DEPT.DEPTNO=EMP.DEPTNO
where SAL > (select AVG(LOSAL)
from SALGRADE);
LOC ENAME SAL
------------- ---------- ----------
DALLAS JONES 2975
CHICAGO BLAKE 2850
NEW YORK CLARK 2450
DALLAS SCOTT 3000
NEW YORK KING 5000
DALLAS FORD 3000
已选择 6 行。3.数据操作
对表中的数据进行操作的语句，包括插入数据（INSERT语句）、更新数据（UPDATE语句）、删除数据（DELETE语句和TRUNCATE语句）。通常使用数据操纵语言（DML）进行。
3.1 INSERT 语句
INSERT INTO table_name [(column_name1[,column_name2]…)] VALUES(express1[,express2]…)- 首先，指定要插入的表的名称 -
table_name。 - 其次，在圆括号内指定逗号分隔列名的列表。
- 第三，指定对应于列列表的逗号分隔值列表。
如果值列表与表列具有相同的顺序，则可以跳过不指定列的列表，但这不被认为是一种好的做法：
INSERT INTO table_name
VALUES (value_1, value_2, value_3, ..., value_n);
注意：如果从语句中排除一列或多列，则必须指定列列表，因为Oracle需要它与值列表中的值相匹配。在INSERT语句中省略的列将使用缺省值(如果可用)或者如果列接受NULL值，则使用NULL值。
3.2 UPDATE语句
语法如下：
UPDATE table_name SET {column_name1=express1[,column_name2=express2...] | (column_name1[,column_name2…])=(selectSubquery)} [WHERE condition] - 更新数字列则可以直接提供数字值，但是字符列或其他列则数据值需用单引号引住
- 更新的数据必须满足约束条件
- 提供的数据 必须 与数据列类型匹配
3.3 DELETE语句
语法如下：
DELETE FROM table_name [WHERE condition]- 如果不使用where子句,将表中所有数据全部删除 delete from test;
- 如果要删除某列的值,delete是不可以做到的
- 删除整个表 drop table 表名
- 当使用delete的时候,一定要考虑表之间参照完整性
3.4 TRUNCATE语句
语法如下：
TRUNCATE TABLE table_nameTRUNCATE语句同样可以删除，但是作用是清空整个表格。
需要格外注意的是，TRUNCATE是DDL语句，运行后会自动提交，包括之前其它未提交的会话，因而一旦清空无法回退。表格里的数据被清空，存储空间被释放。只有表格的创建者或者其他拥有删除任意表格权限的用户（如DBA）才能清空表格。
3.5 综合举例练习
查询所在部门所在城市为'South San Francisco'的员工
查找和id为143在同一个部门和同一个岗位的比他工资高的员工。
查询总工资最多的部门，显示部门编号，部门名
select * from(
select sum(tmp1.salary) as total_salary, tmp1.department_id, tmp1.department_name from (
select e.*,d.department_name from hr.employees e left join hr.departments d on e.department_id = d.department_id
)tmp1 group by tmp1.department_id, tmp1.department_name order by total_salary desc
)tmp2 where tmp2.total_salary = (
select max(total_salary) from (
select sum(tmp3.salary) as total_salary, tmp3.department_id, tmp3.department_name from (
select e.*,d.department_name from hr.employees e left join hr.departments d on e.department_id = d.department_id
)tmp3 group by tmp3.department_id, tmp3.department_name order by total_salary desc
))
为hr方案创建表“USERS”，要求有列：USER_ID,USER_NAME,BIRTHDATE,AGE,SALARY,photo,USER_COMMENT(个人履历)。
create table hr.users (
user_id number,
user_name varchar2(30),
BIRTHDATE date,
AGE number,
SALARY number,
photo varchar2(30),
USER_COMMENT varchar2(3000)
)
select * from hr.users
为“USERS” 表添加一列 “DEPARTMENT_ID”;
alter table hr.users add (department_id number)
删除表“USERS”中的age列
alter table hr.users drop column age
删除表“users”
drop table hr.users4. PL/SQL语言
PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
PL/SQL相比于基本的SQL语句具有更为明显的一些优点：
- 能够使一组SQL语句的功能更具模块化程序特点；
- 采用了过程性语言控制程序的结构；
- 可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；
- 具有较好的可移植性，可以移植到另一个Oracle数据库中；
- 集成在数据库中，调用更快；
- 减少了网络的交互，有助于提高程序性能。
我的体会是PL/SQL与C语言更加相似，具有一定的面向过程的特点，同时引入了变量函数游标等工具，更加便于编程和使用。
4.1 基本语法
PL/SQL是块结构语言。每个块由三个子部分组成 ：
- 声明部分 - 此部分是以关键字
DECLARE开头。这是一个可选部分，并定义了程序中要使用的所有变量，游标，子程序和其他元素。- 可执行命令部分 - 此部分包含在关键字
BEGIN和END之间，这是一个强制性部分。它由程序的可执行PL/SQL语句组成。它应该有至少一个可执行代码行，它可以只是一个NULL命令，表示不执行任何操作。- 异常处理部分 - 此部分以关键字
EXCEPTION开头。这是一个可选部分，它包含处理程序中错误的异常。每个PL/SQL语句以分号(
;)结尾。 使用BEGIN和END可以将PL/SQL块嵌套在其他PL/SQL块中。 以下是PL/SQL块的基本结构。
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;其他的内容如下：
- 标识符
- 注释规范
- 基本和特殊数据类型
- 变量和常量定义
可参考此教程：
PL/SQL基本语法 - PL/SQL教程™ (yiibai.com)https://www.yiibai.com/plsql/plsql_basic_syntax.html
4.2 流程控制
1.选择语句：
set serveroutput on
declare
month int:=10;
begin
if month >= 0 and month <= 3 then
dbms_output.put_line('这是春季');
elsif month >= 4 and month <= 6 then
dbms_output.put_line('这是夏季');
elsif month >= 7 and month <= 9 then
dbms_output.put_line('这是秋季');
elsif month >= 10 and month <= 12 then
dbms_output.put_line('这是冬季');
else
dbms_output.put_line('对不起，数据不合法！');
end if;
end;
/2.循环语句：
1．loop语句
loop
plsql_sentence;
exit when end_condition_ exp
end loop;
2．while语句
while condition_expression loop
plsql_sentence;
end loop;
3．for语句
for variable_ counter_name in [reverse] lower_limit..upper_limit loop
plsql_sentence;
end loop;
set serveroutput on
declare
sum_i int:= 0;--定义整数变量，存储整数和
i int:= 0;--定义整数变量，存储自然数
begin
loop--循环累加自然数
i:=i+1;--得出自然数
sum_i:= sum_i+i;--计算前n个自然数的和
exit when i = 100;--当循环100次时，程序退出循环体
end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);--计算前100个自然数的和
end;
/4.3 游标
游标：游标本质是一块内存区域，由系统或用户以变量的形式定义，可以提高数据库数据处理速度。游标的工作机制是一种能从包括多行数据记录的结果集中每次提取一行记录的机制，即游标提供了在逐行的基础上操作表中数据的方法。
注意：类似于java中迭代器，c语言中的指针
声明游标
CURSOR IS
打开游标
OPEN
获取游标
FETCH INTO
关闭游标
CLOSE
示例如下所示：
set serveroutput on
declare
/*声明游标，检索雇员信息*/
cursor cur_emp (var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;
type record_emp is record --声明一个记录类型（RECORD类型）
(
/*定义当前记录的成员变量*/
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;--声明一个record_emp类型的变量
begin
open cur_emp('MANAGER');
fetch cur_emp into emp_row;--先让指针指向结果集中的第一行
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||',工资是'||emp_row.var_sal);
fetch cur_emp into emp_row;--让指针指向结果集中的下一行
end loop;
close cur_emp;
end;
/5. 事务控制
事务在数据库中是工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制，可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。事务的特点如下：
- 原子性(Atomicity)：一个事务里面所有包含的SQL语句都是一个整体，是不可分割的，要么不做，要么都做。
- 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
- 隔离性(Isolation)：数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务在并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
- 持久性 (Durability) : 当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。
5.1 事务的状态
5.2 事务提交、回滚
提交事务是指把对数据库进行的全部操作持久性地保存到数据库中，这种操作通常使用COMMIT语句来完成。 事务的提交方式包括如下3种： 显式提交：使用commit命令使当前事务生效。 自动提交：在SQL *Plus里执行“set autocommit on;”命令。 隐式提交：除了显式提交之外的提交，如发出DDL命令、程序中止和关闭数据库等
COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];参数
- WORK：可选的。它被 Oracle 添加为符合 SQL 标准。使用或不使用 WORK 参数来执行 COMMIT 将产生相同的结果。
- COMMENT clause：可选的。 它用于指定与当前事务关联的注释。 该注释最多可以包含在单引号中的 255 个字节的文本中。 如果出现问题，它将与事务ID一起存储在名为 DBA_2PC_PENDING 的系统视图中。
- WRITE clause：可选的。 它用于指定将已提交事务的重做信息写入重做日志的优先级。 用这个子句，有两个参数可以指定：
- WAIT 或 NOWAIT (如果省略，WAIT是默认值)
- IMMEDIATE 或 BATCH(IMMEDIATE是省略时的默认值)
- FORCE clause：可选的。 它用于强制提交可能已损坏或有疑问的事务。 有了这个子句，可以用3种方式指定FORCE：
- FORCE'string'，[integer]或FORCE CORRUPT_XID'string' 或 FORCE CORRUPT_XID_ALL
rollback与savepoint往往组合出现，可使用类似的形式实现对表相关情况的操作，回滚。示例如下：
declare
begin
insert into student_info (sno, name, sex) values (1, '张三', '女');
savepoint a1;
insert into student_info (sno, name, sex) values (2, '李四', '男');
savepoint a2;
rollback a2;
rollback a1;
end;
5.3 锁机制
在Oracle中，提供了两种锁机制。
（1）共享锁（share lock）
共享锁通过数据存取的高并行性来实现。如果获得了一个共享锁，那么用户就可以共享相同的资源。许多事务可以获得相同资源上的共享锁。例如，多个用户可以在相同的时间读取相同的数据。
（2）独占锁（exclusive lock）
独占锁防止共同改变相同的资源。假如一个事务获得了某一资源上的一个专用锁，那么直到该锁被解锁，其它的事务才能修改该资源，但允许对资源进行共享。例如，假如一个表被锁定在独占模式下，它并组织其它用户从同一个表得到数据。 所有的锁在事务期间被保持，事务中的SQL语句所做的修改只有在事务提交时才能对其他事务可用。Oracle在事务提交和回滚事务时，释放事务所使用的锁。
5.4 死锁
当两个或者多个用户等待其中一个被锁住的资源时，就有可能发生死锁现象。对于死锁，Oracle自动进行定期搜索，通过回滚死锁中包含的其中一个语句来解决死锁问题，也就是释放其中一个冲突锁，同时返回一个消息给对应的事务。用户在设计应用程序时，要遵循一定的锁规则，尽力避免死锁现象的发生。
5.5 表锁和事务锁
为了使事务能够保护表中的DML存取以及防止表中产生冲突的DDL操作，Oracle获得表锁（TM）。例如，假如某个事务在一张表上持有一个表锁，那么它会阻止任何其他事务获取该表中用于删除或改变该表的一个专用DDL锁。下表列出了不同的模式。当执行特定的语句时，由RDBMS获得这些模式的表锁。通过V$lock动态表可以查看锁的相关信息。模式列的值分别为2、3或6。数值2表示一个行共享锁（RS）；数值3表示一个行独占锁（RX）；数值6表示一个独占锁（X）。当一个事务发出如下表所列出的语句时，将获得事务锁（TX）。事务锁总是在行级上火的。事务锁独占地锁住该行，并阻止其他事务修改行，知道持有该锁的事务回滚或提交数据为止。
6. 数据定义语言（DDL）
数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。
与DML不同，DDL控制的并非其中的数据，而是数据库中的所有对象。同时DDL语句是隐式提交的，意味着不能回滚。
6.数据库对象
- 用户
- 视图
- 索引
- 触发器
- 序列
- 规则
- 储存过程
- 函数
6.2 用户
用户创建：
SQL> CREATE USER username IDENTIFIED BY password
2 DEFAULT TABLESPACE table_spacename
3 TEMPORARY TABLESPACE temp_table_spacename;用户删除：
SQL> DROP USER username CASCADE; DROP USER 是DDL语句，不可退回，确认用户数据确实不再需要，再执行删除操作，在实际生产中，可以先将用户锁定一段时间，确定无用户访问，可以安全删除。
用户锁定、解锁
SQL> ALTER USER username ACCOUNT LOCK; 锁定
SQL> ALTER USER username ACCOUNT UNLOCK; 解锁查看用户状态
SQL> SELECT username,account_status from dba_users;6.3 视图
视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。
创建视图
权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.
语法:
create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,...) ]
as
select ...
[ with check option ] [ constraint constraint_name ]
[ with read only ];6.4 索引
创建索引
CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2 --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1] --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT]; --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用修改索引
1）重命名索引
alter index index_sno rename to bitmap_index;2) 合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
alter index index_sno coalesce;3)重建索引
alter index index_sno rebuild;查看索引
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
示例如下：
create index index_sno on student('name');
select * from all_indexes where table_name='student';6.5 触发器
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。
因此触发器不需要人为的去调用，也不能调用。触发器的触发条件其实在定义的时候就已经设定好了。这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end其中：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
示例如下：
create or replace trigger tri_ddl_oper
before create or alter or drop
on scott.schema --在scott模式下，在创建、修改、删除数据对象之前将引发该触发器运行
begin
insert into ddl_oper_log values(
ora_dict_obj_name,--操作的数据对象名称
ora_dict_obj_type,--对象类型
ora_sysevent,--系统事件名称
ora_login_user,--登录用户
sysdate);
end;
/
create or replace trigger tri_insert_good
before insert
on goods --关于goods数据表的id，在插入id列之前，引起该触发器的运行
for each row --创建行级触发器
begin
select seq_id.nextval
into :new.id
from dual;--从序列中生成一个新的数值，赋值给当前插入行的id列
end;
/6.6 序列
序列: Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。由于oracle中没有设置自增列的方法，所以我们在oracle数据库中主要用序列来实现主键自增的功能。
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
6.7 存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。
语法格式如下：
create [or replace] procedure pro_name [(parameter1[,parameter2]…)] is|as begin plsql_sentences; [exception] [dowith _ sentences;] end [pro_name];1．IN模式参数 这是一种输入类型的参数，参数值由调用方传入，并且只能被存储过程读取。
- 指定名称传递 指定名称传递是指在向存储过程传递参数时需要指定参数名称，即参数名称在左侧，中间是赋值符号“=>”，右侧是参数值，其语法格式如下： pro_name(parameter1=>value1[,parameter2=>value2]…)
- 按位置传递 指定名称传递参数虽然直观易读，但也有缺点，即参数过多时，会显得代码冗长，反而变得不容易阅读。这样用户就可以采取按位置传递参数，采用这种方式时，用户提供的参数值顺序必须与存储过程中定义的参数顺序相同。
- 混合方式传递 混合方式就是将前两种方式结合到一起，这样就可以兼顾二者的优点。
2．OUT模式参数 这是一种输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中，关键字out位于参数名称之后。
示例如下：
CREATE OR REPLACE PROCEDURE 存储过程名称(
s_no in varchar,
s_name out varchar,
s_age number) AS
total NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
dbms_output.put_line('符合该年龄的学生有'||total||'人');
EXCEPTION
WHEN too_many_rows THEN
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
END6.9 常见函数
1.字符类函数
- ASCII(c)函数和CHR(i)函数
- CONCAT（s1,s2）函数
- INITCAP（s）函数
- INSTR（s1,s2[,i][,j]）函数
- LENGTH（s）函数
- LOWER（s）函数和UPPER函数（s）
- LTRIM（s1,s2）函数、RTRIM（s1,s2）函数和TRIM（s1,s2）函数
- REPLACE（s1,s2[,s3]）函数
- SUBSTR（s,i,[j]）函数
2.数字类函数
- CEIL（n）函数
- ROUND(n1,n2)函数
- POWER(n1,n2)函数
3.日期和时间类函数
- SYSDATE()函数
- ADD_MONTHS(d,i)函数
4.转换类函数
- TO_CHAR()函数
- TO_NUMBER(s[,format[lan]])函数
5.聚合类函数
- AVG(x[DISTINCT|ALL])函数
- COUNT(x[DISTINCT|ALL])函数
- SUM(x[DISTINCT|ALL])函数
7. 数据控制语言（DCL）
数据控制语言为用户提供权限控制语言。
grant：授予权限；
revoke:撤销已授予的权限；
三、Oracle管理
1.关键文件管理
1.1 重做日志文件
重做日志文件由重做记录组成，重做记录又称为重做条目，它由一组变更向量组成。每个变更向量都记录了数据库中某个数据块所做的修改。
对于数据库管理员而言，经常查看日志文件是其中一项必要的工作内容，用以了解数据库的运行情况。要了解Oracle数据库的日志文件信息，可以查询如下表所示的三个常用数据字典视图：
字典视图
说 明
V$LOG
显示控制文件中的日志文件信息
V$LOGFILE
日志组合日志成员信息
V$LOG_HISTORY
日志历史信息
1.2 控制文件
在Oracle数据库中，控制文件是一个很小（大小一般在10MB范围内）的二进制文件，含有数据库的结构信息，包括数据文件和日志文件的信息。可以将控制文件理解为物理数据库的一个元数据存储库。控制文件在数据库创建时被自动创建，并在数据库发生物理变化时更新。控制文件被不断更新，并且在任何时候都要保证控制文件是可用的。只有Oracle进程才能够安全地更新控制文件的内容，所以，任何时候都不要试图手动编辑控制文件。
（1）及时备份控制文件 Oracle数据库的控制文件是在创建数据库时自动创建的，一般情况下，控制文件至少有一个副本。
（2）保护控制文件 一旦控制文件被损坏，数据库便无法顺利启动，而且修复也非常困难。也因为如此，控制文件的管理与维护工作显得格外重要。
1.3 归档日志文件
虽然归档日志文件可以保存重做日志文件中即将被覆盖的记录，但它并不是总起作用的，这样要看Oracle数据库所设置的日志模式，通常Oracle有两种日志模式：第一种是非归档日志模式（NOARCHIVELOG），第二种是归档日志模式（ARCHIVELOG）。在非归档日志模式下，原日志文件的内容会被新的日志内容所覆盖；在归档日志模式下，Oracle会首先对原日志文件进行归档存储，且在归档未完成之前不允许覆盖原有日志。
默认情况下，Oracle数据库处于非归档日志模式，这样重做日志文件中被覆盖掉的日志记录就不会被写入到归档日志文件中。根据Oracle数据库对应的应用系统的要求，用户可以把数据库的日志模式切换到归档模式，反之亦可操作。要实现数据库在归档模式与非归档模式之间进行切换，可以使用ALTER DATABASE ARCHIVELOG或NOARCHIVELOG语句
2.表空间管理
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。 所有的数据库对象都存放在指定的表空间中。 但主要存放的是表， 所以称作表空间。 Oracle数据库 中至少存在一个表空间，即SYSTEM的表空间。
2.1 创建表空间
创建表空间的语法如下：
CREATE [SMALLFILE/BIGFILE] TABLESPACE
tablespace_name DATAFILE ‘/path/filename’
SIZE num[k/m] REUSE [,’/path/filename’ SIZE num[k/m] REUSE] [,…] [AUTOEXTEND [ON | OFF] NEXT num[k/m] [MAXSIZE [UNLIMITED | num[k/m]]]]
[MININUM EXTENT num[k/m]] [DEFAULT STORAGE storage] [ONLINE | OFFLINE] [LOGGING | NOLOGGING] [PERMANENT | TEMPORARY] [EXTENT
MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE num[k/m]]]]2.2 维护表空间
更改默认临时表空间：
ALTER DATABASE DEFAULT TEMPRORY TABLESPACE更改默认永久表空间：
ALTER DATABASE DEFAULT TABLESPACE 从表空间中删除数据文件：
ALTER TABLESPACE…DROP DATAFILE改变表空间状态：
ALTER TABLESPACE TABLESPACE_NAME READ ONLY[/WRITE];重命名表空间:
ALTER TABLESPACE TABLESPACE_NAME RENAME TO NEW_TABLESPACE_NAME;四、Oracle特性及优化
1.分区技术
Oracle是最早支持物理分区的数据库管理系统供应商，表分区的功能是在Oracle 8.0版本推出的。分区功能能够改善应用程序的性能、可管理性和可用性，是数据库管理中一个非常关键的技术。尤其在今天，数据库应用系统的规模越来越大，还有海量数据的数据仓储系统，因此，几乎所有的Oracle数据库都使用分区功能来提高查询的性能，并且简化数据库的日常管理维护工作。 那么使用分区技术有哪些优点呢？具体如下：
- 减少维护工作量，独立管理每个分区比管理单个大表要轻松得多。
- 增强数据库的可用性，如果表的一个或几个分区由于系统故障而不能使用，而表其余的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么，只有这部分分区需要修复，这就比修复整个大表耗费的时间少许多。
- 均衡I/O，减少竞争，通过把表的不同分区分配到不同的磁盘来平衡I/O改善性能。
- 分区对用户保持透明，最终用户感觉不到分区的存在。
- 提高查询速度：对大表的查询、增加、修改等操作可以分解到表的不同分区中来并行执行，这样就可以加快运行速度，在数据仓库的TP查询特别有用。
2.调优
2.1 主要系统参数调优
Oracle 11g的初始化参数存放在初始化参数文件SPFILE中。SPFILE是一个二进制文件，只能由Oracle系统进行读写，如果要对其中的参数进行修改，可将所修改的参数写到SPFILE文件中或仅使当前Oracle实例有效而不必写到初始化文件中。
| 参数 | 说 明 |
| buffer_pool_keep | 保留池大小 (从 DB_BLOCK_BUFFERS 分配)。目的是将对象保留在内存中, 以减少 I/O。 |
| buffer_pool_recycle | 循环池大小 (从 DB_BLOCK_BUFFERS 分配)。目的是使用对象后将其清除, 以便重复使用内存。 |
| control_file_record_keep_time | 控制文件中可重新使用部分中的记录必须保留的最短时间 (天数)。 |
| cursor_space_for_time | 在一个游标引用共享 SQL 区时，确定将 SQL 区保留在共享池中还是从中按过期作废处理。 |
| db_block_buffers | 缓冲区高速缓存中 Oracle 块的数量，该参数会显著影响一个例程的 SGA 总大小。 |
| db_keep_cache_size | 指定 KEEP 缓冲池中的缓冲区数，KEEP 缓冲池中的缓冲区大小是主要块大小 (即 db_block_size 定义的块大小)。 |
2.2 系统全局区（SGA）优化
Oracle的存储信息参数是指对内存和硬盘的要求。但是，内存的存储速度比硬盘要快8-10倍，因而，用内存来存放数据更能满足快速请求的要求。但是，内存资源一般比硬盘珍贵，而且内存的配置也是有限的，所以，调整内存的分配以有效利用内存是DBA的一项重要工作。 由于Oracle内存要求与应用程序有关，所以，一般内存的调整是在应用程序和SQL语句做完调整后进行的。但是，如果在应用程序和SQL语句调整前就调整了内存分配，那么，在修改完应用程序和SQL语句后仍需要对Oracle内存结构进行在调整。 另外，建议用户在调整I/O前先调整内存分配。调整内存分配以建立Oracle进行I/O操作所必需的内存总量。
2.3 排序区优化
排序是SQL语法中的一个很小的方面，但却很重要，在Oracle的调整中，它常常被忽略。当使用CREATE INDEX,ORDER BY 或GROUP BY语句时，Oracle数据库将自动执行排序的操作。通常，在以下情况Oracle会进行排序操作。
- （1）在创建索引时。
- （2）使用Order by的SQL语句。
- （3）使用Group by的SQL语句。
- （4）进行table join时，由于现有索引的不足而导致SQL优化器调用MERGE SORT。
当在Oracle实例中建立起一个SESSION时，在内存中就会为该SESSION分配一个私有的排序区域。如果该连接是一个专用连接，那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个PGA（Program Global Area）。如果连接时通过多线程服务器建立的，那么排序的空间就在large_pool中分配。然而，对所有的session用做排序的内存量都必须是一致的，不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须做出平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序的同时，对于那些并不需要进行很大排序的任务，就会出现浪费。当然，当排序的空间需求超出了sort_area_size参数所设置的大小时，将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14000倍。
2.4 常规SQL语句优化
- 建议不用“*”来代替所有列名
- 用TRUNCATE代替DELETE
- 在确保完整性的情况下多用COMMIT语句
- 尽量减少表的查询次数
2.5 表连接优化
驱动表（Driving Table）是指被最先访问的表（通常以全表扫描的方式被访问）。Oracle 11g优化器会检查SQL语句中的每个表的物理大小、索引状态，然后选用花费最低的执行路径。
Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前。那些可以过滤掉最大数据记录的条件必须写在WHERE子句的末尾，也就是在表进行连接操作以前，过滤掉的记录数越多越好。
2.6 合理使用索引
在利用索引的情况下，用于只从表中选择部分行，所以能够提高查询速度。对于只从总行数中查询2%~4%的表，可以考虑创建索引。下面是创建索引的基本原则：
- （1）以查询关键字为基础，表中的行随机排序。
- （2）包含的列数相对比较少的表。
- （3）表中的大多数查询都包含相对简单的WHERE从句。
- （4）对于经常以查询关键字为基础的表，并且该表中的行遵从均匀分布
- （5）缓存命中率低，并且不需要操作系统权限。
工作总结：
通过本周的学习，我对于数据库有了更深刻的认识，同时也提高了自己排查和解决问题的能力。
我在后期学习的过程中觉得很多知识和概念空泛，难以理解。于是后来我看了视频最后的小项目，结合项目一些概念就好理解了。我的体会是学习的过程中，需要经常实践，结合具体的项目需求可以对相关知识有更好的理解。
4459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
