Create DBlink (DATABASE LINK)

Script to create DBlink
================================

CREATE DATABASE LINK
INSYS_DBLINK   /*  DBLINK NAME */
CONNECT TO apps   /*  INSIS DATABASE USER NAME */
IDENTIFIED BY appsdev  /*  INSIS DATABASE PASSWORD */
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.101.1.1)(PORT = 1581)) )(CONNECT_DATA =(SERVICE_NAME = dev)))';


For testing DBlink

select * from tablename(any table in  database)@DBLINKNAME

eg:  select * from po_headers@DBLINK

Create / Drop / Check Database link or DB link

Two ways to create DB link in 11g Database

1. With TNS entry in tnsnames.ora

2. TNS entry given at the time of db link creation.

SQL> desc DBA_DB_LINKS

 Name                                      Null?                              Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL      VARCHAR2(30)

 DB_LINK                                   NOT NULL       VARCHAR2(128)

 USERNAME                                                                 VARCHAR2(30)

 HOST                                                                          VARCHAR2(2000)

 CREATED                                   NOT NULL     DATE

***FIND ALL DBLINKS created in database:

select * from dba_db_links;

Create DB link

Syntax:

Create public/private database_link linkname connect to username identified by Password using ‘Service Name’; i

Example :

1st method: Create DBLINK with tns entry made in tnsnames.ora

CREATE PUBLIC DATABASE LINK "TO_SPOT.WORLD" CONNECT TO CFS_SPOT IDENTIFIED BY CFS_Passwd_123 USING 'Spotprof.was.abc.com’;

Now make an entry in tnsnames.ora present in $TNS_ADMIN

SPTPS.WAS.ABC.COM

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = abc.xyz.was.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = SXXX

      (SERVER = DEDICATED)

    )

  )

2^nd Method:

conn / as sysdba issue this command on sqlprompt

CREATE public DATABASE LINK TO_SPOT CONNECT TO CFS_SPOT IDENTIFIED BY password USING

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))

(CONNECT_DATA=(SID = SXXX')));

Check DB link is working or not

conn / as sysdba or conn to sql and try the below one :

syntax : select * from dual@linkname;

Example

select * from dual@TO_SPOT;

DROP DBLINK:

DROP PUBLIC DATABASE LINK "TO_SPOT.WORLD";

Creating New Schema.
====================

CREATE USER HR_1
  IDENTIFIED BY VALUES ''
  DEFAULT TABLESPACE SYSTEM
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for HR_1
  GRANT DBA TO HR_1;
  ALTER USER HR_1 DEFAULT ROLE ALL;
  -- 8 System Privileges for HR_1
  GRANT CREATE TRIGGER TO HR_1;
  GRANT CREATE PROCEDURE TO HR_1;
  GRANT CREATE SYNONYM TO HR_1;
  GRANT UNLIMITED TABLESPACE TO HR_1;
  GRANT CREATE SESSION TO HR_1;
  GRANT CREATE SEQUENCE TO HR_1;
  GRANT CREATE VIEW TO HR_1;
  GRANT CREATE TABLE TO HR_1;