data dictionary

Thumbnail image for Find Number of Rows in Each Table in a Schema

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 →
Thumbnail image for How to Delete Old Data from the Entire 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 How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

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

In the last post you saw a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks our 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 […]

Read the Full Article →
Thumbnail image for The Special 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 →
Thumbnail image for 20 Things You Should Know About 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 →
Thumbnail image for 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 →
Oracle 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 design of the table, how many and which objects will be […]

Read the Full Article →