Analyze tables by loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Analyze tables by loop

  1. #1
    Join Date
    Sep 2001
    Posts
    200

    Analyze tables by loop

    hey all,
    Can someone take a look at this a tell me what my sins of commission or ommission are

    Thanks

    SQL> begin
    for rec in (select table_name from user_tables where TRUNC(last_analyzed) =('20-DEC-2004')
    loop
    dbms_stats.gather_table_stats(tabname => rec.table_name, degree =>2, estimate_percent => 5, cascade => true);
    end loop;
    end;
    /
    dbms_stats.gather_table_stats(tabname => rec.table_name, degree =>2, estimate_percent => 5, cascade => true);
    *
    ERROR at line 4:
    ORA-06550: line 2, column 91:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 2, column 12:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 4, column 109:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    loop
    Life is what is happening today while you were planning tomorrow.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Line 2 is missing a closing paren. also don't rely on the date format ... use a to_date() and a format picture on the date literal.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Plus, OWNNAME is missing in DBMS_STATS.GATHER_TABLE_STATS proc.

    Tamil

  4. #4
    Join Date
    Sep 2001
    Posts
    200
    Quote Originally Posted by tamilselvan
    Plus, OWNNAME is missing in DBMS_STATS.GATHER_TABLE_STATS proc.

    Tamil
    thanks all
    But I thought since I was going as the table owner (user_tables) I do not need the ownname?
    Life is what is happening today while you were planning tomorrow.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by ndisang
    thanks all
    But I thought since I was going as the table owner (user_tables) I do not need the ownname?
    See if you get an error, w/o OWNNAME.

    Tamil

  6. #6
    Join Date
    Sep 2001
    Posts
    200
    Quote Originally Posted by slimdave
    Line 2 is missing a closing paren. also don't rely on the date format ... use a to_date() and a format picture on the date literal.
    Thanks a bundle....
    Life is what is happening today while you were planning tomorrow.

  7. #7
    Join Date
    Sep 2001
    Posts
    200
    Quote Originally Posted by tamilselvan
    See if you get an error, w/o OWNNAME.

    Tamil
    Sure, it needed the ownname......thanks again...
    Life is what is happening today while you were planning tomorrow.

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