-
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?
-
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.
-
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?
-
is is possible that tran_Details is a synonym that points to something else?
Jeff Hunter
-
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?
-
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
-
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
-
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
-
You're not running 8.1.5 by any chance, are you?
Jeff Hunter
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|