OraFAQ Forum: Server Administration » Long running SELECT that should be erroneous

Home » RDBMS Server » Server Administration » Long running SELECT that should be erroneous (11.2.0.3.0, WIN 2008 R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Long running SELECT that should be erroneous [message #621630]

Fri, 15 August 2014 03:11

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Hello,

first I have to say, that I'm totally new to Oracle DB. I've seen it the first time last week...
Yesterday I stumbled on a behaviour of the software, that I don't really understand and I hope, you can help me.
At the EM performance folder, there is an activity, that runs since a long time (at least since yesterday). Please see the attached screenshots. And there's my first question: Where can I see, at which time it started?
But what I really want to know: Why does it run so long? Normally it has to quit with an error. If I copy the statement to sqlplus it says:

SQL> select version from sys.product_component_version@ods where upper(product) like '%ORACLE%';
select version from sys.product_component_version@ods where upper(product) like '%ORACLE%'
                                                  *
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL>

And that's what I expect (because 'ods' is not set in tnsnames.ora). What can I do now? Can I abort the statement in any way?

Thank you and best regards

Fabain

Attachment: top_activity.png
(Size: 176.40KB, Downloaded 1847 times)

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621631 is a reply to message #621630]

Fri, 15 August 2014 03:15

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

v$session holds SQL_EXEC_START, you'll need to find the sid you're looking for, obviously.

As to why the db link doesnt work, make certain you are using the same account, if it is not the same account you've a good chance of getting unexpected behaviour.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621632 is a reply to message #621631]

Fri, 15 August 2014 03:27

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Thank you for the answer! What do I wrong?

SQL> select sql_exec_start from v$session where sql_id='3hbam82tyc3h2';

SQL_EXEC
--------


SQL>

Relating to the second issue: The statement is running with 'SYS', which is the user I'm working in sqlplus!

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621633 is a reply to message #621632]

Fri, 15 August 2014 03:32

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The statement is running with 'SYS', which is the user I'm working in sqlplus!

NEVER do that.

Read Read SYS is special.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621635 is a reply to message #621633]

Fri, 15 August 2014 03:42

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Michel Cadot wrote on Fri, 15 August 2014 10:32

Quote:
The statement is running with 'SYS', which is the user I'm working in sqlplus!

NEVER do that.

Read Read SYS is special.

Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc... Confused

Confused

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621636 is a reply to message #621635]

Fri, 15 August 2014 04:20

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

So you were given sys but not allowed to create users? Wow...I dont even...just wow.

Anyway, v$session is transient real time data. If you've killed it, you'll lose the data. Depending on your options you might get a rough idea from ASH.

There could be a number of reasons why that has stopped working, though you dont need the tns entry on the server, you can push it into the link description. To be honest though, I'd start by asking the person who ran it in the first place.

Looking at your image, the wait events are extremely unexpected for a query like that. I'd not expect a CPU wait on the issuing side - was the host starved for CPU perhaps? I don't really trust what grid is telling you there. On the remote side, maybe, but not the issuing side.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621637 is a reply to message #621636]

Fri, 15 August 2014 04:35

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Ok, thanks. So there is essentially nothing I can do? The selection was started by the program "Toad", so there is no one I can ask.
Can I kill the selection in any way or will it run until the server will be rebootet?

Roachcoach wrote on Fri, 15 August 2014 11:20

Looking at your image, the wait events are extremely unexpected for a query like that. I'd not expect a CPU wait on the issuing side - was the host starved for CPU perhaps? I don't really trust what grid is telling you there. On the remote side, maybe, but not the issuing side.

Yes, that's another point I wondered about, too...

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621638 is a reply to message #621637]

Fri, 15 August 2014 04:40

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Wait, it is still running? If so, you will be able to locate it in v$session - that will give you machine names and osuer.

Furthermore someone ran it and SYS users should be more than rare (though from what you've said I'm going to guess that might not hold true here). Ask around. Failing that, ASH gives machine (as I recall) if you've got the options.

Yes, you can kill it if it is still running (check the destination as it may not die gracefully there) but if it were me, I'd go round up who was running it and see what is going on.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621639 is a reply to message #621638]

Fri, 15 August 2014 05:01

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Yes, it is still running! How can I locate it in v$session?
It was run by Toad, a management and monitoring tool for Oracle databases. So nobody ran it deliberately.
I can start an ASH Report. But I don't know, what I can read out of it relating my problem. Sorry, I'm really new to Oracle!

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621640 is a reply to message #621639]

Fri, 15 August 2014 05:08

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

TOAD is a gui tool for developers.

Try this

select username, osuser, machine, sid, serial#, program,logon_time, s.sql_id,event, substr(sql_text,1,39) sql_txt from v$session s
left join v$sqlarea sq on s.sql_hash_value=sq.hash_value
where status='ACTIVE'
and s.sid != (select sid from v$mystat where rownum=1)
order by 6,7

You'll need to eyeball it, obviously.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621641 is a reply to message #621640]

Fri, 15 August 2014 06:07

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Thanks for the informative statement, but I'm afraid we talk at cross purposes.
I know the machine, the program and the user. But what can I do now? The program (toad.exe) is not running anymore, I terminated it already yesterday! How can I kill the selection on Oracle?

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621642 is a reply to message #621641]

Fri, 15 August 2014 06:13

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I meant find the user to establish what DB is being used and how.

Get your DBA to kill it.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621643 is a reply to message #621642]

Fri, 15 August 2014 06:20

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

I know the user and the DB.
The DBA is in holiday... Wink

Wink

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621644 is a reply to message #621643]

Fri, 15 August 2014 06:22

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Then I suggest you be very careful and read this

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#SQLRF53115

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621645 is a reply to message #621644]

Fri, 15 August 2014 07:00

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Thank you again. That's what I was looking for.

Result:

SQL> alter system kill session '274, 61841';
alter system kill session '274, 61841'
*
ERROR at line 1:
ORA-00031: session marked for kill


SQL>

And it's still running... Has it become stuck? Is that possible?

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621646 is a reply to message #621645]

Fri, 15 August 2014 07:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Marked for kill means it'll kill it asap.

Usually one sees that because DML is rolling back.

Very occasionally you need to nuke the process at the OS but as you're on windows....I'm out Smile

Smile

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621647 is a reply to message #621646]

Fri, 15 August 2014 07:18

OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany

Junior Member

Ok, perhaps somebody else can help from here?

I did the following:

SQL> select sid, spid from v$process p, v$session s where paddr = addr and s.sid in 274 order by s.sid;

       SID SPID
---------- ------------------------
       274 4796

Now I use the SPID for orakill? Is the following usage right?

orakill SID 4796

(In that case, SID is the instance and not the Session ID, right?)

[Updated on: Fri, 15 August 2014 07:19]

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621649 is a reply to message #621630]

Fri, 15 August 2014 07:53

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

OfflineVIP wrote on Fri, 15 August 2014 03:11

Hello,

<snip>

If I copy the statement to sqlplus it says:
SQL> select version from sys.product_component_version@ods where upper(product) like '%ORACLE%';
select version from sys.product_component_version@ods where upper(product) like '%ORACLE%'
                                                  *
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL>
And that's what I expect (because 'ods' is not set in tnsnames.ora). What can I do now? Can I abort the statement in any way?

Actually that reference to "ods" in the sql is NOT a reference to tnsnames.ora, it is a reference to a database link (query DBA_DB_LINKS). The definition of the database link ODS may then reference an entry in tnsnames, or it may bypass tnsnames by giving the full address spec directly. If it (the db link) references a tns entry (more correctly, a 'net service name') that entry may or may not be "ods", but for sanity's sake in naming conventions, it probably should be.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621652 is a reply to message #621635]

Fri, 15 August 2014 08:20

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

OfflineVIP wrote on Fri, 15 August 2014 04:42

Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc...

What legitimate DBA would give out the SYS password? You now have full access to destroy the database. For fun, I would do it then blame it on the DBA. He will be fired and you will now be the DBA. Fastest way to move up in the company.

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621657 is a reply to message #621637]

Fri, 15 August 2014 10:23

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc..

So you didn't understand what is in the link.
Read it again and again until you understand all what it implies.
ANYTHING is better than using SYS.

Report message to a moderator

Previous Topic:	status of the v$session
Next Topic:	Help about Log file Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 17:03:12 CST 2025