Oracle DBA FAQ - Managing Oracle Database Tables

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

A collection of 18 FAQs on database tables for DBA and developers. Clear answers are provided together with tutorial exercises to help beginners on creating, altering and removing tables, adding, altering and removing columns, and working with recycle bin. Topics included in this FAQ are:

What Is a Database Table?
How Many Types of Tables Supported by Oracle?
How To Create a New Table in Your Schema?
How To Create a New Table by Selecting Rows from Another Table?
How To Rename an Existing Table?
How To Drop an Existing Table?
How To Add a New Column to an Existing Table?
How To Add a New Column to an Existing Table with a Default Value?
How To Rename a Column in an Existing Table?
How To Delete a Column in an Existing Table?
How To View All Columns in an Existing Table?
How To Recover a Dropped Table?
What Is a Recycle Bin?
How To Turn On or Off Recycle Bin for the Instance?
How To View the Dropped Tables in Your Recycle Bin?
How To Empty Your Recycle Bin?
How To Turn On or Off Recycle Bin for the Session?
How To List All Tables in Your Schema?

Sample scripts used in this FAQ assumes that you are connected to the server with the default user account HR on the default instance XE. See other FAQ collections on how to connect to the server.

It is also assumed that user account HR has the CREATE TABLE privilege needed to manage tables.

What Is a Database Table?

A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

How Many Types of Tables Supported by Oracle?

Oracle supports 4 types of tables based on how data is organized in storage:

Ordinary (heap-organized) table - This is the basic, general purpose type of table. Its data is stored as an unordered collection (heap)
Clustered table - A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
Index-organized table - Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
Partitioned table - Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

How To Create a New Table in Your Schema?

If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:

>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
  2    subject VARCHAR(80) NOT NULL,
  3    description VARCHAR(256) NOT NULL,
  4    create_date DATE DEFAULT (sysdate));

Table created.

This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".

How To Create a New Table by Selecting Rows from Another Table?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:

>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> SELECT first_name, last_name, salary FROM emp_dept_10;
FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Jennifer             Whalen                          4400

As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.

This is really a quick and easy way to create a table.

(Continued on next part...)

Part: 1 2 3 4 5

More...