Create database link Oracle DBA

Blog Author


Published Date

30th September, 2020




  • Share this blog :


Create database link

Dba12 rajpal> create database link link1 connect

To Wilshire identified by Wilshire

Using tnsnames_dba12’;

Dba12 rajpal>    select * from tab@link1;

… the tables of Wilshire

Dba12 rajpal> select * from user_db_links;


Dba12 rajpal> select * from emp@link1;

Dba12 rajpal> insert into emp@link1 select * from emp@link1;

Dba12 rajpal> comit;                                       {only DML Txs not DDL Txs

Dba12 SYS > grant creat synonym to rajpal;

Dba12 rajpal> create synonym el1 for emp@link1

Dba12 raj> select * from el1;

Public database links:                                     ram{dba12’s another user

Dba12 SYS > grant create public database link to rajpal;

Dba12 rajpal> create public database link plink1 connect to Wilshire identified by Wilshire using tnsnames_dba12;

Dba12 rajpal> select * from user_db_links;



Dba12 ram> select * from user_db_links;

No rows selected

Dba12 ram> select*from all_db_links;


Dba12 ram> select * from emp@plink1;

….records of wilshire’s emp table.

On public links [only dml.drl, tcl Txs. But not DDL txs]

Dba12 SYS > select * from dba_db_links;

Dba12 SYS> select * from all_db_linnks;

SYS/rajpal> drop database link link1;

>drop public database link plink1; {only SYS [or] rajpal- [creator of the link]

SNAP HOTS (materialized views)

{cjq} co – ordinate job queue, the background process is responsible to reflect the newly inserted records on the master table

Dba12 SYS> grant create snapshot to rajpal;

Dba12 SYS> !

Dba12 ~ ] $ cd $ ORACLE _ HOME /dbs

Dba12 dbs] $ vi initrajpar.ora


[at a time 3 processes]


Dba12 dbs]$exit

Dba12 SYS > startup force

Dba12 SYS> conn rajpal/rajpal

Dba12 rajpal> select * from session_prives;

Dba12 rajpal> create materialized view mv1 refresh complete with rowed start with sysdate next

sypdate   +1 /(24*60*60) as select * from emp@link1;

materialized view created

dba12 rajpal>     save mv1.sql

dba12 rajpal>     select * from tab;


dba12 rajpal> select * from mv1;

materialized view with refresh fast option

it should contain or we should assign primory key in master table

log table should be created.

Dba15 wilshire> alter table emp add primary key (empno);

Dba15 wilshire> create materialized view log on emp:

Dba15 wilshire> select * from tab;

MLOG$ _emp                    These table contains only those records which are

UPA$ _ EMP                       Modified snapshots will receive data from these tables.

DBA12 rajpal>    create masterialized view mvf 1 refresh fast with row id start with SYSdate next sys date  +1/24*60*60) as select * from emp@link1;

Dba15 wilshire> insert into emp select * from emp;

Dba15 wilshire> select * from molg$_emp;

Dba15 wilshire> commit;  


Views regarding {SNAP SHOTS (MV)



For manual refresh  - package-

  physical structure of database Types   Startup  

To know the SCN # for any moment

Dbms_flashback_get_system_change_number [ a package]

Controlfile related views

V $ controlfile


SYS > select type from v $ controlfile_record_section;

SYS > select name from v$controlfile;

SYS> desc v$ database_incarnation;

SYS > select incarnation#, resetlogs_id from


Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

Multiplexing the control files

Make a copy of the control file in the new location physically.

SYS > shut immediate

Dba12 ~] $ cd / disk 3 / oradata/pal

Dba12 pal] $ cp cont. ctl / disk4/oradata/pal/cont2.ctl

Dba 12 pal ] $ cd $ ORACLE _ HOME / dbs

Dba12 dbs] $ vi initrajpar.ora

Control _ files = (i/disk3/oradata/pal/cont.ct;, ‘/disk4 / oradata/pal/cont2. Ctl’)


Dba12 dbs ] $ exit

SYS > startup

SYS> select name from v$controlfile;


/disk3 /oradata/pal/cont.ctl


Renaming the database

Dba12  `]$ sqlplus  ‘/as stsdba’

SYS > startup

SYS> alter database badkup controlfile to trace;


Dba12  ~] $ cd /disk4 /oradata/pal/udump

Dba12 udump ] $ ls – lrt                                 [longlisting reversely with time]

Dba12 udump] $ cp rajpar_ora_20457.trc    ~/control.trc

Dba12 udump]$ cd

Dba12 ~] $ vi control.trc

[will be in ASCII format]

In esc mode

Dgg                        removing upto beginning of the page  

Startup nomount  

Character setr US7ASCII ;  

D G  removing up to end of the page


Dba12 ~ ] $ exit

SYS > shut immediate


Dba12 ~]$ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar. Ora

Db_name= rajpal1

[max 8 chars}


Dba12 dbs]$ cd

Dba12 ~ ] $ vi control.trc

Startup nomount change to db-name                    [No] should be removed

Create controlfile set database “rajpal2”               RESETLOGS noarchivelogs


Dba12 ~ ] $ cd / disk3/oradata/pa;

Dba12 pal]$ cp cont.ctl                                   ~

Dba12 pal ] $ rm cont. ctl

Dba 12 pal}$ exit

SYS > @ control.trc

SYS> alter database open resetlogs;

SYS> select incarnation #, resetlogs-id from


SYS> select name from v$database;



For an in-depth knowledge, click on below

About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us