Managing Schema Objects
- Alter Table Statement
Use the ALTER TABLE statement to :
a.Add a new column
b.Modify an existing column
c.Define a default value for the new column
d.Drop a Column
Use the ALTER TABLE statement to add,modify or drop columns :
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype]…);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype]…);
ALTER TABLE table
DROP (column);
We can add columns to a table, modify columns and drop columns from a table by using the ALTER TABLE statement
In the syntax :
table – Is the name of the Table
ADD|MODIFY|DROP – Is the name of the modification
column – Is the name of the new column
datatype – Is the datatype and length of the new column
DEFAULT expr – Specifies the default value for a new column
2. Adding a Column
a. You can use the ADD clause to add columns
ALTER TABLE emp_master ADD (date_of_birth DATE)
/
b.The new column added to the Table becomes the Last Column in the Table.
You cannot add a NOT NULL column to a table that contains data in other columns.
Example :
CREATE TABLE aa(id NUMBER,name VARCHAR2(40))
/
INSERT INTO aa(name)VALUES(‘HH’)
/
ALTER TABLE aa MODIFY(id NUMBER NOT NULL)
/
This will error out.
— So Delete all the records then add the NOT NULL to the column —
DELETE aa
/
ALTER TABLE aa MODIFY(id NUMBER NOT NULL)
/
DROP TABLE aa
/
DROP TABLE emp_master
/
You can add a NOT NULL column only to an empty Table.
3. Modifying an existing Column
a.We can change a column’s data type, size and default value provided the Table Column is empty
Example :
CREATE TABLE EMP_MASTER(DATE_OF_BIRTH DATE)
/
INSERT INTO EMP_MASTER VALUES(’01-JAN-2010′)
/
ALTER TABLE EMP_MASTER MODIFY(DATE_OF_BIRTH VARCHAR2(20))
/
This will error out.
— So Delete all the records then modify the data type to the column —
DELETE EMP_MASTER
/
ALTER TABLE EMP_MASTER MODIFY(DATE_OF_BIRTH VARCHAR2(20))
/
DROP TABLE EMP_MASTER
/
b.A Change to the default value affects only subsequent insertions to the Table.
Guidelines :
We can increase the width or precision of a numeric column
We can increase the width of numeric or character column
We can decrease the width of a column if
The Column contains NULL values and Table has no records.
The decrease in column width should not be less than the existing values in that column.
We can change the datatype of the column contains only null values
Columns having CHAR Datatype can be converted to VARCHAR2 even if the columns contains values. But the size of the data type cannot be reduced if the col contains any values.
CREATE TABLE EMP_MASTER(NAME VARCHAR2(4))
/
INSERT INTO EMP_MASTER VALUES(‘1234’)
/
ALTER TABLE EMP_MASTER MODIFY(NAME VARCHAR2(2))
/
This command will error out since there is already a value in the column
and we trying to reduce that column width
So delete the records and then modify the Table
UPDATE emp_master SET name = NULL
/
DELETE FROM emp_master
/
ALTER TABLE EMP_MASTER MODIFY(NAME VARCHAR2(2))
/
4.
Dropping a Column
Use the DROP COLUMN to drop columns that you no longer need.
ALTER TABLE DROP COLUMN NAME
/
Example to illustrate Dropping a Column from a Table
1.
— Create a Table —
CREATE TABLE emp3 AS SELECT * FROM emp WHERE 1=2
/
2.
— Describe the Table —
DESC emp3;
3.
— Drop a Single Column —
ALTER TABLE emp3 DROP (empno)
/
4.
— Describe the Table —
DESC emp3;
5.
— Dropping more than one Column —
ALTER TABLE emp3 DROP (ename,sal)
/
6.
— Describe the Table —
DESC emp3;
7.
— Dropping all the Columns in the Table (This SQL will error out) —
ALTER TABLE emp3 DROP (job,mgr,hiredate,comm,deptno)
/
A Column can be dropped even if it contains values in it.
All the columns cannot be dropped
A column cannot be dropped if it is part of a constraint unless the cascade option is added
5.
SET UNUSED option
a.We use the SET UNUSED option to mark one or more columns as unused.
b.We use the DROP UNUSED COLUMNS option to remove the columns marked as unused.
c.All Constraints,Indexes defined on the Column are also removed.
Syntax :
ALTER TABLE
SET UNUSED ()
/
OR
ALTER TABLE
SET UNUSED COLUMN
/
ALTER TABLE
DROP UNUSED COLUMNS
/
Example to illustrate SETUNUSED Columns to a Table.
1.
— Create a Table —
CREATE TABLE rahul(id NUMBER,name VARCHAR2(50),date_of_dob DATE)
/
2.
— Describe the Table —
DESC rahul;
3.
— Alter the Table and use Set Unused for multiple columns —
ALTER TABLE rahul
SET UNUSED (id,name)
/
4.
— Describe the Table —
DESC rahul;
5.
— Alter the Table to add the columns (The Same Column Name for Columns set as Unused) —
ALTER TABLE rahul ADD(id NUMBER)
/
6.
— Describe the Table —
DESC rahul;
7.
— Alter the Table and use Set Unused for a single column —
ALTER TABLE rahul
SET UNUSED (id)
/
8.
— Describe the contents of Unused Column for a Table —
DESC user_unused_col_tabs;
9.
— Query the Data Dictionary View —
SELECT count,table_name FROM user_unused_col_tabs
WHERE table_name = ‘RAHUL’
/
10.
— Drop all the Unused Columns —
ALTER TABLE rahul DROP UNUSED COLUMNS
/
DROP TABLE rahul PURGE
/
6.
Adding a Constraint Syntax
Use the ALTER TABLE statement to :
a.Add or drop a Constraint
b.Enable or disable constraints
c.Add a NOT NULL constraint using the MODIFY clause
ALTER TABLE
ADD [CONSTRAINT ]
type ()
/
Table – Name of the Table
Constraint – Name of the constraint
Type – Constraint Type
Column – Name of the column affected by the constraint
7.
Adding a Constraint
Example to Add a Foreign Key Constraint indicating that a manager must exist as a Valid Employee
1.
— Create a Table similiar to the EMP Table Structure —
CREATE TABLE emp2 AS SELECT * FROM EMP WHERE 1=2
/
2.
— Describe the New Table —
DESC emp2;
3.
— Add a Primary Key Constraint to the New Table —
ALTER TABLE emp2
MODIFY empno PRIMARY KEY
/
— Describe the New Table —
DESC emp2;
4.
— Add Foreign Key Constraint to the Existing Table —
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr)
REFERENCES emp2(empno)
/
5.
— Insert Values into the Foreign Key Table —
— A Foreign Key Column can accept NULL values —
INSERT INTO emp2(empno,ename)VALUES(100,’Rahul’)
/
6.
— Select the Records —
SELECT * FROM emp2
/
7.
— Insert Records into Table with Relation —
INSERT INTO emp2(empno,ename,mgr)VALUES(101,’Ganesh’,100)
/
8.
— Select the Records —
SELECT * FROM emp2
/
9.
— Insert a Non – Existing Parent Value to a Foreign Key Column —
— This will result in Constraint Violation —
INSERT INTO emp2(empno,ename,mgr)VALUES(102,’Sridhar’,10)
/
10.
— Drop the Table —
DROP TABLE emp2
/
Example to Add a Check Constraint to an existing Table
1.
— Create a Table —
CREATE TABLE t55(job VARCHAR2(50))
/
2.
— Using ALTER Table Command Add Check Constraint —
ALTER TABLE t55 ADD CONSTRAINT t55_c CHECK(job IN(‘MGR’,’CLERK’))
/
3.
— Insert Values into the Column —
INSERT INTO t55 VALUES(‘MGR’)
/
— This DML will result in CHECK Constraint Violation —
INSERT INTO t55 VALUES(‘mgr’)
/
INSERT INTO t55 VALUES(‘CLERK’)
/
8.
ON DELETE CASCADE
Delete the child rows when a parent key is deleted.
CREATE TABLE emp2 AS SELECT * FROM EMP
/
ALTER TABLE emp2
ADD CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY(EMPNO)
/
CREATE TABLE DEPT2 AS SELECT * FROM DEPT
/
ALTER TABLE DEPT2 MODIFY deptno PRIMARY KEY
/
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY(deptno) REFERENCES dept2 ON DELETE CASCADE
/
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY(deptno) REFERENCES dept2 ON DELETE SET NULL
/
DROP TABLE EMP2
/
DROP TABLE DEPT2
/
9.
Deferring Constraints
There are three types :
1.NOT DEFERRED IMMEDIATE (Default)
2.DEFERRABLE INITIALLY IMMEDIATE
3.DEFERRABLE INITIALLY DEFERRED
When a Constraint is set as DEFERRED the System enforces the rule only at the time of COMMIT.
If the constraint is violated then COMMIT causes the transactions or set of transactions to rollback.
Only UNIQUE,PRIMARY KEY and REFERENCES (Foreign Key) accepts this clause.NOT NULL and CHECK constraints cannot be set to deferrable.
Example illustrate DEFERRABLE INITIALLY DEFERRED in Constraints.
1.
— Create a Table —
CREATE TABLE a(id NUMBER)
/
2.
— Select the User Constraints for information on Constraints —
SELECT constraint_name,deferrable,deferred
FROM user_constraints WHERE table_name = ‘A’
/
3.
— Add Primary Key Constraint to the existing Table —
ALTER TABLE a ADD CONSTRAINT a_id_pk PRIMARY KEY(ID) DEFERRABLE INITIALLY DEFERRED
/
4.
— Select the User Constraints for information on Constraints —
SELECT constraint_name,deferrable,deferred
FROM user_constraints WHERE table_name = ‘A’
/
5.
— Insert some set of records (Duplicate and Valid Records) —
INSERT INTO a VALUES(1)
/
INSERT INTO a VALUES(2)
/
INSERT INTO a VALUES(3)
/
COMMIT
/
INSERT INTO a VALUES(1)
/
In Between this before a COMMIT I execute a DDL command
System performs an Implicit COMMIT and enables the Constraint.
CREATE TABLE at(id NUMBER)
/
6.
— Give Commit (System performs a ROLLBACK for the Invalid Record) —
COMMIT
/
7.
— Select the Valid Records from the Table —
SELECT * FROM a
/
8.
— This can be changed to IMMEDIATE using the command —
SET CONSTRAINT a_id_pk IMMEDIATE
/
9.
— Insert some set of records (Duplicate Records) —
INSERT INTO a VALUES(3)
/
10.
— From IMMEDIATE it can be changed to DEFERRED using the command —
SET CONSTRAINT a_id_pk DEFERRED
/
10.
— Insert some set of records (Duplicate and Valid Records) —
INSERT INTO a VALUES(4)
/
INSERT INTO a VALUES(5)
/
COMMIT
/
INSERT INTO a VALUES(5)
/
COMMIT
/
SELECT * FROM a
/
Note :
All the constraints can be changed to IMMEDIATE for a particular session.
ALTER SESSION SET CONSTRAINTS = IMMEDIATE
10.
Dropping a Constraint
Remove the Manager Constraint created earlier from emp2 Table
ALTER TABLE
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint[CASCADE]
/
table_name – Name of the Table
Column – Name of the column
Constraint – Name of the constraint
CREATE TABLE emp2 AS SELECT * FROM emp
/
ALTER TABLE emp2
ADD CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY(empno)
/
ALTER TABLE emp2
DROP CONSTRAINT EMP2_EMPNO_PK
/
Remove the PRIMARY KEY constraint on the dept2 Table and drop the associated FOREIGN KEY constraint on the emp2.deptno column
CREATE TABLE dept2 AS SELECT * FROM dept
/
ALTER TABLE dept2 MODIFY deptno PRIMARY KEY
/
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY(deptno) REFERENCES dept2 ON DELETE CASCADE
/
ALTER TABLE dept2
DROP PRIMARY KEY CASCADE
/
DROP TABLE emp2
/
DROP TABLE dept2
/
11.
Disabling a Constraint
a.Use the DISABLE clause in the ALTER TABLE statement to disable a constraint
b.Use the CASCADE option to disable dependent integrity constraints
— Create EMP2 Table —
CREATE TABLE emp2 AS SELECT * FROM emp
/
— Add the PRIMARY KEY Constraint to EMPNO Column —
ALTER TABLE emp2
ADD CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY(empno)
/
— Disable the Constraint PRIMARY KEY —
ALTER TABLE emp2
DISABLE CONSTRAINT EMP2_EMPNO_PK
/
ALTER TABLE emp2
ENABLE CONSTRAINT EMP2_EMPNO_PK
/
— Create DEPT2 Table —
CREATE TABLE dept2 AS SELECT * FROM dept
/
— Add the PRIMARY KEY Constraint to DEPTNO Column —
ALTER TABLE dept2 MODIFY deptno PRIMARY KEY
/
— Add the Foreign Key Constraint to EMP2 Table DEPTNO Column which refers DEPT2.DEPTNO Column —
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY(deptno) REFERENCES dept2 ON DELETE CASCADE
/
— Disable the Constraint PRIMARY KEY —
ALTER TABLE dept2
DISABLE PRIMARY KEY CASCADE
/
DROP TABLE emp2
/
DROP TABLE dept2
/
While disabling a UNIQUE and PRIMARY KEY constraint system removes the unique index too.
12.
Enabling Constraints
— Create EMP2 Table —
CREATE TABLE emp2 AS SELECT * FROM emp
/
— Add the PRIMARY KEY Constraint to EMPNO Column —
ALTER TABLE emp2
ADD CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY(empno)
/
— Disable the Constraint PRIMARY KEY —
ALTER TABLE emp2
DISABLE CONSTRAINT EMP2_EMPNO_PK
/
— Enable the Constraint PRIMARY KEY —
ALTER TABLE emp2
ENABLE CONSTRAINT EMP2_EMPNO_PK
/
DROP TABLE emp2
/
A unique Index is created when we enable a UNIQUE or PRIMARY KEY constraint
Enabling a Primary Key constraint disabled with CASCADE option does not enable any foreign keys dependent on primary key.
13.
Cascading Constraints
ALTER TABLE emp2
DROP COLUMN empno CASCADE CONSTRAINTS
/
Submitting the above statement drops the EMPNO column,the Primary Key Constraint and any Foreign Key Constraint referencing the Primary Key constraint for the EMP2 Table.
ALTER TABLE dept2
DROP COLUMN deptno CASCADE CONSTRAINTS
/
13.
CREATE INDEX with the CREATE TABLE statement
CREATE TABLE new_emp
(empno NUMBER(6) PRIMARY KEY USING INDEX(CREATE INDEX emp_id_idx ON new_emp(empno)),
ename VARCHAR2(40),
salary NUMBER)
/
SELECT index_name,table_name FROM user_indexes
WHERE table_name = ‘NEW_EMP’
/
DROP TABLE new_emp
/
CREATE TABLE emp_unnamed_index
(empno NUMBER(6) PRIMARY KEY,
ename VARCHAR2(45),
salary NUMBER)
/
SELECT index_name,table_name
FROM user_indexes
WHERE table_name = ‘EMP_UNNAMED_INDEX’
/
DROP TABLE emp_unnamed_index
/
— We can also use an existing index for the Primary Key column too. —
Ex When we are expecting a large data load and want to speed up the operation now we may want to disable the constraints and then load and after loading enable the constraints in which case having a unique index on the primary key will still cause the data to be verified during the load.
So we first create a nonunique index on the column designated as PRIMARY KEY and then create the PRIMARY KEY column and specify that it should use the existing index.
Create the Table —
CREATE TABLE new_emp2
(empno NUMBER(6),
ename VARCHAR2(40),
salary NUMBER)
/
Create the Index —
CREATE INDEX emp_id_idx2 ON
new_emp2(empno)
/
Create the Primary Key —
ALTER TABLE new_emp2 ADD PRIMARY KEY(empno)USING INDEX emp_id_idx2
/
SELECT index_name,table_name
FROM user_indexes
WHERE table_name = ‘NEW_EMP2’
/
DROP TABLE new_emp2
/
14.
Function – Based Indexes
a. A Function-based Index is based on expressions
b.The Index expression is built from table columns,constants,SQL Functions and User-Defined functions.
drop table dept1
/
CREATE TABLE DEPT1 AS SELECT * FROM DEPT
/
CREATE INDEX upper_dept_name_idx
ON dept1(UPPER(dname))
/
So the above index would come into effect when the UPPER Function is used for the DNAME Column from DEPT2 Table
SELECT * FROM
dept1
WHERE UPPER(dname) = ‘SALES’
/
So in order to use this Function based index we need to set the value of the QUERY_REWRITE_ENABLED to TRUE
15.
Removing an Index
DROP INDEX index_name
/
DROP INDEX upper_dept_name_idx
/
To use the DROP INDEX command the user should have the DROP ANY INDEX privilege granted.
16.
DROP TABLE…PURGE
When a Table is dropped the Oracle Database does not immediately release the space associated with that table.
It renames the Table and places it in Recycle Bin wherein it can be recovered using FLASHBACK TABLE statement.
If we want to immediately release the space associated with the table use the PURGE with DROP TABLE clause.
So this clause first drops the Table and then purging it from the Recycle Bin.
Example.
CREATE TABLE AAA(ID NUMBER)
/
SELECT * FROM TAB
/
DROP TABLE AAA
/
SELECT * FROM TAB
/
DROP TABLE AAA PURGE
/
17.
FLASHBACK TABLE statement
This is a repair tool for accidental table modifications.
It restores a Table to an earlier point in time.
Syntax
FLASHBACK TABLE[schema.]table[,
[ schema.]table ]…
TO {TIMESTAMP |SCN} expr
[ { ENABLE | DISABLE } TRIGGERS ];
1.Example to illustrate Drop a Table without PURGE and restore it later on.
1.
— Create Table —
CREATE TABLE aaa(id NUMBER)
/
2.
— Drop the Table without PURGE —
DROP TABLE aaa
/
3.
— Query the Recyclebin Contents —
SELECT original_name,operation,droptime
FROM recyclebin
/
4.
— Restore the Table Contents —
FLASHBACK TABLE aaa TO BEFORE DROP
/
2.Example to illustrate Drop a Table without PURGE
1.
— Create Table —
CREATE TABLE aaa(id NUMBER)
/
2.
— Drop the Table without PURGE —
DROP TABLE aaa
/
3.
— Query the Recyclebin Contents —
SELECT original_name,operation,droptime
FROM recyclebin
/
4.
— Purge the Table dropped —
PURGE TABLE aaa
/
5.
— Restore the Table Contents —
FLASHBACK TABLE aaa TO BEFORE DROP
/
3.Example to illustrate Drop a Table with PURGE
1.
— Create Table —
CREATE TABLE aaa(id NUMBER)
/
2.
— Drop the Table with PURGE —
DROP TABLE aaa PURGE
/
3.
— Query the Recyclebin Contents —
SELECT original_name,operation,droptime
FROM recyclebin
/
4.
— Restore the Table Contents —
FLASHBACK TABLE aaa TO BEFORE DROP
/
4.Example to illustrate Drop a Table with PURGE
1.
— Create Table —
CREATE TABLE aaa(id NUMBER)
/
2.
— Drop the Table —
DROP TABLE aaa
/
3.
— Restore the Table Contents —
FLASHBACK TABLE aaa TO BEFORE DROP
/
4.
— Describe the Table —
DESC aaa;
5.
— Drop the Table with PURGE —
DROP TABLE aaa PURGE
/
6.
— Restore the Table Contents —
FLASHBACK TABLE aaa TO BEFORE DROP
/
A user can view his or her objects in the recyclebin by using the following SQL statement
SELECT original_name,operation,droptime
FROM recyclebin
/
A RECYCLEBIN can be purged with the following statement
PURGE RECYCLEBIN
/