|
column tm new_value file_time noprint
select to_char(sysdate,'DDMMYYYY')tm from dual;
spool &file_time.lst
set lines 152
set pages 1000
alter session set "_hash_join_enabled"=true
/
---------DATABASE NAME------
PROMPT Database Name and Status
select name,open_mode,log_mode,database_role from v$database;
PROMPT TABLESPACE COUNT
SELECT COUNT(NAME) FROM V$TABLESPACE;
PROMPT DATAFILES HEADER COUNT
SELECT COUNT(NAME) FROM V$DATAFILE_HEADER;
-----INSTANCE------
PROMPT INSTANCE STATUS
select status from v$instance;
----------------db size--------------
PROMPT DB SIZE(DATAFILE + REDOLOG+CONTROLFILE)
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size
"total_size in GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d;
PROMPT TOTAL DATABASE USAGE
select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;
PROMPT
PROMPT
PROMPT DB PHYSICAL SIZE
PROMPT ==================
select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from
dba_data_files;
PROMPT
PROMPT
PROMPT DB ACUTAL SIZE
PROMPT ================
select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from
dba_segments;
----------TAblespace---------
PROMPT TABLESPACE INFORMATION
select TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE, ONLINE_STATUS from
dba_data_files;
PROMPT TABLESPACE SIZE AND FREE SPACE
set pagesize 100
set lines 130
COLUMN free_space_mb format 99999990.00
COLUMN allocated_mb format 99999990.00
COLUMN used_mb format 99999990.00
COLUMN percent_used format 00.00
col tablespace_name format a25
SELECT SUBSTR(df.tablespace_name,1,30) tablespace_name,
(df.bytes) / 1024 / 1024 allocated_mb,
((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,
round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used
FROM ( select sum(bytes) bytes,tablespace_name from dba_data_files group by
tablespace_name) df,
( select sum(bytes) bytes , tablespace_name from dba_free_space group by
tablespace_name) dfs
WHERE df.tablespace_name = dfs.tablespace_name
ORDER BY percent_used ;
-------------tablespace>75----------------------------------------
PROMPT TABLESPACE USED MORE THAN 75%
#alter session set "_hash_join_enabled"=true;
set pagesize 100
set lines 130
COLUMN free_space_mb format 99999990.00
COLUMN allocated_mb format 99999990.00
COLUMN used_mb format 99999990.00
COLUMN percent_used format 00.00
col tablespace_name format a25
SELECT SUBSTR(df.tablespace_name,1,30) tablespace_name,
(df.bytes) / 1024 / 1024 allocated_mb,
((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,
round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used
FROM ( select sum(bytes) bytes,tablespace_name from dba_data_files group by
tablespace_name) df,
( select sum(bytes) bytes , tablespace_name from dba_free_space group by
tablespace_name) dfs
WHERE df.tablespace_name = dfs.tablespace_name
and round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) > 75
ORDER BY percent_used ;
------------Archivegap-------------------
#alter session set "_hash_join_enabled"=true;
PROMPT ARCIVELOG LAST SEQUENCE
select max(sequence#)"Last Sequence" from v$loghist;
PROMPT LAST APPLIED ARCHIVELOG SEQUENCE ON STANDBY DATABASE
select max(SEQUENCE#)"Applied Sequence" from v$archived_log where
APPLIED='YES' and dest_id=2;
----------temp-----------------
PROMPT TEMP TABLESPACE INFO
SET LINESIZE 200
COL TABLESPACE_NAME FORMAT A20
SELECT TABLESPACE_NAME,SUM(BYTES_USED/1024/1024)
"USED_MB",SUM(BYTES_FREE/1024/1024) "FREE_MB",
SUM(BLOCKS_FREE) "FREE_BLOCKS"
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME;
--------------------------------------------------
PROMPT NUMBER OF ACTIVE SESSION
select count(*)"Active sessions" from v$session;
------------------Log sequence-----------------
PROMPT show parameter log_archive_dest_state_2
show parameter log_archive_dest_state_2
------------------Resource_Limit-----------------
PROMPT RESOURCE_LIMIT
SET LINES 200
SET PAGESIZE 200
COL RESOURCE_NAME FOR A30
COL INITIAL_ALLOCATION FOR A20
COL LIMIT_VALUE FOR A20
select * from v$resource_limit;
PROMPT COUNT OF INVALID OBJECTS
PROMPT ==========================
select count(*) from dba_objects where status='INVALID';
PROMPT DB HIT Ratio
PROMPT ==========================
select (1-(pr.value/
(dbg.value+cg.value)))*100
FROM v$sysstat pr,v$sysstat dbg,v$sysstat cg
WHERE Pr.name='physical reads'
AND dbg.name='db block gets'
AND cg.name='consistent gets';
PROMPT
=============================LOAD========================================
select stat_name,value from v$OSSTAT where STAT_NAME='LOAD';
PROMPT =================================EVENT===================================
select event,count(event) from v$session group by event;
PROMPT
=================================Session========================================
select username,status,count(*) from v$session group by username,status order
by 1,3;
PROMPT =================================Number of
Session========================================
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN max_sess_allowed FORMAT 999,999 JUSTIFY r HEADING 'Max sessions
allowed'
COLUMN num_sessions FORMAT 999,999,999 JUSTIFY r HEADING 'Number of sessions'
COLUMN pct_utl FORMAT a19 JUSTIFY r HEADING 'Percent Utilization'
COLUMN username FORMAT a15 JUSTIFY r HEADING 'Oracle User'
COLUMN num_user_sess FORMAT 999,999 JUSTIFY r HEADING 'Number of Logins'
COLUMN count_a FORMAT 999,999 JUSTIFY r HEADING 'Active Logins'
COLUMN count_i FORMAT 999,999 JUSTIFY r HEADING 'Inactive Logins'
PROMPT =================================Oracle Users
Session========================================
SET verify off
SELECT
TO_NUMBER(a.value) max_sess_allowed
, TO_NUMBER(count(*)) num_sessions
, LPAD(ROUND((count(*)/a.value)*100,0) || '%', 19) pct_utl
FROM v$session b, v$parameter a
WHERE a.name = 'sessions'
GROUP BY a.value;
break on report
compute sum of num_user_sess count_a count_i on report
SELECT
lpad(nvl(sess.username, '[B.G. Process]'), 15) username
, count(*) num_user_sess
, nvl(act.count, 0) count_a
, nvl(inact.count, 0) count_i
FROM
v$session sess
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'ACTIVE'
GROUP BY username) act
, (SELECT count(*) count, nvl(username, '[B.G. Process]') username
FROM v$session
WHERE status = 'INACTIVE'
GROUP BY username) inact
WHERE
nvl(sess.username, '[B.G. Process]') = act.username (+)
and nvl(sess.username, '[B.G. Process]') = inact.username (+)
GROUP BY
sess.username
, act.count
, inact.count;
=============================================== SQL row lock contention==========================================================
set linesize 1000
set pagesize 1000
col USERNAME format a10
select c.USERNAME,
a.SID,
c.SQL_ID,
a.EVENT,
a.STATE,
c.STATUS,
b.TIME_WAITED
from v$session_wait a,
v$session_wait_class b,
v$session c
where a.SID=b.SID
and b.SID=c.SID
and c.SQL_ID is not null
and b.TIME_WAITED<>0
and a.EVENT like '%row lock contention%'
order by 7;
=============================================== Table lock
==================================================================================================================
select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' ) is
blocking by '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || '
) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
and l1.type=l2.type;
Prompt==================================User Expiry Details===========================
select USERNAME,EXPIRY_DATE,PROFILE from dba_users where
account_status='OPEN';
Prompt==================================TABLE MAX SIZE IN
MB=================================
set linesize 1000
set pagesize 750
col Table_Name for a20
col owner for a15
col size for a10
select segment_name as "Table_Name",owner, round(bytes/1024/1024)
|| 'MB' as "Size"
from dba_segments where bytes>2073741824 and owner Not IN
('SYS','SYSTEM')
order by bytes desc;
Prompt==================================TABLE MAX SIZE IN
GB=================================
select segment_name as "Table_Name",owner,
round(bytes/1024/1024/1024) || 'GB' as "Size"
from dba_segments where bytes>2073741824 and owner Not IN
('SYS','SYSTEM')
order by bytes desc;
spool off
|
