--  FILE:   ts_storage8i.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (C) 1999-2000 AppsDBA Consulting. All Rights Reserved.
--
--  DATE:   01/12/1999
--
--  DESCRIPTION:
--          Query to show tablespace storage parameters
--
--  REQUIREMENTS:
--          Oracle8i query
--          
--  MODIFICATIONS:
--          A. Rivenes, 07/03/2000, updated to list extent managment
--            info.
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
PROMPT >      Tablespace storage details  ;
PROMPT >      NOTE: 7.3+ allows unlimited extents, temporary status  ;
PROMPT >      NOTE: 8.0+ allows nologging option ;
PROMPT >      NOTE: 8i allows locally or dictionary managed extent allocation ;
COLUMN tablespace_name    HEADING 'Tablespace'          FORMAT A15    TRUNCATE;
COLUMN iext               HEADING 'Initial(KB)'         FORMAT 999,999,999;
COLUMN next               HEADING 'Next(KB)'            FORMAT 999,999,999;
COLUMN maxx               HEADING 'Max Ext'             FORMAT 9,999,999,999;
COLUMN pct_increase       HEADING 'Pct|Inc'             FORMAT 999;
COLUMN minext             HEADING 'Min Ext|Size(KB)'    FORMAT 9999;
COLUMN status             HEADING 'Status'              FORMAT A9;
COLUMN contents           HEADING 'Contents'            FORMAT A9;
COLUMN logging            HEADING 'Logging'             FORMAT A9;
COLUMN extmgmt            HEADING 'Ext|Mgmt'            FORMAT A10;
COLUMN alloc              HEADING 'Alloc|Type'          FORMAT A9;
COLUMN plug               HEADING 'Plugged|In'          FORMAT A7;
SELECT tablespace_name,
       initial_extent/1024 iext,
       next_extent/1024 next,
       DECODE( max_extents, 2147483645, 'UNLIMITED',
               TO_CHAR(max_extents,'99,999') ) maxx,
       pct_increase,
       min_extlen/1024 minext,
       status,
       contents,
       logging,
       extent_management extmgmt,
       allocation_type alloc,
       plugged_in plug
  FROM dba_tablespaces
/