Tutorials
Oracle 9i
SYNONYM: A synonym is an alternative name given to the table, sequence etc in oracle. There are two types public Synonym and Private Synonym.
1) Public Synonym: A public synonym is accessible to all the users.
Example:
SQL> SELECT * FROM DICT;
DICT is a public Synonym for table DICTIONARY. To create a public synonym we need to login as user SYSTEM (i.e. DBA) and then type the following
SQL> CREATE SYNONYM EMPLOYEE FOR SCOTT.EMP;
In the above statement you have created synonym EMPLOYEE for EMP. Now you need to grant permissions to all the users over this synonym. The synonym for can be used to perform all DML operations (SELECT, UPDATE, INSERT and DELETE) over the table. We cannot ALTER the table using its SYNONYM.
2) Private Synonym: To create a private synonym we write.
SQL> CREATE SYNONYM EMPLOYEE FOR EMP;
SQL> DROP SYNONYM EMPLOYEE; -------- Will remove the Synonym.
INDEX: Indexes will make data access faster. This index tells where a certain row in the table is stored. It is more like an index in the book. When we create an index on a column it is stored separately in the database. A query on any table initially searches for an Index on that table.
SQL> CREATE INDEX EMP_IDX ON EMP(EMPNO);
SQL> DROP INDEX EMP_IDX;
Creating new user account : To create a new user account one need to logon to the database as a DBA as shown above. Then execute syntax similar to the one shown below.

In the above example we have created 2 user accounts by names U1 and U2. Let us say U1 owns a table by name ORDERS which U2 want to use. This is possible only when U1 has given permission to U2. Privileges can be given using GRANT and taken back by using REVOKE command
Below given screen shots explain these commands.

As U2 when we tried to access data from ORDERS of U1 we failed, as there were no sufficient privileges. Later we could access because U1 granted permission to do so.
To take back the privileges we write
SQL> REVOKE SELECT ON ORDERS FROM U2; ---- For doing this You have to login as U1.
SQL> GRANT SELECT, UPDATE ON ORDERS TO U2;
SQL> REVOKE SELECT, UPDATE ON ORDERS FROM U2;
SQL> GRANT ALL ON ORDERS TO U2; --- To give SELECT, UPDATE, INSERT and DELETE privileges in a single command
SQL> REVOKE ALL ON ORDERS FROM U2;
SQL> GRANT SELECT ON ORDERS TO U2 WITH GRANT OPTION;
Above command authorizes U2 to transfer the same privilege to others on behalf of U1.

First Page: Tutorial 7: More Database Objects
| I like your website |
| But its a little complicated. |
| Excellent work. Keep it up |
| Easy to understand |
|
This is the way an tutorial is supposed interspersed with right examples to get a better clarity on the subject matter. Good work team. |
| Thanks for the people who gave all these screen shots,hope we will expect more from you |
| Excellent site.Thanks a lot |
| Excellent examples. Thanks to Site provoders and God |
| It helps me a lot in excelling it with basic concepts and brilliant examples. |
| This is the only way of teaching the oracle to fresher.I hope i could learn more from this site. |