//** This script lists all Tablespaces and its datafiles with their free and used space **//
ORDER BY df.tablespace_name, df.file_name;
4) Tablespaces :
//** This script lists all Tablespaces with their Sizes **//
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;
5) 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;
SET PAGESIZE 14
SET VERIFY ON
6) Data Pump Monitoring Script :
select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
7) RMAN Job Monitoring Script :
SQL > SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;
To see the Rman status and SID:
SQL > SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%';
8) To see what USERS are Running :SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;
**** To see for a
particular USER, what he is running *******
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = '<username>';
9) Find FREE/USED/TOTAL size of oracle database :(used space):----------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments;
(free space):
---------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_free_space;
(Total database size):
---------------------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_data_files;
+
(Temp size):
---------------
SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from v$temp_space_header;
(or)
SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"
FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
FROM dba_temp_files) c;
10) RMAN Cold Backup :
Rman target / nocatalog
Rman > configure controlfile autobackup on;
Rman > run {
2> allocate channel d1 type disk;
3> Backup full tag full_offline_bkup
4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’
5> Database plus archivelog;
6> Release channel d1;
}
