This happened to me when I was trying to drop a table. It means that the table is being used and locked by another process. Following are the steps to solve such a problem.
1. If you have just executed a non select SQL, do a commit or rollback to let it release the resources that it holds. Then, try to drop the table again. If this does not solve your problem, perform step 2 and step 3 below.
2. Identify the session that holds the resources by executing the following SQL.
SET LINESIZE 100
SELECT s.INST_ID, s.SID, s.SERIAL#, p.SPID, s.USERNAME, s.PROGRAM
FROM gv$session s, gv$process p
WHERE p.addr = s.paddr AND p.inst_id = s.inst_id
AND s.type != 'BACKGROUND';
You will see something like this.
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 77 241 5448 CJOHN JDBC Thin Client
1 163 60667 5466 STAO JDBC Thin Client
1 195 827 8836 STAO JDBC Thin Client
1 233 403 5476 EJAY JDBC Thin Client
1 15 321 5159 EJAY JDBC Thin Client
Use the PROGRAM and USERNAME to identify the SPID that holds the resources.
3. Kill the process that holds the resources
At your command line type kill -9 <spid> and then return.
----------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
1. If you have just executed a non select SQL, do a commit or rollback to let it release the resources that it holds. Then, try to drop the table again. If this does not solve your problem, perform step 2 and step 3 below.
2. Identify the session that holds the resources by executing the following SQL.
SET LINESIZE 100
SELECT s.INST_ID, s.SID, s.SERIAL#, p.SPID, s.USERNAME, s.PROGRAM
FROM gv$session s, gv$process p
WHERE p.addr = s.paddr AND p.inst_id = s.inst_id
AND s.type != 'BACKGROUND';
You will see something like this.
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 77 241 5448 CJOHN JDBC Thin Client
1 163 60667 5466 STAO JDBC Thin Client
1 195 827 8836 STAO JDBC Thin Client
1 233 403 5476 EJAY JDBC Thin Client
1 15 321 5159 EJAY JDBC Thin Client
Use the PROGRAM and USERNAME to identify the SPID that holds the resources.
3. Kill the process that holds the resources
At your command line type kill -9 <spid> and then return.
----------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
No comments:
Post a Comment