Inserting Duplicate Values

This morning I was inserting rows in a table with unique indexes, where it found a duplicate key (natural or composite) it abort the execution. I needed insert all rows excluding the rows, of course, maybe drop the index or filter the rows to insert can be a solution, but in my case the hint IGNORE_ROW_ON_DUPKEY_INDEX was the solution. It only work for insert statement. This hint cause the statement ignore a unique key violation and does not cause statement termination. Please let me show a example.

 
Create a new table with 3 rows;

dbp12> create table anr1 (col1 number);

Tabla creada.

dbp12> insert into anr1 values (1);

1 fila creada.

dbp12> insert into anr1 values (5);

1 fila creada.

dbp12> insert into anr1 values (6);

1 fila creada.

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Create a second table with 3 rows, the first row have value 1.

dbp12> create table anr2 (col1 number);

Tabla creada.

dbp12> insert into anr2 values (1);

1 fila creada.

dbp12> insert into anr2 values (2);

1 fila creada.

dbp12> insert into anr2 values (3);

1 fila creada.

Show values for both tables, and perform commit;

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Transcurrido: 00:00:00.29
dbp12> select * from anr2;

      COL1
__________
         1
         2
         3

Transcurrido: 00:00:00.51
dbp12> commit;

Confirmación terminada.

Create a unique index on the first table;

dbp12> create unique index anr1inx on anr1 (col1);

Índice creado.

Now perform a insert/select command, the first value must fail and abort the execution;

dbp12> insert into anr1  select * from anr2;
insert into anr1  select * from anr2
*
ERROR en línea 1:
ORA-00001: restricción única (ANTONION.ANR1INX) violada

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Transcurrido: 00:00:00.28

One more timen, now with the ignore_row_on_dupkey_index hint;

dbp12> insert /*+ ignore_row_on_dupkey_index (anr1,ANR1INX) */ into anr1  select * from anr2;

2 filas creadas.

dbp12>  select * from anr1;

      COL1
__________
         1
         5
         6
         2
         3

In this case the values 2 and 3 have been inserted.

HTH – Antonio NAVARRO

Minimal Number Of Parameters To Start An Instance

Today I needed create an auxiliary instance to restore a pairs of datafiles, for this purpose minimal configurations is enough. Oracle documentations says that only one parameter to start (startup nomount in mi case) is db_name, the rest of parameters are setting on defaults. Please look at down the next text;

 

thunder-3@:$PWD> cat initreco.ora
###
### The only one parameter needed is db_name
###
*.db_name='reco'
thunder-3@:$PWD> sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 30 13:02:47 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  329990144 bytes
Fixed Size                  2158976 bytes
Variable Size             255856256 bytes
Database Buffers           67108864 bytes
Redo Buffers                4866048 bytes

HTH – Antonio NAVARRO.

 

Error RMAN-03002 And RMAN-06429 Registering Database In Catalog

Today I get the next error registing a new database (12.1.0.5) in a RMAN catalog;

connected to target database: FINANCIAL (DBID=5127695875)

connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 08/26/2016 09:59:25
RMAN-06429: BCKCAT database is not compatible with this version of RMAN

This is a typical error when a new greater version database is including into the RMAN catalog. The solution is typical too, is very easy, only execute an upgrade command like show below;

 RMAN> upgrade catalog; recovery catalog owner is RMAN enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog; recovery catalog upgraded to version 12.01.00.05 DBMS_RCVMAN package upgraded to version 12.01.00.05 DBMS_RCVCAT package upgraded to version 12.01.00.05 RMAN> RMAN> RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> 

HTH – Antonio NAVARRO

 

How To See The Last Used Block Or Extent

Today I like show the way of see the last used block in a table (exten too) when we perform, by example, an insert, remember that if it have free blocks at the beginning or in the middle it would use them and not neccesary go to above the hwm (high watermark).

For this proposer we’re going to use the dbms_space.unused_space package.

From Oracle Doc we can see the description for the next fields, this attributes get us the data which we are looking for;

last_used_extent_ file_id -> Returns the file ID of the last extent which contains data.

last_used_extent_ block_id -> Returns the starting block ID of the last extent which contains data.

last_used_block -> Returns the last block within this extent which contains data.

The next script is a simple example of how it works, you need pass the table owner, and table name;

 
set serveroutput on
declare

   TOTAL_BLOCKS              number;
   TOTAL_BYTES               number;
   UNUSED_BLOCKS             number;
   UNUSED_BYTES              number;
   LAST_USED_EXTENT_FILE_ID  number;
   LAST_USED_EXTENT_BLOCK_ID number;
   LAST_USED_BLOCK           number;

 begin
   dbms_space.unused_space('&prop.','&tabname','TABLE',
      TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
      LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
      LAST_USED_BLOCK);

    dbms_output.put_line('LAST USED EXTEN AND BLOCK');
    dbms_output.put_line('-----------------------------------');
    dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||LAST_USED_EXTENT_FILE_ID);
    dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
    dbms_output.put_line('LAST_USED_BLOCK           = '||LAST_USED_BLOCK);

end;
/
set serveroutput off

HTH – Antonio NAVARRO

How To Perform A EXP And Compress On The Fly

Actually I’m upgrading a database (Release 9.2.5.0) to 12c, It is a small database because of this I prefer perform a (traditional) exp and imp that upgrade to a 10g and after that upgrade to 12c.

The problem I have found is when generate the dmp file, the file system have 20 gigas of free space and the dmp file size is greater. There are some solutions, in my case I selected compress the dmp file while it is generated using a unix pipe. Here I show the script;

 
orange-3@> cat dumper.sh
#!/bin/ksh

export ORACLE_SID=FINAN
export ORACLE_HOME=/oracle/server/925

DIRECTORY=/dummy; export DIRECTORY
FILENAME=${DIRECTORY}/fichero_grande.dmp.gz; export FILENAME
LOG=${DIRECTORY}/fichero_grande.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE

test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}

cat $PIPE | /usr/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=104857600 file=${PIPE} owner=FINANCIAL << EOF
financial/xxxxxxxxxx
EOF

HTH – Antonio NAVARRO

How To Get The Avaliable Agents In EM 12C

I like to show the way to get the list the avaliable agents in Enterprise Manager Cloud 12C from command line, of course, it is very easy from graphical enviroment.
First of all we must log;

saturn> ${OMS_HOME}/bin/emcli login -username=’SYSMAN’ -password=’xxxxxxx’

after that using emcli and the option get_supported_platforms get the report;

saturn> ${OMS_HOME}/bin/emcli get_supported_platforms
———————————————–
Version = 12.1.0.5.0
Platform = HP-UX PA-RISC (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Microsoft Windows x64 (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Oracle Solaris on x86-64 (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Microsoft Windows (32-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Oracle Solaris on SPARC (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Linux x86-64
———————————————–
Version = 12.1.0.5.0
Platform = HP-UX Itanium
———————————————–
Version = 12.1.0.5.0
Platform = Linux x86
———————————————–
Version = 12.1.0.5.0
Platform = IBM: Linux on System z
———————————————–
Version = 12.1.0.5.0
Platform = IBM AIX on POWER Systems (64-bit)
———————————————–
Platforms list displayed successfully.

 

HTH – Antonio NAVARRO