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 | 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 | 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 | Leave a comment

What is ETL in Sql ?

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 | 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

What is Agile methodology? Examples, when to use it, advantages and disadvantages

Agile software development methodology is an process for developing software (like other software development methodologies – Waterfall model, V-Model, Iterative model etc.) However, Agile methodology differs significantly from other methodologies. In English, Agile means ‘ability to move quickly and easily’ and responding swiftly to change – this is a key aspect of Agile software development as well.

Brief overview of Agile Methodology

  • In traditional software development methodologies like Waterfall model, a project can take several months or years to complete and the customer may not get to see the end product until the completion of the project.
  • At a high level, non-Agile projects allocate extensive periods of time for Requirements gathering, design, development, testing and User Acceptance Testing, before finally deploying the project.
  • In contrast to this, Agile projects have Sprints or iterations which are shorter in duration (Sprints/iterations can vary from 2 weeks to 2 months) during which pre-determined features are developed and delivered.
  • Agile projects can have one or more iterations and deliver the complete product at the end of the final iteration.

Example of Agile software development

Example: Google is working on project to come up with a competing product for MS Word, that provides all the features provided by MS Word and any other features requested by the marketing team. The final product needs to be ready in 10 months of time. Let us see how this project is executed in traditional and Agile methodologies.

In traditional Waterfall model –

  • At a high level, the project teams would spend 15% of their time on gathering requirements and analysis (1.5 months)
  • 20% of their time on design (2 months)
  • 40% on coding (4 months) and unit testing
  • 20% on System and Integration testing (2 months).
  • At the end of this cycle, the project may also have 2 weeks of User Acceptance testing by marketing teams.
  • In this approach, the customer does not get to see the end product until the end of the project, when it becomes too late to make significant changes.

The image below shows how these activities align with the project schedule in traditional software development.

Agile versus traditional software development methodologies

With Agile development methodology –

  • In the Agile methodology, each project is broken up into several ‘Iterations’.
  • All Iterations should be of the same time duration (between 2 to 8 weeks).
  • At the end of each iteration, a working product should be delivered.
  • In simple terms, in the Agile approach the project will be broken up into 10 releases (assuming each iteration is set to last 4 weeks).
  • Rather than spending 1.5 months on requirements gathering, in Agile software development, the team will decide the basic core features that are required in the product and decide which of these features can be developed in the first iteration.
  • Any remaining features that cannot be delivered in the first iteration will be taken up in the next iteration or subsequent iterations, based on priority.
  • At the end of the first iterations, the team will deliver a working software with the features that were finalized for that iteration.
  • There will be 10 iterations and at the end of each iteration the customer is delivered a working software that is incrementally enhanced and updated with the features that were shortlisted for that iteration.

The iteration cycle of an Agile project is shown in the image below.

Agile Methodology development-what is agile

gile software Development Methodology

This approach allows the customer to interact and work with functioning software at the end of each iteration and provide feedback on it. This approach allows teams to take up changes more easily and make course corrections if needed. In the Agile approach, software is developed and released incrementally in the iterations. An example of how software may evolve through iterations is shown in the image below.

Agile Iterative Development

Agile methodology gives more importance to collaboration within the team, collaboration with the customer, responding to change and delivering working software.

Agile development has become common place in IT industry. In a recent survey over 52% of respondents said that their company practiced Agile development in one form or another. Irrespective of your role in the organization, it has become essential to understand how Agile development works and how it differs from other forms of software development.

In traditional approach each job function does its job and hands over to the next job function. The previous job functions have to signoff before it is handed over the next job function authenticating that the job is full and complete in all aspects. For example, Requirement gathering is completed and handed over to design phase and it is subsequently handed over to development and later to testing and rework. Each job function is a phase by itself.

In Agile way of working, each feature is completed in terms of design, development, code, testing and rework, before the feature is called done. There are no separate phases and all the work is done in single phase only.

Difference between Agile model and Non-Agile models


Agile Model

Non-Agile Models

Approach of this methodology This methodology is very flexible and adjustable and can adapt to the project needs. This methodology is not as flexible as Agile model and it’s tough to accommodate changes in the project.
Measurement of Success The success of the project in Agile model is measured by the Business value delivered. In this methodology the success of the project is measured by the Conformation to plan.
Size of the Project The Project size is usually small in Agile model. However larger projects can also be handled using the Scaled Agile Framework (SAFe). The project size is Large in non- Agile models.
Style of Management The style of management in Agile model is not centralized. It is distributed among the team members. The management style in the non-Agile models is dictatorial. Only one person is the decision maker and rest of the people follows him.
Ability to adapt to change In Agile model the changes are accepted and adapted as per the project needs. But in non-Agile models the changes are not accepted easily in the later stages of the development.
Documentation required Less documentation is required in Agile. More documentation is required in non-Agile models.
Importance of In Agile model more emphasis is given to the people that means it’s People- Oriented. In non-Agile models the more importance is given to the process hence it’s Process- Oreinted.
Cycles or iterations Agile methodology has many cycles or iterations which is also known as Sprints. But, in Non-Agile methodology the cycles are limited.
Planning in Advance There is minimal upfront planning in Agile methodology. In Non-Agile models the planning should be complete before the development starts.
Revenue In Agile method the return on investment is early in the project cycle. In non-Agile methods the return on investment is at the end of the project.
Size of the team The size of the team in Agile methodology is usually small and creative. But in Non-Agile models the team size is large.

When to use Agile and Non-Agile models

Project Attributes Agile Model Non-Agile Model
Requirement of the Project Requirements in Agile model can change as per the customer requirement. Sometimes requirements are not very clear. In Non-Agile models the requirements are very clear before entering into the development phases. Any change in the requirement is not easily accepted during the development phases.
Size of the Project The Project size is small in Agile model hence small team is required. But in Non-Agile models the Project size is usually big hence big team is required.
Design of the Project In Agile model the architecture is made as per the current requirements but is designed to be flexible. In Non-Agile models the architecture is made as per the current requirements as well as for future requirements.
Planning and Control of the Project In Agile model the planning of the project is Internalized and has qualitative control. But in Non-Agile models the plans are documented properly and have quantitative control.
Type of Customers Agile methodology is followed by the collaborated, dedicated collated and knowledgeable customers. In Non-Agile models the customers are of Contract provisions.
Developers required In Agile model the developers should be knowledgeable, analytically strong, collated and collaborative. In Non-Agile models the developers should be more Plan Oriented.
Refactoring In Agile model refactoring is not costly. But in Non-Agile models the refactoring is very costly.
Risks involved Usually in Agile models the chances of occurrence of unknown risks are more which can have major impact in the project. In Non-Agile models the risks are understood clearly and the impact of the risk in the project is very less.

Advantages of Agile Methodology

  • In Agile methodology the delivery of software is unremitting.
  • The customers are satisfied because after every Sprint working feature of the software is delivered to them.
  • Customers can have a look of the working feature which fulfilled their expectations.
  • If the customers has any feedback or any change in the feature then it can be accommodated in the current release of the product.
  • In Agile methodology the daily interactions are required between the business people and the developers.
  • In this methodology attention is paid to the good design of the product.
  • Changes in the requirements are accepted even in the later stages of the development.

Disadvantages of the Agile Methodology

  • In Agile methodology the documentation is less.
  • Sometimes in Agile methodology the requirement is not very clear hence it’s difficult to predict the expected result.
  • In few of the projects at the starting of the software development life cycle it’s difficult to estimate the actual effort required.
  • The projects following the Agile methodology may have to face some unknown risks which can affect the development of the project.
Posted in PHP | Tagged , | Leave a comment