oracle数据库课件.ppt

Oracle数据库,备份与恢复概述,备份与恢复 Oracle数据库备份分为物理备份和逻辑备份。物理备份是数据库文件拷贝的备份，根据备份时数据所处状态的不同，物理备份又可分为冷备份与热备份。导出/导入(export/import)工具用于进行逻辑备份。冷备份(也称作离线备份)是在数据库被正常关闭之后进行的数据文件的物理备份。热备份(也称作在线备份)是在数据库运行于归档日志模式，在打开的情况下做数据的物理备份，可以恢复到任一时间点。,数据库（表）的逻辑备份与恢复,逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行。,导出,导出具体的分为：导出表，导出方案，导出数据库三种方式。导出使用exp命令来完成的，该命令常用的选项有：userid：用于指定执行导出操作的用户名，口令，连接字符串 tables：用于指定执行导出操作的表 owner：用于指定执行导出操作的方案 full=y：用于指定全库导出模式 inctype：用于指定执行导出操作的增量类型 rows：用于指定执行导出操作是否要导出表中的数据 file：用于指定导出文件名,在导入和导出的时候，要到oracle目录的bin 目录下。Cmd 进入命令格式：E:oracleproduct10.2.0db_1BIN导出表 1.导出自己的表 Exp userid=scott/hxhtest tables=(dept)file=d:dept.dmp 2.导出其它方案的表 如果用户要导出其它方案的表，则需要dba的权限或是exp_full_database的权限，比如system就可以导出scott的表 E:oracleproduct10.2.0db_1BIN exp userid=system/hxhtest tables=(scott.emp)file=d:e2.dmp,3.导出表的结构 exp userid=scott/hxhtest tables=(emp)file=d:e3.dmp rows=N4.使用直接导出方式 exp userid=scott/hxhtest tables=(emp)file=d:e4.dmp direct=Y这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。这时需要数据库的字符集要与客户端字符集完全一致，否则会报错.,导出方案 导出方案是指使用export工具导出一个方案或是多个方案中的所有对象（表，索引，约束.）和数据。并存放到文件中。1.导出自己的方案 exp userid=scott/hxhtest owner=scott file=d:scott.dmp 2.导出其它方案 如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限，比如system用户就可以导出任何方案 exp userid=system/hxhtest owner=(scott)file=d:system.dmp,导出数据库 导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限 增量备份exp userid=system/hxhtest full=Y inctype=complete file=d:all.dmp,导入,导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件。与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。imp常用的选项有 userid：用于指定执行导入操作的用户名，口令，连接字符串 tables：用于指定执行导入操作的表 formuser：用于指定源用户 touser：用于指定目标用户 file：用于指定导入文件名 full=y：用于指定执行导入整个文件 inctype：用于指定执行导入操作的增量类型 rows：指定是否要导入表行（数据）ignore：如果表存在，则只导入数据,导入,导入表 1.导入自己的表 imp userid=scott/hxhtest tables=(emp)file=d:emp.dmp2.导入表到其它用户 要求该用户具有dba的权限，或是imp_full_database imp userid=system/hxhtest tables=(emp)file=d:xx.dmp touser=scott,导入,3.导入表的结构只导入表的结构而不导入数据 imp userid=scott/tigermyorcl tables=(emp)file=d:xx.dmp rows=n 4.导入数据 如果对象（如比表）已经存在可以只导入表的数据 imp userid=scott/tigermyorcl tables=(emp)file=d:xx.dmp ignore=y,导入,导入方案 导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba的权限，或者imp_full_database 1 导入自身的方案 imp userid=scott/tiger file=d:xxx.dmp 2 导入其它方案 要求该用户具有dba的权限 imp userid=system/manager file=d:xxx.dmp fromuser=system touser=scott,导入,导入数据库 在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：imp userid=system/manager full=y file=d:xxx.dmp,数据字典,数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成的。这里我们谈谈数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括user_xxx，all_xxx，dba_xxx三种类型。,数据字典,user_tables；用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表 比如：select table_name from user_tables;all_tables；用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其它方案的表：比如：select table_name from all_tables;dba_tables；它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table系统权限。例如：当用system用户查询数据字典视图dba_tables时，会返回system，sys，scott.方案所对应的数据库表。,用户名，权限，角色,在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，oracle会将权限和角色的信息存放到数据字典。通过查询dba_users可以显示所有数据库用户的详细信息；通过查询数据字典视图dba_sys_privs，可以显示用户所具有的系统权限 通过查询数据字典视图dba_tab_privs，可以显示用户具有的对象权限；通过查询数据字典视图dba_col_privs可以显示用户具有的列权限；通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色。,用户名，权限，角色,这里给大家再讲讲角色和权限的关系。例如：要查看scott具有的角色，可查询dba_role_privs；SQL select*from dba_role_privs where grantee=SCOTT;/查询orale中所有的系统权限，一般是dba select*from system_privilege_map order by name;/查询oracle中所有对象权限，一般是dba select distinct privilege from dba_tab_privs;/查询oracle中所有的角色，一般是dba select*from dba_roles;/查询数据库的表空间 select tablespace_name from dba_tablespaces;,问题1：如何查询一个角色包括的权限？问题2：oracle究竟有多少种角色？问题3：如何查看某个用户，具有什么样的角色？,约束,数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null、unique，primary key，foreign key，和check五种。,约束,使用 not null（非空）如果在列上定义了not null，那么当插入数据时，必须为列提供数据。unique（唯一）当定义了唯一约束后，该列值是不能重复的，但是可以为null。primary key（主键）用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。,约束,foreign key（外键）用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在。check 用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。商店售货系统表设计案例 现有一个商店的数据库，记录客户及其购物况，由下面三个表组成：商品goods（商品号goodsId，商品名 goodsName，单价 unitprice，商品类别category，供应商provider）；客户customer（客户号customerId，姓名name，住在address，电邮email，性别sex，身份证cardId）；,约束,购买purchase（客户号customerId，商品号goodsId，购买数量nums）；请用SQL语言完成下列功能：1.建表，在定义中要求声明：(1).每个表的主外键；(2).客户的姓名不能为空值；(3).单价必须大于0，购买数量必须在1到30之间；(4).电邮不能够重复；(5).客户的性别必须是 男 或者 女，默认是男；,约束,create table goods(goodsId char(8)primary key,-主键goodsName varchar2(30),unitprice number(10,2)check(unitprice0),category varchar2(8),provider varchar2(30);create table customer(customerId char(8)primary key,-主键 name varchar2(50)not null,-不为空 address varchar2(50),email varchar2(50)unique,sex char(2)default 男 check(sex in(男，女)，-一个char能存半个汉字，两位char能存一个汉字 cardId char(18);,约束,create table purchase(customerId char(8)references customer(customerId),goodsId char(8)references goods(goodsId),nums number(10)check(nums between 1 and 30);,约束,删除约束 当不再需要某个约束时，可以删除。alter table 表名 drop constraint 约束名称；特别说明一下：在删除主键约束的时候，可能有错误，比如：alter table 表名 drop primary key；这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项 如像：alter table 表名 drop primary key cascade;,约束,显示约束信息 1.显示约束信息 通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。select constraint_name,constraint_type,status,validated from user_constraints where table_name=表名;2.显示约束列 通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。select column_name,position from user_cons_columns where constraint_name=约束名;3.当然也有更容易的方法，直接用pl/sql developer查看即可。简单演示,约束,列级定义 列级定义是在定义列的同时定义约束。如果在department表定义主键约束 create table department4(dept_id number(12)constraint pk_department primary key,name varchar2(12),loc varchar2(12);,约束,表级定义 表级定义是指在定义了所有列后，再定义约束。这里需要注意：not null约束只能在列级上定义。以在建立employee2表时定义主键约束和外键约束为例：create table employee2(emp_id number(4),name varchar2(15),dept_id number(2),constraint pk_employee primary key(emp_id),constraint fk_department foreign key(dept_id)references department4(dept_id);,管理索引-原理介绍,索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：单列索引 单列索引是基于单个列所建立的索引，比如：create index 索引名 on 表名(列名);复合索引 复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：create index emp_idx1 on emp(ename,job);create index emp_idx1 on emp(job,ename);,管理权限和角色,这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在那里。当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。,管理权限和角色,权限 权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。系统权限 系统权限介绍 系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。,管理权限和角色,常用的有：create session 连接数据库 create table 建表 create view 建视图 create public synonym 建同义词 create procedure 建过程、函数、包 create trigger 建触发器 create cluster 建簇,管理权限和角色,显示系统权限 oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。select*from system_privilege_map order by name;,管理权限和角色,授予系统权限 一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明：1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。create user ken identfied by ken;2 给用户ken授权 1).grant create session,create table to ken with admin option;2).grant create view to ken;,管理权限和角色,3 给用户tom授权 我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权：1.grant create session,create table to tom;2.grant create view to ken;-ok吗？不ok,管理权限和角色,回收系统权限 一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项（with admin option）。回收系统权限使用revoke来完成。当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?system-ken-tom(create session)(create session)(create session)用system执行如下操作：revoke create session from ken;-请思考tom还能登录吗？,管理权限和角色,对象权限 指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。比如smith用户要访问scott.emp表（scott：方案，emp：表）常用的有：alter 修改 delete 删除 select 查询 insert 添加 update 修改 index 索引 references 引用 execute 执行,管理权限和角色,显示对象权限 通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为dba_tab_privs SQL conn system/manager;SQL select distinct privilege from dba_tab_privs;SQL select grantor,owner,table_name,privilege from dba_tab_privs where grantee=BLAKE;,管理权限和角色,1.授予对象权限 在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的（with grant option）权限，从oracle9i开始，dba用户（sys，system）可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grant option选项，则可以将该权限转授给其它用户。但是要注意with grant option选项不能被授予角色。,管理权限和角色,1.monkey用户要操作scott.emp表，则必须授予相应的对象权限 1).希望monkey可以查询scott.emp表的数据，怎样操作？grant select on emp to monkey;2).希望monkey可以修改scott.emp的表数据，怎样操作？grant update on emp to monkey;3).希望monkey可以删除scott.emp的表数据，怎样操作？grant delete on emp to monkey;4).有没有更加简单的方法，一次把所有权限赋给monkey？grant all on emp to monkey;,管理权限和角色,2.能否对monkey访问权限更加精细控制。（授予列权限）1).希望monkey只可以修改scott.emp的表的sal字段，怎样操作？grant update on emp(sal)to monkey 2).希望monkey只可以查询scott.emp的表的ename，sal数据，怎样操作？grant select on emp(ename,sal)to monkey,管理权限和角色,3.授予alter权限 如果black用户要修改scott.emp表的结构，则必须授予alter对象权限 SQL conn scott/tiger SQL grant alter on emp to blake;当然也可以用system，sys来完成这件事。4.授予execute权限 如果用户想要执行其它方案的包/过程/函数，则须有execute权限。比如为了让ken可以执行包dbms_transaction，可以授予execute权限。SQL conn system/manager SQL grant execute on dbms_transaction to ken;,管理权限和角色,5.授予index权限 如果想在别的方案的表上建立索引，则必须具有index对象权限。如果为了让black可以在scott.emp表上建立索引，就给其index的对象权限 SQL conn scott/tiger SQL grant index on scott.emp to blake;,管理权限和角色,6.使用with grant option选项 该选项用于转授对象权限。但是该选项只能被授予用户，而不能授予角色 SQL conn scott/tiger;SQL grant select on emp to blake with grant option;SQL conn black/shunping SQL grant select on scott.emp to jones;,管理权限和角色,回收对象权限 在oracle9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户（sys，system）来完成。这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回？【级联回收】如：scott-blake-jones select on emp select on emp select on emp SQL conn scott/tigeraccp SQL revoke select on emp from blake 请大家思考，jones能否查询scott.emp表数据。,角色,角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，假定有用户a，b，c为了让他们都拥有权限 1.连接数据库 2.在scott.emp表上select，insert，update。如果采用直接授权操作，则需要进行12次授权。因为要进行12次授权操作，所以比较麻烦喔！怎么办？如果我们采用角色就可以简化：首先将creat session，select on scott.emp，insert on scott.emp，update on scott.emp授予角色，然后将该角色授予a，b，c用户，这样就可以三次授权搞定。角色分为预定义和自定义角色两类：,预定义角色 预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba 1.connect角色 connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限呢？alter session create cluster create database link create session create table create view create sequence,管理权限和角色,2.resource角色 resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。resource角色包含以下系统权限：create cluster create indextype create table create sequence create type create procedure create trigger,管理权限和角色,3.dba角色 dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。,管理权限和角色,自定义角色 顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式（不验证，数据库验证等）。1.建立角色（不验证）如果角色是公用的角色，可以采用不验证的方式建立角色。create role 角色名 not identified;2.建立角色（数据库验证）采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。create role 角色名 identified by 密码;,管理权限和角色,角色授权 当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。1.给角色授权 给角色授予权限和给用户授权没有太多区别。SQL conn system/manager;SQL grant create session to 角色名 with admin option SQL conn scott/tigermyoral;SQL grant select on scott.emp to 角色名;SQL grant insert,update,delete on scott.emp to 角色名;通过上面的步骤，就给角色授权了。,管理权限和角色,2.分配角色给某个用户 一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。SQL conn system/manager;SQL grant 角色名 to blake with admin option;因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。,管理权限和角色,删除角色 使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。SQL conn system/manager;SQL drop role 角色名;问题：如果角色被删除，那么被授予角色的用户是否还具有之前角色里的权限？答案：不具有了,管理权限和角色,显示角色信息 1.显示所有角色 SQL select*from dba_roles;2.显示角色具有的系统权限 SQL select privilege,admin_option from role_sys_privs where role=角色名;3.显示角色具有的对象权限 通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。,管理权限和角色,4.显示用户具有的角色，及默认角色 当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色 SQL select granted_role,default_role from dba_role_privs where grantee=用户名;,