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@(protected)] Sent: Wednesday, February 09, 2005 7:40 PM To: 'bcoulam@(protected)' Cc: 'oracle-l@(protected)' 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@(protected)> 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@(protected) ************************************************************************* 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
