CODEWITHSUNDEEP
oracle-databaseddlmaterialized-views
no_name
no_name

Reputation: 21

how to create materialized view to connect two different oracle database tables

i need to create a mview to get the data from the remote database to destination database using refresh command.

this is my sql query to create mview:

CREATE DATABASE LINK SI_DB
    CONNECT TO SYSTEM IDENTIFIED BY password
    USING 'SI_DEV';

this is my tns.ora file:

SI_DEV=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = servidor)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MSQL)
)
(HS=OK)
)

I'm getting the following error:

ORA-12154: TNS:could not resolve the connect identifier specified

I don't know how to create a mview for remote target.

Do we need to put our destination or remote database into the .or file?

Can anyone please help me to solve this problem?

Upvotes: 2

Views: 3050

Answers (1)

user330315
user330315

Reputation:

The tnsnames.ora on the server where you create the DBLink needs to contain the connect information to the remote Oracle instance. You can also create a DBLink without changing tnsnames.ora on that server by providing the connection descriptor when creating the dblink:

CREATE PUBLIC DATABASE LINK SI_DB
  CONNECT TO some_user
  IDENTIFIED BY some_password
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = servidor)(PORT = 1521)) ) (CONNECT_DATA = (SID = MSQL) ) (HS=OK))';

Note that creating a DBLink with the system user to enable materialized views to a regular user is a bad idea.

Once you have the DBLink you can create the mview using: 

create materialize view foobar
as
select *
from the_table@si_db;

Note that the_table references a table in the schema of the user that is specified in the DBLink. That's one of the reasons you should not use the system account for the DBLink. If the table is not in the schema of the DBLink's user, you need to qualify the table name:

create materialize view foobar
as
select *
from the_user.the_table@si_db;

Upvotes: 2

Related Questions