Resending.. Looks like, over quoting.. On Tue, Mar 10, 2009 at 9:13 AM, Riyaj Shamsudeen < riyaj.shamsudeen@xxxxxxxxx> wrote: > While I agree fully that 400 columns in a table is bit excessive, but > E-Business suite 11i is quite famous for this issue. For example, there are > tables with 729 columns. > > [ Sorry for long winded SQL. Due to dictionary size access to dba_ views > hangs and so accessing underlying tables directly. ] > > select /*+ no_merge (col1) */ o.obj#, name, namespace , > decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', > 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', > 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', > 11, 'PACKAGE BODY', 12, 'TRIGGER', > 13, 'TYPE', 14, 'TYPE BODY', > 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, > 'LOB', > 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', > 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA > RESOURCE', > 32, 'INDEXTYPE', 33, 'OPERATOR', > 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', > 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', > 42, 'MATERIALIZED VIEW', > 43, 'DIMENSION', > 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', > 48, 'CONSUMER GROUP', > 51, 'SUBSCRIPTION', 52, 'LOCATION', > 55, 'XML SCHEMA', 56, 'JAVA DATA', > 57, 'SECURITY PROFILE', 59, 'RULE', > 62, 'EVALUATION CONTEXT', > 'UNDEFINED') ,cnt > from ( > select * from > ( > select obj#, count(*) cnt from sys.col$ group by obj# having > count(*)>150 > order by 2 desc > ) where rownum <100 ) col1 , > sys.obj$ o > where col1.obj#=o.obj# > and o.type#=2 > / > > OBJ# NAME NAMESPACE DECODE(O.TYPE#,0,' > COL CNT > ---------- ------------------------------ ---------- ------------------ > ---------- > 163169 EDWCMPALLCLASSES 1 > TABLE 729 > 5117656 IGF_AP_LI_CSS_INTS 1 > TABLE 719 > 196447 IGF_AP_CSS_INTERFACE_ALL 1 > TABLE 716 > 32304 ECE_STAGE 1 > TABLE 519 > 41640 OE_LINE_ACKS 1 > TABLE 507 > 5118251 IGF_AP_LI_ISIR_INTS 1 > TABLE 505 > 5114978 ICX_CAT_ITEMS_GT 1 > TABLE 500 > 5116122 ICX_CAT_UPLOAD_IT_DUMP 1 > TABLE 500 > 5119406 IGF_AP_ISIR_INTS_ALL 1 > TABLE 499 > 172404 EDW_ITEMS_M 1 > TABLE 495 > 196016 IGF_AP_ISIR_INTRFACE_ALL 1 > TABLE 490 > 5111252 CSI_HISTORY_ARCHIVE 1 > TABLE 473 > 41598 OE_LINES_IFACE_ALL 1 > TABLE 467 > > ... > > -- > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com > Specialists in Performance, Recovery and EBS11i > Blog: http://orainternals.wordpress.com