Archive

Archive for July, 2012

ORA-20005: object statistics are locked (stattype = ALL)

July 24, 2012 Leave a comment

Today I analyze a table and get the error messages below

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 23829
ORA-06512: at “SYS.DBMS_STATS”, line 23880
ORA-06512: at line 2

Solution:

You can see list of all locked tables in a schema by running following query:

select table_name, stattype_locked from dba_tab_statistics where owner = ‘<scheama>’ and stattype_locked is not null;

To generate unlock statement for all tables in the schema you can use following,

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘<schema>” and stattype_locked is not null;

 

Categories: ORA-XXX

ORA-02085: database link DB2PCTVUAT connects to PCTVUAT

July 18, 2012 Leave a comment

I create a database link from a development db to uat db. Then I test the connection as below and got the error ORA-02085

SQL> create database link db2pctvuat connect to strmadmin identified by strmadmin using ‘pctvuat’;

Database link created.

SQL> select tname from tab@db2pctvuat
2  ;
select tname from tab@db2pctvuat
*
ERROR at line 1:
ORA-02085: database link DB2PCTVUAT connects to PCTVUAT

Once I see the initial parameter global_name=false, the connection test is OK

SQL> select tname from tab@db2pctvuat;

no rows selected

Then, found a Metalink article (DocID=1024124.6), it said that …

  – GLOBAL_NAMES is set to TRUE, the name of the database link should match the global name of the target database
 –  GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire.

Since my uat database name is pctvuat, I test the database with global_names is set to true

SQL>  create database link pctvuat  connect to strmadmin identified by strmadmin using ‘pctvuat’;

Database link created.

SQL> select tname from tab@pctvuat;

no rows selected

SQL> drop database link DB2PCTVUAT;

Database link dropped.

SQL> show parameter global

NAME                                 TYPE        VALUE
———————————— ———– ——————————
global_context_pool_size             string
global_names                         boolean     TRUE

Categories: Administration, ORA-XXX

ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

July 17, 2012 3 comments

Today, I get the error below from a db server

ADR Home = /opt/oracle/product/diag/asm/+asm/+ASM1:

*************************************************************************

2012-07-16 18:22:02.158000 +08:00

Errors in file /opt/oracle/product/diag/asm/+asm/+ASM1/trace/+ASM1_ora_28871.trc:

ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

Additional information: 3

Additional information: 128

Additional information: 1227322244

diag/rdbms/spbpro/spbpro1 Alert Log

 

By checking system kernel resources, I believe that “aio-max-nr” kernel limit is too low.

[oracle@ycsdb01 ~]$ /sbin/sysctl -a|grep aio
fs.aio-max-nr = 65536
fs.aio-nr = 56866
[oracle@ycsdb01 ~]$ cat /proc/sys/fs/aio-nr
56738

Solution : Change the “aio-max-nr” kernel limit as following
fs.aio-max-nr= 3145728

Categories: Uncategorized