Oracle数据库复习
Oracle数据库体系结构
-
物理存储结构
控制文件
较小,二进制;数据库打开即可写;.CTL重做日志文件
记录修改信息;每一个数据库至少两个;循环;REDO01.LOG,REDO02.LOG,REDO03.LOG,数据文件
用户数据文件USERS01.DBF
系统数据文件SYSTEM01.DBF
回退数据文件UNDOTBS01.DBF
临时数据文件TEMP01.DBF -
逻辑存储结构
表空间
段
表(数据段)
索引
临时段
还原段
系统引导段
区
Oracle数据块 -
内存结构
系统全局区SGA
共享池
库高速缓存
sql语句的正文和编译后的代码以及执行计划
数据字典高速缓存
数据文件、表、索引、列、用户和其他数据对象的定义和权限
数据高速缓冲区
目的:为了缓存操作的数据,减少磁盘读取
重做日志缓冲区
目的:为了数据恢复
程序全局区PGA
非共享区 -
进程结构
用户进程
服务器进程
后台进程
系统监控进程SMON
进程监控进程PMON
数据库写进程DBWR
重做日志写进程LGWR
检查点进程CKPT -
用户管理
公有用户(Commons)保存在CDB(Container Database);“C##”,“c##”开头,包含多个PDB用户
本地用户(LocalUser)保存在PDB(Pluggable Database)
用户安全参数:用户名,口令,用户默认表空间,用户临时表空间,用户空间存取限制,用户资源存取限制。
创建用户
create user c##test(即用户名)
Identified by 20190608(即口令,不区分大小写)
default tablespace 表空间名(默认system表空间)
temporary tablespace temp(临时表空间名)
quota 20M on 表空间名(最大空间分配)
profile profile_name(配置文件,默认default)
password expire(设置口令过期,scott用户首次登录强制重置密码)
account lock(默认unlock;锁定)
修改用户
ALTER USER 用户名
IDENTIFIED BY 口令
ALTER USER c##test
IDENTIFIED BY Oracle12
PASWORD EXPIRE
删除用户
DROP USER 用户名
DROP USER test CASCADE;(若已经在test用户下创建对象)
查询用户信息
ALL_USERS:用户名,ID,创建时间
DBA_USERS:状态,表空间等数据库所有用户信息
10-6 查询数据库中所有用户名。默认表空间和账户的状态。
SELECT USERNAME,
default_tablespace,
accoount_status
From DAB_USERS;
USER_USERS:当前用户详细信息
V $ SESSION:用户会话信息
V $ OPEN_CURSOR:包含用户执行的SQL语句信息 -
权限管理
系统权限
三类
允许在系统范围内操作的权限:create session,create tablespace
允许在用户自己账号内管理对象:create table等建立,修改,删除指定对象权限
允许在任何用户账号内管理对象:create any table等带ANY的权限
授权
GRANT CREATE SESSION
TO c##user1
WITH ADMIN OPTION(允许进一步授予其他用户或角色)
回收
REVOKE {系统权限 | 角色}
FROM {用户名 | 角色| public}
对象权限
授权
GRANT SELECT,UPDATE(sname,age)
ON student
TO c##user1
WITH GRANT OPTION;
回收
REVOKE {对象权限 | ALL}
FROM {用户名 | 角色| public}
[restrict | cascade]
CASCADE:回收权限时引起级联回收
RESTRICT:不存在级联连锁时可回收,否则拒绝 -
角色管理
角色是具有名称的一组相关权限的集合,不同权限集合在一起就形成了角色
两类
预定义角色
connect
resource
DBA
exp_full_database
imp_full_database
用户自定义角色
DBA可为数据库用户创建自定义角色
10-14建立带口令的Oracle12c的角色c##student_role
CREATE ROLE c##student_role
IDENTIFIED BY Oracle12c;
修改:
ALTER ROLE c##student_role
[not identified](无口令)
[identified by 2019060841](新口令)
授于角色权限
GRANT SELECT,UPDATE,DELETE
ON student
TO c##student_role;
10-17将角色授予用户
GRANT c##student_role
TO c##user1;
回收角色
REVOKE c##studetn_role FROM c##user1;
删除角色
DROP ROLE role_name; -
游标
显式游标
4个处理步骤
定义游标
CURSOR student_cursor(v_sname CHAR(20)..)
IS SELECT sname,age
FROM student
where dept=v_dept;
打开游标
open student_cursor
将当前行结果提取到PL/SQL变量中
FETCH student_cursor INTO v_sanme,v_age;
FETCH student_cursor INTO PL/SQL_record;
关闭游标 :close 游标名;
由程序员定义和命令的,块的执行部分,特定语句操纵的内存工作区
属性
游标名%ISOPEN :是否打开,true
游标名%NOTFOUND:最近一次fetch操作未返回结果,true
游标名%FOUND:最近一次fetch操作未返回结果,false
游标名%ROWCOUNT:到当前为止返回的记录数
where current of:允许更新或删除当前游标记录
在定义游标查询语句时,必须加上for update of 从句,FOR UPDATE从句表示先对表加锁
FOR m IN mucur LOOP:....END LOOP; 变量m是记录类型的变量。
隐式游标;SQL;系统自动完成(定义、打开、取值、关闭) -
异常处理
Oracle错误
编译时错误
运行时错误
Oracle错误
用户自定义错误
异常的类型
预定义异常
系统预定义错误
非预定义异常
(1)定义异常 declare my_exception EXCEPTION
(2)关联错误declare pragma EXCEPTION_INIT(my_exception,-2=0119)
(3)异常处理 EXCEPTION
WHEN my_exception THEN
DBMS_OUTPUT.PUT_LINE('违反完整性约束')
用户自定义异常
(1)定义异常
(2)触发异常
BEGIN
IF v_sal=0 THEN
RAISE my_exception;
END IF;
END
(3)异常处理
异常处理基本语法
EXCEPTION
when 错误1 OR 错误2 THEN定位
语句序列1 ;
when others then
语句序列n ;
最多只能有一个others从句 -
数据库备份与恢复
Oracle备份
物理备份
冷备份:在数据库关闭下将组成数据库的所有文件全部备份到磁盘或磁带
归档模式
非归档模式
(1)启动SQL*PLUS,以sys身份登录;
(2)关闭数据库 SQL>shutdown IMMEDIATE;
(3)复制以下物理文件到相应的磁盘
所有控制文件、all数据文件、all重做日志 文件、初始化参数文件
(4)重新启动数据库 SQL>startup;
热备份: 联机备份或archivelog备份,在数据库打开下将组成数据库的控制文件、数据文件备份到磁盘或磁带(归档日志)
逻辑备份
交互方式
命令行方式
1.确定用户名和密码 C:\EXP USERID:system/Oracle12c
2.确定要备份的对象 FILE=d:\orcl\stu_cou_sc.dmp
3.确定备份文件的名字及其路径 TABLEs=(student,course,sc);
4.在命令提示下输入EXP语句实现备份
参数文件方式
1.先用文本编辑器编辑一个参数文件,名为c:\stu_cou.TXT
USERID=system/Oracle12c
TABLES={student,course}
FILE=d:\orcl\stu_cou.dmp
2.执行下列命令完成备份操作
c:\EXP parfile=c:\stu.TXT;
Oracle恢复
物理恢复
非归档模式下的脱机恢复
归档模式下的联机恢复
逻辑恢复
交互方式
命令行方式
1.确定用户名和密码
2.确定要恢复的对象
3.确定备份文件的名字及其路径
4.在命令提示下输入IMP语句实现恢复操作
C:\IMP USERID:system/Oracle12c
TABLEs=(student,course,sc)
ROWS=Y
FILE=d:\orcl\stu_cou_sc.dmp;
参数文件方式
1.先用文本编辑器编辑一个参数文件,名为c:\stu_cou.TXT
USERID=system/Oracle12c
TABLES={student,course}
FILE=d:\orcl\stu_cou.dmp
2.执行下列命令完成恢复操作
c:\IMP parfile=c:\stu.TXT;