Create Database Link for User without Knowing its Credential via SYS User

# Basic way to Create and Drop DB LINK

CREATE PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD CONNECT TO ETMFMT IDENTIFIED BY ETM0107FMT USING 'ETMFMIG.WORLD';

CREATE PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD CONNECT TO ETMFMT IDENTIFIED BY ETM0107FMT USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP.world)(PROTOCOL=TCP)(Host=usabhbmsvhz303.net.bms.com)(Port=1521)))(CONNECT_DATA=(SID=ETMFMIG)))';

DROP PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD;

DROP DATABASE LINK EDM_REPORTING_DB_LINK.WORLD;

# Granting Privileges

GRANT CREATE DATABASE LINK TO SCOTT;

GRANT DROP PUBLIC DATABASE LINK TO SCOTT;

GRANT DROP DATABASE LINK TO SCOTT;

REVOKE CREATE PUBLIC DATABASE LINK FROM SCOTT;

# Create DB LINK for another user via Oracle Procedure.

SQL> CREATE or replace PROCEDURE scott.create_db_link AS

BEGIN

EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';

END create_db_link;

2 3 4 5

6 /

Procedure created.

SQL> show user

USER is "SYS"

SQL> exec scott.create_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

OWNER DB_LINK USERNAME HOST CREATED

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

SCOTT LINK1 SCOTT testdb 04-NOV-11

SQL> drop database link scott.LINK1;

drop database link scott.LINK1

*

ERROR at line 1:

ORA-02024: database link not found

SQL> CREATE PROCEDURE scott.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link LINK1';

END drop_db_link; 2 3 4

5 /

Procedure created.

SQL> exec scott.drop_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

no rows selected

-- drop procedure scott.crt_db_link;

-- drop procedure scott.drop_db_link

Oracle User Create, Grant Privileges, Create DML and DCL statement from Select Query

# User Creation
create user AMPRD identified by az7bc2
default tablespace data_ts
quota 100m on test_ts
quota 500k on data_ts
temporary tablespace temp_ts
profile clerk;

# Grant Priviliges to User
grant create session to AMPRD;
grant connect,resource to AMPRD;
alter user AMPRD default tablespace sm_static;
alter user AMPRD quota unlimited on tb_name;
alter user AMPRD quota 100m on tb_name;
grant unlimited tablespace to AMPRD;

# Select Query to Identify useful Information about User
select username,default_tablespace,account_status from DBA_USERS where username ='AMPRD';
select grantee,granted_role from DBA_ROLE_PRIVS where grantee='AMPRD';
select role from DBA_ROLES where role like '%HPXR%';
select owner,object_type,object_name from DBA_OBJECTS where object_name like '%TST%';
select grantee,privilege from DBA_SYS_PRIVS where grantee = upper('FACT');
select owner,grantee,table_name,grantor,privilege from DBA_TAB_PRIVS where grantee='AMPRD';

# Create Drop DML Statements with select query from dual

select 'drop '||object_type ||' '||owner||'.'||object_name||' CASCADE CONSTRAINTS;' from DBA_OBJECTS where owner='AMPRD' and object_type='TABLE';
select 'drop '||object_type ||' '||owner||'.'||object_name||';' from DBA_OBJECTS where owner='AMPRD' and object_type !='TABLE';

# Create Grant DCL Statements to Replicate user with existing user Privileges
select 'grant '||privilege||' to AMPRD;' from DBA_SYS_PRIVS where grantee = upper('FACT');
select 'grant '||privilege||' on '||owner||'.'||table_name||' to AMPRD;' from DBA_TAB_PRIVS where grantee = upper('FACT');

Oracle Session and Its Properties

# What is the difference between v$, gv$, x$ v_$ and gv_$ ?
v$ - these objects are synonyms not view
gv$ - these objects are also synonyms where g stand for "global" it contains some extra columns for RAC information.
v_$ - these are the actual views where v$ synonyms have created from it.
gv_$ - these are also same actual view which contains more information about RAC
x_$ - there are the actual X$ structure Tables which are mapped to v$ views


# V$ View which stores all the information of Dynamic view
v$fixed_view_definition
v$fixed_table

# Set a session to perform activity behalf of another user if you are login as sys/system
ALTER SESSION SET CURRENT_SCHEMA=WIRES;


# Find out your session information 
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL; -- SYS_CONTEXT funtion have multiple parameter as our requirment
SELECT USERENV('SID') "SID" FROM DUAL;
select p.pid,p.spid,p.pname,p.username,p.serial#, p.program,s.username,s.logon_time from v$process p, v$session s where s.paddr=p.addr;
select sid, serial# from v$session s where paddr = (select addr from v$process where spid = 23048);

# To Find Current running statement of Session
set pagesize 50000
set linesize 30000
set long 500000
set head off

select sesion.sid, sql_text from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address;
and sesion.sql_id = sqltext.sql_id;
and sesion.username is not null order by sqltext.piece;

select a.sid, a.username,b.sql_id, b.sql_fulltext 
from v$session a, v$sql b 
where a.sql_id = b.sql_id 
and a.status = 'ACTIVE' and a.username = 'KUMARA45';

select s.username USername, substr(sa.sql_text,1,54) TEST_DATA
from v$process p, v$session s, v$sqlarea sa
where p.addr=s.paddr
and s.username = 'SIEBEL';
and s.sql_address=sa.address(+)
and sa.sql_text like '%DEL%'
and s.sql_hash_value=sa.hash_value(+);


# Find out the Locked Session Details of user
select substr(a.os_user_name,1,15) "OS_User"
,substr(a.oracle_username,1,8) "DB_User"
,substr(b.owner,1,8) "Schema"
,substr(b.object_name,1,30) "Object_Name"
,substr(b.object_type,1,10) "Object_Type"
,substr(c.segment_name,1,15) "RBS"
,substr(d.used_urec,1,12) "#_of-UndoRecords"
,e.sid,e.serial#
from v$locked_object a
,dba_objects b
,dba_rollback_segs c
,v$transaction d
,v$session e
where a.object_id =  b.object_id
and a.xidusn  = c.segment_id
and a.xidusn  = d.xidusn
and a.xidslot = d.xidslot
and d.addr    = e.taddr;

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue';

# Find out the long running session
set lines 200
col "Index Operation" for a60 trunc
col "ETA Mins" format 999.99
col "Runtime Mins" format 999.99

select sess.sid as "Session ID", sql.sql_text as "Index Operation",
longops.totalwork, longops.sofar,
longops.elapsed_seconds/60 as "Runtime Mins",
longops.time_remaining/60 as "ETA Mins"
from v$session sess, v$sql sql, v$session_longops longops
where
sess.sid=longops.sid
and sess.sql_address = sql.address
and sess.sql_address = longops.sql_address
and sess.status  = 'ACTIVE'
and longops.totalwork > longops.sofar
and upper(sql.sql_text) like '%INDEX%'
order by 3, 4;

Oracle SQL*Loader Tips and Tricks

# Oracle SqlLoader from Command Line

sqlldr system/pump5k1n control=LOV_LOAD.ctl data=LOV_Value.txt LOG=LOV_Value.log BAD=LOV_Value.bad

sqlldr system/pump5k1n control=LOV_TYPE_LOAD.ctl data=New_LOV_Type.txt LOG=New_LOV_Type.log BAD=New_LOV_Type.bad