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

DBA_OBJECTS and Calculate the Size of Oracle Objects and Database Files

# How to calculate the Size of Database Datafiles, Tablespace and Temp-Tablespace

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)" from(select(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)", (select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)" from dual);

select sum(bytes/1024/1024/1024) from dba_data_files;

select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual;

select sum(bytes)/1024/1024/1024 temp_size from dba_temp_files;

select TABLESPACE_NAME, sum(bytes)/1024/1024 "DB Size in MB" from dba_data_files group by tablespace_name;

select sum(bytes / (1024*1024)) "DB Size in MB" from dba_data_files where tablespace_name='RECONAREA';

select sum(bytes / (1024*1024)) "Temp Size in MB" from dba_temp_files;

# How to calculate the size of tables

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='SCOTT' and segment_name='EMP_TABLE' group by owner,segment_name;

select sum(bytes)/1024/1024/1024 as size_in_gig,segment_name from dba_segments where segment_name in ('SS7DATA','MSCDATA') and owner='RECON' group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='TRACK_OWNER' and segment_name='PR_ADDTL_DATA' group by owner,segment_name;

# DBA_OBJECTS Queries

select OWNER, object_type, count(object_name) from dba_objects where OWNER in ('USER1','USER2','USER3') group by object_type, owner order by owner;

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type not in ('TABLE','DATABASE LINK');

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type in ('MATERIALIZED VIEW');

select object_type, count(object_name) as Count from dba_objects group by object_type order by cc;

select object_type,status,count(*) from dba_objects where owner='REPOSITORY1' group by object_type, status order by object_type, status;

select owner,object_name,status from dba_objects where status= 'VALID' and owner in ('REPOSITORY1','EMBARC_ETL','EMBARC_SAS');

select owner,object_name,object_type, status from dba_objects where owner ='GPAS_STAGING' and object_name like '%GM_GPAS_REFRESH_ALL_MV%';

# Calculate the Size of TableSpaces

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

   FROM dba_data_files

   GROUP BY tablespace_name) df,

  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

    FROM dba_free_space

    GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

Manage Oracle Tablespace, Datafile and Tempfile.

Use different views for viewing information about temp files and Datafiles with V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

Note: Remember we can extend one Datafile up it Max limit around 32GB only, It depends on a block size of oracle.
Default Block Size: 8192(Byte) Means 8Kb, please run this query to cross verify:

SQL> Select (maxblocks*8192)/1024/1024/1024 Size_GB from dba_data_files where rownum=1;


# Tablespace Creation and Resizing Datafile
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';

CREATE TABLESPACE tb_name DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 20G;
ALTER TABLESPACE tb_name ADD DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 10G;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' RESIZE 24G;


# Temp Tablespace Creation and Managing its Datafile
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement.

SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP';
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/mount2/directory1/directory1/lmtemp01.dbf' SIZE 15G AUTOEXTEND ON;
ALTER TABLESPACE lmtemp ADD TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' SIZE 10G REUSE;
ALTER DATABASE TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' RESIZE 26G;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mount2/directory1/directory1/lmtemp03.dbf' SIZE 512M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;

# Drop Tablespace Datafile along with data.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE lmtemp DROP TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf';
ALTER TABLESPACE tb_name DROP DATAFILE '/mount1/directory1/directory1/my_data_01.dbf';

Create, Modify, Remove Oracle Scheduler Jobs and Queues.

# Some useful Queries to check the status of Jobs and Queues.
SQL> select job, schema_user, last_date, next_date, interval,failures,broken from dba_jobs where job=613;
SQL> SELECT r.sid, r.job, r.this_date, r.this_sec FROM dba_jobs_running r,dba_jobs j WHERE r.job = j.job;
SQL> select job,LOG_USER,SCHEMA_USER,NEXT_DATE,NEXT_SEC,INTERVAL,FAILURES,BROKEN from dba_jobs where job in (243,244);

SQL> select ACTUAL_START_DATE from ALL_SCHEDULER_JOB_RUN_DETAILS order by ACTUAL_START_DATE desc;
SQL> select OWNER,JOB_NAME, STATUS from ALL_SCHEDULER_JOB_RUN_DETAILS where owner != 'SYS';

SQL> select OWNER,JOB_NAME from DBA_SCHEDULER_JOBS where owner != 'SYS';
SQL> select * from DBA_QUEUE_SCHEDULES where qname='SBL_PTCL_IN_Q';
SQL> select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='SBL_PTCL_IN_Q';


# Create, run and drop of Scheduler Job with example
DECLARE
  Y NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => Y
   ,what      => 'begin
 ctms_operation.update_site_score;
 CTMS_OPERATION.generate_xml;
 end;'
   ,next_date => SYSDATE
   ,interval  => 'NEXT_DAY(TRUNC(SYSDATE),''SATURDAY'')+ 5/24'
   ,no_parse  => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(Y));
 COMMIT;
END;
/
SQL> select to_char(NEXT_DAY(TRUNC(SYSDATE),'SATURDAY')+ 5/24,'DD-MM-YYYY:HH:MI:SS') from dual;


# Run, Remove, Broken Operation on Job
SQL> exec dbms_job.run(613);
SQL> exec dbms_job.remove(243);
SQL> exec dbms_job.broken(314,FALSE);
SQL> exec dbms_ijob.broken(27,true);

SQL> DBMS_SCHEDULER.DROP_JOB (job_name => 'STATISTICS_COLUMNS_JOB');
SQL> begin dbms_aqadm.start_queue (queue_name => 'SBL_CONTACT_Q', enqueue => TRUE , dequeue => FALSE); end;
SQL> begin dbms_aqadm.start_queue (queue_name => 'SBL_CONTACT_Q', enqueue => FALSE, dequeue => TRUE); end;

# Please visit Below link to know more with examples.

Link1:  http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml
Link2:  http://www.mandsconsulting.com/oracle-dbms_scheduler-vs-dbms_job-create-run-monitor-remove

How to Remove/Wipe long running KILLED and INACTIVE session in Oracle Database

SQL>
SQL> select SID, SERIAL#, USER#, USERNAME,SCHEMANAME,SCHEMA#, STATUS,PROCESS,OSUSER from v$session where USERNAME='SYS';

       SID    SERIAL#      USER# USERNAME             SCHEMANAME                        SCHEMA# STATUS   PROCESS                  OSUSER
---------- ---------- ---------- -------------------- ------------------------------ ---------- -------- ------------------------ ------------------------------
        10       4827          0 SYS                  SYS                                     0 INACTIVE 23017                    oracle
       209      33035          0 SYS                  SYS                                     0 ACTIVE   13138                    oracle
       226       5355          0 SYS                  TRACK_OWNER                            78 KILLED   8499                     oracle

SQL> select s.username,s.sid,s.serial#,p.spid,s.lockwait,s.logon_time
from v$session s, v$process p
where s.paddr = p.addr and s.USERNAME='SYS';  2    3

USERNAME                    SID    SERIAL# SPID                     LOCKWAIT         LOGON_TIME
-------------------- ---------- ---------- ------------------------ ---------------- ------------------
SYS                         226       5355 8500                                      24-FEB-16
SYS                          10       4827 23018                                     25-FEB-16
SYS                         209      33035 13139                                     25-FEB-16
  
SQL> !ps -aef|grep 8499
oracle    8499  8213  0 Feb24 ?        00:00:00 sqlplus   as sysdba
oracle    8500  8499  0 Feb24 ?        00:04:03 oracleTW1D (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14627 13138  0 13:25 pts/8    00:00:00 /bin/ksh -c ps -aef|grep 8499
oracle   14629 14627  0 13:25 pts/8    00:00:00 grep 8499

SQL> !ps -aef|grep 8500
oracle    8500  8499  0 Feb24 ?        00:04:03 oracleTW1D (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14631 13138  0 13:25 pts/8    00:00:00 /bin/ksh -c ps -aef|grep 8500
oracle   14633 14631  0 13:25 pts/8    00:00:00 grep 8500
oracle   32079  8500  0 Feb24 ?        00:00:02 /u001/app/oracle/product/11.2.0.4/ctx/bin/ctxhx /tmp/drgibUskeUq /tmp/drgitee7wti ASCII8 utf8 H NOMETA 120 HEURISTIC FORMAT NOPDFROTATE /tmp/drgitmp4I7029 ENCRYPTED

SQL> !kill -9 8500  8499

SQL> !kill -9 32079

SQL> !kill -9 23018 23017

SQL> exit
.
.
.
.
SQL>  select SID, SERIAL#, USER#, USERNAME,SCHEMANAME,SCHEMA#, STATUS,PROCESS,OSUSER from v$session where USERNAME='SYS';

       SID    SERIAL#      USER# USERNAME             SCHEMANAME                        SCHEMA# STATUS   PROCESS                  OSUSER
---------- ---------- ---------- -------------------- ------------------------------ ---------- -------- ------------------------ ------------------------------        
  209      33039          0 SYS                  SYS                                     0 ACTIVE   15044                    oracle

SQL>



So, now as you can see there is no KILLED/INACTIVE Session. Please use below Sql Queries:

1)To Find KILLED/INACTIVE Session Process to kill use PPID & PID Column "kill -9 31105 31103"
col username format a20
col SCHEMANAME format a20
col status format a20
col OSUSER format a20
col PPID format a10
col PID format a10

select s.OSUSER,s.username,s.SCHEMANAME,s.sid,s.serial#,p.spid as PPID,s.process as PID,s.logon_time,s.STATUS
from v$session s, v$process p 
where s.paddr = p.addr and s.USERNAME='SYS';   


OSUSER               USERNAME             SCHEMANAME                  SID    SERIAL# PPID       PID        LOGON_TIME         STATUS
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ------------------ --------------------
oracle               SYS                  SYS                           6      45904 31105      31103      26-FEB-16          ACTIVE

SQL>

2)To Generate oracle statemt to kill session.
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='USER_NAME';

How to Perform Oracle Data Pump with Different-2 Scenario on 11g

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later.Oracle Data Pump is made up of three distinct parts:

-The command-line clients, expdp and impdp
-The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
-The DBMS_METADATA PL/SQL package (also known as the Metadata API)

For Monitoring Job Status of Data Pump, Please select a query on DBA_DATAPUMP_JOBS,  USER_DATAPUMP_JOBS, or DBA_DATAPUMP_SESSIONS views. V$SESSION_LONGOPS dynamic performance view indicating the job progress (in megabytes of table data transferred). The entry contains the estimated transfer size and is periodically updated to reflect the actual amount of data transferred.

## How to estimate the size of Database
expdp FULL=Y ESTIMATE_ONLY=Y

## Perform Full Database Export/Import (We can perform any Import Operation if we have Full Database Dump.)
expdp system/password DIRECTORY=DIR_NAME DUMPFILE=Dump_File_Name.dmp LOGFILE=log_file.log FULL=Y
impdp system/password DIRECTORY=DIR_NAME DUMPFILE=Dump_File_Name.dmp LOGFILE=Imp_log_file.log FULL=Y

## On Specific Schema Export/Import.
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=user1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log SCHEMAS=user1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log SCHEMAS=user1 REMAP_SCHEMA=user1:user2 -Use Remap if username is Different

## Schema Table Export/Import from User1.table  to User2.
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log TABLES=user1.table1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1 REMAP_SCHEMA=user1:user2 

## Perform Export/Import on Quey
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log TABLES=user1.table1 QUERY=user1.table1:”WHERE Sex='Female'”
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1 REMAP_TABLE=user1.table1:table2  QUERY=user1.table1:”WHERE name like '%R%'”

Example: 
QUERY=user1.table1:"where DATE > '01-DEC-2013'"
QUERY=user1.table1:"where Department='IT' AND Location='Bangalore'"

## Perform Exclude/Include import/Export
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log FULL=Y EXCLUDE=TABLE:"IN('TABLE1','TABLE2')"

impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=user1 REMAP_SCHEMA=user1:user2 INCLUDE=TABLE:"IN('TABLE1','TABLE2')"

impdp DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=User1 INCLUDE=INDEX SQLFILE=EXP_DIR:domain_idx.sql -To Generate DDL from from dump.

Example:
INCLUDE=TABLE:" like '%TAB%'"

## Take Import/Export Using Parameter (.par) File
vi imp_schema_parfile.par

directory=EXP_DIR
dumpfile=Dump_file.dmp
logfile=Dump_file.log
schemas=Rajesh
Version=COMPATIBLE
remap_schema=rajesh:rahul 
remap_tablespace=VGSM_STATIC:SM_STATIC,VGSM_INDEX:SM_STATIC_I
table_exists_action=truncate
PARALLEL=4 
tables=rajesh.table1, rajesh.table2, rajesh.table3
IGNORE=Y
:wq

impdp parfile=imp_schema_parfile.par
In the same way you can use export as well.

## Take Import/Export Using "NETWORK_LINK "Parameter.
Network_link parameter is very useful, when you've shortage of space in File system while performing import/export. with help of it you can take export in different server as well and you can import the same from there to target server.

Example:
Create a Database link on server(source/target) where you are performing export/import.

CREATE DATABASE LINK SYS_DBLNK CONNECT TO system IDENTIFIED BY password USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=servername)(Port=1521)))(CONNECT_DATA=(SID=REMORTE)))';
OR
CREATE DATABASE LINK SYS_DBLNK CONNECT TO system IDENTIFIED BY password USING 'REMORTE';

nohup expdp system/password directory=EXP_DIR dumpfile=file.dmp logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -Taking .dmp file in different location
                   
nohup impdp system/password directory=EXP_DIR dumpfile=file.dmp logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -Import .dmp file to target DB from .dmp file location
                   
nohup impdp system/password directory=EXP_DIR logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -import data without .dmp file


## How to Run a job in background via shell script using par file or a Single line
nohup impdp '"/ as sysdba"' parfile=imp_schema_parfile.par &
nohup expdp '"/ as sysdba"' parfile=exp_schema_parfile.par &

OR
vi impdp_schema_shell.sh
echo "File import Starting at: `date`"
impdp '"/ as sysdba"' parfile=imp_schema_parfile.par
echo "File import Finish at: `date`"
:wq

nohup sh impdp_schema_shell.sh > impdp_schema_shell.log &


## Check status for running job both side from sql and file level
Find the Job name from import/export .log file location or also you can specify your own job name with JOB_NAME parameter while running job. And to for find a currently running job follow below data dictionary view as well.

SQL> select * from dba_datapump_jobs;
OR 
expdp "/ as sysdba" attach='' -At prompt type "status"
impdp "/ as sysdba" attach='' -At prompt type "status"


## How to kill running export/import job
expdp "/ as sysdba" attach='' -At prompt type "kill_job" Enter "Yes"
impdp "/ as sysdba" attach='' -At prompt type "kill_job" Enter "Yes"


## Create Datapump Directory in Oracle
CREATE DIRECTORY exp_dir AS '/path1/path2/';
CREATE OR REPLACE DIRECTORY exp_dir AS '/path1/path2/';
Grant read,write on DIRECTORY exp_dir to username|public;


Some Commonly Used Parameters are:
-COMPRESSION: Specifies whether to compress metadata before writing to the dump file set
-CONTENT: Enables you to filter what Export unloads: data only, metadata only, or both.
-DIRECTORY: Specifies the default location to which Export can write the dump file set and the log file
-DUMPFILE: Specifies the names, and optionally, the directory objects of dump files for an export job.
-LOGFILE: Specifies the name, and optionally, a directory, for the log file of the export job.
-ESTIMATE_ONLY: Instructs Export to estimate the space that a job would consume, without actually performing the export operation.
-EXCLUDE: Enables you to filter the metadata that is exported by specifying objects and object types that you want excluded from the export operation.
-FULL: Specifies that you want to perform a full database mode export.
-INCLUDE: Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.
-PARFILE: Specifies the name of an export parameter file.
-QUERY: Enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table
-SCHEMAS: Specifies that you want to perform a schema-mode export. This is the default mode for Export.
-TABLES: Specifies that you want to perform a table-mode export.
-REMAP_SCHEMA: Loads all objects from the source schema into a target schema.
-REMAP_TABLE: Allows you to rename tables during an import operation performed with the transportable method (REMAP_TABLE=hr.employees:emps)
-REMAP_TABLESPACE: Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.
-TABLE_EXISTS_ACTION: Tells Import what to do if the table it is trying to create already exists.