Group Discounts available for 3+ students and Corporate Clients

Create database link Oracle DBA

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 ohould 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-







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



Multiplexing the control files

Make the copy of the control file in 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;

SYS> !

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

SYS> !

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;




“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on Create database link Oracle DBA"

Leave a Message

Your email address will not be published. Required fields are marked *


Please Enter Your Details and Query.
Three + 6