Tables and Views

I think I am starting to get the hang of locking in Oracle. Specifically I can tell which locks prevent other locks from going through. Now if only I could remember all these views in the data dictionary, I will be ready for the next topic.

My plan is to do a breadth first study for my first Oracle exam. So I have moved on to tables, columns, and views. Here are some hints I have learned. I always knew you could specify a default value for a column in a table. However I thought it could only be a literal. It turns out you can use things such as SYSDATE, which behaves as expected.

Here is some weird trivia. If you enclose a table name in double quotes, its case will be preserved in the Oracle data dictionary. Normally the name is stored as all caps.

The maximum number of columns in a view is 1000. The same holds true for a table in Oracle. When you CREATE OR REPLACE a view, the privileges are preserved. If you clone a table using the CREATE TABLE ... AS SELECT, then any NOT NULL constraints are copied in the clone as well. Finally you cannot specify the NOT NULL constraint when adding a column via ALTER TABLE if there are any rows in the table at all.

That's all for now. I think I will finish by saying I had a turn around and got some good customer service from Oracle University, the training arm of Oracle Corporation. Read all about it in my Micro ISV Blog.