Tip: Reverse Engineering in Oracle Data Modeler

I am currently building a Data Warehouse using OWB as integration tool. I have almost finished and now comes what I like the less about my job... documentation!
During the development process, you usually create a number of staging tables. Oracle Data Modeler allows you to import tables from a schema. This way it is much easier writing down your documentation and you also have a visual presentation ready to use.
Let's go through the reverse engineering process:
1) Go to File -> Import -> Data Dictionary

2) Add a new connection if you haven't set up any yet

3) Select your Schema/Database



Personally I find the filter option pretty useful for retrieving quickly the Schema you are looking for.

4) Select the objects you want to import

5) Click on Finish

If everything goes ok, you have your Model imported in Oracle Data Modeler and you can export images of your diagrams or generate your DDL code to attach to your documentation.  

Oracle Tip: Find locked objects in Oracle Database

Tipically I end up locking objects by executing an Oracle Warehouse Builder (OWB) mapping and then stopping it because I realize I have forgotten a join condition. If you abruptly kill the session, the object may stay locked until the rollback is completed or endlessy if the session is killed before any action against the target table is taken.
In order to find out which object has been locked (and beg your DBA to kill the associated session or free the locks), run the following query:

 SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#,
                 b.status, b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

The output will look like this:

In case you are the DBA (and you have DBA privileges), then get the SID and the SERIAL# and run the following:

 alter system kill session 'SID,SERIAL#';

Tip: Reset Oracle Sequence

Yesterday I was working on an ODI interface, a pretty easy one, which was supposed to insert new rows  into a table using a <SEQUENCE>.NEXTVAL for populating the primary key.
The primary key was set NUMBER(4). The last value inserted had 17 as primary key. I was expecting new items to start from 18, but I was constantly getting: 

SQL Error: ORA-12899: value too large for column

After spending  a considerable amount of time for finding out which column was making my ODI interface fail, I have executed the following: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

which returned me: 124586!!!

Something has obviously gone wrong with that sequence and I needed to restore it to 17. So basically I have executed the following: 

ALTER SEQUENCE MY_SEQ INCREMENT BY -(124586-17)

and then execute: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

to restore MY_SEQ.CURRVAL to 17. 

Then you reset the correct increment by executing: 

  

 ALTER SEQUENCE MY_SEQ INCREMENT BY 1

My first post on the Avanttic Blog

Recently I have started working for a new Oracle partner and yesterday I was invited to write a post for the company's blog.
The post is a comparison between OWB and ODI. Unfortunately, the blog is in Spanish. I hope to have some time for translating it in the future.

http://blog.avanttic.com/2013/02/15/owb-vs-odi/#more-8210

Tip: How to get directoy listed in SQL*Plus

Today I needed to launch several .sql scripts from the same directory. I needed to get the script names in a quick way, and possibly being able to copy and paste them.
The solution was using:

SQL> host dir
 El volumen de la unidad C es OS
 El número de serie del volumen es: 6A31-3504

 Directorio de C:\Users\cristina\Documents\

24/01/2013  14:02    <DIR>          .
24/01/2013  14:02    <DIR>          ..
23/01/2013  11:44           291.729 acciones.sql
23/01/2013  18:17            17.826 canales_comunicacion.sql
23/01/2013  18:17           292.138 etapas.sql
24/01/2013  12:14       126.673.515 exp1980.sql
24/01/2013  12:23        91.241.885 exp1985.sql
24/01/2013  12:29        38.205.132 exp1989.sql

SQL*Plus HOST command allows you to execute OS commands such as dir, cd, etc.

Tip: Oracle SQL Developer Data Modeler Create Sequence for an auto incrementing ID

Recently I have started using this tool from Oracle and I must admit it is brilliant for designing Star Schemas and generating DDL code.
For my project I need to define an auto incrementing ID field for several tables of my model. I obviously needed a sequence for each and I was looking for a way to define it in the Data Modeler.
Oracle Data Modeler allows you to mark any column field as auto-incrementing. Select the table in your model and double click on the column. In the example below, I want the primary key field to be auto-incrementing.

Select the option Auto-Incrementing in the Column Property panel.

If you check the DDL you will see the code for the sequence and the trigger that is fired whenever a new line is inserted into the table.

CREATE SEQUENCE CNE_CNEIDE_SEQ
    NOCACHE
    ORDER ;

CREATE OR REPLACE TRIGGER CNE_CNEIDE_TRG
BEFORE INSERT ON T_CANAL_ENTRADA
FOR EACH ROW
WHEN (NEW.CNEIDE IS NULL)
BEGIN
    SELECT CNE_CNEIDE_SEQ.NEXTVAL INTO :NEW.CNEIDE FROM DUAL;
END;
/

If you don't need the trigger, just untick the option in the Auto Increment panel.

Tip: APEX Virtual Environment

I am trying to learn APEX and I was looking for a way to install it on my laptop.
No need to do that. Oracle allows you to create your own APEX workspace.
At the following URL, you can request your own workspace and start getting familiar with APEX fast and easily.
http://apex.oracle.com/i/index.html
Just provide your email and you will be given a login, a test database schema and... enjoy developing! ;)