Flushing out Buffer Cache and Shared Pool

Buffer cache which is part of System Global Area (SGA) holds the copy of data blocks in order to minimize the physical I/O.It caches the data that has been most recently accessed by the database users. Once you issue the SQL query, it fetches the data blocks from the disk and places it in the buffer. If you issue the same query again, it will retrieve blocks from the buffer cache instead of reading from disk.

With Oracle 10g. you can flush the buffer cache using the following statement:
alter system flush buffer_cache;

Shared Pool which is a required component in SGA caches most recently used SQL statements. All recently used SQL statements are removed from memory by flushing the shraed pool. Issue the following statement to flush the Shared Pool.
alter system flush shared_pool;

Creating Database Objects (Tables/Views/Sequences/Indexes/Synonyms)

Table: Store data in the form of rows and columns

View: Subset of data from one or more tables

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

Sequence: Generates sequential lists of numbers to create unique surrogate key values.

CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}];


Indexes: Index is a schema object that can be used by Oracle server to speed up retrieval of rows by using a pointer. Indexes are created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition. Manually users can create indexes on columns to speed up access to rows.

CREATE INDEX index_name
ON table (column[, column]...);

Removing an index:
DROP INDEX index_name;

Synonynms: Create another name for an object so that you can shorten lengthy object names.
CREATE [PUBLIC] SYNONYM synonym_name
FOR object;

Drop a Synonym:
DROP SYNONYM synonym_name;

Working with Views:
SQL> create view sview as
select sname, location from sample
where sno>=2;

Modifying Views using CREATE OR REPLACE clause
SQL> create or replace view sview as
select sname as "State", location as "Name" from sample
where sno <=3;

View created.

DML Operations on a View:
You can usually perform DML operations on simple view
You cannot remove a row if the view contains group functions, GROUP BY clause, DISTINCT keyword,

pseudocolumn ROWNUM keyword
You cannot add a row if the view contains group functions, GROUP BY clause, DISTINCT keyword,

pseudocolumn ROWNUM keyword, NOT NULL columns in the base table that are not selected by the view.

You can ensure that no DML operations are performed on the view by using WITH READ ONLY clause
SQL> create or replace view sview as
select sname, location from sample
where sno<=2
with read only;

View created.

SQL> insert into sview values('MO','test');
insert into sview values('MO','test')
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

Dropping a view:
SQL> drop view sview;

View dropped.


Working with Sequences:
SQL> create sequence test_seq
increment by 2
start with 2
maxvalue 1000;

SQL> insert into test
values (test_seq.nextval, 'test1');

SQL> select test_seq.currval from dual;

CURRVAL
----------
4

Modifying Sequences:
SQL> alter sequence test_seq
2 increment by 10
3 maxvalue 500
4 nocycle;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
14

Dropping a Sequence:
SQL> drop sequence test_seq;

Sequence dropped.

Subqueries in SQL

Single-row Subqueries:

• Subquery returns only one row

SQL> select min(sal) from emp;

MIN(SAL)
----------
800

• Use single-row comparison operators (=, >, >=, <, <=, <>)Example:

SQL> select ename, job, sal from emp
where sal>(select min(sal) from emp);

You will get an error, if you use single-row operator and if your subquery returns more than one row
SQL> select ename, job, sal from emp
where sal>(select min(sal) from emp group by deptno);
where sal>(select min(sal) from emp group by deptno)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Multiple-row Subqueries:

• Subquery returns multiple rows

SQL> select min(sal) from emp group by deptno;

MIN(SAL)
----------
950
800
1300

• Use multiple-row comparison operators (IN, ANY, ALL)

SQL> select ename, job, sal from emp
where sal select ename, job, sal from emp
where sal<=ALL(select min(sal) from emp group by deptno);
no rows selected

ALL: Compare value to every value returned by the subquery

HAVING clause with sub-queries
Subqueries are executed first and returns the results into the HAVING clause of main query
Ex:
SQL> select min(sal) from emp where deptno=20; (subquery)

MIN(SAL)
----------
800

SQL> select deptno, min(sal) from emp group by deptno
2 having min(sal)>(select min(sal) from emp where deptno=20);

DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300

Set Operators in SQL

UNION: Returns results from both queries after eliminating duplications
UNION ALL: Returns results from both queries, including all duplications
INTERSECT: Returns rows that are common to both queries
MINUS: Returns rows in the first query that are not present in the second query

Examples:
SQL> select 'vinay' as "Full Name" from dual
2 union
3 select 'kumar' from dual
4 union
5 select 'kasarapu' from dual;

Full Nam
--------
kasarapu
kumar
vinay

SQL> select deptno from emp
2 intersect
3 select deptno from dept;

DEPTNO
----------
10
20
30

SQL> select deptno from emp
2 union all
3 select deptno from dept;

DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
----------
30
20
10
10
20
30
40

18 rows selected.

SQL> select deptno from dept
2 minus
3 select deptno from emp;

DEPTNO
----------
40

Using TO_CHAR function with Numbers

TO_CHAR(number, 'format_model')

Format model elements:
9: Represents a number
0: Forces a zero to be displayed
$: Places dollar sign
. : Prints a decimal point
, : Prints a comma as thousands indicator

SQL> select to_char(6000, '$99,999.00') from dual;
TO_CHAR(600
-----------
$6,000.00

Using TO_CHAR function with Dates

TO_CHAR(date,'format_model')

format_model must be enclosed within quotation marks and had an 'fm' element to remove padded blanks or suppress leading zeros.

Format model can be any of the following:
YYYY: Full year in numbers
YEAR: Year spelled out in English
MM: Two-digit value for month
MONTH: Full name of the month
MON: Three-letter abbreviation of the month
DY: Three-letter abbreviation of day of the week
DAY: Full name of the day of week
DD: Numeric day of the month

Examples using date format model:
SQL> select to_char(sysdate, 'ddspth MON YYYY') from dual;
TO_CHAR(SYSDATE,'DDSPTH
-----------------------
nineteenth JAN 2009

SQL> select to_char(sysdate, 'dd month year') from dual;
TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
19 january two thousand nine

SQL> select to_char(to_date('01-JAN-2009'), 'dd month year') from dual;
TO_CHAR(TO_DATE('01-JAN-2009')
------------------------------
01 january two thousand nine

SQL> select to_char(to_date('01-JAN-2009'), 'fmdd month year') from dual;
TO_CHAR(TO_DATE('01-JAN-200
---------------------------
1 january two thousand nine

Working with DATE

SYSDATE is a function that returns both DATE and TIME. Default format is DD-MON-YY

SQL> select sysdate from dual;
SYSDATE
---------
14-JAN-09

Date Functions:
MONTHS_BETWEEN: Number of months between two dates
ADD_MONTHS: Add calendar months to date
NEXT_DAY: Next day of the date specified
LAST_DAY: Last day of the month
ROUND: Round date
TRUNC: Truncate date

SQL> select months_between('01-JAN-08','01-MAR-08') from dual;
MONTHS_BETWEEN('01-JAN-08','01-MAR-08')
---------------------------------------
-2

Add 6 months to the date specified:
SQL> select add_months('01-FEB-08',6) from dual;
ADD_MONTH
---------
01-AUG-08

Get next coming tuesday from 14-JAN-09:
SQL> select next_day('14-JAN-09','TUESDAY') from dual;
NEXT_DAY(
---------
20-JAN-09

Retrieve the last day in a particular month:
SQL> select last_day('02-FEB-09') from dual;
LAST_DAY(
---------
28-FEB-09