Data definition language (DDL) commands define and maintain data structures in the database. For example, commands for creating tables, modifying packages, dropping constraints.
As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it?
This post helps to clarify the sameness – or otherwise – of Oracle user and schema.
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.
Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
In a perfect world, the database design would be firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add a column to an existing table.
Oracle allows you to alter a table easily for adding a column, but what if you want the new column added at a specific position in the table? Now you’re in a spot. There is no command to “alter table add column at position 2”; Oracle simply places the column after all the existing columns.
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.
You have probably heard other DBAs/developers and websites talk about creating a database table through CTAS. Does that term leave you befuddled? There’s a very simple explanation to that acronym – read on!
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.