What is pragma sql in oracle ?

In Oracle PL/SQL, PRAGMA refers to a compiler directive or “hint” it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:

  1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.
  2. PRAGMA SERIALLY_REUSABLE: This directive tells Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim the memory.
  3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
  4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
  5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.


The procedure P_ERR_LOG uses the PRAGMA AUTONOMOUS_TRANSACTION directive to capture the error occuring in a program unit.



Posted in oracle | Tagged | Leave a comment

What is ‘SERIALLY REUSABLE’ in oracle package ?

SERIALLY_REUSABLE packages let you design applications that manage memory better for scalability.

If a package is not SERIALLY_REUSABLE, its package state is stored in the user global area (UGA) for each user. Therefore, the amount of UGA memory needed increases linearly with the number of users, limiting scalability. The package state can persist for the life of a session, locking UGA memory until the session ends. In some applications, such as Oracle Office, a typical session lasts several days.

How to create serially reusable package ?

To create a SERIALLY_REUSABLE package, include the SERIALLY_REUSABLE pragma in the package specification and, if it exists, the package body.

  n NUMBER := 5;
END pkg;

  n NUMBER := 5;
END sr_pkg;

  pkg.n := 10;
  sr_pkg.n := 10;

  DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n);
  DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n);

--it comes to:

pkg.n: 10
sr_pkg.n: 5

Let’s take one example of explicit cursor as following:

DROP TABLE people;
CREATE TABLE people (name VARCHAR2(20));
INSERT INTO people (name) VALUES ('John Smith');
INSERT INTO people (name) VALUES ('Mary Jones');
INSERT INTO people (name) VALUES ('Joe Brown');
INSERT INTO people (name) VALUES ('Jane White');

  CURSOR c IS SELECT name FROM people;
END sr_pkg;
  v_name  people.name%TYPE;
  IF sr_pkg.c%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open.');
    DBMS_OUTPUT.PUT_LINE('Cursor is closed; opening now.');
    OPEN sr_pkg.c;
  FETCH sr_pkg.c INTO v_name;
  DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_name);
  FETCH sr_pkg.c INTO v_name;
    DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_name);
  END fetch_from_cursor;

First call to server:



Cursor is closed; opening now.
Fetched: John Smith
Fetched: Mary Jones
Cursor is open.
Fetched: Joe Brown
Fetched: Jane White

source: https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS99923

Posted in oracle | Tagged | Leave a comment

what features are not available in oracle express edition ?


  • Enterprise Edition (EE) – The flagship Oracle offering with a vast array of tools and features for the large corporation.
  • Standard Edition (SE) – Oracle SE contains the basic database management functions for small- and medium-sized shops at a far lower cost than the EE. Standard edition also has RMAN.
  • Standard Edition One (SEO) – Oracle SEO is specially-priced for single CPU servers used by small businesses.
  • Oracle Express (XE) – Oracle Express contain some DBA features, including basic read-only replication.

These major features are not included in oracle XE:

  1. Advanced security option :- Oracle Advanced Security provides two important preventive controls to protect sensitive data at the source: encryption and redaction
  2. InterMedia :- Oracle interMedia is a feature that enables to store,manage & retrieve the audio, video, images & heterogeneous data.
  3. Oracle messaging gateway:- Messaging Gateway enables communication between applications based on non-Oracle messaging systems and Oracle Streams.
  4. Oracle change management pack:- The Oracle Change Management Pack is a group of integrated applications used to track and make changes to database object definitions.
  5. Oracle clusterware:- Oracle Clusterware is the cross platform cluster software required to run the Real Application Clusters (RAC) option for Oracle Database.
  6. Oracle configuration  management pack:- it enables you to configure the oracle on your need.
  7. Oracle connection manager:- Oracle Connection Manager is a multipurpose networking solution that offers increased scalability, multiprotocol connectivity.
  8. Oracle data guard:- Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
  9. Oracle partitioning:- Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
  10. Oracle fail safe:- Oracle Fail Safe is a core feature included with every Oracle Database license for Windows Server. It is a high availability software, integrated with Microsoft Failover Cluster, that provides a fast, easy, and accurate way to configure and verify Windows clusters and to automatically fail over Oracle databases and applications.
  11. Oracle label security:- Easily categorize and mediate access to data based on its classification. Designed to meet public-sector requirements for multilevel security and mandatory access control, Oracle Label Security provides a flexible framework that both government and commercial entities worldwide can use to manage access to data on a “need to know” basis.
  12. Oracle OLAP:- Oracle OLAP is a world class multidimensional analytic engine embedded in Oracle Database. Oracle OLAP cubes deliver sophisticated calculations using simple SQL queries – producing results with speed of thought response times.
  13. Oracle Spatial:- Oracle Spatial and Graph includes high performance, enterprise-scale, commercial spatial and graph database and analytics for Oracle Database 12c.
  14. Oracle streams:- Oracle Streams are a generic mechanism for sharing data which can be used as the basis of many processes including messaging, replication and warehouse ETL processes. They are an extension of a number of existing technologies including Advanced Queuing, LogMiner and Job Scheduling.
  15. Oracle ultra search:- Oracle Ultra Search is built on the Oracle Database and Oracle Text technology that provides uniform search-and-location capabilities over multiple repositories such as, Oracle databases, other ODBC compliant databases, IMAP mail servers, HTML documents served up by a Web server, files on disk, and more.
Posted in oracle | Tagged | Leave a comment

Find objects size in MB (TABLES, INDEXES, PARTITIONS etc)


Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)

SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND ROUND(bytes/(1024*1024),2) > 1000 
Posted in oracle | Tagged | Leave a comment

What is ETL in Sql ?ETL

What is ETL: ETL stands for Extract, Transform, Load. Extract means to read data from source, Transform means to apply business logic on data & in last load means to add or load data on other server.

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse. ETL involves the following tasks:

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse. ETL involves the following tasks:

  • extracting the data from source systems (SAP, ERP, other operational systems), data from different source systems is converted into one consolidated data warehouse format which is ready for transformation processing.
  • transforming the data may involve the following tasks:
    applying business rules (so-called derivations, e.g., calculating new measures and dimensions),
    cleaning (e.g., mapping NULL to 0 or “Male” to “M” and “Female” to “F” etc.),
    filtering (e.g., selecting only certain columns to load),
    splitting a column into multiple columns and vice versa,
    joining together data from multiple sources (e.g., lookup, merge),
    transposing rows and columns,
    applying any kind of simple or complex data validation (e.g., if the first 3 columns in a row are empty then reject the row from processing).
  • loading the data into a data warehouse or data repository other reporting applications.

Why we need ETL ?: we need it because whenever you need to move some logic or data from one server to another server then you need to write ETL. for example: you have written a query i.e. using linked server or more than one database so it will take time to run so it’s better option to write ETL for particular data tables.

Posted in oracle, sql | Tagged | Leave a comment

How do I find duplicate values in a table in Oracle?

To find duplicate value we need the sql group by & having clause as below.


How query works?

  1. First it will group the same value by non-clustered index i.e. virtual indexing by oracle in-built.
  2. After grouping of same value, it will calculate the count of rows which is repeatedly.
  3. Query performance will be slower when you use Having clause because it will filter all data from actual output.
Posted in oracle, sql | Tagged | Leave a comment

What is clustered & non-clustered index in oracle?

Clustered index & Non-clustered index

  • A clustered index the table records are physically stored on the basic of that index, A table can have only one clustered index because of physically stored to the table.
  • A Non-clustered index that is one kind of logic or reference to the actual table & data will be stored to the outside of the table, a table can have more than one clustered index.
  • In Theory, each data object within an Oracle database is a totally separate entity.
    In practice, certain tables are frequently used together, especially when using a normalized database design.
    A cluster is a way of organizing data to leverage the related nature of data stored in different locations.

Advantage of cluseter

  • Since related data is stored together, the related data can be accessed with fewer data block reads. With a cluster, Oracle reads the cluster key, which directly points to the disk area that contains the data for that value of the key. The cluster delivers two advantages:
  • First of all, a query can retrieve all the related data with usually no more than 2 logical reads–one to get the cluster key and another to retrieve a data block that contains only the relevant data. For instance, if an employee table is clustered on the department number, a query for employees in a department would first retrieve the relevant cluster key for the department and then the employee rows for that department. If an order header table and an order detail table were clustered together, one read would retrieve the order cluster key and one more read would retrieve both the order header and detail rows for that order.
  • The second advantage is that the value for the cluster key is only stored once, and this reduces the amount of space required for storage.

When to use cluster?

  • The ideal places to use a cluster are when you have a group of tables that are frequently queried together, or when you have a single table that is frequently accessed by an index value. For example, you might cluster an order header table and an order detail table together. If you frequently accessed employees by department, you may want to cluster an employee table by department.

when not to use cluster

  • In some situations, a cluster is definitely not appropriate. You should not use a cluster in the following situations:
    If the value for a cluster key is updated frequently.
  • If the data for the values of the cluster key takes up more than one or two Oracle data blocks.
  • If you frequently require full table scans on the clustered data.
Posted in oracle, sql | Tagged , | Leave a comment