DBA_TAB_COLUMNS also includes columns from views. To see just the tables, you need to use DBA_TABLES.
SYS@dwf10gr2> l 1 select distinct owner, table_name 2 from dba_tab_columns 3 where (owner, table_name) NOT IN (SELECT owner, table_name 4* FROM dba_tables) OWNER TABLE_NAME ------------------------------ ------------------------------ SYS V_$PGASTAT WMSYS WM_EVENTS_INFO SYS _ALL_REPGROUPED_COLUMN OLAPSYS DBA$OLAP_DESCRIPTOR_TYPES SYS USER_MVIEW_COMMENTS WMSYS ALL_VERSION_HVIEW_WDEPTH OLAPSYS ALL$OLAP_FOREIGN_KEYS SYS USER_ADVISOR_SQLW_JOURNAL <output snipped> Regards, Daniel Fink -- Daniel Fink OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Lost Data? http://www.ora600.be/ Mohammed Mehraj Hussain wrote:
select DISTINCT table_name ,OWNER from dba_tab_columns where OWNER NOT IN ('SYS','SYSTEM')AND DATA_TYPE <>'LONG' Regards, MohammedOn Wed, Sep 23, 2009 at 6:41 PM, Helen Mitchell <HelenJMitchell@xxxxxxxxxxx <mailto:HelenJMitchell@xxxxxxxxxxx>> wrote:I need a script that will select the owner and table names for the user-defined schemas that do not have any columns defined with a datatype = LONG. The script need to skip the table if any column is defined as a datatype = LONG.
