oracle_sql 基础 （5）

+++++++++++++++++++++++++
其他数据库对象
其他数据库对象的作用
序列：产生有规律的顺序值
索引：加快查询少量行的速度
同义词：降低访问长对象名时的复杂度

其他数据库对象的作用
许多应用程序要求使用唯一的数字作为主键的值。你即可以在应用程序中构建代码来处理这种需求，也可以用一个序列来产生唯一的数字。
如果你想要增进某些查询的性能，你应该考虑创建一个索引。你也可以用索引在列或列的集合上强制唯一性。你可以用同义词为对象提供可替代的名字。

序列
序列的创建命令：CREATE SEQUENCE Sequence
查询序列主要通过：NEXTVAL和CURRVAL两个伪列实现
可以使用ALTER SEQUENCE sequence修改序列
DROP SEQUENCE sequence命令将删除某个指定序列

序列是用户创建的数据库对象，序列可以被多个用户共享以产生唯一的整数。 序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。
序列由一个Oracle内部程序产生并增加或减少。 序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。
序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。
用CREATE SEQUENCE语句自动产生序列数。语法：
CREATE SEQUENCE Sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];

sequence 是序列发生器的名字
INCREMENT BY n 指定序列号之间的间隔，在这儿n是一个整数(如果该子句
被省略，序列增量为1)
START WITH n 指定要产生的第一个序列数(如果该子句被省略，序列从1
开始)
MAXVALUE n 指定序列能产生的最大值
NOMAXVALUE 对于升序序列指定10^27为最大值，对于降序序列指定-1
为最大值 (这是默认选项)
MINVALUE n 指定最小序列值
NOMINVALUE 对于升序序列指定1为最小值，对于降序序列指定-(10^26)
为最小值 (这是默认选项)
CYCLE|NOCYCLE 指定序列在达到它的最大或最小值之后，是否继续产生
(NOCYCLE是默认选项)
CACHE n|NOCACHE 指定Oracle服务器预先分配多少值，并且保持在内存中(默认
情况下，Oracle服务器缓冲20个值)
如果INCREMENT BY值是负数，序列是降序。另外，ORDER | NOORDER选项可用，ORDER选项保证序列值按顺序产生，如果你将序列用于产生主键值它是不重要的，该选项仅与Parallel Server(并行服务)选项有关。
如果序列值被高速缓冲，如果系统故障它们将被丢失。

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
Nocache
NOCYCLE;
一旦创建了序列，它就被文本化在数据字典中。因为序列是一个数据库对象，你可以在USER_OBJECTS数据字典表中识别它。
Select object_name,object_id,object_type From USER_OBJECTS Where object_name = 'DEPT_DEPTID_SEQ';
你也可以从USER_SEQUENCES数据字典视图中用选择确认序列的设置。
Select * From USER_SEQUENCES Where sequence_name = 'DEPT_DEPTID_SEQ'

用NEXTVAL和CURRVAL伪列引用序列值。NEXTVAL伪列用于从指定的序列中取回连续的序列数的下一个值。
你必须用序列名限定NEXTVAL，当你引用sequence.NEXTVAL时，一个新的序列数被产生并且当前的序列数被放入CURRVAL。
CURRVAL伪列被用于查阅当前当前用户刚才产生的序列数，NEXTVAL必须被在CURRVAL可以被引用之前用于在当前用户的会话中产生一个序列数，
你必须用序列名限定CURRVAL，当sequence.CURRVAL被引用时，最后返回给用户程序的值被显示。

使用规则：
我们可以在下面的上下文中使用NEXTVAL和CURRVAL：
一个不是子查询的一部分的SELECT语句的SELECT列表
在一个INSERT语句中子查询的SELECT列表
一个INSERT语句中的VALUES子句
一个UPDATE语句的SET子句
你不能在下面的上下文中使用NEXTVAL和CURRVAL：
一个视图的SELECT列表
一个带DISTINCT关键字的SELECT语句
一个带GROUP BY、HAVING或ORDER BY子句的SELECT语句
一个在SELECT、DELETE或UPDATE语句中的子句
在CREATE TABLE或ALTER TABLE语句中的DEFAULT表达式

使用序列
在内存中缓冲序列可以对序列值更快地存取，在你第一次使用到序列时被移入缓存。对于下一个序列值的每次请求从缓存的序列中找回。
在最后一个序列值被使用后，对于序列的下一个请求拉出另一个在内存中的序列缓存。
尽管序列发生器发布无间隙的序列数，但因为这种行为不依赖于提交和回退，如果你回退包含一个序列的语句，该序列数将丢失。
另一个能在序列中导致间隙产生的事件是系统崩溃，如果序列在内存中缓冲值，那么，如果系统崩溃那些值将丢失。
因为序列不直接依赖于表，所以，相同的序列可以被用于多个表，如果你这样做了，每个表都将包含序列数的间隙。

如果序列用带NOCACHE 的选项创建的，就可以在序列值不增加的情况下用查询USER_SEQUENCES表的方法，查看下一个可用的序列值。
经常使用的序列用带缓存创建将增进效率，对于缓存的序列，没有办法找出下一个可用的序列将是什么，
该值不实际获得和使用，因此，建议用户不要查找下一个序列值，而相信每次一个序列被用于一个INSERT 语句时系统会提供一个唯一值。
查看当前的序列值：
SELECT dept_deptid_seq.CURRVAL FROM dual;

修改序列的原则
你必须是被修改序列的所有者，或者有ALTER权限。
用ALTER SEQUENCE语句，只有以后的序列数会受影响。
用ALTER SEQUENCE语句，START WITH选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。
一些验证被执行，例如，一个新MAXVALUE如果小于当前的序列值就不能用。

使用DROP SEQUENCE语句从数据字典中删除一个序列。你必须是被删除序列的所有者或者有DROP ANY SEQUENCE权限来删除它。

应该只在下面的情况下创建索引：
列包含一个大范围的值、列包含大量的空值、一个或多个列在WHERE子句或连接条件中被频繁使用、表很大并且大多数查询所期望的返回行数少于总行数的2–4%。
不要忘记，如果你想要强制非唯一，你应该在表中定义一个唯一的约束，然后唯一索引被自动创建。

下面的情况通常不值得创建索引：
表很小，不经常在查询中作为条件被使用的列，大多数查询期望取回多于表中百分之2 到4 的行，表经常被更新，被索引的列作为表达式的的一部分被引用。

从USER_INDEXES数据字典视图可以确认索引的存在。你也可以查询USER_IND_COLUMNS视图，检查与索引有关的列。
Select Ic.Index_Name,
Ic.Column_Name,
Ic.Column_Position Col_Pos,
Ix.Uniqueness
From User_Indexes Ix, User_Ind_Columns Ic
Where Ic.Index_Name = Ix.Index_Name
And Ic.Table_Name = 'EMPLOYEES';

为了创建基于函数的索引，你必须有CREATE INDEX和QUERY REWRITE系统权限。
为了在另一个方案或另一个方案的表上创建索引，你必须有CREATE ANY INDEX和GLOBAL QUERY REWRITE权限。
在基于函数的索引中，表的所有者也必须有关于函数的使用EXECUTE对象的权限。

同义词
语法形式如下：
CREATE [PUBLIC] SYNONYM synonym FOR object
同义词其实在使用时是一种替换操作，Oracle会自动将同义词替换为它所代表的对象名，再对对象进行操作
如果有表和同义词相同，Oracle优化操作表
一个私有同义词名字对于同一个用户必须与所有其它的对象不同。
在Oracle服务器中，DBA可以明确授予CREATE PUBLIC SYNONYM权限给任何用户，这样普通用户就可以创建公共同义词。

++++++++++++++++++++++++++++
权限介绍
权限是执行特殊SQL语句的权利。数据库管理员(DBA) 是一个具有授予用户访问数据库及其对象的能力的高级用户。
用户需要系统权限来访问数据库，需要对象权限来操纵数据库中对象的内容。用户也可以将被授予的权限给其它用户或者角色，角色是相关权限的命名分组。
方案是对象的集合，例如，视图和序列的集合。方案被数据库用户多拥有，并且与用户有相同的名字。

DBA权限：
系统权限               授权的操作
CREATE USER            受让人可以创建其他 Oracle 用户 (需要有 DBA 角色权限)。
DROP USER            受让人可以删除另一个用户。
DROP ANY TABLE        受让人可以删除在任意方案中的表。
BACKUP ANY TABLE    受让人用导出实用程序可以备份在任何方案中的任何表。
SELECT ANY TABLE    受让人可以查询在任何方案中的表、视图或快照。
CREATE ANY TABLE    受让人可以在任何方案中创建表。

CREATE USER user IDENTIFIED BY password;

：CREATE SESSION 连接到数据库，CREATE TABLE 在用户的方案中创建表，CREATE SEQUENCE 在用户的方案中创建序列，
CREATE VIEW 在用户的方案中创建视图，CREATE PROCEDURE 在用户的方案中创建存储过程、函数或包。
当前会话的系统权限可以在字典视图SESSION_PRIVS中找到。
下面的例子进行系统权限的授权：
GRANT create session, create table, create sequence, create View TO scott;

CREATE ROLE manager;
GRANT create table, create view TO manager;
GRANT create table, create view TO manager;
GRANT manager TO DEHAAN, KOCHHAR;

为了改变任何其它的选项你必须有ALTER USER权限。
ALTER USER user IDENTIFIED BY password;

为了授予权限到一个对象上，对象必须在你自己拥有的方案中，或者你必须被用WITH GRANT OPTION选项授予了对象权限。
一个对象所有者可以授予任何该对象上的对象权限给任何其他的用户或者数据库的角色。
任何对象的所有者自动地获得该对象所有对象权限。

授予权限到以更新指定的列到用户和角色：
GRANT update (department_name, location_id) ON departments TO scott, manager;

GRANT select, Insert ON departments TO scott WITH GRANT OPTION;

允许所有在系统上的用户从Alice 的DEPARTMENTS 表中查询数据：
GRANT Select On alice.departments To PUBLIC;

数据字典视图            说明
ROLE_SYS_PRIVS            授予角色的系统权限
ROLE_TAB_PRIVS            授予角色的表权限
USER_ROLE_PRIVS            可由用户访问的角色
USER_TAB_PRIVS_MADE        授予用户的对象上的对象权限
USER_TAB_PRIVS_RECD        授予用户的对象权限
USER_COL_PRIVS_MADE        授予用户对象的列上的对象权限
USER_COL_PRIVS_RECD        授予用户在指定列上的对象权限
USER_SYS_PRIVS            授予用户的系统权限

可以用REVOKE语句撤消授予其他用户的对象权限。当你用REVOKE语句时，你指定要从用户那里撤消的权限，并且通过WITH GRANT OPTION子句被级联授权的那些用户的权限也将被撤消。语法：
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
CASCADE CONSTRAINTS 用于删除任何与该对象相关的约束和对象，例如索
引、触发器、权限、完整性约束等。


数据库链接
使用数据库链接可以访问其他数据库中的数据
数据库链接可以用来构建分布式数据库
Oracle数据库链接的使用非常简单，下面的语句创建一个数据库链接：
CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales';

一个数据库链接是一个指针，该指针定义一条从Oracle数据库服务器到另一个数据库服务器的通信路径。
链接指针实际上在一个数据字典表中被定义为一个条目，为了访问该链接，你必须被连接到包含数据字典条目的本地数据库。
一个数据库链接是一条有感知的路径，一个客户可以通过该路径连接到本地数据库 A，
再用存储在数据库A中的链接访问数据库B中的信息，但连接到数据库B的用户不能使用同一个链接来访问数据库A中的数据，他们必须定义一个链接，并存储在数据库B的数据字典中。
一个数据库链接的连接提供本地用户访问远程数据库上数据的能力，为了产生这种连接，在分布式计算机系统中的每个数据库必须有一个唯一的全局数据库名，
全局数据库名唯一地标识一个在分布式系统中的数据库服务器。
数据库链接最大的好处是，允许用户访问在远程数据库中的另一个用户的对象，但是他们被该对象所拥有的权限集合所限制，
换句话说，一个本地用户可以访问远程数据库而不需要是远程数据库上的用户。典型地，DBA负责创建数据库链接。
字典视图USER_DB_LINKS包含有关用户可以访问的链接信息。你不能在远程对象上授予权限。
下面的例子创建了用户SCOTT用全局名HQ.ACME.COM访问在远程数据库上的EMP。
CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales';
下面的例子使用数据库连接：
SELECT * FROM emp@HQ.ACME.COM;
下面的例子删除一个数据连接：
Drop DATABASE LINK hq.acme.com;

++++++++++++++++++++++++
SQL*Plus编辑命令
A[PPEND] text
C[HANGE] / old / new
C[HANGE] / text /
CL[EAR] BUFF[ER]
DEL
DEL n
DEL m n
I[NPUT]
I[NPUT] text
L[IST]
L[IST] n
L[IST] m n
R[UN]
n
n text
0 text

SQL*Plus 文件命令
SAVE filename
GET filename
START filename
@ filename
EDIT filename
SPOOL filename
EXIT
++++++++++++++++++++++++++