Re: is it possible in pl/sql?
- From: Guang Mei <GMei@xxxxxx>
- To: "'bcoulam@xxxxxxxxx'" <bcoulam@xxxxxxxxx>
- Date: Wed, 9 Feb 2005 19:39:38 -0500
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.
> *************************************************************************
> --
> //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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
