Database Objects

I need to brush up on my skills to modify columns in tables, create indexes, and create constraints. Just don't get a lot of practice in these tasks. I thought I would write down some notes on these and other Oracle object manipulation commands.

This might be obvious. But you cannot add a NOT NULL constraint to a column on a table with records in it (which don't meet the constraint). When you create a foreign key column, you can set the constraint as DEFERRABLE. It won't be enforced until a COMMIT has been issues, or until you explicitly set the constaint as IMMEDIATE.

You can create an index on a column right when you are creating the table. The command can reference an existing index name. Or more commonly, it can just stick the index creation SQL right in there with the column definition in parentheses.

Function based indexes allows you to store indexes on the values of a function. That way when SQL uses the function, the optimizer will use the index to speed things up.

FLASHBACK lets you recover objects and data. For example you can do this on a TABLE. You could also do this on DML that has transpired. When you do it on a table, it recovers the table from the DROP TABLE command. You can't restore the state prior to when the last ALTER TABLE was issued. You can restore it to a newly named table. Non-bitmap indexes are recovered. Non foreign key constraints are recovered. And privileges are recovered.

The FLASHBACK works because when you DROP TABLE, it gets saved away. See details by querying the USER_RECYCLEBIN view. However if you issue a PURGE, the dropped table will be removed from the recycle bin and you cannot FLASHBACK recover it.

The FLASHBACK can bring a table back to a point in time defined by:

1. Prior to the DROP
2. A TIMESTAMP
3. An SCN
4. A restore point

External tables are a special kind of table. They are read only. The metadata about the table is stored in the database. The data is stored outside the database. You can SELECT from the external table. But you cannot issue DML against it. There are no indexes, constraints, LOB columns or unused columns allowed on an external table.

To use an external table, you must first CREATE DIRECTORY where the file will be located. Then you grant privileges on the directory. Finally you create the table with a special EXTERNAL syntax. You have to specify how the table will be loaded (i.e. SQL*Loader or DATAPUMP). We actually use external tables on our project when we have a file we need to access from database code.