help with PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: help with PL/SQL

  1. #1
    Join Date
    Nov 1999
    Posts
    226

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16

    Re: help with PL/SQL

    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?

  4. #4
    Join Date
    Nov 1999
    Posts
    226
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ...or in other terms, how many lines of code can you write to replace existing functionality in Oracle...


    gather stale
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 1999
    Posts
    226
    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

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459
    You could also use these scripts:

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

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width