Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 7: More Database Objects

 

Tutorial 7: More Database Objects

Page 1 of 2
This week we will be learing about Sequences, Synonyms, Indexes, Views, Data Dictionary View,
Controlling Database Objects, GRANT, REVOKE Roles and Privileges.


COMMIT, ROLLBACK and SAVEPOINT


SQL*PLUS has a facility to automatically commit all the work, without explicitly issuing the commit command i.e. SET AUTOCOMMIT ON -- Enables autocommit feature.


SET AUTOCOMMIT OFF -- Is the default and disables the automatic committing


Note: Certain actions automatically force a commit. DDL Commands CREATE, DROP, ALTER and DCL commands EXIT, GRANT, REVOKE, CONNECT, DISCONNECT, AUDIT and NOAUDIT are example for this. (i.e. the work done by these commands can not be taken back by ROLLBACK command).


SAVEPOINT: This statement is used to mark a point in the transaction to which you can later ROLLBACK. (i.e. Undo the changes made to the database till that point)


Following screen shots explain the usage of SAVEPOINT and ROLLBACK commands


 



RENAME COMMAND: This Command is used to change the name of a table.


SQL > RENAME STUD TO STUDENT;


LOCK TABLE : The LOCK TABLE command is used to prevent concurrent processes from changing a table or from using it.


The two Locking modes are
1)IN SHARE MODE: In which concurrent processes are allowed to perform only read-only operations.
2)IN EXCLUSIVE MODE:Prevents concurrent processes from performing any operation the table.


Only the Owner of the table, DBA, or a user having ALTER, DELETE, INSERT, SELECT, UPDATE can lock a table. A table can have multiple SHARE LOCKS but only one EXCLUSIVE LOCK


Example: For obtaining EXCLUSIVE LOCK we write.


SQL> LOCK TABLE STUD in EXCLUSIVE MODE;


SQL> LOCK TABLE STUD in EXCLUSIVE MODE NOWAIT;


If we try to obtain a lock using the first statement then Oracle waits if the table is not available for locking. In second statement it returns immediately.
SQL> LOCK TABLE STUD in SHARE MODE; --- to obtain shared mode lock


MORE DATABASE OBJECTS: A schema is a collection of components and database objects under the control of a given database user. Database Objects in Oracle are SEQUENCE, SYNONYM, and INDEX etc.


SEQUENCE: A sequence is an object in oracle, which is used to generate a series of numbers. It can generate unique sequential numbers for using in a primary key column of a table.




CURRVAL and NEXTVAL are called pseudo columns. CURRVAL returns the current value of a sequence. NEXTVAL increments the sequence and then returns the next value. A sequence can be modified using ALTER statement.



SQL> ALTER SEQUENCE SS


MAXVALUE 99999;


SQL> DROP SEQUENCE SS;


Next Page: Tutorial 7: More Database Objects - Page 2


Read Next: Tutorial 8: Building PL/SQL Blocks



 

 

Comments


Khaled said:

  I like your website
June 10, 2006, 5:50 pm

Khaled said:

  But its a little complicated.
June 10, 2006, 5:55 pm

pallavikanala said:

  Excellent work. Keep it up
June 16, 2006, 9:15 pm

Imtiaz Ali said:

  Easy to understand
May 11, 2007, 9:46 pm

rubash said:

  This is the way an tutorial is supposed interspersed with right examples to get a better clarity on the subject matter.

Good work team.
May 18, 2007, 8:02 am

sumanvollala said:

  Thanks for the people who gave all these screen shots,hope we will expect more from you
June 4, 2007, 4:41 pm

Hareesh said:

  Excellent site.Thanks a lot
October 12, 2007, 4:23 am

phatan said:

  Excellent examples. Thanks to Site provoders and God
December 27, 2007, 8:14 am

Jeni said:

  It helps me a lot in excelling it with basic concepts and brilliant examples.
April 1, 2008, 4:31 am

SP.Murugesa Pandian said:

  This is the only way of teaching the oracle to fresher.I hope i could learn more from this site.
June 18, 2008, 7:59 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape