Category Uncategorized

Grant Oracle user to access oracle table

We can grant all oracle objects from one user to another user by below package CREATE OR REPLACE PROCEDURE GRANT_ORACLE_TABLES(FROM_USER VARCHAR2,TO_USER VARCHAR2) IS BEGIN FOR tab IN (SELECT table_name FROM   all_tables WHERE  owner = FROM_USER) LOOP EXECUTE IMMEDIATE ‘GRANT ALL ON ‘||tab.table_name||’ TO ‘||TO_USER; END LOOP; END; If you want to grant only one objects […]

Db Links creation using TNS in oracle

1.select all db links from oracle using below statement select db_link from user_db_links; 2.db link creation statement is below CREATE DATABASE LINK prod_link CONNECT TO test_user IDENTIFIED BY test_pass USING ‘(DESCRIPTION=(ADDRESS_LIST=( ADDRESS=(PROTOCOL=TCP)(HOST=test_server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=test_db)))’ 3.Table data using db link select * from t@prod_link;   Enjoy!