RE: is it possible in pl/sql?

Guang,

Just a suggestion.  You are making this more difficult on yourself just
because you feel that a dbms_stats.gather_table_stats is "more than you
need".  I would suggest that by gathering stats, you will be getting many
more good things than you think.  And this problem goes away completely
because the num_rows column in user_tables will be populated.  You didn't
mention what kind of database this is (OLTP, Warehouse), but gathering stats
is becoming a standard thing to do for most databases.  And if you turn
table monitoring on, you would be able to gather stats for just those tables
that had a percentage of updates applied.

I would rethink your position.  Just my 2 cents.

Tom

-----Original Message-----
From: Guang Mei [mailto:GMei@xxxxxx] 
Sent: Wednesday, February 09, 2005 7:40 PM
To: 'bcoulam@xxxxxxxxx'
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: is it possible in pl/sql?

To make a long story short, This is a script that we run after we do some
"data conversion" (move data from one schema to another after massaging the
data), so this script will run only once (not every day), and reports the
total count in each table in the new schema. I could do "analyze table ..."
to get the rowcount for each table in the new schema, but that's more than
what I need. I am hoping to get each table's rowcount (and rowcount only) in
one sql, if possible.

Thanks.

Guang



-----------
First begin by what you're trying to accomplish. 

Due to the column "new_rows", it looks like you are trying to track 
how many new rows were added to every table during the day. But that 
can't be right because all the dynamic SELECT does is get the total 
rows in the table. Basically, I could guess all day, but it won't do 
much good. 

Why are you selecting the total count? 
Are you trying a home-grown method of auditing your DML activity? 
What do you do with the information you collect in "myTables"? 

My best guess is that you are writing an auditing mechanism that is 
already handled by a number of built-facilities in the database. You 
could be analyzing your tables nightly and then checking 
user_tables.num_rows for the totals. You could be using MONITORING on 
your tables and then checking user_tab_modifications during the night. 
You could... 

Better info on your requirements equals better solutions. 

- bill c. 

On Wed, 9 Feb 2005 16:16:30 -0500, Guang Mei <GMei@xxxxxx> wrote: 
> -- oracle 9i 
> -- code works something like this now: 
> 
> CURSOR c_user_tables  IS 
> SELECT table_name FROM user_tables ; 
> 
> begin 
> FOR x  IN c_user_tables  LOOP 
>     BEGIN 
>              SELECT table_name INTO dummy FROM myTables 
>              WHERE table_name = x.Table_Name; 
>                sqlstmt := 'UPDATE myTables SET new_Rows = (select count(*)

> from ' || x.Table_Name||  ') WHERE Table_Name = ' || x.Table_Name||'; 
>                -- execute dynamic sql 
>    END; 
> END LOOP; 
> end; 
> / 
> 
> Is there a way to get rid off the cursor looping. And 
> 
> 1. I don't want to use dynamic sql 
> 2. I don't want to analyze user_tables 
> 3. I want to update myTables is one sql statment, prefer using static sql.

> 
> Possible? 
> 
> TIA. 
> 
> Guang 
> 
> ************************************************************************* 
> PRIVILEGED AND CONFIDENTIAL: 
> This communication, including attachments, is for the exclusive use of 
> addressee and may contain proprietary, confidential and/or privileged 
> information.  If you are not the intended recipient, any use, copying, 
> disclosure, dissemination or distribution is strictly prohibited.  If you 
> are not the intended recipient, please notify the sender immediately by 
> return e-mail, delete this communication and destroy all copies. 
> ************************************************************************* 
> -- 
> http://www.freelists.org/webpage/oracle-l 
> 



-- 
bill coulam 
bcoulam@xxxxxxxxx 
************************************************************************* 
PRIVILEGED AND CONFIDENTIAL: 
This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information.  If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited.  If you
are not the intended recipient, please notify the sender immediately by
return e-mail, delete this communication and destroy all copies.  
*************************************************************************
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: