“Order By” Clause Using Substr Function

I like to show a example of Order By using a substr function. Usually people order results by columns but in general “Order by” clause (No only Oracle, in this case I refer standard, ANSI SQL/ISO 9075) is a powerfull option for sort in many differents ways.

In this example I order a string (which is a date) by day, month and year;

 
-- Date into the string have the next format;
-- Sun Oct 01 19:26:01 2017

select date_in_string from dual
order by 
  substr (date_in_string, 9, 2),
  substr (date_in_string, 5, 3),
  substr (date_in_string, 21, 4)
/

HTH – Antonio NAVARRO

Advertisements

Language Is Very Important

Today I was reported with a error inserting one row from a dev team. He told me that inserted one row and work fine, but the second row doesn’t work. He saw me the next;

 

 
DBTT*ANTO> insert into tensorflow2  values (to_date ('Jul 17 18:53:59 2017', 'Mon dd hh24:mi:ss yyyy')); 

1 fila creada.

Transcurrido: 00:00:00.03
DBTT*ANTO> insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'));
insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'))
                                           *
ERROR en línea 1:
ORA-01843: mes no válido

 

Of course, first of all;

i) In this installation, default language is in local language,(Errors messages are in Spanish as you can see).

ii) Jul in English is equal to Jul in Spanish.

ii) Aug in Englies is Ago in Spanish.

Solutions;

i) Write all in english, maybe the best solution in this case.

ii) Change the session parameter (in this case nls_date_language), maybe the worst solution, all is designed to work in Spanish.

                     
DBTT*ANTO> alter session set nls_date_language=english;            

DBTT*ANTO> insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'));

1 fila creada.

HTH – Antonio NAVARRO

 

 

How To grep n Lines Before In Solaris

Hi,

I like to show how to get the n lines before a pattern search using grep in Solaris. First of all, Solaris grep command not have a option to get the lines before or after from a match. You must use ggrep compliance to unix standard. Let me see show a pair of examples;

Test 1, this comamnd show the four previous lines from match ‘ORA-00313’;

cat alert_db.log | /usr/sfw/bin/ggrep -B 4 ORA-00313

Test 2, this command show the previous line from match ‘ORA-00313’;

cat alert_db.log | /usr/sfw/bin/ggrep -B 1 ORA-00313

HTH – Antonio NAVARRO

 

Larry’s Keynote

 

I have watched the key note from Larry Ellision. He announce the new database version 18c and introduce the self-driving  concept where database is “full automated”. Is the DBA in risk of extinction?. He use many buzzwords like machine learning too.

 

HTH – Antonio NAVARRO

ORA-25408 Error

Last morning I get the next error

 
ORA-25408: can not safely replay call

I was taking a snapshot with the follow command;

 
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; 

Looking for the error using the oerr utility

 
jupiter> oerr ora  25408
25408, 00000, "can not safely replay call"
// *Cause:  The connection was lost while doing this call. It may not be
//          safe to replay it after failover.
// *Action: Check to see if the results of the call have taken place, and then
//          replay it if desired.

In this case the core problem is because of I have disable the commit in procedure. It can be enable by using the next command.

 
alter session enable commit in procedure;

The error ORA-25408 is get because of the error ORA-00034. This is a RAC and I suppose it fails and try again in other node in the RAC.

HTH – Antonio NAVARRO

How To Trace DDL Sentences

Last day someone drop a import table in a database. The app get crazy and nothing work fine. The solution was recreate the table but the problem was who deleted it?.

In this case there weren’t any audit. You can activate audit (it is powerfull) but in this case I want talk about trace only DDL SQL (Data Definition Language) by using the enable_ddl_logging parameter.

 

How to check;

BBDD*ANR> show parameter enable_ddl_logging

NAME                                 TYPE                             VALUE
____________________________________ ________________________________ _________________________
enable_ddl_logging                   boolean                          FALSE

How to enable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

Hot to disable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=FALSE;

The audit will be write to (version 12c) a file with format show below into the diagnostic_dest;

ddl_${ORACLE_SID}.log

 

HTH – Antonio NAVARRO