Oracle基本概念与操作（上）

2.1 表的主键：唯一，不能重复。

2.2 事务：commit和rollback满足99%的应用需求。

2.3 虚表dual用途：

1. 查看当前登录用户select user from dual;

2. 获取日期select to_char(sysdate,'yyyymmddhh24miss') from dual;

3. 获取序列(sequence)生成器的值。

create sequence SEQ_FREECPLUS;         -- 创建序列生成器SEQ_FREECPLUS。
select SEQ_FREECPLUS.nextval from dual;  -- 从序列SEQ_FREECPLUS获取下一个值。
select SEQ_FREECPLUS.currval from dual;  -- 从序列SEQ_FREECPLUS获取当前值。

2.4 序列：create sequence SEQ_GIRL;

• dual中使用序列

select SEQ_GIRL.nextval from dual;     -- 获取序列SEQ_GIRL的下一个值。
select SEQ_GIRL.currval from dual;     -- 获取序列SEQ_GIRL的当前值。

• sql中使用

create table T_GIRL
(
  name    varchar2(10),     -- 姓名
  keyid   number(10)        -- 记录编号
);
insert into T_GIRL(name,keyid) values('西施'  ,SEQ_GIRL.nextval);
insert into T_GIRL(name,keyid) values('妲已'  ,SEQ_GIRL.nextval);
insert into T_GIRL(name,keyid) values('杨玉环',SEQ_GIRL.nextval);
insert into T_GIRL(name,keyid) values('李师师',SEQ_GIRL.nextval);
update T_GIRL set keyid=SEQ_GIRL.nextval+100;

• 序列的裂缝

        序列不产生事务：序列的nextval方法不会产生事务，事务回滚命令rollback不会恢复序列的值。

2.5 伪列 rowid和rownum。

• rowid

rowid类型用于存放表中的记录在数据库中的物理地址。rowid不是字符串，长度10字节，但是Oracle可以把10字节的rowid类型与18字节的字符串自动转换。

用于where的条件：rowid是记录了数据存放的物理位置，用rowid作为where的条件的效率永远是最高的，远远超过任何索引，利用这个特点可以提高数据操作的效率。

rowid缺点：1）兼容性不好，rowid是Oracle数据库特有的，其它数据库没有，如果你的应用程序需要迁移到其它数据库，就不能用rowid了。2）rowid记录的是数据存放的物理位置，这个值在数据整理、数据迁移和数据备份的时候会变化。

• rownum

rownum是Oralce为查询结果集的行临时分配的顺序号

应用：限制数据库大事务。

大事物：操作了大量的数据，会消耗缓存资源。

长事务：操作数据的时间很长，长时间持有锁。

while (还有需要删除的数据)
{
    delete from 表名 where 待删除数据的条件 and rownum<=100000 ;
    commit;
}

以上delete语句一次删除100000条记录。

2.6 索引

        总的来说，Oracle数据库非常强大，如果你的数据库性能较低或执行SQL语句的效率很低，最主要原因应该是索引设计不合理或使用索引不合理。所谓的SQL优化，主要的内容就是如何设计索引和如何利用索引。

1. 索引的特点：

• 索引是表的一部分，是可选的，表可以没有索引，就像书可以没有目录一样，数据库不做强制要求。
• 合理的索引可以提高数据查找的效率，减少磁盘I/O。
• 唯一索引可以约束表数据的完整性。
• 索引也是数据，需要存储空间。
• 数据库自动维护索引的内容，当对表的记录进行了增、删、改的操作时，数据库会自动更新索引。
• 索引虽然可以提高数据查找的效率，但对表进行增、删、改操作时，数据库除了维护表中的数据，还要维护索引，所以，索引会让增、删、改操作的效率下降。
• 索引提升了数据查找的效率，降低了数据操作的效率，在设计表和索引的时候，要根据实际情况在两者之间权衡。

2. 索引的存储空间

        每个数据库用户有一个缺省表空间，创建的表、主键和索引存放在缺省表空间中，也可以指定其它的表空间。在实际应用中，会把表和索引的存储空间分开到不同的表空间，减少磁盘的竞争，提升I/O的性能。

查看当前用户的缺省表空间

从数据字典USER_USERS中可以查看当前用户的缺省表空间。

指定索引的表空间

指定索引的表空间的语法如下：

create index 索引名 ...... tablespace 表空间名;

示例：

create unique index IDX_GIRL_3 on T_GIRL(keyid) tablespace INDEXS;

        索引还有其它与存储相关的选项，但应用场景比较少，本文就不介绍了。

3. 索引的优化

        索引的优化体现在SQL语句的where条件中，如果where条件中的字段列表没有创建索引，SQL语句的性能将会很低。在设计索引的时候，必须考虑在常用的where条件，在编写SQL语句的时候，也必须清楚表上创建了哪些索引。在PLSQL软件左侧查看索引。执行计划：选中一行语句点击Tools按F5，从下往上看执行步骤。

2.7 distinct关键字：去掉重复。

select dinstinct yz,sc from t_girl;
select sysdate+1 from dual; --sysdate相加减的是天数。还有add_month等函数。

2.8 一些笔记

oracle自动类型转换（如number和varchar）。

create table tt(c1 varchar2(10),c2 number(8,2));
insert into tt values(3.14,")；  --将会自动转换，并且查询出来c2是空的，而不是0。

2.9 外键

        外键（foreign key）用于约束两个表之间数据的关系，将表中主键字段添加到另一个表中，再创建两个表之间的外键约束，这些字段就成为另一个表的外键。

        合理的数据结构设计，表中的数据一定有一致性约束，使用外键，让数据库去约束数据的一致，不给任何人出错的机会。不用外键会怎样？不用也不会怎么样，如果不用外键，在程序中要写代码进行判断，手工操作数据时也必须处处小心。

外键约束

1）当对从表进行操作时，数据库会：

a）向从表插入新记录时，如果外键值在主表中不存在，阻止插入。

b）修改从表的记录时，如果外键的值在主表中不存在，阻止修改。

2）当对主表进行修改操作时，数据库会：

a）主表修改主键值时，旧值在从表里存在便阻止修改。

3）当对主表进行删除操作时，数据库会（三选一）：根据下面创建外键最后一行说明选择。

a）主表删除行时，其主键值在从表里存在便阻止删除。

b）主表删除行时，连带从表的相关行一起删除。

c）主表删除行时，把从表相关行的外键字段置为null。

alter table 从表名
  add constraint 外键名 foreign key (从表字段列表)
   references 主表名 (主表字段列表)
   [on delete cascade|set null];    --指明三选一方式。

2.10 表空间（了解原理和会使用即可）

用DBA登录数据库，执行以下SQL可以查看数据库全部的表空间。

select * from v$tablespace;

一、管理表空间

1、创建表空间

create tablespace 表空间名 datafile '数据文件名' size 大小;

        在创建数据库实列后，为了方便管理和效率，最好再创建一个专用于存放数据表空间和一个专用于存放索引表空间。

示例：

1）创建数据表空间DATA，同时创建数据文件/oracle/base/oradata/snorcl11g/data01.dbf，数据文件的大小是128M。

create tablespace DATA datafile '/oracle/base/oradata/snorcl11g/data01.dbf' size 128M;

2）创建索引表空间INDEXS，同时创建数据文件/oracle/base/oradata/snorcl11g/indexs01.dbf，数据文件的大小是128M。

create tablespace INDEXS datafile '/oracle/base/oradata/snorcl11g/indexs01.dbf' size 128M;

创建表空间时，可以指定多个数据文件，但致少要指定一个数据文件，数据文件由Oracle自动创建，不需要事先在磁盘上手工的创建文件。

2、使用表空间

1）创建数据库用户时，指定用户缺省使用的表空间。

create user girl identified by pwdgirl default tablespace DATA;

2）创建表时，指定表使用的表空间。

create table T_GIRL
(
 id    char(4)     not null,  -- 编号
 name   varchar2(30)  not null,  -- 姓名
 yz    varchar2(20)    null,  -- 颜值
 sc    varchar2(20)    null  -- 身材
) tablespace DATA;

3）创建主键时，指定主键使用的表空间。

alter table T_GIRL add constraint PK_GIRL primary key(id) using index tablespace INDEXS;

4）创建索引时，指定索引使用的表空间。

create index IDX_GIRL_1 on T_GIRL(name) tablespace INDEXS;

3、删除表空间

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

drop tablespace 表空间名 including contents and datafiles;

Including contents 表示删除表空间，同时删除该表空间全部的数据库对象，而datafiles表示将数据库文件也删除。

示例：

删除DATA和INDEXS表空间，和该表空间全部的数据库对象以及数据文件。

drop tablespace DATA including contents and datafiles;
drop tablespace INDEXS including contents and datafiles;

4、向表空间中添加数据文件 （扩展表空间）

表空间的大小由数据文件的大小决定，向表空间中添加数据文件，可以扩展表空间的容量。

alter tablesapce 表空间名 add datafile '数据文件名' size 大小
   [reuse]
   [autoextend [on/off] next 大小]
   [maxsize [unlimited|大小]];

参数说明：

size 大小：数据文件的初始大小，单位：K|M|G|T|P|E。

[reuse]：如果操作系统上已经存在数据文件，加reuse参数表示覆盖它，否则会提示错误，建议一般不要启用reuse选项，容易产生误操作，如果磁盘上已存在同名的数据文件，可以先手工的删除它。

[autoextend [on/off] next 大小]：数据文件是否自动扩展，如果为on，next参数指定每次扩展的大小。

[maxsize [unlimited|大小]]：如果数据文件可以自动扩展，maxsize参数可以限制文件的最大值，unlimited表示无限制。

示例：

1）向DATA表空间添加数据文件，大小100M，可重用，可自动扩展，每次扩展10M，最大200M。

alter tablespace DATA add datafile '/oracle/base/oradata/snorcl11g/data02.dbf' 
   size 100M reuse autoextend on next 10M maxsize 200M;

2）向DATA表空间添加数据文件，大小100M，可重用，可自动扩展，每次扩展10M，无最大限制。

alter tablespace DATA add datafile '/oracle/base/oradata/snorcl11g/data03.dbf' 
   size 100M reuse autoextend on next 10M maxsize unlimited;

3）向DATA表空间添加数据文件，大小200M，不可重用，不能自动扩展。

alter tablespace DATA add datafile '/oracle/base/oradata/snorcl11g/data04.dbf' 
   size 100M autoextend off;

二、查看表空间和数据文件

1、查看当前用户的缺省表空间

select username,default_tablespace from user_users;

2、查看数据文件

数据文件存放在数据字典DBA_DATA_FILES中，用DBA权限执行以下SQL查询。

select file_name,file_id,tablespace_name,bytes,status,autoextensible,
       maxbytes,user_bytes,online_status
  from DBA_DATA_FILES order by tablespace_name,file_name;

3、查看表空间使用情况

从数据字典DBA_FREE_SPACE和DBA_DATA_FILES中可以查询表空间使用情况。一般项目的话要讲下面查询的信息展示出来，了解使用情况。

select a.tablespace_name "表空间名",total "表空间大小",
       (total - free) "已使用大小",free "表空间剩余大小",
       round(total/(1024*1024)) "表空间大小(M)",
       round((total-free)/(1024*1024)) "已使用大小(M)",
       round(free/(1024*1024)) "表空间剩余大小(M)",
       round((total-free)/total,4)*100 "使用率%"
  from
(select tablespace_name,sum(bytes) free from DBA_FREE_SPACE group by tablespace_name) a,
(select tablespace_name,sum(bytes) total from DBA_DATA_FILES group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

2.11 用户和权限管理

在安装数据库的服务器上，用dba身份登录数据库。

1、创建用户

创建用户的命令是create user，它的选项非常多，在这里我介绍一些常用的选项。

语法：

create user 用户名 identified by 密码
       [default tablespace 表空间名]
       [temporary tablespace 表空间名]
       [quota 大小 on 表空间名]
       [profile 用户配置文件];

参数说明：

create user 用户名 identified by 密码：指定登录数据库的用户名和密码。

[default tablespace 表空间名]：指定用户的永久表空间，该用户全部的数据库对象（表、索引）将存放在该表空间中。

[temporary tablespace 表空间名]：指定用户的临时表空间，临时表空间主要用于排序、运算、管理索引、存放临时数据等，当任务完成之后系统会自动清理。

[quota 大小 on 表空间名]：表空间配额，用户使用表空间的大小，单位有[K|M|G|T|P|E]，缺省是unlimited，无限制。

[profile 用户配置文件]：用户的配置文件，它是密码限制，资源限制的命名集合，利用profile 可以对数据库用户进行基本的资源管理，密码管理，缺省是default profile，无限制。

示例：

create user girl
  identified by girlpwd
  default tablespace users
  temporary tablespace temp
  quota 20M on users;

创建一个用户名为girl的新用户，密码是pwdgirl，缺省表空间是users（可使用20M的空间），临时表空间是temp。

注意，创建用户时，如果不指定default tablespace/ temporary tablespace选项，则使用系统缺省的永久/临时表空间， 用以下SQL可以查看系统缺省的永久/临时表空间。

select property_value from DATABASE_PROPERTIES where 
property_name in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

2、修改用户的密码

alter user 用户名 identified by 新密码;

普通用户只能修改自已的密码，DBA可以修改其它用户的密码。

3、锁定/解锁用户

alter user 用户名 account lock;

alter user 用户名 account unlock;

锁定/解锁用户需要DBA权限。

4、修改用户表空间配额

alter user 用户名 quota 大小 on 表空间;

修改用户表空间配额需要DBA权限。

5、删除用户

drop user 用户名 [cascade];

drop user 只有在用户下没有任何数据库对象的时候才能删除用户，否则会提示错误。

采用cascade选项删除用户以及用户下全部的数据库对象，包括表、视图、函数、同义词、过程等。

删除用户需要DBA权限。

创建完用户sqlplus登陆不上去，还需要赋予权限（2.13）。

2.12 权限管理

权限是指用户执行特定命令或操作数据库对象的权利。Oracle的用户权限分系统权限和对象权限。

1、系统权限

系统权限针对的是系统安全性，执行数据库操作，如登录、创建表、创建函数等，通过查询SYSTEM_PRIVILEGE_MAP数据字典可以看到Oracle的两百多种系统权限。

select * from SYSTEM_PRIVILEGE_MAP;

以下是几种常用的系统权限：

create session       创建会话。
create/drop user     创建/删除用户。
create/drop sequence 创建/删除序列。
create/drop synonym  创建/删除同名对象。
create/drop table    创建/删除表。
create/drop view     创建/删除视图。

2、对象权限

对象权限针对的是数据安全性，对某一特定对象（如表，视图、序列、存储过程等）执行的操作，对象的权限分类如下：

1）select权限

对表而言，允许对表执行select语句；对序列而言，允许对序列获取下一个值（nextval）和当前值（currnvl）。

2）insert权限

允许向表和视图中插入记录。

3）update权限

允许在表或视图中执行update语句，update权限必须随同select权限授予，否则被授权用户不能够选择行。

4）delete权限

允许在表或视图中执行delete语句，delete权限必须随同select权限授予，否则被授权用户不能够选择行。

5）alter权限

对表而言，允许对表执行alter table语句；对序列而言，允许对序列alter sequence语句。

6）execute权限

执行存储过程、函数和包的权限。

7）index权限

允许在表上创建索引。

8）reference权限

允许在表上创建完整性约束，如外键。

9）all

对象的全部权限（上面列出的全部权限）。

以下是Oracle数据库对象与权限的对照表。

对象权限	表	视图	序列	过程（函数和包）	备注
select	是	是	是		常用。
insert	是	是			常用。
update	是	是			常用。
delete	是	是			常用。
alter	是		是		不常用。
execute				是	不常用。
index	是				不常用。
references	是				不常用。

3、角色

由于Oracle提供的权限非常之多，日常操作时我们不可能将所需权限一一赋予用户，引入了角色。角色是一组相关权限的集合，使用角色最主要的目的是简化权限管理。将一组权限打包到角色中，将角色赋予用户就是把角色下得全部权限都赋给了用户，简化了赋权操作。

Oracle提供三种标准的角色：

1）connect角色

connect是最基本的用户权限，拥有connect权限的用户只可以登录Oracle（仅具有创建SESSION的权限），不可以创建和操作任何对象。

2）resource角色

拥有resource权限的用户可以创建数据库对象，和对本用户的数据库对象拥有全部的操作权限。

3）DBA角色

DBA角色，拥有全部特权，是系统最高权限。

4、授于/收回权限

Oracle采用grant命令授于权限，revoke命令收回权限。

grant命令非常复杂，在实际应用中远远没有那么复杂，所以本文只介绍grant的常用方法。

1）将系统权限和角色权限授于用户，revoke收回。

grant { 系统权限 | 角色 }, …… to { public | 角色名 | 用户名 },……;

revoke { 系统权限 | 角色 }, …… from { public | 角色名 | 用户名 },……;

一般情况下，会给新用户授权connect角色和resource角色。

grant connect,resource to 用户名;  --执行完新用户就可以有connect登录权限了session权限。

将select any table权限授于全部用户。

grant select any table to public;

revoke select any table from public;

将select any table权限授于connect角色。

grant select any table to connect;

将select any table和insert any table权限授于girl和scott用户。

grant select any table,insert any table to girl,scott;

revoke select any table,insert any table from girl,scott;

将connect、resource权限授于girl用户。

grant connect,resource to girl;

2）将对象权限授于用户和角色，revoke收回。

grant 对象权限列表 on 对象名 to { public | 角色名 | 用户名 },……;

revoke对象权限列表 on 对象名 from { public | 角色名 | 用户名 },……;

将scott.T_GIRL表的select和insert权限授于girl和scott用户。

grant select,insert on scott.T_GIRL to girl,scott;

revoke select,insert on scott.T_GIRL from girl,scott;

grant delete on scott.emp to girl; --把队scott.emp表的删除权限属于girl用户。

将scott.T_GIRL表的全部权限授于girl和scott用户。

grant all on scott.T_GIRL to girl,scott;

revoke all on scott.T_GIRL from girl,scott;

将scott.T_GIRL表的全部权限授于全部用户。

grant all on scott.T_GIRL to public;

将scott.T_GIRL表的全部权限授于resource角色。

grant all on scott.T_GIRL to resource;

5、查询权限

从数据字典中可以查询当前用户拥有的权限和角色。

1）查询当前用户拥有的角色：

select * from USER_ROLE_PRIVS;

2）查询当前用户拥有的系统权限：

select * from USER_SYS_PRIVS;

3）查询当前用户拥有的对象权限：

select * from USER_TAB_PRIVS;

6、注意事项

        在Oracle中没有其它数据库系统中的数据库的概念，对象都是创建在用户下。当前用户具有当前用户下全部对象的全部权限无论该对象是否是当前用户所创建。举个简单例子，创建了girl用户并授予connect和resource权限，然后管理员在girl用户下创建一张表，girl用户可以删除管理员在girl用户下创建的表。

2.13 视图

视图是一个查看数据的窗口，是查询语句模板，视图本身没有数据，在数据库中只保存了视图的定义。

一、创建视图

语法：

create [or replace] [{force|noforce}] view 视图名
as
select查询语句
[with read only];

语法解析：

or replace：如果视图已经存在，则替换旧视图。

force：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

noforce：如果基表不存在，无法创建视图，该项是默认选项。

with read only：默认可以通过视图对基表执行增删改操作，with read only强调这是只读视图，不能通过该视图进行增删改操作。在实际工作中，基本上不会通过视图对表中的数据进行增删改操作。

示例一，在超女表中创建一个简单的视图V_GIRL，只提取编号、姓名、身材和颜值字段。

create or replace noforce view V_GIRL
as
select id,name,sc,yz from T_GIRL
with read only;

从V_GIRL视图中查询数据：

四、视图的优点（便捷、安全）

1）可以向数据访问者只开放访问视图的权限，屏蔽基表信息。

2）可以将复杂的查询语句保存为视图，简化了SQL语句的编写。

3）创建视图的时候，可以限制只访问基表中的部分列或者部分行的数据，这样可以实现一定的安全性。

4）简化用户权限的管理，只授予用户使用视图的权限。

5）从基表中按一定的业务逻辑抽出用户关心的部分数据创建视图，简化了业务逻辑。

2.14 数据字典

Oracle通过数据字典来管理和展现数据库内部的信息，数据字典储存数据库的元数据，是数据库的“数据库”。

数据字典系统表，保存在system表空间中。执行以下语句可以查询所有数据字典：

select * from dictionary;

user_tab_columns用户的表列信息

tab当前用户的表和视图

2.15 数据库链路 dblink

数据库链路（database link），简称dblink，它是一个通道，本地数据库与远程数据库之间的通道，通过dblink，在本地数据库中可以直接访问远程数据库的对象。

dblink不是应用程序与数据库之间的通道，而是数据库之间的通道。

一、创建dblink

1、创建dblink的权限

用dba权限登录数据库，授于用户create database link和create public database link权限。

语法：

grant create database link to 用户名;

grant create public database link to 用户名;

示例：

2、创建dblink的命令

语法：

create [public] database link 数据库链路名称
connect to 用户名
identified by 密码
using '远程数据库参数';

说明：

public：dblink的类型，缺省是当前用户私有的，只有当前用户可以使用该dblink，如果加上public选项，表示公用dblink，所有的数据库用户都可以使用。

数据库链路名称：给dblink起个名字。

用户名：登录远程数据库的用户名。

密码：登录远程数据库的用户的密码。

远程数据库参数：该参数的配置有两种方法：1）远程数据库的服务名，也就是本地数据库服务器上$ORACLE_HOME/network/admin/tnsnames.ora中配置的数据库服务名；2）不采用tnsnames.ora中的配置，直接指定远程数据库的参数。

1）远程数据库的服务名

远程数据库服务名在$ORACLE_HOME/network/admin/tnsnames.ora文件中配置，内容如下：

snorcl11g_198 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 118.89.50.198)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = snorcl11g)
      (SERVER = DEDICATED)
    )
  )

2）测试远程数据库登录参数

3）创建dblink

用远程数据库服务名创建dblink，例如：

create database link dblink_01 connect to shqx identified by pwdidc using 'snorcl11g_198';

4）测试dblink

通过dblink获取远程数据库的时间。

2）不采用tnsnames.ora中的配置，直接指定远程数据库的参数。

如果不用数据库服务创建dblink，就不需要配置tnsnames.ora文件。

示例：

create database link dblink_02 connect to shqx identified by pwdidc using
'(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 118.89.50.198)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SID = snorcl11g)
     (SERVER = DEDICATED)
   )
 )';

dblink_01和dblink_02完全一样。

二、dblink的使用

如果要访问远程数据库的表或视图，在表和视图名之后附加@dblinkname就可以了。

访问远程数据库的dual表：

select sysdate from dual@dblink_01;

访问远程数据库的scott.emp表：

select * from scott.emp@dblink_02;

三、删除dblink

删除当前用户私有的dblink：

drop database link 数据库链路名;

删除公用的dblink：

drop public database link 公用数据库链路名;

四、应用经验

dblink的知识很容易掌握，用dblink访问远程数据库的对象很方便，但是，如果在程序中采用dblink对远程数据库的表进行增、删、改、查操作时一定要遵守一个原则：尽可能不要产生远程事务，因为数据库对远程的事务难以控制，也就是说，尽可能不要对远程数据库的表进行增、删、改操作，查询是没有问题的。

2.16 同义词

同义词（synonym）就是数据库对象的别名，这里的数据库对象包括表、视图、序列、存储过程、包等。

同义词有两方面的意义：1）简化了数据库对象名称的书写；2）提高了数据库对象的安全性（因为只知道对象的别名，不知道它的真名）。

Oracle的dual虚表其实就是一个公用的同义词。

2.17 游标for update

在select语句后面加上for update，那么查询到的结果可以直接进行修改。

2.18 其他的知识和相关的概念

        还在学习中、、、