Partitioning a table online with DBMS_REDEFINITION

If there is a requirement to change the structure of a table that is already in use productively, it may be impossible to get a maintenance downtime for that table, because it is constantly in use. That can be the case for all kind of structural changes of a table, particularly for the change from an ordinary heap table into a partitioned table, which we are going to take here as an example. In order to demonstrate that, we will create a demonstration user with a non-partitioned table with privileges and additional dependent objects on it:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam

Connected.

SQL> create table original as select

rownum as id,

mod(rownum,5) as channel_id,

5000 as amount_sold,

mod (rownum,1000) as cust_id,

sysdate as time_id

from dual connect by level<=1e6;  

Table created.

SQL> create index original_id_idx on original(id) nologging;

Index created.

SQL> grant select on original to hr;

Grant succeeded.

The challenge is now to change this table into a partitioned one while it is used with DML & queries by end users. For this purpose, we introduced already in 9i (if I recall it right) the package DBMS_REDEFINITION. First step would be to ask, whether it can be used in this case:

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> begin

dbms_redefinition.can_redef_table

 (uname=>'ADAM',

 tname=>'ORIGINAL',

 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

end;

/

PL/SQL procedure successfully completed.

Because there is no Primary Key on the original table, I have to use CONS_USE_ROWID, else I could use CONS_USE_PK. There are no objections against the online redefinition of the table here – else an error message would appear. Next step is to create an interim table of the structure, desired for the original table. In my case, I create it interval partitioned (an 11g New Feature). I could also change storage attributes and add or remove columns during that process.

SQL> create table interim

(id number,

channel_id number(1),

amount_sold number(4),

cust_id number(4),

time_id date)

partition by range (cust_id)

interval (10)

(partition p1 values less than (10));

Table created.

My original table has 1000 distinct cust_ids, so this will lead to 100 partitions – each partion will contain 10 distinct cust_ids. One benefit of that would be the possibility of partition pruning, should there be statements, specifying the cust_id in the where-condition. These statements will be about 100 times faster as a full table scan. The next step will basically insert all the rows from the original table into the interim table (thereby automatically generating 99 partitions), while DML during that period is recorded:

SQL> set timing on

SQL>

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE

 (uname=>'ADAM',

 orig_table=>'ORIGINAL',

 int_table=>'INTERIM',

 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.76

If this step takes a long time to run it might be beneficial to use the SYNC_INTERIM_TABLE procedure occasionally from another session. That prevents a longer locking time for the last step, the calling of FINISH_REDEF_TABLE. Next step is now to add the dependent objects/privileges to the interim table:

SQL> set timing off

SQL> vari num_errors number

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

 (uname=>'ADAM',

 orig_table=>'ORIGINAL',

 int_table=>'INTERIM',

 num_errors=>:num_errors);

END;

/

PL/SQL procedure successfully completed.

SQL> print num_errors

NUM_ERRORS

----------

 0

There was no problem with this step. Until now the original table is still an ordinary heap table – only the interim table is partitioned:

SQL> select table_name from user_part_tables;

TABLE_NAME

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

INTERIM

In the last step, the two tables change their names and the recorded DML that occured in the meantime gets used for actualization:

SQL> begin

dbms_redefinition.finish_redef_table

 (uname=>'ADAM',

 orig_table=>'ORIGINAL',

 int_table=>'INTERIM');

end;

/  

PL/SQL procedure successfully completed.

We will now determine that the original table is partitioned and the dependencies are still there:

SQL> select table_name,partitioning_type from user_part_tables;

TABLE_NAME                     PARTITION

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

ORIGINAL                       RANGE

SQL> select count(*) from user_tab_partitions;

 COUNT(*)

----------

 100

SQL> select grantee,privilege from  user_tab_privs_made where table_name='ORIGINAL';

GRANTEE                        PRIVILEGE

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

HR                             SELECT

SQL> select index_name,table_name from user_indexes;

INDEX_NAME                     TABLE_NAME

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

ORIGINAL_ID_IDX                ORIGINAL

TMP$$_ORIGINAL_ID_IDX0         INTERIM

The interim table can now be dropped. We changed the table into a partitioned table without any end user noticing it!

This picture illustrates the steps you have seen above:

Conclusion: If a table structure needs to be modified and the table is permanently accessed by end users, this can be done with some effort using DBMS_REFDEFINITION. One common –  but not the only possible – use case is the modification of a non-partitioned table into a partitioned one. You have seen a simplified demonstration about it. As always: Don’t bel

Oracle MERGE statement - Features, Enhancements and Logging

The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an "upsert". The MERGE statement reduces table scans and can perform the operation in parallel if required. With different releases of Oracle MERGE has also got enhancements. 

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. 

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

Oracle Database does not implement fine-grained access control during MERGE statements. If you are using the fine-grained access control feature on the target table or tables, then use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.

Prerequisite:
You must have the INSERT and UPDATE object privileges on the target table and the SELECT object privilege on the source table. To specify the DELETE clause of the merge_update_clause, you must also have the DELETE object privilege on the target table.

Syntax:

MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery 
         } [ t_alias ]
   ON ( condition )
   [ merge_update_clause ]
   [ merge_insert_clause ]
   [ error_logging_clause ] ;

merge_update_clause ::=

merge_insert_clause ::=

error_logging_clause ::=

LOG ERRORS 
  [ INTO [schema.] table ]
  [ (simple_expression) ]
  [ REJECT LIMIT { integer | UNLIMITED } ]

Initially when MERGE statement was introduced it has the following syntax:

Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.
MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

The source can also be a query.
MERGE INTO employees e
    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

With further Oracle release there was a tremendous enhancement in the way MERGE works.

MERGE Statement Enhancements in Oracle Database 10g

We will be using a test table to explain the Enhancement with example. 

CREATE TABLE test1 AS
SELECT *
FROM   all_objects
WHERE  1=2;

1. Optional Clauses

The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

In previous oracle version these two clauses were used together. From 10g the MATCHED and NOT MATCHED clauses are now optional ( can be used without other) making all of the following examples valid.
-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

Conditional Operations

Conditional inserts and updates are now possible by using a WHERE clause on these statements.

Specify the where_clause if you want the database to execute the insert/update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the insert/update operation when merging the row into the table.
-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

DELETE Clause

An optional DELETE WHERE clause can be added to the MATCHED clause to clean up after a merge operation. Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. Depending on which table the DELETE WHERE references, it can target the rows prior or post update. The following examples clarify this.

Create a source table with 5 rows as follows.
CREATE TABLE source AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 5;

SELECT * FROM source;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5

5 rows selected.

SQL>

Create the destination table using a similar query, but this time with 10 rows.
CREATE TABLE destination AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 10;

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

10 rows selected.

SQL>

The following MERGE statement will update all the rows in the destination table that have a matching row in the source table. The additional DELETE WHERE clause will delete only those rows that were matched, already in the destination table, and meet the criteria of the DELETE WHERE clause.
MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>

SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice there are rows with a status of "10" that were not deleted. This is because there was no match between the source and destination for these rows, so the delete was not applicable.

The following example shows the DELETE WHERE can be made to match against values of the rows before the update operation, not after. In this case, all matching rows have their status changed to "10", but the DELETE WHERE references the source data, so the status is checked against the source, not the updated values.
ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE s.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         10 Updated
         3         10 Updated
         5         10 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice, no extra rows were deleted compared to the previous example.

By switching the DELETE WHERE to reference the destination table, the extra updated rows can be deleted also.
ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

5 rows selected.

SQL>

Restrictions on the merge_update_clause 
This clause is subject to the following restrictions:

You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.

Restriction on Merging into a View 

• You cannot specify DEFAULT when updating a view.

Advantages of Using MERGE 

Oracle Flashback Query (SELECT AS OF)

To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

•  Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
•  Comparing current data with the corresponding data at an earlier time. For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
•  Checking the state of transactional data at a particular time. For example, you can verify the account balance of a certain day.
•  Simplifying application design by removing the need to store some kinds of temporal data. Oracle Flashback Query lets you retrieve past data directly from the database.
•  Applying packaged applications, such as report generation tools, to past data.
•  Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

Example of Examining and Restoring Past Data

Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the employees table, and you know that at 9:30AM the data for Chung was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.

Example 1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

Example 1 Retrieving a Lost Row with Oracle Flashback Query

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

Example 2 restores Chung's information to the employees table:

Example 2 Restoring a Lost Row After Oracle Flashback Query

INSERT INTO employees (
  SELECT * FROM employees
  AS OF TIMESTAMP
  TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE last_name = 'Chung'
);

Guidelines for Oracle Flashback Query

• You can use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query.
• To use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.
• If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp. 
• You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
  If you specify a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For example:

  CREATE VIEW hour_ago AS
    SELECT * FROM employees
      AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  

  SYSTIMESTAMP refers to the time zone of the database host environment.
• You can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times.
  You can store the results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, this query reinserts into table employees the rows that existed an hour ago:

  INSERT INTO employees
      (SELECT * FROM employees
       AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
      MINUS SELECT * FROM employees;
  

  SYSTIMESTAMP refers to the time zone of the database host environment.

We can use either AS OF TIMESTAMP or AS OF SCN. For example.

CREATE TABLE flashback_query_test (
  id  NUMBER(10)
);

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     722452 2004-03-29 13:34:12

INSERT INTO flashback_query_test (id) VALUES (1);
COMMIT;

SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

SELECT COUNT(*)
FROM   flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
         0

SELECT COUNT(*)
FROM   flashback_query_test AS OF SCN 722452;

  COUNT(*)
----------
         0

ORA-04068: existing state of packages has been discarded

Error ORA-04068: existing state of packages has been discarded comes with package having variables defined in package specification or body. Whenever there are variables/constants defined in package specification or body that package is said to have a state. Upon first calling the package, the "state" is created in memory to hold the values of those variables etc.

This error is received when following changes are performed on package which has state :

•  If an object that the package depends upon e.g. a table is altered in some way e.g. dropped and recreated, then because of the database dependencies, the package takes on an INVALID status.
• If there is some compilation error in package specification or body due to which it's state goes invalid.
•  Value of any variable in the package specification or body is changed and package is compiled, in a different session.
• If we make any modification in package specification or body in a different session. Even if the change doesn't make the state INVALID.

If we compile such a package specification then all the procedures/packages referring procedure/function/variable of that procedure will give this error.

When you next make a call to the package, Oracle looks at the status and sees that it is invalid, then determines that the package has a "state". Because something has altered that the package depended upon, the state is taken as being out of date and is discarded, thus causing the "Package state has been discarded" error message.

NOTE: We will never receive this error if we do not have any variable/constant defined in a package.

Solution:

• Move all constants and variables into a stand-alone package specification and reference those from your initial package. Thus when the status of your original package is invalidated for whatever reason, it has no package state and can be recompiled automatically, however the package containing the variables/constants will not become invalidated as it has no dependencies, so the state that is in memory for that package will remain and can continue to be used.
• Make the package SERIALLY_REUSABLE. No state will be saved for the package and oracle will create new state of the package for every call

Simulation of ORA-04068: existing state of packages has been discarded error:

SQL> set serveroutput on

SQL> create table dependonme (x number)
  2  / 

Table created.

SQL>
SQL> insert into dependonme values (5)
  2  / 

1 row created.

SQL>
SQL> create or replace package mypkg is
 v_statevar number := 5;
procedure myproc;
    end mypkg;
    / 

Package created.

SQL>
SQL> create or replace package body mypkg is
     -- this means my package has a state
  
      procedure myproc is
        myval number;
      begin
        select x
        into myval
        from dependonme;

     -- myval := myval * v_statevar;
      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
    end;
   end mypkg;
 / 

Package body created.

SQL>
SQL> exec mypkg.myproc
My Result is: 25

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

1. Changing the Value of a variable declared in package specification:

I have a variable declared in package specification. This variable is not being used anywhere.
a). In same session: 
SQL>  create or replace package mypkg is
  2      v_statevar number := 15;
  3      procedure myproc;
  4      end mypkg;
  5      /

Package created.

SQL>  exec mypkg.myproc;
My Result is: 5

Change of value in same session didn't make any error.

b). In a second session.
Chaning the value in session 2:
create or replace package mypkg is
   v_statevar number := 25;
   procedure myproc;
   end mypkg;
/

Executing procedure in previos session 1:
SQL>  exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1

NOTE: Just compiling and not changing the value will not give this error. If value of variable is changed in session 2 only then we will receive this error.

2. Changing  the Value of a variable declared in package body:

Similarly if the variable is declared in Package Body rather than in specification and if we make changes to that variable in package body in a different session, then again we will receive this error.

SQL> create or replace package mypkg is
  2
  3         procedure myproc;
  4          end mypkg;
  5      /

Package created.

SQL> create or replace package body mypkg is
  2        v_statevar number := 105;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

SQL>  exec mypkg.myproc;
My Result is: 525

PL/SQL procedure successfully completed.

SQL> create or replace package body mypkg is
  2        v_statevar number := 205;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

SQL> exec mypkg.myproc;
My Result is: 1025

PL/SQL procedure successfully completed.

Session 2: Compiling the package body with different value set for variable in it, in session 2 will generate this error.

SQL>    create or replace package body mypkg is
  2        v_statevar number := 305;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

Session 1: Executing the dependent procedure again.

SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1


3. Making changes to object on which procedure in package body is dependend

a) Droping the table used in procedure body which maked the package body INVALID. These operation can be performed in same session.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

SQL> drop table dependonme;

Table dropped.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        INVALID

SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1

However if we did not have any variable defined then we would got the same error which is recieved on second call of this procedure.

SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04063: package body "SCOTT.MYPKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1


NOTE: 
 Testing this same scenario but by declaring the variable in specificaiton rather then body would give the same result. So variable defined anywhere in package specification or body, when state of body get invalid, we will receieve this error.


4. Making changes to logic of procedure in package body 

b). In Same session. No error will be generated if we change the logic in procedure.

SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select sal
  6          into myval
  7          from emp
  8     where empno = 7369;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000

PL/SQL procedure successfully completed.

SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select x
  6          into myval
  7          from dependonme;
  8
  9         myval := myval * mypkg.v_statevar;
 10         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11       end;
 12     end mypkg;
 13   /

Package body created.

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 500

PL/SQL procedure successfully completed.

b). Changing the package logic in a different session will generate this error.

Session 1:
-----------
SQL> exec mypkg.myproc;
My Result is: 500

PL/SQL procedure successfully completed.

Session 2:
-----------
SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select sal
  6          into myval
  7          from emp
  8     where empno = 7369;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

Session 1:
-----------
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000

PL/SQL procedure successfully completed.


5. Checking the procedure specification variable change with two session
It has been observed that whenever there is a change in variable value in package specification, the package body got invalid and all the session who calls the procedure in this package starts showing "ORA-04068: existing state of packages has been discarded" error.

Session 1:
-----------
SQL> create or replace package mypkg is
  2   v_statevar number := 40;
  3   procedure myproc;
  4   end mypkg;
  5  /

Package created.

SQL> create or replace package body mypkg is
  2        procedure myproc is
  3          myval number;
  4        begin
  5          select x
  6          into myval
  7          from dependonme;
  8
  9       --  myval := myval * mypkg.v_statevar;
 10         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11       end;
 12     end mypkg;
 13   /

Package body created.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

Session 2:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5

PL/SQL procedure successfully completed.

Session 3:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5

PL/SQL procedure successfully completed.

Session 1:
----------
SQL>  create or replace package mypkg is
  2    v_statevar number := 40;
  3    procedure myproc;
  4    end mypkg;
  5   /

Package created.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        INVALID

Session 2
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1

Session 3
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1

6. Package Body Depended on Package Specification
This example shows package body is dependent on package specification and any change in it will make package body invalid. However we do not receive error while referring that package body procedure in same session because Package body is complied when we call the procedure.

SQL>  create or replace package mypkg is
  2       v_statevar number := 40;
  3        end mypkg;
  4      /

Package created.

SQL>  create or replace package body mypkg is
  2            procedure myproc is
  3              myval number;
  4            begin
  5              select x
  6              into myval
  7              from dependonme;
  8  
  9           --  myval := myval * mypkg.v_statevar;
 10          DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11        end;
 12      end mypkg;
 13    /

Package body created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS
----------                    ------- 
MYPKG                          VALID                               MYPKG                          VALID                                            
SQL>  create or replace package mypkg is
  2       v_statevar number := 140;
  3        end mypkg;
  4      /

Package created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                               
--------------                -------  
MYPKG                          VALID     
MYPKG                          INVALID                                          
SQL>  create or replace package body mypkg is
  2            procedure myproc is
  3  
  4            begin
  5                 DBMS_OUTPUT.PUT_LINE('My Result is: ');
  6        end;
  7      end mypkg;
  8    /

Package body created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                               
-----------------             -------                               
MYPKG                          VALID                               
MYPKG                          VALID                                            
SQL>  create or replace package mypkg is
  2       v_statevar number := 240;
  3        end mypkg;
  4      /

Package created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                             
---------------               -------                               
MYPKG                          VALID                             MYPKG                          INVALID                                          

Conclusion:
"ORA-04068: existing state of packages has been discarded" error is received only on a package which has variable defined in its package specification or body. We will get this error in following situaions:

•  If value of variable defined in specification or body, is modified in a different session.
•  Package specification or body got INVALID.
•  Package specification or body is modified in a different session.

Solution:

With pragma SERIALLY_REUSABLE package

Making the package SERIALLY_REUSABLE will avoid this error.
Session 1:
-----------
SQL> create or replace package mypkg is
  2  pragma SERIALLY_REUSABLE;
  3  v_statevar number := 5;
  4  procedure myproc;
  5  end mypkg;
  6  /

Package created.

SQL> create or replace package body mypkg is
  2  pragma SERIALLY_REUSABLE;
  3           procedure myproc is
  4          myval number;
  5        begin
  6          select x
  7          into myval
  8          from dependonme;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

SQL> exec mypkg.myproc;
My Result is: 25

PL/SQL procedure successfully completed.

Session 2: Modifying the variable value

SQL>  create or replace package mypkg is
  2   pragma SERIALLY_REUSABLE;
  3   v_statevar number := 50;
  4   procedure myproc;
  5   end mypkg;
  6   /

Package created.

Session 1:

SQL> exec mypkg.myproc;
My Result is: 250

PL/SQL procedure successfully completed.