Comparison of different Backup Strategies

Traditional Logical Backup

We don't consider the Logical backup as main backup since it doesn't support Recovery.

Advantages of Logical Backup:

·         Logical backups are more useful in case of schema refreshes

·         Logical backups are useful in case of database upgradations as well as migrations

·         Logical backups are platform independent

·         Logical backups are more useful in case of DB-Reorg, Schema Re-Org and Table Re-Org

·         Files generated by Logical backup are Dump file and Log File

·         Dump file is partial binary and partial text in nature. Dump file contains metadata as well as Business data. Business data will be in encrypted format

·         Log file is a text file which shows the progress of the job.  Log file contains messages as well as error messages that are encountered during the progress of the job. By default a user can take backup of his own objects.

·         If a user wants to take backup of entire database, he must have exp_full_database  role. In the same way in order to import full database he must have imp_full_database role

·         In order to invoke exp and imp utilities, Database should be up and running

·         If you don't specify Dump file name, by default oracle creates a dumpfile with name expdat.dmp

·         If you specify same name for the dump file, simply it overwrites the  existing file

·         By default Traditional logical backup is conventional, which means it will go through the SGA processing layer  by specifying Direct=Y

·         During import sometimes we get undo related errors. To overcome this specify commit=Y
Commit=Y inserts array of records into the database and then commits

·         To suppress the DDL related errors(Eg: Object EMP is already available) during import, we can specify ignore=Y

How to print column wise data in rows

It can be done by using UNPIVOT

In Oracle :

SQL>create table udfvalues as

    select 'one' c1, 'two' c2, 'three' c3 from dual;

SQL> select * from udfvalues

unpivot ( val for (source) in

                ( c1 as 'C1',                                                                                                          

                   c2 as 'C2',         

                   c3 as 'C3'  )

                )

/

Insert into multiple tables using single SELECT Query

Multi table Insert is a extension to INSERT...SELECT .

There are two types of Multi table Inserts

·         INSERT FIRST

·         INSERT ALL

Syntax :

INSERT ALL|FIRST

   [WHEN condition THEN] INTO target [VALUES]

   [WHEN condition THEN] INTO target [VALUES]

   ...

   [ELSE] INTO target [VALUES]

SELECT ...

FROM   source_query;

Example:

SQL> SELECT * FROM EMP;

SQL> SELECT DEPTNO,COUNT(1) FROM EMP GROUP BY DEPTNO;

SQL> CREATE TABLE EMP_10 AS SELECT * FROM EMP WHERE 1 =2 ;

SQL> CREATE TABLE EMP_20 AS SELECT * FROM EMP_10;

SQL> CREATE TABLE EMP_30 AS SELECT * FROM EMP_10;

SQL> CREATE TABLE EMP_OTHER AS SELECT * FROM EMP_10;

Inserting all records in Multiple tables :

SQL> Insert All

                into EMP_10

                into EMP_20

                into EMP_30

        select * from emp;

SQL> SELECT * FROM EMP_10;

SQL> SELECT * FROM EMP_20;

SQL> SELECT * FROM EMP_30;

Condition based Insert in Multiple tables :

Insert FIRST

                WHEN Deptno = 10

                THEN INTO  EMP_10

                WHEN Deptno = 20

                THEN INTO EMP_20

                WHEN Deptno = 30

                THEN INTO EMP_30

                ELSE

                INTO EMP_OTHER

SELECT * FROM EMP

We can also insert Particular Columns Data:

Insert FIRST

                WHEN Deptno = 10

                THEN INTO  EMP_10(empno,sal)

                                values (empno,sal)

                WHEN Deptno = 20

                THEN INTO EMP_20(empno,sal)

                                values (empno,sal)

                WHEN Deptno = 30

                THEN INTO EMP_30(empno,sal)

                                values (empno,sal)

                ELSE

                INTO EMP_OTHER

SELECT * FROM EMP

how to change sqlplus prompt from sql to username

After login to Sqlplus

set sqlprompt "_user> "

It will come as SYS>

You can also see as SYS @ hrms> by setting

set sqlprompt "_user '@' _connect_identifier > "

If you want it it permanently

You have to set the same in the file  $ORACLE_HOME\sqlplus\admin\glogin.sql script