When I first started studying for the Oracle SQL and PL/SQL certification exam, I had no study materials. So I just Googled the web for information. This helped me learn a lot. However it was a slow process as I had to cull through the query results.
This all changed when I got the OCP Oracle 8i DBA SQL and PL/SQL study guide book. I have gone through all 10 chapters. It was great because there were many tough example questions at the end of each chapter.
Now that I am done with the book, I am going over my notes from when I looked for material on the web. And I am finding a bunch of gems. For example, you can use the ALTER TABLE statement to do things with columns in a table. Most of the time, this statement does not include the word COLUMN in the command. That applies to adding and modifying columns. However to remove or rename a column, you have to use the word COLUMN. It is difficult to keep the syntax straight.
Let's talk a little bit about constraints. You do not have to name them when you create them. Oracle will give them a default name. Constraints are for columns in tables. Views cannot have constraints. Views can be created with deferred constraints. That means the constraint is not enforced until the transaction is committed.
You cannot drop a table unless you first drop some constraints which restrict it. For example, if you have a child table with a foreign key constraint pointing to the parent table, you cannot drop the parent table before this foreign key constraint on the child is dropped.
Teamwork Challenges - One of the newer guys on the team asked our project manager for some info. He needed to figure out how to get his scripts logging in to run unattended. Th...