Re: Database copy question 2 ..(HELP!!!)

all_tab_columns includes views also.
Try doing

   SELECT 'UPDATE ' || TABLE_NAME || ' SET COST = 1 ;'    FROM ALL_TAB_COLUMNS
   WHERE COLUMN_NAME = 'COST'
    AND TABLE_NAME IN (SELECT table_name from all_tables);

Be Wary of this statement though (i.e. check all the update clauses) since PLAN_TABLE and RESOURCE_COST$ tables also have a COST column in them,,,, which I suppose you would not want to update.

Anurag

"JK Yao" <jkyf0131_at_hotmail.com> wrote in message news:93f65984.0109070042.5861d059_at_posting.google.com...
> dear all,
>
> After I write an procedure to run what I select from the script :
>
> SELECT 'UPDATE ' || TABLE_NAME || ' SET COST = 1 ;'
> FROM ALL_TAB_COLUMNS
> WHERE COLUMN_NAME = 'COST' ;
>
> I create upgrade some table and the error code is
> ORA-01733: virtual column not allowed here.
> I cannot find those error table, coz there are no such tables.
>
> Question:
> Will the all_tab_columns include objects other than table? It is
> supposed to include just "Table" information, isn't it?
> Please clarify. Thanks a lot.
>
> JK
>
>
>
> Reid Lai <reidlai_at_netvigator.com> wrote in message
news:<3B97A31A.AE8F97CE_at_netvigator.com>...
> > SELECT
> > 'UPDATE ' || TABLE_NAME || ' SET COST = 1 ;'
> > FROM
> > ALL_TAB_COLUMNS
> > WHERE
> > COLUMN_NAME = 'COST' ;
> >
> > JK Yao wrote:
> >
> > > dear all,
> > >
> > > Is there any fast way to set all column called 'cost' to 1? Since I
> > > have 'Cost' column involved in so many table, I need some script of
> > > special way to done my job.
> > >
> > > Please clarify. Thanks a lot.
> > >
> > > I use Oracle8.1.6 in Solaris2.6
> > >
> > > regards,
> > > JK
Received on Fri Sep 07 2001 - 08:31:52 CDT