ORACLE SQL QUERIES FOR DEVELOPERS AND ADMINISTRATORS

ORACLE SQL QUERIES FOR DEVELOPERS AND ADMINISTRATORS

Oracle is a Relational Data Base Management System (R.D.B.M.S).

 

SQL (Sequel): Structure Query Language is defined with following languages-

 

DDL - DATA DEFINITION LANGUAGE

 

Create

Alter

Drop

Truncate

 

DML - DATA MANIPULATION LANGUAGE

 

Insert

Update

Delete

 

DRL - DATA RETRIVAL LANGUAGE

 

Select

 

TCL - TRANSACTION CONTROL LANGUAGE

 

Commit

Roll Back

Save Point

 

 

SCL - SESSION CONTROL LANGUAGE

 

Alter Session

Set Role

 

SYCL - SYSTEM CONROL LANGUAGE

 

Alter System

 

DCL - DATA CONTROL LANGUAGE

 

Grant

Revoke

 

 

 

Sql Prompt : SQL>

 

UserName : Scott

 

Password : Tiger

 

HostString : Oracle

 

To clear the screen : cl scr (This commands comes under SQL *PLUS)

Clear device

Shift + Del + Enter

 

 

Table : It is a basic unit of storage of data or information in the form of rows and columns.

 

Data Types: To enter specific values into specified fields we require data types- Oracle8i data types are –

 

Char Min is 1 Max is 2000 bytes

Varchar2 Min is 1 Max is 4000 bytes

Number(S, P) S->0 to 38 digits P-> -84 to 127 digits

Date 1 Jan 4712 BC ---- 31 Dec 9999AD

Raw Min is 1 Max is 2000bytes

Long Min is 1 Max is 2GB

Long Raw Min is 1 Max is 2GB

LOB’S_____________

|→ CLOB - Character Lob Max is 4GB |

|→ BLOB - Binary Lob Max is 4GB

|→ Bile - Binary File O/S dependent

 

 

UDT’S - User Defined Data Types

Or ADT’S Abstract Data Types

|

|→Objects →Types (These or almost same as Structures in C)

|→Collections

|→VArrays (Arrays and Multi dimensional arrays in C)

|→Nested Tables

 

 

 

Sql Terminator is - ;

Date format is - DD-MON-YYYY

 

To create a table in Oracle:

 

Create Table table_name (column_name1 datatype1, column_name2 datatype2…………);

+ Enter Key

 

Output

Table Created (If Success)

 

In case of error

 

Sql/> Ed

This edits the note pad and we can change the sql statement which was already written and save and exit will display the new sql statement to the sql prompt……To execute the query

Sql/> /

This will execute the previous query with pupations…..

 

Conditions to give a Table Name:

 

1.     First letter should be an alphabet

2.     Max of 30 characters

3.     No space in the middle

4.     No special character allowed except $ and _

5.     Oracle reserved words cannot be used as table names.

 

 

Note: Max Number of columns in a table can be up to 1000.

 

Example:

 

Sql/> Create table emp(eno number(4),ename varchar2(20),Sal number(8,2),dob date);

Table Created.

Sql/>

 

To see all the Tables Name that has been created in a user:

 

Sql/> select * from tab;

This displays all the table names that has been created

 

To desc the table with all data types

 

Sql/> desc table_name;

This describes the complete table with their column names and data types. This commands comes under SQL *PLUS

 

 

 

 

To insert a Record into the table.

 

Except for number data types we have to give single quotes to all values which are to be inserted.

Sql/> Insert into table_name1 values (1001,’Raja’, 6000,’12-sep-1979’);

1 row created.

 

If u want to insert multiple rows---

 

Sql/> Insert into table_name1 values (&no,’&name’, &no,’&date’);

This asks as follows-

Enter the no: “Enter the first column value”

Enter the name: “Enter the second column value”

Enter the no: “Enter the third column value”

Enter the date: “Enter the forth column value”

1 row created.

 

To insert values to only some particular columns

 

Sql/> Insert into emp(eno,ename) values (100,’gita’);

1 row inserted.

 

Again if u want to insert one more record just do…

Sql/> /

 

This do the same as above….to insert the record.

/ - To execute the previous command.

 

 

To display records in the table:

 

Select statement is used to display records in the Table.

Ex:

Sql>Select * from table_name;

 

 

NChar: National Character Set….It supports 32 languages and Oracle is NChar Language.

 

If u want to change the format of Date this can be done as follows-

Sql/> Alter Session set NLS_DATE_FORMAT = ‘DD/MON/YYYY’;

This changes the format of the date

Sql/> column Sal format 99999.99

This changes the format of the salary with two decimal points

 

 

To Delete the records from the table:

 

Sql/> Delete from table_name;

Number of rows deleted

Or

Sql/> Delete table_name;

Number of rows deleted

 

 

To Rename the table

 

Sql/> rename OldName to NewName;

 

To Delete the table

 

Sql/> Drop table table_name;

 

 

To add a column to a table

 

Sql/> Alter table table_name add ( column_name data_type);

 

Sql/> Alter table emp add( Deptno number(4));

 

To modify a particular column data type for a table

 

Sql/> Alter table table_name modify( Column_name data)type);

 

Sql/> Alter table emp modify(ename varchar2(25));

 

Note: To reduce the size of a column or to change the data type of the column that particular column must not have any information.

 

 

To Delete particular column from a table:

 

Sql/> Alter table table_name drop column_name;

 

Sql/> Alter table table_name drop ( column_name1, column_name2….);

 

( To delete more than one particular column give directly the names with out any specification of the column and we cannot drop all the columns in a table at least one column must be present).

 

 

 

Sql/> select * from emp;

This display all the records from the table emp;

Sql/> select eno,ename from emp;

This display all the records ( only two columns) from the table emp;

 

Sql/>Select *from emp where sal>6000;

This display all the records whose sal is greater than 6000.

 

Relational Operators:

 

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> not equal to (!=)

= equal to

 

 

Logical Operators:

 

AND

OR

BETWEEN…………AND

 

 

Sql/> Select *from emp where sal>6000 and deptno=10;

This display all the records in the emp table whose salary is greater than 6000 and he belongs to deptno 10.

 

Sql/> Select * from emp where sal>=3000 and sal<=9000;

This display all the records whose sal is between 3000 and 9000. This can also be written as

Sql/> Select * from emp where sal between 3000 and 9000;

 

 

Sql/>Select * from emp where deptno=10 or deptno=20;

This display all the records who belong to deptno 10 or 20.

 

Sql/>Select *from emp where deptno in(10,20,30);

This display all the records who belong to deptno 10,20,30.

 

Sql/>Select * from emp where deptno no in (10,20,30);

This display all the records who does not belong to deptno 10,20,30.

 

 

 

Sql/> Select * from emp where sal not between 300 and 2500;

This display all the records who sal is not in between 300 and 2500.

 

Sql/> Select *from emp where ename=’chaitanya’;

This display the record with the ename chaitanya.

 

Note : “Where” retrieve the information …Oracle is Case Sensitive.

 

 

Sql/> Select *from emp where ename like’chaitanya’;

 

 

Like is used for exact comparisons.

 

Char is used only for fixed length field and downward compatibility where as varchar2 is variant .

 

% - This symbol is used for wild character in oracle

_ - This symbol is also used as wild character in oracle.

 

Sql/> select * from emp where ename like ‘C%’;

To display all the records starting with C in their ename.

 

Sql/> Select * from emp where ename like ‘C%A’;

To display all the records starting with C and ending with A in their ename.

 

Sql/> Select *from emp where ename like ‘C__A’;

To display all the records starting with C and ending with A and in between only two characters in their ename.

 

Sql/>Select * from emp where ename not like’C%’;

To display all the records that are not starting with C in their ename.

 

Some of the commands that can be used are

 

Not in

Not between

Not like

 

 

Sql/>Delete from emp where sal=6000;

This deletes all the record with sal 6000.

 

*** Note: All the conditions applied for select are applicable for delete and update also.

 

Sql/> update emp set sal=6000;

This update all the records with sal as 6000.

 

Sql/> update emp set sal=sal+100;

This update all the records with increment of 100 in their sal;

 

Sql/>update emp set sal=sal+100 where sal>6000;

This updates all the records whose Sal is greater than 6000 with an increment of 100 in their Sal.

 

To display all the records whose Sal is NULL.

 

Sql/> Select * from emp where sal is NULL;

 

 

COMMIT, ROLLBACK, AND SAVEPOINT :

 

These are transaction control language

 

Sql> Commit;

To save up to date commands,

Note:

All DDL commands are auto command.

(Create, Alter, Drop, Truncate, Grant, Revoke)

 

Sql>Rollback;

it undo to the last previous commit

 

Commit;

 

sql> s->15;

sql> insert 2 rec;

sql> Save point A;

sql> delete 3 rec

sql>Save point B;

sql>insert 4 rec;

sql>save point c;

sql>del 3 rec;

sql>s->16 rec;

sql rollback to save point b;

sql>s->14;

sql>roll back to save point c (Error)

 

Note: When commit command is given all save point before given will be flushed.

 

GRANT and REVOKE:

 

These are used to grant or revoke privileges

 

 

GRANT:

 

sql>Grant all on emp to ov9292

 

Privileges:

 

-Insert

-Update

-Delete

-Select

-Alter

-Index

-Reference

 

Sql> Grant select, insert on emp to ov9292

to give some privileges

 

Sql> Grant all on emp to ov9292, ov9393;

 

Sql> Grant all on emp to public;

Privilege given not only to existing but also to the future login name also.

 

Sql> Grant all on emp to ov9292 with Grant option

 

Sql> Select * from ov9165.emp;

Sql> Insert into ov9165.emp values();

sql>Grant all on ov9165.Emp to ov9798

sql>Select * from ov9265.emp;

 

REVOKE:

 

Sql> Revoke all on emp from ov9292;

Sql> Revoke delete on emp from ov9292;

 

Sql> Select * from USER_TAB_PRIVS_MADE

To see whom you have given the privileges..

 

 

Sql>Select * from USER_TAB_PRIVS_RECD

To see the PRIVILEGES GRANTED TO YOU

 

TRUNCATE:

 

Sql>Truncate table emp;

 

Here, all the information is lost and table is empty but it will not delete.

 

 

Duplicating a Table:

 

Sql> Create table employee as select * from emp;

-It Creates table Employee with the same structure and information as emp these tables are known as pseudo table.

 

Sql> Create table employee as select * from emp where 1=2;

It create a table employee with same structure as emp table with out any information.

 

Note: Here any false condition can be given.

 

To Change name of column:

 

Sql> Create table employee (Eno,Ename,Sal) as select empno,ename,sal from emp;

 

Sql> Drop table emp;

Sql>Rename Employee to emp;

 

Sql> Desc Emp

 

Output:

Eno

Ename

Sal

 

Sql> Select Distinct (sal) from emp;

 

Note: We can pass only one parameter for distinct Command

 

Sql> Select * from emp order by sal

 

-Sorting in default Ascending Order

 

Sql> Select * from emp order by sal desc;

 

-Sorting in Descending order.

 

Sql> Select * from emp order by sal, Empno;

 

-First sort will be done on sal, If two sal are equal then sorting will be done by empno.

 

Sql> Select * from emp order by sal, empno desc;

 

Sal – Ascending order

Empo – Descending order

 

Sql> Select * from emp order by sal Desc, Empno desc;

 

Sal – Descending order

Empno – Descending order

 

Sql> Select * from emp order by sal desc, empno;

 

Sal – Descending order

Empno – Ascending order

 

 

Functions:

 

Single Row Functions:

 

●       Char

●       Date

●       Numeric

●       Conversions(conv)

●       Misc

 

Group Functions

 

CHARACTER FUNCTIONS:

 

Sql> Select upper (‘New’) from dual;

-Displays given string in Upper Case (NEW)

 

Sql> Select upper(ename) from emp;

-Displays all enames of emp in upper case.

 

Sql> Select lower (‘Test) from dual;

-Displays given string in Lower Case (test)

 

Sql> Select initcap(‘ORACLE’) from dual;

-Displays given String with Initial Caps (Oracle)

Sql> select lpad(‘Oracle’,8,’*’) from dual;

 

Lpad

**Oracle

 

Sql> select rpad(‘Oracle’,8,’*’) from dual;

 

Rpad

Oracle**

 

Sql> Select Ascii(‘A’) from dual;

 

- Displays Ascii Value of Character ‘A’ (American Standard Code for Information Interchange)

 

Sql> Select chr(65) from dual;

 

- Displays Character value for given Ascii number

 

Sql> Select replace (‘Jack and Jill’,’J’,’B’) from dual;

 

- Replace the given character with the new character

Back and Bill

 

 

SQL> SELECT REPLACE('JACK AND JILL','JA','BC') FROM DUAL;

 

REPLACE('JACK

-----------------------

BCCK AND JILL

 

SQL> SELECT TRANSLATE('JACK AND JILL','J','B') FROM DUAL;

 

TRANSLATE('JA

----------------------

BACK AND BILL

 

SQL> SELECT TRANSLATE('JACK AND JILL','JA','BC') FROM DUAL;

 

TRANSLATE('JA

----------------------

BCCK CND BILL

 

 

 

 

 

SQL> SELECT LENGTH('JACK AND JILL') FROM DUAL;

 

-Gives the length of the given String.

 

LENGTH('JACKANDJILL')

----------------------------------

            13

 

SQL> SELECT REVERSE('RAMA') FROM DUAL;

 

REVE

---------

AMAR

 

SQL> SELECT SUBSTR('RAMARAO',3,4) FROM DUAL;

 

SUBS

----------

MARA

 

SQL> SELECT SUBSTR('VENKATA RAO',3) FROM DUAL;

 

SUBSTR

-------------

NKATA RAO

 

SQL> SELECT SUBSTR('VENKATA RAO',-3,3) FROM DUAL;

 

SUB

------

RAO

 

SQL> SELECT INSTR('RAMA KRISHNA PRASAD','A',3,2) FROM DUAL;

 

-Returns the position of the given Character in the given String.

 

INSTR('RAMAKRISHNAPRASAD','A',3,2)

--------------------------------------------------------

12

 

SQL> SELECT INSTR('RAMAKRISHNA PRASAD','A',3,2) FROM DUAL;

 

INSTR('RAMAKRISHNAPRASAD','A',3,2)

---------------------------------------------------------

           11

 

 

 

SQL> SELECT LTRIM('VENKATA RAO','V') FROM DUAL;

 

LTRIM

-----------

ENKATA RAO

 

SQL> SELECT LTRIM('VENKATA RAO','VE') FROM DUAL;

 

LTRIM

-----------

NKATA RAO

 

SQL> SELECT LTRIM('RAJARAO','ARJ') FROM DUAL;

 

L

-

O

 

SQL> SELECT RTRIM('RAJA RAO','O') FROM DUAL;

 

RTRIM

------------

RAJA RA

 

DATE FUNCTIONS:

 

SQL> SELECT SYSDATE FROM DUAL;

 

-Returns Current System Date.

 

SYSDATE

---------------

09-DEC-05

 

SQL> SELECT SYSDATE+3 FROM DUAL;

 

SYSDATE+3

------------------

12-DEC-05

 

SQL> SELECT SYSDATE-3 FROM DUAL;

 

SYSDATE-3

-----------------

06-DEC-05

 

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

 

LAST_DAY

----------------

31-DEC-05

 

SQL> SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;

 

ADD_MONTH

--------------------

09-FEB-06

 

SQL> SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;

 

ADD_MONTH

-------------------

09-OCT-05

 

SQL> SELECT NEXT_DAY(SYSDATE,'FRI') FROM DUAL;

 

NEXT_DAY

----------------

16-DEC-05

 

SQL> SELECT NEXT_DAY(SYSDATE,'SUN') FROM DUAL;

 

NEXT_DAY

-----------------

11-DEC-05

 

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'5-MAR-2005') FROM DUAL;

 

MONTHS_BETWEEN(SYSDATE,'5-MAR-2005')

-----------------------------------------------------------------

           9.1547805

 

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'5-MAR-2006') FROM DUAL;

 

MONTHS_BETWEEN(SYSDATE,'5-MAR-2006')

----------------------------------------------------------------

-2.845209

 

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'09-JAN-2006') FROM DUAL;

 

MONTHS_BETWEEN(SYSDATE,'09-JAN-2006')

-----------------------------------------------------------------

            -1

 

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'09-NOV-2005') FROM DUAL;

 

MONTHS_BETWEEN(SYSDATE,'09-NOV-2005')

------------------------------------------------------------------

           1

 

NUMERICAL FUNCTIONS:

 

SQL> SELECT SQRT(64) FROM DUAL;

 

SQRT(64)

---------

8

 

SQL> SELECT MOD(7,2) FROM DUAL;

 

MOD(7,2)

-------------

1

 

SQL> SELECT POWER(3,2) FROM DUAL;

 

POWER(3,2)

-----------------

9

 

SQL> SELECT SIGN(123) FROM DUAL;

 

SIGN(123)

---------------

1

 

 

 

SQL> SELECT SIGN(-123) FROM DUAL;

 

SIGN(-123)

---------------

 -1

 

SQL> SELECT LOG(10,100) FROM DUAL;

 

-Returns the value of Log ₁₀100

 

LOG(10,100)

-----------------

 2

SQL> SELECT LOG(10,2) FROM DUAL;

 

-Returns the value of Log₁₀ 2

LOG(10,2)

--------------

.30103

 

SQL> SELECT EXP(2) FROM DUAL;

 

- Returns the value of e²

 

EXP(2)

---------

7.3890561

 

SQL> SELECT LN(2) FROM DUAL;

 

●       Returns the value of Log_e 2

●        

LN(2)

------------

.69314718

 

SQL> SELECT SIN(45) FROM DUAL;

 

SIN(45)

-----------

.85090352

 

SQL> SELECT COS(45) FROM DUAL;

 

COS(45)

-------------

.52532199

 

SQL> SELECT TAN(45) FROM DUAL;

 

TAN(45)

-------------

1.6197752

 

SQL> SELECT SINH(45) FROM DUAL;

 

SINH(45)

-------------

1.747E+19

 

SQL> SELECT COSH(45) FROM DUAL;

 

COSH(45)

--------------

1.747E+19

 

SQL> SELECT TANH(45) FROM DUAL;

 

TANH(45)

--------------

1

 

SQL> SELECT CEIL(123.45) FROM DUAL;

 

CEIL(123.45)

------------------

124

 

SQL> SELECT FLOOR(123.45) FROM DUAL;

 

FLOOR(123.45)

---------------------

123

 

CONVERSIONS:

 

SQL> SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;

 

TO

----

09

 

SQL> SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL;

 

TO_

-------

DEC

 

SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY BC') FROM DUAL;

 

TO_CHAR(SYSDA

---------------------------

09-12-2005 AD

 

 

SQL> SELECT TO_CHAR(SYSDATE,'HH') FROM DUAL;

 

TO

----

07

 

SQL> SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;

 

TO

----

19

 

SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL;

 

TO_CHAR(SYS

-----------------

19:19:53 PM

 

 

SQL> SELECT * FROM SCOTT.EMP;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

 

14 rows selected.

 

SQL> SELECT ROUND(101,-2) FROM DUAL;

 

ROUND(101,-2)

----------------------

100

 

SQL> SELECT TRUNC(26.12,-1) FROM DUAL;

 

TRUNC(26.12,-1)

-------------------------

20

 

SQL> SELECT ROUND(26.52,-1) FROM DUAL;

 

ROUND(26.52,-1)

-------------------------

30

 

SQL> SELECT ROUND(26.27,-1) FROM DUAL;

 

ROUND(26.27,-1)

---------------

30

 

MISC FUNCTIONS:

 

 

SQL> SELECT USER FROM DUAL;

 

USER

------------------------------

CHAITANYA

 

SQL> SHOW USER

USER is "CHAITANYA"

 

SQL> SELECT UID FROM DUAL;

 

UID

---------

37

 

SQL> SELECT * FROM ALL_USERS;

 

USERNAME USER_ID CREATED

------------------------------ --------- ---------

SYS 0 01-MAR-99

SYSTEM 5 01-MAR-99

OUTLN 11 01-MAR-99

DBSNMP 20 01-MAR-99

MTSSYS 28 01-MAR-99

AURORA$ORB$UNAUTHENTICATED 25 01-MAR-99

SCOTT 26 01-MAR-99

DEMO 27 01-MAR-99

ORDSYS 30 01-MAR-99

ORDPLUGINS 31 01-MAR-99

MDSYS 32 01-MAR-99

CTXSYS 35 01-MAR-99

PRASAD 37 08-MAR-05

 

13 rows selected.

 

SQL> SET PAUSE ON

 

SQL> SELECT VSIZE('JIM CARRY') FROM DUAL;

 

        9

 

 

SQL> SELECT NVL(COMM,200) FROM SCOTT.EMP;

 

NVL(COMM,200)

-------------

200

300

500

200

1400

200

200

200

200

0

200

200

200

200

 

14 rows selected.

 

 

SQL> SELECT MAX(SAL) FROM EMP;

 

-Returns the Maximum value from that column.

 

●       MIN() -- Returns Minimum Value

●       AVG() -- Average

●       SUM() -- Sum

●       STDDEV() -- Standard Deviation

●       VARIANCE() – Variance

 

SQL> SELECT COUNT(*) FROM EMP;

 

●       This will display the number of values.

 

COUNT(*)

-------------

            8

 

SQL>SELECT COUNT(SAL) FROM EMP;

 

COUNT(SAL)

----------

15

 

SQL>SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;

 

DEPTNO MAX(SAL)

--------- ------------------

10 50000

20 3000

30 2850

 

SQL>SELECT DEPTNO,COUNT(*),MAX(SAL) FROM EMP GROUP BY DEPTNO;

 

DEPTNO COUNT(*) MAX(SAL)

--------- --------- -----------------------

10 4 50000

20 5 3000

30 6 2850

 

SQL>SELECT DEPTNO, MAX(SAL), COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>5;

 

DEPTNO MAX(SAL) COUNT(*)

--------- --------- ----------------------

30 2850 6

 

SET OPERATIONS:

 

●       UNION

●       UNION ALL

●       INTERSECT

●       MINUS

 

 

UNION: It gives all the values from both queries with out duplicates.

 

SQL> SELECT EMPNO FROM EMP1

UNION

SELECT EMPNO FROM EMP2;

 

UNION ALL: It gives all the values from both queries including duplicates.

 

SQL> SELECT EMPNO FROM EMP1

UNION ALL

SELECT EMPNO FROM EMP2

 

INTERSECT: It gives the values which are common from both the queries.

 

SQL> SELECT EMPNO FROM EMP1

UNION

SELECT EMPNO FROM EMP2;

 

MINUS: It gives all the values from first query except common values in the second query

 

SQL> SELECT EMPNO FROM EMP1

MINUS

SELECT EMPNO FROM EMP2;

 

JOINS:

 

SIMPLE JOINS

-EQUI JOINS

-NON EQUI JOINS

 

OUTER JOINS

 

SELF JOINS

 

SIMPLE JOIN:

 

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME

FROM EMP, DEPT;

The symbol ‘=’ is known as Equi Join Other than this symbol (>, <, <>, ect) are known as Non Equi Join

 

OUTER JOIN:

 

This is used to retrieve information from both matching records and non matching records.

 

SQL> SELECT EMP.EMPNO, EMP.ENAME, EMP.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO (+);

 

SELF JOIN:

 

The tables which pointed to it self are known as Self Joined.

 

SQL> SELECT A.EMPNO, A.SAL, B.EMPNO, B.SAL FROM EMP A,

EMP B WHERE A.EMPNO=B.EMPNO AND A.SAL>B.SAL;

 

 

SUB-QUERIES:

 

Two queries combined to form sub queries

 

SQL> SELECT * FROM EMP WHERE SAL= (SELECT MAX (SAL) FROM EMP);

 

To find the Second maximum salary:

 

SQL> SELECT MAX(SAL) FROM EMP WHERE

SAL<(SELECT MAX(SAL) FROM EMP);

 

Nested Sub-Queries:

 

SQL> SELECT * FROM EMP WHERE

SAL=(SELECT MAX(SAL) FROM EMP WHERE

SAL<(SELECT MAX(SAL) FROM EMP));

 

Note:

1.     If the number of ‘Select’ statements are one then it is a query

2.     If the number of ‘Select’ statements are two it is called sub query.

3.     If the number of ‘Select’ statements are more than two then it is called a nested sub query.

4.     The maximum number of ‘Select’ statements in a nested sub query are 255.

 

Co-Related Sub Query:

 

The number of times the parent query executes that number of times the child query also executes known as correlated sub query.

To display the information of employee who are earning more than average sal of the respective departments.

 

SQL> SELECT * FROM EMP A

WHERE SAL>(SELECT AVG(SAL) FROM EMP

WHERE DEPTNO=A.DEPTNO);

 

 

CONSTRAINTS:

 

Constraints are some conditions to be imposed on a table.

 

DOMAIN INTEGRITY CONSTRAINTS

●       NOT NULL

●       CHECK

 

ENTITY INTEGRITY CONSTRAINTS

●       UNIQUE

●       PRIMARY KEY

 

REFERENCIAL INTEGRITY CONSTRAINTS

●       FOREIGN KEY (Or) REFERENCIAL

 

A constraints can be given on a table is two levels

 

1.     Column Level

2.     Table Level

 

Column Level:

 

If we want to given single constraint for single column we have to go for column level.

 

Table Level:

 

If we want to give single constraint for one column or more than one column we have to go for table level.

 

NOT NULL:

 

SQL> Create table emp

( Empno number(5) constraint nay not null,

Ename varchar2(15),

Sal number(14,2));

 

 

 

SQL> Create table emp

(Empno number (5) Not Null,

Ename varchar2 (15),

Sal number (14, 2));

 

Note: A NOT NULL Constraint cannot be given in table level.

 

SQL> Alter table emp modify (Ename Varchar2 (20) Constraint Nk3 Not Null);

 

The Existing column must not have any null value (Ename) for above Example.

 

UNIQUE CONSTRAINT (Column Level):

 

It doesn’t allow you to enter any duplicates.

 

Sql> Create Table Emp

( Empno Number(4) Constraint Uk1 UNIQUE,

Ename Varchar2 (30),

Sal Number (14, 2));

 

Sql> Create Table Emp

( Empno Number(4) Constraint Uk1 UNIQUE,

Ename Varchar2 (20) Constraint Uk2 UNIQUE,

Sal Number (14,2));

 

●       It will check individually

 

UNIQUE CONSTRAINT (Table Level):

 

1) Sql> Create Table Emp

(Empno Number (4),

Ename Varchar(20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno));

 

- In above example there is a Unique Constraint defined on the field Empno. i.e, No duplication is allowed on this field.(Only one constraint is defined)

 

 

2) Sql> Create Table Emp

( Empno Number(4),

Ename Varchar2 (20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno),

Constraint Uk4 Unique (Ename));

- In the above example there are two Unique Constraint Defined on the fields Empno and Ename. i.e. We cannot duplicate the values of both the fields.

 

 

3) Sql> Create Table Emp

(Empno Number (4),

Ename Varchar2 (20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno, Ename));

 

●       In above example there is a single unique constraint defined on both the fields (i.e. Empno and Ename). This is also known as Composite Unique Constraint means together they cannot be duplicated.

 

 

COMPOSITE UNIQUE CONSTRAINT:

 

The maximum number of columns you can specify in a COMPOSITE UNIQUE CONSTRAINT is 32 in Oracle 8 and 16 in Oracle7.

 

By default every column has null Constraint so it can be used in table level.

 

Sql> Alter table Emp Add Constraint Uk3 Unique (Empno);

 

Note: In above example the existing column must not have any duplicate values.

 

CHECK CONSTRAINT (Column Level):

 

Sql> Create Table Emp

(Empno Number (4),

Ename Varchar2 (20),

Comm Number (8, 2),

Sal Number (14, 2) Constraint Ck Check(Sal>1500));

 

- In the above example there is a Check Constraint defined on the ‘Sal’ field to check that it is grater than 1500.

 

CHECK CONSTRAINT (Table Level):

 

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Sal Number (14, 2),

Constraint Ck Check (Sal>1500));

●       This is similar to above Example.

 

Sql> Create table emp

(Empno Number (4),

Ename Varchar2 (20),

Comm Number (8,2),

Sal Number (14, 2),

Constraint Ck Check (Sal > Comm));

 

- In above example there is a Check condition Sal>Comm it can only be possible in table level but, not in the column level. (i.e. Comparison between two columns).

 

Sql> Alter Table Emp Add Constraint Ck3 Check (Sal between 3000 and 9000);

 

 

PRIMARY KEY CONSTRAINT:

 

It is a combination of Unique and Not Null Constraint.

 

Column Level:

 

Sql> Create Table Dept

(Deptno Number (4) Constraint Pk Primary Key,

Dname Varchar2 (20),

Loc Varchar2 (30));

 

Note: A Table can have only one PRIMARY KEY Constraint

 

Table Level:

 

Sql> Create Table Dept

(Deptno Number (4),

Dname Varchar2 (20),

Loc Varchar2 (30),

Constraint Pk1 Primary Key (Deptno));

 

Sql> Create Table Dept

(Deptno Number (4),

Dname Varchar2 (30),

Loc Varcha2 (30),

Constraint Pk2 Primary Key (Deptno,Dname));

 

Note: This is known as Composite Primary Key Constraint. In this case it check for the uniqueness in the combined columns and Not Null for individual Columns.

 

1.     A table can have only one primary key constraint

2.     We can give a simple primary key constraint for more than one column then it is called as composite primary key constraint.

3.     The maximum number of columns you can specify in a composite primary constraint are

1.     Oracle 8 – 32

2.     Oracle 7 – 16.

 

Sql> Alter table dept Add Constraint Pk2 Primary Key(Deptno);

 

Note:  The existing must not have null or duplicate values.

 

 

REFERENTIAL INTEGRITY:

 

This is used to provide link between Master and Child Tables

 

 

Parent Table(Master) Child Table

 

 

DEPT

Deptno

DName

Loc

 

EMP

Empno

Ename

Deptno

 

 

<- Parent Key / Primary Key (Reference)

 

Child Key / Foreign Key ->

 

 

Note: The parent key column in the parent table should have either a primary key constraint or a unique constraint.

 

For Example: In the department table the deptno column should have either a primary key constraint or unique constraint.

 

Column Level:

 

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (2),

Constraint FK references Dept (Deptno));

 

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (2) Constraint Fk1 References Dept (Deptno) on delete Cascade);

 

Note: In the above example if the master table row is deleted then all child table rows corresponding references to that row will be deleted by using “On Deleted Cascade”.

i.e. The use of “On Delete Cascade” is if we delete a master record the corresponding child records automatically deleted.

 

 

Table Level:

 

Sql> Create table emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (3),

Constraint Fk1 Foreign Key (Deptno) References Dept (Deptno));

 

Sql> Alter table emp add constraint Fk foreign key (Deptno) references Dept (Deptno);

 

Note: What ever the value of deptno in emp table, it should be there in deptno of dept table.

 

Sql> Create table emp

(Empno Number (4) constraint Nk Not Null initially deferred deferrable);

 

If we place ‘INITIALLY DEFERRED DEFERRABLE’ in the query then it will not check every time, the constraints only check at commit. If the entry is wrong it automatically rollback.

 

The following Queries are used to see the Constraints:

 

SQL>select constraint_name, constraint_type from user_constraints;

 

CONSTRAINT_NAME C

------------------------------ -

PK_DEPT P

PK_EMP P

FK_DEPTNO R

SYS_C001257 P

 

Sql> Select * from user_cons_columns;

 

Note: If column violates the constraint we can enforce the constraint as follows.

 

Sql> Alter table EMP enforce constraint <Constraint-name>;

 

Sql> Alter table EMP modify Constraint PK disable/enable/drop;

 

Sql> Alter table EMP Disable / Enable / Drop Constraint PK;

 

Disable: It will disable the constraint

Enable: It will again invokes the constraint.

Drop: Completely deletes the constraint.

 

DATA BASE OBJECTS:

 

Any object which is stored in data base either directly or indirectly is called as data base object.

 

View: (An imaginary object)

A view doe’s not occupy any table space.

 

1.     Sql>Create view ABC as Select * from Emp;

2.     Sql> Create view ABC as select eno, ename from emp;

3.     Sql> Creat view ABC as select * from emp where deptno=10;

4.     Sql> Create view ABC as select * from emp where deptno=10 with check option;

5.     Sql> Create view ABC as select emp.empno,emp.ename,dept.dno,dept.dname from emp,dept where emp.dno=dept.dno;

 

Note: If a view is based on joins or set operators or group function those type of views are neither insertable nor updatable nor deletable.(DMlL commands will not work)

 

6.     Sql> Create view ABC (Dno,msal) as (Select Deptno, ,max(Sal) from emp group by deptno);

7.     Sql> Create view ABC as select * from emp with read only;

 

Force View:

 

Sql> Create force view ABC as select * from custom;

 

Sql> create table custom(Custid number(4), custname varchar2(20));

 

To work the view:

 

Sql> Alter view ABC compile; (Before 7.1)

 

SYNONYM: (Alias Name)

 

Owner name is masked

- Private Synonym (User can create)

- Public Synonym (DBA)

 

Sql> Create Synonym KLM for Emp;

 

Note: We must use user name

 

Sql> Create public synonym KLM for emp;

 

If we grant table KML to any user he can directly use the table as “select * from KML;”

 

INDEX:

 

Sql> Create index ind on emp (Empno);

Sql> Select * from emp where empno=7900;

 

It will take less time to execute where you create index. Maximum of 5 index will give better performance.

 

Sql> Create index Ind on emp(Empno,Ename);

Maximum of 32 fields can be indexed

 

Sql> Create index ind on emp(Empno) local;

Where if the table is created based on partition. it will create the same values to index.

 

Local Partition Index:

 

Sql> Create index ind on emp(Empno) global partition by range(Empno)

(Partition s1 values less than (2000),

Partition s2 values less than (5000),

Partition s3 values less than (Maxvalue));

 

Global is a key word which is used if the table is not a partition table and if we want to create index table as partition.

 

Global Partition Index:

 

Note: If the table is partitioned on two columns and if the index is given on first column it is called as prefixed partition index.

 

 

SEQUENCE:

 

Create sequence

Start with 1

Increment by 2

Maxvalue 20

Cycle

Cache 5;

 

Sql> Select seq.nextval from dual;

 

Sql> Select seq.currval from dual.

 

Sysdate, nextval, currval from pseudo columns.

 

Sql> Insert into cust values(Seq.nextval, ‘&Con’);

 

Number is taken from the sequence in this case.

 

Sql> Select view_name from user_views;

 

Sql> Select synonym_name from user_synonyms;

Sql> Select index_name from user_indexes;

Sql> Select Sequence_name from user_sequences;

 

 

Sql> Drop Sequence <Seq_name>;

Sql> Drop Index <Ind>;

Sql> Drop View <aol>;

Sql> Drop Synonym <syn>;

 

 

Index Organised Tables:

 

Sql> Create table emp(Empno number(4) primary key) organization index;

 

1.     These types of table’s doesnot have rowid.

2.     Information retrieval is faster compared to ordinary tables.

3.     This type of table should have primary key constraints.

 

User Defined Types:

 

UDT’s (Abstract Data Types (ADT’s))

 

- Objects -> Types

- Collections

-> Varrays

-> Nested Tables

 

Types:

A type does not occupy any table space. Type is similar to structure in C-Language.

 

Sql> Create type dept_ty as object (Deptno Number(2), Dname varchar2(30))

 

Sql> Create table Employee(Empno number(4), ename varchar2(30), dept_info dept_ty);

 

Sql> Insert into emp values(1,’Abc’,Dept_ty(20,’Acc’));

 

Sql> Select * from emp;

Sql> Select a.dept_info.deptno from Employee A;

 

Sql> Select eno, a.dept_info.deptno, a.dept_info.dname from emp a;

 

To See the types created:

 

Sql> Select type_name from user_types;

 

Note:

1.     The columns in the types are called attributes.

2.     These can be any no. of attributes in a single type.

3.     Table dependent types cannot be dropped.

 

Sql> Drop type dept_ty;

 

Sql> Drop type Dept_ty force;

 

The columns of the table related to dept_ty also deleted and it will not create again even the type is again created.

 

 

Varrays: (Varying Arrays)

 

Set of elements of similar data types is called as Varray.

 

Sql> Create type books_var as varray(5) of varchar2(30);

 

Sql> Create table STUD (Studno number(4), Books Books_Varr);

 

Sql> Insert into Stud values(1001,books_varr(‘c’,’c++’,’java’,’vb’,’.net’));

 

Sql> select * from stud;

 

Updating is also possible.

 

Nested Tables:

 

A table inside a table is called nested table.

 

Sql> Create type books_ty as object (Bookno number(4), Author varchar2(20));

 

Sql> Create type books_nt as table of books_ty;

 

Sql> Create table student(Studno number(4), Books Books_nt)

Nested table books

Store as books_tab;

Sql> Insert into student values (100,books_nt(books_ty(101,’jack’),books_ty(102,’jill’),books_ty(103,’John’));

 

Sql> Select * from student;

 

 

Sql> Select * from the(select books from student where studno=1001); (Flattened Queries)

Sql> Insert into the (Select books from student where studno=1001) values(104,’jim’);

 

Sql> Update the (Select books from student where studno=1001) set author_name=’Johmmy’ where bookno=102;

 

Sql> Delete from the(Select books from student where studno=1001) where bookno=104;

 

 

Object Views:

 

1.     Create type sal_ty as object (sal number(4), comm. Number(4));

2.     Create view ABC(Empno,Salinfo) as (select empno,sal_ty(sal,comm.) from emp);

 

Object Tables:

 

Sql> Create type dept_ty as object (Deptno number(4), Dname varchar2(20));

 

Sql> Create table dept of dept_ty;

 

Sql> Desc dept

 

Sql> Insert into dept values(10,’all’);

 

Sql> Select * from dept;

 

Sql> Select ref(A) from dept A;

-Address of the record (entered as pointer in c).

 

Sql> Create table emp(Empno number(4), ename varchar2(30), dept_info ref dept_ty);

 

Sql> Insert into Emp Select 1001,’Rani’,ref(A) from Dept A where deptno=10;

 

Sql> Select deref(A deptinfo) from emp A;

 

 

 

 

SQL *PLUS

 

Sql> Set numwidth 4 // To change the number width

Set feedback off/on //To display number of rows

Set space 3 // To leave 3 spaces between columns

Set Underline *

Set heading off/on

Set pagesize 18

Set sqlterminator *

Set define #

Set verify off/on

Set Sqlprompt OracleSql>

Set autocommit on/off

Set editfile sqlOracle

Set Time on

Set timing on/off This depends on server spec

SetTtitle ‘Oracle Corp.’ [on/off]

Set Btitle ‘End of Report’ [on/off]

 

 

THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ON BLOG https://chaitanyaoracledba.blogspot.com/

 

 

 

 

ORACLE DATAGUARD

Oracle Dataguard - DR Disaster Server:

-------------------------------------------------

           Oracle Data guard ensures high availabilty data protection,and disaster recovery for enterprise data,datagurad provides a comprehensive set of services that create,maintain,manage and monitor one or more stand by databases to enable production oracle databases to survive disaster and data corruptions,datgurad physical standbysetup using the dataguard broker in oracle database 12c release 1 Dataguard is the name for oracle standby database solution,used for disaster recovery and high  availability

    LNS process of primary database  captures redo from redolog buffer  send it to RFS .RFS process of stand by database through ORACLE NET ,RFS process then writes that redo information to standby redolog files,MRP applies information from the archived redologs to the standby database,when performing managed receovery operations,log apply services automatically apply archived redologs to mainatained transactional synchronization with primary database  

  

Primary system :

-------------------

configure standby server up to 30 for a single primary

standby replica of primary

       DDL / DML changes in primary will replicate to standby

Features - standby :

-----------------------

called as dataguard from 9i

prior to 9i , called as standby system

media failure/disk failure/power/disaster - 

purpose is to protect primary database

Two types:

-------------

Physical Standby - using redo apply with archives are shipped

Logical Standby - using sql apply

11g introduced - snapshot standby

         Standalone system

        Primary Database

        For Disaster Purpose

        any media failure

       For that we need a standby setup for Primary

       The standby environment is same has Primary.

    Using Primary Archives - shipped to Standby Server.

     In Standby - RFS will receive and MRP will apply the shipped archives.

RFS - Remote file server process

MRP - Managed recovery Process.

In Primary , LNS wil send the changes made to standby.

        LNS ---- >Log network service

From 9i

Primary -> LNS -> Ship -> standby ->RFS receive -> MRP apply

--------------------------------------------------

Prior to Oracle 9i - we called as standby

From 9i - its Dataguard

In 8i , a DBA intervention is required to manage archive shipping from primary to standby by using a crontab script

 with a SCP Command.

From 9i , the LNS service will ship the archives.The gap automatically resolved.

-------------------------------------------------------------------------------------------------

Dataguard license is only availiable with

        Enterprise Edition/SE2

    But not with standard edition

Standby Database Types :

---------------------- --------- 

    Physical Standby - using redo apply

    Logical Standby - using sql apply

Physical Standby ---> Using archives (a copy of block) is going

to apply in standby mount stage - Media recovery mode

Logical Standby -- >using sql statements

    degrades the performance of primary.

    Not recommended.

A logical standby database works in a different manner which keeps in sync with the primary by transforming redo data received from the primary database into logical SQL statements and then executes those SQL statements against the standby database.

With a logical standby database, the standby remains open for user access in read/write mode while still receiving and applying logical records from the primary.

---------------------------------

Dataguard Setup - Physical Standby

---------------

Primary Configuration - Min down time is needed to configure.

------------------------------------------------------------------------------

1. Enable Archivelog

sql>archive log list

2. Create Password file - OS

    cd $ORACLE_HOME/dbs

    ls -ltr

    $ mv orapwprod orapwprod_old

An encrypted sys password stored in OS  Password file.

    $orapwd file=orapwprod password=sys123

For standby DB - Copy paswd file (must be same)

    $cp orapwprod  orapwstan

3. Enable Forced Logging

In case, developer enable table nologging for faster inserts/updates of bulk changes. with this no redo will generate on that table. For that we are missing changes in archives. So will enable globally force logging, so those tables are logged forcebly.

    sql>ALTER DATABASE FORCE LOGGING;

    SQL> select force_logging from v$database;

Parameter file configuration - Primary

---------------------------------------

Lets add few parameters

sql>create pfile from spfile;

sql>shutdown immediate

sql>exit

cd $ORACLE_HOME/dbs

vi initprod.ora

#append the parameters - following

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stan)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

*.LOG_ARCHIVE_DEST_2='SERVICE=stan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.db_unique_name='prod'

#remove existing log_archive_dest_1

Save it

start the DB with pfile

sqlplus '/as sysdba'

sql>startup pfile='$ORACLE_HOME/dbs/initprod.ora'

sql>create spfile from pfile;

sql>shutdown immediate

sql>startup

Q)Why nologging option is needed in DG config for primary ?

A) nologging option on those tables - they are inserting/updating/delete

using nologging , redo will not generate for that table

if no redo , missing changes in archives...

missing  - will not sync with standby from primary

We enable - globally 

force logging

will log forcebly the changes to archives...

----------------------------------------

 Standby Configuration:

-----------------------------   

In stanby Server , we dont have database.

Using RMAN, Will duplicate/Cloning the database from primary to standby.

Preparation

--------------

1. password file - orapwstan (copy from orapwprod)

    must be same as primary.

2. Create pfile from primary

Node1

------

$export ORACLE_SID=prod

$sql>create pfile from spfile;

$cd $ORACLE_HOME/dbs

$cp initprod.ora initstan.ora

$export ORACLE_SID=stan

$echo $ORACLE_SID

Edit the pfile for stan

$vi initstan.ora

 #do following changes.

 #also change the path for audit,controlfiles

*.audit_file_dest='/u01/app/oracle/admin/stan/adump'

*.control_files='/oradata/stan/control01.ctl','/u01/app/oracle/fast_recovery_area/stan/control02.ctl'

#Verify following parameters as follows.

-----------------------------------------

*.db_name='prod' (Must be same on both nodes)

*.db_unique_name='stan' (not similar)

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stanXDB)'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(stan,prod)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/stan VALID_FOR=

(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan'

*.LOG_ARCHIVE_DEST_2='SERVICE=prod LGWR ASYNC VALID_FOR=

(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

#Append additional parameters for stanby

----------------------------------------------------

*.db_file_name_convert='/oradata/prod/','/oradata/stan/'

*.log_file_name_convert='/oradata/prod/','/oradata/stan/'

*.fal_server='prod'

*.fal_client='stan'

*.standby_file_management='auto'

*.instance_name='stan'

*.standby_archive_dest='/archives/stan/'

#remove memory_target if on same node (use comment#)

Save the file...

Create following directories for Stan.

---------------------------------------------

mkdir -p /u01/app/oracle/admin/stan/adump

mkdir -p /u01/app/oracle/oradata/stan

mkdir -p /u01/app/oracle/fast_recovery_area/stan

mkdir -p /oradata/stan/

mkdir -p /archives/stan

------------------------------------------------

Network Configuration

----------------------

    register the stan in Listener and tnsnames.

$netmgr

    Click Listener -  > - Database services

            Add Database -> stan

    Click Service name - > Edit - Create - Service name

                stan...

save

$lsnrctl start LISTENER

$tnsping prod

$tnsping stan

---------------------------------------------------------

Make sure - primary is up and running . ps -ef | grep pmon

----------------------------------------

OPen New Terminal - Another - for stan

$export ORACLE_SID=stan

$echo $ORACLE_SID

sqlplus '/as sysdba'

sql>startup nomount

sql>exit

---------------------------------------------------------

Connect using rman to primary and auxiliary standby instance.

$rman target sys/sys123@prod auxiliary sys/sys123@stan

Use duplicate command to create standby DB.

------------------------------------------

This can be used for Cloning a DB with SID (instead of standby)

rman>

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  NOFILENAMECHECK;

rman>exit

$export ORACLE_SID=stan

$sqlplus '/as sysdba'

sql>select open_mode,name,database_role from v$database;

    mounted physical_standby

Keep in managed recovery  mode - so RFS and MRP will start.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Verify - v$managed_standby - Monitor

sql>select process,sequence#,status from v$managed_standby;

sql>select * from v$archive_gap;

or

v$archive_log,v$log_history

Finding errors in log for standyby:

SQL> select message from v$dataguard_status;

Shutdown - Standby

--------------------------

$export ORACLE_SID=stan

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

sql>shutdown immediate

For startup - standby

--------------------------

$export ORACLE_SID=stan

sql>startup mount

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

sql>select process,sequence#,status from v$managed_standby;

Process started,receiving and applying using v$managed_standby.

-----------------------------------

Protection Modes

-----------------------

Three protection Modes

Max Performance Mode - default

Max Availability Mode

Max Protection Mode

sql>select database_role,protection_mode from v$database;

Maximum Protection -

--------------------------

    Zero Dataloss ,Two sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME

Transactions do not commit until written in atleast one standby server. Wait 

for acknowledgement from standby

If standby is down, primary will also down.

Maximum Availability-

----------------------------

    Zero Dataloss,Single sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME

Transactions do not commit until written in atleast one standby server.Wait for 

acknowledgement from standby.

If standby is down, primary will change the mode to performance mode.

Maximum Performance(default) -

-----------------------------------------

    least Dataloss,Async,NOAFFIRM,DB_UNIQUE_NAME

Transaction will commit then transfer to standby to avoid performance issue. will not Wait for acknowledgement from standby.

-------------------------------------

Execute the following SQL statement on the primary database:

On Prod - Node1

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

eg:

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

SQL> ALTER DATABASE OPEN;

------------------------------------------

Two New Features in Standby - 11g

    Active Dataguard - read only - reporting db

    Snapshot dataguard - read/write - for test cases 

Active Dataguard

=================

    In Mount, we cannot read/write the data.

    To run the reports, can use Active dataguard.

    Converting physical standby to Active DG. 

    ADG will be in read only mode.

    The archives logs will not apply in read only mode.

 

To switch the standby database into read-only mode, do the following.

On Node2 - stan

----------------------

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE OPEN READ ONLY;

SQL> select open_mode,database_role,protection_mode from v$database;

OPEN_MODE

------------------

READ ONLY

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Snapshot dataguard - read/write

----------------------------------------

    For test cases, we can use physical standby converting to snapshot dataguard.

    The changes can be made and rollbacked after keeping in managed recovery mode.

    Converting - ? Mount to read write.-> mount

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

sql>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;

sql>alter database open;

sql>select open_mode from v$database;

SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;

To convert in to physical standby from snapshot

----------------------------------------------------------

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

alter database convert to physical standby;

shutdown immediate

startup mount

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

sql>select process,sequence#,status from v$managed_standby;

startup/shutdown steps - stan - cancel-shut

    for start- mount-last disconnect

v$managed_standby - process started-MRP/RFS

v$archive_log - finding the gap - applied

v$archive_gap - finding the gap

Protection modes - max-avai,per,pro

Snapshot - read write - will undo after - con-phy

ADG - readonly

rman - auxiliary-only instance

duplicate db from active -

If too many archives found gap ? how to fix ?

    RMAN incremental backup from prod - catalogue and apply in standby.

In case no archives - but still too much gap?

    rebuild the standby

Brief  - Prod and Stan - parameters

Primary is Live production server - changes happen

Physical standby - is Standby system - where changes from primary to standby will receive by archives and applied in mount stage

Active Dataguard - is a standby system converted to read only to read/run reports

Snapshot dataguard - is a standby , converted to read/write for test cases

   once converted to physical standby,the changes are rollbacked.

Primary - read/write

Physical - Mounted

Active Dataguard - read-only

Snapshot DG - read/write

NOTE: info on dataguard it may differ on your environment like production,testing,development

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES VIST MY BLOG

https://chaitanyaoracledba.blogspot.com/

DATA PUMP QUESTIONS & ANSWERS

DATA PUMP QUESTIONS & ANSWERS
------------------------------------------------------

1) What is use of CONSISTENT option in exp?
A)Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2) What is use of DIRECT=Y option in exp?
A)Setting direct=yes, to extract data by reading the data directly, bypasses the SGA, bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.

3) What is use of COMPRESS option in exp?
A)Imports into one extent. Specifies how export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y. If table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced. Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on disk (tablespace), and do not want imports to fail.

4) How to improve exp performance?
a) Set the BUFFER parameter to a high value. Default is 256KB.
b) Stop unnecessary applications to free the resources.
c) If you are running multiple sessions, make sure they write to different disks.
d) Do not export to NFS (Network File Share). Exporting to disk is faster.
e) Set the RECORDLENGTH parameter to a high value.
f) Use DIRECT=yes (direct mode export).

5) How to improve imp performance?
a) Place the file to be imported in separate disk from datafiles.
b) Increase the DB_CACHE_SIZE.
c) Set LOG_BUFFER to big size.
d) Stop redolog archiving, if possible.
e) Use COMMIT=n, if possible.
f) Set the BUFFER parameter to a high value. Default is 256KB.
g) It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
h) Use STATISTICS=NONE
i) Disable the INSERT triggers, as they fire during import.
j) Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

6) What is use of INDEXFILE option in imp?
A)Will write DDLs of the objects in the dumpfile into the specified file.

7) What is use of IGNORE option in imp?
A)Will ignore the errors during import and will continue the import.

8)What are the differences between expdp and exp (Data Pump or normal exp/imp)?
A)Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

9) Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?
A)Data Pump is block mode, exp is byte mode. 
Data Pump will do parallel execution.
Data Pump uses direct path API.

10) How to improve expdp performance?
A)Using parallel option which increases worker threads. This should be set based on the number of cpus.

11) How to improve impdp performance?
A)Using parallel option which increases worker threads. This should be set based on the number of cpus.

12) In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?
A)Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.
Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

13) What is the order of importing objects in impdp?
A) Tablespaces
 Users
 Roles
 Database links
 Sequences
 Directories
 Synonyms
 Types
 Tables/Partitions
 Views
 Comments
 Packages/Procedures/Functions
 Materialized views

14) How to import only metadata?
A)CONTENT= METADATA_ONLY

15)How to import into different user/tablespace/datafile/table?
A)REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE 
REMAP_DATA

16) How to export/import without using external directory?
a) Run the older CATEXP.SQL script on the database to be exported
b)use the older export utility to create the dump file
c)use the older import utility to import to the target db

17) Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?
A) No guarantee that an later release expdp dmp file will import into a earlier relese in 10g or 91 this is called forward compatibility ,impossible we can not import to earlier releses


18) How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?
A) $impdp directory= datapump dumpfile=emp_bkp.dmp logfile =imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'




THANK YOU  FOR VIEWING MY BLOG MORE UPDATES VISIT MY BLOG  

ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS

ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS:  

RMAN:

             Rman or Oracle Recovery Manager introduced in oracle 8i,oracle proprietary software client or utility similar to sqlplus used to perform backups,restores,recoveries and other Database operations,Rman takes backup only usedblocks in entire database blocklevel backup,it performs block level backup parallelism,Rman is the utility to take backups and restore oracle recommends,Rman is faster it takes block level backups,Rman is faster because we can initiative parallesism,validate your database using Rman detect block corruption,Rman will repair the database block corruption for you validating backup,incremental backup,recovery catalog.and it is a platform independent tool.

            Rman stores backup metadata inforamtion in the database ,use control file to hold backup metadata information catalog, also using remote repsitory catalog schema database it is a online backup tool its a special feature does block backup with incremental concept,metadata is the control file data stores the information into different database on server,Multi destination backups like tapes,Archive log mode is must to use Rman. Rman utility comes with oracle binaries,no special installation orlicence required for using Rman at command prompt just type rman,it defaults connects to database environmental variables defined, Rman utility can be used only when Database is atleast mount stage,Rman is used while the database is UP and running and have a very little performance impact is backup is running .

COMPONENTS OF RMAN

------------------------------------

RMAN PROMPT

TARGET DATABASE

RECOVERY CATALOG

AUXILAR DATABASE-->clone DB connection target clone

MEDIA MANAGEMENT LAYER-->RMAN and third party tools (net backup)

RMAN CHANNELS---> back up is speed 

FULL BACKUP-----> ENTIRE DATABASE BACKUP (RMAN FULL BACKUP IS EQUAL TO HOT BACKUP  CANNOT APPLY INCREMENTAL BACKUP ON FULL BACKUP)

              

INCREMENTAL BACKUP----> LEVEL 0--->FULL DB BACKUP--> Full Db backup takes bakup of the used blocks( we can restore Db level0)

  |----> LEVEL 1--->BACKUP CHANGES ONLY FROM LAST LEVEL 0 BACKUP--->Takes Backup of only the changed blocks from last backup,Rman will know which block will be changed block header SCN number backup only changed block  taken by referrring data block header for updated SCN (we can recover DB applying archives and open database)

RECOVERY IN TWO PARTS----->RESTORE--->LEVEL 0

                                              |--->RECOVERY--> LEVEL 1

CONFIGURATIONS OF RMAN

-----------------------------------------

To connect using rman

----------------------

$rman target/

To Display the configuration of RMAN.

------------------------------------

rman>show all;

Configuring Device Type - Tape / Disk

----------------------- --------------

For Tape: Tivoli manager (IBM)

rman>Configure default device type to sbt;

For Disk : Default

-------------------

rman>Configure default device type to disk;

Configuring Channels with Parallelism Option - EE

---------------------------------------------

Multiple backupsets can run with parallel option.

Depends on number of cores, can increase the performance of the backup using multiple channels.

$mkdir -p /orabackup/prod/rman/stream1

$mkdir -p /orabackup/prod/rman/stream2

rman>Configure device type disk parallelism 2;

rman>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream1/backup%U';

rman>CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream2/backup%U';

Maximum Piece Size

------------------

Limit the backup piece size 

rman>CONFIGURE CHANNEL  DEVICE TYPE DISK MAXPIECESIZE 1000m;

Optimization

------------

oracle skips the files that are already backedup by enabling optimization.

rman>configure backup optimization on;

Control file autobackup

-----------------------

Control file holds data and control information of database

 as well metadata of backup information.

On every backup event, the control has to get backed up. Enable autobackup.

With control file - includes spfile also

rman>configure controlfile autobackup on;

rman>configure controlfile autobackup off;

rman>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/prod/rman/%F';

Eg:

Output :

--------

Starting Control File and SPFILE Autobackup at 23-JUL-20

piece handle=/orabackup/prod/rman/c-369100151-20171110-00 comment=NONE

Finished Control File and SPFILE Autobackup at 23-JUL-20

Retention Policy - default 1 day

----------------

is defined , how to long to hold the backups ..

Once retention value reached, the backup files will expire.

rman>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Snapshot controlfile

---------------------

rman>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabackup/prod/rman/snapcf_prod.f';

To update the recovery catalog to get sync for consistent , will have a snapshot controlfile.

Backup sets / Backup Pieces

---------------------------

RMAN can also store its backups in an RMAN-specific format called a backup set.

A backup set is a collection of files called backup pieces, each of which may contain the backup of one or several database files.

retention

optimization

control on , path/loc

snapshot

parallelism

channels 

maxpiecesize

disk/tape

---------------------------------------------

How to have a backup using rman?

rman target/

rman>backup database plus archivelog;

Only datafiles

rman>backup database;

only archivelog files

rman>backup archivelog all;

Image backup - same as dbf format

rman>backup as copy database;

list of backup files

rman>list backup;

rman>list backup summary;

Error : scenario:

----------------

ORA-19625: error identifying file /archives/prod/1_986936449_51.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

in case : 

archive log missing , cannot continue backup.

for that , use crosscheck command . will validate the existing archives and mark has expired for

those missing archives in control file. so next backup will skip those missing archives found expired.

rman>crosscheck archivelog all;

rman>backup database plus archivelog;

For backups 

rman>crosscheck backup;

Error : Scenario:-

-----------------

ORA-19502: write error on file "/orabackup/prod/rman/stream2/backup0kth1k51_1_1", block number 51456 (block size=8192)

ORA-27072: File I/O error

Verify the physical file system space on /orabackup

$df -h

if 100% 

remove old files using

Step1 : rman>report obsolete;

will list all files that are expired/obsolete;

Step2 : rman>delete obsolete;

will delete those listed obsolete files.

If those files not listed and not part of the same database.

then delete using rm -rf * from /orbackup/prod/rman/stream1

1. cd /orabackup/prod/rman/stream1

2. ls -ltrh

3. $rm -rf *

Compressed backup

-----------------

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while 

backup.

----------------------------------------------------

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%

Take a archive log backup,and resume space by deleteing those backedup archives.

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

Connect - RMAN

Backup and Recovery

Configuration

retention policy

controlfile auto

path - controlfile

parallelism - faster

channels

optimization - to skip

snapshot -

maxpiece size

Backup sets - specific format

Backup pieces - files

backup database plus archivelog

crosscheck - validate

delete obsolete

report obsolete

Incremental - Level0 - level 1 | Cummulative

Difference - delete obsolete | delete expired

compressed

Backup Strategy - Explain ?

--------------------------

Incremental backup - Block Level - only changes.

Two types of incremental backup.

    Level 0 - Full incremental backup

    Level 1

        Differential incremental backup.

        Cummulative Backup

Full Incremental - Level 0 Backup -is the full complete

 base backup taken normally on peak off hours.

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level - 1

Differential Backup

-----------------

Sunday – full backup Including all archivelogs – this is a base backup.

Up on this – all changes made will be backedup on every day since last

 incremental backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Cummulative – Including all Previous Changes from

base backup (Level 0) on Sunday.

----------

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

So we can use next incremental backup for recovery , if previous level 1 backups are lost.

Its the best backup. If no redundancy.

But still, having multiple reduncies in terms tapes/disks. Will

recommend differential.

Crosscheck

---------

we have archives ,... while backup if archives are missing ? can we continue

 the backup?

What happens ?

 The backup fails.

How to continue ?...

ORA-19625: error identifying file /archives/prod/1_998901437_22.arc

RMAN> crosscheck archivelog all;

Crosscheck command validates archives physically exists and updates control

 file repository the file status.

So while rman backup , will verify the catalog and skip those files are

expired(marked) which are physically not exists.

rman>backup archivelog all;

But recommended to continue with full backup in this situation.

every 2/4 hrs will have archive log backup.

Difference between delete obsolete and delete expired?

------------------------------------------------------

Obsolete? : out of retention period. physically files exists and files are

 out retention.

Expired : physically not exists and marked has expired in catalog.

rman>report obsolete;

rman>delete obsolete;

rman>delete expired backup;

will delete records from catalog those expird.

Note:

Level 0 - Full Incremental Backup - Complete

1TB

Level 1

Differential -- Only changes from last incremental

Mon - 1GB -

Tues - 0.5G

Wed - 0.25G -

Only those changes - the difference

Cummulative - best - including previous changes on top of base backup

Mon - 1G

Tues - 1G+0.5G - lost

Wed = 1G+0.5+0.25G - can retain from wed backup of Tues

which includes previous changes as it is cummulative

Space and time

Real time , we use only differential if redundancy managed with additional tape and storage backup.

If tape backup of tuesday with differential and lost disk backup

Can we retain from tape

/orabackup - rman - tues -

once done

will have tape backup

Note:

------

rman>backup archivelog all delete input skip inaccessible;

difference delete obsolete and expired

based on retention, the backup files get expired but physically exists

those need to be deleted

as backup policy , retention 7 days if incremental

after 7 days - expired

need to clean up space to resume

use obsolete

Recoveries

----------

Recover - Lost datafile

-----------------------

    users will not be able to write in users datafile (scott/hr-eg)

Make sure - we have full DB backup - last night including - archives.

as we lost - keep offline datafile.

@09 last night

rman>backup database plus archivelog;

@next day at 03PM - lost datafile

$rm users01.dbf

SQL> alter database datafile '/oradata/prod/users01.dbf' offline;

rman target/

RMAN>restore datafile '/oradata/prod/users01.dbf';

rman>recover datafile '/oradata/prod/users01.dbf';

will recover changes from last night 09 till today 03pm from

archives.

SQL> alter database datafile '/oradata/prod/users01.dbf' online;

---------------------------------------------------

inconsistent Recovery - If no archives.

    Timebased

        using SCN number(v$database)

As you would expect, RMAN allows incomplete recovery to a specified time,

 SCN or sequence number:

$rman target/

$rman>run {

 shutdown abort;

 startup mount;

 set until sequence 21;

 restore database;

 recover database; #media recovery from archiveslogs

 alter database open resetlogs;

}

-----------------------------------

Whole database recovery

-----------------------

If the controlfiles and online redo logs are still present a whole database

 recovery can be achieved by running the following script:

rman target/

rman>

run { shutdown abort

# use abort if this fails

 startup mount;

 restore database;

 recover database;

alter database open;

}

Note: Using run{} block ,we can execute multiple commands in rman.

-------------------------------------

Difference between delete obsolete and delete expired

Delete Obsolete - will delete, where the files physical exists and out of

 retention period.

rman>delete obsolete;

rman>delete obsolete noprompt;

Delete expired - will delete those files are marked as expired and physically

 not exists.

rman>delete expired backup;

---------------------------------------------------

Compressed backup

-----------------

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while

backup.

----------------------------------------------------

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%--->/orabackup

space resume by delete

if missed,skipping

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

13.arc bkp arclog

14.arc

15.arc

16.arc till - next fail. due to missing archives

18.arc skip(17) - inaccessible will continue also delete

those backedup(13,14,15,16,18).

21.arc skip(19,20) - inaccessible

backup completed and resumed space

22.arc

23.arc

missing 17/19

NOTE: info on Rman may be differs in  your environment like production,testing,development or u r host machine.

THANK YOU VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG REGULARLY

MANAGING TABLESPACE AND DATAFILES IN ORACLE

MANAGING TABLESPACE AND DATAFILES IN ORACLE

Introduction: 

Tablespace :In this blog i am going to explain MANAGING TABLESPACE AND DATAFILES IN ORACLE,Tablespace is one or more logical storage units in oracle database  which collectively store all the database data and each tablespace has one or more datafiles, datafiles are physical structure tablespace is not visbile in the filesytem of the machine which datbase resides,The tablespace builds the bridge between the oracle database and the filesystem in which the tables or index's data stored ,The data in oracle database are stored in tablespaces, An oracledatabase can be logically grouped into smaller logical areas of space known as tablespaces,each tablespace consits of one or more operating system files,which are called datafiles

1)SYSTEM TABLESPACE: SYSTEM tablespace is used to store the sytem related data which includes tables,indexes,sequences,and other objects,Contains the data dictionary,including stored program units Contains the SYSTEM undo segment,should not contain user data,SYSTEM tablespace always online when database is open ,oracle database have a SYSTEM tablespace when database created SYSTEM is the first tablespace


2)SYSAUX TABLESPACE : SYSAUX TABLESPACE stores many databse components,always online when database is open,these SYSAUX table created when oracle database installed,if u SYSAUX table gets filled 100% none of the users will not be able to login 


3)UNDO TABLESPACE :  UNDO TABLESPACE is a kind of permanent tablespace used by oracele to manage undo data if your running your database in automatic undo management mode this undo data or undo records are generally used to roll back transations  Recover from logical corruptions using flashback feature 


4)USERS TABLESPACE: USERS TABLESPACE is used to store the user objects and data in permanently every database have a tablespace for permanent user data is assigned to users otherwise the objects will be stored SYSTEM tablespace  


5)TEMP TABLESPACE:  TEMP TABLESPACE is stores the temporary data  that only exists during the database session,oracle uses temporary tablesapce to improve the concurrency of multiple sort operations, temp tablesapce shared by multiple users


DATABASE STORAGE HIERARCHY  HOW DATA IS STORED IN LOGICAL AND PHYSICAL
--------------------------------------------------------------------------------------------------------------------------

  DATABASE
     |
     |
     v
  TABLESPACE  ---------> DATAFILE-----> PHYSICAL------> SAN(STORAGE AREA NETWORK)
     |                                    | 
     |                                    |-----------> NAS (NETWORK ATTACHED STORAGE)
     v
  SEGMENT---------->LOGICAL
     |
     |
     v
  EXTENT----------->LOGICAL
     |
     |
     v
 ORACLE DATA BLOCK------------> LOGICAL OS BLOCK



TABLESPACE------>LOGICAL STORAGE UNIT
SEGMENTS-------->SPACE ALLOCATED FOR A TABLES INFORM OF EXTENTS
EXTENTS -------->CONTIGUOS ORACLE BLOCK 



Dictionary Tables

dba_tablespaces
dba_data_files
dba_segments
dba_extents
dba_free_space


List the tablespaces

sql>desc dba_tablespaces

sql>select tablespace_name from dba_tablespaces;

sql>select tablespace_name,extent_management,allocation_type,segment_space_management from dba_tablespaces;

sql>desc dba_tablespaces

Finding tablespace for sys owned.

Sys stores objects in system tablespace.

SQL> select username,default_tablespace from dba_users where username like 'SYS';


Listing datafiles of a tablespace:

sql>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';

Creating Tablespace:

sql>create tablespace CHAITBS datafile '/oradata/prod/chaitu01.dbf' size 100m;

Adding datafile:

sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Note: 32G is the max file size on unix level

Finding size of a datafile:

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';


Resize Datafile:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' resize 200m;

Enable Autoextend on/off:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Unlimited:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize unlimited;

In oracle, the maxsize of the datafile on unix level is max 32G.


Dropping tablespace :

sql>drop tablespace CHAITBS including contents and datafiles;


Create tablespace
add datafile
resize
autoextend on
drop tablespaces
finding free space
find datafile location/size/max/autoextnd
list of tablespaces /managed type/segment space management

Dictionary tables/views

    dba_tablespaces
    dba_data_files
    dba_free_space
    dba_segments
    dba_extents


Find the usage

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'SYSTEM';


Finding free space.of a particular /specific tablespace

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name like 'SYSTEM' group by tablespace_name;

For all tablespaces - using group by

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


--------------------90%(7GB)-------limi(8G)----------------------max(32G)
New --------------------------------------------------------------max(32G)
existing limit ------------(8G)

Assume - Limit - 8GB
        threshold reached 90% (7GB)
            but Max size 32GB
Now action:
        Add new datafile with unlimited
        then limit existing datafile to 8GB


sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Now limit existing

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Temp Tablespace (non system Tablespace)

    purpose : for sorting,analyze,index rebuild,group by

    default - temp(one)

dba_temp_files
v$temp_space_header

we can create many
sql>create temporary tablespace chaitempfile '/oradata/prod/chaitutemp01.dbf' size 100m autoextend on maxsize unlimited;

Adding temp file:

sql>alter tablespace cctemp add tempfile '/oradata/prod/cctemp02.dbf' size 100m autoextend on maxsize unlimited;

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name like 'CCTEMP';

Finding free usage:

sql>desc v$temp_space_header

SQL> select tablespace_name,sum(bytes_used/1024/1024),sum(bytes_free/1024/1024) from v$temp_space_header group by tablespace_name;

Error:

unable to extend an extent in temp segment of temporary tablespace
find the usage and add tempfile

Datafile monitoring,check freespace,add , limit,autoextend.

V$sort_usage - inactive - kill - support - approval


Dictionary Tables :

dba_tablespaces
dba_data_files
dba_free_space
dba_temp_files
v$temp_space_header


User management


Create user Chaitanya identified by chaitu123 default tablespace chaitbs temporary tablespace cctemp;

application users ---> application - -->chaitanya- - ->db

schema- - ->collection of objects ---> acces to db users


file limit - 8G
--------------------7G---->8G(Max)-----------setto(32G-YES)
  90% reached - alert received action

add datafile New - -32G
limit existing to 8G - autoextend on maxsize 8G.


resize - if disable autoextend on ,then limit by resize - physically allocate
maxsize- only allocate on growth


Managing Tablespaces and data files locally managed tablespace


sql>create  tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size=50m
    extent management local autoallocate;

Alternative to Autoallocate is uniform

sql>create tablespace tbs2 datafile' '/oradata/prod/data01.dbf'
   size=50m
  extent management local uniform size 256k;

Dictionary managed tablespace


sql>create tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size 50m
   extent management dictionary;


Big File Tablespace


sql>create bigfile tablespace tbsbf1 datafile '/oradata/prod/databf01.dbf'
    size 50g;

Coalesce statement

sql>alter tablespace tbs1 coalesce;


Viewing information about tablespace and datafiles

sql>select * from dba_tablespaces;
sql>select * from v$tablespace;


To view information about datafiles


sql>select * from dba_data_files;
sql>select * from v$datafiles;

To view information about tempfiles


sql>select * from dba_temp_files;
sql>select * from v$tempfile;


To view information about freespace in tempfiles

sql>select * from v$temp_space_header;


To view information about free space in datafiles

sql>select * from dba_free_space;


To view the value and type of the blocksize

sql>show parameter db_block_size;

Note : Info on MANAGING TABLESPACE AND DATAFILES IN ORACLE it may be differ in your environment like production,testing,development,etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES SUBSCRIBE ME AND FOLLOW ME