Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Wednesday 24 July 2013

What is a Global Temporary Table in Oracle?



Global Temporary Table:
The oracle gives the advantage of creating the session specific tables known as Global temporary table.
They are same as the other tables in oracle except that they are exclusive for every session means that the data in temporary table is private to the session (i.e. each session can see and modify its own data). The LOCK statement has no effect on a temporary table because each session has its own private data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session.
It does not truncate the data of other sessions that are using the same table.

Syntax for creating Global Temporary Table:
·         Session Level Global Temporary Table:-
    Create global temporary table temp_table (column1  NUMBER,column2  NUMBER)
    on commit preserve rows

·         Transaction Level Global Temporary Table:-
    Create global temporary table temp_table (column1  NUMBER,column2  NUMBER)
     on commit delete rows;


The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
The ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.


Summary:

·         You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.
·         You can create views that access both temporary and permanent tables.
·         You can also create triggers on temporary tables.
·         You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.

Monday 22 July 2013

Difference between Procedure and Function in oracle pl/sql



Difference between Procedure and Function

  • Usually functions are used to perform a specific computational task and procedures are used to perform or implement some business logic.
  • Procedure may or may not return value but function should always return one value.
  • We can call functions in select statement (That’s why we can use them in the script for creating views also) whereas for calling procedure we have to write a PL/SQL block in PL/SQL.
  • We can call Stored Procedure within function. We can also call function within stored procedure.

Wednesday 15 May 2013

Oracle DataBase Import Export Steps

ORACLE DATABASE EXPORT- IMPORT STEPS

This document will describe the steps to export the oracle database from one server (machine) and import this database to different server (machine) or we can do this on same machine.

1)      Exporting the DataBase :- Please refer to  the following commands for doing so:

a)      First create a Directory for the actual path where original DataBase resides. Suppose the Path is d:/database. Then the command should be as:

àCreate or replace directory ‘Directory Name’ as ‘d:/database’;
    (Run these commands on CMD)
     
                  b) Now run this Command for exporting database

à Expdp UesrName/Password@SID    SCHEMAS=() directory=’Directory name’ dumpfile= ‘File name.dmp’ logfile=’file name.txt’;

 Note :- If want to export all schemas just mention full=Y in place of schemas.

c) Exract the table spaces in the database with following command in toad
select file_name from dba_data_files;
Match all the table space with standard tablespace script which needs to be run in the bank database before import data and create additional tablespaces if any.(Sometimes a table space is exteneded automatically to manage the more data so for this additional tablespace we need to create in the blank database as mention in some later steps of import data)
     2)  Importing the DataBase :- Please refer to  the following commands for doing so:

a)      Create a blank database in destination oracle. 
  1. Run as :à dbca
  2.  Click Next. 
  3. Click Next.
  4. Type the DATABASE & SID Name --Click Next.
  5.  Click Next.
  6.  Type password for DATABASE --Click Next.
  7.  Click Next.
  8.  Click Next.
  9. Click Finish.
  10.  It will create the Blank DataBase.
After creating the blank database
 Create tablespace Script with commands as:
 Create  tablespace extracted in step 2 (c) as below

CREATE TABLESPACE "ERES_SMALL"
LOGGING
    DATAFILE 'D:\oracle\product\10.2.0\tbs\ERES_SMALL.ora' SIZE 10M AUTOEXTEND ON NEXT 10M
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

---D:\oracle\product\10.2.0\tbs  :- This should be the path of the tablespace folder created for  database
--Execute the script as SYSDBA user And Give all grats required to the users you created
 --This script should contains create command for all the table spaces in the previous database.

Create a directory from sqlPlus db_dir for folder where .dmp file and .log file exists or we copied it (Database backup files)as

SQL> create or replace directory db_dir as 'D:\CathpciDatabase';


CMD>impdp UsrName/Password@SID schemas=(Schema Names) Directory=db_dir DUMFILE=Where actual DB exists LOGFILE=Where actual logfile exists VERSION=10.2.0.3