Category: Oracle

ORA-20005: object statistics are locked in AWS/RDS environment

While testing the AWS RDS environment, I ran across the issue that the statistics were locked:

begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;

The error was:

Error starting at line : 6 in command -
begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;
Error report -
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34707
ORA-06512: at line 2

I checked with

select table_name, stattype_locked from dba_tab_statistics where table_name in ( select table_name from dba_tables where owner = 'DEAN');

The STATTYPE_LOCKED column contained the value of “ALL”. I was able to unlock this with

exec dbms_stats.unlock_table_stats('DEAN', 'table name');

This changed the value in the STATTYPE_LOCKED to “null” after which I was able to gather statistics.

Cleaning up the sys.aud$ table

I inherited a non-production database that had been auditing to the DB (audit_trail=DB) without any cleanup process in place. The sys.aud$ table had over 8 million rows of data. Before I set up the cleanup jobs, I decided to remove all the data in the table. I issued a truncate:

Error starting at line : 16 in command -
truncate table sys.aud$
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

A select on dba_objects indicated that sys.aud$ was a synonym

select owner, object_name, object_type from dba_objects where object_name = 'AUD$';
 
OWNER    OBJECT_NAME   OBJECT_TYPE
-------- -----------   -----------
SYS      AUD$          SYNONYM
SYSTEM   AUD$          TABLE

The table is SYSTEM.AUD$, but I thought I would check anyway:

select table_owner, table_name from dba_synonyms where owner = 'SYS' and synonym_name = 'AUD$';
 
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYSTEM                         AUD$

Truncated the base table:

truncate table system.aud$;

Oracle 11.2.0.4 and obfuscated (identified by values) passwords

In versions of Oracle older than 11.2.0.4, I have always migrated DB links with dbms_metadata.get_ddl to get the ddl for a db link as I do not have the passwords for the user at the remote site. This provides the ddl as shown below with an obfuscated password:

CREATE DATABASE LINK "db_link_name" 
CONNECT TO "remote_user" 
IDENTIFIED BY VALUES '06AD92A1C4E671B083FE5A long sting here' 
USING '(DESCRIPTION=
(ADDRESS=                                                            
(PROTOCOL=TCP)(HOST=remote.server.com)(PORT=remote port))
    (CONNECT_DATA=                      
      (SERVER=dedicated)
      (SERVICE_NAME=remote service name)
    )                                       
)'                                                                              
;

Oracle 11.2.0.4 has a security enhancements to prevent the use of obfuscated passwords (See Doc ID 1905221.1). So you have 2 options:
a. Know the password and provide it explicitly
b. Use expdp/impdp to copy it over

Very often, option a. is not available as we do not know that password of the user at the remote site.

So I tried option b which works and creates the ddl as:

CREATE DATABASE LINK "db_link_name" 
CONNECT TO "remote_user" 
IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION=
(ADDRESS=                                                            
(PROTOCOL=TCP)(HOST=remote.server.com)(PORT=remote port))
    (CONNECT_DATA=                      
      (SERVER=dedicated)
      (SERVICE_NAME=remote service name)
    )                                       
)'                                                                              
;

This can be directly imported via impdp.

One approach to identifying the cause of ORA-01017

If you have a user account that is repeatedly getting locked out as a result of exceeding the number of failed password attempts (ORA-01017) and you are unable to identify who or what is causing it, this approach may be helpful:

Create a table as follows to hold the failed login information:

create table dean_logon_errors
(
login_date date,
os_user varchar(15),
ip_address varchar(50),
AUTHENTICATED_IDENTITY varchar(50),
AUTHENTICATION_METHOD varchar(50),
CLIENT_IDENTIFIER varchar(50), 
CLIENT_INFO varchar(50),
terminal varchar(50)
)
TABLESPACE name_of_table_space
;

Create a trigger that will be fired when an ORA-01017 error occurs:

CREATE OR REPLACE TRIGGER dean_logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO dean_logon_errors values
    ( sysdate
     ,sys_context('USERENV','OS_USER')
     ,SYS_CONTEXT('USERENV','IP_ADDRESS')
     ,SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
     ,SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
     ,SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
     ,SYS_CONTEXT('USERENV','CLIENT_INFO')
     ,SYS_CONTEXT('USERENV','TERMINAL')
);
  END IF;
END logon_failures;
/

Wait for some time to allow the trigger to collect information and then execute the below SQLs:

alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select * from dean_logon_errors order by login_date desc;

Output will look like:

LOGIN_DATE         OS_USER         IP_ADDRESS      AUTHENTICATE AUTHENTICATION_ CLIENT_IDENTIFI CLIENT_INFO     TERMINAL
------------------ --------------- --------------- ------------ --------------- --------------- --------------- ---------------
13-JAN-17 20.50.00 john_doe_os     110.170.248.345 user_acct    PASSWORD
13-JAN-17 20.50.00 john_doe_os     110.170.248.345 user_acct    PASSWORD

Checking the use of undo space in the past

We had a circumstance in which we had to delete 4 billion rows from a 5 billion row table. This was done programmatically as we could not take an outage on the system. After the deletes were complete we were attempting to reclaim space on the index on a table, we were attempting to reclaim the space via an online rebuild of the index when we ran out of undo space: Continue reading “Checking the use of undo space in the past”

142
51
73
77