Creating and Maintaining Database Objects
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
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,
hourrate number(5,2) not null default 6.50,
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,
last_name varchar2(25) not null,
email varchar2(25) not null,
job_id varchar2(10) not null,
hire_date date not null,
hourly_rate number(5,2) default 6.50 not null,
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:
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.
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
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 (
(default directory comment_dir
(records delimited by newline
fields terminated by ',' (employee_id char, comments char))
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.
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
on commit preserve rows;
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.
The purpose of indexes can be summarized in one word: performance. An index is a database structure designed to reduce the amount of time necessary to retrieve one or more rows from a table. Indexes can also enforce uniqueness on one or more columns of a table.
Index A database object designed to reduce the amount of time it takes to retrieve rows from a table. An index is created based on one or more columns in the table.
Any number of indexes may be created on a table. An index may also be built against a combination of columns in a table; this type of index is known as a composite index.
Indexes are maintained automatically. When new rows are added to the table, new entries are recorded in the indexes. When rows are deleted from the table, the corresponding index entries are also deleted.
Composite Index An index that is created on two or more columns in a table.
Warning Be cautious when creating indexes in an environment with frequent update, insert, and delete operations. The overhead of keeping the indexes up-to-date can have a performance impact on the database and potentially increase the response time for users.
Indexes can be either unique or nonunique. A unique index prevents duplicate values from being inserted into a table column with a unique index. For example, an employee table might have a column with a social security number. Since no two employees will have the same social security number, a unique index can be created on the column. If a primary key is defined for a table, a unique index is automatically created to enforce the uniqueness of the primary key.
Nonunique indexes, by definition, will not enforce uniqueness but can still speed processing by narrowing down the range of blocks where the desired rows of a table can be found. For example, a nonunique index on a column with a last name would likely have many entries for Smith. Each of the index entries for Smith would point to a row in the table where the last name was Smith. Using this nonunique index to find all the Smith entries will typically take much less time than scanning the entire table for Smith directly.
An index on a database table column corresponds closely to the real-world analogy of an index in a book. A topic in a book can be located much more quickly if the topic's title is located in the book's index with the corresponding page number. Without the index, you might need to search through each page of the book to locate the topic you want.
The simplest form of the CREATE INDEX statement looks like this:
CREATE INDEX index_name
ON table_name (column1[, column2]...);
The columns column1, column2, and so forth are the columns to be indexed on the table table_name. The index name index_name must be unique across all objects within the same schema.
Janice has been receiving complaints that the queries against the COUNTRIES table have been slow. She knows that there is already an index on the COUNTRY_ID column, so she is surprised that the response time would be poor when selecting a row from the COUNTRIES table. After further investigation, she discovers that a lot of users are trying to find the two-letter country code given the name of the country—the users are searching the table using a WHERE clause on the COUNTRY_NAME column. She decides that an index on the COUNTRY_NAME column might improve the response time. To create the index, she uses the following command:
create index countries_ie1 on countries(country_name);
The index did not necessarily need the name of the table in its name. However, Janice realizes that it's good practice to include the table name so that she can easily avoid duplicate index names in the database.
Creating and Using Views
In this section, we'll talk about views that users can create themselves, and then we'll cover views owned by SYS that contain important information about the objects in the database.
Views are database objects that look like tables but are instead derived from a SELECT statement performed on one or more tables. In other words, a view is a subset of data from one or more tables. A view does not contain its own data; the contents of a view are dynamically retrieved from the tables on which it is based. A view is sometimes referred to as a stored query.
View A database object that is based on a SELECT statement against one or more tables or other views in the database. A regular view does not store any data in the database; only the definition is stored. Views are also known as stored queries.
Views can enhance the usability of the database by making complex queries appear to be simple. For example, users may frequently join together two or more tables in the same way. A view will make the users' lives a bit easier, allowing them to write a query against a single view instead of needing to rewrite a complex query over and over.
Views can also be used to restrict access to certain rows or columns of a table. For example, the DBA can create a view against the EMPLOYEES table that excludes the SALARY column and can make this view available to those departments that need to see employee information but should not see salary information.
The CREATE VIEW statement looks like this:
CREATE VIEW view_name (alias1[, alias2] ...)
The subquery clause is a SELECT statement that may join more than one table and may also have a WHERE clause. Column aliases can be specified for the resulting columns from the subquery.
After reviewing some of the SELECT statements that the users are writing, Janice, the DBA and application developer, notices that there are frequent joins between the EMPLOYEES table and the DEPARTMENTS table, similar to the following:
select employee_id, last_name, first_name,
from employees join departments using(department_id);
Creating a view based on this query might help the users who typically don't use SQL to join tables but need to see the associated department information for each employee. Janice creates the view using the sample query above as the sub-query in a CREATE VIEW statement:
emp_dept(emp_id, lname, fname, dept_id, dname) as
select employee_id, last_name, first_name,
from employees join departments using(department_id);
Notice that Janice has supplied column aliases so that the original column names are not visible to the users of the view. For all intents and purposes, the EMP_DEPT view looks and operates in the same way as a single table, as demonstrated below with the DESCRIBE and SELECTstatements:
select * from emp_dept;
106 rows selected.
The EMP_DEPT view can be used in the same way as any database table. The users can add a WHERE clause to the SELECT statement above. Also, the EMP_DEPT view can be joined with a table in another query if so desired.
Data Dictionary Views
Data dictionary views are predefined views that contain a variety of information about tables, users, and various other objects in the database. Like other views, data dictionary views are based on one or more tables. The main differences between data dictionary views and user-created views are that data dictionary views are owned by the user SYS and the views themselves may appear to have different results depending on who is accessing them.
Data Dictionary Views Read-only views owned by the user SYS that are created when the database is created and contain information about users, security, and database structures, as well as other persistent information about the database.
Data Dictionary View Types
Data dictionary views have one of three prefixes:
USER_ These views show information about the structures owned by the user (in the user (in the user's schema). They are accessible to all users and do not have an OWNER column.
ALL_ These views show information about all objects that the user has access to, including objects owned by the user and objects to which other users have granted the user access. These views are accessible to all users. Each view has an OWNER column, since some of the objects may reside in other user's schemas.
DBA_ These views have information about all structures in the database—they show what is in all users' schemas. Accessible to the DBA, they provide information on all the objects in the database and have an OWNER column as well.
Common Data Dictionary Views
Some data dictionary views are commonly used by both developers and DBAs to retrieve information about tables, table columns, indexes, and other objects in the database. The following descriptions refer to the ALL_ version of each of the views.
The ALL_TABLES view contains information about all database tables to which the user has access. The following query, run by the user HR, identifies the table and owner of all tables that HR can access:
select table_name, owner from all_tables;
44 rows selected.
Many of the tables visible to HR are tables owned by SYS and SYSTEM, such as the DUAL table. The user HR can also access the EMP and SALGRADE tables owned by SCOTT.
The ALL_TAB_COLUMNS view contains information about the columns in all tables accessible to the user. If the user HR wanted to find out the columns and datatypes in the COUNTRIES table, the query would be written as follows:
select column_name, data_type from all_tab_columns
where table_name = 'COUNTRIES';
3 rows selected.
The ALL_INDEXES view contains information about the indexes accessible to the user. If the HR user wanted to find out the indexes that were created against the COUNTRIES table and whether the indexes were unique, the query would look like this:
select table_name, index_name, uniqueness from all_indexes
where table_name = 'COUNTRIES';
2 rows selected.
The COUNTRIES table has two indexes, one of which is a unique index.
The ALL_IND_COLUMNS view contains information about the columns indexed by an index on a table. Following the previous example, the HR user can use the INDEX_NAME to help identify the indexed column or columns on the table:
select table_name, column_name from all_ind_columns
where index_name = 'COUNTRY_C_ID_PK';
1 row selected.
The index COUNTRY_C_ID_PK indexes the COUNTRY_ID column in the COUNTRIES table.
The ALL_OBJECTS view combines all types of Oracle structures into one view. This view comes in handy when you want a summary of all database objects using one query, or you have the name of the object and want to find out what kind of object it is. The following query retrieves all the objects accessible to HR and owned by either the HR or JANICE schema:
select owner, object_name, object_type, temporary
where owner in ('HR','JANICE');
43 rows selected.
The TEMPORARY (T) column in the ALL_OBJECTS view indicates whether the object is temporary. The temporary table TRAVEL_DEST, created and owned by JANICE but accessible to all users, is indicated correctly as being a temporary table in the query results.
Dynamic performance views
Dynamic performance views are similar in nature to data dictionary views, with one important difference: Dynamic performance views are continuously updated while the database is open and in use; they are re-created when the database is shut down and restarted. In other words, the contents of these views are not retained when the database is restarted. The contents of dynamic performance views primarily relate to the performance of the database.
The names of the dynamic performance views begin with V$. Two common dynamic performance views are V$SESSION and V$INSTANCE.
Dynamic Performance Views Data dictionary views owned by the user SYS that are continuously updated while a database is open and in use and whose contents relate primarily to performance. These views have the prefix V$ and their contents are lost when the database is shut down.
The dynamic performance view V$SESSION contains information about each connected user or process in the database. To find out what programs the user HR is using to connect to the database, you can query the PROGRAM column of V$SESSION:
select sid, serial#, username, program from v$session
where username = 'HR';
4 rows selected.
In this case, the user HR has four connections open in the database using three different programs. The SID and SERIAL# columns together uniquely identify a session. This information is needed by the DBA if, for some reason, one of the sessions must be terminated.
The V$INSTANCE view provides one row of statistics for each Oracle instance running against the database. Multiple instances running against a single database can greatly enhance the scalability of the Oracle database by spreading out the CPU resource usage over multiple servers. The following query finds out the version of the Oracle software and how long the instance has been up since the last restart, along with other instance information:
select instance_name, host_name, version,
startup_time, round(sysdate-startup_time) "Days Up",
status from v$instance;
1 row selected.
Creating Sequences and Synonyms
Various other database objects are needed to support the main objects in the database (such as tables). Two such objects are sequences and synonyms.
An Oracle sequence is a named sequential number generator. A sequence is often used to generate a unique key for the primary key of a table. A sequence object is owned by a single schema, but it can be used by other database users if the proper permissions are granted to the users.
Sequence A database structure that generates a series of numbers typically used to assign primary key values to database tables.
Sequences can begin and end with any value, can be ascending or descending, and can skip (increment) a specified number between each value in the sequence. The basic syntax for CREATE SEQUENCE is as follows:
CREATE SEQUENCE sequence_name
[START WITH starting_value]
[INCREMENT BY increment_value];
If all optional parameters are omitted, the sequence starts with one and increases by increments of one, with no upper boundary.
Sequences are referenced in DML statements by using the syntax sequence_ name.currval or sequence_name.nextval. The qualifier nextval retrieves the next value. The qualifier currval retrieves the most recent number generated without incrementing the counter. For example, here are some sample SELECT statements that access the sequence used for employee numbers, EMPLOYEES_SEQ:
select employees_seq.nextval from dual;
1 row selected.
select employees_seq.nextval from dual;
1 row selected.
select employees_seq.currval from dual;
1 row selected.
The HR department has asked the DBA, Janice, to re-create the sequence for the EMPLOYEES table to start at 501 and increment by 10. Janice drops the old sequence and re-creates it:
drop sequence hr.employees_seq; Sequence dropped.
create sequence hr.employees_seq
start with 501
increment by 10;
After the sequence has been created, the user HR inserts a record into the EMPLOYEES table as follows:
insert into employees
(employee_id, last_name, first_name, email,
(employees_seq.nextval, 'JUDD', 'DAWN', 'DRJUDD',
1 row created.
select employee_id from employees
where last_name = 'JUDD';
1 row selected.
The next time the employees_seq sequence is used, the value returned will be 511.
A synonym is an alias for another database object, such as a table, sequence, or view. Synonyms provide easier access to database objects outside the user's schema.
There are two kinds of synonyms: public and private. Public synonyms are available to all database users. A private synonym is available only in the session of the schema owner who created it.
Synonym An alias assigned to a table, view, or other database structure. Synonyms can be either available to all users (public) or available only to one schema owner (private).
Synonyms are useful in providing a common name to a database object, regardless of which username is logged in to the database. The temporary table created by Janice the DBA, called TRAVEL_DEST, must be qualified with the schema name if anyone other than Janice wants to access it. For example, if the user HR is connected to the database and no synonym has been specified, the table must be fully qualified:
insert into janice.travel_dest
values(101, 1201, 320.50, 988.00);
The syntax for creating a synonym is as follows:
CREATE [PUBLIC] SYNONYM synonym_name
To facilitate easy access to the table TRAVEL_DEST, Janice creates a public synonym for the table:
create public synonym travel_dest for travel_dest;
What happens if a user has a private synonym called TRAVEL_DEST, or worse yet, his or her own table is called TRAVEL_DEST? Unqualified object references (object references that aren't prefixed with a schema name) are resolved in the following order:
- A real object with the specified name
- A private synonym owned by the current user
- A public synonym
Private synonyms can be useful in a development environment when you have a copy of a table with a different name. A private synonym can be created to refer to the copy of the production table with the same name as the production table. During testing, the developer's private synonym points to the copy and does not impact the production table. When development is complete, the developer can remove the private synonym and move the new SQL code into a production environment, without changing any table names in the SQL code.