Oracle Tutorial

INTRODUCTION

This tutorial will cover the basics of oracle database operations with sample queries.

I will cover the below topics

1. DDL (Data Definition Language) Operations
2. DML (Data Manipulation Language) Operations
3. DCL (Data Control Language) Operations
4. DATE Columns Operations
5. TIMESTAMP Columns Operations
6. CREATE New User
7. Find Operations

1. DDL OPERATIONS

1.1 CREATE

1.2 ALTER

1.3 DROP

1.4 TRUNCATE

1.5 RENAME

1. Rename Table

ALTER TABLE <OldTableName> RENAME TO <NewTableName>;

     in case, if you are running on above statement in Stored Procedure, then

EXECUTE IMMEDIATE 'ALTER TABLE <OldTableName>RENAME TO <NewTableName>';

2. Rename Index

ALTER INDEX <OldIndexName> RENAME TO <NewIndexName>;

in case, if you are running on above statement in Stored Procedure, then

EXECUTE IMMEDIATE 'ALTER INDEX <OldIndexName> RENAME TO <NewIndexName>';

3. Rename Constraints

ALTER TABLE <TableName> RENAME CONSTRAINT <OldConstraintName> TO <NewConstraintName>;

in case, if you are running on above statement in Stored Procedure, then

EXECUTE IMMEDIATE 'ALTER TABLE <TableName> RENAME CONSTRAINT <OldConstraintName> TO <NewConstraintName>'

2. DML OPERATIONS

2.1 SELECT

1. How to select Multiple Rows with Multiple Values?

select case 'TEST' when 'X' then '123' when 'Y' then '456' when 'TEST' then 'success' else 'exit' end from dual select decode('TEST','X','123','Y','456','TEST','Success','exit') from dual;

2.2 INSERT

2.3 UPDATE

1. Normal Update.

UPDATE <TABLENAME> SET COLUMN=<VALUE> WHERE COLUMN=<SOME VALUE>;

2. Update a particular text on the field value

Consider a table MYEMPLOYEE with Columns: EMPID, NAME, ADDRESS

When inserting the data, if any value entered wrongly as below

and want to update a particular text in it, we can use the below query.

UPDATE myemployee set address = replace(address, 'gandhi', 'nehru') where address like '%gandhi%';

and the result would be upon select query is

2.4 DELETE

2.5 MERGE

3. DCL OPERATIONS

3.1 GRANT

3.2 REVOKE

4. DATE COLUMN OPERATIONS

4.1 SELECT

SELECT * from employee where to_char(dob, 'mmddyyyy') = 03262012;

4.2 UPDATE

UPDATE employee set dob=to_date('20120117', 'yyyymmdd') where id = 1004;

5. TIMESTAMP COLUMN OPERATIONS

5.1 UPDATE

UPDATE employee SET dob = TO_TIMESTAMP('17-Jan-2012 12:00:00.000000 PM') where id=1004;

6. CREATE NEW USER

1. Find the tablespaces (Default & Temporary tablespace)

select * from dba_tablespaces;

if tablespace not found then create it.

create tablespace lob_data datafile 'lob_data' size 200M; create tablespace user_data datafile 'user_data' size 100M; create tablespace index_data datafile 'index_data' size 100;

2. Create new user

CREATE USER RANJITH IDENTIFIED BY RANJITH DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"; ALTER USER RANJITH QUOTA UNLIMITED ON LOB_DATA; ALTER USER RANJITH QUOTA UNLIMITED ON INDEX_DATA; ALTER USER RANJITH QUOTA UNLIMITED ON USER_DATA; GRANT UNLIMITED TABLESPACE TO RANJITH; GRANT ALL PRIVILEGES to RANJITH;

3. Grant Permissions

grant create session, connect, resource to RANJITH;

4. Simple admin tasks to Resize datafile in case of out of space

These command should be run from sysdba user

ALTER DATABASE DATAFILE 'lob_data' RESIZE 1G; ALTER DATABASE DATAFILE 'user_data' RESIZE 500M; ALTER DATABASE DATAFILE 'index_data' RESIZE 300M;

7. FIND OPERATIONS

1. How to find duplicate data in a table in Oracle?

SELECT columnName, COUNT(columnName) FROM tableName GROUP BY columnName HAVING COUNT (columnName) > 1; (OR) SELECT columnName, COUNT(*) FROM tableName GROUP BY columnName HAVING COUNT (*) > 1;