Home » SQL & PL/SQL » SQL & PL/SQL » Error Ora -0054 in transaction
Error Ora -0054 in transaction [message #290396] Fri, 28 December 2007 12:42 Go to next message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
I have a problem when execute some SQLs in Transaction. The error throw "ora-00054 - resource busy with nowait specifier'...
Here is what i call in transaction:
-Update some row in Table1
-call a procedure to alter Table2 to rename a columns

(
 sTableName in varchar2, sColumnName varchar2, sColumnNameNew varchar2
)
as
  sTable varchar2(256);
  sSQL varchar2(2000);
  sSchemaName varchar2(256);
begin
  if (FN_MDCS_CHECK_USER() = 0) then
    raise_application_error( -20001, 'You do not have permissions to perform this operation.');
    return;
  end if;

  sSchemaName := sys_context('USERENV', 'CURRENT_SCHEMA');
  sTable := sSchemaName || '.' || sTableName;
  sSQL := 'ALTER TABLE ' || sTable || ' RENAME COLUMN ' || sColumnName || ' TO ' || sColumnNameNew;
  execute immediate(sSQL);
  commit;
  exception
	when others
		then
		rollback;
				raise;
end;



[Edited by DreamzZ] [Corrected the [code] tags ]

[Updated on: Fri, 28 December 2007 12:48] by Moderator

Report message to a moderator

Re: Error Ora -0054 in transaction [message #290399 is a reply to message #290396] Fri, 28 December 2007 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need to lock the table in exclusive mode in order to alter it (this is internally done) but someone else is using it, so the statement is aborted.

By the way, ALTER statement ends the current transaction.

In the end, I don't see any business need to dynamically rename column.
(Wasn't this the purpose of another topic?
[Edit: yes it was: http://www.orafaq.com/forum/m/288543/102589/?srch=rename+column#msg_288543)

Regards
Michel

[Updated on: Fri, 28 December 2007 13:06]

Report message to a moderator

Re: Error Ora -0054 in transaction [message #290403 is a reply to message #290396] Fri, 28 December 2007 13:38 Go to previous messageGo to next message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
HI Michel,
I know that exclusive lock apply on the table. But i expected that if the statement( alter) aborted, The update statement apply to table 1 should be aborted too. I mean rollback too. But it did not. I don't understand why?
Re: Error Ora -0054 in transaction [message #290405 is a reply to message #290403] Fri, 28 December 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DDL statement (ALTER is a DDL statement) starts with a commit, so anything that happens after doesn't roll back what was done before.

Regards
Michel

[Updated on: Fri, 28 December 2007 13:46]

Report message to a moderator

Re: Error Ora -0054 in transaction [message #290407 is a reply to message #290405] Fri, 28 December 2007 14:02 Go to previous messageGo to next message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
HI Michel,
I aware what you say. Alter is DDL and we can not rollback what is done before. If that a case, what should i do to guarantee that either both SQL run or nothing run. Since right now i get into a situation that Update Table 1 is done but alter is not done. Since i want either both run or nothing run, so i put it in a transaction. But it doesn't solve in this case.


Regards,
V
Re: Error Ora -0054 in transaction [message #290408 is a reply to message #290407] Fri, 28 December 2007 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
The 2 operations are in different transactions and you can't change that.

But of course, you know that if you need what you want to do your design is bad (read the other topic).

Regards
Michel
Re: Error Ora -0054 in transaction [message #290409 is a reply to message #290407] Fri, 28 December 2007 14:21 Go to previous messageGo to next message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
Thank so much Michel.
Now i understand that i can not put those in one transaction. But can i ask one more question? Do you mean that running any DDL statement is a separate transaction itself?

Regards,
V
Re: Error Ora -0054 in transaction [message #290411 is a reply to message #290409] Fri, 28 December 2007 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is what I meant.
More, it ends the previous transaction before starting its own.

Regards
Michel

[Updated on: Fri, 28 December 2007 14:41]

Report message to a moderator

Re: Error Ora -0054 in transaction [message #290424 is a reply to message #290396] Fri, 28 December 2007 16:42 Go to previous messageGo to next message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
Hi Michel,
Thank you so much for your help. Now I have a clear idea about my problem. and understand more about DML and DDL statement. I found that it is so helpful to go to this forum and your answer is very helpful to me.

Again Thanks and happy new year to you
V
Re: Error Ora -0054 in transaction [message #291606 is a reply to message #290407] Sat, 05 January 2008 07:07 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
you could use a proc or fun to make this happen. Just use the DBMS_SQL package for the Alter statement (DDL). Both will be done of none would be executed

- Das
Re: Error Ora -0054 in transaction [message #291753 is a reply to message #290396] Sun, 06 January 2008 10:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
That is not correct Das. A DDL always does an implicit commit before executing, no exceptions. The mechanism you use to get the DDL to the database is irrelevant.

Have you tried this? Here is some code for you to play with. When you run this code you will see that even though the add column fails on the second table, the insert still succeeds on the first table, no matter how hard one tries not to let it happen. This is because as Michel said, there are two seperate transactions and there is no way to change that.

As the code comments say, you will need two sqlplus sessions to test this.

drop table temp1;
drop table temp2;
create table temp1 (a number);
create table temp2 (a number);
desc temp1;
desc temp2;
select * from temp1;
select * from temp2;

--
-- lock temp2 in exclusive mode in a different sqlplus session first, then run this
--

--
-- first try using execute immediate, easier code to write
--
begin
  insert into temp1 values (1);
  execute immediate 'alter table temp2 add b number';
  rollback;
exception when others then rollback; raise;
end;
/
select * from temp1;

--
-- now do it the long way
--
declare
  c1 integer;
  rows_processed integer;
begin
  insert into temp1 values (1);
  c1 := dbms_sql.open_cursor;
  dbms_sql.parse(c1, 'alter table temp2 add b number',dbms_sql.native);
  dbms_sql.close_cursor(c1);
  rollback;
exception when others then rollback; raise;
end;
/
select * from temp2;


Have fun, Kevin.
Re: Error Ora -0054 in transaction [message #292105 is a reply to message #290396] Mon, 07 January 2008 19:17 Go to previous message
vvanvu
Messages: 9
Registered: December 2007
Junior Member
Thank Kevin and Das for your input. Good sharing
Previous Topic: ORA-01403: no data found
Next Topic: And condition within the same table
Goto Forum:
  


Current Time: Wed Dec 07 03:05:10 CST 2016

Total time taken to generate the page: 0.09526 seconds