However, the target table or view must be available at the time you use the synonym. I can create a table using the schema synonym: SQL> create table DEMO2.MYTABLE as select * from dual; Table … Apps schema contains only Synonyms we can’t create tables in apps schema,where as other schemas contains tables, & all the objects. The PUBLIC phrase in the above plsql create synonym syntax means that the synonym will be accessible for all the valid users, but the user must have the sufficient privileges for the object to use its synonym. The SCHEMA phrase in the plsql create synonym is the name of the schema where the synonym will reside. In a distributed database system, synonyms help simplify SQL statements. Suppose you have a table called sales in the schema owned by the user lion, and you granted the SELECT privilege for the sales table to PUBLIC. Our team utilized AWS Schema Conversion Tool (SCT) to convert the source Oracle database to PostgreSQL. 4. Example: Admin schema has all the tables. Drop a synonym – describe how to drop a synonym from the database. What is an Oracle Synonym? Each of our development environments contain many schemas with numerous tables, grants and synonyms throughout. I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle. Example: Admin schema has all the tables. How can I write a query to accomplish this task? I don't like that very much though, I would always prefer views to private synonyms if the target is a view or a table. The doc will also explain WHAT a synonym is, HOW to create it and HOW to use it and has examples. Sometimes, developer/functional team might ask you access for APPS schema. This is required to make the tables available for other users to access. Create Synonym For All Tables In Schema public synonyms are available to all users in the database. Login to schema DEV. The script below creates synonyms for all the relevant objects in your schema—if they dont exist automatically. Purpose. 2. You can use the Oracle Oracle CREATE SYNONYM command to create a public and private synonym having the same name. Note that the schema object (schema.object) cannot be contained in a package. Create public synonyms for all tables in target schema using below query. To be able to clone an environment, I'd like to be able to run a script that will generate a ""create synonym"" script where I can recreate all the private synonyms for a particular schema. How can I write a query to accomplish this task? dear please send me sample how i create public synonyms for all table and user. Is the a security risk in doing so? I want the User schema to access all the tables owned by Admin. A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.