CODEWITHSUNDEEP
oracle-databaseperldbi
user9318576
user9318576

Reputation: 389

DBD ERROR: error possibly near <*> indicator at char 15 in 'TRUNCATE TABLE <*>MONTHLY_DATA')

I have piece of Perl code where i'm trying to truncate the table while truncating the table sometime I'm getting error like below.It is eating so much time of mine any help we appreciated

Error :-

DBD::Oracle::st execute failed: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired (DBD ERROR: error possibly near <*> indicator at char 15 in 'TRUNCATE TABLE <*>MONTHLY_DATA') [for Statement "TRUNCATE TABLE MONTHLY_DATA"] at data_arrival.pl line 3.

Code

$dbh = DBI->connect("dbi:Oracle:xxx","xxx","xxx", {RaiseError => 1});
$sth=$dbh->prepare("TRUNCATE TABLE MONTHLY_DATA");
$sth->execute();
$dbh->disconnect();

Upvotes: 2

Views: 1581

Answers (1)

Kirs Kringle
Kirs Kringle

Reputation: 929

You have a table that has a lock already. This has been explained at this link: https://community.oracle.com/thread/634676. It is frowned upon to provide just a link so I will put the answer here too.

Because some other session has a lock on that table, you can delete it because you are in fact deleting your read-consistent view of the table. See this test case:

In session 1:

YAS@10G>create table t as select * from dual;

Table created.

YAS@10G>insert into t values(2);

1 row created.

In session 2:

YAS@10G>truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


YAS@10G>delete from t;

1 row deleted.

YAS@10G>commit;

Commit complete.

YAS@10G>select * from t;

no rows selected I was not able to truncate it because session 1 inserted a row and it is holding a lock on the table. I could delete from the table because the rows I want to delete are not locked.

TRUNCATE is DDL and it needs an exclusive lock on the table. Session 1 is holding a shared lock on the table which does not permit the other session to get an exclusive lock.

To possibly solve your problem you could just retry your truncate on failure.

#!/usr/bin/perl
# your code goes here
use strict;
use warnings;
my $dbh = DBI->connect("dbi:Oracle:xxx","xxx","xxx", {RaiseError => 1});
my $sth=$dbh->prepare("TRUNCATE TABLE MONTHLY_DATA");
my $retryLimit = 5;
my $retrySleep = 5;
my $retryCount = 0;

$sth->execute()
    || &retry();
$dbh->disconnect();

sub retry {
    sleep($retrySleep);
    $retryCount++;
    if ($retryCount le $retryLimit) {
        print qq{Retrying the TRUNCATE of MONTHLY_DATA\n};
        $sth=$dbh->prepare("TRUNCATE TABLE MONTHLY_DATA");
        $sth->execute() || &retry();
        return;
    } else {
        print qq{Retried TRUNCATING TABLE MONTHLY_DATA $retryLimit times. Quiting now\n};
        exit; 
    }
}

This would generally just be working around your problem. Playing the waiting game. If your table is always being written too it may not be possible to use this with any success. I would then suggest locking your table and then truncating. Another suggestion is creating a table like that table then swapping them and dropping the old table (I know you can do this in MySQL not sure about Oracle).

Upvotes: 2

Related Questions