How to: Compare two database structures

A few days ago, I was asked for a task that can seem pretty simple in most cases.  Comparing 2 instances of “the same” database.  When your DB holds a few tables, this can be done manually, using some simple tools, using SQL commands like DESC or DEFINE as needed.  You can even use the Oracle Data Modeler an read the Data Dictionary from the 2 databases and compare them through it.
The problem comes when you so many tables that making an effective comparison would be a highly time-consuming effort.
For the effects of this post, I will state that what I need to compare is table names, column names and data types.  That was the challenge.  Of course, a procedure would pop-up to solve it.
Prerequisites for this procedure are:

1. 1 schema must be local and be the current schema from which he procedure is called
2. 1 schema must be reachable through a database link called “templink”

1: CREATE PROCEDURE COMPARE_DB_STRUCTURE IS
2:  ---------------
3:  -- VARIABLES --
4:  ---------------
5:   v_localtable user_tables.table_name%TYPE;
6:   v_localcol  user_tab_cols.column_name%TYPE;
7:   v_localtype  user_tab_cols.data_type%TYPE;
8:   v_localtabnum NUMBER;
9:   v_localcolnum NUMBER;
10:   
11:   v_remotetable user_tables.table_name%TYPE;
12:   v_remotecol  user_tab_cols.column_name%TYPE;
13:   v_remotetype user_tab_cols.data_type%TYPE;
14:   v_remotetabnum NUMBER;
15:   v_remotecolnum NUMBER;
16:  
17:  -------------
18:  -- CURSORS -- 
19:  -------------
20:   CURSOR c_localtable IS
21:   SELECT table_name
22:   FROM user_tables
23:   WHERE table_name NOT IN (
24:    SELECT table_name
25:    FROM user_tab_cols
26:    WHERE column_name = 'STATIC'
27:   )
28:   ORDER BY table_name;
29:  
30:   CURSOR c_remotetable IS
31:   SELECT table_name
32:   FROM user_tables@templink
33:   WHERE table_name NOT IN (
34:    SELECT table_name
35:    FROM user_tab_cols@templink
36:    WHERE column_name = 'STATIC'
37:   )
38:   ORDER BY table_name;
39: 
40:   CURSOR c_localcols IS
41:   SELECT column_name, data_type
42:   FROM user_tab_cols
43:   WHERE table_name = v_localtable
44:   ORDER BY column_name;
45:   
46:   CURSOR c_remotecols IS
47:   SELECT column_name, data_type
48:   FROM user_tab_cols@templink
49:   WHERE table_name = v_remotetable
50:   ORDER BY column_name;
51:  
52:  -------------------------
53:  -- MAIN PROCEDURE BODY --
54:  -------------------------
55:  BEGIN
56:  
57:      dbms_output.enable(1000000);
58:         dbms_output.put_line('Starting procedure: COMPARE DATABASE STRUCTURE...');
59:         dbms_output.put_line('');
60:   -- OPEN TABLE CURSORS
61:   
62:   SELECT COUNT(table_name) INTO v_localtabnum
63:   FROM user_tables
64:   WHERE table_name NOT IN (
65:    SELECT table_name
66:    FROM user_tab_cols
67:    WHERE column_name = 'STATIC'
68:   );
69:   
70:   SELECT COUNT(table_name) INTO v_remotetabnum
71:   FROM user_tables@templink
72:   WHERE table_name NOT IN (
73:    SELECT table_name
74:    FROM user_tab_cols@templink
75:    WHERE column_name = 'STATIC'
76:   );
77: 
78:         dbms_output.put_line('Number of static tables');
79:         dbms_output.put_line('-----------------------');
80:         dbms_output.put_line('');
81:      dbms_output.put_line('Local tables:  '|| v_localtabnum);
82:          dbms_output.put_line('Remote tables: '|| v_remotetabnum);
83:         dbms_output.put_line('');
84:   
85:   OPEN c_localtable;
86:   OPEN c_remotetable;
87:   -- GET TABLES
88:   FETCH c_localtable INTO v_localtable;
89:   FETCH c_remotetable INTO v_remotetable;
90:   
91:   WHILE c_localtable%FOUND LOOP
92:         IF v_localtable = v_remotetable THEN
93:           -- OPEN COLUMN CURSORS
94:           OPEN c_localcols;
95:           OPEN c_remotecols;
96:           
97:           WHILE c_localcols%FOUND LOOP
98:             
99:             IF v_localcol != v_remotecol THEN
100:               dbms_output.put_line('Found problem in table '|| v_localtable ||' - Inconsistency in column names.');
101:             ELSE
102:               IF v_localtype != v_remotetype THEN
103:                 dbms_output.put_line('Found problem in table '|| v_localtable ||' Column '|| v_localcol ||' data types are inconsistent');
104:               END IF; -- DATA TYPE
105:             END IF; -- COLUMN NAME
106:           END LOOP; -- COLUMN LOOP
107:           -- dbms_output.put_line('Finished local table '||v_localtable);
108:           -- dbms_output.put_line('Finished remote table '||v_remotetable);
109:           -- CLOSE COLUMN CURSORS
110:           CLOSE c_localcols;
111:           CLOSE c_remotecols;
112:           -- NEXT TABLE
113:           dbms_output.put_line('Table '|| v_localtable ||' checked');
114:           FETCH c_localtable INTO v_localtable;
115:           FETCH c_remotetable INTO v_remotetable;
116:         ELSE
117:           IF v_localtable > v_remotetable THEN
118:             dbms_output.put_line('Local table '|| v_localtable);
119:             dbms_output.put_line('Table '|| v_remotetable ||' not found in local DB');
120:             FETCH c_remotetable INTO v_remotetable;
121:           ELSE
122:             dbms_output.put_line('Remote table '|| v_remotetable);
123:             dbms_output.put_line('Table '|| v_localtable ||' not found in remote DB');
124:             FETCH c_localtable INTO v_localtable;
125:           END IF; -- TABLE COMPARE
126:         END IF;
127:  END LOOP; -- TABLES LOOP
128:   
129:  -- CLOSE TABLE CURSORS
130:  CLOSE c_localtable;
131:  CLOSE c_remotetable;
132:   
133:         dbms_output.put_line('');
134:         dbms_output.put_line('Process finished');
135:         dbms_output.put_line('----------------');
136:         dbms_output.put_line('');
137: END COMPARE_DB_STRUCTURE;

So, this procedure will alert of any difference found at table, column or data type level through the messages shown through the dbms_output.


This procedure can be easily edited and customized to work on 2 local DBs or even in 2 remote DBs.


Any comments, enhancement proposals and such are welcome.


Hope this can help any of you out there. Enjoy.

Execute Immediate and Dynamic SQL

When working with PL/SQL you may find that you would like to iteratively execute a query with different parameters.  Well, got you good news.  You can!

There’s an instruction that will make those ideas come true.

EXECUTE IMMEDIATE is an Oracle implementation that allows the developer to prepare a statement and then execute it from inside the PL/SQL block, defining also input and/or output parameters.

More so, this instruction will generate some tracing info that can be very useful when trying to model the business logic in the pl/sql program.

In its most basic form, it’s used like this:

EXECUTE IMMEDIATE <sql_query>;

This <sql_query> can be as simple or as complex as we want.  It can even be another pl/sql block.  So, you can use a variable to store the statement, let’s say v_smnt and then execute it with:

EXECUTE IMMEDIATE

v_smnt;

Even more, you can use bind variables or parameters with the simple addition of the USING and RETURNING INTO keywords followed by the pl/sql environment variables you want to use.

All these features make this command very powerful.  It also keep some tracking info in memory that you can access through some special variables, like SQL%ROWCOUNT.

Now that you know what would you be looking for, you can visit the next few links for more info on this command and its uses.

Oracle Docs - Command Diagram and elements explanation

Oracle Docs - Dynamic SQL explanation and some examples

And, of course, you can make questions here and I’ll answer as soon as I can.

How to: I need an empty Database – Part 2

In the first part of his post I showed the way to use IMP/EXP utilities from Oracle to get the DDL of a single schema or an entire database.  Some people don’t fully trust these utilities or just dislike the command line and prefer to work over procedures.  This post will give you another option to get a clean database using the information of the data dictionary on any DBMS or the user_<whatever> tables or the all_<whatever> tables from Oracle.
So, here we go.

Oracle

In particular, for the Oracle DBMS, I’ll use a package.  Let’s call it SCHEMA_CLEANER.  Now, we have to state what do we need?  For any database (that respects the relations between tables and data within those tables), the problems when you have to delete some data begin with the foreign keys being violated.
This indicates that the first thing to do is to DISABLE all those constraints so that we can delete all data in the schema without having to take core of the order we’re deleting that data.  But of course, we have later to take the time to enable all those constraints again, because we want to work with a consistent schema, and leaving the constraints disabled will lead almost for sure to inconsistent data.
Following, this we got 2 specifications for our package: DISABLE_FKS and ENABLE_FKS.
Finally, we need the procedure that will do the bulk delete.  So that’s one more specification for the package, let’s say: DELETE_DATA.
For his example I’ll be running the package procedures from the schema we want to clean.  Nevertheless, this code can be easily modified to be run as a DBA from the SYSTEM schema passing the schema to clean up as a parameter.
In this code, then I’ll use the user_<whatever> tables to get the info I need.  The mod would be to use the all_<whatever> tables and filter through the OWNER field in each table.
So, here is the code:

CREATE OR REPLACE PACKAGE SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS;
PROCEDURE ENABLE_FKS;
PROCEDURE DELETE_DATA;
PROCEDURE CLEAN_SCHEMA;

END SCHEMA_CLEANER;
/


CREATE OR REPLACE PACKAGE BODY SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to disable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' DISABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' disabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys disabled.');
END DISABLE_FKS;

PROCEDURE ENABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to enable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
dbms_output.put_line('Foreign Key found: ' || fkey);
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' ENABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' enabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys enabled.');
END ENABLE_FKS;

PROCEDURE DELETE_DATA AS
thetable user_tables.table_name%TYPE;

CURSOR ctable IS
SELECT table_name
FROM user_tables;

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('Starting data deletion process...');
OPEN ctable;
FETCH ctable INTO thetable;
dbms_output.put_line('Deleting, please wait...');
WHILE ctable%FOUND LOOP
dbms_output.put_line('Table found: '|| thetable);
dbms_output.put_line('Deleting data in '|| thetable);
EXECUTE IMMEDIATE ('DELETE FROM ' || thetable);
dbms_output.put_line(thetable || ' is empty.');
FETCH ctable INTO thetable;
END LOOP;
CLOSE ctable;
dbms_output.put_line('Data deletion process finished.');
END DELETE_DATA;

PROCEDURE CLEAN_SCHEMA AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SCHEMA_CLEANER.DISABLE_FKS;
SCHEMA_CLEANER.DROP_DYNAMIC_TABLES;
SCHEMA_CLEANER.DELETE_DATA;
SCHEMA_CLEANER.ENABLE_FKS;
END CLEAN_DB;

END SCHEMA_CLEANER;

/

How to: I need an empty Database! – Part 1

In your daily work, for various reasons you may need an empty database.  May be for testing purposes, for testing bulk data load or to identify changes over tables caused by an application that is working over the database.

Whichever the case may be, you need a clean database and not always got the time (or the option) to start following relational constraints backwards.

Here’s an option to get an empty database out from a fully working online one.

Import / Export  Utilities (Oracle only)

For the task at hand, using the IMP/EXP utilities can be quite helpful and fast. Both of them provide the option to import/export the full database, only a schema and with or without data.

If you don’t have any .DMP file or just want to create a new one, you can use the following command to export the database DDL.

EXP SYSTEM/mypswd FILE=fulldb.dmp FULL=Y ROWS=N

Or as well you can make a parameters file containing this information

FILE=fulldb.dmp
FULL=Y
ROWS=N
LOG=export.log

and then use it as a parameter itself as follows

EXP SYSTEM/mypswd PARFILE=myparams.dat

Now, if you do have a DMP generated, let’s say, by a backup policy, then you can just use the IMP utility to import the DDL mostly the same way as before with just some little changes.

Let’s state that when someone says “I need an empty database” it really means (usually) “I need an empty schema”

So, you must specify (to avoid loosing data dictionary and other important data) the parameters FROMUSER and TOUSER.  This will tell the utility which schema we want to import, as follows

IMP SYSTEM/mypswd FILE=fulldb.dmp FROMUSER=my_schema TOUSER=another_schema

In the next part, I’ll post some procedures that can be used to clean any schema of the database.  For Oracle, they can be packaged, for other DBMS you just can run the stored procedures.

As always, hope this helps…  And comments are welcome!

Good Practices for Creation Scripts - Part 1

When you're working with small databases with just a few tables, everything is handled very fast and easy.

But what happens when our small application starts to grow and some modules appear and require more and more tables? Why not start doing things the right way to avoid future "complete reconstructions" due to incompatibilities?

So, here are some tips:

• For each table, always have a code (2 to 4 chars)

employee --> emp
department --> dep
title --> ttl
works_in --> wrk

• Each column in the table must contain the code of its parent table. Foreign keys will have the referenced table code.

In table employee:
id --> emp_id
name --> emp_name
title --> ttl_id

• Always write complete creation scripts. Avoid using simplified coding style, because it will generate constraints with system-generated names, hard to find and identify later on. When a constraint is violated, it’s good to have it named to avoid encounters with messages like “Constraint SYS00013292384 violated”.
  

I'll present 2 ways to create a very small employee database, make your own conclusions:

The short way:

CREATE TABLE title (
id NUMBER(10) PRIMARY KEY,
desc VARCHAR2(50) NOT NULL
);

CREATE TABLE employee (
id NUMBER(12) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
title NUMBER(10) NOT NULL
);
ALTER TABLE employee ADD FOREIGN KEY (title) REFERENCES title (id);

CREATE TABLE department (
id NUMBER(2) PRIMARY KEY,
name VARCHAR2(50) NOT NULL
);

CREATE TABLE works_in (
emp NUMBER(12) NOT NULL,
dep NUMBER(2) NOT NULL
);
ALTER TABLE works_in ADD PRIMARY KEY (emp,dep);
ALTER TABLE works_in ADD FOREIGN KEY (emp) REFERENCES employee (id);
ALTER TABLE works_in ADD FOREIGN KEY (dep) REFERENCES department (id);

The right way (for me) to save us trouble in the future (and start making good coding habits):

CREATE TABLE title
(
ttl_id NUMBER(2),
ttl_name VARCHAR(50)
);

ALTER TABLE title ADD CONSTRAINT "PK_ttl" PRIMARY KEY (ttl_id);


CREATE TABLE employee
(
emp_id NUMBER(12),
emp_name VARCHAR(50),
ttl_id NUMBER(2),
);

ALTER TABLE employee
ADD CONSTRAINT "PK_emp" PRIMARY KEY (emp_id);
ALTER TABLE employee
ADD CONSTRAINT "NN_emp_name" CHECK (emp_name IS NOT NULL);
ALTER TABLE employee
ADD CONSTRAINT "FK_emp_ttl" FOREIGN KEY (ttl_id)
REFERENCES title (ttl_id);


CREATE TABLE department
(
dep_id NUMBER(2),
dep_name VARCHAR(25),
dep_location VARCHAR(50)
);

ALTER TABLE department
ADD CONSTRAINT "PK_dep" PRIMARY KEY (dep_id);

CREATE TABLE works_in
(
emp_id NUMBER(12),
dep_id NUMBER(2)
);

ALTER TABLE works_in
ADD CONSTRAINT "PK_wrk" PRIMARY KEY (emp_id, dep_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_emp" FOREIGN KEY (emp_id)
REFERENCES employee (emp_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_dep" FOREIGN KEY (dep_id)
REFERENCES department (dep_id);

If you use these simple tips, you’ll not only work in a more organized way, you’ll be more efficient when solving problems over databases with these kind of implementation.

Once again, I’m not telling this is THE WAY to do things.  Just one way that works for me.  Comments are always welcome.

Using other users' schemas as our own

As a DBA (A for Administrator or Architect), some times you'll be required to create, edit, drop or test schema objects of users of the database.

As you want to keep your DBA rights and don't want to bother creating new connections (on GUI clients) or just don't want to close your current session con SQL*Plus and due to the fact that is a pain in the back to write every schema object with the schema name in front of it... You may consider setting your default schema for the current session to the one of the user that requires your assistance.

How? It's really easy, you can simpy execute a query to ALTER your own session and point it to another schema. That's done as follows:

ALTER SESSION SET CURRENT_SCHEMA = the_schema_you_want_to_use

This will make all of your queries over that new schema as if you were its owner, of course maintaining your privileges as DBA.

More so, you may have groups of users that should work over the same schema, you can assign a role and then implement a trigger to alter the session when they log on, with a trigger that should look like this (not tested yet):

CREATE OR REPLACE TRIGGER set_default_schema
AFTER LOGON ON DATABASE WHEN (USER = 'XYZ')
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ABC';
END;

It can be even be modified to see if (USER IN (SELECT... FROM... WHERE...)) so you can get groups of users pointing at the same schema as soon as they login.

Drop that unknown NOT NULL constraint - Part 2

Recently I got word from someone at a Database Forum pointing that it would be easier to join a couple tables to get the name of the constraint we want to drop.

The code is this:

SELECT c.constraint_name
FROM user_tab_columns t, user_cons_columns c
WHERE t.table_name = my_table
AND t.table_name = c.table_name
AND t.column_name = my_column
AND t.nullable = 'N';

That's it, we got now the constraint name and can drop it easely.

That would shorten the script a lot, tho the performance will probably depend on various details as the number of rows per table and the position of the value we look up.

So, the new script would look as follows:

DECLARE
constname user_cons_columns.constraint_name%TYPE;
BEGIN
SELECT c.constraint_name INTO constname
FROM user_tab_columns t, user_cons_columns c
WHERE t.table_name = my_table
AND t.table_name = c.table_name
AND t.column_name = my_column
AND t.nullable = 'N';

EXECUTE IMMEDIATE 'ALTER TABLE my_table DROP CONSTRAINT ' || 'constname';
dbms_output.put_line('Constraint '|| constname || ' dropped successfuly!');
COMMIT;
END;

Nevertheless and despite the obvious change in the size and performance of this script, during the test, I found something interesting: it fails if the constraint is disabled, due to the fact that user_tab_columns.nullable contains the current value of the system itself and not the one from the constraint.

Oracle Error Codes - A nightmare? Not anymore!

Yes, usually we're working with a DB and all of the sudden an ORA-XXXXX error rises without a lot more info than the error code itself...

So you have to go Google-it, start looking in forums, blogs and all sort of strange net-places if not books to find what the... base does that error code mean...

Well, this is the shortest post I'll probably write here.

Just enter go to http://*error-code-here*.ora-code.com/

That means that if you're looking for the error code ora-00997, you must go to

http://ora-00997.ora-code.com

Enjoy!

And big thanks to people behind that site!
http://any-code.ora-code.com

Drop that unknown NOT NULL constraint - Part 1

Is very natural for any DBA to create tables through the ol' good DDL statement:

CREATE TABLE sysuser (
login VARCHAR2(20) PRIMARY KEY,
pswd VARCHAR2(20) NOT NULL
);

But that generates later problems because the DBMS will name those constraints as "SYSXXXXX", as I have discovered recently in my job. Some, will instantly say:
-Hey, wait. For that PK I use the ALTER TABLE statement and add a name like "PK_sysuser" to the constraint for better understanding of later raisen errors.

Well, yes. That's right, but we almost never take the time to add custom names to NOT NULL constraints. That's exactly the problem here.

Some time ago, a table was created (in development environment and later on in production), with its constraints and everything was fine until yesterday. A developer came to me, asking if it's posible to drop that constraint "automatically" since we don't have access to the production environment. The answer was clear, if it exists... It can be dropped. More so, the Data Dictionary got all the necesary information to do it. So it should be a quick and easy task to accomplish.

What I got:

• Table & field names

What I need:

• Find the name of the constraint
• Drop the constraint
  

I first used:

SELECT *
FROM user_constraints
WHERE table_name = my_table;

Got a bunch of data and got the search_condition of the constraint as "my_field" IS NOT NULL. And I thought - Job done! All I have to do is refine my query to use this field and I'll get the name of the constraint I want to drop; so it would be a simple PL/SQL block of about a half dozen lines.

So my fingers ran through the keyboard writing:

SELECT constraint_name
FROM user_constraints
WHERE search_condition = '"my_field" IS NOT NULL';

- AHA!!! Oracle error: ORA-00997!!!

So I googled a bit and found that LONG (datatype) fields can't be used in WHERE clauses!!
And, as Murphy's Laws dictates, search_condition is a LONG field.

To shorten this up, I'll say that I took some workarounds and finished creating this script:

SET SERVEROUTPUT ON;

DECLARE
aux user_constraints.search_condition%TYPE;
condition user_constraints.search_condition%TYPE;
constname user_constraints.constraint_name%TYPE;
CURSOR names IS
SELECT constraint_name
FROM user_constraints
WHERE table_name LIKE 'my_table'
AND constraint_type = 'C';
CURSOR search IS
SELECT search_condition
FROM user_constraints
WHERE table_name LIKE 'my_table'
AND constraint_type = 'C';
BEGIN
OPEN names;
OPEN search;

condition:= '"my_field" IS NOT NULL';
dbms_output.put_line('Contrained column is: my_field');

LOOP
FETCH names INTO constname;
FETCH search INTO aux;

dbms_output.put_line('Constraint name is: '||constname);
dbms_output.put_line('Search condition is: '|| aux);

IF aux = condition THEN
dbms_output.put_line('Selected constraint to drop: '|| constname);
END IF;

EXIT WHEN aux = condition;
END LOOP;

CLOSE names;
CLOSE search;
EXECUTE IMMEDIATE ('ALTER TABLE my_table DROP CONSTRAINT ' || constname);
COMMIT;
dbms_output.put_line('Constraint dropped succesfully');

END;

I hope this helps some of you, and I'm not stating here this is the best way to do it. It's just one way to do it. I even created a Stored procedure with table_name and column_name as INPUT parameters that will drop any "unknown" NOT NULL constraint from any table and any field.

All comments are welcome!