REM Script to list all tablespaces and its characteristics REM Version 8i REM REM Biju Thomas - Jun 2001 REM SET VERIFY OFF ECHO OFF PAGES 999 LINES 80 FEEDBACK OFF TTITLE "ALL TABLESPACES INFORMATION" COL TABLESPACE_NAME FORMAT A25 COL EXT_SIZE FORMAT A17 HEADING "EXT_SIZE I/N/M" COL EXT_LIMITS FORMAT A18 HEADING "EXT_LIMITS N/X/P" COL STATUS FORMAT A1 HEADING S COL CONTENTS FORMAT A1 HEADING T COL LOGGING FORMAT A1 HEADING L COL EXTENT_MANAGEMENT FORMAT A1 HEADING M COL PLUGGED_IN FORMAT A1 HEADING P COL ALLOCATION_TYPE FORMAT A1 HEADING A SPOOL /tmp/tsall8i.lst SELECT TABLESPACE_NAME , (INITIAL_EXTENT/1024 ||' /' || NEXT_EXTENT/1024 || ' /' || MIN_EXTLEN/1024) EXT_SIZE, (MIN_EXTENTS || ' /' || DECODE(MAX_EXTENTS,2147483645,'UNL',MAX_EXTENTS) || ' /' || PCT_INCREASE) EXT_LIMITS, DECODE(STATUS, 'ONLINE','O', 'OFFLINE','F', 'READ ONLY','R','U') STATUS, DECODE(CONTENTS, 'PERMANENT','P', 'TEMPORARY','T', 'O') CONTENTS, DECODE(LOGGING, 'LOGGING','Y','N') LOGGING, SUBSTR(EXTENT_MANAGEMENT, 1,1) EXTENT_MANAGEMENT, DECODE(ALLOCATION_TYPE, 'USER','U', 'SYSTEM','S', 'UNIFORM','F', 'O') ALLOCATION_TYPE, SUBSTR(PLUGGED_IN, 1,1) PLUGGED_IN FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME; PROMPT PROMPT EXT_SIZE => I-INITIAL, N-NEXT, M-MINIMUM_EXTENT PROMPT EXT_LIMITS => N-MIN, X-MAX, P-PCTINCREASE PROMPT S: STATUS => O-ONLINE, F-OFFLINE, R-READ ONLY, U-UNKNOWN PROMPT T: CONTENTS => T-TEMPORARY, P-PERMANENT, O-OTHER PROMPT L: LOGGING => Y-YES, N-NO PROMPT M: MANAGEMENT => L-LOCAL, D-DICTIONARY PROMPT A: ALLOCATION => U-USER, S-SYSTEM, F-UNIFORM PROMPT P: PLUGGED IN => Y-YES, N-NO SPOOL OFF PROMPT PROMPT Output saved at /tmp/tsall8i.lst SET VERIFY ON ECHO OFF PAGES 24 LINES 80 FEEDBACK ON TTITLE OFF