Tutorials Oracle 10g.
.
INSERT INTO Emp_tab (Empno, Ename, Job)
VALUES (Emp_sequence.NEXTVAL, 'SMITH', 'CLERK');
Figure 30. Inserting in a table using a synonym (Emp_tab).
If the synonym named fire_emp refers to a standalone procedure or package procedure, then you could execute it with the command
EXECUTE Fire_emp(7344);
You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the DROP ANY SYNONYM system privilege. To drop a public synonym, you must have the DROP PUBLIC SYNONYM system privilege.
Drop a synonym that is no longer required using DROP SYNONYM statement. To drop a private synonym, omit the PUBLIC keyword. To drop a public synonym, include the PUBLIC keyword.
For example, the following statement drops the private synonym named emp:
DROP SYNONYM emp;
Figure 31. Dropping a synonym.
The following statement drops the public synonym named public_emp:
DROP PUBLIC SYNONYM public_emp;
Figure 32. Dropping a public synonym.
When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain. However, they become invalid (not usable).
The following views display information about views, synonyms, and sequences:
|
View
|
Description
|
DBA_VIEWS ALL_VIEWSUSER_VIEWS |
DBA view describes all views in the database. ALL view is restricted to views accessible to the current user. USER view is restricted to views owned by the current user. |
DBA_SYNONYMS ALL_SYNONYMSUSER_SYNONYMS |
These views describe synonyms.
|
DBA_SEQUENCES ALL_SEQUENCESUSER_SEQUENCES |
These views describe sequences.
|
DBA_UPDATABLE_COLUMNS ALL_UPDATABLE_COLUMNSUSER_UPDATABLE_COLUMNS |
These views describe all columns in join views that are updatable.
|
First Page: Oracle 10g Free Training - Synonyms
|
I have a userID/Password in oracle. This user has a number of privileges in other schema objects also. Now, if I have sufficient privileges, I will be able to view other schema objects also from my schema. In that case, I have to write schema_name.object_name in my query. But from my schema I am being able to view a table which is not in my schema, but in some other schema. I am not even giving the schema_name before the table name. How is this possible? An option is that this is a synonym. But I’ve checked out the all_synonyms view and its name is not there in the list. Please explain. I’m totally confused. Also, I deleted one row from the table/synonym without giving its schema name. The deletion is reflected in the table present in the other schema also. Are these two tables same? Then why I am not to give the schema name? If this is a synonym, then why it is not present in all_synonyms view? |
|
I think, you have given the 'sys' level privileges to user.please check your user privileges Thanks and Regards Vikram S. |
