雷浪个人网

我愿奔赴大海

文章93
阅读8806

人生倒计时

今日已经过去小时
这周已经过去天
本月已经过去天
今年已经过去个月

热评文章

首页未分类正文内容

oracle初级系列教程《二》

admin 未分类 2018-02-21 00:35:42 128

oracle初级系列教程《二》

【十一、oracle 数据库管理员】

===================================================================

一、数据库管理员

每个oracle数据库应该至少有一个数据库管理员(dba)，对于一个小的数据库，一个dba就够了，但是对于一个大的数据库可能需要多个dba分担不同的管理职责。那么一个数据库管理员的主要工作是什么呢：

1.安装和升级oracle 数据库

2.建库，表空间，表，视图，索引…

3.制定并实施备份和恢复计划

4.数据库权限管理，调优，故障排除

5.对于高级dba，要求能参与项目开发，会编写sql 语句、存储过程、触发器、规则、约束、包

二、管理数据库的用户主要是sys和system(sys好像是董事长，system好像是总经理，董事长比总经理大，但是通常是总

经理干事)

在前面我们已经提到这两个用户，区别主要是：

1.最重要的区别，存储的数据的重要性不同

sys：所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle 的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper角色或权限，是oracle权限最高的用户。

system：用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有dba，sysdba 角色或系统权限。

注意：sysdba可以建数据库，sysoper不能建数据库

2. 其次的区别，权限的不同。

sys用户必须以as sysdba或as sysoper形式登录。不能以normal方式登录数据库。

system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的，从登录信息里面我们可以看出来。

sysdba和sysoper权限区别图，看图：

sysdba>sysoper>dba

可以看到：只要是sysoper拥有的权限，sysdba都有；蓝色是它们区别的地方。（它们的最大区别是：sysdba可以创建数据库，sysoper不可以创建数据库）

dba权限的用户

dba用户是指具有dba角色的数据库用户。特权用户可以执行启动实例，关闭实例等特殊操作，而dba用户只有在启动数据库后才能执行各种管理工作。（相当于说dba连startup和shutdown这两个权限都没有）。

两个主要的用户，三个重要权限，他们的区别和联系，大家要弄清楚。

三、管理初始化参数

1)、管理初始化参数(调优的一个重要知识点，凭什么可以对数据库进行调优呢？是因为它可以对数据库的一些参数进行修改修正)

初始化参数用于设置实例或是数据库的特征。oracle9i提供了200多个初始化参数，并且每个初始化参数都有默认值。

2)、显示初始化参数：show parameter

3)、如何修改参数

需要说明的是如果你希望修改这些初始化的参数，可以到文件D:\oracle\admin\myoral\pfile\init.ora文件中去修改。

【十二、oracle 数据库(表)的逻辑备份与恢复】

===================================================================

一、介绍

逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程。

逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。

物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行。

二、备份(导出)

导出分为导出表、导出方案、导出数据库三种方式。

导出使用exp命令来完成的，该命令常用的选项有：

userid：用于指定执行导出操作的用户名，口令，连接字符串

tables：用于指定执行导出操作的表

owner：用于指定执行导出操作的方案

full=y：用于指定执行导出操作的数据库

inctype：用于指定执行导出操作的增量类型

rows：用于指定执行导出操作是否要导出表中的数据

file：用于指定导出文件名

注意：特别说明-->在导入和导出的时候，要到oracle目录的bin目录下。

1)、导出表

1.导出自己的表

exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp --导出单个表

exp userid=scott/oracle@orcl tables=(emp,dept) file=d:\emp.dmp --导出多个表

eg、

C:\Users\jiqinlin>cd D:\dev\oracle\product\10.2.0\db_1\bin

C:\Users\jiqinlin>d:

D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp

2.导出其它方案的表

如果用户要导出其它方案的表，则需要dba的权限或是exp_full_database的权限，比如system就可以导出scott的表

D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/oracle@orcl tables=(scott.emp) file=d:\emp.emp

D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/oracle@orcl tables=(scott.emp,scott.dept) file=d:\emp.emp

3. 导出表的结构

exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp rows=n

4. 使用直接导出方式

exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp direct=y

这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。

这时需要数据库的字符集要与客户端字符集完全一致，否则会报错...

2)、导出方案

导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表，索引，约束...)和数据，并存放到文件中。

1. 导出自己的方案

exp userid=scott/oracle@orcl owner=scott file=d:\scott.dmp

2. 导出其它方案

如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限，

比如system 用户就可以导出任何方案

exp userid=system/oracle@orcl owner=(system,scott) file=d:\system.dmp

3)、导出数据库

导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限

增量备份（好处是第一次备份后，第二次备份就快很多了）

exp userid=system/oracle@orcl full=y inctype=complete file=d:\all.dmp

三、恢复(导入)

导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件。与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。

imp常用的选项有

userid：用于指定执行导入操作的用户名，口令，连接字符串

tables：用于指定执行导入操作的表

formuser：用于指定源用户

touser：用于指定目标用户

file 用于指定导入文件名

full=y：用于指定执行导入整个文件

inctype：用于指定执行导入操作的增量类型

rows：指定是否要导入表行（数据）

ignore：如果表存在，则只导入数据

1)导入表

1. 导入自己的表

imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp

2. 导入表到其它用户

要求该用户具有dba的权限，或是imp_full_database

imp userid=system/oracle@orcl tables=(emp) file=d:\xx.dmp touser=scott

3. 导入表的结构

只导入表的结构而不导入数据

imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp rows=n

4. 导入数据

如果对象（如比表）已经存在可以只导入表的数据

imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp ignore=y

2)导入方案

导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba 的权限，或者imp_full_database

1．导入自身的方案

imp userid=scott/oracle@orcl file=d:\xxx.dmp

2．导入其它方案

要求该用户具有dba的权限

imp userid=system/oracle@orcl file=d:\xxx.dmp fromuser=system touser=scott

3)导入数据库(相当于数据库迁移)

在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：

imp userid=system/oracle@orcl full=y file=d:\xxx.dmp

【十三、oracle 数据字典和动态性能视图】

===================================================================

一、概念

数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。

动态性能视图记载了例程启动后的相关信息。

二、数据字典

1)、数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。

2)、用户只能在数据字典上执行查询操作(select语句)，而其维护和修改是由系统自动完成的。

3)、这里我们谈谈数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括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 可以显示用户所具有的角色。

这里给大家讲讲角色和权限的关系。

1)、要查看scott具有的角色，可查询dba_role_privs；

SQL> select * from dba_role_privs where grantee='SCOTT';

2)、查询orale中所有的系统权限，一般是dba

select * from system_privilege_map order by name;

3)、查询oracle中所有对象权限，一般是dba

select distinct privilege from dba_tab_privs;

4)、查询oracle 中所有的角色，一般是dba

select * from dba_roles;

5)、查询数据库的表空间

select tablespace_name from dba_tablespaces;

问题1：如何查询一个角色包括的权限？

a.一个角色包含的系统权限

select * from dba_sys_privs where grantee='角色名'

另外也可以这样查看：

select * from role_sys_privs where role='角色名'

b.一个角色包含的对象权限

select * from dba_tab_privs where grantee='角色名'

问题2：oracle究竟有多少种角色？

SQL> select * from dba_roles;

问题3：如何查看某个用户，具有什么样的角色？

select * from dba_role_privs where grantee='用户名'

显示当前用户可以访问的所有数据字典视图。

select * from dict where comments like '%grant%';

显示当前数据库的全称

select * from global_name;

其它说明

数据字典记录有oracle数据库的所有系统信息。通过查询数据字典可以取得以下系统信息：比如

1.对象定义情况

2.对象占用空间大小

3.列信息

4.约束信息

...

但是因为这些个信息，可以通过pl/sql developer工具查询得到，所以这里我就飘过。

四、动态性能视图

动态性能视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图；当停止oracle server时，系统会删除动态性能视图。oracle的所有动态性能视图都是以v_$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且其同义词是以V$开始的，例如v_$datafile的同义词为v$datafile;动态性能视图的所有者为sys，一般情况下，由dba或是特权用户来查询动态性能视图。

因为这个在实际中用的较少，所以飞过

【十四、oracle 数据库管理--管理表空间和数据文件】

===================================================================

一、概念

表空间是数据库的逻辑组成部分。

从物理上讲，数据库数据存放在数据文件中；

从逻辑上讲，数据库数据则是存放在表空间中，表空间由一个或多个数据文件组成。

二、数据库的逻辑结构

oracle中逻辑结构包括表空间、段、区和块。

说明一下数据库由表空间构成，而表空间又是由段构成，而段又是由区构成，而区又是由oracle块构成的这样的一种结构，可以提高数据库的效率。

三、表空间

1、概念

表空间用于从逻辑上组织数据库的数据。数据库逻辑上是由一个或是多个表空间组成的。通过表空间可以达到以下作用：

1)、控制数据库占用的磁盘空间

2)、dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。

2、建立表空间

建立表空间是使用crate tablespace命令完成的，需要注意的是，一般情况下，建立表空间是特权用户或是dba来执行的，如果用其它用户来创建表空间，则用户必须要具有create tablespace的系统权限。

1)、建立数据表空间

在建立数据库后，为便于管理表，最好建立自己的表空间

--路径D:\dev\oracle\product\10.2.0\要存在，否则创建不成功

create tablespace data01 datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' size 20m uniform size 128k;

说明：执行完上述命令后，会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k

2)、使用数据表空间

create table mypart(

deptno number(4),

dname varchar2(14),

loc varchar2(13)

) tablespace data01;

3、改变表空间的状态

当建立表空间时，表空间处于联机的(online)状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句。但是在进行系统维护或是数据维护时，可能需要改变表空间的状态。一般情况下，由特权用户或是dba来操作。

1)、使表空间脱机

alter tablespace 表空间名 offline;

eg、alter tablespace data01 offline;--表空间名不能加单引号

2)、使表空间联机

alter tablespace 表空间名 online;

eg、alter tablespace data01 online;

3)、只读表空间

当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读

alter tablespace 表空间名 read only;

注意：修改为可写是alter tablespace 表空间名 read write;)

我们给大家举一个实例，说明只读特性：

1)、知道表空间名，显示该表空间包括的所有表

select * from all_tables where tablespace_name=’表空间名’;

eg、select * from all_tables where tablespace_name='DATA01'; --DATA01要大写格式

2)、知道表名，查看该表属于那个表空间

select tablespace_name, table_name from user_tables where table_name='EMP';

通过2我们可以知道scott.emp是在system这个表空间上，现在我们可以将system改为只读的但是我们不会成功，因为system是系统表空间，如果是普通表空间，那么我们就可以将其设为只读的，给大家做一个演示，可以加强理解。

3)、

4)、使表空间可读写

alter tablespace 表空间名 read write;

4、删除表空间

一般情况下，由特权用户或是dba来操作，如果是其它用户操作，那么要求用户具有drop tablespace 系统权限。

drop tablespace ‘表空间’ including contents and datafiles;

eg、drop TABLESPACE DATA01 including contents and datafiles;

说明：including contents表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。

5、扩展表空间

表空间是由数据文件组成的，表空间的大小实际上就是数据文件相加后的大小。那么我们可以想象，假定表employee存放到data01表空间上，初始大小就是2M，当数据满2M空间后，如果在向employee表插入数据，这样就会显示空间不足的错误。

案例说明：

1）. 建立一个表空间sp01

eg、create tablespace sp01 datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' size 1m uniform size 128k;

2）. 在该表空间上建立一个普通表mydment其结构和dept一样

create table mypart(

deptno number(4),

dname varchar2(14),

loc varchar2(13)

) tablespace sp01;

3）. 向该表中加入数据insert into mydment select * from dept;

4）. 当一定时候就会出现无法扩展的问题，怎么办？

5）. 就扩展该表空间，为其增加更多的存储空间。

有三种方法：

1. 增加数据文件

SQL> alter tablespace sp01 add datafile 'D:\dev\oracle\product\10.2.0\dada02.dbf' size 1m;

2. 修改数据文件的大小

SQL> alter tablespace sp01 'D:\dev\oracle\product\10.2.0\dada01.dbf' resize 4m;

这里需要注意的是数据文件的大小不要超过500m。

3. 设置文件的自动增长。

SQL> alter tablespace sp01 'D:\dev\oracle\product\10.2.0\dada01.dbf' autoextend on next 10m maxsize 500m;

6）、移动数据文件

有时，如果你的数据文件所在的磁盘损坏时，该数据文件将不能再使用，为了能够重新使用，需要将这些文件的副本移动到其它的磁盘，然后恢复。

下面以移动数据文件sp01.dbf为例来说明：

1. 确定数据文件所在的表空间

select tablespace_name from dba_data_files where file_name=upper('D:\dev\oracle\product\10.2.0\dada01.dbf');

2. 使表空间脱机

--确保数据文件的一致性，将表空间转变为offline的状态。

alter tablespace sp01 offline;

3. 使用命令移动数据文件到指定的目标位置

host move D:\dev\oracle\product\10.2.0\dada01.dbf c:\dada01.dbf;

4. 执行alter tablespace 命令

在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改：

alter tablespace sp01 rename datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' to 'c:\sp01.dbf';

5. 使得表空间联机

在移动了数据文件后，为了使用户可以访问该表空间，必须将其转变为online状态。

alter tablespace sp01 online;

7）、显示表空间信息

查询数据字典视图dba_tablespaces，显示表空间的信息：

select tablespace_name from dba_tablespaces;

显示表空间所包含的数据文件

查询数据字典视图dba_data_files,可显示表空间所包含的数据文件，如下：

select file_name, bytes from dba_data_files where tablespace_name='表空间';

四、表空间小结

1. 了解表空间和数据文件的作用

2. 掌握常用表空间，undo表空间和临时表空间的建立方法

3. 了解表空间的各个状态(online, offline, read write, read only)的作用，及如何改变表空间的状态的方法。

4. 了解移动数据文件的原因，及使用alter tablespace 和alter datatable命令移动数据文件的方法。

五、其它表空间

除了最常用的数据表空间外，还有其它类型表空间：

1. 索引表空间

2. undo表空间

3. 临时表空间

4. 非标准块的表空间

这几种表空间，大家伙可以自己参考书籍研究，这里我就不讲。

六、其它说明

关于表空间的组成部分段/区/块，我们在后面给大家讲解。

【十五、oracle 约束】

===================================================================

一、维护数据的完整性

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

二、约束

约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null、 unique， primary key， foreign key和check 五种。

1)、not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

2)、unique(唯一)

当定义了唯一约束后，该列值是不能重复的，但是可以为null。

3)、primary key(主键)

用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。

需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。

4)、foreign key(外键)

用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique 约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

5)、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). 客户的性别必须是男或者女，默认是男；

代码：

SQL> create table goods(

goodsId char(8) primary key, --主键

goodsName varchar2(30),

unitprice number(10,2) check(unitprice>0),

category varchar2(8),

provider varchar2(30)

);

SQL> 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)

);

SQL> create table purchase(

customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodsId),

nums number(10) check (nums between 1 and 30)

);

表是默认建在SYSTEM表空间的

四、商店售货系统表设计案例二

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是要注意：增加not null约束时，需要使用modify选项，而增加其它四种约束使用add选项。

1)、增加商品名也不能为空

SQL> alter table goods modify goodsName not null;

2)、增加身份证也不能重复

SQL> alter table customer add constraint xxxxxx unique(cardId);

3)、增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；

SQL> alter table customer add constraint yyyyyy check (address in ('海淀','朝阳','东城','西城','通州','崇文','昌平'));

删除约束

当不再需要某个约束时，可以删除。

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查看即可。简单演示一下下...

五、表级定义、列级定义

1)、列级定义

列级定义是在定义列的同时定义约束。

如果在department表定义主键约束

create table department4(

dept_id number(12) constraint pk_department primary key,

name varchar2(12),

loc varchar2(12)

);

2)、表级定义

表级定义是指在定义了所有列后，再定义约束。这里需要注意：

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)

);

【十六、oracle 索引】

===================================================================

一、管理索引-原理介绍

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：

为什么添加了索引后，会加快查询速度呢？

二、创建索引

1)、单列索引

单列索引是基于单个列所建立的索引

语法：create index 索引名 on 表名(列名);

eg、create index nameIndex on custor(name);

2)、复合索引

复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：

create index emp_idx1 on emp(ename, job);

create index emp_idx1 on emp(job, ename);

以上这两个索引是两个不同的索引。

三、使用原则

1)、在大表上建立索引才有意义

2)、在where子句或是连接条件上经常引用的列上建立索引

3)、索引的层次不要超过4层

这里能不能给学生演示这个效果呢？

如何构建一个大表呢？

四、索引的缺点

索引有一些先天不足：

1)、建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。

2)、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的：

1. 很少或从不引用的字段；

2. 逻辑型的字段，如男或女(是或否)等。

综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标

五、其它索引

按照数据存储方式，可以分为B*树、反向索引、位图索引；

按照索引列的个数分类，可以分为单列索引、复合索引；

按照索引列值的唯一性，可以分为唯一索引和非唯一索引。

此外还有函数索引，全局索引，分区索引...

对于索引我还要说：

在不同的情况，我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。

比如：B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。

六、显示索引信息

1)、在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息：select index_name, index_type from user_indexes where table_name = '表名';

2)、显示索引列

通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息

select table_name, column_name from user_ind_columns where index_name ='IND_ENAME';

你也可以通过pl/sql developer工具查看索引信息

【十七、oracle 权限】

===================================================================

一、介绍

这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在哪里。

当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。这里我们会详细的介绍。

二、权限

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。

三、系统权限

1)、系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。

常用的有：

create session 连接数据库

create table 建表

create view 建视图

create public synonym 建同义词

create procedure 建过程、函数、包

create trigger 建触发器

create cluster 建簇

2)、显示系统权限

oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。

select * from system_privilege_map order by name;

3)、授予系统权限

一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明：

1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。

create user ken identified 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

4)、回收系统权限

一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项(with admin option)。回收系统权限使用revoke来完成。当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?[不是级联回收！]

system --------->ken ---------->tom

(create session)(create session)( create session)

用system 执行如下操作：

revoke create session from ken; --请思考tom还能登录吗？

答案：能，可以登录

四、对象权限

1)、对象权限介绍

指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。

比如smith用户要访问scott.emp表(scott：方案，emp：表)

常用的有：

insert 添加

delete 删除

alter 修改

select 查询

index 索引

references 引用

execute 执行

2)、显示对象权限

通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为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';

3)、授予对象权限

在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option)权限，从oracle9i 开始，dba用户(sys，system)可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant 命令来完成的。对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grantoption 选项，则可以将该权限转授给其它用户。但是要注意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;

4)、回收对象权限

在oracle9i 中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户(sys，system)来完成。

这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回？【级联回收】

如：scott------------->blake-------------->jones

select on emp select on emp select on emp

SQL> conn scott/tiger@accp

SQL> revoke select on emp from blake

请大家思考，jones能否查询scott.emp表数据。

答案：查不了了(级联回收，和系统权限不一样，刚好1相反)

【十八、oracle 角色】

===================================================================

一、介绍

角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。

假定有用户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角色具有哪些系统权限呢？

create cluster

create database link

create session

alter session

create table

create view

create sequence

2)、resource角色

resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含unlimited tablespace系统权限。

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角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

四、自定义角色

1、顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式(不验证，数据库验证等)。

1)、建立角色（不验证）

如果角色是公用的角色，可以采用不验证的方式建立角色。

create role 角色名 not identified;

2)、建立角色（数据库验证）

采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。

create role 角色名 identified by 密码;

2、角色授权

1)、给角色授权

给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。

SQL> conn system/oracle;

SQL> grant create session to 角色名 with admin option

SQL> conn scott/oracle@orcl;

SQL> grant select on scott.emp to 角色名;

SQL> grant insert, update, delete on scott.emp to 角色名;

通过上面的步骤，就给角色授权了。

2)、分配角色给某个用户

一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。

SQL> conn system/oracle;

SQL> grant 角色名 to blake with admin option;

因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。

3、删除角色

使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。

SQL> conn system/oracle;

SQL> drop role 角色名;

问题：如果角色被删除，那么被授予角色的用户是否还具有之前角色里的权限？

答案：不具有了

4、显示角色信息

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 = ‘用户名’;

五、精细访问控制

精细访问控制是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句(select，insert，update，delete)时，oracle会自动在sql语句后追加谓词(where子句)，并执行新的sql语句，通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如：

用户 scott blake jones

策略 emp_access

数据库表 emp

如上图所示，通过策略emp_access，用户scott，black，jones在执行相同的sql语句时，可以返回不同的结果。

例如：当执行select ename from emp时，根据实际情况可以返回不同的结果。

【十九、oracle pl/sql简介】

===================================================================

一、pl/sql 是什么

pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。

pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

二、为什么要学pl/sql

1.提高应用程序的运行性能

2.模块化的设计思想(分页的过程，订单的过程，转账的过程。。)

3.减少网络传输量

4.提高安全性(sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会)

三、Oracle为什么在PL/SQL developer执行很快,用c# oracleclient执行就慢

因为PL/SQL这门语言是专门用于在各种环境下对Oracle数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。

而c#语言是微软的产品，它在连接ORACLE的时候先存到“连接池”中，所以第一次会慢点，但是当你的Web程序没有重起的时候，以后的速度就不会慢了。

四、使用pl/sql的缺点

移植性不好(换数据库就用不了)

五、pl/sql理解

1)、存储过程、函数、触发器是pl/sql编写的

2)、存储过程、函数、触发器是存在oracle中的

3)、pl/sql是非常强大的数据库过程语言

4)、存储过程、函数可以在java中调用

六、编写一个存储过程，该过程可以向某表中添加记录。

代码：

1、创建一张简单的表

CREATE TABLE mytest(

username VARCHAR2(30),

pwd VARCHAR2(30)

);

2、创建过程(replace:表示如果有insert_proc，就替换)

CREATE OR REPLACE PROCEDURE insert_proc IS

BEGIN

INSERT INTO mytest VALUES('林计钦', '123456');

END;

3、如何查看错误信息：show error;

注意要在命令窗口执行

4、如何调用该过程：exec 过程名(参数值1，参数值2...);

eg、exec insert_proc;

注意要在命令窗口执行

【二十、oracle pl/sql基础】

===================================================================

一、pl/sql developer开发工具

pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。

二、pl/sql介绍

开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。

三、pl/sql可以做什么

可以用来编写存储过程、函数、触发器、包等

四、编写规范

1.注释

1).单行注释 --

select * from emp where empno=7788; --取得员工信息

2).多行注释 /*...*/来划分

select * from dba_sys_privs where grantee='SCOTT'

/* or select * from role_sys_privs where role='SCOTT'; */ ;

2.标志符号的命名规范

1).当定义变量时，建议用v_作为前缀v_sal

2).当定义常量时，建议用c_作为前缀c_rate

3).当定义游标时，建议用_cursor 作为后缀emp_cursor

4).当定义例外时，建议用e_作为前缀e_error

五、pl/sql块介绍

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

六、块结构示意图

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。

如下所示：

declare

/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/

begin

/*执行部分——要执行的pl/sql 语句和sql 语句*/

exception

/*例外处理部分——处理运行的各种错误*/

end;

说明：

定义部分是从declare开始的，该部分是可选的；

执行部分是从begin开始的，该部分是必须的；

例外处理部分是从exception开始的，该部分是可选的。

可以和java编程结构做一个简单的比较。

七、pl/sql块的实例一

实例一只包括执行部分的pl/sql块

代码：

set serveroutput on; --打开输出选项

begin

dbms_output.put_line('hello world');

end;

/ --执行

雷浪个人网

人生倒计时

热评文章

苹果App Store怎么取消支付宝绑定 App Store解除绑定支付宝教程

苹果手机怎样批量删除全部短信

iPhoneX常见操作的使用教程

欢迎使用Z-BlogPHP！

自搭建服器解决斐讯Wifi智能插座DC1暴雷后官方App无法控制-变废为宝

Oracle学习路线指南及书籍推荐

oracle初级系列教程《二》

相关推荐

oracle基础30天培训课程大纲

oracle初级系列教程《三》

oracle初级系列教程《一》

Oracle学习路线指南及书籍推荐

« 2025年7月 »
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31