Check if datapump session is running or not

On OS level type following:

ps -eaf | grep impdp
ps -eaf | grep expdp

ON database level:

set linesize 300
col JOB_NAME format A20
col DATAPUMP_SESSIONS format 999
col JOB_MODE format A10
col OPERATION format A10
select * from dba_datapump_jobs;

or

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
    s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
    from v$session s, v$process p, dba_datapump_sessions d
   where p.addr=s.paddr and s.saddr=d.saddr;

or

select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

Gather statistics on schema level

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

View POOL sizes in SGA

select pool, name , bytes from v$sgastat order by pool;

or

select pool, name, bytes from v$sgastat  select sum (value)/(1024*1024)
from v$parameter
where name in
  (
       'large_pool_size',
       'java_pool_size',
       'shared_pool_size'
   )
order by pool, name;

/*
select sum(bytes) from v$sgastat where pool = 'shared pool';

/* show parameter shared_pool
/*
select name,value
from v$parameter
where  upper(name) in ('DB_NAME','SGA_TARGET','DB_CACHE_SIZE','DB_BLOCK_SIZE','SGA_MAX_SIZE',
'SHARED_POOL_SIZE', 'LARGE_POO_SIZE');

How apply PSU patch on database level?

export ORACLE_HOME= [provive right path]
export ORACLE_SID=test
@$ORACLE_HOME/rdms/admin/catbundle.sql psu apply

then verify with following sql script:

Select
BUNDLE_SERIES,ACTION ,NAMESPACE,VERSION,ID,COMMENTS
from registry$history;

How to use opatch utility? or how to apply oracle patch

set OPatch patch first:

$> export PATH=$PATH:/ora02/app/oracle/product/11.2.0.3/OPatch

to check opatch version:

$> opatch version (Note: always read the readme file for patch, see which version of opatch you should use)

Then find out whether this patch already has been applied or not?

$> opatch lsinventory

Note: will give list off all the applied patch

then goto patch directory then type

$> cd <patch number>
$> opatch apply

How to rename table in Oracle database?

Method 1: Simple
rename {old_table_name} to {new_table_name}

Example:
rename CUSTOMER to CUSTOMER_BACKUP

Method 2:

alter table {old_table_name} rename to {new_table_name};

Example:
alter table CUSTOMER rename to CUSTOMER_BACKUP;

Unix/Linux — Find files that contain a string

To find the files that contain a string we can use:
find . -exec grep -l “string to find” {} \;
This starts the search from the current directory, looks for files that  contain the specified string, and then it prints their names.