Skip to content Skip to main navigation Skip to footer

Creating Tables

Objectives

This lesson covers the following objectives:

  • List and categorize the main database objects
  • Review a table structure
  • Describe how schema objects are used by the Oracle database
  • Create a table using the appropriate data type for each column
  • Explain the use of external tables
  • Query the Data Dictionary to obtain the names and other attributes of database objects

Purpose

  • Up until now, you have selected, updated, inserted, and deleted information in the existing tables of a database.
  • As a Database Administrator (DBA), you will be expected to know how to create tables as well.
  • In this lesson, you will learn which database objects are most frequently used, how to look at the table structure, and how to create new tables.
  • Your tables will be small compared to tables that hold millions of rows and hundreds of columns, but creating a small table involves the same SQL statements and syntax as creating a very large one.
  • You will also learn about external tables—tables which are similar in structure to the normal Oracle database tables, but the actual data rows are stored externally in a flat file and are accessed only when needed.

Database Schema Objects

  • An Oracle Database can contain many different types of objects.
  • This section introduces the most commonly used objects, and also describes how the Oracle Server uses the information stored in the Data Dictionary when it is performing work as a result of the SQL statements you issue.
  • The main database object types are:
    • Table
    • Index
    • Constraint
    • View
    • Sequence
    • Synonym
  • Some of these object types can exist independently and others can not.
  • Some of the object types take up space, known as storage, in the database and others do not.
  • Database objects taking up significant storage space are known as Segments.
  • Tables and Indexes are examples of Segments, as the values stored in the columns of each row take up significant physical disk space.
  • Views, Constraints, Sequences, and Synonyms are also objects, but the only space they require in the database is in the definition of the object—none of them have any data rows associated with them.
  • The database stores the definitions of all database objects in the Data Dictionary, and these definitions are accessible to all users of the database as well as to the database itself.
  • Have you ever wondered how Oracle knows which columns to return from a Query?
  • For example, if you specify SELECT * FROM jobs instead of SELECT job_id, job_title FROM jobs, how does Oracle know which columns to return?
  • The database looks up the definition of the table used in the query, translates the ‘*’ into the full list of columns, and returns the result to you.
  • The database uses the Data Dictionary for all statements you issue, even if you list the column names instead of using ”.
  • It checks that the tables you are referencing in your statement exist in the database, it checks that the column names are correct, it checks if you have the correct privileges to perform the action you are requesting, and finally it uses the Data Dictionary to decide the Execution Plan – how it will actually perform the request.
  • The Data Dictionary itself can be queried by all database users.
  • In Application Express, it can be accessed both via SQL statements in the SQL Workshop> SQL Commands interface and also from the SQL Workshop> Object Browser interface.
  • In the SQL Commands window, you have to know the names of the table you are querying, and in the Object Browser interface, you simply click on the listed objects to see their details.
  • If you want to see the details of a table, you simply click on its title in the table listing.
  • Using the Object Browser, you can see the details of the tables as well as the options to view the data, indexes, constraints, grants, and other details of the table.
  • Using the SQL Commands window, you must ask for a DESCription of the table.
  • All the extra options offered by Object Browser are not available in this interface.
DESCRIBE jobs;
Database Schema Objects

You can use DESCRIBE or its abbreviated form DESC to view table descriptions.

Table Creation

  • All data in a relational database is stored in tables.
  • When creating a new table, use the following rules for 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, _ (underscore), $, and #
    • Must not duplicate the name of another object owned by the same user
    • Must not be an Oracle Server reserved word

Naming Conventions

  • It is best to use descriptive names for tables and other database objects.
  • If a table will store information about students, name it STUDENTS, not PEOPLE or CHILDREN.
  • Table names are not case sensitive.
  • For example, STUDENTS is treated the same as STuDents or students.
  • Table names should be plural, for example STUDENTS, not student.
  • Creating tables is part of SQL’s data definition language (DDL).
  • Other DDL statements used to set up, change, and remove data structures from tables include ALTER, DROP, RENAME, and TRUNCATE.

CREATE TABLE

  • To create a new table, you must have the CREATE TABLE privilege and a storage area for it.
  • The database administrator uses data control language (DCL) statements to grant this privilege to users and assign a storage area.
  • Tables belonging to other users are not in your schema.
  • If you want to use a table that is not in your schema, use the table owner’s name as a prefix to the table name:
    • You must be granted access to a table to be able to select from it.
SELECT *
FROM mary.students;

CREATE TABLE Syntax

  • To create a new table, use the following syntax details:
    • table is the name of the table
    • column is the name of the column
    • Data type is the column’s data type and length
    • DEFAULT expression specifies a default value if a value is omitted in the INSERT statement
CREATE TABLE table
(column data type [DEFAULT expression],
(column data type [DEFAULT expression],
(……[ ] );

Column definitions are separated by commas.

Data Types are covered in more detail in the next lesson.

CREATE TABLE Example

  • The examples below show the CREATE TABLE statement:
CREATE TABLE my_cd_collection
(cd_number NUMBER(3),
title VARCHAR2(20),
artist VARCHAR2(20),
purchase_date DATE DEFAULT SYSDATE);
CREATE TABLE my_friends
(first_name VARCHAR2(20),
last_name VARCHAR2(30),
email VARCHAR2(30),
phone_num VARCHAR2(12),
birth_date DATE);

External Tables

  • Oracle also supports another table type: External table.
  • In an external table, the data rows are not held inside the database files but are instead found in a flat file, stored externally to the database.
  • Typically an external table is used to store data migrated from older versions of the databases used by a company.
Oracle External Tables
  • When a company is implementing a new application and database, they typically need to import most of the data from the old systems to the new system for normal read and write access, but there may be some data that is not used frequently and therefore would only need to be accessed for read access.
  • This kind of data could be held in an external table.
Oracle database external tables Backed up once at operating system level
  • One of the many benefits for Oracle is that data held in external tables only has to be backed up once, and then never again unless the contents of the file change.
  • The syntax to create an external table is very similar to that of creating a standard table, except that it has extra syntax at the end.

Companies often buy referenced data from outside sources, for example marketing information on potential customers, or address files with zip (post) codes. Rather than load all this data into the database, companies often leave the data in flat files external to the database, and access them as an External Table.

  • The new syntax (shown in red) below, is not used in standard SQL statements for table creation.
  • ORGANIZATION EXTERNAL — tells Oracle to create an external table
  • TYPE ORACLE_LOADER — of type Oracle Loader (an Oracle Product)
  • DEFAULT DIRECTORY def_dir1 — the name of the directory for the file
  • ACCESS PARAMETERS — how to read the file
  • RECORDS DELIMITED BY NEWLINE — how to identify the start of a new row
  • FIELDS – the field name and data type specifications
  • LOCATION – name of the actual file containing the data
  • An example of the new syntax is found in red below in this page.

External Tables Example

External Tables Example

This query will not run in APEX because there is no connection to an external table.

Data Dictionary

  • Two kinds of tables exists in an Oracle Database: User tables and Data Dictionary tables.
  • You can issue SQL statements to access both kinds of tables—you can select, insert, update, and delete data in the user tables, and you can select data in the Data Dictionary tables.
Data Dictionary in ORacle Database
  • User tables created by you containing your data:
    • employees, departments, jobs, etc.
  • Data Dictionary tables:
    • DICTIONARY, USER_OBJECTS, USER_TABLES, USER_SEGMENTS, USER_INDEXES, etc.
  • The Data Dictionary tables are all owned by a special Oracle user called SYS and only SELECT statements should be used when working with any of these tables.
  • To make these tables safe from accidental user access, they all have views created through which the Data Dictionary is accessed by database users.

Views will be described in more detail later in the course.

  • If any Oracle user attempts to do inserts, updates, or deletes against any of the Data Dictionary tables, the operation is disallowed as it might compromise the integrity of the entire database.
  • When you are using the Data Dictionary views in the SQL Commands interface, you need to know the names of the Dictionary views you are working with.
  • In Oracle, this is quite simple: prefix the object type you are looking for with a USER_xxx or an ALL_xxx, where xxx is the object type.
SELECT table_name, status
FROM USER_TABLES;
SELECT table_name, status
FROM ALL_TABLES;

So if you want to investigate indexes, then simply select from USER_INDEXES; if you want information about
sequences, then the table is USER_SEQUENCES and so on.

SELECT *
FROM user_indexes;
SELECT *
FROM user_objects
WHERE object_type = 'SEQUENCE';

If you are uncertain of the Data Dictionary view that you need, you can SELECT * FROM DICTIONARY, and this will return the name of the views contained in the dictionary with a description of it’s contents.

Terminology

Key terms used in this lesson included:

  • CREATE TABLE
  • Data dictionary
  • Table
  • External table
  • Schema
  • DEFAULT

Summary

In this lesson, you should have learned how to:

  • Categorize the main database objects
  • Review a table structure
  • Describe how schema objects are used
  • Create a table using the appropriate data type for each column
  • Explain the use of external tables
  • Use the Data Dictionary to obtain the names and other attributes of database objects

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *