if it took 28 hours to get there -- it might take 28 hours or more to GET BACK.

You are rolling back. Until you roll back -- locks.

Use v$transaction and look at used_ublk to see how fast it is rolling back and how far its got to go.

what you NEED TO DO immediately, while waiting, is get your load code and fill it FULL of calls to dbms_application_info set session longops -- so you can monitor in v$session_longops where you are and how far you have to go so you do not get tempted to kill things again.

A little instrumentation goes a long long long way (and it gives you something to watch)

doesn't seem to apply a bit in this case. totally different scenarios.

pmon does not release the resources.

the SESSION does.

if it took a long time to get to the point where you killed it, it'll take a long time to get back to the point where it began (eg: killing an update that has been running for 30 seconds will take slightly less time to release all resources than will killing an update that has been running for 6 hours. "rollback" takes "time")

that is a bug in your application, nothing to do with Oracle at all.

You are using a connection pool.
You are not committing your transactions.
That is the only thing that could be happening here.
You have a bug in your code.

you have another issue, your keyboard is failing -- it appears to be dropping very relevant letters for communicating to people -- the vowels!


An alter system kill session, applied to a session that was in the middle of a long running transaction, it itself going to "take a while" as it has to now UNDO whatever it had been doing for a potentionally long period of time. (you might want to re-read the answer above, i just paraphrased the entire thing right here again)

query v$transaction and monitor used_ublk

step 1 would be to contact support -- they may have something to say.

can you identify the process in the OS that was associated with that session, killing it might be the only thing (short of a restart) if it is truly stuck.

1) yes, you cannot replace the code of something that is actually *running*

2) this is a short excerpt from "Expert One on One Oracle"

...
A view that is invaluable for looking at this information is the DBA_DDL_LOCKS view. There is no V$ view we can look at. This view is built on the more mysterious X$ tables and by default, it will not be installed in your database. You can install this and other locking views by running the CATBLOCK.SQL script found in the directory $ORACLE_HOME/rdbms/admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example in a single user database I see:

tkyte@TKYTE816> select * from dba_ddl_locks;

session mode mode
id OWNER NAME TYPE held reqe
------- ------ ------------------------------ -------------------- ---- ----
8 SYS DBMS_APPLICATION_INFO Body Null None
8 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Table/Procedure/Type Null None
8 SYS DBMS_OUTPUT Body Null None
8 TKYTE TKYTE 18 Null None
8 SYS DATABASE 18 Null None

6 rows selected.

These are all of the objects my session is 'locking'. I have breakable parse locks on a couple of the DBMS_* packages. These are a side effect of SQL*Plus; it calls DBMS_APPLICATION_INFO, for example. I may see more than one copy of various objects here - this is normal, and just means I have more than one thing I'm using in the shared pool that references these objects. It is interesting to note that in the view, the OWNER column is not the owner of the lock, rather it is the owner of the object being locked. This is why you see many rows with SYS, but they all belong to my session - SYS owns those packages. The other interesting thing to note is the TYPE 18 rows. These are rows that make it not possible to drop that user. If I attempted to drop TKYTE, the DROP would fail attempting to get a lock on the user TKYTE in exclusive mode.
.............

was the error "package state discarded" and we are really dicussing packages?

ora-600 = internal error, isn't supposed to happen, all bets off (eg: a bug)

ora-600 >>> internal error -->>>>> bug ---->>>>>>> all bets off, something that wasn't supposed to happen, did.

fact: proc_a references proc_b

false conclusion: We should NEVER be able to re-compile PROC_B while PROC_A is 
running.


proof:
--------------------- test.sql ----------------------------
@testb

create or replace package demo_pkg_a
as
    procedure p;
end;
/
                                                                                                                                                                                                      create or replace package body demo_pkg_a
as
    procedure p
    is
    begin
        dbms_lock.sleep(30);
        demo_pkg_b.p;
    end;
end;
/
                                                                                                    exec demo_pkg_a.p
--------------------------- testb.sql ---------------
create or replace package demo_pkg_b
as
    procedure p;
end;
/
create or replace package body demo_pkg_b
as
                                                                                                    
g_global number;
                                                                                                    
    procedure p
    is
    begin
        g_global := nvl(g_global,0)+1;
        dbms_output.put_line( 'hello ' || g_global );
    end;
end;
/
--------------------------------------------------------

run test.sql, in session two immediately run testb.sql, session 1 will return with:

ops$tkyte@ORA9IR2> exec demo_pkg_a.p
BEGIN demo_pkg_a.p; END;
 
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
"OPS$TKYTE.DEMO_PKG_B"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "OPS$TKYTE.DEMO_PKG_A", line 7
ORA-06512: at line 1
 


<b>what is ipso facto correcto here is you got an ora-600, and ora-600 = "a bug"</b>
 

works for me

that view shows DML locks...

look towards v$access
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1003.htm#i1398731

it means "something is running that needs this" - meaning typically "someone is running the code"

you cannot recompile a procedure that someone is using.

would not matter - you still will not be able to recompile proc_b

if you try to compile it WHILE running it (make test_child take a long time), you cannot.

#2 would be "if the calling statement is not currently CALLING IT"



as for the last paragraph - it can be hours or days before I respond, it can be seconds before I respond.

As with every question about Oracle - the only answer to "how fast do you respond" is...........


it depends...

what is it marked as? what is its status in v$session

how do you get "not marked killed but is actually killed"??? what got you into that state?


what or how are you "killing" this?

see above

show me how you ascertained it was blocking others with a lock.

a session marked as killed will have an OS process, it will have a session, but all of its resources should be released. It OS process stays there so that when the client that was associated with this session tries a database operation, we can cleanly return ora-28, your session was killed. then and only then will the session totally go away.