We often need to produce record counts of the particular schema.
Create this procedure, change it as per yr requirements.
right now I am inserting rowcounts in a table with timestamp.
create table table_count(T_owner varchar2(50), T_tablename varchar2(50),
T_rows number);
create or replace procedure count_records is
v_ct number := 0 ;
v_sqlcode number := 0 ;
v_stmt varchar2(90) ;
cursor c_tbl is
select owner, table_name
from sys.dba_tables
where owner = 'SYSADM';
--
r_tbl c_tbl%rowtype;
--
begin
open c_tbl;
loop
fetch c_tbl into r_tbl;
exit when c_tbl%notfound;
v_stmt := 'select count(*) from '||r_tbl.owner||'.'||r_tbl.table_name;
execute immediate v_stmt into v_ct;
v_sqlcode := SQLCODE;
if v_sqlcode = 0
-- An insert into a row count history table should probably be here
then dbms_output.put_line('Table '||r_tbl.owner||'.'||
rpad(r_tbl.table_name,30)||
' count is '||to_char(v_ct,'999999999990')
);
insert into table_count values(r_tbl.owner,r_tbl.table_name,v_ct);
else dbms_output.put_line('Bad return code'||v_sqlcode||
' on select of '||r_tbl.owner||
'.'||r_tbl.table_name
);
end if;
end loop;
close c_tbl;
end;
/
Regards - Chirag
On 10/14/05, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
>
> Manoj,
>
> > > I do not have select access to actual table.
> > Analyze the table, query dba_tables.num_rows.
>
> If you don't want to analyze the table as Dimitre suggests, you can
> check the LAST_ANALYZED column on either dba_tables or all_tables to
> see when the table was last analyzed and you can decided if the
> information is recent enough for your purposes.
> Although, if the table owner doesn't want to give you select
> access, then you are probably out of luck in any case.
>
> Dennis Williams
> --
> http://www.freelists.org/webpage/oracle-l
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 15 2005 - 10:56:53 CDT
