my notes...

1. Data Query
2. Logical Operators (AND, OR, NOT etc) & Single row Functions
3. DML & Locks
4. DDL & Constraints
5. Views & Top-N Analysis
6. Sequences
7. Database Link (note : scoll to bottom of privilege notes)
   

1. Creating Database manually / Restoring backed-up database
   
2. Password authentication setup : Operating System authentication / password file authentication
   
3. Control Files
4. Redo Log Files
5. Segments & Data Blocks - INITRANS-MAXTRANS / PCTFREE-PCTUSED-FREELIST
6. Tablespaces
7. Managing User (Create-Alter-Drop)
   
8. Profiles
9. Privileges (Grant-Revoke) & Database Link
   
10. Roles(Create-Alter-Set-Drop)
11. Auditing(Audit-Noaudit)
12. Password Management script utlpwdmg.sql
13. Views : Storage relationships (Segments & Extents)
    
14. Views : Users-Quota-Profile-Resource cost-Resource Limit-Privileges

posted by sk @ 1:25 AM 0 comments

• Typically used to generate Primary Keys

• Automatically generates unique numbers
• Replaces application code
• Sharable
• Speeds up access speed when cached in memory
• View sequence values in USER_SEQUENCES data dictionary.

Note : A sequence should be named after its intended use for clarity. However it can be used anywhere irrespective of its name.

Syntax:

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

DROP SEQUENCE sequence_name;

INCREMENT BY : default sequence increment is by 1.

START WITH : default sequence starts with 1.

NOMAXVALUE : 10^27 for ascending sequence, -1 for descending sequence.

NOMINVALUE : 1 for ascending sequence, 10^26 for descending sequence.

CACHE : how many values Oracle preallocates & keeps in memory. default is 20.

USER_SEQUENCES data dictionary:

SELECT sequence_name, min_value, max_value, increment_by, last_number

from USER_SEQUENCES;

Note : The last_number displays the next available sequence number unless there are cached values.

Pseudocolumns NEXTVAL & CURRVAL:

• sequence.NEXTVAL : returns the next available sequence value.

• sequence.CURRVAL : returns the current sequence value.
  

• Note : NEXTVAL must be issued before CURRVAL contains any value. If cache is disabled, than the next available sequence can be seen in USER_SEQUENCES without using NEXTVAL first.

Rules for using NEXTVAL & CURRVAL

Can be used in:

• VALUES clause of an INSERT statement.

• SELECT list of a Sub-query in an INSERT statement

• SET clause of an UPDATE statement

• SELECT list of SELECT statement not part of a Subquery

Can't be used in:

• A SELECT list of a View

• A SELECT statement with DISTINCT

• A SELECT statement with GROUPBY, HAVING, or ORDER BY clause

• A subquery in a SELECT, DELETE, or UPDATE statement

• DEFAULT expression in a CREATE TABLE or ALTER TABLE statement

Note : CACHE is populated the 1^st time the sequence is referred. NEXTVAL requests pull the values from the cache until the last value is used up, after which the next NEXTVAL request pulls another cache of sequences into the memory.

Gaps in Sequences:

• A rollback occurs. (Sequence generation is independent of commit & rollback.)

• System crash (Cached values in the memory are lost.)

• Sequence used in another table (same sequence used in multiple tables will lead to gaps in all those tables)

Altering Sequences:

• Only future sequence numbers are affected

• START WITH can't be altered. DROP & CREATE to sequence again to start it at a different number.

• Some Validation are performed (such as setting MAXVALUE lower than current sequence

• number not permitted).

• One must have the privileges to change the sequence.

Usage Example:

CREATE SEQUENCE departments_seq2

START WITH 20

INCREMENT BY 20

MAXVALUE 999

NOCACHE

NOCYCLE;

ALTER SEQUENCE departments_seq2

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

INSERT INTO departments (department_id, departments_name, location_id)

VALUES (departments_seq.NEXTVAL, 'Navigation', 9152);

SELECT departments_seq.CURRVAL FROM DUAL;

posted by sk @ 11:24 PM 0 comments

View: A view is a logical table based on a table or another view. It is like a window through which data from tables can be viewed or changed. It is stored as a SELECT statement in the data dictionary. It's definition can be viewed in the USER-VIEWS data dictionary table.

Advantages of Views & its purpose :

• To restrict data access.

• Makes complex queries easy.

• Provide data independence. One view can be used to obtain data from several tables .

• Obtain different views of the same data.

• Can be removed without effecting underlying data.

Views options :

• Simple view based on one table

• complex view based on more than 1 table or containing groups of functions

• can replace other views with the same name

• can contain a check constraint

• can be read only

SYNTAX for creating a VIEW:

CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW viewname [(column alias[, column alias]...)]

AS subquery

[WITH CHECK OPTION] [CONSTRAINT constraint_name]

[WITH READ ONLY] [CONSTRAINT constraint_name];

Controlling column names by including aliases after the Create statement & prior to the subquery:

CREATE OR REPLACE VIEW empvw80 (id_number, name, sal, department_id)

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Controlling column names by including aliases within the subquery:

CREATE OR REPLACE VIEW empvw80

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Note : Using CREATE OR REPLACE a view can be created even when it already exists, thus replacing an existing one. This means that the view can be altered without dropping, re-creating and re-granting object privileges.

Creating complex views:

CREATE OR REPLACE VIEW dept_sum_vu (name, minimum, maximum,average)

AS

SELECT d.department_name, MIN(e.salary),Max(e.salary), AVG(e.salary)

FROM employees e, departments d

WHERE e.department_id=d.department_id

GROUP BY d.department_name;

Query a view just like any table :

SELECT * FROM empvw80;

Steps undertaken to executing a view based query:

• Retrieve view definition from USER_VIEWS

• check access privileges

• convert the view query into an equivalent operation on underlying base table(s).

DML operations on a VIEW:

Can't remove or modify or add a row if a view contains the following:

• GROUP funcions

• GROUP BY clause

• DISTINCT keyword

• Pseudocolumn ROWNUM keyword

Can't modify or add a row if

• columns defined by an expression (eg. Salary * 2)

Can't add a row if

• NOT NULL columns in the base table are not selected by the view

Data can be added into a view unless it contains any of the above listed items or there are NOT NULL columns without default values in the base table that are not selected by the view. All required values must be present in the view.

WITH CHECK OPTION :

• to ensure that DML operations performed on the view remain within the domain of the view.

Can be used for the following:

1. referential integrity checks

2. enforce constraints at database level

3. can also be used to protect data integrity but its use is very limited.

WITH READ ONLY : Denying DML operations.

• Attempt to delete using view throws Oracle Server Error: ORA-01752 : cannot delete from view without exactly one key-preserved table.

• Attempt to insert or update row using view throws Oracle Server Error :ORA-01733 :virtual column not allowed here.

SYNTAX for dropping a VIEW: DROP VIEW view-name;

Inline Views :

• An inline view is a sub-query with an alias.

• A named query in the FROM clause is an example of inline view.

• An inline view is not a schema object.

SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) b
WHERE a.department_id=b.department_id
AND a.salary

SYNTAX for TOP-N Analysis: Used to display only the n top most or the n bottom-most records from a table based on a condition. These queries use a consistent nested query structure with following elements :

• a subquery or inline view to generate the sorted list of data. Use of ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values a DESC parameter is needed.

• An outer query to limit the number of rows in the final result set. It includes a ROWNUM pseudocolumn which assigns a sequential value starting from 1 to each of the rows returned from the subquery. A WHERE clause, which specifies the n rows to be returned. The outer WHERE must use a < or <= operator.

SELECT [column list...], ROWNUM

FROM (SELECT [column list...]

FROM tablename

ORDER BY Top-N-column)

WHERE ROWNUM<=N;

eg:

SELECT ROWNUM as RANK, last_name,salary
FROM (SELECT last_name,salary from employees ORDER BY salary DESC)
WHERE ROWNUM<=3;

SET LONG 600;

SELECT * FROM user_views;

posted by sk @ 3:57 AM 0 comments

NAME OF TABLE/COLUMS : Must begin with a letter.
SIZE : 1-30 characters
Allowed characters in a name : A-Z,a-z,0-9,_,$,#

Note : Name must not be duplicate another object owned by the owner. And it must not be an Oracle reserved word. Names are not case sensetive.

DATA TYPES

VARCHAR2(max_size) : Variable length Character data. (Min size:1; Max size:4000)

CHAR([size]) : Fixed length Character data, Oracle pads the values with blanks upto the declared length of the column. (default/Min size:1; Max size:2000)

• eg . : create table test1 (column1 char(10), column2 varchar2(10));
• insert into test1 values ('Ram', 'Ram');
• select vsize(column1), vsize(column2) from test1 ; -----> length(column1) -> 10, length(column2)->3
  

NUMBER([n,m]) : n: total number of digits(1 to 38, default 38), m: number of decimal points(upto 38).

• eg: xyx(15,2) // ---------> 1234567890123.45 ;
  
• inserting more than 13 digits to the left of decimal point will result in an error.
  
• inseting more than 2 decimal points will result in rounding off.
  

LONG : Variable length Character data, upto 2 gigabyte.

• Not copied when table created using a sub-query.

• Can't be used in a GROUP BY or ORDER BY clause.
• No constraints can be defines. Use CLOB instead.
• Data in LONG & LONG RAW stored inline with the rest of the table data. So only one column can be declared as LONG type in a table

• Note : For newer data types BLOB,CLOB, NCLOB data is stored in following ways:

• if less than 4k, the data can be stored inline with the rest of the data in the table.
  
• if more than 4K, the data is stored outside the table & a pointer to it is stored inline with the rest of the table.
  
• This allows Oracle to store more than one BLOB,CLOB, NCLOB column per table unlike LONG or LONG RAW.
  

CLOB : Character data upto 4 gigabyte.
RAW(MAX_size) : Raw binary data. Max size is 2000.
LONG RAW : Raw binary data of variable length upto 2 gigabyte.
BLOB : Binary data upto 4 gigabyte.
BFILE : Binary data stored in external file; upto 4 gigabyte.
ROWID : A 64 base number system representing the unique address of a row in its table. These could be physical ROWIDs or logical ROWIDs.

Single bytes or multibyte UNICODE character based data types :

NVARCHAR2(MAX_size), NCHAR ([size]), NCLOB : same as above but stores UNICODE data.

DDL COMMANDS

Summary of available DDL commands :

CREATE TABLE tablename
ALTER TABLE tablename

• ADD (columns) .....

• ADD CONSTRAINT .....

• MODIFY (columns) .....

• MODIFY CONSTRAINT...

• DROP (columns) .....

• DROP COLUMN column_name .....
• DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

• DISABLE CONSTRAINT constraint_name [CASCADE];

• ENABLE CONSTRAINT constraint_name;

• SET UNUSED

• DROP UNUSED COLUMNS

DROP TABLE tablename
TRUNCATE tablename
RENAME ... TO ...
COMMENT ON TABLE tablename IS '....' ;

Explainations :

CREATE TABLE [schema.]table
(column_name datatype [DEFAULT expression] [[CONSTRAINT constraint_name] constraint type]
[,column_name ....]
[,[CONSTRAINT constraint_name] constraint type (column,column...)]
[,CONSTRAINT ....] );

DEFAULT can be literal values, expressions, SQL functions. These can't be another column name or pseudocolumn.

CREATE TABLE tablename [(column [DEFAULT expression] [ [CONSTRAINT constraint_name] constraint type], [...])]
AS
SELECT [*|column list] FROM tablename2;

• Integrity rules are not copied. Only column type definitions are passed onto new table.

• Not Null constraint is passed. (no Default value setting passed?)
• Without column list, the name of the table columns are the same as the subquery.
• If column names are listed, then they can't show column_type. Only DEFAULT value may be configured.
• Use a column alias to change the column name, or where an expression is used.
• Use * to copy full table.
• If table is copied using subquery and constraints are also declared for the new table, any data not meet the constraints results in the failure of DDL and table is not created. For selected creation of table use the condition is the subquery part.

ALTER TABLE table_name
ADD (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

Note : The new columns is initially null for all the rows.

ALTER TABLE table_name
MODIFY (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

• Modify a columns data_type, size, default values. Modification only effects subsequent values.

• Data type change possible only if the column has null values.
• Increase width/precision : decrease possible only if table is empty (or if actual data permits?)
• Change of DEFAULT value effects only subsequent insertions.
• CHAR<-> Varchar2 only if null values or if u don't change width (or if actual data permits?).

ALTER TABLE table_name
DROP (column, column,....) [CASCADE CONSTRAINTS];

ALTER TABLE table_name
DROP COLUMN column_name [CASCADE CONSTRAINTS];

• Dropped column may or may not contain data. The dropped column can't be recovered.

• With DROP COLUMN command only one column can be dropped at a time.

• The table must have one remaining column after the alteration.

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

ALTER TABLE table_name
SET UNUSED (column_name, column_name,.....);

• Once a column is marked UNUSED a user has no access to that column.

• Helps to mark the unused columns, to be dropped during low resource demand period.

• The table must have one remaining column after the alteration.

ALTER TABLE tablename
DROP UNUSED COLUMNS;

• Use drop to clean up marked unused columns when demand on system resources is low. This command returns without error even if the table has no unused columns.

• Can have multiple add/modify/drop constraints in a single ALTER TABLE command except drop column/set unused commands. eg:

alter table my_emp2
drop Primary Key
drop Unique (Lname)
modify (id number(6))
add (id2 number(8,4));

DROP TABLE tablename;

• Removes the defination of an Oracle table. The database loose all data in the table and all the indexes associated with it. This command is irreversable, the table is removed without question. Remember that all DDL statements are committed automatically!

RENAME tablename1 TO tablename2;

• Only the owner can rename the table. Rename a table, sequence or synonym.

TRUNCATE TABLE tablename;

• Only the owner or have DELETE TABLE System privileges to truncate table. If the table is the parent of a referencial entity, it can't be truncated. Such constraints must be disabled prior to TRUNCATE.

• TRUNCATE removes all rows of the table & release the storage space used by them. Oracle uses a characteristic called 'high-water mark' to keep track of the largest size the table has ever grown to. TRUNCATE rests this high-water mark to zero thereby improving performance.

• Rows removed using TRUNCATE can't be rolled back. Alternatively use "DELETE [FROM] tablename" which doesnot release storage space and can be rollbacked.

Note : TRUNCATE is faster than DELETE TABLE for following reasons:

• DDL statement, so generates no rollback information.

• Truncating doesnot fire delete triggers of the table.

COMMENT ON TABLE tablename IS 'this is test table';

COMMENT ON COLUMN tablename.column IS ' testing column comment';

• Drop a comment using empty string (''). Can't be rolled back.

ALTER TABLE tablename
ADD [CONSTRAINT constraint_name] constraint type (column);

ALTER TABLE tablename
MODIFY column [CONSTRAINT constraint_name] NOT NULL;

ALTER TABLE tablename
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;

NOTE : DISABLE/ENABLE can be used in both CREATE TABLE/ALTER TABLE statements. CASCADE option causes any depended constraints also to be dropped or disabled.

DISABLEing a Primary Key or Unique Key constraint removes the unique index.

ENABLING creates the UNIQUE KEY or PRIMARY KEY indexes.If you enable a constraint, then all data in the table must fit the constraint. Enabling a primary key constraint disabled using CASCADE doesnot enable any foreign keys that are dependent upon the primary key.

Steps to take in case existing data violates a disabled Constraint:

• Create the special table called EXCEPTIONS ( created by running ORACLE_HOME/rdbms/admin/utlexcpt.sql).

• Describe exceptions;

  ROW_ID ROWID
  OWNER VARCHAR2(30)
  TABLE_NAME VARCHAR2(30)
  CONSTRAINT VARCHAR2(30)

• alter table enable constraint PK_.... -> can't enable, Primary Key Violated.

• select e.row_id, a.column from exceptions e, tablename a where e.row_id=a.row_id;

• either modify the identified offending records or drop them and then re-enable constraints!!

CONSTRAINTS

Syntax :

Column Constraint Level : column [CONSTRAINT constraint_name] REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Table Constraint Level :
column,... [CONSTRAINT constraint_name] FOREIGN KEY (column,...) REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Note : check constraints declared at table level can't reference other columns. All constraints can be defined at Table Level except NOT NULL. Composite unique/primary key/Foreign key contraints are created at table level declaration.

NOT NULL : can only be defined at column level. A NOT NULL constraint can only be defined if the table is empty or if the column has values for all the rows.

UNIQUE : Ensures uniqueness of column or column combination. Unless NOT NULL is also defined of the column, unique constraint allows any number of null values as NULL are not considered equal to anything. If the Unique constraint comprises more than one column eg. Unique(col1,c2) that group of column is called a Composite Unique Key. Can't have identical values in the non-null columns of a partially null UNIQUE key constraint.

PRIMARY KEY : Ensures uniqueness of column or column combination. No columns which are part of a composite primary key can contain a NULL. Only one Primary key is allowed per table.

Oracle enforces Unique & Primary key constraints by implicitly creating UNIQUE indexes on these columns/column combinations.

FOREIGN KEY : Referential integrity constraint. Designates a column or column combination as foreign key and establishes a relationship between a primary key or unique key in the same table or different table. A foreign key must match an existing value in the parent table or be NULL. Foreign keys are based on data values and are purely logical pointers.The rows in the parent table can't be deleted if it is referenced in the child table, unless ON DELETE CASCADE or ON DELETE SET NULL is specified. This default behaviour is called the restrict rule which disallows update or deletion of referenced data.

Note: A FOREIGN KEY can't be created on the child table until the parent table is created and the PRIMARY KEY is defined on that parent table.

CHECK constraints : Defines a condition that each row must satisfy.

• Static criteria : CHECK constraints can only compare data in that column to a specific set of constant values or operations on those values.

• A column can have multiple check constraints which refer to the column in its defination. There is no limit on the number of these check constraints.

The CHECK condition may be the same as SQL conditions except following:

• references to CURRVAL, NEXTVAL, LEVEL, ROWNUM & ROWID pseudocolumns

• call to SYSDATE, UID, USER & USERENV

• reference to any other column or row in this or any other table

CASCADE CONSTRAINTS : Used along with DROP COLUMN clause. It drops all referencial integrity constraints that refer to the primary & unique keys defined on the dropped columns. It also drops all multicolumn constraints defined on the dropped columns.

eg.
CREATE TABLE test1 (column1 primary key,columns...,
CONSTRAINT constraint_name1 FOREIGN KEY (column1) references test1,
CONSTRAINT constraint_name2 CHECK (column1>0, column3>0);

Alter Table drop column1 ; -> error as column1 is a parent key
ALTER TABLE DROP column3 ; -> error because column3 is referenced by multi-column constraint_name2

Solution :
ALTER TABLE DROP (column1) CASCADE CONSTRAINTS; -> drops constraints primary key, foreign key & constraint_name2

ALTER TABLE DROP (column1,column2,column3); -> this is valid as all the columns referenced by the constraints defined on the dropped columns are also dropped.

DateTime Data Types

TIMESTAMP [(fractional_second_precision)][WITH TIME ZONE | WITH LOCAL TIME ZONE]

• fractional_second precision : range : 0 to 9; default : 6

WITH TIME ZONE : timestamp + time zone displacement between local time & UTC(GMT). Can't be used for index/primary keys/unique keys.

WITH LOCAL TIME ZONE : Data stored in database is normalised to the database time zone.
Oracles returns the data in the user's local session time zone.

INTERVAL YEAR [(year precisiom)] TO MONTH

• To get the interval in years & months

• Default year precision is 2

• Default month precision is 3

example:
INTERVAL '123-2' YEAR(3) TO MONTH : +123-02 (month value can be 0-11)
INTERVAL '123' YEAR(3) : +123-00
INTERVAL '300' MONTH(3) : +025-00

INVERVAL DAY[day precision] TO SECOND[fractional_second_precision]

• To get the interval to fraction of seconds

• Day Precision : 0-9, Default 2.

example:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) : +04 05:12:10.222
INTERVAL '7' DAY : +07 00:00:00
INTERVAL '180' DAY(3) : +180 00:00:00
INTERVAL '4 5:12' DAY TO MINUTE : +04 05:12:00
INTERVAL '4 5' DAY TO HOUR : +04 05:00:00
INTERVAL '5:12:10.1234567' HOUR TO SECOND(7) : +00 05:12:10.1234567

Data Dictionary Views :
All data dictionary tables are owned by SYS user.

Data dictionary views :

Prefixes:

• USER_ : Information about objects owned by user

• ALL_ : Information about all objects accessible to the user
• DBA_ : Restricted views accessed only by a DBA role user
• V$ : dynamic views of database server performance, memory, locking

Default data dictionary views available in Oracle 9i :

• USER_TABLES or TABS : (tables)

• USER_OBJECTS : (tables,index,sequences)
• USER_CATALOG or CAT
• TAB

• USER_UNUSED_COL_TABS : shows table_name with the count of unused columns.

• ALL_COL_COMMENTS

• USER_COL_COMMENTS
• ALL_TAB_COMMENTS
• USER_TAB_COMMENTS

• USER_CONSTRAINTS

• USER_CONS_COLUMNS

Note : C: check P: Primary key, R : Referential Integrity, U : Unique key. Not NULL is a check constraint

A SQL statement to get a useful table constraints view :

SELECT ucc.table_name, ucc.column_name, uc.constraint_type, ucc.constraint_name,
uc.r_constraint_name,uc.delete_rule,uc.status,uc.search_condition, uc.index_name
FROM user_constraints uc
JOIN user_cons_columns ucc
ON (ucc.constraint_name=uc.constraint_name)
ORDER BY ucc.table_name;

posted by sk @ 3:28 AM 0 comments

• INSERT INTO tablename
• UPDATE tablename
• DELETE [FROM] tablename
• MERGE tablename
• COMMIT
• SAVEPOINT savepointname
  
• ROLLBACK [TO savepointname]
  

• Ensure data integrity
• Group logically related operations.
• Preview data change before committing (other users still see the old unchanged data)

• DML basically effects the database buffers
• The current user can review the results by querying the database
• Other users can't view the results of DML statements done by current user. The Oracle Server institutes read consistency to insure that each user sees data as it exits at last COMMIT.
• the effected rows are locked, other users can't change the data in these locked rows.

• Data change made permanent and all users can view the results.
• Locks are released & all savepoints erased.

• Oracle does a statement level rollback if that statement fails during execution (by implementing an implicit savepoint for each DML statement).

• If a single DML statement fails during execution of transaction, its effects is undone by a statement level rollback, but the changes made by the previous DML statements is the transaction are not discarded. They can be committed or rollbacked explicitly by the user.

• The purpose of read consistency is to ensure that the user sees the data at the last commit. before a DML operation started.

• Implementation : Oracle keeps a partial copy of database in a Undo Segment. When an INSERT/UPDATE/DELETE is carried out, the old data is copied in Undo Segments and then the changes are made in the database. All users except the one who made the changes, see the data as it existed before the change - they view the snapshot of data stored in rollback segment.

• Before change is committed, only the user who made the data modifications can view the database with the alterations, everyone else sees the snapshot in the undo segment.

• COMMIT: The changes made to database become visible to all users. The space occupied by old data in the undo segment is freed for reuse.

• ROLLBACK : The original, older version of the data in the undo segment is written back to the table. Now all users see the database as it existed before the transaction began.

• Implicit locking occurs for all statements except SELECT.
• A share lock is automatically obtained at the table level during DML operation.
• An exclusive lock is acquired automatically for each row modified by DML statement.
• DDL locks occur when a database object is modified.
• Locks hold until commit or rollback.

posted by sk @ 7:58 PM 0 comments