Home » RDBMS Server » Server Administration » Table Scan: (stale) obj#
Table Scan: (stale) obj# [message #283587] Tue, 27 November 2007 09:48 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I query v$session_longops to view the progress of particular running processs:

select sid, message from v$session_longops
where sid = &sid
order by start_time; 


I then enter in the appropriate sid to attain the results. One result for the sid 14 is:
14 Table Scan:  (stale) obj# 671097: 92409 out of 92409 Blocks done


How can I find out what 'obj# 671097' is? I've queried dba_objects and it's neither the object_id or data_object_id.

Thanks.
Re: Table Scan: (stale) obj# [message #283589 is a reply to message #283587] Tue, 27 November 2007 10:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Did you check SQL_TEXT in v$sqlarea view ?
select distinct b.sql_text
from v$session a, v$sqlarea b , v$session_longops c
where a.sid = c.sid
and a.sql_hash_value = b.hash_value
and c.sid='&sid';
Re: Table Scan: (stale) obj# [message #283594 is a reply to message #283589] Tue, 27 November 2007 10:20 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
sql_text in v$sqlarea gives me the tables listed in the query being executed by that particular sid. I can view last_analyzed in dba_tables for those tables to see if they have valid statistics. However, how do I determine which object is 'obj# 671097'?

Thanks.
Re: Table Scan: (stale) obj# [message #283595 is a reply to message #283587] Tue, 27 November 2007 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe a temporary object.
Check sys.obj$ instead of dba_objects.

Regards
Michel
Re: Table Scan: (stale) obj# [message #283597 is a reply to message #283595] Tue, 27 November 2007 10:24 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
No luck....
SQL> select name from sys.obj$ where obj# = '671097';

no rows selected

SQL> select name from sys.obj$ where dataobj# = '671097';

no rows selected

unless I'm querying the wrong columns in the view.
Re: Table Scan: (stale) obj# [message #283606 is a reply to message #283597] Tue, 27 November 2007 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what was the sql text?

Regards
Michel
Re: Table Scan: (stale) obj# [message #283607 is a reply to message #283606] Tue, 27 November 2007 12:01 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
SQL> select distinct b.sql_text
from v$session a, v$sqlarea b , v$session_longops c
where a.sid = c.sid
and a.sql_hash_value = b.hash_value
and c.sid='&sid';
Enter value for sid: 14
old   5: and c.sid='&sid'
new   5: and c.sid='14'


SQL_TEXT
-----------------------------------------------------------------
SELECT M.MEMBERID, MB.MEMBERID, M.EMAILADDRESS, M.WEBSITEID, M.ADDEDDATE_ID 
FROM DB1.TMP_MEMADD M, DB1.MEMBERS MB 
WHERE M.EMAILADDRESS = MB.EMAILADDRESS AND M.WEBSITEID = MB.WEBSITEID

[Updated on: Tue, 27 November 2007 12:06] by Moderator

Report message to a moderator

Re: Table Scan: (stale) obj# [message #283608 is a reply to message #283607] Tue, 27 November 2007 12:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet TMP_MEMADD is a GTT (Global Temporary Table).
So segment no more exists after transaction/session ends.

Regards
Michel
Previous Topic: Autoextend Datafile
Next Topic: Oracle replication and version upgrade
Goto Forum:
  


Current Time: Sun Dec 04 08:35:26 CST 2016

Total time taken to generate the page: 0.07534 seconds