Oracle学习笔记8_维护数据完整性，管理密码安全和资源

本文链接：https://blog.csdn.net/m0_62902783/article/details/134384828

数据完整性

约束类型

NOT NULL

在列级别定义约束。
创建表时使用CREATETABLE来定义约束。下面的例子展示了ORDER_NUM列上的命名约束;对于ORDER_DATE, Oracle生成一个名称。

CREATE TABLE ORDERS (
ORDER_NUMNUMBER (4) CONSTRAINT NN ORDER_NUM NOT NULL
ORDER DATEDATE NOT NULL，
PRODUCT_ID)

使用ALTER TABLE MODIFY可以在现有表的列上添加或删除NOT NULL约束。下面的代码展示了删除约束和添加约束的示例。

ALTER TABLE ORDERS MODIFY ORDER DATE NULL;
ALTER TABLE ORDERS MODIFY PRODUCT_ID NOT NULL;

CHECK

它们可以在列级或表级定义。
CHECK子句中指定的条件应该计算为布尔结果，并且可以引用同一行其他列中的值;条件不能使用查询。
不能使用SYSDATE、USER、USERENV、UID等环境函数和ROWNUM、CURRVAL、NEXTVAL、LEVEL等伪列来评估检查条件。
一个列可以定义多个CHECK约束。列可以有一个NULL值。

它们可以使用CREATE TABLE或ALTER TABLE创建。

CREATE TABLE BONUS (
EMP_IDVARCHAR2 (40) NOT NULL,
SALARY NUMBER (9,2),
BONUS NUMBER (9,2),
CONSTRAINT CK_BONUS CHECK (BONUS > 0));

ALTER TABLE BONUS
ADD CONSTRAINT CK BONUS2 CHECK (BONUS < SALARY);

UNIQUE

可以在列级别为单列唯一键定义它们。对于多列唯一键(复合键-指定的最大列数可以是32)，约束应该在表级别定义。
Oracle在唯一键列上创建唯一索引来强制惟一性。如果表上已经存在一个唯一索引或非唯一索引，并且索引中有相同的列，则Oracle使用现有的索引。要使用现有的非唯一索引，表中必须不包含任何重复的键。
唯一约束允许约束列中的NULL值。
可以为创建键时创建的隐式索引指定存储空间。如果没有指定存储空间，则索引是在默认表空间上使用该表空间的默认存储参数创建的。您可以指定LOGGING和NOSORT子句，就像创建索引时一样。创建的索引可以是本地索引，也可以是全局分区索引。该索引将具有与唯一约束相同的名称。下面是两个例子。第一个定义了一个有两列的唯一约束，并为索引指定了存储参数。第二个例子在EMP表中添加了一个新列，并在列级别创建了一个唯一键。

ALTER TABLE BONUS
ADD CONSTRAINT UQ_EMP_ID UNIQUE (DEPT，EMP_ID)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 0);
ALTER TABLE EMP ADD
SSN VARCHAR2 (11) CONSTRAINT UQ SSN UNIQUE;

PRIMARY KEY

UNIQUE键的所有特征都适用，除了在主键列中不允许使用NULL值。

一个表只能有一个主键。

Oracle为键中的每一列创建一个唯一的索引和NOT NULL约束。如果主键的所有列都在索引中，Oracle可以使用现有索引。下面的例子在创建表时定义了一个主键。为表和主键索引指定存储参数。

为强制执行唯一键和主键而创建的索引可以像任何其他索引一样进行管理。然而，这些索引不能被显式删除。

CREATE TABLE EMPLOYEE (
DEPT_NO VARCHAR2 (2),
EMP_IDNUMBER (4)，
NAMEVARCHAR2 (20) NOT NULL,
SSNVARCHAR2 (11)，
SALARYNUMBER (9,2) CHECK (SALARY > 0)
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (DEPT_NO，EMP_ID)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 64K NEXT 64K)
NOLOGGING，
CONSTRAINT UQ_SSN UNIQUE (SSN)
USING INDEX TABLESPACE INDX)
TABLESPACE USERS
STORAGE (INITIAL 128K NEXT 64K);

FOREIGN KEY

外键是在其中创建约束的表(子表)中的一个或多个列;引用键是被约束引用的表(父表)中的主键、唯一键列或列。以下规则适用于外键约束:

可以在列级别或表级别定义外键约束的水平。在表级别定义多列外键。
外键列和引用键列可以在同一个表中(自引用完整性约束)。
外键列中允许使用NULL值。下面是在CITY表的COUNTRY_CODE和STATE_CODE列上创建外键约束的示例，该约束引用STATE表(STATE表的复合主键)的COUNTRY_CODE和STATE_CODE列。

ALTER TABLE CITY ADD CONSTRAINT FK STATE
FOREIGN KEY (COUNTRY_CODE，STATE_CODE)
REFERENCES STATE (COUNTRY_CODE，STATE_CODE);

DELETE NO ACTION “无操作”（默认）选项指定，如果生成的数据违反参照完整性约束，则无法更新或删除引用的键值。例如，如果主键值被外键中的值引用，则由于依赖数据，无法删除引用的主键值。
DELETE CASCADE当包含引用键值的行被删除时，删除级联，导致子表中具有依赖外键值的所有行也被删除。例如，如果父表中的一行被删除，并且该行的主键值被子表中的一个或多个外键值引用，则子表中引用主键值的行也会从子表中删除。
DELETE SET NULL 当包含引用的键值的行被删除时，delete 将设置为 null，从而导致子表中具有依赖外键值的所有行都将这些值设置为 null。例如，如果员工 ID 引用 TMP 表中的manager_id则删除经理会导致为该经理工作的所有员工的行的经理 ID 值设置为 null。

ON DELETE子句指定了当父表中的一行被删除并且子行存在且父主键被删除时所采取的操作。您可以删除子行(CASCADE)或将外键列值设置为NULL (SET NULL)。如果省略这个子句，如果存在子记录，Oracle将不允许从父表中删除。必须先删除子行，然后再删除父行。以下是在外键中指定删除操作的两个示例。

ALTER TABLE CITY ADD CONSTRAINT FK_STATE
FOREIGN KEY (COUNTRY_CODE，STATE_CODE)
REFERENCES STATE (COUNTRY CODE，STATE CODE)
ON DELETE CASCADE;

ALTER TABLE CITY ADD CONSTRAINT FK_STATE
FOREIGN KEY (COUNTRY_CODE，STATE_CODE)
REFERENCES STATE (COUNTRY CODE，STATE CODE)
ON DELETE SET NULL;

创建禁用的约束

当您创建约束时，它将自动启用。您可以通过在约束定义后指定disabled关键字来创建已禁用的约束。例如:

ALTER TABLE CITY ADD CONSTRAINT FK_STATE
FOREIGN KEY (COUNTRY_CODE，STATE_CODE)
REFERENCES STATE (COUNTRY_CODE，STATE_CODE) DISABLE;

ALTER TABLE BONUS
ADD CONSTRAINT CK BONUS CHECK (BONUS > 0) DISABLE;

删除约束

要删除约束，可以使用ALTER TABLE。您可以通过指定约束名称来删除任何约束。

ALTER TABLE BONUS DROP CONSTRAINT CK BONUS2;

要删除带有引用外键的唯一键约束，请指定CASCADE子句来删除外键约束和唯一约束。指定唯一键列。例如:

ALTER TABLE EMPLOYEE DROP UNIQUE (EMP ID) CASCADE;

要删除带有引用外键约束的主键约束，请使用CASCADE子句删除所有外键约束，然后删除主键。

ALTER TABLE BONUS DROP PRIMARY KEY CASCADE;

约束状态

完整性约束的有效使用:一个过程

按照以下顺序使用完整性约束状态可以确保获得最佳效益:

禁用状态。
执行操作(加载、导出、导入)。
启用novalidate状态。
启用状态。

按此顺序使用约束的一些好处是:

没有锁被持有。
所有约束都可以并发地进入启用状态。
约束启用是并行完成的。
允许在表上并发活动。

约束检查

Immediate vs Deferred

使用SET CONSTRAINTS语句将约束设置为DEFERRED或IMMEDIATE。
ALTER SESSION语句也有将约束设置为DEFERRED或IMMEDIATE的子句。

Primary Key和Unique Key执行

外键注意事项

并发外键

创建TB时的约束

CREATE TABLE hr.employee(
id NUMBER(7)
    CONSTRAINT employee id pk PRIMARY KEY
    DEFERRABLE
    USING INDEX
    STORAGE(INITIAL 100K NEXT 100K)
    TABLESPACE indx,
last_name VARCHAR2(25)
    CONSTRAINT employee_last_name_nn NOT NULL,
dept id NUMBER(7))
TABLESPACE users;

约束指引

主要和唯一的约束:

将索引放在单独的表空间中。
如果批量加载频繁，请使用非唯一索引。

Self-referencing外键:

在初始加载后定义或启用外键。
推迟约束检查。

启用约束

ALTER TABLE hr.departments
ENABLE NOVALIDATE CONSTRAIT dept_pk;

ALTER TABLE hr.employees
ENABLE VALIDATE CONSTRAINT emp_dept_fk;

约束重命名

使用以下命令重命名约束:

ALTER TABLE employees
RENAME CONSTRAINT emp_dept_fk
TO employees_dept_fk;

使用exceptions表

通过运行utlexpt1.sql脚本创建EXCEPTIONS表。
执行带有EXCEPTIONS选项的ALTER TABLE语句。
使用ÉXCEPTIONS上的子查询查找包含无效数据的行。
纠正错误。
重新执行ALTER TABLE以启用约束。

获取约束信息

可以通过查询以下视图获取约束信息:

DBA_CONSTRAINTS
DBA_CONS_COLUMNS

配置文件（Profile）

配置文件是一组已命名的密码和资源。
配置文件通过CREATEUSER或ALTER USER命令分配给用户。
配置文件可以启用或禁用。
概要文件可以与DEFAULT概要文件相关联。

密码管理

开启密码管理

将它们分配给用户。通过使用配置文件和设置密码管理。
使用CREATE USER或ALTER USER命令锁定、解锁和过期帐户。
密码限制总是强制执行的。
要启用密码管理，以SYS用户身份运行utlpwdmg.sql脚本。

密码帐号锁定

密码历史

密码验证

用户提供的Passwd函数

该函数必须在SYS模式下创建，并且必须具有以下规格:

function name(
userid parameter IN VARCHAR2(30),
password parameter IN VARCHAR2(30),
old password parameter IN VARCHAR2(30))
RETURN BOOLEAN

Oracle提供的函数 Verif function: VERIFY_FUNCTION

最小长度为4个字符。
密码不应该等于用户名。
密码至少包含一个字母、一个数字和一个特殊字符。
密码不能与以前的密码相同至少三个字母不同。

创建配置文件

CREATE PROFILE grace_5 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_GRACE_TIME 5;

修改配置文件

ALTER PROFILEdefault LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;

删除配置文件

使用Drop profile命令删除配置文件。

DEFAULT配置文件不能被删除。

CASCADE从分配给其的用户中撤销配置文件。

DROP PROFILE developer_prof;

DROP PROFILE developer_prof CASCADE;

资源管理

资源管理限制可以在session级别、call级别或两者同时实施。
配置文件可以使用CREATE PROFILE命令定义限制。
使用命令启用资源限制:

ESOURCE_LIMIT初始化参数

ALTER SYSTEM命令

启用资源限制

将初始化参数RESOURCE_LIMIT设置为TRUE。

通过使用ALTER SYSTEM命令启用该参数来强制执行资源限制。

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

Setting @ Session 级别

Setting @ Call 级别

创建配置文件

CREATE PROFILE developer prof LIMIT
SESSIONS PER USER 2
CPU PER SESSION 10000
IDLE TIME 60
CONNECT TIME 480;

RM方式管理资源

为Oracle服务器提供对资源管理决策的更多控制
数据库资源管理器的元素:

资源消费群体

资源计划

资源分配方法

资源计划指令

使用DBMS_RESOURCE_MANAGER包创建和维护元素
需要ADMINISTER_RESOURCE_MANAGER权限
资源计划指定属于该计划的资源消费者组。
资源计划包含如何分配的指令在消费者群体之间分配资源。

资源计划指令

数据库资源管理器提供了几种分配资源的方法:

CPU的方法
活动会话池和排队
并行度限制
自动消费者群体切换
最大估计执行时间
取消配额

获取信息

可以通过以下视图查询密码和资源限制信息:

DBA_USERS
DBA_PROFILES