data dictionary

Check if JVM is Installed in Oracle

How to Check if JVM is Installed in Oracle

I recently thought of using Java’s built-in BigInteger.gcd() method inside a PL/SQL function wrapper to calculate GCD in SQLs. And so I wrote the Java code and ran it on Oracle XE. Things didn’t go as planned.

Read the Full Article →
Thumbnail image for Range Interval Partitioning

Range Interval Partitioning

Oracle 11G brought with it a beautiful new twist to range partitioning – the ability to create partitions on-the-fly rather than having to pre-create all partitions. Hello interval partitioning.

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

How will you find out the number of rows in each table in a schema in Oracle? Here are two approaches with their pros and cons.

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

20 Things You Should Know About Oracle Views

A view is like a virtual table. It takes the output of a query and treats it like a table. A view can be based on one or more tables or other views. These tables/views are called base tables. A view takes up no storage space other than for the definition of the view in […]

Read the Full Article →
Rename Column in an Oracle Table

Rename Column in an Oracle Table

Can you change the name of a table column in Oracle? Oracle 9i and above, you can very easily – there is a direct RENAME column command. Pre-Oracle 9i, you have to do a little more. In this article, we’ll look at both the current and older methods of renaming a table column in Oracle.

Read the Full Article →
Database Object Dependencies

Which objects depend on a given table?

A database table usually has other objects referring to it – tables linked through foreign keys, stored procedures referring to it. You might want to find out – which packages refer to this table? Are there views created on it? If I change the table design, how many and which objects will be affected? The […]

Read the Full Article →