Oracle Concepts - Administering Oracle Objects 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 constraints.

Administering Oracle Objects and Constraints

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 chapter.

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.

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 earlier chapters.

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 column.

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 numeric characters. 

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 date. 

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 unbounded.

 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 you started.

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 publisher here.

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.