Sunday, June 20, 2010

SQL Data Definition Language

In this section, the basic SQL Data Definition Language statements are introduced and their syntax is given with examples.

An Oracle database can contain one or more schemas. A schema is a collection of database objects that can include: tables, views, indexes and sequences. By default, each user has their own the schema which has the same name as the Oracle username. For example, a single Oracle database can have separate schemas for HOLOWCZAK, JONES, SMITH and GREEN.

Any object in the database must be created in only one schema. The object name is prefixed by the schema name as in: schema.object_name
By default, all objects are created in the user's own schema. For example, when JONES creates a database object such as a table, it is created in her own schema. If JONES creates an EMPLOYEE table, the full name of the table becomes: JONES.EMPLOYEE. Thus database objects with the same name can be created in more than one schema. This feature allows each user to have their own EMPLOYEE table, for example.

Database objects can be shared among several users by specifying the schema name. In order to work with a database object from another schema, a user must be granted authorization. See the section below on GRANT and REVOKE for more details.

Please note that many of these database objects and options are not available under Personal Oracle Lite. For example, foreign key constraints are not supported. Please see the on-line documentation for Personal Oracle Lite for more details.

Create, Modify and Drop Tables, Views and Sequences

SQL*Plus accepts SQL statements that allow a user to create, alter and drop table, view and sequence definitions. These statements are all standard ANSI SQL statements with the exception of CREATE SEQUENCE.

ALTER TABLE - Change an existing table definition. The table indicated in the ALTER statement must already exist. This statement can be used to add a new column or remove an existing column in a table, modify the data type for an existing column, or add or remove a constraint. ALTER TABLE has the following syntax for adding a new column to an existing table:
Another ALTER TABLE option can change a data type of column. The syntax is:

Finally, ALTER TABLE can also be used to add a constraint to a table such as for a PRIMARY KEY, FOREIGN KEY or CHECK CONSTRAINT. The syntax to add a PRIMARY KEY is: The syntax to add a FOREIGN KEY constraint is: In Oracle, you must use an ALTER TABLE statement to define a composite PRIMARY KEY (a key made up of two or more columns).
NOTE: In Oracle, there is no single command to drop a column of a table. In order to drop a column from a table, you must create a temporary table containing all of the columns and records that will be retained. Then drop the original table and rename the temporary table to the original name. This is demonstrated below in the section on Creating, Altering and Dropping Tables.
CREATE TABLE - Create a new table in the database. The table name must not already exist. CREATE TABLE has the following syntax: An alternate syntax can be used to create a table with a subset of rows or columns from an existing table.
DROP TABLE - Drop a table from the database. The table name must already exist in the database. The syntax for the DROP TABLE statement is:
CREATE INDEX - Create a new Index that facilitates rapid lookup of data. An index is typically created on the primary and/or secondary keys of the table. The basic syntax for the CREATE INDEX statement is:
DROP INDEX - Drop an index from the database. The syntax for the DROP INDEX statement is:
CREATE SEQUENCE - Create a new Oracle Sequence of values. The new sequence name must not exist. CREATE SEQUENCE has the following syntax:
DROP SEQUENCE - Drop an Oracle Sequence. The sequence name must exist. DROP SEQUENCE has the following syntax:
CREATE VIEW - Create a new view based on existing tables in the database. The table names must already exist. The new view name must not exist. CREATE VIEW has the following syntax:
where sql select statement is in the form:

Additional information on the SELECT statement and SQL queries can be found in the next section.
Note that an ORDER BY clause may not be added to the sql select statement when defining a view.
In general, views are read-only. That is, one may query a view but it is normally the case that views can not be operated on with INSERT, UPDATE or DELETE. This is especially true in cases where views joing two or more tables together or when a view contains an aggregate function.
DROP VIEW - Drop a view from the database. The view name must already exist in the database. The syntax for the DROP VIEW command is:

In the following section, each of the SQL DDL commands will be discussed in more detail.

Creating, Altering and Dropping Tables

A table is made up of one or more columns (also called attributes in relational theory). Each column is given a name and a data type that reflects the kind of data it will store. Oracle supports four basic data types called CHAR, NUMBER, DATE and RAW. There are also a few additional variations on the RAW and CHAR data types. The basic datatypes, uses and syntax, are as follows:

VARCHAR2 - Character data type. Can contain letters, numbers and punctuation. The syntax for this data type is: VARCHAR2(size) where size is the maximum number of alphanumeric characters the column can hold. For example VARCHAR2(25) can hold up to 25 alphanumeric characters. In Oracle8, the maximum size of a VARCHAR2 column is 4,000 bytes. The VARCHAR data type is a synonym for VARCHAR2. It is recommended to use VARCHAR2 instead of VARCHAR.
NUMBER - Numeric data type. Can contain integer or floating point numbers only. The syntax for this data type is: NUMBER(precision, scale) where precision is the total size of the number including decimal point and scale is the number of places to the right of the decimal. For example, NUMBER(6,2) can hold a number between -999.99 and 999.99.
DATE - Date and Time data type. Can contain a date and time portion in the format: DD-MON-YY HH:MI:SS. No additional information is needed when specifying the DATE data type. If no time component is supplied when the date is inserted, the time of 00:00:00 is used as a default. The output format of the date and time can be modified to conform to local standards.
RAW - Free form binary data. Can contain binary data up to 255 characters. Data type LONG RAW can contain up to 2 gigabytes of binary data. RAW and LONG RAW data cannot be indexed and can not be displayed or queried in SQL*Plus. Only one RAW column is allowed per table.
LOB - Large Object data types. These include BLOB (Binary Large OBject) and CLOB (Character Large OBject). More than one LOB column can appear in a table. These data types are the prefferred method for storing large objects such as text documents (CLOB), images, or video (BLOB).

A column may be specified as NULL or NOT NULL meaning the column may or may not be left blank, respectively. This check is made just before a new row is inserted into the table. By default, a column is created as NULL if no option is given.

In addition to specifying NOT NULL constraints, tables can also be created with constraints that enforce referential integrity (relationships among data between tables). Constraints can be added to one or more columns, or to the entire table.

Each table may have one PRIMARY KEY that consists of a single column containing no NULL values and no repeated values. A PRIMARY KEY with multiple columns can be identified using the ALTER TABLE command.

Up to 255 columns may be specified per table. Column names and table names must start with a letter and may not contain spaces or other punctuation except for the underscore character. Column names and table names are case insensitive. This means that you can specify the names of columns and tables in any way you like. For example, the following three SELECT statements are all identical:

SELECT lname, fname, address FROM employee; SELECT LNAME, FNAME, ADDRESS FROM EMPLOYEE; SELECT Lname, Fname, Address FROM Employee;

In the following example, a new table called ``employee'' is created with ten columns of a variety of types. The columns indicated by NOT NULL will be mandatory while the other columns, by default, will be optional.

SQL&amp;amp;gt; CREATE TABLE employee 2 (fname VARCHAR2(8), 3 minit VARCHAR2(2), 4 lname VARCHAR2(8), 5 ssn VARCHAR2(9) NOT NULL, 6 bdate DATE, 7 address VARCHAR2(27), 8 sex VARCHAR2(1), 9 salary NUMBER(7) NOT NULL, 10 superssn VARCHAR2(9), 11 dno NUMBER(1) NOT NULL) ; Table created. SQL&amp;amp;gt;

The numbers 2 through 11 before each line indicate the line number supplied by the SQL*Plus program as this statement was typed in. We will omit these numbers in the rest of the examples to facilitate copying and pasting this material directly into a live SQL*Plus session.

A new table can also be created with a subset of the columns in an existing table. In the following example, a new table called emp_department_1 is created with only the fname, minit, lname and bdate columns from the employee table. This new table is also populated with data from the employee table where the employees are from department number 1.

SQL&amp;amp;gt; CREATE TABLE emp_department_1 AS SELECT fname, minit, lname, bdate FROM employee WHERE dno = 1 ; Table created. SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE SQL&amp;amp;gt;

One can also create a new table with all of the columns from the original table, but with only a subset of the rows form the original table:

SQL> CREATE TABLE high_pay_emp
     AS SELECT *
     FROM employee
     WHERE salary > 50000 ;

 Table created.

DESCRIBE is an SQL*Plus command that displays the columns of a table and their data types. The syntax for the DESCRIBE command is:

DESCRIBE &amp;amp;lt;table name&amp;amp;gt; ;

The copying of data can be suppressed by giving a WHERE clause that always evaluates to FALSE for each record in the source table. The following example makes a duplicate of the employee table but does not copy any data into it.

SQL&amp;amp;gt; CREATE TABLE copy_of_employee AS SELECT * FROM employee WHERE 3=5 ; Table created. SQL&amp;amp;gt; DESCRIBE copy_of_employee Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) SSN NOT NULL VARCHAR2(9) BDATE DATE ADDRESS VARCHAR2(27) SEX VARCHAR2(1) SALARY NOT NULL NUMBER(7) SUPERSSN VARCHAR2(9) DNO NOT NULL NUMBER(1)

Constraints can be added to the table at the time it is created, or at a later time using the ALTER TABLE statement. Constraints can include:

Primary key and Unique key constraints.
Foreign key constraints (for referential integrity).
Check constraints.

Here is an example of creating a primary key constraint on the empid column:

CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn) );

Referential integrity constraints can also be added. In the following example, the dno column in the employee table references the dnumber column in the department table. If a department is deleted, all employees that reference the department are also deleted. This is given by the ON DELETE CASCADE option:

CREATE TABLE department (dnumber NUMBER(1), dname VARCHAR2(15), mgrssn VARCHAR2(9), mgrstartdate DATE CONSTRAINT pk_department PRIMARY KEY (dnumber) ); CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn), CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department (dnumber) ON DELETE CASCADE);

In order to specify a foreign key constraint, the column in the child (or detail) table (e.g., the dnumber column in the department table in the above example) must be either the primary key or a unique key for the table. Thus, the child (or detail) table must be created first before the parent (or master) table is created using the above constraints.

Additional CREATE TABLE constraint statements allow the specification of what should happen when a row is deleted or updated in a parent table. In the above example, deleting a department causes all employees in that department to also be deleted. Other options include ON DELETE SET DEFAULT and ON DELETE SET NULL. In addition, the behavior of child tables when a parent table is updated can also be specified using an ON UPDATE clause.

CHECK constraints can be added to check the values for a given column. This can be used to allow only a specific set of valid values for a column. In the following example, CHECK constraints are added to limit the valid values for the sex column and to check if the salary is greater than 10,000 (be sure to DROP TABLE employee before you try the next one). CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(8), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1) CONSTRAINT ck_sex CHECK (sex IN ('M', 'F')), salary NUMBER(7) NOT NULL CONSTRAINT ck_salary CHECK (salary &amp;amp;gt; 10000), superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (ssn), CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department (dnumber) ON DELETE CASCADE);

The CHECK constraints are activated when inserting a new row or when updating existing data. In the following example, the value given for sex is 'm': SQL&amp;amp;gt; insert into employee values 2 ('Joe', 'M', 'Smith', '123456789', '01-JUN-45', 3 '123 Smith St.', 'm', 45000, '123456789', 1) ; insert into employee values * ERROR at line 1: ORA-02290: check constraint (HOLOWCZAK.CK_SEX) violated

In the previous examples, constraints were given names with the following prefixes:

Primary key constraints: pk_
Foreign key constraints: fk_
Check constraints: ck_

Naming constraints in this fashion is simply a convenience. Any name may be given to a constraint.

The ALTER TABLE command can be used to add a new column to an existing table or to change the data type of an existing column. The following examples add a new column manager to an existing table named emp_department_1 and then modify the data type of the fname column.

SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(8) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE SQL&amp;amp;gt; ALTER TABLE emp_department_1 ADD (manager VARCHAR2(8)) ; Table altered. SQL&amp;amp;gt; ALTER TABLE emp_department_1 MODIFY (fname VARCHAR2(15)); Table altered. SQL&amp;amp;gt; DESCRIBE emp_department_1 Name Null? Type ------------------------------- -------- ---- FNAME VARCHAR2(15) MINIT VARCHAR2(2) LNAME VARCHAR2(8) BDATE DATE MANAGER VARCHAR2(8)

The ALTER TABLE command can also be used to change the datatype of column provided there is no data in the table. To get around this if there is data in the table, create a temporary table using all of the data from the existing table, delete the existing records from the original table, alter the datatype, and then insert the records from the temporary table back into the original table. For example, assume the emp_department_1 table has some records in it and we want to change the datatype for the MANAGER column: CREATE TABLE temp AS SELECT * FROM emp_department_1; DELETE FROM emp_department_1; ALTER TABLE emp_department_1 MODIFY (manager VARCHAR2(15)); INSERT INTO emp_department_1 SELECT * FROM temp; DROP TABLE temp; This trick can also be used to drop a column from a table. Assume the Employee table has the following columns: fname, minit, lname, ssn, bdate, address, sex, salary, superssn and dno, and we want to drop the salary column from the table: CREATE TABLE temp AS SELECT fname, minit, lname, ssn, bdate, address, sex, superssn, dno FROM employee; DROP TABLE employee; CREATE TABLE employee AS SELECT * FROM temp;

The DROP TABLE command can be used to drop a table definition and all of its data from the database. In the following example, the table emp_department_1 created previously, is dropped from the database.

SQL&amp;amp;gt; DROP TABLE emp_department_1 ; Table dropped.

SQL Statements

The following is an alphabetical list of SQL statements that can be issued against an Oracle database. These commands are available to any user of the Oracle database. Emphasized items are most commonly used.

ALTER - Change an existing table, view or index definition
AUDIT - Track the changes made to a table
COMMENT - Add a comment to a table or column in a table
COMMIT - Make all recent changes permanent
CREATE - Create new database objects such as tables or views
DELETE - Delete rows from a database table
DROP - Drop a database object such as a table, view or index
GRANT - Allow another user to access database objects such as tables or views
INSERT - Insert new data into a database table
No AUDIT - Turn off the auditing function
REVOKE - Disallow a user access to database objects such as tables and views
ROLLBACK - Undo any recent changes to the database
SELECT - Retrieve data from a database table
UPDATE - Change the values of some data items in a database table

Some examples of SQL statements follow. For all examples in this tutorial, key words used by SQL and Oracle are given in all uppercase while user-specific information, such as table and column names, is given in lower case.

To create a new table to hold employee data, we use the CREATE TABLE statement:

CREATE TABLE employee
   (fname           VARCHAR2(8),
    minit           VARCHAR2(2),
    lname           VARCHAR2(8),
    ssn             VARCHAR2(9) NOT NULL,
    bdate           DATE,
    address         VARCHAR2(27),
    sex             VARCHAR2(1),
    salary          NUMBER(7) NOT NULL,
    superssn        VARCHAR2(9),
    dno             NUMBER(1) NOT NULL) ;

To insert new data into the employee table, we use the INSERT statement:

INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122',
       '24-JAN-54', '987 Western Way, Plano, TX', 
       'M', 42000, NULL, 5);

To retrieve a list of all employees with salary greater than 30000 from the employees table, the following SQL statement might be issued (Note that all SQL statements end with a semicolon):

SELECT fname, lname, salary FROM employee WHERE salary &amp;amp;gt; 30000;

To give each employee in department 5 a 4 percent raise, the following SQL statement might be issued:

UPDATE employee
          SET    salary = salary * 1.04
          WHERE  dno = 5;

To delete an employee record from the database, the following SQL statement might be issued:

DELETE FROM employee
          WHERE  empid = 101 ;

The SQL Language

Structured Query Language (SQL) is the language used to manipulate relational databases. SQL is tied very closely with the relational model. In the relational model, data is stored in structures called relations or tables. Each table has one or more attributes or columns that describe the table. In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc.
SQL statements are issued for the purpose of:

Data definition - Defining tables and structures in the database (DB).
Data manipulation - Inserting new data, Updating existing data, Deleting existing data, and Querying the Database ( Retrieving existing data from the database).

Another way to say this is the SQL language is actually made up of 1) the Data Definition Language (DDL) used to create, alter and drop scema objects such as tables and indexes, and 2) The Data Manipulation Language (DML) used to manipulate the data within those schema objects. The SQL language has been standardized by the ANSI X3H2 Database Standards Committee. Two of the latest standards are SQL-89 and SQL-92. Over the years, each vendor of relational databases has introduced new commands to extend their particular implementation of SQL. Oracle's implementation of the SQL language conforms to the basic SQL-92 standard and adds some additional commands and capabilities.

SQL*Plus Basics

Oracle's SQL*Plus is a command line tool that allows a user to type SQL statements to be executed directly against an Oracle database. SQL*Plus has the ability to format database output, save often used commands and can be invoked from other Oracle tools or from the operating system prompt.

In the following sections, the basic functionality of SQL*Plus will be demonstrated along with sample input and output to demonstrate some of the many features of this product.

1 Running SQL*Plus

In this section, we give some general directions on how to get into the SQL*Plus program and connect to an Oracle database. Specific instructions for your installation may vary depending on the version of SQL*Plus being used, whether or not SQL*Net or Net8 is in use, etc.

Before using the SQL*Plus tool or any other development tool or utility, the user must obtain an Oracle account for the DBMS. This account will include a username, a password and, optionally, a host string indicating the database to connect to. This information can typically be obtained from the database administrator.

The following directions apply to two commonly found installations: Windows 95/98 or NT client with an Oracle server, and a UNIX installation.

1.1 Running SQL*Plus under Windows 95/98 and Windows NT

To run the SQL*Plus command line program from Windows 95/98 or Windows NT, click on the [Start]

button, Programs, Oracle for Windows 95 and then SQL*Plus. The SQL*Plus login screen will appear after roughly 15 seconds.
[SQL*Plus Login Screen]

In the User Name: field, type in your Oracle username.
Press the TAB key to move to the next field.
In the Password: field, type your Oracle password.
Press the TAB key to move to the next field.
In the Host String: field, type in the Service Name of the Oracle host to connect to. If the DBMS is Personal Oracle lite then this string might be ODBC:POLITE. If the DBMS is Personal Oracle8, then the host string might be beq-local. For Client/Server installations with SQL*Net or Net8, this string will be the service name set up by the SQL*Net assistant software.

Finally, click on the OK button to complete the Oracle log in process. SQL*Plus will then establish a session with the Oracle DBMS and the SQL*Plus prompt (SQL> ) will appear. The following figure shows the results of logging into Oracle using SQL*Plus:

There are a number of situations in which an error may occur:

You might mistype your username, password and/or the Host String
SQL*Plus and SQL*Net may not be configured properly on your Windows client.
The network between your Windows client and the Oracle server may have a problem
The Oracle server may be temporarily shut down or otherwise unavailable

In any of the above cases, an error message will be returned. If the Oracle server is not available or if you supply the wrong username or password, an error will be returned right away. If there is a networking problem, SQL*Plus may take several minutes before returning an error.

Here are some common error messages and some suggestions on how to resolve them:

ERROR: ORA-12154: TNS:could not resolve service name: Either the Host string was mis-typed or SQL*Net is not configured properly. Exit SQL*Plus and try logging in again. If the error still occurs, try another PC.
ERROR: ORA-01017: invalid username/password; logon denied: Either the username or password was typed incorrectly. Exit SQL*Plus and try again.

Unfortunately, most versions of SQL*Plus will not re -display the login screen if your attempt to connect is unsuccessful. You should exit SQL*Plus completely by pulling down the File menu and choosing the Exit menu item. Then run SQL*Plus again from the beginning.

For users of Personal Oracle Lite, there is a default database schema created upon installation of the software. To log into Personal Oracle Lite using SQL*Plus, supply the following values on the SQL*Plus login screen:

In the User Name: field, type in OOT_SCH
In the Password: field, type in OOT_SCH
In the Host String: field, type in ODBC:POLITE.

1.2 Running SQL*Plus under UNIX

To run SQL*Plus under UNIX, log into your UNIX account and at the UNIX command prompt (shown as unix% below), type the sqlplus command followed by a carriage return. When prompted for a username, supply your Oracle username (This may be the same as or different from your UNIX account name). When prompted for a password, supply your Oracle account password (this should not be the same as your UNIX account password). unix% sqlplus SQL*Plus: Release 3.3.2.0.0 - Production on Sun Dec 21 13:32:53 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: holowczak Enter password: **************** Connected to: Oracle7 Server Release 7.3.2.3.0 - Production Release With the distributed, replication, and parallel query options PL/SQL Release 2.3.2.3.0 - Production SQL&amp;amp;gt;

To exit the SQL*Plus program (in any operating system), type EXIT and press Enter or carriage return:

SQL&amp;amp;gt; EXIT

Once a session has been established using the SQL*Plus tool, any SQL statements or SQL*Plus Commands may be issued. In the following section, the basic SQL*Plus Commands are introduced.

2 SQL*Plus Commands

SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to:

Enter, edit, store, retrieve, and run SQL statements
List the column definitions for any table
Format, perform calculations on, store, and print query results in the form of reports
Access and copy data between SQL databases

The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:

/ - Execute the current SQL statement in the buffer - same as RUN
ACCEPT - Accept a value from the user and place it into a variable
APPEND - Add text to the end of the current line of the SQL statement in the buffer
AUTOTRACE - Trace the execution plan of the SQL statement and gather statistics
BREAK - Set the formatting behavior for the output of SQL statements
BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
CHANGE - Replace text on the current line of the SQL statement with new text
CLEAR - Clear the buffer
COLUMN - Change the appearance of an output column from a query
COMPUTE - Does calculations on rows returned from a SQL statement
CONNECT - Connect to another Oracle database or to the same Oracle database under a different user name
COPY - Copy data from one table to another in the same or different databases
DEL - Delete the current line in the buffer
DESCRIBE - List the columns with datatypes of a table
EDIT - Edit the current SQL statement in the buffer using an external editor such as vi or emacs
EXIT - Exit the SQL*Plus program
GET - Load a SQL statement into the buffer but do not execute it
HELP - Obtain help for a SQL*Plus command (In some installations)
HOST - Drop to the operating system shell
INPUT - Add one or more lines to the SQL statement in the buffer
LIST - List the current SQL statement in the buffer
QUIT - Exit the SQL*Plus program
REMARK - Place a comment following the REMARK keyword
RUN - Execute the current SQL statement in the buffer
SAVE - Save the current SQL statement to a script file
SET - Set a variable to a new value
SHOW - Show the current value of a variable
SPOOL - Send the output from a SQL statement to a file
START - Load a SQL statement located in a script file and then run that SQL statement
TIMING - Used to time the execution of SQL statements for performance analysis
TTITLE - Place a title on the top of each page in the printout from a SQL statement
UNDEFINE - Delete a user defined variable

Examples of these SQL*Plus commands are given in the following sections.

Note the distinction made between SQL*Plus Commands and SQL Statements. SQL*Plus commands are proprietary to the Oracle SQL*Plus tool. SQL is a standard language that can be used is just about any Relational Database Management System (RDBMS).

3 SQL*Plus Help Facilities

Some versions of SQL*Plus store the help documentation in the database and make it available via the SQL*Plus command line. Newer installations have changed this and now store the documentation in HTML format which can be read using a World Wide Web Browser such as MS Internet Explorer or Netscape Navigator.

The following two sections describe how to invoke help in SQL*Plus under Windows 95/NT and under UNIX. The method you use to access help may differ according to how your software was installed.

3.1 Getting Help Under Windows 95/98/NT

To get HELP on any of the oracle tools, use the Oracle8 Documentation which is accessible through a web browser. To access the Oracle8 Documentation, click on the Windows 95 [Start]

button, then Programs, Oracle for Windows 95 and finally Oracle8 Documentation:

. This will launch your local Web Browser (Netscape Navigator/Communicator or Microsoft Internet Explorer) and the Welcome to the Oracle8 Documentation Library! screen will be displayed. From here, click on the "TEXT VERSION" link to get to the Oracle Product Documentation Library

Once in the Oracle8 Documentation main screen, click on Oracle8 Enterprise Edition and then SQL*Plus Getting Started for Windows NT/95. Other documentation you may find useful are:

Help File/Link	Contents
SQL Reference	Comprehensive syntax for all SQL statements
SQL*Plus Getting Started for Windows NT/95	Specific SQL*Plus commands and options for Windows 95 and NT users.
SQL*Plus Quick Reference	Quick reference guide to SQL*Plus commands.
SQL*Plus User's Guide and Reference	Comprehensive guide to using SQL*Plus.

Each of these can be found on the same Oracle8 Enterprise Edition page.

Oracle Products: An Overview

The Oracle products suite includes the following tools and utilities:

1 Application Development Tools

SQL*Plus - A command line tool used to manipulate tables and other database objects in an Oracle database.
Developer/2000 and Developer A suite of application development tools including Forms, Reports and Graphics.
- Oracle*Forms - A screen based tool used to develop data entry forms and menus that access tables in an Oracle database.
- Oracle*Reports - A screen based tool used to develop reports that access tables in an Oracle database.
- Oracle*Graphics - A graphical tool used to develop charts and reports that access tables in an Oracle database.
CASE*Designer and Oracle Designer/2000 - A graphical tool used to create and display models contained in the CASE*Dictionary.
CASE*Dictionary - A repository for business rules, functional models and data models used for organizing and documenting an application development effort.
CASE*Generator - A code generating tool that uses information stored in CASE*Dictionary to develop data entry forms, reports and graphics.
Oracle*Book - A graphical tool used to develop on-line documentation with hypertext capabilities.
SQL*TextRetrieval and Oracle Context - A suite of tools and API used to develop sophisticated text search and retrieval applications.
Programmer/2000 - Including the Pro* precompilers - Libraries of routines and utilities that can be linked with ``C'', C++, FORTRAN, Java, ADA, COBOL or other host languages to allow access to Oracle databases.

2 Database Utilities

Enterprise Manager - A GUI based collection of utilities for managing Oracle Databases.
SQL*DBA and SVRMGR - A utility that allows the database administrator (DBA) to monitor database activity and to tune the database for optimal performance.
Export/Import - Command line utilities that allow a user or the DBA to export data from an Oracle database into a machine readable file or to import data from a machine readable file into an Oracle database.
SQL*Loader - A command line utility to load ASCII or binary data files into an Oracle database.
Oracle*Terminal - A utility program used to customize the user interface and keyboard mappings for all Oracle tools. This utility allows all Oracle tools to have a similar ``look and feel'' across many different hardware and operating system platforms.

3 Connectivity and Middleware Products

SQL*Net and Net8 - A communications driver that allows an Oracle tool running on a client machine to access Oracle data on a separate server machine.
SQL*Connect and Oracle Gateways - A communications driver that allows an Oracle tool running on a client machine to access Non-Oracle data on a server machine such as data residing in a DB2 database or MS SQL Server database.
ORACLE Server - Typically a part of the Oracle RDBMS running on a database server, this component receives requests from client machines and submits them to the Oracle RDBMS. The results are then passed back to the client machines.
Oracle ODBC Drivers - Open DataBase Connectivity drivers for connecting software to Oracle databases using the ODBC standard.

4 Core Database Engine

ORACLE RDBMS - The Oracle Relational Database Engine. Now called the Oracle Universal Server with several options in addition to managing relational data. These options are now called Cartridges:
- Oracle Web Applications Server - A WWW Server (HTTP server) linked into the Oracle RDBMS. Allows web based applications using HTML forms and JAVA to access and manipulate data.
- Spatial Data Cartridge- Allows storage of temporal and spatial data in the Oracle RDBMS. Useful for Geographic Information Systems (GIS).
- Video Cartridge - Provides storage and real-time serving of streaming video.
- ConText Cartridge - Provides storage and retrieval of text documents.
- Messaging Option - A groupware architecture built on top of the RDBMS.
- OLAP Option - Tools and database support for On-Line Analytical Processing.
- Objects Option - Allows complex objects to be modeled and stored in the DBMS. Includes Object Oriented features such as encapsulation, inheritance, server and client side methods, etc.
Integrated Data Dictionary - Stores and manages access to all of the tables owned by all users in a system.
SQL - The language used to access and manipulate database data.
PL/SQL - A procedural extension to the SQL language unique to the Oracle line of products.

5 Typical Development Environments

Developing applications using an Oracle database requires access to a copy of the Oracle RDBMS (or a central Oracle RDBMS server), and one or more of the development tools. Third party development tools such as PowerBuilder, Visual Basic or Java can also be used for applications development.

Stand-alone development in a single user environment can be accomplished using the Personal Oracle or Personal Oracle Lite RDBMS in conjunction with Oralce Developer or a third party development tool.

Muli-user development in a shared environment can be accomplished using an Oracle RDBMS server running on a server machine. Distributed client PCs can develop the applications using any of the tools mentioned above.

Regardless of the development environment, used, the Oracle SQL*Plus utility is a convenient and capable tool for manipulating data in an Oracle database. In the following section, the SQL*Plus tool is introduced.

Introduction to oracle

The Oracle Relational Database Management System (RDBMS) is an industry leading database system designed for mission critical data storage and retrieval. The RDBMS is responsible for accurately storing data and efficiently retrieving that data in response to user queries.

The Oracle Corporation also supplies interface tools to access data stored in an Oracle database. Two of these tools are known as SQL*Plus, a command line interface, and Developer/2000 (now called simply Developer), a collection of forms, reports and graphics interfaces. This technical working paper introduces the features of the SQL*Plus tool and provides a tutorial that demonstrates its salient features.

This tutorial is intended for students and database practitioners who require an introduction to SQL, an introduction to working with the Oracle SQL*Plus tool, or both.

This document is organized as follows. A brief overview of the suite of Oracle products is first presented in Section 2. In Section 3, we discuss the basics of working with the SQL*Plus tool. Structured Query Language (SQL), including data definition language (DDL) and data manipulation language (DML) is discussed in section 4. Advanced SQL*Plus commands are discussed in section 5 and a brief introduction to stored procedures and triggers is given in section 6.

DBA Views

Storage Information

DBA_EXTENTS, DBA_FREE_SPACE, DBA_OBJECTS,

DBA_OBJECT_SIZE, DBA_SEGMENTS, DBA_TABLESPACES,

DBA_ROLLBACK_SEGS, DBA_UNDO_EXTENTS

Operating System

DBA_DATA_FILES, DBA_EXP_FILES, DBA_TEMP_FILES

Privileges

DBA_COL_PRIVS, DBA_PROFILES, DBA_ROLES,

DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS,

DBA_UPDATABLE_COLUMNS

Indexes

DBA_INDEXES, DBA_IND_COLUMNS,

DBA_IND_EXPRESSIONS, DBA_IND_PARTITIONS

Tables/Views

DBA_TABLES, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS,

DBA_TAB_COMMENTS,

DBA_UNUSED_COL_TABS, DBA_VIEWS

Constraints

DBA_CONSTRAINTS, DBA_CONS_COLUMNS

Triggers

DBA_TRIGGERS, DBA_TRIGGER_COLS,

DBA_INTERNAL_TRIGGERS

Materialized Views

DBA_MVIEW_AGGREGATES, DBA_MVIEW_ANALYSIS,

DBA_MVIEW_DETAIL_RELATIONS, DBA_MVIEW_JOINS,

DBA_MVIEW_KEYS

Partitions

DBA_PART_COL_STATISTICS, DBA_PART_HISTOGRAMS,

DBA_PART_INDEXES, DBA_PART_KEY_COLUMNS,

DBA_PART_LOBS, DBA_PART_TABLES,

DBA_IND_SUBPARTITIONS, DBA_LOB_PARTITIONS,

DBA_LOB_SUBPARTITIONS

Objects, Methods and Types

DBA_OBJECT_TABLES, DBA_METHOD_PARAMS,

DBA_METHOD_RESULTS, DBA_TYPES, DBA_TYPE_ATTRS,

DBA_TYPE_METHODS, DBA_DIMENSIONS, DBA_LOBS

Operators

DBA_OPANCILLARY, DBA_OPARGUMENTS,

DBA_OPBINDINGS, DBA_OPERATORS

Summaries

DBA_SUMMARIES, DBA_SUMMARY_AGGREGATES,

DBA_SUMMARY_DETAIL_TABLES, DBA_SUMMARY_JOINS,

DBA_SUMMARY_KEYS

Miscellaneous

DBA_DB_LINKS, DBA_SOURCE, DBA_SEQUENCES,

DBA_SYNONYMS, DBA_USERS, DBA_OUTLINES,

DBA_JOBS, DBA_JOBS_RUNNING, DBA_LIBRARIES,

DBA_PENDING_TRANSACTIONS, DBA_RULESETS,

DBA_OUTLINE_HINTS, DBA_POLICIES,

DBA_SUBPART_KEY_COLUMNS, DBA_TS_QUOTAS,

DBA_JAVA_POLICY, USER_JAVA_POLICY

Dynamic Performance Views

Instance Level Tuning

v$GLOBAL_TRANSACTION, v$OBJECT_DEPENDENCY,

v$SHARED_POOL_RESERVED, v$SORT_SEGMENT,

v$SYSTEM_CURSOR_CACHE, v$SORT_USAGE, v$STATNAME,

v$SYSSTAT, v$SYSTEM_EVENT, v$TRANSACTION, v$LATCH,

v$LIBRARYCACHE, v$ROLLSTAT, v$ROWCACHE,

vRSGASTAT, v$SQLAREA, v$SQLTEXT, v$WAITSTAT

Recovery Based Views

v$ARCHIVE, v$ARCHIVE_DEST, v$BACKUP_CORRUPTION,

v$BACKUP_DEVICE, v$BACKUP_REDOLOG,

v$DELETED_OBJECT, v$RECOVERY_LOG, v$RECOVER_FILE,

v$ARCHIVED_LOG, v$BACKUP, v$BACKUP_DATAFILE,

v$BACKUP_PIECE, v$BACKUP_SET,

v$RECOVERY_FILE_STATUS, v$RECOVERY_STATUS,

v$DATABASE_BLOCK_CORRUPTION,

v$DATABASE_INCARNATION

Cache Views

v$CACHE, v$LIBRARYCACHE, v$SUBCACHE,

v$DB_OBJECT_CACHE, v$ROWCACHE

Control File Views

v$CONTROLFILE, v$CONTROLFILE_RECORD_SELECTION

Cursor and SQL Views

v$SYSTEM_CURSOR_CACHE, v$OPEN_CURSOR, v$SQLAREA,

v$SQL, v$SQLTEXT, v$SQLTEXT_WITH_NEWLINES,

v$SQL_CURSOR, v$SQL_BIND_METADATA,

v$SQL_SHARED_MEMORY, v$SQL_BIND_DATA,

v$SQL_WORKAREA, v$SQL_WORKAREA_ACTIVE

Security Views

v$ENABLEDPRIVS, v$PWFILE_USERS

Session Views

v$ACCESS, v$MYSTAT, v$PROCESS, v$SESSION ,

v$SESSION_CONNECT_INFO, v$SESSION_CURSOR_CACHE,

v$SESSION_EVENT, v$SESSION_LONGOPS,

v$SESSION_OBJECT_CACHE, v$SESSION_WAIT, v$SESSTAT

(needs v$statname, v$session join), v$SESS_IO

Latch and Lock Views

v$BUFFER_POOL, v$CACHE_LOCK, v$CLASS_PING,

v$DLM_CONVERT_LOCAL, v$DLM_CONVERT_REMOTE,

v$DLM_LATCH, v$DLM_MISC, v$ENQUEUE_LOCK,

v$EVENT_NAME, v$FALSE_PING, v$FILE_PING, v$LATCH,

v$LATCHHOLDER, v$LATCHNAME, v$LATCH_CHILDREN,

v$LATCH_MISSES, v$LATCH_PARENT, v$LOCK,

v$LOCK_ACTIVITY, v$LOCK_ELEMENT, v$LOCKED_OBJECT,

v$LOCKS_WITH_COLLISIONS, v$PING, v$RESOURCE,

v$RESOURCE_LIMIT, v$TRANSACTION_ENQUEUE, v$LOCK

Instance Views

$ACTIVE_INSTANCES, v$BGPROCESS, v$BH,

v$COMPATIBILITY, v$COMPATSEG, v$COPY_CORRUPTION,

v$DATABASE, v$DATAFILE, v$DATAFILE_COPY,

v$DATAFILE_HEADER, v$DBFILE, v$DBLINK, v$DB_PIPES,

v$INSTANCE, v$LICENSE, v$OFFLINE_RANGE, v$OPTION,

v$ROLLSTAT, v$SGA, v$SGA_STAT, v$TABLESPACE,

v$TRANSACTION, v$UNDOSTAT, v$VERSION

Fixed Views

v$FIXED_TABLE, v$FIXED_VIEW_DEFINITION,

v$INDEXED_FIXED_COLUMN

Miscellaneous Views

v$TIMER, v$TYPE_SIZE, v$SEQUENCES

MTS and Parallel Server Views

v$CIRCUIT, v$DISPATCHER, v$DISPATCHER_RATE, v$MTS,

v$QUEUE, v$REQDIST, v$SHARED_SERVER, v$THREAD

File Mapping

v$MAP_LIBRARY, v$MAP_FILE, v$MAP_FILE_EXTENT,

v$MAP_ELEMENT, v$MAP_EXT_ELEMENT,

v$MAP_SUBELEMENT, v$MAP_COMP_LIST,

v$MAP_FILE_IO_STACK

1 comments:

Oracle Application 11.5.10 Installation on Linux Adv. Server

Operating System: Red Hat Enterprise Linux AS 3.0

First Node
Hardware Specification

Brand & Model: Dell Optiplex GX270
CPU: Pentium 4, Speed: 2.8GHz
HDD: 200GB
RAM: 2 GB

Required Packages:

compat-libstdc++-devel-7.3-2.96.122
libstdc++-devel-3.2.3-20
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-db-4.0.14-5
openmotif21-2.1.30-8
setarch-1.3-1
compat-libstdc++-7.3-2.96.122

Oracle Applications 11i (11.5.10) Installation:

Group creation
# groupadd dba

User Creation

User Name: appltest
Password: appltest
User ID: 501
Primary group: dba
Full Name: appltest
Login Shell: /bi/bash
Home Directory: /home/appltest

Stage Area Creation: (Enter the following commands)

# cd /applest
# mkdir Stage11i
# cd Stage11i
# mkdir oraDB
# mkdir oradata
# mkdir ora8i
# mkdir oraApps
# mkdir StartCD

# cd StartCD
# mkdir Disk1
# cd ..
# cd oraDB
# mkdir Disk1
# cd ..
# cd oradata
# mkdir Disk1
# mkdir Disk2
# mkdir Disk3
# mkdir Disk4
# cd ..
# cd ora8i
# mkdir Disk1
# cd ..
# cd oraApps
# mkdir Disk1
# mkdir Disk2

Oracle Applications 11i – RDBMS - Disk1# mount /mnt/cdrom
# cd /appltest/Stage11i/oraDB/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Database - Disk1# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Database – Disk2
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk2
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Database – Disk3
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk3
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Database – Disk4# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk4
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Tools - Disk1
# mount /mnt/cdrom
# cd /appltest/Stage11i/ora8i/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – APPL_TOP - Disk1# mount /mnt/cdrom
# cd /appltest/Stage11i/oraApps/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – APPL_TOP – Disk2
# mount /mnt/cdrom
# cd /appltest/Stage11i/oraApps/Disk2
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Download the latest Rapid Install Patch 4132885, p4132885_11i_GENERIC.zip from http://metalink.oracle.com
This patch contains the latest Rapid Install Wizard, version 11.5.10.33

Copy the p4132885_11i_GENERIC.zip file from CD to the 11i stage directory
# cp -r /mnt/cdrom/* /appltest/Stage11i/StartCD/Disk1

# unzip -o p4132885_11i_GENERIC.zip -d /appltest/Stage11i/StartCD/Disk1

Execute the following command to show the latest Rapid Install Wizard version 11.5.10.33
# cd /appltest/Stage11i/StartCD/Disk1/startCD/Disk1/rapidwiz
# sh RapidWizVersion
Version 11.5.10.33
#

Download the Patch 3006854, p3006854_9204_Linux.zip from http://metalink.oracle.com
Copy p3006854_9204_Linux.zip file to /opt/Patch
# cd /opt/Patch
# unzip p3006854_9204_Linux.zip
# cd 3006854
# sh rhel3_pre_install.sh
Patch Installed
#

Login as root
Go to startCD
# cd /appltest/Stage11i/StartCD/Disk1/startCD/Disk1/rapidwiz
# sh rapidwiz
**Provide your feedback on this post.

Tech Area

Pages