DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Analyze Not Working?

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Analyze Not Working?

    I am logged in as the table owner and I do an analyze and it is not analyzing

    my_user@mydb>Analyze table tran_details ESTIMATE statistics SAMPLE 10 PERCENT;

    Table analyzed.

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANAL
    ---------
    03-NOV-04

    my_user@mydb>Analyze table MY_USER.tran_details ESTIMATE statistics SAMPLE 10 PERCENT;

    Table analyzed.

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANAL
    ---------
    03-NOV-04

    my_user@mydb>


    I then granted analyze any to the user and reran the same commands and it is still not analyzing?? Any ideas?

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    Is it possible that you have DD-MON-YY, and you are somwhere in Asia?
    What is the timestamp on the server?
    I remember when this place was cool.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Mr.Hanky
    Is it possible that you have DD-MON-YY, and you are somwhere in Asia?
    So this "somwhere in Asia" is supposed to be about 1 month back in time form New Joisey?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    is is possible that tran_Details is a synonym that points to something else?
    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."

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    is is possible that tran_Details is a synonym that points to something else?
    No, not a logical explanation. Table names from a local schema are resolved before the public synonyms. So those "ANALYZE TABLE ... " and "SELECT FROM USER_TABLES..." should by all means reference the same object (owned table).

    But there might be a pinch of salt in Mr.H's suggestion! Lesstjm, what is your server's sysdate right now? Can you post the result from
    Code:
    ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
    SELECT SYSDATE FROM DUAL;
    And do perform the sequence of the ANALYZE-SELEC_FROM_USER_TABLES-ANALYZE-SELECT commands that you posted before with the above NLS_DATE_FORMAT set and let us see the results.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2001
    Posts
    515

    Results

    The database is in NY and yes there is a synonym. I checked this out beforew I posted. The synonym points to my_user.tran_details table.

    Here are the results requested:

    my_user@mydb>ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

    Session altered.

    my_user@mydb>SELECT SYSDATE FROM DUAL;

    SYSDATE
    --------------------
    03-DEC-2004 07:44:36

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANALYZED
    --------------------
    03-NOV-2004 14:44:45

    my_user@mydb>Analyze table tran_details ESTIMATE statistics SAMPLE 10 PERCENT;

    Table analyzed.

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANALYZED
    --------------------
    03-NOV-2004 14:44:45

    my_user@mydb>Analyze table my_user.tran_details ESTIMATE statistics SAMPLE 10 PERCENT;

    Table analyzed.

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANALYZED
    --------------------
    03-NOV-2004 14:44:45

    my_user@mydb>Analyze table MY_USER.tran_details COMPUTE STATISTICS;

    Table analyzed.

    my_user@mydb>select last_analyzed from user_tables where table_name = 'TRAN_DETAILS';

    LAST_ANALYZED
    --------------------
    03-NOV-2004 14:44:45

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    as system, show us:
    Code:
    analyze table my_user.tran_Details compute statistics
    /
    select owner, table_name, last_analyzed
    from dba_tables
    where table_name = 'TRAN_DETAILS'
    /
    select owner, table_name, last_analyzed
    from dba_tables
    where last_analyzed > trunc(sysdate)
    /
    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."

  8. #8
    Join Date
    Jan 2001
    Posts
    515

    System User Results

    system@mydb>ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

    Session altered.

    system@mydb>SELECT SYSDATE FROM DUAL;

    SYSDATE
    --------------------
    03-DEC-2004 09:05:46

    system@mydb>analyze table my_user.tran_details compute statistics;

    Table analyzed.

    system@mydb>select owner, table_name, last_analyzed
    2 from dba_tables
    3 where table_name = 'TRAN_DETAILS';

    OWNER TABLE_NAME LAST_ANALYZED
    ------------------------------ ------------------------------ --------------------
    MY_USER TRAN_DETAILS 03-NOV-2004 14:44:45

    system@mydb>select owner, table_name, last_analyzed
    2 from dba_tables
    3 where last_analyzed > trunc(sysdate)
    4 /

    no rows selected

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You're not running 8.1.5 by any chance, are you?
    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."

  10. #10
    Join Date
    Jan 2001
    Posts
    515

    version

    8.1.7

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