oracle基本管理（3）

维护数据完整性共有三种主要方法：
应用程序代码、数据库触发器、声明完整性约束。
缺省情况下，表中的所有列均可以为空。空意味着没有值。NOT NULL 约束要求表列必须包含值。
UNIQUE 关键字约束要求某列或一组列（关键字）中的值必须是唯一的。表中的任何两行在指定的一列或一组列中不能有重复的值。
数据库中的每个表至多有一个 PRIMARY KEY 约束。PRIMARY KEY 约束确保以下两种情况均为真：表中的任何两行在指定列中没有重复的值。
主键列不包含NULL 值某列或一组列上的CHECK 完整性约束要求，对于表的每一行，指定的条件必须为真或未知。
虽然 NOT NULL 和CHECK 约束并不直接要求DBA 关注，但PRIMARY KEY、UNIQUE 和FOREIGN KEY 约束仍须进行管理，以确保高可用性和性能水平可接受。
约束有如下四种状态：
DISABLE NOVALIDATE
DISABLE VALIDATE
ENABLE NOVALIDATE
ENABLE VALIDATE
DISABLE NOVALIDATE：不检查处于DISABLE NOVALIDATE 状态的约束。表中的数据（包括输入或更新的新数据）可以不符合约束所定义的规则。
DISABLE VALIDATE：当约束处于此状态时，不允许对受约束的列进行任何修改。另外，约束上的索引将被删除并且禁用约束。注：如果约束可延迟，则不删除索引。
ENABLE NOVALIDATE：如果约束处于此状态，则不能输入违反约束的新数据。但是，表可能包含无效的数据，即数据违反约束。启用处于NOVALIDATE 状态的约束对正在上载有效OLTP 数据的数据仓库配置是非常有用的。
ENABLE VALIDATE：如果约束处于此状态，则不能将违反约束的行插入到表中。但是，禁用该约束时，可以插入此类行。
此类行称为该约束的例外。如果约束处于ENABLE NOVALIDATE 状态，则在禁用约束时输入的数据所引起的违反情况仍然存在。要将约束置于已验证状态，必须更新或删除违反约束的行。
当某一约束由禁用状态更改为 ENABLE VALIDATE 时，将锁定表并对表中的所有数据进行一致性检查。这可能会引起DML 操作（如等待数据加载），因此，建议先从禁用状态转为ENABLE NOVALIDATE，然后再转为ENABLE VALIDATE。
这些状态之间的转换须符合以下规则：
除非指定 NOVALIDATE，否则ENABLE 表示VALIDATE。
除非指定 VALIDATE，否则DISABLE 表示NOVALIDATE。
VALIDATE 和NOVALIDATE 没有缺省的ENABLE 和DISABLE 状态。当唯一键或主键从DISABLE 状态转为ENABLE 状态且没有现有索引时，将自动创建唯一索引。（如果索引可延迟，则将存在异常。）与此类似，当唯一键或主键从ENABLE 转为DISABLE 且是使用唯一索引启用时，则删除该唯一索引。
当任何约束从 NOVALIDATE 状态转为VALIDATE 状态时，必须检查所有的数据。但是，从VALIDATE 转为NOVALIDATE 时，将忽略数据已经过检查这一事实。将单个约束从ENABLE NOVALIDATE 状态转为ENABLE VALIDATE 状态时，并不禁止使用读取、写入或其它DDL 语句。

立即约束
在每次改变发生时马上检查约束
如违反约束只回滚发生修改的DML语句
延迟的约束
在事务结束时检查约束
如违反约束回滚整个事务
约束的注意事项
主键和唯一键通过索引执行
如果关键字为可延迟，则在关键字列上创建一个非唯一索引。
如果关键字为不可延迟，则将创建一个唯一索引。
在未删除或禁用外键之前无法截断父表
在删除包含父表的表空间之前， 必须先删除外键

主键和唯一键通过索引执行。可控制用来执行这些约束的索引的位置和类型。Oracle 服务器按下列步骤实现唯一键和主键约束：
如果约束被禁用，则不需要索引。如果启用约束且约束中的列构成索引的主要部分，则无论是否将索引本身创建为唯一还是非唯一索引，都可以使用该索引执行约束。如果启用约束且没有任何索引将约束列用作索引的主要部分，则按照下列规则创建一个名称与约束相同的索引：
如果关键字为可延迟，则在关键字列上创建一个非唯一索引。
如果关键字为不可延迟，则将创建一个唯一索引。
如果可以使用某个索引，并且约束是不可延迟的，则使用现有索引。如果约束是可延迟的，并且索引是非唯一的，则使用现有索引。
维护外键关系中的表时，应该考虑几个因素。
涉及父表的 DDL：
在删除父表之前，必须先删除外键。可以使用以下一条语句同时执行这两个操作：
DROP TABLE table CASCADE CONSTRAINTS
在未删除或禁用外键之前无法截断父表。
在删除包含父表的表空间之前，必须先删除外键。可使用下列命令完成该操作：
DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
如果从父表中删除行时没有使用 DELETE CASCADE 选项，Oracle 服务器必须确保子表中的行不包含相应的外键。同样，仅当子行中不包含旧键值时，才允许更新父键。如果子表的外键上没有索引，则Oracle 服务器锁定子表并禁止更改以确保引用完整性。如果表上有索引，则可通过锁定索引项并避免子表上有更具限制性的锁来维护引用完整性。如果必须从不同的事务处理同时更新两个表，则在外键列上创建索引。
当在子表中插入数据或更新子表中的外键列时，Oracle 服务器检查父表上用来执行引用关键字的索引。因此，仅当包含索引的表空间联机时，该操作才能成功。注意，包含父表的表空间在子表上执行DML 操作时不需要联机。
Oracle9i 在主键上执行更新或删除操作时，不再要求在未建索引的外键上获取共享锁定。它仍然获取表级共享锁定，但在获取后立即释放该锁定。如果更新或删除多个主键，则每行获取和释放一次锁定。

约束定义的原则
将用于执行主键约束和唯一性约束的索引与表放在不同的表空间中
如果经常向表中批量加载数据，则最好先禁用约束，执行完加载后再启用约束
如果表中包含自引用外键，最好在初始加载后定义或启用外键，或将约束定义为可延迟的约束。
定义约束时遵循下列原则十分有用：
将用于执行主键约束和唯一性约束的索引与表放在不同的表空间中。这可通过指定USING INDEX 子句或通过创建表、创建索引并改变表以添加或启用约束来实现。
如果经常向表中批量加载数据，则最好先禁用约束，执行完加载后再启用约束。如果唯一索引用于执行主键约束或唯一性约束，则在禁用约束时必须删除该索引。
在这种情况下，可以使用非唯一索引执行主键约束或唯一性约束来改善性能：创建可延迟的键，或者在定义或启用键之前创建索引。
如果表中包含自引用外键，请使用下列方法之一加载数据：在初始加载后定义或启用外键。将约束定义为可延迟的约束。
在频繁加载数据的情况下，第二种方法非常有用。

执行使用EXCEPTIONS 子句的ALTER TABLE 命令：
ALTER TABLE hr.employee ENABLE VALIDATE CONSTRAINT employee_dept_id_fk EXCEPTIONS INTO system.exceptions;

通过查询以下视图获取有关约束的信息：
DBA_CONSTRAINTS、DBA_CONS_COLUMNS

对于不再使用的配置文件，可以使用：DROP PROFILE 文件名[CASCADE]，删除配置文件。但是，此命令只能删除没有分配给任何用户的空闲配置文件。DEFAULT 配置文件是无法被删除的。

create profile pro2 limit failed_login_attempts 3 PASSWORD_LOCK_TIME 1;
配置文件已创建
我创建了配置文件PRO2，在其中将failed_login_attempts定为了3，而PASSWORD_LOCK_TIME定为了1。配置文件中还有很多参数，其他的参数都取默认值。
现在我将此配置文件分配给UPLOOKING用户。alter user uplooking profile pro2;
FAILED_LOGIN_ATTEMPTS n ：当用户登录N次后仍没有输入正确的密码，用户被自动的锁定。
PASSWORD_LOCK_TIME n ：当达到上面参数的限制，用户被锁定后，用户被锁定的天数。这个值可以是小数或分数，例如，如果想将此参数定为1小时，值为1/24，10分钟为10/1440。
配置文件中，所有单位是天数的参数，都可以使用小数、分数，将参数值定为1天以内的值。
PASSWORD_LIFE_TIME n ：设置口令的有效期，单位是天数。
PASSWORD_GRACE_TIME n ：宽限期。
口令无效后，用户应该即时的更换口令。在指定的宽限期内，如果用户仍没有更换口令，用户将无法登录。在宽限期内每登录一次，都会收到一条警告信息。
PASSWORD_REUSE_TIME ：可以重复使用以前用过的口令前的天数。也就是当设置新口令后，在N天内，不能使用以前用过的老口令再做为新口令。
PASSWORD_REUSE_MAX ：某一口令最多被使用的次数。
如果将PASSWORD_REUSE_MAX设置为3，在更改用户口令时，某一串符号被三次设置为口令后，这串符号将不能再被设为口令。

启用资源限制
在配置文件中，除了口令管理类参数外，还有一类参数是资源限制类参数。通过资源限制类参数，可以限制一个会话或一个调用所使用的CPU时间、I/O数量等等值。
资源限制类参数的使用，要比口令管理多一个步骤，就是先需要设置初始化参数resource_limit。
这个参数是动态的，两种取值TRUE和FALSE。设为TRUE代表启用资源限制，FALSE为禁用资源限制。默认值就是FALSE。此参数可以在会话级和系统级动态设置。在系统级设置的值，将影响所有会放在。
配置文件中的口令管理参数，不受此参数的限制。
配置文件中的资源限制参数又分为两类，一类针对会话级，另一类针对调用级。下面我们先从会话级资源限制参数说起。

会话级资源限制
会话级资源限制，主要针对会话，在配置文件中，有下面几个会话级资源限制参数：
CPU_PER_SESSION ：会话所能使用的CPU时间总量。单位是百分之一秒。
SESSIONS_PER_USER ：同一用户所能的开的最多会话数
CONNECT_TIME ：以分钟计算的会话持续连接时间
IDLE_TIME ：以分钟计算的空闲时间
LOGICAL_READS_PER_SESSION ：会话所能读取的数据块数。
PRIVATE_SGA ：在共享服务器模式下，每个用户连接所能使用的SGA中的空间，单位是字节。
如果开启了资源限制的会话，超出了上面这些限制，将会报出ORA-02391错误，ORACLE将同时断开会话的连接。

调用级资源限制
调用级别的资源限制，主要针对每条命令的执行。每条命令的执行都是一次调用，调用级资源限制决定了每次调用所能使用的CPU时间和I/O数。调用级资源限制参数有上面两个：
CPU_PER_CALL ：每次调用所能占用的CPU时间，单位百分之一秒
LOGICAL_READS_PER_CALL ：每次调用所能读取的数据块数
调用级资源限制也可以称为语句级资源限制。它只针对某一条语句，限制某条语句执行所用的资源。
如果某条语句超出了它的限制，只有本条语句被停止，这一样上，它不像会话级的限制，在会话级限制中，超出的会话将会被注销。
如果是DML语句超出，只有超出的那条语句被回滚，整个事务不会被回滚。

会话级和调用级资源限制的参数在一个配置文件中可以同时使用，这样即限制了会话级别的资源使用，又对单条语句进行了限制。
要注意的是，在为用户分配了新的配置文件后，无论会话级还是调用级的限制，只有当会话重新连接后，才会生效。而有配置文件中有关密码的参数，只要将配置文件分配给用户，其内部的参数将会马上生效。
alter profile pro4 limit CPU_PER_CALL 1000;
alter profile pro1 limit PASSWORD_VERIFY_FUNCTION default;

用户是对数据中所有对象的逻辑分割。每一个用户可以有自己的表、视图、索引、序列、同义词、约束等数据库对象。不同用户中的数据对象可以重名。
只所以说用户只是数据库对象的逻辑分割，是因为分属不同用户的对象，完全有可能存储在同一磁盘空间的同一数据文件中。
用户并不决定一个数据库对象的具体物理存贮位置，因此说用户是数据库对象的逻辑分割。

可以在使用多个QUTOA子句，在多个表空间中，为用户指定所使用储存空间的限额：
sid=38 pid=19> alter user u3 quota 1M on users quota 2m on exam1;
另有一种方法可以让用户在每个表空间中都可以使用无限制的限额，就是授予用户UNLIMITED TABLESPACE权限：
sid=38 pid=19> grant UNLIMITED TABLESPACE to u3;

另有一种方法可以让用户在每个表空间中都可以使用无限制的限额，就是授予用户UNLIMITED TABLESPACE权限：
sid=38 pid=19> grant UNLIMITED TABLESPACE to u3;

UNLIMITED TABLESPACE权限是优先于限额限制的，虽然上面已经指定了U3用户在USERS中可以使用1M的空间，在EXAM1中可以使用2M的空间外，
但U3用户一旦有了UNLIMITED TABLESPACE 权限，它就可以在任何表空间中使用无限制的存储空间。
如果在用户正在使用过程中，将用户在某一表空间上的限额设为了0，那么，用户在此表空间中不能再分配新的存储空间，但仍可以正常使用原来已分配的存储空间。
好，有关空间限额的问题就说到这里，下面我看下一个选项：

PASSWORD EXPIRE
当创建用户时选择了此选项，或者使用了ALTER USER将用户的状态改为此选项后，再次以指定用户，登录到ORACLE时，将强制使用户在登录成功后更改密码，例如，为U3用户设置此选项：
sid=38 pid=19> alter user u3 PASSWORD EXPIRE;

用户的密码都是记录在数据字典表中，只有一个用户例外，它就是管理员用户，对于它，Oracle使用：
操作系统验证
密码文件验证
LINUX下的ORACLE管理用户的组是DBA。只要用户属于DBA组，也可以使用操作系统验证。
ORACLE在数据库被创建前，已经为数据库创建好了一份密码文件。密码文件中的内容不能修改，如果你想改变什么，只能重建。重建密码文件的命令如下：
Orapwd file=文件位置/名字password＝密码entries＝最大用户数
注意，在创建密码文件前，如果以前已经有了一个密码文件，应该先将原密码文件删除，否则，创建会报出错误
关于密码文件验证方式，有一个初始化参数：remote_login_passwordfile ，显示一下它的值：
sid=49 pid=14> show parameter remote_login_passwordfile
它的默认值就是EXCLUSIVE ，意思启动密码文件验证方式。此参数还有一个值NONE，就是禁用密码文件验证方式。下面我把它改为NONE试一下：
sid=49 pid=14> alter system set remote_login_passwordfile=NONE scope=spfile;


DROP USER user [CASCADE]
原则：在删除用户前，CASCADE 选项将删除方案中的所有对象。如果方案中包含任何对象，则必须指定该选项。不能删除当前与Oracle 服务器连接的用户。
查询用户的信息：DBA_USERS、DBA_TS_QUOTAS。

创建角色时，可以将角色创建为
不需验证角色的创建：
CREATE ROLE 角色名
使用口令验证的角色：
CREATE ROLE 角色名IDENTIFIED BY 密码
还有一种应用程序角色，专用于应用程序中：
CREATE ROLE 角色名IDENTIFIED BY 程序名
需要验证的角色在使用时，必须输入正确的密码，用户才可获得角色中的权限。
角色的修改，主要指的是修改角色的验证方式。将带口令的角色改为不带口令的角色：ALTER ROLE 角色名NOT IDENTIFIED；将不带口令的角色改为带口令的角色：ALTER ROLE 角色名IDENTIFIED BY 密码。

显示一下用户U1的权限：
sid=39 pid=16> select * from SESSION_PRIVS;
ORACLE中另有一个“缺省角色”的概念。每个用户都可以拥有一些缺省角色，只要是用户的缺省角色，在用户被连接后，将会自动启用，无论是否带有密码。
而用户的非缺省角色，在用户连接的同时，并不会自动的启用，用户需要使用额外的命令，启用角色。
对于非缺省的角色，在用户启用时，ORACLE会要求提供密码信息，不带密码的角色当然就不必提供了。
而默认方式下使用“GRANT 角色To 用户”方式授予用户的角色，都是用户的缺省角色。有一个关于用户角色的视图可以显示出此点：
GRANTEE ：用户名
GRANTED_ROLE ：角色名
ADMIN_OPTION ：在授予角色时,是否带有WITH ADMIN OPTION选项。也就是用户是否可以把角色转授他人
DEFAULT_ROLE ：该角色是否是用户的缺省角色。
下面我显示一下U1用户所拥有的角色：
select * from dba_role_privs where grantee='U1';

设置用户的缺省角色：
ALTER USER 用户名DEFAULT ROLE 角色名|ALL|NONE|ALL EXCEPT 角色名
角色名：指定要把用户的那个角色定为缺省角色。我们可以在此处以逗号分隔，一次性将多个角色指定为用户的缺省角色。凡是不是此处指定的角色，都不再是用户的缺省角色。
ALL ：把用户所拥有的所有角色都定为缺省角色。
NONE ：让用户所有的角色都不是缺省角色。
ALL EXCEPT 角色名：除指定的角色外，其他的角色都是缺省角色。我们也可以在此处以逗号分隔指定多个角色。
好，下面我将ROLE_U1指定为U1的非缺省角色：
sid=42 pid=15> alter user u1 default role all except role_u1;
启用非缺省角色的命令是：
SET ROLE 角色1[IDENTIFEID BY密码]，角色2[IDENTIFEID BY密码]，……;
使用这个命令可以一次性启用多个角色，多个角色间用逗号分隔。如果某个角色是不带密码的，则不需要IDENTIFIED选项。
这条命令还有如下的使用形式：
SET ROLE ALL EXCEPT 角色|ALL|NONE
ALL ：启用所有角色。如果用户的角色中，有带密码的角色，这个命令的运行将会失败。它只能用于没有带密码角色的用户中。
ALL EXCEPT 角色：是禁用指定角色，启用其他的角色。和上面一样，被启用的角色中，不能有带密的。
NONE ：禁用所有的角色
注意一个初始化参数，max_enabled_roles，它决定了用户最多可以启用的角色数。

grant role_u1 to u2;
如果我现在撤消U1用户的ROLE_U1角色，对U2用户有怎样的影响呢？
sid=42 pid=15> revoke role_u1 from u1;
U2用户仍然拥有ROLE_U1角色。
总结一下通过with admin option转授的权限、角色撤消时的影响，下面将权限或角色授予于A，A又授予B，那么，在撤消A的权限或角色时：
对象权限：B的权限随之被撤消。
系统权限：B的权限不受影响。
角色：B的角色不受影响。
也就是除了对象权限外，转授予的系统权限、角色都不会有影响。\

删除角色，非常简单：
 drop role role_name
 可以删除已授予用户的角色
 角色相关的权限在用户下次登录时被取消
把系统权限或对象权限、角色授于PUBLIC，PUBLIC是公共的意思，每个用户都将自动拥有被授予PUBLIC的权限、角色。命令的使用形式非常简单：
sid=42 pid=15> grant create session to public;
从PUBLIC撤消权限、角色和从普通用户中撤消是一样的：
sid=42 pid=15> revoke create session from public;
ORACLE为我们创建了大量的预定义角色，也就是在创建数据库时，ORACLE自动创建的角色。常用的有CONNECT、RESOURCE和DBA等

查看数据库的所有角色，视图是：DBA_ROLE。角色可以授予用户，也可以授予角色，DBA_ROLES_PRIVS视图中就显示了所有授予用户、角色的角色，这个视图我们前面有过了好几次。
而ROLE_ROL_PRIVS中，则只显示了授予角色的角色。session_roles显示了当前用户所有已启用的角色。
系统权限可以直接的授予用户，也可以授予角色，DBA_SYS_PRIVS视图显示了授予用户或角色的系统权限。
ROLE_SYS_PRIVS则只显示授予角色的系统权限。对象权限也可以授予角色，dba_tab_privs视图显示了授予用户、角色的对象权限，ROLE_TAB_PRIVS只显示授予角色的对象权限。


权限分为两大类，系统权限和对象权限。系统权限并不针对具体某一个表、视图数据库对象等等。
它只针对操作，你是否可以创建、删除、查看等等。而对象权限只针对某一个数据库对象，它决定一个用户对于一个对象，可以做怎样的操作。比如是否可以查看、删除、更新等等。
重要的权限相关视图有：
 DBA_SYS_PRIVS ：显示某一个用户所拥有的系统权限。
 SESSION_PRIVS，它显示当前会话的用户，所被授予的权限
 DBA_TAB_PRIVS：用户所拥有的对象权限。
 GRANTEE ：被授予对象权限的用户。
OWNER ：对象的所有者
TABLE_NAME ：对象名。
GRANTOR ：对象权限的授予者。也就是那个用户将对象权限授予用户的。
PRIVILEGE ：权限名
GRANTABLE ：在授予对象权限时，是否带有WITH GRANT OPTION选项。
DBA_COL_PRIVS，它显示以列为单位，授于用户的对象权限 grant update(dept) on dept1 to u1;

SYSDBA和SYSOPER权限
这两个权限比较特殊。即使把它俩授予用户后，用户再次登录数据库，仍没有SYSDBA和SYSOPER中的权限。
这是因为SYSDBA和SYSOPER权限比较特殊，它们两个允许用户完成著如启动、关闭数据库等管理性操作。想要用这两个权限登录数据库，登录方式必须是：
Conn sys/密码as sysdba|sysoper
AS后的SYSDBA和SYSOPER决定了是以SYSDBA权限登录数据库，还是以SYSOPER权限登录数据库。SYSDBA权限中包括了对数据库所能做的所有操作。SYSDBA权限的功能，包括SYSOPER权限。
SYS方案
SYS用户中存储的有数据库中最重要的数据字典表、X$视图，和在它们两个之上的数据字典视图、动态性能视图。
拥有SELECT ANY TABLE权限的用户仍然无法访问SYS用户中的DBA_系列视图、数据字典表
O7_DICTIONARY_ACCESSIBILITY的默认值是FALSE，此时，SELECT ANY TABLE权限不包括SYS用户中的一些特殊表或视图。
如果将O7_DICTIONARY_ACCESSIBILITY值定为TRUE，被授予了SELECT ANY TABLE权限的用户将可以显示数据库的所有表。这个参数是一个静态参数，修改后必须重启数据库。
sid=42 pid=15> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
。注意ORACLE为所有的DBA_系列视图都建立有公有同义词，因此访问DBA_系列视图可以不必像SYS.DBA_这样加用户名。
我们要注意O7_DICTIONARY_ACCESSIBILITY参数可能带来的安全性问题。它使它普通用户可以访问到SYS中的一此表。建议这个参数一直保留为假比较好。

可使用 DDL 命令GRANT 和REVOKE 控制权限，这两个命令为用户或角色添加和撤消系统权限。
被授予者可通过 ADMIN 选项进一步为其他用户授予系统权限。使用ADMIN 选项授予系统权限时应小心。这样的权限通常只限于安全管理员使用，很少授予其他用户。
撤消系统权限：可以使用REVOKE SQL 语句撤消系统权限。使用ADMIN OPTION 授予系统权限的用户可以撤消任何其他数据库用户的权限。撤消者不必是原先授予该权限的那个用户。
REVOKE 命令只能撤消使用GRANT 命令直接授予的权限。
撤消系统权限可能对一些相关对象有影响。例如，如果将 SELECT ANY TABLE 授予某用户，而该用户已创建了使用其它方案中的表的过程或视图，则撤消该权限将使这些过程或视图无效。

授予和撤销对象权限
REVOKE 语句用来撤消对象权限
要撤消对象权限，撤消者必须是被撤消的对象权限的原始授予者
撤销对象权限的限制：授予者只能对其已经授予权限的用户撤消对象权限
CASCADE CONSTRAINTS：删除撤消使用REFERENCES 或ALL 权限定义的任何引用完整性约束
撤销对象权限的限制：授予者只能对其已经授予权限的用户撤消对象权限。