help with PL/SQL

I am trying to run a PL/SQL to get the table_name , num_rows from user_tables and count(*) for each table and if the difference between num _rows and count(*) for that table is greater than 10000 then I want to display it .

declare
tab varchar2(100);
a number;
b = 2 number ;
c number
begin
loop
b:= b +1;
SELECT table_name into tab FROM user_tables where rownum < a;
SELECT NUM_ROWS INTO b FROM USER_TABLES WHERE TABLE_NAME = 'tab';
select count(*) into c from 'tab';
if b - c > 10000 then
dbms_output.put_line ('tab' , b , c );
exit when b = 30;
end loop;
end;
/

Please let me know what is wrong with this .

Thanks

You need to use dynamic sql to pass table_name.

Example:
CREATE OR REPLACE procedure tamil_upd_sbl_tbls_rowcount as
p_rowcount Number := 0 ;
p_tablesize Number := 0 ;
stmt_str varchar2(4000) ;
p_new_run_id Number := 0 ;
p_tsname varchar2(30);
p_ext Number := 0 ;

begin
---- execute immediate 'truncate table tamil_dba_siebel_tables';
---- get maximum run_id from tamil_dba_siebel_tables
execute immediate
'alter session set optimizer_mode = choose ' ;
select max(run_id) into p_new_run_id
from tamil_dba_siebel_tables;
p_new_run_id := nvl(p_new_run_id,0) + 1; ---- increment by 1
for ctab in ( select table_name as tabname
from user_tables
) Loop
-- dbms_output.put_line(ctab.tabname||' '|| ctab.tabname) ;
stmt_str := 'select /*+ full(a) parallel(a,8) */ count(*) from '||ctab.tabname ||' a';
-- dbms_output.put_line(stmt_str);
execute immediate stmt_str into p_rowcount ;
stmt_str := 'select bytes , tablespace_name, extents from user_segments where '
||'segment_name = :X1 ' ;
execute immediate stmt_str into p_tablesize, p_tsname, p_ext using ctab.tabname ;
---- first, insert tables' details
insert into tamil_dba_siebel_tables
(run_id, object_name, object_type, row_count, bytes, run_date, tablespace_name, extents)
values
(p_new_run_id, ctab.tabname, 'TABLE', p_rowcount, p_tablesize, sysdate, p_tsname, p_ext);
end loop ;
---- next, insert indexes' details
insert into tamil_dba_siebel_tables
(run_id, object_name, object_type, row_count, bytes, run_date, tablespace_name, extents)
select p_new_run_id, segment_name, 'INDEX', 0, bytes, sysdate, tablespace_name, extents
from user_segments
where segment_type = 'INDEX' ;
commit;
exception when others then
dbms_output.put_line('Error encounterd ' );
end;
/

The above procedure does row counts and store in a table.

Tamil

Quote:

---

Originally posted by puneet
I am trying to run a PL/SQL to get the table_name , num_rows from user_tables and count(*) for each table and if the difference between num _rows and count(*) for that table is greater than 10000 then I want to display it .

declare
tab varchar2(100);
a number;
b = 2 number ;
c number
begin
loop
b:= b +1;
SELECT table_name into tab FROM user_tables where rownum < a;
SELECT NUM_ROWS INTO b FROM USER_TABLES WHERE TABLE_NAME = 'tab';
select count(*) into c from 'tab';
if b - c > 10000 then
dbms_output.put_line ('tab' , b , c );
exit when b = 30;
end loop;
end;
/

Please let me know what is wrong with this .

Thanks

---

Let's start with...everything?! Not to be destructive, but I can not correlate what you say you're trying to do with what that code is doing whatsoever.

First, you do a loop and increment the variable 'b', which you then stomp on later on with the line "select num_rows into b from user_tables..." -- and I'm not even sure what you're attempting to accomplish with that line; it asks where table_name = 'tab', and obviously you can only have zero or one tables with the exact name 'tab', maybe you mean where table_name LIKE 'tab%' or maybe you think that 'tab' will use the variable tab, which it won't.

Now you exit when b = 30, which may or may not ever happen given that you stomp its value.

I think you might need to more clearly state your goal, because from your code I can not even derive it; but it looks like you want to do more than what you say? What are you doing a difference for? What is the actual purpose of 'b'? Also, why are you selecting table names where rownum < a?

The objective is to find tables that need to be analyzed . I need to find tables whose num_rows in user_tables are different from their count(*) by more than 10000

Thanks

...or in other terms, how many lines of code can you write to replace existing functionality in Oracle...


gather stale

Got it , Here are the steps

1) create table test_table (id number , table_name varchar2(30) , num_rows number , cnt number ) ;

2) create table test2 select table_name , num_rows from user_tables;

3) insert into test_table (table_name , num_rows ) select (table_name , num_rows ) from test2;

4 ) commit;

5) create sequence test_seq nomaxvalue nocycle ;

6) update table test_table set id = test_seq.nextval;

7 ) commit;

8 ) declare
v_table_name VARCHAR2(30);
v_cnt number ;
v_counter number := 1;
stmt_str varchar2(4000) ;
begin
for v_counter in 1.. 100 loop
select table_name into v_table_name from test_table where id = v_counter;
dbms_output.put_line (v_table_name ) ;
stmt_str := 'select count(*) from '||v_table_name ||'';
dbms_output.put_line(stmt_str);
execute immediate stmt_str into v_cnt ;
update test_table set cnt = v_cnt where table_name = v_table_name ;
dbms_output.put_line(v_cnt);
commit;
end loop;
end ;

9 ) select table_name from test_table where cnt - num_rows > 10000 ;


Thats all , It works

Thanks guys

You could also use these scripts:

http://www.ixora.com.au/scripts/query_opt.htm

:cool: