library cache pin & library cache lock

Once there was an error on one of our production database. I saw some process awaiting for "library cache lock". One process was in "library cache pin' state. This situation was expected during table partition movement operation.

As I understood it was a consequences of invalid package which became invalid due to "alter table XXX move partition". Session 388 was getting library cache pin when he or some system process had been tried to recompile invalid object during partition movement. The others were waiting for 388 process to get a lock. After partition had been moved successfully package was recompiled and the queue disappeared.

b-tree index structure (Non-Unique)

First name kind of field based index. This is Non-Unique index.

How b-tree database indexes work and how to tell if they are efficient (100' level)


src

Dropping and creating tables in read only tablespaces?!

Dropping and creating tables in read only tablespaces?!

July 11th, 2010, Tanel Poder


You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;
Tablespace created.
create table test tablespace ronly as select * from all_users;
Table created.
alter tablespace ronly READ ONLY;
Tablespace altered.
drop table test;
Table dropped.

How to prevent scheduled jobs to run at instance startup?

The solution is very simple:

1. startup in restricted mode
startup restrict;


2. disable all windows
select 'exec DBMS_SCHEDULER.DISABLE('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ENABLED='TRUE';


3. close all windows
select 'exec DBMS_SCHEDULER.CLOSE_WINDOW('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ACTIVE='TRUE';


4. stop running & disable jobs

CASE WHEN state !='RUNNING' THEN 'begin'  || CHR(10)  || 'DBMS_SCHEDULER.STOP_JOB ('''||OWNER||'.'||JOB_NAME||''', TRUE); '  || CHR(10)  || 'EXCEPTION WHEN OTHERS THEN NULL;'  || CHR(10)  || 'END; ' || CHR(10)  || '/' || CHR(10)  || 'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', TRUE );'
ELSE
'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', true);' 
end DDD
FROM DBA_SCHEDULER_JOBS
where OWNER not in ('SYS','EXFSYS','ORACLE_OCM');"| sqlplus -S '/ as sysdba'

5. shutdown immediate

6. startup

ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

When table is partitioned and it has subpartitions I received ORA-14287 error when I was trying to move partition to tablespace with nologging storage option:


SQL> ALTER TABLE TABLE_NAME
    MOVE PARTITION PARTITION_NAME
    TABLESPACE TABLESPACE_NAME;
    MOVE PARTITION PARTITION_NAME
                   *
ERROR at line 2:
ORA-14257: cannot move partition other than a Range or Hash partition


It means that it is impossible move logical structure to somewhere. It is better to move subpartition first and modify default attributes for partition next.

alter table TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

DBMS_LOGMNR something useful. Who generates redo?

На наших системах некоторые данные подлежат периодической перегенерации и располагаются в partitioned таблицах на nologging разделах. Иногда разработчики промахиваются и создают таблицу в разделе с опцией NOLOGGING. Это приводит к генерации большого количества архивных redo логов. Определить кто производит столь непосильную работу поможет следующий скрипт:

##########################
#
# DBMS_LOGMNR
#

STORE SET /tmp/_sqlplus-env.sql
SET AUTOP OFF
set long 200000
set lines 180
set trimspool on
set heading off
set feedback off
set pages 0
set verify off
set echo off
set timing offn
SET LINESIZE 5000
SET TERMOUT OFF
SET TRIMS ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
WHENEVER OSERROR EXIT FAILURE;
set serveroutput on;
spool /tmp/_tmp2run.sql

Some things that I've found to recover database without redo

SQL> startup mount
SQL> recover database until
SQL> alter database open resetlogs;

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [25501152], [0],
[25521272], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [25501151], [0],
[25521272], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [25501149], [0],
[25521272], [12583040], [], [], [], [], [], []
Process ID: 10846
Session ID: 1 Serial number: 5

_allow_resetlogs_corruption=true