Oracle using drop user cascade error ora-69 solution

Source: Internet
Author: User
Tags oracle database

1. Symptom:

The code is as follows: Copy code

SQL> drop user test cascade;
Drop user test cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for T1
 
 
SQL> conn test/test
Connected.
SQL>
SQL>
SQL> desc t1
Name Null? Type
-----------------------------------------------------------------------------
A DATE
 
SQL>
SQL>
SQL> drop table t1;
Drop table t1
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1

2. Cause:

The code is as follows: Copy code

SQL>!
Oer [oracle @ rac1 ~] $ Oerr ora 69
00069,000 00, "cannot acquire lock -- table locks disabled for % s"
// * Cause: A command was issued that tried to lock the table indicated in
// The message. Examples of commands that can lock tables are:
// Lock table, alter table... ADD (...), and so on.
// * Action: Use the alter table... enable table lock command, and retry
// The command.
[Oracle @ rac1 ~] $
[Oracle @ rac1 ~] $
[Oracle @ rac1 ~] $ Sqlplus "/as sysdba"
 
SQL * Plus: Release 11.2.0.4.0 Production on Thu May 19 11:29:19 2016
 
Copyright (c) 1982,201 3, Oracle. All rights reserved.
 
 
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SQL>
SQL> select owner, table_name, table_lock from dba_tables where table_name = 'T1'
SQL>/
 
OWNER TABLE_NAME TABLE_LO
--------------------------------------------------------------------
TEST T1 DISABLED
 
SQL>
SQL>
SQL>
SQL>

3. Solution:

The code is as follows: Copy code

SQL> alter table test. t1 enable table lock;
 
Table altered.
 
SQL>
SQL> select owner, table_name, table_lock from dba_tables where table_name = 'T1 ';
 
OWNER TABLE_NAME TABLE_LO
--------------------------------------------------------------------
TEST T1 ENABLED
 
SQL>
 
SQL> drop user test cascade;
 
User dropped.
 
SQL>

Further investigation reveals that DBAs mistakenly regard table_lock as the statistic information to determine whether to lock the field. The table_lock of most tables that do not lock the statistics information is enable, therefore, you can use alter table disable table lock to lock statistics.

To check whether the statistics are locked, dba_tab_statistics.stattype = 'all' indicates that the statistics are locked.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.