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:
- Prior to the DROP
- A TIMESTAMP
- An SCN
- A restore point
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.