create db link

select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='SYSADM';

select * from dba_objects where object_type='DATABASE LINK';

select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='SYSADM_RO';

--DB link Creatd on CSDEV side

--CSUAT side need to create a SYSADM_RO account

--create a SYSADM_RO account

DROP USER SYSADM_RO CASCADE;

CREATE USER SYSADM_RO

  IDENTIFIED BY SYSADM_RO

  DEFAULT TABLESPACE PSDEFAULT

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

-- 2 Roles for SYSADM_RO 

GRANT CONNECT TO SYSADM_RO;

GRANT SELECT_CATALOG_ROLE TO SYSADM_RO;

ALTER USER SYSADM_RO DEFAULT ROLE ALL;

-- 4 System Privileges for SYSADM_RO 

GRANT ADVISOR TO SYSADM_RO;

GRANT CREATE PUBLIC DATABASE LINK TO SYSADM_RO;

GRANT CREATE SESSION TO SYSADM_RO;

GRANT SELECT ANY TABLE TO SYSADM_RO;

-- 21 Object Privileges for SYSADM_RO 

GRANT SELECT ON SYS.DBA_OBJECTS TO SYSADM_RO;

GRANT EXECUTE ON SYS.DBMS_ADVISOR TO SYSADM_RO;

GRANT SELECT ON SYS.V_$ACCESS TO SYSADM_RO;

GRANT SELECT ON SYS.V_$LOCK TO SYSADM_RO;

GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO SYSADM_RO;

GRANT SELECT ON SYS.V_$OPEN_CURSOR TO SYSADM_RO;

GRANT SELECT ON SYS.V_$PROCESS TO SYSADM_RO;

GRANT SELECT ON SYS.V_$ROLLNAME TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESSION TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESSION_EVENT TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESSION_LONGOPS TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESSION_WAIT TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESSTAT TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SESS_IO TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SQL TO SYSADM_RO;

GRANT SELECT ON SYS.V_$SQLTEXT_WITH_NEWLINES TO SYSADM_RO;

GRANT SELECT ON SYS.V_$STATNAME TO SYSADM_RO;

GRANT SELECT ON SYS.V_$TRANSACTION TO SYSADM_RO;

GRANT SELECT ON SYSADM.PSOPRDEFN TO SYSADM_RO;

GRANT SELECT ON SYSADM.PS_CS_LEARNING TO SYSADM_RO;

GRANT SELECT ON SYSADM.PS_CS_LRNG_ROL TO SYSADM_RO;

--alter system set global_names=false;

--create db link on CSDEV

--if target CSUAT global_names=false

drop public database link LINK_CSDEV2CSUAT;

create public database link "LINK_CSDEV2CSUAT"

connect to SYSADM_RO identified by "SYSADM_RO"

using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 168.192.11.16)(PORT = 1526)))(CONNECT_DATA =(SERVICE_NAME = CSUAT )))';

--if target CSUAT global_names=true, then the db link name must the same as service

drop public database link CSUAT;

create public database link "CSUAT"

connect to SYSADM_RO identified by "SYSADM_RO"

using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 168.192.11.16)(PORT = 1526)))(CONNECT_DATA =(SERVICE_NAME = CSUAT )))';

--test db link on CSDEV side

SELECT * FROM [email protected]_CSDEV2CSUAT;

--test db link on CSDEV side

SELECT * FROM [email protected];