Virtual Column in Oracle 11g

Oracle 11g has introduced a new feature that allows us to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). Oracle 11g enables us to store expressions directly in the base tables themselves as virtual columns.Virtual columns are more flexible than any of their prior alternatives.

The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Creating a virtual column :     Here we will create a table witha single column as follows .

SQL> create table vir_tab 
     ( id number , 
       sal number , 
       bonus number generated always as (id + sal) virtual 
     );
 Table created.

We can see that the virtual column is generated from a simple expression involving the other columns in our table. Note that the VIRTUAL keyword is optional and is included for what Oracle calls "syntactic clarity"

Virtual column values are not stored on disk. They are generated at runtime using their associated expression (in our example, N1 + N2). This has some implications for the way we insert data into tables with virtual columns, as we can see below.

SQL> insert into vir_tab values (100,11000,11100);
INSERT INTO t VALUES (10, 20, 30)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Hence ,we cannot explicitily add data to virtual columns, so we will attempt an insert into the physical columns only as follows

SQL> insert into vir_tab values(100,11000);
INSERT INTO t VALUES (10, 20)
            *
ERROR at line 1:
ORA-00947: not enough values

Here we see that we cannot insert columns,they are still considered part of the table's column list.This means that we must explicitily reference the physical columns in our insert statements, as follows .

SQL> insert into vir_tab (id,sal) values (100,11000);
1 row created.

Now we have successfully inserted the data and can query the table.

SQL> select * from vir_tab;

  ID        SAL      BONUS
---- ---------- ----------
 100      11000      11100


Indexes and Constraints on virtual columns :

SQL> create index sal_idx on vir_tab(sal);

For Constraint :

SQL> alter table vir_tab add constraint vir_id_pk primary key(id);

Benefits of Virtual Columns :

• Automatic re-computation of derived columns for ad-hoc query tools
• Reduction in redundant disk space for columns that must be derived from other columns (e.g. a MONTH column that is derived from another DATE column).
• Easier for interval partitioning

There are few restrictions on the virtual columns.
1.   We cannot write/insert the data into virtual columns.
2.   There is no support for index_organized, external, object, cluster, temporary tables.
3.   There is no support for Oracle-supplied datatypes, user-defined datatypes, LOBs, or LONG RAWs.

4.   We can partition the table based on virtual column .We can use below query to define virtual columns     defined in the users schema.

SQL> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS   WHERE VIRTUAL_COLUMN = ‘YES’;
5. we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

Reference ::  http://www.oracle-developer.net/display.php?id=510


Enjoy     :-) 

Database Resident Connection Pooling in Oracle 11g

In traditional client/server architectures, there is a one-to-one correspondence between a user session and a database connection. In Web-based systems however, this may not be the case.

Web based systems are “stateless” in nature--when we visit a page, a database connection is established with the database and when the page loading is over, the connection to the database is severed. Later, when the user clicks again on the page, a new connection is established that is severed after the desired effect. This process makes it unnecessary to maintain a large number of simultaneous connections.

According to the Tom Kytes : 

Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database.These connections are stored in a pool in the middle tier, an "array" if we will.  Each connection is set to "not in use" . When a user submits a web page to the application server, it runs a piece of code, our code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to us.  We generate the page, format the html whatever -- and then return the connection to the pool where someone else can use it.In this fashion, using connections to the database, we can avoid the connect/disconnect overhead.

Establishing connections is expensive in overhead, so connection pooling is an important requirement in the apps. when a page needs database access, it allocates one of the already established connections out of the pool. After the work is done, the Web session returns the connection back to the pool.

The problem with traditional client-side or middle-tier connection pooling, however, is:

• Each pool is confined to a single middle-tier node.

• A proliferation of pools results in excessive number of pre-allocated database servers and excessive database server memory consumption.

• Workload is distributed unequally across pools.

To obviate these problems, Oracle Database 11g provides a server-side pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.

Oracle Database 11g comes preinstalled with a default connection pool but it is shut down. To start it, use:
SQL> execute dbms_connection_pool.start_pool;
Now to connect to the pooled connections instead of a regular session, all we have to do is add a line (SERVER=POOLED) to the TNS entry as shown below :
e.g;

PRONE3_POOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SID = PRONE3)
)
)

The clients can connect to the connection pool, using the connect string PRONE3_POOL. That’s it. Now our apps will connect to the pool instead of the server. If we use the standard connect string without the TNSNAMES.ORA file, we can use the POOLED clause. For instance, in PHP, you will connect as:

$c = oci_pconnect(‘myuser’, ‘mypassword’,’xxxx/PRONE3:POOLED’);
or
$c = oci_pconnect(‘myuser’, ‘mypassword’,’PRONE3_POOLED’);

In the above description we started the default pool that comes with Oracle with the default options. we can use the procedure CONFIGURE_POOL in the supplied package DBMS_CONNECTION_POOL :

For More detail visit below links :
http://download.oracle.com/docs/cd/B14117_01/win.101/b10117/features001.htm
http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/connpoca.htm


Enjoy     :-)

Oracle 11g - Rman Backup Committed Undo ? Why?

We already know what undo data is used for. When a transaction changes a block, the past image of the block is kept it the undo segments. The data is kept there even if the transaction is committed because some long running  query that started before the block is changed can ask for the block that was changed and committed. This query should get the past image of the block—the pre-commit image, not the current one. Therefore undo data is kept undo segments even after the commit. The data is flushed out of the undo segment in course of time, to make room for the newly inserted undo data.

When the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed, since they are already in the redo log streams, or even in the datafiles (provided the dirty blocks have been cleaned out from buffer and written to the disk) and can be recovered from there. So, why bother backing up the committed undo data?

In Oracle Database 11g, RMAN does the smart thing: it bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup (and the recovery as well). In many databases, especially OLTP ones where the transaction are committed more frequently and the undo data stays longer in the undo  segments, most of the undo data is actually committed. Thus RMAN has to backup only a few blocks from the undo tablespaces.

The best part is that we  needn’t do anything to achieve this optimization; Oracle does it by itself.


Enjoy    :-) 

Proactive Health Checks using Validate Command

It  helps  us to  sleep  better  at night  knowing  that the  database  is  healthy and has no  bad blocks. But how can  we ensure that ? Bad blocks show  themselves only when  they are accessed so we  want  to identify  them  early  and  hopefully  repair  them  using  simple  commands  before  the  users  get an error. The tool  dbverify  can  do  the  job but  it  might  be  a little  inconvenient  to  use  because  it  requires writing  a  script  file contaning  all  datafiles and  a  lot  of  parameters. The  output also  needs scanning and interpretation. 

In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes this operation trivial by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output that is partially shown below:

C:\Users\Administrator> rman target sys/xxxx
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 31 15:03:47 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PIKE (DBID=1619611654)
RMAN> validate database;

Starting validate at 31-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=D:\APP\ORADATA\PIKE\CC_DICT_01.DBF
input datafile file number=00006 name=D:\APP\ORADATA\PIKE\CC_PIKE_01.DBF
input datafile file number=00007 name=D:\APP\ORADATA\PIKE\CC_PIKE_02.DBF
input datafile file number=00008 name=D:\APP\ORADATA\PIKE\CC_PIKE_03.DBF
input datafile file number=00009 name=D:\APP\ORADATA\PIKE\CC_PIKE_04.DBF
input datafile file number=00010 name=D:\APP\ORADATA\PIKE\CC_PIKE_05.DBF
input datafile file number=00001 name=D:\APP\ORADATA\PIKE\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\PIKE\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\ORADATA\PIKE\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\ORADATA\PIKE\USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14358        92192           3135230
  File Name: D:\APP\ORADATA\PIKE\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              60862
  Index      0              13160
  Other      0              3780

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              19066        79368           3135229
  File Name: D:\APP\ORADATA\PIKE\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              19106
  Index      0              14603
  Other      0              26585

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            18560           3135230
  File Name: D:\APP\ORADATA\PIKE\UNDOTBS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              18559

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              481          640             935664
  File Name: D:\APP\ORADATA\PIKE\USERS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15
  Index      0              2
  Other      0              142

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              248051       256000          1325211
  File Name: D:\APP\ORADATA\PIKE\CC_DICT_01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1074
  Index      0              1113
  Other      0              5762

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              119022       256000          3099996
  File Name: D:\APP\ORADATA\PIKE\CC_PIKE_01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              93382
  Index      0              29835
  Other      0              13761

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              124364       256000          3099672
  File Name: D:\APP\ORADATA\PIKE\CC_PIKE_02.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              89229
  Index      0              28893
  Other      0              13514

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              128858       256000          3099896
  File Name: D:\APP\ORADATA\PIKE\CC_PIKE_03.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              81211
  Index      0              33860
  Other      0              12071

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              68787        256000          3099998
  File Name: D:\APP\ORADATA\PIKE\CC_PIKE_04.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              137858
  Index      0              32218
  Other      0              17137

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              97469        256000          3099998
  File Name: D:\APP\ORADATA\PIKE\CC_PIKE_05.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              115137
  Index      0              30373
  Other      0              13021

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              614
Finished validate at 31-AUG-11

We can also validate a specific tablespace:

RMAN> validate tablespace users;

Or, datafile:

RMAN> validate datafile 1;

Or, even a block in a datafile:

RMAN> validate datafile 4 block 56;

The VALIDATE command extends much beyond datafiles however. we can validate spfile, controlfilecopy, recovery files, Flash Recovery Area, and so on.

Enjoy   :-) 

Database Monitoring scripts

Below is the monitoring script which is very useful for monitoring the health of database.This script has no adverse affects. There are no DML or DDL actions taken.The script must be run as sys user. 

REM Monitoring and tuning script for Oracle databases all versions

REM This script has no adverse affects. There are no DML or DDL actions taken

REM Parts will not work in all versions but useful info should be returned from other parts

REM Uses anonymous procedures to avoid storing objects in the SYS schema

REM therefore this script must be run as sys 

REM calls to v$parameter need to be moved into subblocks to prevent NO_DATA_FOUND exceptions

REM parameter numbers are different between Oracle Versions

REM This daily script is a subset of the weekly script 

REM This script monitors only those things that might cause an application or database failure

REM and not all of those

REM For nicer formatting run the following in vi: %s/  *$//

REM This strips the trailing whitespace returned from oracle

set pause off

set verify off

set echo off

set term off

set heading off

REM Set up dynamic spool filename

spool C:\spool.sql
select 'spool C:\'||name||'_'||'daily'||'_'||to_char(sysdate,'yymondd')||'.txt'
from sys.v_$database;
spool off


set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000


/*************************  START REPORT  **********************************/
/* Run dynamic spool output name */
@c:\spool.sql


set feedback off
set heading off


select 'Report Date: '||to_char(sysdate,'Monthdd, yyyy hh:mi')
from dual;


set heading on
prompt =============================================================
prompt .                      DATABASE (V$DATABASE) (V$VERSION)
prompt =============================================================
select NAME "Database Name",
CREATED "Created",
LOG_MODE "Status"
  from sys.v_$database;
select banner "Current Versions"
  from sys.v_$version;


prompt ============================================================
prompt .                      UPTIME (V$DATABASE) (V$INSTANCE)
prompt =============================================================


set heading off
column sttime format A30


SELECT NAME, ' Database Started on ',TO_CHAR(STARTUP_TIME,'DD-MON-YYYY "at" HH24:MI')
FROM V$INSTANCE, v$database;
set heading on
prompt .
prompt ==============================================================
prompt .                      SGA SIZE (V$SGA) (V$SGASTAT)
prompt ==============================================================
column Size format 99,999,999,999
select decode(name, 'Database Buffers',
'Database Buffers (DB_BLOCK_SIZE*DB_BLOCK_BUFFERS)',
'Redo Buffers',
'Redo Buffers     (LOG_BUFFER)', name) "Memory",
value "Size"
from sys.v_$sga
UNION ALL
select '------------------------------------------------------' "Memory",
to_number(null) "Size"
  from dual
UNION ALL
select 'Total Memory' "Memory",
sum(value) "Size"
  from sys.v_$sga;


prompt .
prompt .
prompt Current Break Down of (SGA) Variable Size
prompt ------------------------------------------


column Bytes format 999,999,999
column "% Used" format 999.99
column "Var. Size" format 999,999,999


select a.name "Name",
bytes "Bytes",
(bytes / b.value) * 100 "% Used",
b.value "Var. Size"
from sys.v_$sgastat a,
sys.v_$sga b
where a.name not in ('db_block_buffers','fixed_sga','log_buffer')
and b.name='Variable Size'
order by 3 desc;


prompt .
set feedback ON
declare
h_char          varchar2(100);
h_char2 varchar(50);
h_num1          number(25);
result1         varchar2(50);
result2         varchar2(50);


cursor c1 is
        select lpad(namespace,17)||': gets(pins)='||rpad(to_char(pins),9)||
                                     ' misses(reloads)='||rpad(reloads,9)||
               ' Ratio='||decode(reloads,0,0,to_char((reloads/pins)*100,999.999))||'%'
        from v$librarycache;


begin
    dbms_output.put_line('========================================');
    dbms_output.put_line('.  SHARED POOL: LIBRARY CACHE (V$LIBRARYCACHE)');
    dbms_output.put_line('========================================');
    dbms_output.put_line('.');
    dbms_output.put_line('.         Goal: The library cache ratio < 1%' );
    dbms_output.put_line('.');
 
    Begin
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 23;
    SELECT 'Current setting: '||substr(value,1,30) INTO result2
    FROM V$PARAMETER
    WHERE NUM = 325;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    h_num1 :=1;
    END;
    dbms_output.put_line('Recommendation: Increase SHARED_POOL_SIZE '||rtrim(result1));
    dbms_output.put_line('.                        OPEN_CURSORS '    ||rtrim(result2));
    dbms_output.put_line('.               Also write identical sql statements.');
    dbms_output.put_line('.');
     
    open c1;
    loop
fetch c1 into h_char;
exit when c1%notfound;

dbms_output.put_line('.'||h_char);
    end loop;
    close c1;


    dbms_output.put_line('.');


    select lpad('Total',17)||': gets(pins)='||rpad(to_char(sum(pins)),9)||
                                 ' misses(reloads)='||rpad(sum(reloads),9),
               ' Your library cache ratio is '||
                decode(sum(reloads),0,0,to_char((sum(reloads)/sum(pins))*100,999.999))||'%'
    into h_char,h_char2
    from v$librarycache;
    dbms_output.put_line('.'||h_char);
    dbms_output.put_line('.           ..............................................');
    dbms_output.put_line('.           '||h_char2);
    dbms_output.put_line('.');
end;
/


declare
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        result1         varchar2(50);


begin
    dbms_output.put_line  ('==========================================');
        dbms_output.put_line('.    SHARED POOL: DATA DICTIONARY (V$ROWCACHE)');
    dbms_output.put_line   ('==========================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The row cache ratio should be < 10% or 15%' );
        dbms_output.put_line('.');
        dbms_output.put_line('.         Recommendation: Increase SHARED_POOL_SIZE '||result1);
        dbms_output.put_line('.');


        select sum(gets) "gets", sum(getmisses) "misses", round((sum(getmisses)/sum(gets))*100 ,3)
        into h_num1,h_num2,h_num3
        from v$rowcache;


        dbms_output.put_line('.');
        dbms_output.put_line('.             Gets sum: '||h_num1);
        dbms_output.put_line('.        Getmisses sum: '||h_num2);


        dbms_output.put_line('         .......................................');
        dbms_output.put_line('.        Your row cache ratio is '||h_num3||'%');


end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        h_num4          number(25);
        result1         varchar2(50);
begin
    dbms_output.put_line('.');
    dbms_output.put_line ('===============================================');
        dbms_output.put_line('.          BUFFER CACHE (V$SYSSTAT)');
    dbms_output.put_line ('===============================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The buffer cache ratio should be > 70% ');
        dbms_output.put_line('.');
Begin
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 125;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    result1 := 'Unknown parameter';
END;
        dbms_output.put_line('.          Recommendation: Increase DB_BLOCK_BUFFERS '||result1);
        dbms_output.put_line('.');


        select lpad(name,15)  ,value
        into h_char,h_num1
        from v$sysstat
        where name ='db block gets';
        dbms_output.put_line('.         '||h_char||': '||h_num1);


        select lpad(name,15)  ,value
        into h_char,h_num2
        from v$sysstat
        where name ='consistent gets';
        dbms_output.put_line('.         '||h_char||': '||h_num2);


        select lpad(name,15)  ,value
        into h_char,h_num3
        from v$sysstat
        where name ='physical reads';
        dbms_output.put_line('.         '||h_char||': '||h_num3);


        h_num4:=round(((1-(h_num3/(h_num1+h_num2))))*100,3);


        dbms_output.put_line('.          .......................................');
        dbms_output.put_line('.          Your buffer cache ratio is '||h_num4||'%');


    dbms_output.put_line('.');
end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);


        cursor buff2 is
        SELECT name
                ,consistent_gets+db_block_gets, physical_reads
                ,DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null)
                ,to_char((1-physical_reads/(consistent_gets+db_block_gets))*100, 999.999))
        FROM v$buffer_pool_statistics;
begin
     dbms_output.put_line  ('==========================================');
        dbms_output.put_line('.  BUFFER CACHE (V$buffer_pool_statistics)');
    dbms_output.put_line   ('==========================================');


        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line('Buffer Pool:         Logical_Reads     Physical_Reads        HIT_RATIO');
        dbms_output.put_line('.');


        open buff2;
        loop
            fetch buff2 into h_char, h_num1, h_num2, h_num3;
            exit when buff2%notfound;


   dbms_output.put_line(rpad(h_char, 15, '.')||'         '||lpad(h_num1, 10, ' ')||'         '||
    lpad(h_num2, 10, ' ')||'       '||lpad(h_num3, 10, ' '));


        end loop;
        close buff2;


    dbms_output.put_line('.');
end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        result1         varchar2(50);


        cursor c2 is
        select name,value
        from v$sysstat
        where name in ('sorts (memory)','sorts (disk)')
        order by 1 desc;


begin
  dbms_output.put_line  ('==============================================');
        dbms_output.put_line('.       SORT STATUS (V$SYSSTAT)');
dbms_output.put_line  ('==============================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: Very low sort (disk)' );
        dbms_output.put_line('.');
        BEGIN
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 320;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    result1 := 'Unknown parameter';
    END;
        dbms_output.put_line('           Recommendation: Increase SORT_AREA_SIZE '||result1);
        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line(rpad('Name',30)||'Count');
        dbms_output.put_line(rpad('-',25,'-')||'     -----------');


        open c2;
        loop
        fetch c2 into h_char,h_num1;
        exit when c2%notfound;
                dbms_output.put_line(rpad(h_char,30)||h_num1);
        end loop;
        close c2;
end;
/


prompt .
prompt =============================================================
prompt .          TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)
prompt =============================================================
column Tablespace format a30
column Size format 999,999,999,999
column Used format 999,999,999,999
column Free format 999,999,999,999
column "% Used" format 999.99
select tablespace_name "Tablesapce",
        bytes "Size",
        nvl(bytes-free,bytes) "Used",
        nvl(free,0) "Free",
        nvl(100*(bytes-free)/bytes,100) "% Used"
  from(
select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
FROM (select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_name(+)
group by ddf.tablespace_name, ddf.bytes)
  order by 5 desc;


set feedback off
set heading off
select rpad('Total',30,'.') "Tablespace",
  sum(bytes) "Size",
        sum(nvl(bytes-free,bytes)) "Used",
        sum(nvl(free,0)) "Free",
        (100*(sum(bytes)-sum(free))/sum(bytes)) "% Used"
  from(
select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
FROM (select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_name(+)
group by ddf.tablespace_name, ddf.bytes);


set feedback on
set heading on


prompt .
prompt ========================================================
prompt .       FREE SPACE FRAGMENTATION (DBA_FREE_SPACE)
prompt ========================================================
column Tablespace format a30
column "Available Size" format 99,999,999,999
column "Fragmentation" format 99,999
column "Average Size" format 9,999,999,999
column "   Max" format 9,999,999,999
column "   Min" format 9,999,999,999
select tablespace_name Tablespace,
count(*) Fragmentation,
sum(bytes) "Available Size",
avg(bytes) "Average size",
max(bytes) Max,
min(bytes) Min
from dba_free_space
group by tablespace_name
order by 3 desc ;


prompt .
prompt ============================================================
prompt .      SUMMARY OF INVALID OBJECTS (DBA_OBJECTS)
prompt ============================================================


select owner, object_type, substr(object_name,1,30) object_name, status
from dba_objects
where status='INVALID'
order by object_type;


prompt .
prompt ============================================================
prompt .       LAST REFRESH OF SNAPSHOTS (DBA_SNAPSHOTS)
prompt ============================================================


select owner, name, last_refresh
from dba_snapshots
where last_refresh < (SYSDATE - 1);




prompt .
prompt ============================================================
prompt .               LAST JOBS SCHEDULED (DBA_JOBS)
prompt ============================================================


set arraysize 10
set linesize 65
col what format a65
col log_user format a10
col job format 9999
select job, log_user, last_date, last_sec, next_date, next_sec,
failures, what
from dba_jobs
where failures > 0;


set linesize 100


prompt .
prompt ============================================================
prompt .     ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)
prompt ============================================================
column Tablespaces format a30
column Segment format a40
column "NEXT Needed" format 999,999,999
column "MAX Available" format 999,999,999
select a.tablespace_name "Tablespaces",
a.owner "Owner",
a.segment_name "Segment",
a.next_extent "NEXT Needed",
b.next_ext "MAX Available"
  from sys.dba_segments a,
(select tablespace_name,max(bytes) next_ext
from sys.dba_free_space
group by tablespace_name) b
 where a.tablespace_name=b.tablespace_name(+)
   and b.next_ext < a.next_extent;


prompt =============================================================
prompt .        WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)
prompt =============================================================
column Tablespace format a30
column Segment format a40
column Used format 9999
column Max format 9999
select tablespace_name "Tablespace",
owner "Owner",
segment_name "Segment",
extents "Used",
max_extents "Max"
  from sys.dba_segments
 where (extents/decode(max_extents,0,1,max_extents))*100 > 70
   and max_extents >0;


prompt ============================================================
prompt .          LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)
prompt ============================================================
column Tablespace_ext format a30
column Segment format a40
column Count format 9999
break on "Tablespace_ext" skip 1
select tablespace_name "Tablespace_ext" ,
owner "Owner",
segment_name    "Segment",
count(*)        "Count"
  from sys.dba_extents
 group by tablespace_name,owner,segment_name
 having count(*)>12
 order by 1,3 desc;


prompt =============================================================
prompt End of Report


spool off
/* Remove  spool scripts */


Find the attached output of the above script  :  http://www.box.net/shared/zu755erl5s6nqg8am7nv


Enjoy     :-)