Read-only reference tables and views for Oracle database metadata. Oracle’s data dictionary includes 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.
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 as well.
How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.
DUAL is a special one-row, one-column table in Oracle data dictionary.
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 the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:
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 the data dictionary.
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 for how to rename column in an Oracle 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 table ALL_DEPENDENCIES has the answer to the question: Which objects refer to this table? Walk through the following examples for more.