. System privilege:Enables users to perform particular actions in the database
. Object:Enables users to access and manipulate a specific object
. There is no CREATE INDEX privilege.
. CREATE TABLE includes the CREATE INDEX and the ANALYZE commands. The user
must have a quota for the tablespace or must have been granted UNLIMITE TABLESPACE.
. Privileges such as CREATE TABLE, CREATE PROCEDURE, or CREATE CLUSTER include the dropping of these objects.
. UNLIMITED TABLESPACE cannot be granted to a role.
. For truncating a table in another schema, the DROP ANY TABLE privilege is necessary.
3.Constraint States
An integrity constraint can be enable(data is checked as it is entered or updated) or disabled(data that dose not confirm to the constraint’s rule is prevented from being entered).
An integrity constraint can be in one of the following states:
.DISABLE NOVALIDATE:Old data and new data will not be checked.And the index will be dropped.
.DISABLE VALIDATE:Any modification of the constrained columns is not allowed.In addition,the index on the constraint is dropped and the constraint is disabled.
.ENABLE NOVALIDATE:new data(will be inserted into table) check, old data(original data in the table) not check.If it’s on primary key ,novalid will still check the old data.
Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.
.ENABLE VALIDATE:Old data and new data will be checked.When a constraint changes to enable validate from disabled state,the table is loced and all data in the table
is checked for soformity.This may cause DML operations such as a data load to wait,so it is advisable to move first from a disabled state
to enable novalidate,and then to enable validate.
Note:Unique or primary key moves from the DISABLE state to ENABLE state and there’s no existing index,a unique index is created automatically.
Similarly,when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index,the unique index is dropped.
Moving a single constraint from ENABLE NOVALIDATE state to ENABLE VALIDATE state dose not block reads,writes or other DDL statements.
Nodeferred (Immediate) Constraints
Enforced at the end of every DML statement.A constraint that is defined as nondeferrable cannot be modified to be enforced at the end of transaction.
Deferred Constraints
Deferred constraints are constraints that are checked only when a transaction is commited.
A constraint that is defined as deferred can be specified as one of the following
.Initially immediate specifies that by default it should function as an immediate constraint unless explicitly set otherwise.
.Initially deferred specifies that by default the constraint should only be enforced at the end of the transaction
5.Changing the Enforcement of Constraints
.SET CONSTRAINTS statement
1
2
3
4
ALTER SESSION
SET CONSTRAINT | CONSTRAINT[S]{constraint |ALL}{IMMEDIATE|DEFERRED}
The SET CONSTRAINT mode lasts for duration of the transaction or until another SET CONSTRAINTS statement resets the mode.
The SET CONSTRAINT statement is disllowed inside triggers.
.ALTER SESSION statement
ALTER SESSION
SET CONSTRAINT[S] =
{IMMEDIATE|DEFERRED|DEFAULT}
The ALTER SESSION statement imply setting ALL deferrable constraints statmemt applies to a current session only.
6.Primary and Unique Key Enforcement
Primary and unique keys are enforced using indexes.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
IF Key_enabled='Yes' THEN
IFIS an INDEX availabe FORUSE?='Yes'IF Constraint Deferrable?='Yes' THNE
USE existing INDEX;
ELSE
USE existing INDEX;
END IF;
ELSE
IF Constraint deferrable?='Yes' THEN
CREATE nonunique INDEX;
ELSE
CREATEUNIQUEINDEX;
END IF;
END IF;
ELSE
Do NOTUSEINDEX;
END IF
7.Foreign Key Considerations
--------------------------------------------------------------------------------------------------
Desired Action |Applicaton sulution |
--------------------------------------------------------------------------------------------------
Drop parent table |Cascade constraints |
--------------------------------------------------------------------------------------------------
Truncate parent table |Disable or drop foreign key |
--------------------------------------------------------------------------------------------------
Drop tablespace containing parent table|Use the CASCADE CONSTRAINTS clause |
--------------------------------------------------------------------------------------------------
Perform DML on child table |Ensure the tablespace constaining the parent key is online|
--------------------------------------------------------------------------------------------------
.The foreign key must be dropped before dropping the parent table.
DROP TABLE table CASCADE CONSTRAINTS
.The parent table cannot be truncated whithout dropping or disabling the foreign key.
.The foreign must be dropped before tablespace containing the parent is dropped.
1
DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
If there is no index on the foreign key on the child table, the Oracle server
locks the child table and prevents changes to ensure referential integrity. If there is an index on
the table, the referential integrity is maintained by locking the index entries and avoiding more
restrictive locks on the child table. If both tables need to be updated concurrently from different
transactions, create an index on the foreign key columns.
When data is inserted into or the foreign key column is updated in the child table, the Oracle
server checks the index on the parent table that is used for enforcing the referenced key.
Therefore, the operation succeeds only if the tablespace containing the index is online. Note that
the tablespace containing the parent table does not need to be online to perform DML operations
on the child table.
Oracle9i no longer requires a share lock on unindexed foreign keys when doing an update or
delete on the primary key. It still obtains the table-level share lock, but then releases it
immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained
and released once per row.
8.Defining Constraings While Creating a Table
Syntax
10.Guidelines for Defining Constraints
. Place indexes used for enforcing primary key and unique constraints in a tablespace different from that of the table.
. If data is frequently loaded in bulk into a table, it is preferable to disable the constraints,
perform the load, and then enable the constraints. If a unique index is used for enforcing a
primary key or unique constraint, this index needs to be dropped when the constraint is
disabled. Performance can be enhanced by using a nonunique index for enforcement of
primary key or unique constraints in such situations: either create the key as deferrable or
create the index before defining or enabling the key.
. If a table contains a self-referencing foreign key, use one of the following methods to load
data:
– Define or enable the foreign key after the initial load
– Define the constraint as a deferrable constraint
The second method is useful if data loads are performed frequently.
11.Using the EXCEPTIONS Table
.Create exception table use:$ORACLE_HOME/rdbms/admin/utlexcpt.sql
.Add constraint with exceptions into clause
eg:
1.Enable archiving Mode
(1)Shutdown the database.
(2)Start the database in Mount state.
(3)Set the database in Archivelog mode by using the ALTER DATABASE command.
(4)Open the database.
(5)Take a full backup of the database.
More…
Popularity: 3% [?]
1.Categories of Failures
.Statement failure
.User process failure
.user error
.Instance failure
.Media failure
.Network failure
More…
Popularity: 14% [?]
1.Security Domain
Authentication Mechanism
. Data Dictionary
. Operating system
. Network
Syntax
Use the following command to create a new user:
More…
Popularity: 13% [?]
1.Two Types of Oracle user privileges
More…
Popularity: 13% [?]
Maintaining Data Integrity
1.Methods to Guarantee Data Integrity
.Application code
.Database triggers
.Declarative integrity constraints
2.Types of Constraints
.NOT NULL
.UNIQUE
.PRIMARY KEY
.FOREIGN KEY
.CHECK
3.Constraint States
An integrity constraint can be enable(data is checked as it is entered or updated) or disabled(data that dose not confirm to the constraint’s rule is prevented from being entered).
An integrity constraint can be in one of the following states:
.DISABLE NOVALIDATE:Old data and new data will not be checked.And the index will be dropped.
.DISABLE VALIDATE:Any modification of the constrained columns is not allowed.In addition,the index on the constraint is dropped and the constraint is disabled.
.ENABLE NOVALIDATE:new data(will be inserted into table) check, old data(original data in the table) not check.If it’s on primary key ,novalid will still check the old data.
Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.
.ENABLE VALIDATE:Old data and new data will be checked.When a constraint changes to enable validate from disabled state,the table is loced and all data in the table
is checked for soformity.This may cause DML operations such as a data load to wait,so it is advisable to move first from a disabled state
to enable novalidate,and then to enable validate.
Note:Unique or primary key moves from the DISABLE state to ENABLE state and there’s no existing index,a unique index is created automatically.
Similarly,when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index,the unique index is dropped.
Moving a single constraint from ENABLE NOVALIDATE state to ENABLE VALIDATE state dose not block reads,writes or other DDL statements.
4.Constraint Checking
Constraint check road
DML statement->Check nondeferred constraints->COMMIT->Check deefrred constraints
Nodeferred (Immediate) Constraints
Enforced at the end of every DML statement.A constraint that is defined as nondeferrable cannot be modified to be enforced at the end of transaction.
Deferred Constraints
Deferred constraints are constraints that are checked only when a transaction is commited.
A constraint that is defined as deferred can be specified as one of the following
.Initially immediate specifies that by default it should function as an immediate constraint unless explicitly set otherwise.
.Initially deferred specifies that by default the constraint should only be enforced at the end of the transaction
5.Changing the Enforcement of Constraints
.SET CONSTRAINTS statement
The SET CONSTRAINT mode lasts for duration of the transaction or until another SET CONSTRAINTS statement resets the mode.
The SET CONSTRAINT statement is disllowed inside triggers.
.ALTER SESSION statement
ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE|DEFERRED|DEFAULT}The ALTER SESSION statement imply setting ALL deferrable constraints statmemt applies to a current session only.
6.Primary and Unique Key Enforcement
Primary and unique keys are enforced using indexes.
7.Foreign Key Considerations
.The foreign key must be dropped before dropping the parent table.
.The parent table cannot be truncated whithout dropping or disabling the foreign key.
.The foreign must be dropped before tablespace containing the parent is dropped.
DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTSIf there is no index on the foreign key on the child table, the Oracle server
locks the child table and prevents changes to ensure referential integrity. If there is an index on
the table, the referential integrity is maintained by locking the index entries and avoiding more
restrictive locks on the child table. If both tables need to be updated concurrently from different
transactions, create an index on the foreign key columns.
When data is inserted into or the foreign key column is updated in the child table, the Oracle
server checks the index on the parent table that is used for enforcing the referenced key.
Therefore, the operation succeeds only if the tablespace containing the index is online. Note that
the tablespace containing the parent table does not need to be online to perform DML operations
on the child table.
Oracle9i no longer requires a share lock on unindexed foreign keys when doing an update or
delete on the primary key. It still obtains the table-level share lock, but then releases it
immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained
and released once per row.
8.Defining Constraings While Creating a Table
Syntax
Defining a constraint from the type NOT NULL after creating a table is only possible with:
9.Defining Constraints After Creating a Table:Example
10.Guidelines for Defining Constraints
. Place indexes used for enforcing primary key and unique constraints in a tablespace different from that of the table.
. If data is frequently loaded in bulk into a table, it is preferable to disable the constraints,
perform the load, and then enable the constraints. If a unique index is used for enforcing a
primary key or unique constraint, this index needs to be dropped when the constraint is
disabled. Performance can be enhanced by using a nonunique index for enforcement of
primary key or unique constraints in such situations: either create the key as deferrable or
create the index before defining or enabling the key.
. If a table contains a self-referencing foreign key, use one of the following methods to load
data:
– Define or enable the foreign key after the initial load
– Define the constraint as a deferrable constraint
The second method is useful if data loads are performed frequently.
11.Using the EXCEPTIONS Table
.Create exception table use:$ORACLE_HOME/rdbms/admin/utlexcpt.sql
.Add constraint with exceptions into clause
eg:
You also can use the following statement to except the duplicated rows after data load.
12.Obtaining Constraint information
.DBA_CONSTRAINTS
.DBA_CONS_COLUMNS
Popularity: 31% [?]