oracle sql and dba tutorial logo

Tutorial for Oracle DBA

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions

42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Create Table

Oracle SELECT Statement

Formatting in SQL*Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result




Oracle SQL Functions

Oracle NVL() Function with Examples

Oracle SUBSTR() function with Examples

Oracle TO_DATE() with complete examples

Oracle DECODE function

Oracle INSTR() function with examples

Oracle TO_CHAR() function


Number Functions (Math Functions)

Character Functions

Miscellaneous Functions

Aggregate Functions

Date and Time Functions

Oracle Join Queries

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions




Data Control Language (GRANT, REVOKE)



Integrity Constraints (PRIMARY KEY, NOT NULL...)


Dropping Constraints

Disabling and Enabling

Differing Constraints Check

View Info about Constraints

Working with Dates

Oracle Views

Oracle Sequences

Oracle Synonyms

Indexes and Clusters

Table Partitioning

Altering Partition Tables

Dropping Partitions

Merging Partitions

Splitting Partitions

Coalescing Partitions

Oracle Objects and Object Types










Oracle DDL Commands

Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Oracle implicitly commits the current transaction before and after every DDL statement.

Many DDL statements may cause Oracle to recompile or reauthorize schema objects.

DDL Statements are

CREATE        :Use to create objects like CREATE TABLE, CREATE FUNCTION,
                        CREATE SYNONYM, CREATE VIEW. Etc.

ALTER           :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
                         TABLESPACE, ALTER DATABASE. Etc.

DROP             :Use to Drop Objects like DROP TABLE, DROP USER, DROP
                        TABLESPACE, DROP FUNCTION. Etc.

REPLACE      :Use to Rename table names.

TRUNCATE   :Use to truncate (delete all rows) a table.



To create tables, views, synonyms, sequences, functions, procedures, packages etc.


To create a table, you can give the following statement

create table emp (empno number(5) primary key,
                   name varchar2(20),
                   sal number(10,2),
                   job varchar2(20),
                   mgr  number(5),
                   Hiredate  date,
                   comm number(10,2));

Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000.



To do this we can first create TAX table by defining column names and datatypes and then use INSERT into EMP SELECT …. statement to insert rows from emp table. like given below.

create table tax (empno number(5), tax number(10,2));

insert into tax select empno,(sal-5000)*0.40
                     from emp where sal > 5000;

Instead of executing the above two statements the same result can be achieved by giving a single CREATE TABLE AS statement.

create table tax as select empno,(sal-5000)*0.4
   as tax from emp where sal>5000

You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement.

create table emp2 as select * from emp;

To copy tables without rows i.e. to just copy the structure give the following statement

create table emp2 as select * from emp where 1=2;

Temporary Tables (From Oracle Ver. 8i)

It is also possible to create a temporary table. The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. You use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific:

This example creates a temporary table that is transaction specific:

        (empno number(5),
          ename varchar2(20),
          sal   number(10,2),
          tax   number(10,2))

Indexes can also be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.


Use the ALTER TABLE statement to alter the structure of a table.


To add  new columns addr, city, pin, ph, fax to employee table you can give the following statement

alter table emp add (addr varchar2(20), city varchar2(20),
      pin varchar2(10),ph varchar2(20));

To modify the datatype and width of a column. For example we you want to increase the length of the column ename from varchar2(20) to varchar2(30) then give the following command.

alter table emp modify (ename varchar2(30))

To decrease the width of a column the column can be decreased up to largest value it holds.

alter table emp modify (ename varchar2(15));

The above is possible only if you are using Oracle ver 8i and above. In Oracle 8.0 and 7.3 you cannot decrease the column width directly unless the column is empty.

To change the datatype the column must be empty in All Oracle Versions.

To drop columns.

From Oracle Ver. 8i you can drop columns directly it was not possible in previous versions.

For example to drop PIN, CITY  columns from emp table.

alter table emp drop column (pin, city);

Remember you cannot drop the column if the table is having only one column.

If the column you want to drop is having primary key constraint on it then you have to give cascade constraint clause.

alter table emp2 drop column (empno) cascade constraints;

To drop columns in previous versions of Oracle8.0 and 7.3. and to change the column name in all Oracle versions do the following.

For example we want to drop pin and city columns and to change SAL column name to SALARY.

Step     1: Create a temporary table with desired columns using subquery.

create table temp as select empno, ename,
 sal AS salary, addr, ph from emp;

Step     2: Drop the original table.

drop table emp;

Step     3: Rename the temporary table to the original table.

rename temp to emp;


Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.


To rename table emp2 to employee2 you can give the following command.

rename emp2 to employee2


Use the drop statement to drop tables, functions, procedures, packages, views, synonym, sequences, tablespaces etc.


The following command drops table emp2

drop table emp2;

If emp2 table is having primary key constraint, to which other tables refer to, then you have to first drop referential integrity constraint and then drop the table. Or if you want to drop table by dropping the referential constraints then give the following command

drop table emp2 cascade constraints;


Use the Truncate statement to delete all the rows from table permanently . It is same as “DELETE FROM <table_name>” except


truncate table emp;

If you do not want free space and keep it with the table. Then specify the REUSE storage clause like this

truncate table emp reuse storage;




HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved