Oracle Concepts - Administering Oracle
Oracle Tips by Burleson Consulting
Administering Oracle Objects and Constraints
One of the main responsibilities of Oracle DBA’s is the
creation of objects and constraints within the database. Once you have the
database and tablespaces created (which we discussed earlier in the book), then
you can create objects. Objects are things stored inside the database. They are
used to store data, and to make accessing that data faster and easier. This
chapter first introduces you to the management of basic Oracle objects such as
tables and indexes. We will then introduce you the management of Oracle
Administering Oracle Objects and
One thing to mention is that before you start creating
objects, you should first create a user account that you will create these
objects in. The newly created Oracle database does come with a few users,
already built in (for example, SYS and SYSTEM). These accounts are system
management accounts or sample accounts, and really are not designed for you to
create your own objects in. When creating your own objects, you should first
create a special user for them. We cover how to create users in the next
For the examples in this chapter, we will use the SCOTT
user, which will have been created for you if you followed the database creation
instructions in chapter two.
Administering Oracle Tables
Oracle Tables are the “bookshelves” of Oracle. This is
where Oracle stores your data. In this section we will discuss what tables are
for, and then we will discuss how to create, alter and drop Oracle tables.
As we said, tables are used to store data. Tables as
assigned a name when they are created. This name should describe what data is
stored in the table, such as cars, employees or addresses.
A table is created using the “create table” command.
When created, the table is assigned to a tablespace (which we discussed in
Think of a table like a spreadsheet in a way. It has
columns, which are defined when the table is created. Each column is given a
name to describe the data that is contained in the column. Each column is also
assigned a datatype, which indicates what kind of data may be stored in the
For example, if you want to store letters in the table,
you would make the datatype a varchar2 (varchar stands for varying character,
which means the column data can vary in length). A varchar2 also can hold
If you want to store numbers, then the column will be a
number type, and if you want to store date/time stamps, the column type would be
The following table lists the basic data types that you
will use when dealing with tables as a beginning DBA:
Note that you can define how many decimal points a given
number has, and how big the number can be. In the first example, the number can
only have 5 digits in it (99999). In the second, it can have 5 digits, but 2 of
those are after the decimal point (999.99). Finally, the last number is
This is a character column that can vary in length.
This is good, because it means that the column is probably going to take up less
space overall than one defined as a char(30). For example, if we put the
character string “This is a test” into a char(30) column, it would always take
up 30 bytes. If we put it into a varchar2(30) column, it would only take up 14
bytes. This difference can be big when there is a lot of data to be stored.
There are other data types, but these are good to get
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.