REM Author: Alexander Geldutes, May 2000
REM E-mail: info@dbaoncall.net
REM W-page: http://www.dbaoncall.net
REM -----------------------------------------------
REM  Fragmentation info, rule1:
REM  segments inside one tablespace have the same
REM  extents.
REM -----------------------------------------------
REM Requires db.sql file
REM -----------------------------------------------

SET PAGES 999 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF

TTITLE OFF
CLEAR COLUMN

SPOOL rule1.log

PROMPT
@./Ut/db.sql
PROMPT
PROMPT ****************************************************
PROMPT * Fragmentation: Rule 1 Violation
PROMPT * Tablespaces with wrong default storage parameters
PROMPT ****************************************************

SET TERMOUT OFF

DEFINE mltp_block_read = '0'
DEFINE block_size = '0'

COLUMN "db_block_size" NEW_VALUE block_size

SELECT value AS "db_block_size"
FROM v$parameter
WHERE name = 'db_block_size'
/

COLUMN "db_file_multiblock_read_count" NEW_VALUE mltp_block_read

SELECT value AS "db_file_multiblock_read_count"
FROM v$parameter
WHERE name = 'db_file_multiblock_read_count'
/

SET TERMOUT ON 


COLUMN tablespace_name FORMAT a15 HEADING "Tablespace"
COLUMN initial_extent_kb FORMAT 999,999 HEADING "Initial (K)"
COLUMN next_extent_kb FORMAT 999,999 HEADING "Next (K)"
COLUMN pct_increase FORMAT 999 HEADING "PCT Inc"

SELECT tablespace_name,
  initial_extent/1024 "initial_extent_kb",
  next_extent/1024 "next_extent_kb", 
  pct_increase
FROM dba_tablespaces
WHERE 
  ( 
    MOD( initial_extent, &mltp_block_read * &block_size ) <> 0
    OR next_extent <> initial_extent
    OR pct_increase <> 0
  )
  AND contents = 'PERMANENT'
  AND tablespace_name <> 'SYSTEM'
  AND tablespace_name NOT IN (select tablespace_name from dba_rollback_segs)
ORDER BY tablespace_name
/

PROMPT
PROMPT ***************************************************

PROMPT DB_FILE_MULTIBLOCK_READ_COUNT = &mltp_block_read
PROMPT DB_BLOCK_SIZE (bytes) = &block_size

SPOOL OFF
CLEAR COLUMN

UNDEFINE mltp_block_read
UNDEFINE block_size

SET PAGES 24 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ***************************************************
PROMPT
PROMPT Output saved at rule1.log
PROMPT