An Old Release Of Oracle (7.3)

Today I had to connect to a machine (which maintains a historical database) ev version 73 to do a review of it, since the machine has suffered an incident.

In my case I started working with versions 6 and 7 of Oracle and the truth is that it took a bit to connect, since the old svrmgrl is used, I leave a screen of what the v $ instance contained in this version.

version73_svrmgrl
And another screen with the help that this tool had;

HELP_SVRMGRL

I have to admit that my tear drops when I see it.

HTH – Antonio NAVARRO

Advertisements

Difference Between “whoami” And “who am I”

We are going to see the following example, in the we connect to unix-box as a nominal user and we make a su command to another user

 
login as: anton
Using keyboard-interactive authentication.
Password:
Last login: Fri Nov 23 08:39:43 2018 from 10.201.91.166
Oracle Corporation      SunOS 5.11      11.3    June 2018
anton@houston:~$ whoami
anton
anton@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)
anton@houston:~$ su - dbowner
Password:
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner
dbowner@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)
dbowner@houston:~$ # ********* do su one more time  *********
dbowner@houston:~$ su - dbowner
Password:
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner
dbowner@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)

You can execute it as many times as you want “who am I” will always return the user with the one you have connected to the system and whoami will only return the user with whom you are working at a given moment.

 
HTH – Antonio NAVARRO

Possible new features in 19c

I have been making a brief summary of all the new features that were announced for Oracle version 19c in the last Oracle Open World. Keep in mind that they will only be official  when the new version comes out.

This is the list of the most interesting;

  • Stability
  • Automatic Indexing
  • Data-guard DML Redirect
  • Real-time Stats and Stats Only Queries
  • Schema-only Oracle accounts
  • DB REST API
  • Partial JSON Update support
  • Schema-only Oracle accounts
  • Partitioned Hybrid Tables

HTH – Antonio NAVARRO

 

 

ORA-31693 ORA-01466 Error When Executing Expdp

Executing an expdp at work I got the next output;

 
ORA-31693: Table data object "REPORT_BW"."TRAC_ACTIVITY" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed

 

The problem is a bug, if you have access to ML/MOS, please take a look at 1072871.1 note. The possible solutions are;

  • Upgrade to 12c
  • Apply patchset 11.2.0.3
  • Apply interim Patch 9110642
  • Don’t use FLASHBACK_SCN or FLASHBACK_TIME

HTH – Antonio NAVARRO

 

Estimate Size For Export Data Pump

In this entry  I like to show how to estimate the size for a data pump export. It is very easy because expdp command has a parameter to calculte. The problem is that this function, based in mi experiencie, no estimate very well but it is a starting  point. Here you have an example;

expdp system/xxx FULL=y ESTIMATE_ONLY=y ESTIMATE=blocks DIRECTORY=work_directory LOGFILE=EstimateFullSizeDump.log

HTHT – Antonio NAVARRO

 

ORA-00034 Error When Commit In Curren Session

This co-worker ask me about the next error last morning when I was executing a pl/sql script;

 
BBDD_12> SELECT DBMS_METADATA.GET_DDL ('TABLESPACE','F_MOVISTAR_DAT') FROM DUAL;
ERROR:
ORA-00034: no se puede ROLLBACK en la sesión PL/SQL actual
ORA-06512: en "SYS.KUPU$UTILITIES_INT", línea 735
ORA-00034: no se puede COMMIT en la sesión PL/SQL actual
ORA-06512: en "SYS.DBMS_METADATA", línea 6069
ORA-06512: en "SYS.DBMS_METADATA", línea 8666
ORA-06512: en línea 1

I tell him it is a “logical lock” that I use when to execute stored procedure or anonymous pl/sql of mine or other persons and not perform a commit in an irresponsible way. In my login.sql I have the next entry;

alter session disable commit in procedure;

To solve it you must only switch it to enable;

BBDD_12> ALTER SESSION ENABLE COMMIT IN PROCEDURE; 

HTH – Antonio NAVARRO

 

How To Close A Database Link

Today someone ask me about how to close a dabase link in Oracle, I post a very simple example to close open cursors;

 

 
DB12*ANTO> select version from V$instance;

VERSION
_________________
12.1.0.2.0

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE I (Using alter session)
DB12*ANTO> REMARK
DB12*ANTO> r
  1* select * from v$dblink

ninguna fila seleccionada

DB12*ANTO> select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> select * from v$dblink;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> 
DB12*ANTO> 
DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;
ERROR:
ORA-02080: el enlace de base de datos está en uso

DB12*ANTO> REMARK
DB12*ANTO> REMARK Of course, the transaction is open you need to close (commit/rollback)
DB12*ANTO> REMARK
DB12*ANTO> ROLLBACK;

Rollback terminado.

DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;

Sesión modificada.

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE II (Using DBMS_SESSION PACKAGE)
DB12*ANTO> REMARK
DB12*ANTO> exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');
BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link'); END;

*
ERROR en línea 1:
ORA-02081: el enlace de base de datos no está abierto
ORA-06512: en "SYS.DBMS_SESSION", línea 191
ORA-06512: en línea 1

DB12*ANTO> REMARK The database link is close, you need to user the db link to open it
DB12*ANTO>  select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> SELECT * FROM V$DBLINK;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> COMMIT;

Confirmación terminada.

DB12*ANTO>  exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');

Procedimiento PL/SQL terminado correctamente.

DB12*ANTO> 

HTH – Antonio NAVARRO