DBA SCRIPTS:
=============
A Shell Script To Take RMAN Cold/Hot and Export Backup:
======================================================
#!/bin/bash
ORACLE_SID=SHAAN;export ORACLE_SID
echo $ORACLE_SID
echo “Which kind of backup you want to take?”
echo “1) RMAN COLD BACKUP”
echo “2) RMAN HOT BACKUP”
echo “3) LOGICAL OR EXPORT BACKUP”
echo “Enter your option”
read option
while [ $option -gt 3 ]||[ $option -le 0 ]
do
echo “Please Enter the correct option”
read option
done
case $option in
1|2) echo “You are taking RMAN backup of shaan Database”
rman target sys/oracle@shaan @/ORACLE/RMAN_BACKUP/rman_backup_$option.txt;exit;;
3) echo “You are taking export backup of shaan Database”
exp system/oracle file=/ORACLE/EXPORT_BACKUP/exp_dat.dmp log=/oracle/export_backup/exp_dat.log full=y;
exit;;
esac
exit
# Now Create two simple text file for Rman Cold and Hot backup.
# The above script can call anyone of the following RMAN script depending on the option 1-cold or 2-hot backup.
rman_backup_1.txt:
run
{
shutdown immediate;
startup mount;
allocate channel d1 type disk FORMAT "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db";
backup full format "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db" (database);
copy current controlfile to 'H:\Cold_Backup\COLD_BACKUP_Control.db';
release channel d1;
release channel d1;
alter database open;
}
rman_backup_2.txt:
run {
allocate channel d1 type disk FORMAT "H:\Hot_Backup\Hot_BACKUP_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\Hot_Backup\Hot_BACKUP_%T_%d-%s_%p.db";
sql 'alter system archive log current';
backup
tag Catalog_Full_Rman
filesperset 10
format "H:\Hot_Backup\Hot_Backup_%T_FULL_%d-%s_%p.db"
database archivelog all;
release channel d1;
release channel d1;
}
Monitor RMAN status, while a backup is running:
======================================
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1)
/
Archlog Files Backed up during past 24 Hours:
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')
/
RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/
Script to List Completed and Pending RMAN backup of last 24-hours:
=================================================
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#;
Datafiles Backed up during past 24 Hours
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1);
Archlog Files Backed up during past 24 Hours
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO');
RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore';
Script: Recompile Invalid Objects:
===========================
SELECT DISTINCT 'alter session set current_schema=' || owner || ';' cmd, owner, 1 order_col, NULL object_name
FROM dba_objects
WHERE status = 'INVALID' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
UNION
SELECT 'ALTER ' || DECODE ( object_type, 'PACKAGE BODY', 'PACKAGE', object_type ) || ' ' || owner || '.' || object_name || ' COMPILE' ||
DECODE ( object_type, 'PACKAGE BODY', ' BODY', '' ) || ';' cmd, owner, 2 order_col, object_name
FROM dba_objects outer
WHERE status = 'INVALID' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
AND ( object_type <> 'PACKAGE BODY' OR NOT EXISTS ( SELECT NULL
FROM dba_objects
WHERE owner = outer.owner AND object_name = outer.object_name
AND object_type = 'PACKAGE' AND status = 'INVALID')
)
ORDER BY 2, 3, 4
/
Script: To Monitor Tablespaces/datafiles
Important Note: If any of the script in this blog is not running then please re-type it or try to retype quotation, command and braces (may be format is changed). I am using toad so if you are using SQL then try to fix column length before exectunig the script (if any).
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To check Tablespace free space:
========================
SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
To check Tablespace by datafile:
========================
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE
group by tablespace_name, file_id;
To Check Tablespace used and free space:
==============================
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;
Tablespace used and Free space in MB:
============================
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
--or--
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 order by t.tablespace;
List all Tablespaces with free space < 10% or full space> 90%:
=============================================
set pagesize 300
set linesize 100
column tablespace_name format a15 heading ‘Tablespace’
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading ‘Total Size(Mb)’
column Tot_Free format 999,999,999 heading ‘Total Free(Kb)’
column Pct_Free format 999.99 heading ‘% Free’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
column Min_Add format 999,999,999 heading ‘Min space add (MB)’
ttitle center ‘Tablespaces With Less Than 10% Free Space’ skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
To check Datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
To check Used free space in Temporary Tablespace:
======================================
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM V$temp_space_header GROUP BY tablespace_name;
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Who is using which UNDO or TEMP segment?
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
/
Who is using the Temp Segment?:
=========================
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Total Size and Free Size of Database:
select a.data_size+b.temp_size+c.redo_size "Total_Size (GB)"
from ( select sum(bytes/1024/1024/1024) data_size
from dba_data_files ) a,
( select nvl(sum(bytes/1024/1024/1024),0) temp_size
from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size
from sys.v_$log ) c;
--Total Size of Database with free space
col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size"
, round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/
To find used space of database:
========================
SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
To Check Default Tablespace and Temporary Tablespace:
=========================================
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
To know default and Temporary Tablespace for a User:
=========================================
select username,temporary_tablespace,default_tablespace from dba_users where username='STARREP';
To know Default Tablespace for All User:
select default_tablespace,temporary_tablespace,username from dba_users;
IO status of all of the datafiles in database:
===============================
WITH total_io AS
(SELECT SUM (phyrds + phywrts) sum_io
FROM v$filestat)
SELECT NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
WHERE a.file# = b.file#
ORDER BY a.file#
/
Script: RMAN Hot Backup on Linux Environment:
=====================================
# This script can be used to take a hot backup using RMAN.
# Some of the variables in the initial section of the script need to have valid values
# Set the catalog to the catalog database
# This script will also mail errors encountered in the backup process
# =============================
#!/bin/sh
export ORACLE_SID=...
export ORACLE_HOME=....
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
DBA="shahid.ahmed@al-sadhan.com"
INST=`hostname`
TODAY=`date +%d%b`
BACKUP_DIR=/u01/backup/
LOG_DIR=/u01/backup/logs
LOGFILE=$LOG_DIR/rman_backup_${ORACLE_SID}_${TODAY}.log
ERRFILE=$LOG_DIR/err_status.log
$ORACLE_HOME/bin/rman <<EOF > ${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
connect target/
connect catalog catalog/catalog@rman
run {
allocate channel t1 TYPE DISK;
setlimit channel t1 kbytes 2048000;
allocate channel t2 TYPE DISK;
setlimit channel t2 kbytes 2048000;
allocate channel t3 TYPE DISK;
setlimit channel t3 kbytes 2048000;
allocate channel t4 TYPE DISK;
setlimit channel t4 kbytes 2048000;
backup full tag BACKUP_FULL_${ORACLE_SID} format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.dbf' database;
backup current controlfile format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.ctl';
sql 'alter system archive log current';
backup archivelog all format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.arc'
archivelog until time 'sysdate-7' delete input;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
EOF
egrep "RMAN-|ORA-" $LOGFILE > $ERRFILE
if [ -s $ERRFILE ]; then
mail -s "MTD PROD chfoh-ls-ipr RMAN Backup FAILED $ORACLE_SID" $DBA <
${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
else
mail -s "MTD PROD chfoh-ls-ipr RMAN Backup SUCCESS $ORACLE_SID" $DBA <
${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
fi
session wait events:
==============
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT s.inst_id,
NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC;
Lists all locked objects for whole RAC
============================
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
Displays information on all database sessions for whole RAC.
===========================================
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;
SET PAGESIZE 14
Displays information on all long operations for whole RAC.
=========================================
SET LINESIZE 200
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;
Locked Objects:
============
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Displays information on all long operations.
================================
SET PAGESIZE 14
SET VERIFY ON
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
Displays information on all database session waits.
======================================
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
number of daily concurrent requests:
=============================
SELECT trunc(REQUESTED_START_DATE), count(*)
FROM FND_CONCURRENT_REQUESTS
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate
group by rollup(trunc(REQUESTED_START_DATE)) ;
DB size with Tablespace :
===================
set head on
set pagesize 30
select NVL(tablespace_name,'** Total **') "Tablespace Name",
sum("allocated") "Allocated(M)",
sum("used") "Used(M)",
sum("free") "Free(M)",
sum(df_cnt) "#(File)"
from
(
select a.tablespace_name, trunc(b.assigned/1048576) "allocated",
trunc((b.assigned-a.free)/1048576) "used",
trunc(a.free/1048576) "free",
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;
Calculate number of concurrent users of an existing instance:
==============================================
The view v$license keeps track of concurrent sessions and users.
SQL> desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER
select sessions_current from v$license;
=============
A Shell Script To Take RMAN Cold/Hot and Export Backup:
======================================================
#!/bin/bash
ORACLE_SID=SHAAN;export ORACLE_SID
echo $ORACLE_SID
echo “Which kind of backup you want to take?”
echo “1) RMAN COLD BACKUP”
echo “2) RMAN HOT BACKUP”
echo “3) LOGICAL OR EXPORT BACKUP”
echo “Enter your option”
read option
while [ $option -gt 3 ]||[ $option -le 0 ]
do
echo “Please Enter the correct option”
read option
done
case $option in
1|2) echo “You are taking RMAN backup of shaan Database”
rman target sys/oracle@shaan @/ORACLE/RMAN_BACKUP/rman_backup_$option.txt;exit;;
3) echo “You are taking export backup of shaan Database”
exp system/oracle file=/ORACLE/EXPORT_BACKUP/exp_dat.dmp log=/oracle/export_backup/exp_dat.log full=y;
exit;;
esac
exit
# Now Create two simple text file for Rman Cold and Hot backup.
# The above script can call anyone of the following RMAN script depending on the option 1-cold or 2-hot backup.
rman_backup_1.txt:
run
{
shutdown immediate;
startup mount;
allocate channel d1 type disk FORMAT "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db";
backup full format "H:\Cold_Backup\COLD_BACKUP_%T_%d-%s_%p.db" (database);
copy current controlfile to 'H:\Cold_Backup\COLD_BACKUP_Control.db';
release channel d1;
release channel d1;
alter database open;
}
rman_backup_2.txt:
run {
allocate channel d1 type disk FORMAT "H:\Hot_Backup\Hot_BACKUP_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\Hot_Backup\Hot_BACKUP_%T_%d-%s_%p.db";
sql 'alter system archive log current';
backup
tag Catalog_Full_Rman
filesperset 10
format "H:\Hot_Backup\Hot_Backup_%T_FULL_%d-%s_%p.db"
database archivelog all;
release channel d1;
release channel d1;
}
Monitor RMAN status, while a backup is running:
======================================
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1)
/
Archlog Files Backed up during past 24 Hours:
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')
/
RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/
Script to List Completed and Pending RMAN backup of last 24-hours:
=================================================
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#;
Datafiles Backed up during past 24 Hours
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1);
Archlog Files Backed up during past 24 Hours
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO');
RMAN Backups Still Running:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore';
Script: Recompile Invalid Objects:
===========================
SELECT DISTINCT 'alter session set current_schema=' || owner || ';' cmd, owner, 1 order_col, NULL object_name
FROM dba_objects
WHERE status = 'INVALID' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
UNION
SELECT 'ALTER ' || DECODE ( object_type, 'PACKAGE BODY', 'PACKAGE', object_type ) || ' ' || owner || '.' || object_name || ' COMPILE' ||
DECODE ( object_type, 'PACKAGE BODY', ' BODY', '' ) || ';' cmd, owner, 2 order_col, object_name
FROM dba_objects outer
WHERE status = 'INVALID' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
AND ( object_type <> 'PACKAGE BODY' OR NOT EXISTS ( SELECT NULL
FROM dba_objects
WHERE owner = outer.owner AND object_name = outer.object_name
AND object_type = 'PACKAGE' AND status = 'INVALID')
)
ORDER BY 2, 3, 4
/
Script: To Monitor Tablespaces/datafiles
Important Note: If any of the script in this blog is not running then please re-type it or try to retype quotation, command and braces (may be format is changed). I am using toad so if you are using SQL then try to fix column length before exectunig the script (if any).
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To check Tablespace free space:
========================
SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
To check Tablespace by datafile:
========================
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE
group by tablespace_name, file_id;
To Check Tablespace used and free space:
==============================
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;
Tablespace used and Free space in MB:
============================
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
--or--
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 order by t.tablespace;
List all Tablespaces with free space < 10% or full space> 90%:
=============================================
set pagesize 300
set linesize 100
column tablespace_name format a15 heading ‘Tablespace’
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading ‘Total Size(Mb)’
column Tot_Free format 999,999,999 heading ‘Total Free(Kb)’
column Pct_Free format 999.99 heading ‘% Free’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
column Min_Add format 999,999,999 heading ‘Min space add (MB)’
ttitle center ‘Tablespaces With Less Than 10% Free Space’ skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
To check Datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
To check Used free space in Temporary Tablespace:
======================================
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM V$temp_space_header GROUP BY tablespace_name;
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Who is using which UNDO or TEMP segment?
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
/
Who is using the Temp Segment?:
=========================
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Total Size and Free Size of Database:
select a.data_size+b.temp_size+c.redo_size "Total_Size (GB)"
from ( select sum(bytes/1024/1024/1024) data_size
from dba_data_files ) a,
( select nvl(sum(bytes/1024/1024/1024),0) temp_size
from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size
from sys.v_$log ) c;
--Total Size of Database with free space
col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size"
, round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/
To find used space of database:
========================
SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
To Check Default Tablespace and Temporary Tablespace:
=========================================
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
To know default and Temporary Tablespace for a User:
=========================================
select username,temporary_tablespace,default_tablespace from dba_users where username='STARREP';
To know Default Tablespace for All User:
select default_tablespace,temporary_tablespace,username from dba_users;
IO status of all of the datafiles in database:
===============================
WITH total_io AS
(SELECT SUM (phyrds + phywrts) sum_io
FROM v$filestat)
SELECT NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
WHERE a.file# = b.file#
ORDER BY a.file#
/
Script: RMAN Hot Backup on Linux Environment:
=====================================
# This script can be used to take a hot backup using RMAN.
# Some of the variables in the initial section of the script need to have valid values
# Set the catalog to the catalog database
# This script will also mail errors encountered in the backup process
# =============================
#!/bin/sh
export ORACLE_SID=...
export ORACLE_HOME=....
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
DBA="shahid.ahmed@al-sadhan.com"
INST=`hostname`
TODAY=`date +%d%b`
BACKUP_DIR=/u01/backup/
LOG_DIR=/u01/backup/logs
LOGFILE=$LOG_DIR/rman_backup_${ORACLE_SID}_${TODAY}.log
ERRFILE=$LOG_DIR/err_status.log
$ORACLE_HOME/bin/rman <<EOF > ${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
connect target/
connect catalog catalog/catalog@rman
run {
allocate channel t1 TYPE DISK;
setlimit channel t1 kbytes 2048000;
allocate channel t2 TYPE DISK;
setlimit channel t2 kbytes 2048000;
allocate channel t3 TYPE DISK;
setlimit channel t3 kbytes 2048000;
allocate channel t4 TYPE DISK;
setlimit channel t4 kbytes 2048000;
backup full tag BACKUP_FULL_${ORACLE_SID} format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.dbf' database;
backup current controlfile format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.ctl';
sql 'alter system archive log current';
backup archivelog all format '${BACKUP_DIR}/%d_%t_%p_%s_%c_%u.arc'
archivelog until time 'sysdate-7' delete input;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
EOF
egrep "RMAN-|ORA-" $LOGFILE > $ERRFILE
if [ -s $ERRFILE ]; then
mail -s "MTD PROD chfoh-ls-ipr RMAN Backup FAILED $ORACLE_SID" $DBA <
${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
else
mail -s "MTD PROD chfoh-ls-ipr RMAN Backup SUCCESS $ORACLE_SID" $DBA <
${LOG_DIR}/rman_backup_${ORACLE_SID}_${TODAY}.log
fi
session wait events:
==============
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT s.inst_id,
NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC;
Lists all locked objects for whole RAC
============================
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
Displays information on all database sessions for whole RAC.
===========================================
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;
SET PAGESIZE 14
Displays information on all long operations for whole RAC.
=========================================
SET LINESIZE 200
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;
Locked Objects:
============
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Displays information on all long operations.
================================
SET PAGESIZE 14
SET VERIFY ON
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
Displays information on all database session waits.
======================================
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
number of daily concurrent requests:
=============================
SELECT trunc(REQUESTED_START_DATE), count(*)
FROM FND_CONCURRENT_REQUESTS
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate
group by rollup(trunc(REQUESTED_START_DATE)) ;
DB size with Tablespace :
===================
set head on
set pagesize 30
select NVL(tablespace_name,'** Total **') "Tablespace Name",
sum("allocated") "Allocated(M)",
sum("used") "Used(M)",
sum("free") "Free(M)",
sum(df_cnt) "#(File)"
from
(
select a.tablespace_name, trunc(b.assigned/1048576) "allocated",
trunc((b.assigned-a.free)/1048576) "used",
trunc(a.free/1048576) "free",
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;
Calculate number of concurrent users of an existing instance:
==============================================
The view v$license keeps track of concurrent sessions and users.
SQL> desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER
select sessions_current from v$license;
No comments:
Post a Comment