Queries : Memory, Free Space, Size, Temp Files, Control Files
etc..
1)Database block size:
select to_number(value)
"Block size in bytes"
from sys.v_$parameter
where name = 'db_block_size';
2)Max number of possible extents:
select to_number(value)/16-7
"MaxExtents"
from sys.v_$parameter
where name = 'db_block_size';
3)Min extent size:
select to_number(a.value) *
to_number(b.value) / 1024 "Min extent size in K"
from sys.v_$parameter a, sys.v_$parameter b
where a.name = 'db_block_size'
and b.name = 'db_file_multiblock_read_count';
4)List objects in the SYSTEM tablespace that doesn't
belong to SYS or SYSTEM
select * from sys.dba_segments
where owner not in ('PUBLIC',
'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';
5)Reports information about your current database
context
select 'User: '|| user || ' on
database ' || global_name,
'
(term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')'
as MYCONTEXT
from global_name;
6)Reports free memory available in the SGA
select name,
sgasize/1024/1024 "Allocated
(M)",
bytes/1024 "Free (K)",
round(bytes/sgasize*100, 2) "%
Free"
from (select sum(bytes) sgasize from
sys.v_$sgastat) s,
sys.v_$sgastat f
where f.name = 'free memory';
7)Measure the Buffer Cache Hit Ratio
Get initial Buffer Hit Ratio
reading
SELECT ROUND((1-(phy.value /
(cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat
con, v$sysstat phy
WHERE cur.name = 'db block
gets'
AND con.name = 'consistent
gets' AND phy.name = 'physical reads';
8)Database users with deadly roles assigned to them.
select grantee, granted_role,
admin_option
from sys.dba_role_privs
where granted_role in ('DBA',
'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'OEM_MONITOR')
and
grantee not in ('SYS', 'SYSTEM', 'OUTLN',
'AQ_ADMINISTRATOR_ROLE',
'DBA',
'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'OEM_MONITOR',
'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS',
'ORDPLUGINS',
'ORDSYS',
'TIMESERIES_DBA');
9)Database users with deadly system privilages
assigned to them.
select grantee, privilege,
admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or
privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or
admin_option = 'YES')
and
grantee not in ('SYS', 'SYSTEM', 'OUTLN',
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS',
'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS',
'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA');
10)Allocated, Used & Free space within datafiles
SELECT SUBSTR (df.NAME, 1, 40) file_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
ORDER BY file_name;
11)Show Used/free space by tablespace name
SELECT Total.name "Tablespace
Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0)
Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024)
Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace
B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) =
Total.name
ORDER BY Total.name;
12)INvestigating undo-tablespace in EBS
SELECT version FROM
v$timezone_file;
select count(status) from
dba_undo_extents where status = 'ACTIVE';
select count(status) from
dba_undo_extents where status = 'UNEXPIRED';
select (sum(bytes)/(1024*1024))
"SIZE in MB",tablespace_name from dba_free_space group by
tablespace_name;
13)Find type of objects available in a tablespace
select
ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments
ds,dba_objects do where ds.tablespace_name='TOOLS'
Temp tablespace usage
information
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;
14)To count no. of segments in each t.s.
select tablespace_name,count(*)
from dba_segments group by tablespace_name;
15)Database growth per month for last year
select to_char(creation_time,
'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in
Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
16)Query to find the ATG rollup Patch level in your
application (11i).
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc;
17)To find RUP Level in R12 : -
select bug_number,
creation_date
from ad_bugs
where bug_number in
('6022657','6266113','6728000')
ORDER BY 2;
18)To Check Maintenance Family Pack Level
select patch_level from
fnd_product_installations
where application_id = 275;
19)Query to find the Apps Version : -
select release_name from
fnd_product_groups;
20)Which FND_USER is locking that table
The column named "module" will tell you
the name of the Form Function
or the Concurrent Program Short name which has
aquired a lock onto that table.
SELECT c.owner
,c.object_name,c.object_type,
fu.user_name locking_fnd_user_name
,fl.start_time
locking_fnd_user_login_time,
vs.module,vs.machine ,vs.osuser
,vlocked.oracle_username,vs.sid,vp.pid
,vp.spid AS os_process,vs.serial#
,vs.status ,vs.saddr
,vs.audsid ,vs.process
FROM fnd_logins fl ,fnd_user fu
,v$locked_object vlocked
,v$process vp ,v$session vs,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id =
c.object_id
AND vs.paddr = vp.addr
AND vp.spid =
fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' ||
upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status ,'XX') !=
'KILLED';
21)Security related database initialization
parameters and password file users.
select name || '=' || value
"PARAMTER"
from sys.v_$parameter
where name in ('remote_login_passwordfile',
'remote_os_authent',
'os_authent_prefix',
'dblink_encrypt_login',
'audit_trail',
'transaction_auditing');
22)List security related profile information
select profile, resource_name,
limit
from dba_profiles
where resource_name like '%PASSWORD%'
or
resource_name like '%LOGIN%';
23)To find space used by a database user
Query to find space used by a
database user. Following query can be used to know the space used by the logged
in user in MBs:
SELECT sum(bytes)/1024/1024
user_size FROM user_segments;
24)Query to find the space occupied by all the users
in a database.
This requires access to
dba_segments table:
SELECT owner,
sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY
total_size DESC;
25)Total space occupied by all users:
SELECT sum(bytes)/1024/1024
total_size FROM dba_segments;
26)To find Database Size
The database mainly comprises
of datafiles, temp files and redo log files.
The biggest portion of a
database’s size comes from the datafiles.
27)To find out how many megabytes are allocated to
all datafiles:
SELECT sum(bytes)/1024/1024
data_size FROM dba_data_files;
28)To get the size of all TEMP files:
SELECT
nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;
29)To get the size of the on-line redo-logs:
SELECT sum(bytes)/1024/1024
redo_size FROM sys.v_$log;
Finally, summing up the three
above, total database size can be found:
SELECT (dsize.data_size +
tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0)
temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;
30)To find free space in temporary tablesapce:
SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free)
FROM V$temp_space_header GROUP BY tablespace_name;
31)To find tablespace free space in a database
SELECT a.tablespace_name,
a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes)
free_bytes
FROM dba_free_space b GROUP BY
file_id) b
WHERE a.file_id=b.file_id
and a.tablespace_name='SYSTEM'
ORDER BY a.tablespace_name;
32)To find Table size in a database :
select sum(BYTES/1024/1024) as
TOTAL_GIG from user_segments where
SEGMENT_NAME = 'TABLE_NAME';
Note: Need to execute as owner
of the table. (OR)
select sum(BYTES/1024/1024) as
TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS';
33)=== Temp Usage ===
select sum(blocks)/1024*8
"Size in MB" FROM v$sort_usage;
SELECT ss.sid,
sum(st.blocks)/1024*8
FROM v$sort_usage st, v$session
ss
where ss.saddr=st.session_addr
group by ss.sid
having sum(st.blocks)/1024*8
> 100 order by 2;
SELECT /*+ RULE */ s.username, s.osuser, s.sid,
NVL(s.machine,'N/A'),
NVL(s.module,'N/A'),
NVL(s.action,'N/A'),
NVL(s.program,'N/A'),
s.status ||' for
'||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0')
||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr',
u.tablespace, u.contents,
u.extents,
round((u.blocks*8)/1024),s.sql_address,
s.sql_hash_value
FROM v$session s, v$sort_usage u
WHERE s.saddr
= u.session_addr
AND u.contents = 'TEMPORARY'
AND s.audsid != USERENV('sessionid')
AND (u.blocks*8)/1024 >= 1000
ORDER BY 1,2,3,4,5 Desc;
34)=== High Redo ===
SELECT s.inst_id,s.sid,
s.serial#, s.username,
s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes >
10000000
ORDER BY 6 desc, 1, 2, 3, 4;
35)=== Rollback Used ===
SELECT rn.name,
ROUND(rs.rssize/1024/1024),
s.username, s.osuser, s.sid,
NVL(s.machine,'N/A'),
NVL(s.module,'N/A'),
NVL(s.action,'N/A'),
NVL(s.program,'N/A'),
s.sql_address,
s.sql_hash_value, p.spid,
s.status ||' for
'||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0')
||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
round(ceil((t.used_ublk*8)/1024),1)
FROM v$rollname rn, v$rollstat rs,
v$session s, v$transaction t,
v$process p
WHERE rn.usn = rs.usn
AND round((t.used_ublk*8)/1024) >= 1000
AND rs.usn = t.xidusn
AND s.sid = p.pid (+)
AND s.taddr = t.addr
ORDER BY 2 desc, s.sid ,s.status;
36)=== Roll back segement Information ====
select tablespace_name, status
segment_status,
count(extent_id) "Extent
Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024)
"Total Space in GB"
from dba_undo_extents
where tablespace_name like
'%UNDO%'
group by tablespace_name,
status;
37)=== Shared Pool Usage ===
SELECT
INST_ID,ROUND(bytes/1024/1024, 2)||' MB'
FROM gv$sgastat
WHERE name='free memory' AND
pool='shared pool';
38)=== Archive Generation for last 5 hours ===
SELECT TO_CHAR(first_time,
'DD-MM-YY') AS Day,
TO_CHAR(first_time, 'HH24') AS
Hour,COUNT(*)
FROM v$log_history
WHERE TO_CHAR(first_time,
'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY')
AND TO_CHAR(first_time, 'HH24') >=
TO_CHAR(sysdate, 'HH24') - 5
GROUP BY TO_CHAR(first_time, 'DD-MM-YY'),
TO_CHAR(first_time, 'HH24')
ORDER BY 2;
39)=== High Memory ===
select s.inst_id, s.sid, name,
round(value/1024/1024)
Mem_in_mb, p.*
from gv$sesstat s, v$statname
n, gv$session p
where n.statistic# =
s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;
40)=== Performance ===
select sql_hash_value, count(*)
from v$session
where event like 'enq%'
group by sql_hash_value;
select
sql_hash_value,username,osuser, count(*)
from v$session
where event like 'enq%'
and SQL_HASH_VALUE='&event'
group by
sql_hash_value,username,osuser;
select sql_text
from v$sqlarea
where hash_value =
'&hash_value';
select
s1.sid,FETCHES,ROWS_PROCESSED
from v$sql s,v$session s1
where s.HASH_VALUE=s1.SQL_HASH_VALUE
and s1.sid=4885;
select
s.sid,s.serial#,s.status,s.username,s.module,
s.osuser,x.event
from v$session s,v$session_wait
x
where s.paddr in ( select addr
from v$process
where spid in
(2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;
select
s.sid,s.serial#,s.status,s.username,
s.module,s.osuser,x.event
from v$session s,v$session_wait
x
where x.sid=s.sid and x.event
like '&event_name';
How to find the ORACLE_HOME path in Oracle Database?
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2)
ORACLE_HOME FROM
dba_libraries
WHERE
library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC
dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
=== Other Script to update the daily report ===
select status,count(1) from
gv$session group by status;
select count(1) from dba_tables
where logging='NO';
select distinct status,count(1)
from dba_indexes group by status;
select count(1) from
dba_objects where status='INVALID';
select sum(bytes)/1024/1024
from dba_data_files;
select sum(bytes)/1024/1024
from v$log;
select sum(bytes)/1024/1024/1024 from
dba_data_files
+
select sum(bytes)/1024/1024/1024 from dba_temp_files
+
select sum(bytes)/1024/1024/1024 from v$log
+
select sum(bytes)/1024/1024/1024 from dba_temp_files
+
select sum(bytes)/1024/1024/1024 from v$log
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
