SQL ScriptTable/Index extents for a given owner |
This script is very similar to the List all tables/indexes extents in a given tablespace script. The difference being that this list extents detail for a given table owner. It prompts to enter the owner 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 regardless of tablespace. Another MUST script for all production DBA's.
&tspace - The table owner 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 trimspool on 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 extents FORMAT 999 HEADING 'CNT' col ratio format 99.9 heading '%age' ACCEPT owner prompt 'Enter Table owner : ' ACCEPT min_ext prompt 'Enter Minimum Value of Extents.maxextents %age : ' BREAK ON REPORT COMPUTE SUM OF sum(bytes) ON REPORT spool extentst.lst SELECT ,segment_type ,segment_name ,extents ,max_extents ,(extents/max_extents)*100 ratio ,bytes/1024/1024 Mb FROM dba_segments WHERE segment_type IN ('TABLE','INDEX') AND owner = UPPER('&owner') AND extents >= max_extents*&min_ext AND max_extents != 2147483645 order by (EXTENTS/MAX_EXTENTS) desc / PROMPT PROMPT Partitions PROMPT col segment_type form a20 heading "Segment Type" SELECT segment_type ,partition_name ,extents ,(extents/max_extents)*100 ratio ,max_extents ,bytes/1024/1024 Mb FROM dba_segments WHERE segment_type IN ('TABLE PARTITION','INDEX PARTITION') AND owner = UPPER('&owner') AND extents >= max_extents*&min_ext AND max_extents != 2147483645 order by (EXTENTS/MAX_EXTENTS) desc / spool off CLEAR
Table/Index extents for a given owner
Return to Index of SQL Scripts
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|