To find out impdp/expdp OS pid

set pages 200 set linesize 300 col vs.spid for a10 col SID format 9999 col vp.spid format 99999 col vs.PROGRAM format a35 col USERNAME format a20 col OSUSER format a10 col STATUS format a10 col MACHINE format a20 select /*+ RULE */ vp.inst_id "SID", vp.spid "OS PID", vs.sid, vs.program, vs.username, vs.osuser, vs.logon_time, vs.status, vs.module,vs.machine from gv$process vp, gv$session vs where vs.module like 'Data%' and vp.inst_id = vs.inst_id and vs.paddr = vp.addr order by vp.inst_id, vs.sid;

As a normal user how to check SID

select sys_context('USERENV','SID') from dual;


(or)

select sid from v$mystat where rownum <=1;

Shell script for Tablespace alert via email

for sid in `ps -ef | grep pmon | awk '{print $8}' | egrep -i "ora_pmon" | sed 's/ora_pmon_//'`

do

DB=${sid}

ORAENV_ASK=NO

ORACLE_SID=$DB

. /u01/oracle/bin/oraenv > /dev/null 2>&1

ORAENV_ASK=YES

> output.log

> output1.log



sqlplus -s '/ as sysdba' << EOF
SET LINESIZE 130 PAGES 0 verify off heading off feedback off
COLUMN TABLESPACE FORMAT A25
spool output.log
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free"
from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs
where t.tablespace=fs.tablespace and round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) > 90
and t.tablespace not in ('SYSTEM','SYSAUX')
order by t.tablespace;
spool off;

SET LINESIZE 130 PAGES 10 heading on feedback on
spool output1.log
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free"
from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs
where t.tablespace=fs.tablespace and round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) > 90
and t.tablespace not in ('SYSTEM','SYSAUX')
order by t.tablespace;
spool off;
exit
EOF
done

if [ `cat output.log | wc -c` -gt 0 ] ; then mailx -s "$DB@`hostname` DB TS info" email@com < output1.log ; fi

Getting TNS strings from tnsnames.ora file in Linux

grep '^[A-Za-z]' tnsnames.ora | sed 's/=.*//;s/,.*//'

DBMS_WORKLOAD_REPOSITORY

MODIFY_SNAPSHOT_SETTINGS Procedure

This procedure controls two aspects of snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload Repository.

Syntax:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS((
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);

Parameters:

Parameter Description

retention:
The new retention time (in minutes). The specified value must be in the range of 1 day to 100 years. If ZERO is specified, the maximum value of 100 years will be used. If NULL is specified, the old value for retention is preserved.

interval:
The new interval setting between each snapshot, in units of minutes. The specified value must be in the range between 10 minutes to 1 year. If ZERO is specified, the maximum value of 1 year will be used. If NULL is specified, the current value is preserved.

dbid:
The database id (default to local DBID.


Examples:

This example changes the interval setting to one hour and the retention setting to two weeks for the local database:

execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 20160);


If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

(or)

 select SNAP_INTERVAL,RETENTION from DBA_HIST_WR_CONTROL;
(from Oracle 11g)

Ex:
Interval > 60 min
Keep time > 30 days.

exec dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 43200);