Синоним oracle dblink

While I understand that this question is 3+ years old, someone might be able to benefit from a different answer in the future.

Let’s imagine that I have 4 databases, 2 for production and 2 for dev / testing.

Prod DBs: PRDAPP1DB1 and PRDAPP2DB1
Dev DBs: DEVAPP1DB1 and DEVAPP2DB1

The «APP2» databases are running procedures to extract and import data from the APP1 databases. In these procedures, there are various select statements, such as:

declare
iCount INTEGER;
begin
  insert into tbl_impdata1
  select sysdate, col1, col2, substr(col3,1,10), substr(col3,15,3)
  from tbl1@dblink2; -- Where dblink2 points to DEVAPP1DB1
  ...
  <more statements here>
  ...
EXCEPTION
  <exception handling code here>
end;

Now that is okay for development but the dblink2 constantly needs to be changed to dblink1 when deploying the updated procedure to production.

As it was pointed out, synonyms cannot be used for this purpose.
But instead, create the db links with the same name, different connection string.

E.g. on production:

CREATE DATABASE LINK "MyDBLINK" USING 'PRDAPP1DB1';

And on dev:

CREATE DATABASE LINK "MyDBLINK" USING 'DEVAPP1DB1';

And then in the procedures, change all «@dblink1» and «@dblink2» to «@mydblink» and it all should be transparent from there.

In this article, I’ll explain all you need to know about Synonyms.

What is an Oracle Synonym?

A synonym is an object in a database that represents an alternative name for other objects, such as tables, views, sequences, and stored procedures.

Why Would you Use a Synonym?

Why would you want to create a synonym? Why not just rename the original object?

The main reason is to simplify the user access to an object. You can provide access to an object to a user using a synonym, and you don’t need to worry about who owns the original object.

It also simplifies access to objects, especially when multiple databases and schemas are involved.

The examples later in this article will demonstrate this further.

Oracle Synonym vs View

As mentioned above, a synonym is an alias or alternative name for an object.

How is this different from a view?

A view object is an SQL query that is saved and run when other queries use that view. It works like a table.

Why would you use a synonym instead of a view?

Well, a view contains more complicated logic. If you just want to provide an alias for a table or another object, you can use a synonym. If you want to SELECT data from a table and use WHERE conditions or other logic, then a view is better.

Also, a view can only be created on objects with data, such as tables and other views. Synonyms can be created on tables, views, sequences, stored procedures, and more.

They both have their advantages, but they should only be used when appropriate.

Difference Between Public and Private Synonym in Oracle

There are two types of synonyms that can be created on an Oracle database: public and private.

• Public synonym: can be accessed by any user on the database. The user who creates the synonym it does not own it – it’s owned by the PUBLIC user group.
• Private synonym: can only be accessed by the person who created the synonym. This user is also the owner. The synonym name must be unique within the schema.

What happens if you have a public synonym with the same name as an existing object? Or a private synonym?

This is a valid scenario, and Oracle has rules on which order they are processed in. Just like how, in mathematics, the multiplication and division are performed before the addition and subtraction, Oracle has an order in the way that the objects are looked up.

The order of precedence for objects and synonyms is:

1. Local schema object. Oracle will look at the user’s schema for the object name.
2. Private synonym. If a schema object is not found, Oracle will look in the private synonyms for the user.
3. Public synonym. If a private synonym is not found, Oracle will look in the public synonyms.

This is how public synonyms and private synonyms compare:

Criteria	Public	Private
Who owns it?	PUBLIC user group	The user that created it
Scope of the unique name	All database objects	The schema of the user who created it
Who can use it?	All database users	Only the user who created it
Order of precedence	Third, after schema objects and private synonyms	Second, after schema objects

Generally, it’s a good idea to avoid creating public synonyms. Sure, they can make management a bit easier, but they can pose a security risk and can make development harder, because there’s an object that exists and available to all users.

How to Create a Synonym in Oracle

To create a synonym in Oracle (either private or public), we use the CREATE SYNONYM command.

The syntax is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] synonym_name
FOR [schema.] object_name [@dblink_name];

The parameters for this function are:

• OR REPLACE: This is optional and allows you to replace an existing synonym name, instead of dropping it and recreating it.
• PUBLIC: Use the PUBLIC keyword to create a public synonym. If this is omitted, a private synonym is created.
• schema: This is optional and is the name of the schema you want to create the synonym in. If this is omitted, the synonym is created in your own schema. If you are creating a PUBLIC synonym, you cannot specify a schema.
• synonym_name: The name of the new synonym that is being created. It should be 30 characters or less. I say should, because names longer than 30 characters can be created and dropped, but they are transformed into a shorter string for storage.
• FOR schema: The schema is optional, but specifies the schema name that the object exists in.
• object_name: The name of the object that the synonym will relate to.
• dblink_name: This is the name of the database link if it is required.

A synonym can be created on the following types of objects:

• table
• view
• stored procedure
• function
• package
• sequence
• materialised view
• synonym
• java class schema object
• user-defined object

Let’s see some examples of creating a synonym.

Example 1 – Create private synonym

CREATE SYNONYM emp
FOR hr.employees;

This creates a new private synonym called emp, which refers to the employees object in the hr schema.

Example 2 – Create public synonym

CREATE PUBLIC SYNONYM cust
FOR sales.customers;

This will create a new public synonym for the customer object in the sales schema. All users can query the cust synonym and access the sales.customers table (if they have access to the underlying sales.customers table).

Example 3 – Same name

CREATE SYNONYM product
FOR sales.product;

This will create a private synonym. Notice that both objects have the same name. A query such as this will look for the local object first (which does not exist), and then the synonym:

SELECT * FROM product;

However, querying on the original object will also access that object:

SELECT * FROM sales.product;

If a new table was created in the user’s current schema called product, then this query will look at that table instead of the synonym:

SELECT * FROM product;

How to Drop a Synonym in Oracle

To remove a synonym that already exists, you can drop it from the database.

The syntax for doing this is:

DROP [PUBLIC] SYNONYM [schema.] synonym_name [FORCE];

The parameters are:

• PUBLIC: specifies whether you are dropping a private or public synonym. If you’re not sure what type your synonym is, then see the section below on finding all synonyms in a database.
• schema: This is the name of the schema that your synonym exists on. This is only needed if you are dropping a private schema.
• synonym_name: The name of the synonym to be dropped.
• FORCE: This will force Oracle to drop the synonym even if it has dependencies.

Let’s see some examples.

Example 1 – Drop a public synonym

DROP PUBLIC SYNONYM emp;

This will drop the public synonym emp.

Example 2 – Drop a private synonym

DROP SYNONYM customer;

This will drop the private synonym called customer.

Example 3 – Drop a synonym with a schema

DROP SYNONYM hr.emp;

This will drop the synonym emp that exists in the hr schema.

How to Find All Synonyms In the Database

So you’ve created some synonyms, or your database already has some. How can you check the synonyms in Oracle? How do you know if a synonym is public or private?

There is no “oracle list synonyms” command, but you can query some database views to find this information:

• all_synonyms: View synonyms available to the current user.
• dba_synonyms: View all synonyms in the database.
• user_synonyms: Private synonyms owned by the current user.

To find a list of synonyms on the database, you can run these queries.

SELECT * FROM all_synonyms;

This shows us a list of all synonyms available to the current user.

A query on the dba_synonyms view will show us all synonyms in the database:

SELECT * FROM dba_synonyms;

And finally, a query on user_synonyms will show us all private synonyms owned by the current user:

SELECT * FROM user_synonyms;

Conclusion

So, in summary, a synonym is a database object that allows you to create an alias to another object. They are helpful for simplifying your queries and improving the way access to data and objects is handled.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Answer by Brayden Potter

dblink  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 dblink and omit schema, 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.,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.,If you omit dblink, then Oracle Database assumes the object is located on the local database.,To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE SYNONYM: Examples To define the synonym offices for the table locations in the schema hr, issue the following statement:

CREATE SYNONYM offices 
   FOR hr.locations;

To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table 
   FOR [email protected];

Oracle Database Resolution of Synonyms: Example Oracle Database attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. For example, the schemas oe and sh both contain tables named customers. In the next example, user SYSTEM creates a PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

If the user sh then issues the following statement, then the database returns the count of rows from sh.customers:

SELECT COUNT(*) FROM customers;

To retrieve the count of rows from oe.customers, the user sh must preface customers with the schema name. (The user sh must have select permission on oe.customers as well.)

SELECT COUNT(*) FROM oe.customers;

If the user hr‘s schema does not contain an object named customers, and if hr has select permission on oe.customers, then hr can access the customers table in oe‘s schema by using the public synonym customers:

SELECT COUNT(*) FROM customers;

---

Answer by Hunter Taylor

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!,Connor and Chris don’t just spend all day on AskTOM. You can also catch regular content via Connor’s blog and Chris’s blog. Or if video is more your thing, check out Connor’s latest video and Chris’s latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account. ,Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

--
-- source db
--
SQL> grant create session, create database link, create public synonym to craig identified by craig;

Grant succeeded.

SQL> grant create session, create database link to denver identified by denver;

Grant succeeded.
--
-- target db
--
SQL> grant create session to craig identified by craig;

Grant succeeded.

SQL> grant create session to denver identified by denver;

Grant succeeded.


--
-- source db
--
SQL> conn craig/craig
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from [email protected];

no rows selected

SQL> create public synonym fooaudit for [email protected];

Synonym created.

SQL> select * from fooaudit;

no rows selected

SQL> conn denver/denver
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from [email protected];

no rows selected

--
-- Now you might think that this would fail.  
-- After all, DENVER does not have access to CRAIG's objects
--
-- but it works 
--
SQL> select * from fooaudit;

no rows selected

SQL> select * from dba_synonyms
  2  where synonym_name like 'FOO%'
  3  @pr
==============================
OWNER                         : PUBLIC
SYNONYM_NAME                  : FOOAUDIT
TABLE_OWNER                   :
TABLE_NAME                    : TAB
DB_LINK                       : FOO
ORIGIN_CON_ID                 : 0

SQL> drop public synonym fooaudit;

Synonym dropped.

SQL> create public synonym fooaudit for [email protected];

Synonym created.

SQL> conn denver/denver
Connected.

SQL> select * from fooaudit;
select * from fooaudit
              *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from FOO


SQL> conn craig/craig
Connected.

SQL> select * from fooaudit;

no rows selected

---

Answer by Leighton Barrera

Use the CREATE SYNONYM statement to create a synonym, 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.,As it was pointed out, synonyms cannot be used for this purpose.
But instead, create the db links with the same name, different connection string.,But when I query anything using the synonym instead of the dblink, I’m getting connection description for remote database not found error.,

1

What is exactly your problem with the 100 synonym? I mean are you concerned about the number of objects or just the time it would take to create them?

– Gergely Bacso

Dec 8 ’15 at 8:01

The reason why your second query fails is that the synomym you have created is not functioning correctly. It is not being validated properly at creation time, and you can create any sort of incorrect synonyms like that. To verify, just test the following statement:

create synonym dblink3 for no_object_with_this_name;

You will still get a response like this:

*Synonym DBLINK3 created.*

---

Answer by Frankie Moran

Database Link to None-Oracle database
,These are online courses outside the o7planning website that we introduced, which may include free or discounted courses.,
 

Split comma separated string and pass to IN clause of select statement in Oracle
,
Access objects via Database Link

-- On Prod1 schema.
-- Create a Database Link named mydblink
-- Connect to Prod2 schema on myserver2.

CREATE DATABASE LINK mydblink
CONNECT TO PROD2 IDENTIFIED BY "Prod2Password"
USING '(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVICE_NAME = db12c)
)
)';

---

Answer by Juliette Nunez

Use the CREATE SYNONYM statement to create a synonym, 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.,The reason why your second query fails is that the synomym you have created is not functioning correctly. It is not being validated properly at creation time, and you can create any sort of incorrect synonyms like that. To verify, just test the following statement:,Is it a pain to have to set/reset 100+ private synonyms? Yep. But if it is something you need to do often then bundle up a procedure to do it for you where you pass in the db_link name and it cycles through and resets the synonyms for you.,Your only solution is to have the code instead reference the tables by synonyms, and set private synonyms to point across the correct db_link. That way your code continues to «Select from REMOTE_TABLE1» and you just can flip which DB_LINK you are getting that remote table from.

The reason why your second query fails is that the synomym you have created is not functioning correctly. It is not being validated properly at creation time, and you can create any sort of incorrect synonyms like that. To verify, just test the following statement:

create synonym dblink3 for no_object_with_this_name;

You will still get a response like this:

*Synonym DBLINK3 created.*

I don’t see the point in creating a synonym for the dblink itself. Ideally you create the synonym for the remote table using the dblink.

CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';
CREATE SYNONYM my_table FOR [email protected]_db_link;

Now, you could query the remote table using the synonym:

SELECT * FROM my_table;

---

Answer by Alonzo Preston

First, specify the name of the database link after the CREATE DATABASE LINK keywords.,Home » Oracle Database Administration » Oracle CREATE DATABASE LINK,To create a private database link, you use the CREATE DATABASE LINK statement as follows:,Second, provide user and password of the remote database after the CONNECT TO and IDENTIFIED BY keywords.

Once you create a database link, you can access the tables or views from the remote database using the following pattern:

.wp-block-code {
	border: 0;
	padding: 0;
}

.wp-block-code > div {
	overflow: auto;
}

.shcb-language {
	border: 0;
	clip: rect(1px, 1px, 1px, 1px);
	-webkit-clip-path: inset(50%);
	clip-path: inset(50%);
	height: 1px;
	margin: -1px;
	overflow: hidden;
	padding: 0;
	position: absolute;
	width: 1px;
	word-wrap: normal;
	word-break: normal;
}

.hljs {
	box-sizing: border-box;
}

.hljs.shcb-code-table {
	display: table;
	width: 100%;
}

.hljs.shcb-code-table > .shcb-loc {
	color: inherit;
	display: table-row;
	width: 100%;
}

.hljs.shcb-code-table .shcb-loc > span {
	display: table-cell;
}

.wp-block-code code.hljs:not(.shcb-wrap-lines) {
	white-space: pre;
}

.wp-block-code code.hljs.shcb-wrap-lines {
	white-space: pre-wrap;
}

.hljs.shcb-line-numbers {
	border-spacing: 0;
	counter-reset: line;
}

.hljs.shcb-line-numbers > .shcb-loc {
	counter-increment: line;
}

.hljs.shcb-line-numbers .shcb-loc > span {
	padding-left: 0.75em;
}

.hljs.shcb-line-numbers .shcb-loc::before {
	border-right: 1px solid #ddd;
	content: counter(line);
	display: table-cell;
	padding: 0 0.75em;
	text-align: right;
	-webkit-user-select: none;
	-moz-user-select: none;
	-ms-user-select: none;
	user-select: none;
	white-space: nowrap;
	width: 1%;
}[email protected]_link
Code language: SQL (Structured Query Language) (sql)

For example, you can query data from a table in the remote database as if it was in the local server:

SELECT * FROM [email protected]_link;
Code language: SQL (Structured Query Language) (sql)

This syntax shows how to create a synonym for a remote table:

CREATE SYNONYM local_table
FOR [email protected]_link;
Code language: SQL (Structured Query Language) (sql)

And this query uses the synonym instead of the remote table name with the database link:

SELECT * FROM local_table;
Code language: SQL (Structured Query Language) (sql)

To create a private database link, you use the CREATE DATABASE LINK statement as follows:

CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';
Code language: SQL (Structured Query Language) (sql)

The following statement shows how to create the private database link to a user in a remote database with a full connection string.

CREATE DATABASE LINK dblink 
    CONNECT TO remote_user IDENTIFIED BY password
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=service_name))
            )';
Code language: SQL (Structured Query Language) (sql)

To create a public database link, just add the PUBLIC keyword:

CREATE PUBLIC DATABASE LINK dblink 
    CONNECT TO remote_user IDENTIFIED BY password
    USING 'remote_database';
Code language: SQL (Structured Query Language) (sql)

First, add the following entry to tnsnames.ora file in the local Oracle Database server. Typically, the tnsnames.ora is located in the directory /NETWORK/ADMIN/ under ORACLE_HOME:

SALES =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.100.143)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = SALES_PRD)
    )
)
Code language: SQL (Structured Query Language) (sql)

Next, use the CREATE DATABASE LINK statement to create a new private database link that connects to the SALES database via bob‘s account:

CREATE DATABASE LINK sales 
    CONNECT TO bob IDENTIFIED BY Abcd1234
    USING 'SALES';
Code language: SQL (Structured Query Language) (sql)

Then, issue the SELECT statement to query data from the customers table on the SALES database:

SELECT * FROM [email protected];
Code language: SQL (Structured Query Language) (sql)

After that, insert a new row into the customers table:

INSERT INTO [email protected](customer_id, name, email)
VALUES(2,'XYZ Inc','[email protected]');
Code language: SQL (Structured Query Language) (sql)

Finally, query data from the customers table again:

SELECT * FROM [email protected]
Code language: SQL (Structured Query Language) (sql)

---

Answer by Nadia Stanton

This Oracle tutorial explains how to create and drop synonyms in Oracle with syntax and examples.,A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.,Let’s look at an example of how to create a synonym in Oracle.,The syntax to create a synonym in Oracle is:

The syntax to create a synonym in Oracle is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
  FOR [schema .] object_name [@ dblink];

For example:

CREATE PUBLIC SYNONYM suppliers
FOR app.suppliers;

This first CREATE SYNONYM example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

SELECT *
FROM suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the OR REPLACE phrase as follows:

CREATE OR REPLACE PUBLIC SYNONYM suppliers
FOR app.suppliers;

---

First, definition

Oracle’s synonym is understood from the literal, the meaning of alias, and the functionality of the view is a mapping relationship. When using synonyms, the Oracle database translates him into the name of the corresponding scheme. Similar to the view object, the synonym does not occupy the actual storage space, only the definition of synonyms are saved in the data dictionary. Most database objects in the Oracle database, such as tables, views, materialized views, sequences, functions, stored procedures, package symbols, and database administrators can define synonyms in the actual situation.

Oracle Object Knowledge: Synonyms Create a deletion, synonym is an alias of the database object, Oracle can specify an alias for tables, views, sequences, processes, functions, packages, and the like. Synonyms have two types:

Private synonym: Users with CREATE SYNONYM (including non-administrator users) create private synonyms, created private synonyms can only be used by current users (creator users).

Public synonym: System administrators can create public synonyms, and public synonyms can be accessed by all users. (Note: Public synonyms are accessible to all users under the same instance)

Second, synonym

Oracle has two types of synonyms, namely Oracle utility synonyms and private synonyms.

The synonyms created by ordinary users are generally private synonyms. The common synonyms typically have DBA creation. If you want to create synonyms, you will need Create Public Synonym system privileges.

1), Oracle common synonym: It is owned by a special user group public. As the name suggests, all users of the database can use common synonyms. Common synonyms are used to indicate some common database objects, which often need to be referenced.

2), Oracle private synonym: It is corresponding to the public synonym, he is all all users who create his users, of course, this synonym creator can control other users to use their own private synonyms.

Third, the benefits of synonyms

1, do not account for memory space, save a lot of database space

2, simplify the access of the database object

3. Improve the security of database object access

4. The scope of use of the extended database can achieve seamless interaction between different database users; synonyms can be created on different one database servers, connect via network

Fourth, synonymouseffect

1) Multi-user coordinated development, you can block the name of the object and its holder. If there is no synonym, when operates the table of other users, you must pass the User name. Object name, you can hide the User name after using the Oracle synonym. Of course, it should be noted that the PUBLIC synonym only defines a database object. Public alias, other users can access this database object through this alias, but also to authorize this user.

2) Simplify the SQL statement for the user. One of the above is actually an embodiment of simplifying SQL, and if the name of the table built is very long, you can create an Oracle synonym for this table to simplify SQL development.

3) Provide location transparency for remote objects of distributed databases.

4) The role of Oracle synonyms in the database link

The database link is a named object that describes the path to another database, through which communication between different databases can be implemented.

5. Give privileges, when you create synonyms, you need to have related permissions:

Use the SYS account to give the test account to create [public] Synonym permissions

- Authorized limited private synonym:
Grant create synonym to username;
Grant create any synonym to username;

 - Authorized limited public synonym:
Grant create public synonym to username;

 - Delete private synonyms need to have DROP ANY SYNONYM permissions. (If there is a Drop Any Table, you can also delete the drop any synonym permissions)
Grant drop any synonym to username;

 - Delete the public synonym needs to have a Drop public Synonym permission.
Grant drop public synonym to username;

 - View the way the current user permission
select * from user_sys_privs;

6. The syntax of the synonym is:

- View all synonyms:
select * from dba_synonyms；
 - Query results and meaning
Owner,synonym_name,table_owner,table_name,db_link;
 (Synonyms owner's username), (the name of synonym), (the owner of the object referenced by the same word), (the name of the object referenced by the synonym), (the name of the database link referenced in the remote synonym)

7. The grammar for creating synonyms is:

- Creating a syntax for a synonym with a male Oracle:
 Create [public] Synynym synonym name for [username.] Objectname;
 For example: CREATE PUBLIC SYNONYM TABLE_A for User.table_a;

 - Creating a grammar for private Oracle synonyms:
 Create Synonym synonym name for [username.] Objectname;
 For example: CREATE SYNONYM TABLE_B for User.table_b;

Eight, the deletion of synonyms

Because synonyms are also objects, delete syntax is the same as

DROP [public] Synonym synonym name
 - Delete public synonym:
drop public synonym table_a;

 - Delete private synonyms:
drop synonym table_b;

Nine, cross-library query, Oracle DBLINK

1, DBLINK extension access, create synonyms

If you want to access tables table_a under different databases or different users, of course, you can also use synonyms, but you need to create a Database Link to extend access, and then create database peer in the following statements:

create synonym table_a for [email protected]_Link;

2, Oracle DBLINK creation case

Oracle When making a cross-library access, you can create a DBLINK implementation, such as the A library to check the B library, you can build a dblink on the A library, do not need to be built in B, unless the B library also wants to check A Table of the library;

3. Create a DBLINK syntax:

CREATE [PUBLIC] DATABASE LINK link
CONNECT TO username IDENTIFIED BY password
USING ‘connectstring’

 Description:
 1) Permissions: Creating a database link must have system permissions for Create Database Link or Create Public Database Link,
 The account used to log in to the remote database must have CREATE SESSION permissions. Both permissions are included in the Connect role
 (CREATE PUBLIC DATABASE LINK Permissions in DBA). A public database link is available for all users in the database,
 And a private link is only available to the user created. It is impossible to authorize the private database link by a user to another user, a database
 The link is either public or private.

 2) LINK When global_name = true, the LINK name must be the same as the global database name global_name of the remote database;
                         Otherwise, you can name any.

 3) ConnectString connection string, define the connection string of the remote database in tnsnames.ora.

 4) UserName, Password: The username, password of the remote database. If you do not specify, log in to the remote database with the current username and password.

4, give permission

Before you create Database Link, we need to judge whether the user has permission to create Database Link;

Database Link is divided into two types:A PRIVATE (Person) for public (public). As the name suggests, all users of the library can be used, and personal dblink can only be used by the creator user.
Creating DBLINK is to have the appropriate permissions:

[email protected]>select * from user_sys_privs where privilege like upper('%LINK%');
USERNAME             PRIVILEGE               ADM
------------------------------ ---------------------------------    ---
SYS               CREATE DATABASE LINK         NO
SYS               DROP PUBLIC DATABASE LINK      NO
SYS               CREATE PUBLIC DATABASE LINK      NO

5, create dblink 

The establishment of the server is configured to configure the TNSName configuration of the remote database. When there is a parameter in the TNSNames file, you can create DBLINK after the corresponding permissions, and the creation method has the following:

Create a personal dblink statement begins with «CREATE DATABASE LINK»:

create database link test1 using 'test';

Create a public dblink statement «create public database link», create a public dblink with the specified user and password:

create public database link test3 connect to scott identified by tiger using 'test';

Built a shared database connection:

create shared public database link test4 connect to scott identified by "tiger" 
authenticated by username identified by "passwd" using 'test';

Database link using Shared is the number of connections to the remote database to avoid too much connection to remote databases.

Another way of creation is not using TNSNAME

CREATE database link link_name
CONNECT TO user IDENTIFIED BY password
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sa)
)
)';

6, delete DBLINK

- First need to query what DBLINK is available 
select owner,object_name from dba_objects where object_type='DATABASE LINK';

 If Pubilc is deleted as: Drop public Database Link Link Name
 If you are personal, the delete is: DROP DATABASE LINK Link Name

Ten, example demonstration

1. Different users in the same library

As shown in the figure, there is a table v_rpt_insinfo_tj in WST users.

But in Test users don’t have this table, the table V_RPT_INSINFO_TJ cannot be accessed under TEST users;

Create a private synonym at TEST users:

- Create a public synonym
create public synonym V_RPT_INSINFO_TJ for wst.V_RPT_INSINFO_TJ;

 - Create a private synonym
create synonym V_RPT_INSINFO_TJ for wst.V_RPT_INSINFO_TJ;

After you create it, you can access the WST user’s table V_RPT_INSINFO_TJ under Test User.

View synonym

Inquiries in DBA privileges (SYS):

SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME IN ( 'V_RPT_INSINFO_TJ','V_WEB_FEE_INSINFO');


OWNER        SYNONYM_NAME      TABLE_OWNER    TABLE_NAME         DB_LINK
--------      ------------------   --------------   ---------------   ---------------
PUBLIC        V_RPT_INSINFO_TJ    WST        V_RPT_INSINFO_TJ       null
TEST        V_WEB_FEE_INSINFO   WST        V_WEB_FEE_INSINFO      null

Query the current user (TEST):

SELECT * FROM USER_SYNONYMS;

SYNONYM_NAME           TABLE_OWNER       TABLE_NAME          DB_LINK
-------------------    ------------------   ------------------------  -------------------
V_WEB_FEE_INSINFO        WST           V_WEB_FEE_INSINFO      null

2, cross-library synonym presentation

In the 192.168.81.10 library, the user CW cannot access V_RPT_INSINFO_TJ, as shown in the following figure.

Give permission statement

- View some current users with Create Database LINK permissions
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='CW';

If the query is returned, it means that you have a Database Link permission, otherwise, you need to use it.Sys landingORCL gives CW users to create permissions

- Grant CW users to create DBLINK permissions
grant create public database link to cw;

grant create database link to cw;


 - Delete DBLINK permissions for CW users
grant drop public database link to cw;

At this time, the SQL statement that is available in the CW user and then performs the SQL statement that has permission, and it will find that there is a return line, indicating that CW This user already has permission to create Database Link.

SQL creates a DBLINK statement (CW user):

Note that if the password is the beginning, «» enclose it (due to the 192.168.81.10 server CW users, there is no TNSNAME file, so it is created without using the TNSNAME condition)

- Create a public DBLINK
create public database link CWLINK connect to wst identified by "12" USING '192.168.81.10:1521/gbksl';

 - View CW users have a public DBLINK name to "CWLINK"
select * from SYS.ALL_DB_LINKS where db_link='CWLINK';

Public dblink illustration:

- Create a personal dblink
create database link CWSLINK 
  connect to wst identified by "12"
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';
  
 - View CW users have private DBLINK names "cwslink", as shown below:
select * from SYS.ALL_DB_LINKS where db_link='CWSLINK';

Private DBLINK illustration:

In this way, you will complete the simple Database simple creation. CW users can query across library.

Create synonym

From the previous step we can see the CW user query WST user’s V_RPT_INSINFO_TJ view is more complicated, because it is necessary to query success in @dblink methods. Fortunately, Oracle provides the concept of synonyms, we can use «v_rpt_insinfo_tj @ cwslink» to use a simpler word (such as V_RP, as long as you don’t follow the CW user conflict), so we can inquire The same is to query the V_RP view like a normal table / view.

create synonym V_RP for [email protected]

DBLINK Note Description:

In the DBLink description above, we see that A (CW) users want to access the B (WST) user’s v_rpt_insinfo_tj table or view, we directly query in a (CW) user by creating DBLINK’s dblink in a (WST) user To a v_rpt_insinfo_tj table or view, all tables or views in the B (WST) user can be accessed by the DBLink created by A (CW) users, and this is for B (WST) users. Saying is unsafe, in many cases the remote database does not open the entire user B (WST) to give a (CW) user access, so we need to introduce C users to solve this problem, C users are new Remote users with B user part of data access rights.

Create C users and authorize in b users. Since the demand is to implement a V_rpt_insInfo_tj table or view that users can successfully access B users, then we perform the creation of C users in b Users and give C users authorizes B user v_rpt_insinfo_tj table or view, of course, Connect authority must have of. C After the creating is completed, we can also view the permissions of the C user above, and transform DBLINK connecting to the C user in a user.

В этом учебном материале вы узнаете, как создавать и удалять синонимы (create and drop synonyms) в Oracle/PLSQL с синтаксисом и примерами.

Описание

В Oracle/PLSQL Synonym это альтернативное имя для таких объектов, как таблицы, представления, последовательности, хранимые процедуры и другие объекты базы данных.
Как правило, вы используете синонимы, когда предоставляете доступ к объекту из другой схемы, и вы не хотите, чтобы пользователи беспокоились о том, к какой схеме относится объект.

Create (or Replace) Synonym

Вы можете создать синоним, так чтобы пользователи не использовали префикс имени таблицы с именем схемы, при использовании таблицы в запросе.

Синтаксис

Синтаксис для создания синонимов Oracle/PLSQL:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
FOR [schema .] object_name [@ dblink];

OR REPLACE
Позволяет пересоздать синоним (если он уже существует), без необходимости выдавать команду DROP synonym.
PUBLIC
Это означает, что синоним является публичным и доступен для всех пользователей. Помните, что пользователь сначала должен иметь соответствующие привилегии для объекта использования синонима.
schema
Соответствующая схема. Если эта фраза опущена, Oracle предполагает, что вы имеете в виду вашу собственную схему.
object_name
Имя объекта, для которого вы создаете синоним. Это может быть один из следующих объектов:

• table
• view
• sequence
• stored procedure
• function
• package
• materialized view
• java class schema object
• user-defined object
• synonym

Пример

Рассмотрим пример того, как создать синоним в Oracle/PLSQL.

Например:

CREATE PUBLIC SYNONYM suppliers FOR app.suppliers;

Этот первый пример CREATE SYNONYM демонстрирует, как создать синоним с названием suppliers. Теперь, пользователи других схем могут ссылаться на таблицу с suppliers без префикса с именем схемы и именем таблицы.

Например:

Если это синоним уже существует, и вы хотите его пересоздать, то можете использовать OR REPLACE следующим образом:

CREATE OR REPLACE PUBLIC SYNONYM suppliers FOR app.suppliers;

Drop synonym

После того, как синоним был создан в Oracle, вам в какой-то момент понадобится его удалить.

Синтаксис

Синтаксис drop synonym в Oracle/PLSQL:

DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];

PUBLIC
Позволяет удалить public synonym. Если вы указали PUBLIC, то можете не указывать схему.
force
Это принудит Oracle удалить synonym, даже если он имеет зависимости. Это, вероятно, не очень хорошая идея, чтобы использовать force, поскольку это может привести к недействительности объектов Oracle.

Пример

Рассмотрим пример того, как удалить synonym в Oracle/PLSQL.

Например:

DROP PUBLIC SYNONYM suppliers;

Оператор DROP удалит синоним с названием suppliers, который мы определяли ранее.