Data Control Language
These commands are used to grant the privileges or take back the granted privileges.
Grant Used to grant privileges.
Revoke Used to take back Privileges.
PRIVILEGES
1) System level Privileges – they allow a user to perform certain actions within the DB.
· DBA will grant these privileges to user.
Example – Create session, Create table, Create views, etc.
2) Object level privileges – They allow a user to perform certain action on DB objects.
* Granting a permission by one user of a DB to another user of a DB on a specify objects.
Select, update, delete, insert.
ROLE
A ROLE is a collection of Permissions (Privileges).
§ Oracle Default ROLES are DBA, CONNECT, RESOURCE.
§ Rather than assigning privileges one at a time directly to a USER, we can create a ROLE and assign privileges to that ROLE, then grant that ROLE to multiple user & ROLE.
§ When we add or delete privileges from all users and ROLE assign that ROLE Automatically RECEIVE or LOSE those Privileges.
§ We can assign multiple ROLES to a single user or one ROLE to another ROLE.
INDEX
Æ Default index is B-Tree index.
Æ Index is a db objects, provide a fast access path to column that are indexed.
Æ It contains an entry for each value that appears in the Index columns.
Æ Indexes are stored separately from actual data.
Æ Index are independent of the table, it indexes both physically and logically.
Æ Index do not have activated & deactivated.
Æ We can use up to 16 COLUMNS as index.
Æ Index are mostly useful on large tables and on column that are frequently appear in WHERE clause.
Æ Index should not be done on a table with less than 100 rows.
Æ It will BETTER and FAST if data is loaded first and then index is created.
Æ If the table is Index organized, then a secondary index can created.
Æ If the Table is TEMPORARY table, then Index is also TEMPORARY.
Æ We can create index on columns containing NULL or DUPLICATE if UNIQUE keyword is not used.
Æ We can create index on column contains a large number of NULL.
Æ We can’t specify both UNIQUE & BITMAP indexes on one table.
Æ Index can be created or dropped at any time and have no effect on the base table or other index.
Æ When table dropped, index will also drop.
Æ More than one indexes are allow in one table.
Æ Each DML operations that is committed on a table with index, means that index must be updated.
Æ PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a non-clustered index is not specified when you create the PRIMARY KEY constraint.
Type of index
Simple index
A simple index on column.
>create index sid on emp(empno);
Composite index
An index created on more than one column of table.
>create index cid on emp(empno,ename);
Unique index
An index with unique constraint it will not allow duplicate values.
>create unique index uid on dept(dname)
Bit map index
Bitmap indexes are normally used to index low cardinality columns in a warehouse environment.(statics)
>create bit map index bmid on emp(deptno);
B-tree index
B tree index are normally used OLTP Environment.(Dynamic)
Function based index
This function is created on the arithmetic expression on columns of table.
>Create index fid on emp(sal+comm.);
Reverse index
This is created on columns containing data in sequential order.
>create index rid on emp(empno) reverse;
Descending index
This is created on columns which are displayed in sorted order.
>create index dixd on emp(ename asc, sal desc);
CLUSTER INDEX
Clustered index physically rearrange the data that users inserts in your tables.
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
NON-CLUSTER INDEX
The data is present in arbitrary order, but the logical ordering is specified by the index.
Non cluster index is a special type of index in which the logical order of the index does not match the physical sorted order of the row on the disk
If primary key is a cluster index it must be foreign key is non cluster index.
CLUSTER
¯ Cluster is a method of storing tables that are related and often access together.
¯ A cluster is a group of rows from separate table stored in a small disk block
Transaction control language (TCL)
Transaction
v A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued
v DDL and DCL command is auto commit.
v The current user can review the result of the data manipulation operation by querying the table.
v Other user can’t view the result of the DML operations made by the current user
v When the environment setting auto commit is ON,
v >set autocommit ON;
Commit is used to save the transaction to disc. i.e. to make change permanent to DB.
Roll back is used to undo the transactions.
Save point is used to identify a portion of the transaction, for rollback purpose.
A SAVEPOINT is a marker within a transaction that allows for a partial rollback.
SEQUENCE
o Sequence is a DB object that can generate unique sequential value (serial Number).
o Its automatic creates sequence.
o Sequence value is often used for PRIMARY KEY’s & UNIQUE KEY’s.
o When sequence is created, we can define its initial value and the increment between its value..
o The maximum value generated by the sequence is 38 digits.
o To generate starting value sequence NEXTVAL should be used.
· >create sequence (seq_name)
· [Increment by n
· Start with n
· Max value n/NOMAXVALUE
· Min value n/NOMINVALUE
· Cycle/Nocycle
· Cache/Nocache
· Order/No order];
Increment by
· Specify the interval between the sequence numbers.
· Value can be positive or negative but can’t be zero.
· If the value is POSITIVE it is INCREMENT SEQUENCE.
· If the value is NEGATIVE it is DECREMENT SEQUENCE.
· Default increment is 1.
Start with n
· Specify the starting value of sequence.
· Default value is 1.
Min value
· Specify the min value of sequence.
No min value
· Specify a minimum value of 1 for ascending sequence.
· -1026 for descending sequence.
Max value
· Specify the max value of sequence.
No max value
· Specify the maximum value of 1027 for ascending sequence.
· -1 for descending sequence.
Cycle
· Specify the sequence will continue to generate value after reaching either maximum or minimum value.
No cycle
· Specify sequence can’t generate value after reaching max or min value.
· Default is NO CYCLE.
Cache
· Cache is a buffer.
· It specifies the No. of integer to keep in memory.
· Default cache is 20.
· Minimum cache is 2.
Order
· This guarantees that sequence Numbers are generated in the order of request.
No order
£ This does not guarantee that sequence Numbers are generated in the order of request.
© Alter is available in sequence.
© The START WITH OPTION can’t be changed using ALTER SEQUENCE.
© To change START WITH OPTION, drop the SEQUENCE and then recreate the sequence.
ž >ALTER sequence (seq_name)
ž [Increment by n
ž Max value n
ž Min value n
ž Cycle
ž Cache];
PSEDUO COLUMN
Ø Pseudo column behave like a table’s columns, but it is not stored in a table.
Ø Upon pseudo column only SELECT statements can be implemented.
Ø DML can’t be implemented.
Ø The available Pseudo columns are..
A. Currval
B. Nextval
C. Level
D. Rowid
E. Rownum
F. Sysdate
CURRVAL- Return the current value of sequence.
NEXTVAL- Increment the sequence value by declared.
VIEW
¯ View is a logical table, based on one or more table or views.
¯ View contains NO data by itself.
¯ The table on which a view is based are called as BASE TABLE.
¯ View require no memory to store data but some memory required for storing definition of view.
¯ View can replaced if already exit.
¯ We can create view without existing the table forcibly.
¯ Complex select statement can be stored in DB and retrieve the data writing simple select statements using view.
VIEW’S TYPE
n Simple view
n Complex view
n Materialized view
SIMPLE VIEW
¯ A view based on single table.
¯ Simple view will not have any expression or group function.
¯ All DML allow in simple views.
¯ If table drop then view will not drop but view become invalid. View become valid when table again created with same as dropped table column name.
COMPLEX VIEW
® Complex view is created on multiple table and group function.
® Complex view is created only with READ ONLY OPTIONS.
® DML are not allowed.
® Through view we can insert data into child table not in parent table.
® Select is allow.
MATERIALIZED VIEW
§ Simple and complex view will not have any data so they will not demand any space.
§ Materialized view contains both definition and data, it demand space.
§ DML not allowed.
§ Select is allowed.
§ Materialized view refreshed based on data in table.
§ To create materialized view with refreshment option, table should have atleast one primary key constraint column.
§ Order by clause is allowed in view onwards oracle 8i.
§ Alias must be defined for all columns of the view those are derived from arithmetic expression, function or pseudo column.
§ >create materialized view dept_mv
§ Refresh complete with rowid
§ Start with sysdate
§ Next sysdate+1/(24*60*60)
§ As
§ Select * from dept;
§ > create materialized view dept_mv
§ Refresh fast
§ Start with sysdate
§ Next sysdate+1/(24*60*60)
§ As
§ Select * from dept;
Difference between ROWID and ROWNUM.
Row id
|
RowNum
|
1.Physical address of the rows.
2.Rowid is permanent
3.Rowid is 16-bit hexadecimal
4.Rowid gives address of rows or records
5. Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row.
6. ROWID is the fastest means of accessing data.
7. They are unique identifiers for the any row in a table.
|
1. Rownum is the sequential number, allocated to each returned row during query execution.
2. Rownum is temporary.
3.Rownum is numeric
4.Rownum gives count of records
5. Rownum is an dynamic value automatically
retrieved along with select statement output.
6. It represents the sequential order in which Oracle has retrieved the row.
|
Note::
v We can’t use rowid as a PK.
v We can change the rowid.
v ROWID will change every time the record is physically moved.
v Rownum indicates the order in which oracle engine selects the row from a table .
v Rowid can’t change manually through DML operation.
SYNONYM
¯ A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
¯ We can avoid the entry of SCHEMA.
¯ DML operation performed on synonym will affect the table.
¯ The main functionality of synonym is to access the database objects between different schemas without using the schema names.
Types of Synonyms
§ Private synonyms- it is created by user.
¯ Public synonyms-
F Which can be accessed by all USERS in the DB?
F Synonym created by user with CREATE PUBLIC SYNONYM privileges.
>create synonym (syn_name) for scott.
Difference between VIEW and SYNONYM.
view
|
synonym
|
1. It doesn’t contain data itself.
2.view occupies no space for data
3.view has only query
|
1. It contain data itself.
2.synonym occupy space for data
3.synonym can be used as an alias name for the table name
|
Constraint
Constraint is a rule or restriction.
Data integrity constraint
It is a constraint which is enforce by oracle on columns of a table to maintain the accuracy & integrity of a data.
Unique
£ It will not allow duplicate values but allow null values.
£ Maximum of 32 columns are allow under one unique key, called composite or concatinate key.
£ Oracle create an index implicitly on unique key column.
Not Null
¢ It will not allow null but allow the duplicate values.
¢ It is independent of data type.
¢ Not null constraint should be defined at column level.
¢ The default constraint is NULL
Primary key
¤ It is a combination of unique and not null constraint.
¤ It will not allow null and duplicates.
¤ Only one primary key allow per table.
¤ Maximum 32 columns allow for one primary key or composite key.
¤ We can’t create unique key and PK on same column.
¤ A master table can have any number of child tables.
¤ One PK has any number of foreign key.
¤ It is not possible to disable PK when it has FK referring it.
¤ It is also not possible to disable FK first and then PK.
Check
n Used to enforce the condition rule.
n Check, the values in specified list or not.
n Check, the value in specify range or not.
n Multiple Check allow in one table.
Foreign key
® It is used for establish the parent-child or master-detailed relationship between tables.
® We can define same column as foreign key & Primary/Unique key.
® Foreign key allows duplicate and Null.
References
¶ It is establish relation between the columns of different table.
¶ Foreign key and ON DELETE CASCADE.
Self referenced key
F A Foreign key which is referring the Primary key of the same table is called SELF REFRENCED KEY.
