clear col
set head off
set pause off
set pages 0
set verify off
set feedback off

clear breaks
clear compute

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 130 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'Sizing Data - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set head on
set verify on
set feedback on
col table_name format a30
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990
spool all_sizing.rslt

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                                                                     */
/*  Name:    all_sizing.sql                                            */
/*                                                                     */
/* this script will show various database extent parameters.           */
/*  SECTION 1 - Show all Tables(storage parameters)                    */
/*  SECTION 2 - Show all Indexs(storage parameters)                    */
/*                                                                     */
/* ******************************************************************* */

set echo off;

column TableName    format A18        heading 'Table|Name'
column IndexName    format A19        heading 'Index|Name'
column Tablespace   format A9        heading 'Tblspace'
column ObjSize      format 9,999.999  heading 'Total|Size|(MB)'
column InitialExt   format 999.999    heading 'Initial|Extent|(MB)'
column NextExt      format 999.999    heading 'Next|Extent|(MB)'
column InitTrans    format 999        heading 'Init|Trans'
column FreeList     format 999        heading 'Free|List'

set echo on

/* **************************************************** */
/*  SECTION 1 - Show all Tables(space requirements)     */
/* **************************************************** */

set echo off;
break on "TableName" on "IndexName";

select A.Table_name                              TableName,
       A.tablespace_name                                     Tablespace,
       substr(to_char(B.bytes/(1024*1024), '9,999.999'), 1, 12)
                                                             ObjSize,
       substr(to_char(A.initial_extent/(1024*1024), '9,999.999'), 1, 10)
                                                             InitialExt,
       substr(to_char(A.next_extent/(1024*1024), '999.999'), 1, 8)
                                                             NextExt,
  substr(to_char(A.ini_trans,'999'),1,4)   InitTrans,
  substr(to_char(A.freelists,'999'),1,4)   FreeList,
       decode(trunc(A.initial_extent/(1024*1024)),0,' ', '(set INIT=4;FREE=16)') Action
from   dba_tables   A,
       dba_segments B
where A.owner = 'PSOFT'
and B.owner = A.owner
and B.segment_name = A.table_name
and B.segment_type = 'TABLE'
order by 1, 2;

set echo on;
 

/* **************************************************** */
/*  SECTION 2 - Show all Indexs(space requirements)     */
/* **************************************************** */

set echo off;

select A.Table_name                              TableName,
       A.Index_name                              IndexName,
       A.tablespace_name                                     Tablespace,
       substr(to_char(B.bytes/(1024*1024), '9,999.999'), 1, 12)
                                                             ObjSize,
       substr(to_char(A.initial_extent/(1024*1024), '9,999.999'), 1, 10)
                                                             InitialExt,
       substr(to_char(A.next_extent/(1024*1024), '999.999'), 1, 8)
                                                             NextExt,
  substr(to_char(A.ini_trans,'999'),1,4)   InitTrans,
  substr(to_char(A.freelists,'999'),1,4)   FreeList,
       decode(trunc(A.initial_extent/(1024*1024)),0,' ', '(set INIT=4;FREE=16)') Action
from   dba_indexes  A,
       dba_segments B
where A.owner = 'PSOFT'
and B.owner = A.owner
and B.segment_name = A.index_name
and B.segment_type = 'INDEX'
order by 1, 2;

clear breaks;
clear compute;
set head on;
set pause off;
spool off;
ttitle off;
commit;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips