Interview Questions

Creating and Maintaining Database Objects

Overview

As both a DBA and a developer, you will be responsible for creating and maintaining a variety of database objects. First and foremost, you will be creating tables. You will also need to know how to create indexes and views.

To keep track of tables, indexes, and other database objects, you can use data dictionary views, which allow you to retrieve various kinds of statistics about tables and other database objects.

Two other useful database objects covered here are sequences and synonyms. Sequences make it easy to generate a series of unique numbers that are typically used for the primary key of a table. Synonyms facilitate a consistent naming convention for database objects that may exist in the user's schema or in another schema of the same database.

Creating Tables

The table is the most basic and most important object you will create in a database. Essentially, you could do without every other database object in a database except for tables. Without tables, you cannot store anything in a database.

You can create tables with the CREATE TABLE statement or "on the fly" with a method known as Create Table As Select, or CTAS.

Once you know that you need to create a table, you must decide what kind of table you want. In this section, we'll cover the most common types of tables:

  • Relational tables
  • Table created directly from the result of a quary
  • Tables whose data resides outside the database.
  • Tables with a definition that is available to all sessions but whose data is local to the session that created the data

Relational Tables

A relational table is the most common form of a table in the Oracle database. It is created with the CREATE TABLE statement, its data is stored in the database, and it can be partitioned. When you partition a table, the data for the table is internally stored in two or more pieces to potentially improve performance and to make the table easier for the DBA to manage if the table has many rows.

Relational Table The most cannon form of a table in the oracle dafault type areated with the CREATE TABLE statement. A relational table is pemarment and can be partitioned.

The basic syntax for the CREATE TABLE statement is as follows:

CREATE TABLE [schema.]tablename
(column1 datatype1  [DEFAULT expression]
[, ...];

The table that Scott, the company founder, created back in Chapter " SQL*Plus and iSQL*Plus Basics," was built with this statement:

create table emp_hourly (
empno  number(4)    not null,
ename  varchar2(10),
job    varchar2(9),
mgr    number(4),
hiredate   date,
hourrate   number(5,2) not null default 6.50,
deptno     number(2),
constraint pk_emp
primary key ( empno ) ) ;

Now, the HR schema is used to manage employee information. Therefore, Janice, the DBA and senior developer, must re-create the table to match the datatypes and name of the EMPLOYEES table in the HR schema, as follows:

create table employees_hourly (
employee_id     number(6)     not null,
first_name      varchar2(20),
last_name       varchar2(25)  not null,
email           varchar2(25)  not null,
phone_number    varchar2(20),
job_id          varchar2(10)  not null,
manager_id      number(6),
hire_date date  not null,
hourly_rate     number(5,2) default 6.50 not null,
department_id   number(4),
ssn             varchar2(11),
constraint pk_employees_hourly
primary key( employee_id ) ) ;

Because of the PRIMARY KEY constraint on the EMPLOYEE_ID column, the values in the EMPLOYEE_ID column must be unique within the table.

Create Table As Select (CTAS)

If you want to base the contents of a new table on the results of a query of one or more other tables, you can use the statement CREATE TABLE ... AS SELECT, otherwise known as CTAS. It's shorthand for two or more individual statements: the traditional CREATE TABLE statement and one or more INSERT statements. Using CTAS, you can create a table and populate it in one easy step.

CTAS   Also known as Create Table As Select, a method for creating a table in the database by using the results from a subquery to both populate the data and specify the datatypes of the columns in the new table.

The syntax for CTAS varies from the basic syntax of a CREATE TABLE statement as follows:

CREATE TABLE [schema.]tablename
AS SELECT <select_clauses>;

Notice that with CTAS you cannot specify the datatypes of the new columns; the column datatypes of the original columns, along with any NOT NULL constraints, are derived from the columns in the SELECT query. Any other constraints or indexes may be added to the table later. Column aliases in the SELECT query are used as the column names in the new table.

At Scott's widget company, the Order Entry department frequently sends out mailings to non-administrative staff, but the mailing list is becoming outdated. The manager in the Order Entry department asks Janice to grant the developers in the group the rights to access the EMPLOYEES table. However, the EMPLOYEES table contains sensitive personal information about employees, such as their salary. So, instead of granting access to the EMPLOYEES table, Janice decides to give the Order Entry department developers their own table with a limited number of columns. Using CTAS, her CREATE TABLE statement extracts the name and e-mail address for the Order Entry department as follows:

create table oe.non_admin_employees
as select employee_id, last_name, first_name, email
from hr.employees e where e.job_id not like 'AD_%';

Notice that Janice is copying some of the rows with only a few of the columns from the EMPLOYEES table in the HR schema, and she is creating a new table named NON_ADMIN_EMPLOYEES in the OE schema. To confirm her work, Janice checks the new table:

describe oe.non_admin_employees


select * from oe.non_admin_employees


103 rows selected.

Everyone in the EMPLOYEES table is in the new NON_ADMIN_EMPLOYEES table, except for the four administrative employees whose job ID begins with AD_.

Janice makes sure to re-create the table in the OE schema every time employees are added, deleted, or changed in HR's EMPLOYEE table. If the Order Entry department wants any other constraints or indexes other than the NOT NULL constraint on columns in the new table, Janice will need to create them manually.

External Tables

Sometimes you want to access data that resides outside the database, but you want to use it as if it were another table within the database. An external table is a read-only table whose definition is stored within the database but whose data stays external to the database itself.

External Table  A table whose definition is stored in the database but whose data is stored externally to the database.


You may ask, "Why not use one of Oracle's utilities to load the external data into an internal table, and then use the internal table?" While this is an option, there are many reasons why this may not be the best solution. One reason is that you can use the functionality of Oracle SQL against the external table to more easily load the data into other tables. Also, if the external data source is maintained by another business area in a text format, the database's copy of the data most likely will be out of synch until the next time you import it. If you treat the external data as a table, it will always be up to date every time you access it as an external table.

There are a few drawbacks to using external tables. External tables are read-only; changes cannot be made to the external data source with UPDATE statements. Also, external tables cannot be indexed. Therefore, if you need to access only a small fraction of the rows in the external table, an internal table with an index might be a better solution.

Janice, the DBA, has been assigned the task of making the customer feedback files maintained by the Customer Service group accessible from within the database. Currently, the Customer Service group receives customer feedback, which is entered on a daily basis into a text file on the shared network drive I:CommonCustomerComments with a filename of feedback.txt.

Directory  A database object that stores a reference to a directory on the host operating system's filesystem.

The first step Janice must perform is to define an Oracle object known as a directory. An Oracle directory is an Oracle object that contains an alias to a directory path on the operating system's filesystem. Once defined in this manner, the Oracle directory object can be used to refer to the location on the filesystem in subsequent Oracle commands, such as the CREATE TABLE ... ORGANIZATION EXTERNAL command. You need to run the CREATE DIRECTORY command only once for each filesystem pathname you want to access. Janice's command for creating this directory object is as follows:

create directory comment_dir as
'I:CommonCustomerComments';
Directory created.

The file that contains the data for the external table, feedback.txt, looks like this:

154,Helpful and Friendly.
150,Took the time to help me buy the widgets I really needed.
156,Didn't really seem too enthusiastic.
152,The Best experience I've had with Widgets-R-Us.

The external table will have two columns: The first field is the employee number, and the second field is the text of the comments from the customer. A comma separates the employee number from the comment. Janice uses the following CREATE TABLE statement to create the external table:

create table cust_comments (
employee_id   number,
 comments      varchar2(100)) 
 organization external  
(default directory comment_dir  
access parameters  
(records delimited by newline 
 fields terminated by ','  (employee_id char, comments char)) 
 location('feedback.txt'));
Table created.

The first part of the CREATE TABLE statement looks familiar. It contains two columns: EMPLOYEE_ID and COMMENTS. The ORGANIZATION EXTERNAL clause specifies this table to be an external table. The operating system file is located in the directory defined by the directory object comment_dir. Each line of data corresponds to one row in the table, and each column in the external file is separated by a comma. Both of the fields are character strings in the external file, so we define those fields as CHAR. Finally, we specify the name of the external file itself with the LOCATION clause.

Janice, as well as anyone else who can access tables in the HR schema, can use the CUST_COMMENTS table in a query as easily as using any of the internal tables:

select * from cust_comments;


4 rows selected.

To prodeuce a report that is more readable for the boss, Janice joins the external table with the internal EMPLOYEES table:

select employee_id "EmpID",
last_name || ', ' || first_name "Name", comments
from employees join cust_comments using (employee_id);


The CUST_COMMENTS table is indistinguishable in usage from any other table in the database, as long as you don't try to perform any INSERT, UPDATE, or DELETE statements on the external table.

Temporary Tables

A temporary table is a table whose definition is available to all sessions in the database but whose rows are available only to the session that added the rows to the table. Once the transaction is committed or the session is terminated, the data created during that session is removed from the temporary table. To create a temporary table, you use the familiar CREATE TABLE syntax with the addition of the GLOBAL TEMPORARY clause. An additional clause, ON COMMIT PRESERVE ROWS, retains the rows added to the table until the end of the session; otherwise, the rows are removed after each COMMIT.

Temporary Table  A table whose definition is persistent and shared by all database users but whose data is local to the session that created the data. When the transaction or session is completed, the data is truncated from the temporary table.

A temporary table might be useful in an application that uses a table for its session data and is used by hundreds of users; the table needs to be created only once, with the proper permissions so that all application users can access it.

Janice, the DBA, is installing a travel itinerary application that employees use to plan their business trips. The application needs a table that temporarily holds the travel destinations and costs for the employee. Janice realizes a temporary table is perfect for this purpose. Her CREATE TABLE statement looks like this:

create global temporary table travel_dest
(employee_id      number(6),
destination_id   number(4),
airfare          number(7,2),
hotel            number(6,2))
on commit preserve rows;
 Table created.

Once the travel itinerary application is terminated and the user disconnects from the database, any rows placed in this table by the user are automatically removed.


Pragna Meter
Next Chapter  
e-University Search
Related Jobs