SQL Script to check whats going on in database

This is a simple SQL script to check for all sessions describing whats going on in the database. 

set pages 300
set lines 300
col OSUSER format a30
col SCHEMANAME format a25
col USERNAME format a25
col PROGRAM format a20
col COMMAND format a50
select
   osuser, schemaname,
   substr(s.username,1,18) username,
   substr(s.program,1,15) program,
   decode(s.command,
       0,'No Command',
       1,'Create Table',
       2,'Insert',
       3,'Select',
       6,'Update',
       7,'Delete',
       9,'Create Index',
      15,'Alter Table',
      21,'Create View',
      23,'Validate Index',
      35,'Alter Database',
      39,'Create Tablespace',
      41,'Drop Tablespace',
      40,'Alter Tablespace',
      53,'Drop User',
      62,'Analyze Table',
      63,'Analyze Index',
         s.command||': Other') command
from 
   v$session     s,
   v$process     p,
   v$transaction t,
   v$rollstat    r,
   v$rollname    n
where s.paddr = p.addr
and   s.taddr = t.addr (+)
and   t.xidusn = r.usn (+)
and   r.usn = n.usn (+)
and s.schemaname  not in ('SYS','SYSTEM')
order by 1
;

Example o/p:

OSUSER                       SCHEMANAME             USERNAME                    PROGRAM       COMMAND
-------------- ------------------------ --------------------       --------------------       ---------------
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                       INFO_TNJ             INFO_TNJ             pmrepagent.exe       Select
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       Select
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
Info_USer                      INFO_TNJ             INFO_TNJ             pmrepagent.exe       No Command
oracle                             DBSNMP               DBSNMP               emagent@uxsserv    No Command

18 rows selected. 

SQL>

dba_tab_col_statistics & dbms_stats.convert_raw_value() to display min and max value of a table in Oracle

Well, couple of days back we had a request to delete the records which are older than 6 months from audit table. The size of the table was more than 35GB. So we thought of doing this over the weekend, but still we wanted to have baseline statistics of how many rows we got to retain and how many rows we got to delete. As we analyze our tables daily we got the total number of rows in the SYS.AUD$ table easily.

SQL> select num_rows from dba_tables where table_name='AUD$';
NUM_ROWS
----------

214285904
SQL>

Thats a huge table aint'it then we wanted to check for the minimum and maximum value of the table. While google'ng around I found few articles on dbms_stats.convert_raw_value() and believe me its really helpfull to get us the minimum and maximum values of big tables in Oracle, It is used to convert the raw values to the displayable format. Since we have LOW_VALUE and HIGH_VALUE in raw type, we can easily convert it in to human readable format by using this package. We used the below block to find out what is the minimum value of column Timestamp# in Aud$ table.

 SQL> desc dba_tab_col_statistics
 Name                                      Null?    Type
 ----------------------------------------- -------- ------        OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(30)
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 HISTOGRAM                                          VARCHAR2(15)

PLSQL block to convert raw values in LOW_VALUE/HIGH_VALUE columns

set serveroutput on
DECLARE
rv RAW(32) ;
dt DATE;
BEGIN
select low_VALUE into rv from dba_tab_col_statistics where TABLE_NAME='AUD$' and COLUMN_NAME='TIMESTAMP#';
dbms_stats.convert_raw_value(rv, dt);
dbms_output.put_line( TO_CHAR(dt, 'dd-MON-yyyy hh:mm'));
END;
/

PL/SQL procedure successfully completed.
01-MAY-2009 12:05
SQL>

as simple as that, else had I run "select min(timestamp#) from aud$" it would have taken atleast ~40mins to 1hour to get me the result set. We can change the tablename and column name as required,

SQL Script to display the table space report for the tablespaces with less than 15% free space.

The below script can be used to display the table space report for only those tablespaces which is 85% Full or in other words which has less than 15% free space.

col tablespace_name format a30 heading "Tablespaces|over 85% Full"
col total_space format 999,999,990.00 heading "Current|Total|Space (Gb)"
col free_space format 999,999,990.00 heading "Current|Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col addl_space format 999,990.00 heading "Total Space|after|AutoExtend"
col addl_free_space format 999,990.00 heading "Total Free|Space after|AutoExtend"
break on a1 on report
compute sum of a3 on report
compute sum of a4 on report
set termout off
set pause off
ttitle "View Free Tablespace Size by Free%"
set pages 50 lines 100
select  t.tablespace_name,
        s.total_space,
        f.free_space,
        (f.free_space / s.total_space) * 100 pct_free,
        s.extensible addl_space,
        f.free_space + (s.extensible - s.total_space) addl_free_space
from    dba_tablespaces                  t,
        (select         tablespace_name,
                        sum(user_bytes) / (1024*1024*1014) total_space,
                        sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / (1024*1024*1014) extensible
         from           dba_data_files
         group by       tablespace_name) s,
        (select         tablespace_name,
                        sum(bytes) / (1024*1024*1014) free_space
         from           dba_free_space
         group by       tablespace_name) f
where   t.contents = 'PERMANENT'
and     s.tablespace_name = t.tablespace_name
and     f.tablespace_name = t.tablespace_name
and     (f.free_space / s.total_space) * 100 <= 15
order by 4
/

Example o/p will be some thing like below:

Thu Oct 08                                                                                 page    1
                                 View Free Tablespace Size by Free%

                         Current         Current              Total Space      Total Free
Tablespaces                Total            Free Current            after     Space after
over 85% Full         Space (Gb)      Space (Gb)  % Free       AutoExtend      AutoExtend
----------       --------------- --------------- -------      -----------     -----------
RBS32                       0.59            0.05    9.10            32.32           31.78

And if we want to display the details of all existing tablespaces then just remove the line " and     (f.free_space / s.total_space) * 100 <= 15" from the script it will display the details of all the existing tablespaces in the database,

Script:

col tablespace_name format a30 heading "Tablespaces"
col total_space format 999,999,990.00 heading "Current|Total|Space (Gb)"
col free_space format 999,999,990.00 heading "Current|Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col addl_space format 999,990.00 heading "Total Space|after|AutoExtend"
col addl_free_space format 999,990.00 heading "Total Free|Space after|AutoExtend"
break on a1 on report
compute sum of a3 on report
compute sum of a4 on report
set termout off
set pause off
ttitle "View Free Tablespace Size by Free%"
set pages 50 lines 100
select  t.tablespace_name,
        s.total_space,
        f.free_space,
        (f.free_space / s.total_space) * 100 pct_free,
        s.extensible addl_space,
        f.free_space + (s.extensible - s.total_space) addl_free_space
from    dba_tablespaces                  t,
        (select         tablespace_name,
                        sum(user_bytes) / (1024*1024*1014) total_space,
                        sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / (1024*1024*1014) extensible
         from           dba_data_files
         group by       tablespace_name) s,
        (select         tablespace_name,
                        sum(bytes) / (1024*1024*1014) free_space
         from           dba_free_space
         group by       tablespace_name) f
where   t.contents = 'PERMANENT'
and     s.tablespace_name = t.tablespace_name
and     f.tablespace_name = t.tablespace_name
order by 4
/

Example Report:

Thu Oct 08                                                                                 page    1
                                 View Free Tablespace Size by Free%

                       Current         Current           Total Space  Total Free
                         Total            Free   Current       after Space after
Tablespaces         Space (Gb)      Space (Gb)    % Free  AutoExtend  AutoExtend
-------------- --------------- ---------------   ------- ----------- -----------
RBS32                     0.59            0.05      9.10       32.32       31.78
TABLESPACE1               0.99            0.47     48.13       32.32       31.80
SYSTEM                    0.20            0.15     74.57        0.20        0.15
USERS                     0.99            0.99     99.99       32.32       32.32

SQL>
 

Display database name

-- This script will display database name, open mode and whether its configured in archivelog or noarchive log 

select name, open_mode, log_mode from v$database;

example o/p:
SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE  LOG_MODE
--------- ---------- ------------
ORCL      READ WRITE NOARCHIVELOG
SQL>
 
Some times when we log in to non-system user account we wont be able to access the v$database view, if we want to know to which database we have connected before implementing the change you can easily get it from "golbal_name"
 
SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL>