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 SYSADM.PSOPRDEFN@LINK_CSDEV2CSUAT;

--test db link on CSDEV side
SELECT * FROM SYSADM.PSOPRDEFN@CSUAT;