Extract Oracle DB information in one go


A single script to gather various information's of your database. Save the following code as .sql file and run it from sql prompt. This will generate a .lst file with the datetime.

This script has been tested on 11gR2, 12cR1, 12cR2, 19c 


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

 

No comments:

Post a Comment