“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 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

How To Drop A RAC Database (12c)

In this sample I like show you how to delete a database in RAC mode, it is a little different from standalone.

We need change de cluster database parameter;

show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

exit

Stop the database in all nodes, in this case I have two, you can only have one instance active to drop database, in other case you will get an error;

srvctl stop database -d DB121

Mount in restrict the database like previous step to the delete;

sqlplus / as sysdba
startup mount exclusive restrict

-- Make sure you are deleting the correct database
select instance_name from gv$instance;
SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
DB121_1

drop database;
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

exit

After that remove database from the clusterware;

srvctl remove database -db DB121
Remove the database DB121? (y/[n]) y

Like final step, you must to cleanup other files, manually, like archives, password file,….

HTH – Antonio NAVARRO

Clean Up A Obsolete Thread

Today I have been working with a new database, it is an upgrade from a RAC database but now it is single instance. The problem (maybe not, you can work in this way without issues) is thread 2 is not active. I like to keep clean all I can. Here I show you how to drop this thread.

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        247  104857600        512          1 NO  INACTIVE
         2          1        248  104857600        512          1 NO  INACTIVE
         3          1        249  104857600        512          1 NO  CURRENT
         4          2          1  104857600        512          1 NO  CURRENT
         5          2          0  104857600        512          1 YES UNUSED

In this case you only need execute the next commands;

First of all

SQL> ALTER DATABASE DISABLE THREAD 2;

After you need drop the group associaties to the thread 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Now, if we repeat the query on v$log;

SQL> SELECT * FROM V$LOG;                                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        250  104857600        512          1 NO  INACTIVE
         2          1        251  104857600        512          1 NO  INACTIVE
         3          1        252  104857600        512          1 NO  CURRENT

HTH – Antonio NAVARRO

How To Install Statspack On 9i Windows-box

Today I have been installing a statspack package for a older database, version 9i, maybe it is not relevant for many people but I want to post it in this entry because of I use the blog like a work diary and documentation place.

First of all we need to create a new tablespace where create the new objects for perfstat user, of course, you can use an existing tablepaspace but it is not a good idea;

 


CREATE TABLESPACE "ESTADISTICAS" DATAFILE
'X:\ORACLE\DATOS\ESTADISTICAS.DBF' SIZE 2147483648 -- 2 Gigas
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

After we need the spcreate.sql scritp, locate on %ORACLE_HOME%\rdbms\admin

foto1

Connect to database as sysdba and execute de sql;

foto2

This sql script call the following scripts;

  • SPCUSR.SQL: Creates the user and grants privileges
  • SPCTAB.SQL: Creates the tables
  • SPCPKG.SQL: Creates the package

Ask me for the password for user perfstat (in clear text);

foto3.PNG

Next ask me for tablespace where locate tables with the data are recolected from the system. You must write down the tablespace name create before. You need a temporary tablespace too;

foto5.PNG

if all is okay you get a reply similar to the next window, however it is necessary to review the spcpkg.lis file for any errors;

foto7.PNG

Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;

foto7

And the output;

foto8

HTH – Antonio NAVARRO

 

RDBMS 32 Or 64 Bits?

In this entry, I like show how to know  if platform is 32 Bits or 64 Bits, for this simple question you can user the next query.

SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

Output example for 32 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
32

Output example for 64 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
64

HTH – Antonio NAVARRO