<!doctypehtml><html lang=en><meta content=IE=edge http-equiv=X-UA-Compatible><meta content=width=device-width,initial-scale=1.0,maximum-scale=1 name=viewport><meta charset=utf-8><title>
   CREATE SYNONYM
  </title><meta content="Oracle DARB XHTML Converter - Version 4.5.2 Build 31"name=generator><meta content=2005-07-10T12:57:20+08:00 name=dcterms.created><meta content=all name=robots><meta content="Database SQL Reference"name=dcterms.title><meta content=B14200-02 name=dcterms.identifier><meta content=SQLRF name=dcterms.isVersionOf><link href=../../index.htm rel=Start title=Home type=text/html><link href=../../dcommon/html/cpyr.htm rel=Copyright title=Copyright type=text/html><script src=../../dcommon/js/headfoot.js></script><script src=../../nav/js/doccd.js></script><link href=toc.htm rel=Contents title=Contents type=text/html><link href=index.htm rel=Index title=Index type=text/html><link href=statements_7.htm rel=Prev title=Previous type=text/html><link href=statements_7002.htm rel=Next title=Next type=text/html><link title="PDF version"href=../b14200.pdf rel=alternate type=application/pdf><link title="ePub version"href=../B14200-02.epub rel=alternate type=application/epub+zip><link title="Mobipocket version"href=../B14200-02.mobi rel=alternate type=application/x-mobipocket-ebook><link href=http://purl.org/dc/terms/ rel=schema.dcterms><link href=../../dcommon/css/fusiondoc.css rel=stylesheet><link href=../../dcommon/css/header.css rel=stylesheet><link href=../../dcommon/css/footer.css rel=stylesheet><link href=../../dcommon/css/fonts.css rel=stylesheet><link href=../../dcommon/css/foundation.css rel=stylesheet><link href=../../dcommon/css/codemirror.css rel=stylesheet><link href=../../nav/css/html5.css rel=stylesheet title=Default><link href=../../dcommon/css/respond-480-tablet.css rel=stylesheet><link href=../../dcommon/css/respond-768-laptop.css rel=stylesheet><link href=../../dcommon/css/respond-1140-deskop.css rel=stylesheet><script src=../../dcommon/js/modernizr.js></script><script src=../../dcommon/js/codemirror.js></script><script src=../../dcommon/js/jquery.js></script><script src=../../dcommon/js/foundation.min.js></script><script src=../../dcommon/js/jqfns.js></script><script src=../../dcommon/js/ohc-inline-videos.js></script><link href=../../dcommon/fancybox/jquery.fancybox.css?v=2.1.5 media=screen rel=stylesheet><script src=../../dcommon/fancybox/jquery.fancybox.pack.js?v=2.1.5></script><link href=../../dcommon/fancybox/helpers/jquery.fancybox-buttons.css?v=1.0.5 media=screen rel=stylesheet><script src=../../dcommon/fancybox/helpers/jquery.fancybox-buttons.js?v=1.0.5></script><script src=../../dcommon/fancybox/helpers/jquery.fancybox-media.js?v=1.0.6></script><link href=../../dcommon/fancybox/helpers/jquery.fancybox-thumbs.css?v=1.0.7 media=screen rel=stylesheet><script src=../../dcommon/fancybox/helpers/jquery.fancybox-thumbs.js?v=1.0.7></script><script>window.ohcglobal || document.write('<script src="/en/dcommon/js/global.js">\x3C/script>')</script><body><a class="accessibility-top skipto"href=#BEGIN tabindex=0> Go to main content </a><header></header><div class=row id=CONTENT><div class="IND large-9 medium-8 columns"dir=ltr><a id=BEGIN name=BEGIN> </a><span id=PAGE style=display:none;> 393/479 </span><a id=CJAJCDDF> </a><hr><div class=refsect1><h1 class=indexterm><a id=SQLRF01401> </a> CREATE SYNONYM <a id=sthref7344> </a></h1><p class=titleinrefsubsect><a id=sthref7345> </a> Purpose<p><a id=sthref7346> </a> <a id=sthref7347> </a> <a id=sthref7348> </a> <a id=sthref7349> </a> <a id=sthref7350> </a> <a id=sthref7351> </a> <a id=sthref7352> </a> <a id=sthref7353> </a> <a id=sthref7354> </a> Use the <code>
       CREATE
      </code> <code>
       SYNONYM
      </code> statement to create a <span class=bold> synonym </span> , which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.<p><a id=sthref7355> </a> <a id=sthref7356> </a> <a id=sthref7357> </a> <a id=sthref7358> </a> <a id=sthref7359> </a> Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.<p>You can refer to synonyms in the following DML statements: <code>
       SELECT
      </code> , <code>
       INSERT
      </code> , <code>
       UPDATE
      </code> , <code>
       DELETE
      </code> , <code>
       FLASHBACK
      </code> <code>
       TABLE
      </code> , <code>
       EXPLAIN
      </code> <code>
       PLAN
      </code> , and <code>
       LOCK
      </code> <code>
       TABLE
      </code> .<p>You can refer to synonyms in the following DDL statements: <code>
       AUDIT
      </code> , <code>
       NOAUDIT
      </code> , <code>
       GRANT
      </code> , <code>
       REVOKE
      </code> , and <code>
       COMMENT
      </code> .<div class=infoboxnotealso><p class=notep1>See Also:</p><span class=italic> <a class="olink CNCPT"href=../b14220/toc.htm> Oracle Database Concepts </a> </span> for general information on synonyms</div><p class=titleinrefsubsect><a id=sthref7360> </a> Prerequisites<p>To create a private synonym in your own schema, you must have the <code>
       CREATE
      </code> <code>
       SYNONYM
      </code> system privilege.<p>To create a private synonym in another user's schema, you must have the <code>
       CREATE
      </code> <code>
       ANY
      </code> <code>
       SYNONYM
      </code> system privilege.<p>To create a <code>
       PUBLIC
      </code> synonym, you must have the <code>
       CREATE
      </code> <code>
       PUBLIC
      </code> <code>
       SYNONYM
      </code> system privilege.<p class=titleinrefsubsect><a id=sthref7361> </a> Syntax<p class=subhead2><a id=sthref7362> </a> <span class=italic> create_synonym </span> ::=</p><img alt="Description of create_synonym.gif follows"title="Description of create_synonym.gif follows"height=123 src=img/create_synonym.gif width=671><br><a href=img_text/create_synonym.htm id=sthref7363> Description of the illustration create_synonym.gif </a><br><br><p class=titleinrefsubsect><a id=sthref7364> </a> Semantics<p class=subhead1><a id=sthref7365> </a> OR REPLACE<p>Specify <code>
       OR
      </code> <code>
       REPLACE
      </code> to re-create the synonym if it already exists. Use this clause to change the definition of an existing synonym without first dropping it.<p><a id=sthref7366> </a> <span class=subhead3> Restriction on Replacing a Synonym  </span> You cannot use the <code>
       OR
      </code> <code>
       REPLACE
      </code> clause for a type synonym that has any dependent tables or dependent valid user-defined object types.<p class=subhead1><a id=sthref7367> </a> PUBLIC <a id=sthref7368> </a> <a id=sthref7369> </a> <a id=sthref7370> </a><p>Specify <code>
       PUBLIC
      </code> to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.<p>When resolving references to an object, Oracle Database uses a public synonym only if the object is not prefaced by a schema and is not followed by a database link.<p>If you omit this clause, then the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.<p><a id=sthref7371> </a> <span class=subhead3> Notes on Public Synonyms  </span> The following notes apply to public synonyms:<ul><li><p>If you create a public synonym and it subsequently has dependent tables or dependent valid user-defined object types, then you cannot create another database object of the same name as the synonym in the same schema as the dependent objects.</p><li><p>Take care not to create a public synonym with the same name as an existing schema. If you do so, then all PL/SQL units that use that name will be invalidated.</p></ul><p class=subhead1><a id=sthref7372> </a> <span class=italic> schema </span><p>Specify the schema to contain the synonym. If you omit <code>
       <span class=codeinlineitalic> schema </span>
      </code> , then Oracle Database creates the synonym in your own schema. You cannot specify a schema for the synonym if you have specified <code>
       PUBLIC
      </code> .<p class=subhead1><a id=sthref7373> </a> <span class=italic> synonym </span><p>Specify the name of the synonym to be created.<div class=infoboxnotewarn><p class=notep1>Note:</p> Synonyms longer than 30 bytes can be created and dropped. However, unless they represent a Java name they will not work in any other SQL command. Names longer than 30 bytes are transformed into an obscure shorter string for storage in the data dictionary.</div><div class=infoboxnotealso><p class=notep1>See Also:</p><a href=#i2153082> "CREATE SYNONYM: Examples" </a> and <a href=#i2102145> "Oracle Database Resolution of Synonyms: Example" </a></div><p class=subhead1><a id=sthref7374> </a> FOR Clause<p>Specify the object for which the synonym is created. The schema object for which you are creating the synonym can be of the following types:<ul><li><p>Table or object table</p><li><p>View or object view</p><li><p>Sequence</p><li><p>Stored procedure, function, or package</p><li><p>Materialized view</p><li><p>Java class schema object</p><li><p>User-defined object type</p><li><p>Synonym</p></ul><p>The schema object need not currently exist and you need not have privileges to access the object.<p><a id=sthref7375> </a> <span class=subhead3> Restriction on the FOR Clause  </span> The schema object cannot be contained in a package.<p><a id=sthref7376> </a> <span class=subhead3> <span class=italic> schema </span>   </span> Specify the schema in which the object resides. If you do not qualify object with <code>
       <span class=codeinlineitalic> schema </span>
      </code> , then the database assumes that the schema object is in your own schema.<p>If you are creating a synonym for a procedure or function on a remote database, then you must specify <code>
       <span class=codeinlineitalic> schema </span>
      </code> in this <code>
       CREATE
      </code> statement. Alternatively, you can create a local public synonym on the database where the object resides. However, the database link must then be included in all subsequent calls to the procedure or function.<p><a id=sthref7377> </a> <span class=subhead3> <span class=italic> dblink <a id=sthref7378> </a> <a id=sthref7379> </a> <a id=sthref7380> </a> </span>   </span> You can specify a complete or partial database link to create a synonym for a schema object on a remote database where the object is located. If you specify <code>
       <span class=codeinlineitalic> dblink </span>
      </code> and omit <code>
       <span class=codeinlineitalic> schema </span>
      </code> , then the synonym refers to an object in the schema specified by the database link. Oracle recommends that you specify the schema containing the object in the remote database.<p>If you omit <code>
       <span class=codeinlineitalic> dblink </span>
      </code> , then Oracle Database assumes the object is located on the local database.<p><a id=sthref7381> </a> <span class=subhead3> Restriction on Database Links  </span> You cannot specify <code>
       <span class=codeinlineitalic> dblink </span>
      </code> for a Java class synonym.<div class=infoboxnotealso><p class=notep1>See Also:<ul><li><p><a href=sql_elements009.htm#i27761> "Referring to Objects in Remote Databases" </a> for more information on referring to database links</p><li><p><a href=statements_5005.htm#i2061505> CREATE DATABASE LINK </a> for more information on creating database links</p></ul></div><p class=titleinrefsubsect><a id=g2266838> </a> Examples<p><a id=i2153082> </a> <span class=subhead3> CREATE SYNONYM: Examples  </span> To define the synonym <code>
       offices
      </code> for the table <code>
       locations
      </code> in the schema <code>
       hr
      </code> , issue the following statement:<pre>CREATE SYNONYM offices 
   FOR hr.locations;

</pre><p>To create a <code>
       PUBLIC
      </code> synonym for the <code>
       employees
      </code> table in the schema <code>
       hr
      </code> on the <code>
       remote
      </code> database, you could issue the following statement:<pre>CREATE PUBLIC SYNONYM emp_table 
   FOR hr.employees@remote.us.oracle.com;

</pre><p>A synonym may have the same name as the underlying object, provided the underlying object is contained in another schema.<p><a id=i2102145> </a> <span class=subhead3> Oracle Database Resolution of Synonyms: Example  </span> Oracle Database attempts to resolve references to objects at the schema level before resolving them at the <code>
       PUBLIC
      </code> synonym level. For example, the schemas <code>
       oe
      </code> and s <code>
       h
      </code> both contain tables named <code>
       customers
      </code> . In the next example, user <code>
       SYSTEM
      </code> creates a <code>
       PUBLIC
      </code> synonym named <code>
       customers
      </code> for <code>
       oe.customers
      </code> :<pre>CREATE PUBLIC SYNONYM customers FOR oe.customers;

</pre><p>If the user <code>
       sh
      </code> then issues the following statement, then the database returns the count of rows from <code>
       sh.customers
      </code> :<pre>SELECT COUNT(*) FROM customers;

</pre><p>To retrieve the count of rows from <code>
       oe.customers
      </code> , the user <code>
       sh
      </code> must preface <code>
       customers
      </code> with the schema name. (The user <code>
       sh
      </code> must have select permission on <code>
       oe.customers
      </code> as well.)<pre>SELECT COUNT(*) FROM oe.customers;

</pre><p>If the user <code>
       hr
      </code> 's schema does not contain an object named <code>
       customers
      </code> , and if <code>
       hr
      </code> has select permission on <code>
       oe.customers
      </code> , then <code>
       hr
      </code> can access the <code>
       customers
      </code> table in <code>
       oe
      </code> 's schema by using the public synonym <code>
       customers
      </code> :<pre>SELECT COUNT(*) FROM customers;

</pre></div></div></div><footer></footer><noscript><p>Scripting on this page enhances content navigation, but does not change the content in any way.</p></noscript>