Case Of Use

Last week I post a code for show percentages in a way the grafic by user text mode. Today I want to show a new example of this;

                                           
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN grafi format a20

BBDD*ANTO> SELECT s.sid,
  2  s.serial#,
  3  s.machine,
  4  ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
  5  ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
  6  PERCENTAGE_GRAPH_TEXT (ROUND(sl.sofar/sl.totalwork*100, 2)) grafi
  7  FROM v$session s,
  8  v$session_longops sl
  9  WHERE s.sid = sl.sid
 10  AND s.serial# = sl.serial#
 11  /

 SID  SERIAL# MACHINE                        ELAPSED    REMAINING  GRAFI
____ ________ ______________________________ __________ __________ ____________________
####    19162 #######                        0:12       0:5        [******....] 68,63%
####      291 #######                        0:11       0:11       [****......] 49,62%
####      291 #######                        0:11       0:11       [****......] 49,62%
####      291 #######                        0:11       0:11       [****......] 49,62%
####    28799 #######                        1:37       20:55      [..........] 3%

5 rows selected.

 

HTH – Antonio NAVARRO

 

Advertisements

Graphic In Text Mode

I have to admit that I am a fanatic of the text mode, began to play computers in the eighties and practically everything was text mode. Today I will show a very simple function to put a kind of graphic (in text mode) to get our querys a little more interesting.

Here is the code;

-------------------------------------------------------------------------------- 
--Function that generate a graphic for percentages in text mode
--The function have five parametero, first one mandatory and the rest
--are optionals.
--
-- Parameter list;
--
-- Position 1 Is the figure that represent the percentage
-- Position 2 Is how many characters are used for generate the graph text (10)
-- Position 3 Is the simbol used for represent the percent (*)
-- Position 4 Is the simbol used for represent the space until the end ( )
-- Position 5 Is a flag to show the last string (number plus % simbol) (TRUE)
-- 
-- --- example i ----
-- SELECT PERCENTAGE_GRAPH_TEXT (20) FROM DUAL;
--
-- Result: [**........] 20%
--
-- -- example ii -----
-- SELECT PERCENTAGE_GRAPH_TEXT (20, 5, '=', ' ') from dual;
--
-- Result: [=    ] 20%
--
--
--  AUTOR:   Antonio NAVARRO
--  FECHA:   28.10.99
--  MODIFICADO: 28.10.99
--  FICHERO: PERCENTAGE_GRAPH_TEXT.SQL
--  LOCALIZACION: \\SAN/NAS
--
---------------------------------------------------------------------------------

CREATE OR REPLACE
FUNCTION PERCENTAGE_GRAPH_TEXT
(
numero number ,
longitud number default 10 ,
simbolo varchar2 default '*' ,
simbolo_relleno varchar2 default '.' ,
numero_por_centaje boolean default true
) RETURN VARCHAR2 IS

TEXTO VARCHAR2(110);

BEGIN


-- Open textgraph ---
TEXTO := TEXTO || '[';

FOR i IN 1..longitud LOOP
/*** Si estamos en porcentaje escribir simbolo, sino escribir vacio ***/
IF round ((i*100)/longitud) <= numero THEN
TEXTO := TEXTO || simbolo;
ELSE
TEXTO := TEXTO || simbolo_relleno;
END IF;

END LOOP;

-- Close texgraph ---
TEXTO := TEXTO || ']';

IF numero_por_centaje THEN
TEXTO := TEXTO || ' ' || numero || '%';
END IF;

RETURN (TEXTO);


END; /*** PERCENTAGE_GRAPH_TEXT ***/
/

An example of this will be like show below;

WEB*ANTO> SELECT PERCENTAGE_GRAPH_TEXT (20) FROM DUAL;

PERCENTAGE_GRAPH_TEXT(20)
_______________________________________________________________________
[**……..] 20%

WEB*ANTO> SELECT PERCENTAGE_GRAPH_TEXT (20, 5, ‘=’, ‘ ‘) from dual;

PERCENTAGE_GRAPH_TEXT(20,5,’=’,”)
_______________________________________________________________________
[= ] 20%

There are many options and possibilities that are left for the reader.

HTH – Antonio NAVARRO

“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

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

 

 

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