Maybe The First Step Into The Relational Model

In this post I like show the origin of Relational database model. There are many books, papers, conferences about this subject, but I find out recently a serie of (very good) videos from CJ Date, one of fathers of the Relational model. Please follow the next link. No problem if you have a lot of years of experience in Relational model (I have about 20 years) and you think you know all about it.

Enjoy it.

HTH – Antonio NAVARRO

Advertisements

How To See Privileges In Oracle Directories

In this post I show a simple query for get privileges for users on directories. You can query those privileges by using the next query;

 
COLUMN GRANTOR       FORMAT A10
COLUMN GRANTEE       FORMAT A20
COLUMN TABLE_SCHEMA  FORMAT A10
COLUMN TABLE_NAME    FORMAT A20
COLUMN PRIVILEGE     FORMAT A10

SELECT *
FROM ALL_TAB_PRIVS
WHERE 
  TABLE_NAME IN (SELECT DIRECTORY_NAME FROM DBA_DIRECTORIES);

GRANTOR    GRANTEE              TABLE_SCHE TABLE_NAME           PRIVILEGE  GRA HIE COM TYPE
__________ ____________________ __________ ____________________ __________ ___ ___ ___ _______________
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY

 

HTH – Antonio NAVARRO

 

ORA-20000 DRG-50857 ORA-01031 Error Executing CTX_DDL

Last day Developer Team get the next error when they were executing a rebuild for a index text;

ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexOptimizeRebuild
ORA-01031: privilegios insuficientes
ORA-06512: en "CTXSYS.DRUE", línea 160
ORA-06512: en "CTXSYS.CTX_DDL", línea 1161
ORA-06512: en "STOREPROCEDURE_EXAMPLE", línea 3
ORA-06512: en línea 1

The problem error only occurs when they execute the index rebuild from a store procedure. Execute it direct work fine;

sql> REMARK   *** This execution work ***
sql> begin
  2  ctxsys.ctx_ddl.optimize_index('MY_CTX_INDEX', 'REBUILD');
  3  end;
  4  /

The problem when execution by named sql;

sql> REMARK   *** This execution work ***
create procedure STOREPROCEDURE_EXAMPLE as
begin
ctxsys.ctx_ddl.optimize_index('MY_CTX_INDEX', 'REBUILD');
end;
/
sql> exec STOREPROCEDURE_EXAMPLE
BEGIN STOREPROCEDURE_EXAMPLE; END;
*
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexOptimizeRebuild
ORA-01031: privilegios insuficientes
ORA-06512: en "CTXSYS.DRUE", línea 160
ORA-06512: en "CTXSYS.CTX_DDL", línea 1161
ORA-06512: en "STOREPROCEDURE_EXAMPLE", línea 3
ORA-06512: en línea 1

The error looks a problem with the privileges but the user (who execute the call to ctx_ddl) have all the need. The issue is that the privileges are into a rol. In this case you need direct grant to the user. The needed direct privileges are;

sql> REMARK     *** In this case, granted from sys user  ***
sql> grant create session, create table, create trigger, unlimited tablespace, resource, ctxapp to <user_who_call_ctx_ddl>
sql> grant execute on ctxsys.ctx_ddl to <user_who_call_ctx_ddl>

 

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

 

 

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