Creating and Managing Tables

Objectives
After completing this lesson, you should be able to
do the following:
• Describe the main database objects
• Create tables
• Describe the data types that can be used when
specifying column definition
• Alter table definitions
• Drop, rename, and truncate tables

Database Objects
Object Description
Table Basic unit of storage; composed of rows
and columns
View Logically represents subsets of data from
one or more tables
Sequence Numeric value generator
Index Improves the performance of some queries
Synonym Gives alternative names to objects

Database Objects
An Oracle database can contain multiple data structures. Each structure should be outlined in the database
design so that it can be created during the build stage of database development.
• Table: Stores data
• View: Subset of data from one or more tables
• Sequence: Numeric value generator
• Index: Improves the performance of some queries
• Synonym: Gives alternative names to objects
Oracle9i Table Structures
• Tables can be created at any time, even while users are using the database.
• You do not need to specify the size of any table. The size is ultimately defined by the amount of space
allocated to the database as a whole. It is important, however, to estimate how much space a table will use
over time.
• Table structure can be modified online.

Naming Rules
Table names and column names:
• Must begin with a letter
• Must be 1 to 30 characters long
• Must contain only A–Z, a–z, 0–9, _, $, and #
• Must not duplicate the name of another object
owned by the same user
• Must not be an Oracle Server reserved word

Naming Rules
Name database tables and columns according to the standard rules for naming any Oracle database object:
• Table names and column names must begin with a letter and be 1–30 characters long.
• Names must contain only the characters A–Z, a–z, 0–9, _ (underscore), $, and # (legal characters, but
their use is discouraged).
• Names must not duplicate the name of another object owned by the same Oracle Server user.
• Names must not be an Oracle Server reserved word.
Naming Guidelines
Use descriptive names for tables and other database objects.
Note: Names are case insensitive. For example, EMPLOYEES is treated as the same name as eMPloyees
or eMpLOYEES.

The CREATE TABLE Statement
• You must have:
– CREATE TABLE privilege
– A storage area
• You specify:
– Table name
– Column name, column data type, and column size
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);