Re: need help with script

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Wed, 23 Sep 2009 07:31:20 -0600
Message-ID: <4ABA2328.8010702_at_optimaldba.com>

DBA_TAB_COLUMNS also includes columns from views. To see just the tables, you need to use DBA_TABLES.

SYS_at_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,

> Mohammed

>

>

> On Wed, Sep 23, 2009 at 6:41 PM, Helen Mitchell 

> <HelenJMitchell_at_comcast.net <mailto:HelenJMitchell_at_comcast.net>> 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.

>

>




--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 23 2009 - 08:31:20 CDT