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 →
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 →
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

Three ways to find out your Oracle database version in SQL and PL/SQL: using V$VERSION, V$INSTANCE, or DBMS_DB_VERSION constants.

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 →
Thumbnail image for Drop and Recreate All Foreign Keys on Oracle Table

Drop and Recreate All Foreign Keys on Oracle Table

You might need to reistate a dropped table in Oracle, and in doing so, restore the foreign keys as well. Here’s how to drop and recreate all foreign keys on an Oracle table.

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 →