-
Hello,
I looking for the script which will show the highly fragmented tables in the database.
Thanks,
Seenu
-
analyze the schema and then:
select * from dba_tables where chain_cnt > 0;
- Magnus
-
Hello Magnus ,
The script which you have provided will give us the table information which has chained rows, but there are some tables which has fragmentation may not have chained rows.
Thanks,
Seenu
-
you mean migrated rows?
define FRAGMENTATION and I'll be more specific for you.
- Magnus
-
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) )
FROM dba_segments
GROUP BY segment_name)
-
number of extents does NOT affect fragmentation, define what do you mean by fragmentation?
-
thanks for the backup Pando!
;-)
-
I though fragmentation was the number of extents?
That's why it has it's own view (dba_extents).
I guess it could be exanded to segments, since a segment could be made up of multiple extents, but I don't know if I want to be that generous.
select segment_name,count(*) from dba_extents
where segment_type='TABLE'
group by segment_name;
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
fragmentation is based on those free extents not contiguos and one here one there you see them everywhere but just not contiguos! (and even after coalesce there are loads of them)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|