Oracle9i OCA Certification Training Documentation

by Deborah Dorsey & Michelle Fintsch

 

There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. Oracle was founded in 1977, and sold the first commercial relational database and is now the world’s leading database company and second largest independent  software company, with revenues of more than $10 billion, serving more than 145 countries.

 

The basic structure of data storage in Oracle9i database is a table. A table consists of columns and its characteristics. Data is stored in the table as rows.  SQL is a simple, yet powerful, language used to create, access, and manipulate Data and structure in the database. SQL statements can be categorized as:

Data

Manipulation                     

Language

(DML )

Used to access, create, modify, or delete data in the existing structures of the database. DML statements include those to query information (SELECT), add new rows (INSERT), modify existing rows (UPDATE), delete existing rows (DELETE), perform a conditional update or insert operation (MERGE), see an execution plan of SQL (EXPLAIN PLAN), and lock a table to restrict access (LOCK TABLE).

Data

Definition

Language

(DDL)

Used to define, alter, or drop database objects and their privileges. DDL statements Include those to create, modify, drop, or rename objects (CREATE, ALTER, DROP, RENAME), remove all rows from a database object without dropping the structure (TRUNCATE), manage access privileges (GRANT, REVOKE), audit database use (AUDIT, NOAUDIT) and add a description about an object to the dictionary

(COMMENT).

 

To log in to Oracle you must be a user created in the Oracle9i database granted. The only way you can become a user is to have someone with system privileges create you as a user. Here is a step by step procedure of how to log in to Oracle and how the users are created. This is how you log into SQL Plus in Oracle9i.

 

 

This is the log on screen. You must be a user created in the database in order to log in to Oracle9i. I have system privileges because I am the owner of the computer. I have access to all administrative privileges because that’s the way I set up the software when it was installed. There are still default settings and I haven’t changed anything. You would normally change all the default settings of the software when you install it so that anyone can not log into a database. I’ve actually created a user with my name under the sys dba schema. Also, usually the database is configured to accept a connection string. I decide not to use a connection string to make it a little easier, as this database is only being used for practice.

 

 

Below shows you that you are connect to the database.  I can now see tables and views and manipulate data within the schema. I can connect to other schemas and see their tables and views but I cannot manipulate the data in those schemas unless the administrator or user grants specific privileges me.

 

 

 

 

 

 

 

Below is an example of how to see every table under your schema. Because I was created under another user, (sys dba) I have privileges to that schemas tables and views. There are a great number of tables because sys dba is the default schema within the Oracle9i database.

 

 

The Oracle Certification Program begins with the Associate level. At this apprentice skill level, Oracle Certified Associates have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. Earning your Oracle Certified Associate credential as a beginning IT professional will give you recognition for your foundation of knowledge using Oracle technologies. This recognition will differentiate you from those without any credentials, and can help you build your career by obtaining access to entry-level opportunities.

 

To become an Oracle Certified Associate, you must pass the exams required for the Oracle Certified Associate level of your selected job role. To earn the Oracle9i Database Administrator Certified Associate credential, two exams are required; the first one can be taken via the Internet, while subsequent exams must be taken in a proctored environment. You must pass the following two exams to obtain your Oracle9i DBA OCA credential:

After mastering SQL in Oracle9i, you need to have knowledge of the Oracle9i Architectural Components. This is very important when taking the second part of the OCA, which includes Exam #1Z0-031 Oracle9i Database: Fundamentals 1. The Architecture consists of the Logical Storage Structures and the Physical Structure. It is important to know these structures when setting up Oracle9i software, creating databases, and administrating the databases.

Here’s an example of the Logical Storage Structure:

 

 

Oracle SQL can be used on Windows, Unix or on DOS platforms. Here’s and example of connecting on DOS platform:

Go to Start, Run                                               Run Cmd

          

 

 

 

 

 

 

 

 

 

Now you are in Dos. You can connect to a database once you are in SQL. You have to startup the process first by typing net start.

 

Then you must start up SQL. You can only start up through and administrator. I’ve used sysdba and the code to startup SQL is: C

 

Once you are connected to SQL you can connect to the database using your user name and password.

 

Why Become an Oracle Certified Professional?

The demand for professionals in information technology (IT) is high, and the competition for jobs is intense. Individuals, experienced or new to the profession, need to know what skills make them attractive to employers.

Employers look for ways to distinguish employees and prospective employees who have the solid foundation of skills needed for effective performance. The Oracle Certification Program helps the IT industry make these distinctions by establishing a standard of competence in specific job roles.

An Oracle Technical Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and skill.

 

 

Her