Thanks for the question, Ankit.
Asked: February 27, 2013 - 2:51 am UTC
Last updated: November 30, 2023 - 5:36 pm UTC
Version: 11.1.1
Viewed 10K+ times! This question is 
You Asked
Hi,
I am getting following error while migration.
ORA-00054: RESOURCE BUSY AND ACQUIRE WITH NOWAIT SPECIFIED
Migration code is given below. I am not able to understand I have acquired exclusive lock on this table then why I am getting this error.
Please help ASAP this has to go in production tomorrow. Thanks in AdvanceCode:
DECLARE
l_script clob := q'[BEGIN
DELETE FROM fqng_report.rpt_refresh_history_data_queue q
WHERE q.source_table_name = 'FQNG_INVENTORY';
EXECUTE IMMEDIATE 'ALTER TABLE FQNG_REPORT.RPT_INVENTORY NOLOGGING';
EXECUTE IMMEDIATE 'TRUNCATE TABLE FQNG_REPORT.rpt_inventory';
EXECUTE IMMEDIATE 'LOCK TABLE FQNG_REPORT.rpt_inventory IN EXCLUSIVE MODE';
INSERT INTO FQNG_REPORT.RPT_INVENTORY
SELECT *
FROM FQNG.fqng_inventory
;
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE FQNG_REPORT.RPT_INVENTORY LOGGING';
END;]';
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MASTER.REPORT_DATA_MIGRATION_INVENT',
job_type => 'PLSQL_BLOCK',
job_action => l_script,
number_of_arguments => 0,
start_date => SYSDATE,
repeat_interval => NULL,
end_date => NULL,
job_class => 'SYS.DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => TRUE,
comments => 'This is one time job for Reports Migration',
credential_name => NULL,
destination_name => NULL);
end;
/
Here the data is migrated from the transaction table to the table in the reporting schema.
and Tom said...
Please help ASAP this has to go in production tomorrow. hahahahahahaha, thanks for the morning laugh, I really like that. please....
the ora-54 is being raised on the DDL, the alter table and or truncate table are being blocked by some other existing transaction on that table.
Funny thing is - you can remove the NOLOGGING/LOGGING bit altogether, they are not only not necessary - they do nothing. You are NOT doing a direct path load - so it will be logged anyway!!!!!!
If you wanted to do a direct path load, you'd have to use INSERT /*+ APPEND */ INTO table - and that would presume that there are no triggers and no referential integrity involved with this table (if there are, the append hint is ignored).
You can use ddl that waits - normally ddl will just time out immediately.
see:
https://blogs.oracle.com/connect/post/on-seeing-double-in-v$sql the section on "Some DDL Just Got Easier, and Some Just Got Added"
and never never never go to the internet and say something like "please help ASAP this has to go in production tomorrow". Think about this....
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment