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.