SQL ScriptTable/Index Extents within tablespace |
This script first gives a list of tablespaces. It then prompts to enter a tablespace name and a minimum value of extents. The script then lists all tables and indexes with the the number of extents greater than the value entered. This is a MUST script for all production DBA's. It can easily be amended to be put in a nightly schedule. You can spend a lot of cash on database monitoring utilities to do this for you, but it's very simple !!
&tspace - The tablespace to report on
&min_ext - the minimum number of extents
SET PAGES 999 SET FEEDBACK OFF SET HEADING ON SET PAGESIZE 999 SET LINES 79 SET VERIFY OFF col segment_type FORMAT a5 heading 'Type' col segment_name FORMAT A25 HEADING 'Seg Name' col partition_name FORMAT A25 HEADING 'Part Name' col iniMb FORMAT 9999.9 HEADING 'Intl(M)' col nxtMb FORMAT 9999.9 HEADING 'Next(M)' col iniKb FORMAT 9999.9 HEADING 'Intl(K)' col nxtKb FORMAT 9999.9 HEADING 'Next(K)' col extents FORMAT 999 HEADING 'CNT' Listing Tablespaces ... select tablespace_name from dba_tablespaces; ACCEPT tspace prompt 'Enter Tablespace name: ' ACCEPT min_ext prompt 'Enter Minimum Value of Extents : ' BREAK ON REPORT COMPUTE SUM OF sum(bytes) ON REPORT spool extentst.lst SELECT segment_type ,segment_name ,initial_extent/(1024*1024) iniMb ,next_extent/(1024*1024) nxtMb ,initial_extent/1024 iniKb ,next_extent/1024 nxtKb ,extents ,sum(bytes) FROM dba_segments WHERE segment_type IN ('TABLE','INDEX') AND tablespace_name = UPPER('&tspace') AND extents >= &min_ext GROUP BY segment_type,segment_name,initial_extent,next_extent,extents / PROMPT PROMPT Partitions PROMPT col segment_type form a20 heading "Segment Type" SELECT segment_type ,partition_name ,initial_extent/(1024*1024) iniMb ,next_extent/(1024*1024) nxtMb ,extents ,sum(bytes) FROM dba_segments WHERE segment_type IN ('TABLE PARTITION','INDEX PARTITION') AND tablespace_name = UPPER('&tspace') AND extents >= &min_ext GROUP BY segment_type,partition_name,initial_extent,next_extent,extents / spool off CLEAR
Table/Index Extents within tablespace
Return to Index of SQL Scripts
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|