Database Design

The design of an overall database system, including the choice of data structures, identifying data relationships and defining models on the basis of these relationships.

Thumbnail image for Unique Constraint vs Unique Index in Oracle

Unique Constraint vs Unique Index in Oracle

A unique constraint and a unique index, on the face of it, look very alike. Dig deeper, and you will see that they differ. A rundown of unique constraint vs unique index in Oracle.

Read the Full Article →
JSON in the database

Storing JSON Data in Oracle: Why and How

Storing JSON data in Oracle has been made easy in version 12c. A demo of how JSON can be stored in Oracle and validated using the IS JSON check.

Read the Full Article →
DBMS_REDEFINITION for online table redefinition

DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]

Partitioning a non-partitioned table in an Oracle pre-12.2 database, using DBMS_REDEFINITION built-in methods. Scripts and demo.

Read the Full Article →
Thumbnail image for In Which Package Is This Procedure Placed?

In Which Package Is This Procedure Placed?

Given a stored procedure name and the knowledge that it sits in *some* PL/SQL package in the database, how should one find out the name of the package in which the procedure is placed?

Read the Full Article →
Thumbnail image for Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB

Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB

Binary XML, object-relational, or CLOB? An overview of approaches for storing XML data in Oracle to best fit the nature of the data and its expected use.

Read the Full Article →
Thumbnail image for DATE Format SQL for Dates Stored as Strings

DATE Format SQL for Dates Stored as Strings

Convert VARCHAR2 to DATE with appropriate format masks, for comparing two dates stored as strings with different DATE formats in the database.

Read the Full Article →
Thumbnail image for Range Interval Partitioning

Range Interval Partitioning

Create Oracle table partitions on-the-fly using range interval partitioning, a beautiful new feature in 11G that obviates the need to pre-create partitions.

Read the Full Article →
Oracle Total Recall

Oracle Total Recall for Tracking History

Oracle 11g Flashback Data Archive (Oracle Total Recall), essentially meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities – say, the trail of changes to customer’s […]

Read the Full Article →
Thumbnail image for 10 Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Follow these ten data modeling guidelines to make sure that your application database design is accurate, usable and scalable.

Read the Full Article →
Difference between User and Schema in Oracle

The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it? This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

Read the Full Article →