DBA

Tasks typically handled by a database administrator, such as installation, patching, configuration, migration, security, troubleshooting, monitoring, backup, recovery.

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 →
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 →
When should we use dynamic SQL

When Should We Use Dynamic SQL?

We know that, if there exists a choice between static SQL vs dynamic SQL, static SQL is the way to go. In that case, why and when should we use dynamic SQL?

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 →
Oracle XML Object-Relational Storage

Oracle XML Object-Relational Storage [EXAMPLE]

Object-relational storage of XML data in Oracle is based on shredding the XML data into SQL objects. Scripts and stepwise execution for object-relational XML storage.

Read the Full Article →
Oracle Binary XML Storage

Oracle Binary XML Storage, With or Without XML Schema [EXAMPLE]

The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema. This post shows working examples of binary XML storage 1.1. without XML Schema 1.2 with XML Schema

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 Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view […]

Read the Full Article →