Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Script to Get Oracle Database info

  1. #1

    Script to Get Oracle Database info

    REM-----------------------------------------------------
    REM File-name : database_status.sql
    REM Purpose : Database General information
    REM--------------------------------------------------------

    REM column instdate new_value v_instdate noprint
    REM SELECT TO_CHAR(sysdate,'DDmon') instdate FROM dual;

    prompt
    prompt Specify the Report name and path name
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    prompt like In windows c:\dbrep.txt or in unix,solaris /dbrep.txt
    prompt


    spool &Report_path_and_Name


    SET LINESIZE 80
    SET PAGESIZE 60



    TTITLE CENTER ''
    SELECT ('---Database Information as of : '
    ||TO_CHAR(sysdate,'dd-Mon-yyyy, hh24:mi:ss')) "Start"
    FROM dual;
    TTITLE CENTER 'About Database' SKIP 1 CENTER ================ SKIP 2
    SELECT name,dbid,created,log_mode
    FROM V$DATABASE;

    TTITLE CENTER 'About Oracle RDBMS Version' SKIP 1 CENTER ============================ SKIP 2
    SELECT *
    FROM v$version;

    TTITLE CENTER 'About LOGFILES' SKIP 1 CENTER ================ SKIP 2
    COLUMN group# HEADING Group FORMAT 999
    COLUMN member HEADING Member FORMAT A60 WRAP
    COLUMN size HEADING "Size|(MB)" FORMAT 9999.9999
    SELECT a.group#,
    a.member,
    b.bytes/1048576 "Size"
    FROM v$logfile a, v$log b
    WHERE a.group# = b.group#
    ORDER BY a.group#;

    TTITLE CENTER 'About CONTROL FILES' SKIP 1 CENTER ===================== SKIP 2
    COLUMN name HEADING ControlFile FORMAT A70 WRAP
    SELECT name
    FROM v$controlfile;

    TTITLE CENTER 'About ROLLBACK SEGMENTS' SKIP 1 CENTER ========================= SKIP 2
    COLUMN segment_name HEADING 'Segment|Name' FORMAT A15 WRAP
    COLUMN owner HEADING 'Owner' FORMAT A12 WRAP
    COLUMN tablespace_name HEADING 'Tablespace|Name' FORMAT A12 WRAP
    COLUMN segment_id HEADING 'Seg|ID' FORMAT 999
    rem COLUMN file_id HEADING 'FID' FORMAT 999
    rem COLUMN block_id HEADING 'Blk|ID' FORMAT 99999
    rem COLUMN initial_extent HEADING 'Init|Ext' FORMAT 9999999
    rem COLUMN next_extent HEADING 'Next|Ext' FORMAT 9999999
    rem COLUMN min_extents HEADING 'Min|Ext' FORMAT 999999
    rem COLUMN max_extents HEADING 'Max|Ext' FORMAT 999999
    rem COLUMN pct_increase HEADING 'PCT|Increase' FORMAT 999999
    COLUMN seg_size HEADING "Size|(MB)" FORMAT 999.9999
    COLUMN status HEADING 'Status' FORMAT A10
    rem COLUMN instance_num HEADING 'Instance|Number' FORMAT 999999
    SELECT a.segment_name,
    a.owner,
    a.tablespace_name,
    a.segment_id,
    a.status,
    b.bytes/1048576 seg_size,
    b.extents
    FROM dba_rollback_segs a, dba_segments b
    WHERE b.segment_name = a.segment_name
    ORDER BY segment_name;


    TTITLE CENTER 'About TABLESPACES' SKIP 1 CENTER =================== SKIP 2
    COLUMN "Size" FORMAT 99999.99 HEADING "TS Size|(In MB)"
    COLUMN "Free" FORMAT 99999.99 HEADING "TS Free|(In MB)"
    COLUMN "TS_PCT_Free" FORMAT 99999.99 HEADING "TS % Free"
    BREAK ON Report SKIP 1
    COMPUTE SUM OF size ON Report
    COMPUTE SUM OF free ON Report
    COMPUTE AVG OF ts_pct_free ON Report


    SELECT t.tablespace_name,
    SUM(d.bytes/1048576) ts_size,
    t.initial_extent,
    t.next_extent,
    t.pct_increase,
    t.status
    FROM dba_tablespaces t, dba_data_files d
    WHERE t.tablespace_name = d.tablespace_name
    GROUP BY t.tablespace_name,
    t.initial_extent,
    t.next_extent,
    t.pct_increase,
    t.status
    ORDER BY tablespace_name;

    COLUMN tablespace_name HEADING 'Tablespace Name' FORMAT A30 WRAP
    TTITLE CENTER 'About Indexes in Tablespace ' SKIP 1 CENTER ============================ SKIP 2
    COLUMN index_count FORMAT 9999 HEADING "Index_Count"
    BREAK ON Report SKIP 1
    COMPUTE SUM OF index_count ON Report
    SELECT DISTINCT tablespace_name,
    COUNT(tablespace_name) index_count
    FROM dba_indexes
    GROUP BY tablespace_name
    ORDER BY tablespace_name;

    TTITLE CENTER 'About Data Files' SKIP 1 CENTER ================== SKIP 2
    COLUMN tablespace_name HEADING 'Tablespace|Name' FORMAT A12 WRAP
    COLUMN dfile HEADING 'File Name ' FORMAT A40 WRAP
    COLUMN d.tablespace_name HEADING 'TS Name' FORMAT A12 WRAP
    COLUMN COUNT(f.file_id) HEADING Frag FORMAT 9999
    COLUMN d.bytes/1048576 HEADING 'Size In MB' FORMAT 99999
    COLUMN d.status HEADING 'Status' FORMAT A10
    SELECT d.file_name dfile,
    d.tablespace_name,
    COUNT(f.file_id) frag,
    d.bytes/1048576,
    d.status
    FROM dba_data_files d, dba_free_space f
    WHERE d.file_id=f.file_id
    GROUP BY d.file_name,
    d.tablespace_name,
    d.bytes/1048576,
    d.status
    ORDER BY tablespace_name;

    TTITLE CENTER 'About USERS' SKIP 1 CENTER ============= SKIP 2
    COLUMN username HEADING 'User Name' FORMAT A15 WRAP
    COLUMN default_tablespace HEADING 'Default TS' FORMAT A15 WRAP
    COLUMN temporary_tablespace HEADING 'Temporary TS' FORMAT A15 WRAP
    COLUMN created HEADING 'Created' FORMAT A10
    COLUMN profile HEADING 'Profile' FORMAT A15 WRAP
    SELECT username,
    default_tablespace,
    temporary_tablespace,
    created,
    profile
    FROM dba_users
    ORDER BY username;

    TTITLE CENTER 'About Non-Default Parameter' SKIP 1 CENTER ================= SKIP 2
    COLUMN name FORMAT A39 WRAP
    COLUMN value FORMAT A39 WRAP
    SELECT name,
    value
    FROM v$parameter
    WHERE isdefault= 'FALSE'
    ORDER BY name;

    TTITLE CENTER 'About SGA' SKIP 1 CENTER =========== SKIP 2
    COLUMN name FORMAT A30 WRAP
    COLUMN value FORMAT 999999999999 WRAP
    BREAK ON REPORT SKIP 1
    COMPUTE SUM OF value ON REPORT
    SELECT name,
    value
    FROM v$sga;

    TTITLE CENTER 'space in tablespace' SKIP 1 CENTER ================= SKIP 2

    COLUMN TABLESPACE_NAME FORMAT A15 HEADING TS_Name
    COLUMN pct_free HEADING "%FREE"
    COLUMN pct_used HEADING "%USED"

    select a.TABLESPACE_NAME,
    a.total,nvl(b.used,0) USED,a.total-nvl(b.used,0) free,
    nvl((b.used/a.total)*100,0) pct_USED,100-nvl((b.used/a.total)*100,0) pct_free
    from (select TABLESPACE_NAME,sum(bytes)/(1024*1024) total from sys.dba_data_files
    group by TABLESPACE_NAME) a,(select TABLESPACE_NAME,bytes/(1024*1024) used
    from sys.SM$TS_USED) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+);

    spool off

    spool

    TTITLE CENTER 'HIT RATIOS' SKIP 1 CENTER ================ SKIP 2


    select sum(gethits)*100/sum(gets) library_cache_hit_ratio
    from v$librarycache;
    select sum(gets-getmisses)*100/sum(gets) dictionary_cache_hit_ratio
    from sys.v_$rowcache;
    select round((congets.value+dbgets.value-physreads.value)*100/
    (congets.value+dbgets.value),4) buffer_cache_hit_ratio
    from v$sysstat congets,v$sysstat dbgets,v$sysstat physreads
    where congets.name='consistent gets'
    and dbgets.name='db block gets'
    and physreads.name='physical reads';


    TTITLE CENTER 'SPACE MANAGEMENT' SKIP 1 CENTER ================ SKIP 2

    COLUMN TABLESPACE_NAME FORMAT A15 HEADING TS_Name
    COLUMN pct_free HEADING "%FREE"
    COLUMN pct_used HEADING "%USED"

    select a.TABLESPACE_NAME,
    a.total,nvl(b.used,0) USED,a.total-nvl(b.used,0) free,
    nvl((b.used/a.total)*100,0) pct_USED,100-nvl((b.used/a.total)*100,0) pct_free
    from (select TABLESPACE_NAME,sum(bytes)/(1024*1024) total from sys.dba_data_files
    group by TABLESPACE_NAME) a,(select TABLESPACE_NAME,bytes/(1024*1024) used
    from sys.SM$TS_USED) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+);


    TTITLE CENTER ' total sessions'SKIP 1 CENTER ================ SKIP 2

    select count(*),program from v$session where username is not null group by program;


    TTITLE CENTER 'active sessions'SKIP 1 CENTER ================ SKIP 2

    select count(*),program from v$session where username is not null and status='ACTIVE'
    group by program;



    TTITLE CENTER 'Redo Generation' SKIP 1 CENTER ================ SKIP 2

    select (max(sequence#)-min(sequence#))*30 FROM v$log_history where
    substr(to_char(first_time,'ddmonyy'),1,11)=substr( to_char(SYSDATE-1,'ddmonyy'),1,11);

    TTITLE CENTER 'latest_objects(today)'SKIP 1 CENTER ================ SKIP 2

    select object_name,created from user_objects where to_char(created,'DD-MM-YY')
    =to_char(SYSDATE,'DD-MM-YY');


    TTITLE CENTER 'latest_objects(previous date)' SKIP 1 CENTER ================ SKIP 2

    select object_name,created from user_objects where to_char(created,'DD-MM-YY')
    =to_char(SYSDATE-1,'DD-MM-YY');


    TTITLE CENTER 'invalid_objects' SKIP 1 CENTER ================ SKIP 2

    COLUMN object_name FORMAT A15 HEADING obj_name
    select object_type,object_name,status from dba_objects where status='INVALID' and
    owner='CSN' order by object_type;



    TTITLE CENTER 'last_analyzed' SKIP 1 CENTER ================ SKIP 2

    select count(*),trunc(last_analyzed) from dba_tables where owner = 'CSN'
    group by trunc(last_analyzed);


    TTITLE CENTER 'Extents' SKIP 1 CENTER ================ SKIP 2

    COLUMN segment_name FORMAT A15 HEADING segment_name
    select sum(extents),segment_name,max_extents from dba_segments where
    segment_type='TABLE' and owner='CSN' group by segment_name,max_extents;



    TTITLE CENTER 'db links' SKIP 1 CENTER ================ SKIP 2

    COLUMN owner FORMAT A10 HEADING owner
    COLUMN db_link FORMAT A15 HEADING db_link
    COLUMN host FORMAT A15 HEADING host
    select owner,db_link,host,to_char(created,'MM/DD/YYYY HH24:MI:SS')
    from dba_db_links order by owner,db_link;

    spool off
    exit




    Last edited by shoaibansari; 02-14-2012 at 08:06 PM.

  2. #2
    select active_session_history.event,sum(active_session_hi story.wait_time +
    active_session_history.time_waited) ttl_wait_time
    from v$active_session_history active_session_history
    where active_session_history.sample_time between sysdate - 60/2880 and sysdate
    group by active_session_history.event order by 2

    ================================================== =

    select sesion.sid,sesion.username,sum(active_session_hist ory.wait_time +
    active_session_history.time_waited) ttl_wait_time
    from v$active_session_history active_session_history,v$session sesion
    where active_session_history.sample_time between sysdate - 60/2880 and sysdate
    and active_session_history.session_id = sesion.sid
    group by sesion.sid, sesion.username order by 3

    ************************************************** *

    select active_session_history.user_id,dba_users.username, sqlarea.sql_text,
    sum(active_session_history.wait_time +
    active_session_history.time_waited) ttl_wait_time
    from v$active_session_history active_session_history,
    v$sqlarea sqlarea,dba_users
    where active_session_history.sample_time between sysdate - 60/2880 and sysdate
    and active_session_history.sql_id = sqlarea.sql_id
    and active_session_history.user_id = dba_users.user_id
    group by active_session_history.user_id,sqlarea.sql_text,db a_users.username
    order by 4

    ==================================================

    select dba_objects.object_name,dba_objects.object_type,ac tive_session_history.event,
    sum(active_session_history.wait_time +
    active_session_history.time_waited) ttl_wait_time
    from v$active_session_history active_session_history,dba_objects
    where active_session_history.sample_time between sysdate - 60/2880 and sysdate
    and active_session_history.current_obj# = dba_objects.object_id
    group by dba_objects.object_name, dba_objects.object_type,active_session_history.eve nt
    order by 4

    ==================================================

    SQL> SELECT DISTINCT SID FROM V$SESSION
    SQL> SELECT DISTINCT SID FROM V$SESSION_EVENT

    Finding the SID/SERIAL# of a user showing a particular type of wait:

    SELECT s.sid , s.serial#, s.status, s.server, s.username,e.event, e.time_waited
    FROM v$session_event e, v$session s
    WHERE e.sid=s.sid
    AND e.event like '&WAITEVENT_TO_CHECK'
    AND e.time_waited > '&WAIT_TIME_THRESHOLD'
    -------------------------------------------------
    The following SQL statement will show if many different statements are hashing to the same value:
    SELECT hash_value, count(hash_value) FROM v$sqlarea
    GROUP BY hash_value HAVING count(hash_value) > 5;
    -------------------------------------------------
    select * from dba_ts_quotas where username='HR';
    --------------------------------------------------

+ Reply to Thread

Similar Threads

  1. Dynamic Script to take Hot Backup of Oracle Database
    By Hemant in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 10
    Last Post: 03-14-2012, 11:40 PM
  2. Script to Get Oracle Database info
    By shoaibansari in forum Oracle Database Administration 10g and 11g
    Replies: 1
    Last Post: 02-14-2012, 08:09 PM
  3. Replies: 1
    Last Post: 01-01-2012, 03:50 PM
  4. Replies: 0
    Last Post: 01-01-2012, 01:33 PM
  5. Health Check script for Oracle RAC Database
    By Amit in forum RAC Concepts and Administration
    Replies: 0
    Last Post: 11-03-2011, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics