Data Dictionary

Read-only reference tables and views for Oracle database metadata, such as definitions of schema objects, tablespace and auditing information, etc. Most data dictionary views come in three flavors, distinguished by their prefixes: DBA, ALL and USER.

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 →
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* package in the database, how does one find out *which* package?

Read the Full Article →
Proxy authentication in Oracle

Proxy User Authentication in Oracle

Proxy user authentication in Oracle provides an effective way to manage both security and ease of setup, especially useful in a multi-user work environment.

Read the Full Article →
Check if JVM is Installed in Oracle

How to Check if JVM is Installed in Oracle

Creating Java source in Oracle gives an ORA error? Check if JVM is installed and valid in the Oracle database, this way.

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 →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Oracle schema record count

Find Number of Rows in Each Table in a Schema

Ways to find out the number of rows in each table in a schema in Oracle – one via data dictionary lookup, the other via dynamic SQL.

Read the Full Article →
Delete Old Data from Database Schema

How to Delete Old Data from the Entire Schema

We may want to slim down a database schema, pruning old data from all tables, for a number of reasons. Let’s say to set up the test environment for a data migration project, a replica of the legacy database schema has been created. The legacy database schema has millions of records spanning multiple years, but […]

Read the Full Article →
Drop Recreate Foreign Keys

How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

The last post showed a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it. The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated […]

Read the Full Article →
Oracle DUAL Table

The Special DUAL Table

DUAL is a special one-row, one-column table in Oracle’s data dictionary. Of what use is DUAL? DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table – if you want to know […]

Read the Full Article →