ORA-01940: cannot DROP a user that is currently logged in

" ORA-01940: cannot DROP a user that is currently logged in "

ORA-01940

We were trying to drop a user ( schema ) in our local Oracle Database & we suddenly hit the ORA-01940 error.

We closed all the SQL Clients ( Oracle SQL Developer, SQL* Plus, TOAD ) that we were using, but still the error persisted. We were quite flummoxed - until, we googled and landed on this article from Burleson Consulting.

We followed the advice in the article & quitely killed the "stale" sessions. We could then drop the schema and recreate it.

I have always admired the articles from Burleson Consulting & found the explanation of the ORA-01940 error very informative.

Revision Control for Rows

Revision Control deals with managing multiple revisions of an entity. The core functionality in any Revision Control system is to ensure that the previous version is not lost. Revision Control is a vast topic that merits hours of study & practice.

I definitely don't have the breadth of knowledge or experience to comment on various "must-have" features, etc. However, I did encounter an interesting scenario where I put Revision Control to practice for information stored in rows of a Database Table.

We can achieve Revision Control for rows in a Database Table in a simple way - with two columns. We can use one column to store the Row version and another to "flag" the row that contains the latest version.

I am using an Oracle XE Database & Oracle SQL Developer to model the tables, fashion out the queries, etc. You could do the same with any other Database & your favorite tools.

Here, I consider the example of an Item "on display" at a shop. The Product is available in multiple version ( different colors ), but only one "version" of the product is currently on display ( the item ).

First, I'll model the database table. I have made a few assumptions to arrive at this model :-

Assumption # 1 :- All the rows are linked by a common identifier ( in my case, the Product ID ).
Assumption # 2 :- At any given point of time, only one version of the product is "active".
Assumption # 3 :- Each "version" is saved into the database. Any "undo" will simply revert back to the previous saved version.
Assumption # 4 :- Each "undo" or "redo" is saved into the database as the current version.

Here's a table that shows this design :-

CREATE TABLE ITEM
(
ITEM_ID NUMBER PRIMARY KEY,
ITEM_PRODUCT_ID NUMBER,
ITEM_DESCRIPTION VARCHAR2(100),
ITEM_COLOR VARCHAR2(10),
ITEM_VERSION NUMBER,
ITEM_CURRENT_VERSION VARCHAR2(1) CONSTRAINT check_ver_flag CHECK(ITEM_CURRENT_VERSION IN ('Y','N'))
);

Here's the data that I stuffed into the table :-

INSERT INTO ITEM VALUES (1,566,'Smiley Dolls','Red',1,'N');
INSERT INTO ITEM VALUES (2,566,'Smiley Dolls','Blue!',2,'N');
INSERT INTO ITEM VALUES (3,566,'Smiley Dolls','Yellow',3,'Y');
INSERT INTO ITEM VALUES (4,566,'Smiley Dolls','Cyan',4,'N');

COMMIT;

Now, to dish out the queries...

Which is the current version of the Product ?

SELECT ITEM_DESCRIPTION, ITEM_COLOR FROM ITEM WHERE ITEM_CURRENT_VERSION = 'Y';

ITEM_DESCRIPTION ITEM_COLOR
------------------- ----------
Smiley Dolls Yellow

1 rows selected

Can we "undo" the current version & revert to the previous saved version ?

SELECT a.ITEM_DESCRIPTION, a.ITEM_COLOR FROM ITEM A, ITEM B
WHERE A.ITEM_VERSION = B.ITEM_VERSION-1 AND B.ITEM_CURRENT_VERSION = 'Y'

ITEM_DESCRIPTION ITEM_COLOR
----------
Smiley Dolls Blue!

1 rows selected

( After this, we set the current version flag to "Y" )

Revision Control can be achieved in this simple manner.

Parent Child Relations in a Database Table

Parent - Child relationships ( hierarchical relationships ) are very common & often crop often during database designs.

You can expect to find Parent -Child relationships while modeling employee-manager, office-outlets & other similar concepts. I recently encountered the need for a parent - child relationship in a need to model a product-sub product concept.

You can find a lot of literature that talks about this topic in detail. I just want to add my 2 cents..

The hierarchical relationships are best explained by examples. Here, I consider a popular fast food chain of outlets ( "Yummies" ) that has outlets across the city.
Al the outlets in a particular part of the city "report" to a single outlet - the "parent" outlet.

All these "parent" outlets in turn "report" to a single "parent" outlet - something like a "head office".

The "report"s could be anything - daily sales figures, inventory status, customer feedback, staff shortages, etc. However, at this point of time, the main focus is to model the relationship of a parent-child outlet chain.

I am using an Oracle XE Database & Oracle SQL Developer to model the tables, fashion out the queries, etc. You could do the same with any other Database & your favourite tools.

First, I'll model the database table. I have made a couple of assumptions to arrive at this model :-

Assumption #1 : Each child outlet has a single parent outlet.
Assumption #2 : Each outlet has a parent outlet, except the "root" outlet ( head office ) .

Here's the table structure that I have designed :-

CREATE TABLE OUTLET
(
OUTLET_ID NUMBER PRIMARY KEY,
OUTLET_NAME VARCHAR2(30) NOT NULL,
OUTLET_PARENT NUMBER
);

The OUTLET_ID of the "parent" outlet is stored in the "child" outlet's OUTLET_PARENT.

Here's the data I stuffed into the table :-

INSERT INTO OUTLET VALUES(1,'Yummies M G Rd',null);
INSERT INTO OUTLET VALUES(2,'Yummies BSK ',1);
INSERT INTO OUTLET VALUES(3,'Yummies BSK I Stage ',2);
INSERT INTO OUTLET VALUES(4,'Yummies BSK II Stage ',2);
INSERT INTO OUTLET VALUES(5,'Yummies BSK III Stage ',2);
INSERT INTO OUTLET VALUES(6,'Yummies Food World ',3);

COMMIT;

Now, to dish out the queries...

Which outlet is the "mother of all outlets" ? ( root outlet / head office )

The outlet that has no parent ( OUTLET_PARENT is null ) is the "mother of all outlets".

SELECT * FROM OUTLET WHERE OUTLET_PARENT IS NULL;

OUTLET_ID OUTLET_NAME OUTLET_PARENT
--------- ------------------ ---------------
1 Yummies M G Rd

1 rows selected

Which are the outlets under the root outlet ?

SELECT
a.outlet_id,
a.outlet_name,
a.outlet_parent,
b.outlet_parent
FROM outlet a,
outlet b
WHERE b.outlet_id = a.outlet_parent
AND b.outlet_parent IS NULL;

OUTLET_ID OUTLET_NAME OUTLET_PARENT OUTLET_PARENT_1
--------- ------------------ --------------- ---------------
2 Yummies BSK 1

I can now dish out various queries that involve parent-child relationships on the chain of outlets.

The model described above works best only if the two critical assumptions are satisfied. If not, you may need to explore alternate ways to achieve this relationship - e.g.: moving the relationship data ( OUTLET_ID, OUTLET_PARENT) to a separate table, etc.

Ten Common Database Design Mistakes

I recently chanced upon an article written by Louis Davidson titled "Ten Common Database Design Mistakes". The article summarizes the list of critical mistakes that should be avoided while designing a database.

As the author humbly points out in the article, the list also summarizes the mistakes he too did during the early stages of his career. I liked the article as I had to endure the same learning curve as the author.

Here's a summary of the most common mistakes :-

1. Poor design/planning
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity/guid columns as your only key
7. Not using SQL facilities to protect data integrity
8. Not using stored procedures to access data
9. Trying to build generic objects
10. Lack of testing

I couldn't have summarized it better !