- I took Test 1: Introduction to Oracle: SQL and PL/SQL - The exam costs $125 (list price, there may be discounts). You need to pass five exams to get the Oracle8 Certified DBA status, and if you fail an exam, you must pay again $125 when you retake it. So this is not cheap (especially if you need any other learning materials). - I had to answer 60 questions in 120 minutes. Time was not a problem for me, although I considered each question carfully, I was done after approximately 90 minutes. The testing software allows you to mark questions and use the spare time at the end to review these marked questions or any other questions. The number of questions you have to answer may vary. - Out of the 60 questions, I had to get 43 right to pass (I got 54 right). - The exam is completely closed book. You must even leave your purse outside the room, and they say that they videotape you (you are also watched all the time from behind). - There was at least one question which I didn't understand because of my imperfect English and lacking experience with business notions. In the assessment test, there was one question which I believe was not formulated correctly (but native speakers may understand it). In general, you need to read the questions very carefully. - Knowledge about the ALTER TABLE command is needed (e.g. adding/dropping constraints). We didn't treat this in class (maybe I will change the course materials in INFSCI 2710 to cover it). The RENAME command and ALTER DATABASE RENAME is also important. - In PL/SQL, you need to know tables and records (which we also didn't treat). - You also need to know the implicit cursor and its attributes, e.g. SQL%ISOPEN. - They showed me a PL/SQL program containing "&&" in front of a variable. I have never seen that and it doesn't appear in the index of the PL/SQL manual. If anybody knows it, please send me an email. (Maybe it was only some formatting command left over. It didn't influence the result in any way. In general, I found most PL/SQL questions simple (if you know some programming language)). - You need to know the Oracle Data Dictionary quite well, at least the part which is important for normal users (it will again be tested in the DBA exam, then probably with the more storage oriented things). E.g. constraints, comments, tables/columns/objects, grants. - You need to know variables in SQL*Plus (DEFINE, ACCEPT). - It is strange that when they showed a database schema with a foreign key the arrow was pointing in the opposite direction as I usually do it. E.g. if COURSE contains a foreign key referencing INSTRUCTOR, my arrow points from INSTRUCTOR to COURSE, but their arrow points from COURSE to INSTRUCTOR. Don't get confused (the foreign key is clear, I had no problems). - You need to know what are legal identifiers in Oracle SQL (e.g. for naming tables, columns, etc.) - The TRUNCATE command is also important. It works like DELETE FROM (i.e. delete all rows), but it cannot be rolled back and frees all storage and sets the hight water mark back. I didn't know about this, but fortunately it appeared in the assesment test. We would have needed it to delete all rows from large tables without deleting the table definition and without getting problems with the rollback segments. - You need to know about system privileges and roles, not only the standard grant command for object privileges. - You need to know the ALTER USER command, at least for specifying a password. - You need to know a bit about indexes already in this first exam, not only in the performance tuning exam. - You need to know datatype functions for strings, numbers, dates. You should know that / and * bind stronger (are evaluated before) + and -, and that all these operators are left associative, e.g. that 5 - 3 - 1 gives 1 and not 3. - You should know about transactions, which commands end a transaction (e.g. all DDL commands), and you also need to know about savepoints. - The CREATE VIEW statement also allows CREATE OR REPLACE VIEW (just as CREATE OR REPLACE PROCEDURE). The advantage over dropping and recreating the view is that you do not have to grant privileges again. In an assessment test, also CREATE FORCE VIEW appears (I haven't checked it yet in the manual). - You need to know about sequences. - Sometimes, guessing may help. (I had 9 questions where I really wasn't sure). - Unfortunately, at the end they don't show you which questions you got wrong. This would be an important learning experience (and after all, you paid $125). They do show you areas where you lost points, but e.g. I got one question wrong for simple SQL queries, and one for CREATE TABLE, both of which are areas where I feel very confident. Good luck! (But don't sue me or the University if you fail. You need to know more than we did in the course, you need some practical experience, you need to know the English language sufficiently well (it is possible to take the exam in other languages), and you need a bit of luck). Official List of Topics (but please consult the latest Candidate Guide): ----------------------------------------------------------------------- - Selecting Rows - Limiting Selected Rows - Single Row Functions - Displaying Data from Multiple Tables - Group Functions - Subqueries - Specifying Variables at Runtime - Overview of Data Modeling and Database Design - Creating Tables - Oracle Data Dictionary - Manipulating Data - Altering Tables and Constraints - Creating Sequences - Creating Views - Creating Indexes - Controlling User Access - Overview of PL/SQL - Developing a Simple PL/SQL Block - Interacting with Oracle - Controlling Flow in PL/SQL Blocks - Processing Queries by Using Explicit Cursors - Error Handling ------------------------------------------------------------------------------- NOTE: A student told me that && in front of a variable in SQL*Plus is not a syntax error, but legal. It means that Oracle will take the previous value for that variable, whereas if you use only &x, it will ask you again for a value for x whenever &x appears.