ORA-04068 - Existing State Of Packages Has Been Discarded

Feb 4, 2011

We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.

I'll provide the timeline of what occurred:

1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.

2.) I ran a "drop package body" statement to drop the package body from MY schema.

3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.

4.) I ran a script to check for any invalid objects under the application user schema. There were none.

5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.

6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.

I also ran utlrp.sql.

I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.

I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.

View 4 Replies


ADVERTISEMENT

ORA-04068 / Existing State Of Packages Has Been Discarded

Feb 6, 2007

I am trying to use dbms_jobs in Oracle 8i database.

This is the syntax I am using:

begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;

i have defined the variable jobno as number!

And i am getting the error :

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

I then go and look at the Sys.dbms_job package and see its not compiling

I have this error:

(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored

View 2 Replies View Related

PL/SQL :: Existing State Of Package Has Been Discarded

Nov 2, 2012

I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.

usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.

but i am getting the following error, even if the package is in "valid state".

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has
been invalidated
ORA-04065: not executed, altered or dropped package
"TEST_OWNER.TEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called:
"TEST_OWNER.TEST_PKG"
ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error,
but do we get this error even when the package is in "valid" state too?

Re: When should be package invalidated?

View 4 Replies View Related

SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?

Dec 19, 2011

I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.

GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'

[code]....

View 5 Replies View Related

SQL & PL/SQL :: How To Debug The Packages

Nov 2, 2011

Any ways for Debugging in PLSQL.

Ex:In a package or procedure i have thousands lines of Code how can i Debug.

View 1 Replies View Related

SQL & PL/SQL :: Packages - Roles And Privileges?

Aug 9, 2005

I'm not sure if this belong to this place. move it to the proper place.

I'm having an annoying problem: (I'm not sure if it is a problem)
- In a package body I'm trying to delete/update/insert/select the contents of a table in other schema.
- The respective synonym exists.
- I had created a role with the respective privileges over the synonym.
- I granted the role to the package's owner.
- I try to compile the package, but it keeps returning compilation errors. (Not table found)
- In standard SQL, I can delete/insert/update/select over the table.
- The only way to compile the package, without errors, is to grant the privileges directly to the package's owner.

Is it supposed to work in this way?

View 7 Replies View Related

PL/SQL :: Display Names And Packages

Jul 17, 2013

NAME         package

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

Anand         basic           Anand         pascal    Juliana       cobol           Kamala        dbase     Mary          oracle    Mary            c         Partick        c++             Qadir         assembly        Qadir            c               Ramesh         dbase     Remitha          c               Remitha        assembly        Revathi         pascal    Revathi         basic          

View 5 Replies View Related

RAC & Failsafe :: Oracle Asm Packages For RHEL5.3 32 Bit?

Jul 3, 2011

I am unable to locate the asm packages for rhel 5 :2.6.8-128 kernel of 32 bit.

Only 64 bit rpm are availbale on OTN.Can ayone suggeet me the URL for 32 bit>

View 1 Replies View Related

SQL & PL/SQL :: Packages Component Full Details

May 19, 2011

Get following output using user_source(or other) view?

Package_nameProcedure_nameline_startline_end
ABCXYZ1022

In procedure_name column it could be any procedure, function name or any object name exist in package body.

View 6 Replies View Related

PL/SQL :: How To Know A Trigger Gets Called By Which All Packages / Procedures

Sep 11, 2012

Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?

View 2 Replies View Related

JDeveloper, Java & XML :: Parsing In PL/SQL Without Any DBMS_XML Packages?

Jun 15, 2012

i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"

[code]....

View 29 Replies View Related

PL/SQL :: Difference Using Cursor In Specifications And Body In Packages

Jun 24, 2013

what is the difference using cursor in specifications & body in packages?

View 1 Replies View Related

SQL & PL/SQL :: Keep Logging Track Of Each Task Performed In Procedure Packages

Nov 26, 2010

we have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.

now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.

View 4 Replies View Related

Cloud Service :: Adding Packages To Cart In SQL Developer

Jan 14, 2013

I am really wondering about data loading to the cloud, I've just read the documentation, but regarding the packages, I have more than 100 package and I have to "add to cart" one by one the problem is that the package body is not added by default, so you have to expand the packages one by one then select the package body right click and add to cart. This task is very tedious, is there any other options.”

View 8 Replies View Related

RAC/ASM Clusterware Installation :: 11g Release 2 Install Missing Packages?

Aug 8, 2012

I'm trying to install 11g Release 2 on a CentOS (4.9) box, but the pre-req check shows that I'm missing the following packages (I had 9 to begin):

gcc-3.4.6 (X86_64)
gcc-c++-3.4.6(X86_64)
libstdc++-devel-3.4.6

Tried to use the yum command "yum install gcc" but get the following error.
Error: cannot find a valid baseurl for repo:update

I think this is due to the "CentOS-Base.repo" file, which I've tried to update by changing the mirror URL's but no success.Then tried to find the rpms online ([URL]...

INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: rm -f ntcontab.*

[code]....

View 2 Replies View Related

Server Administration :: How To Spot Which Oracle Packages Are Installed / Licensed

Feb 22, 2011

I need to figure out which Oracle Database packages are installed on a certain server.

First problem: no Enterprise Manager available. So I need some command line tools or SQL statements.

I've already found some with them I was able to spot RAC, partitioning and so on. But I'm still missing a option/command to spot

- diagnistic pack
- tuning pack
- web logic
- web server

View 3 Replies View Related

Server Utilities :: Export Procedure Views Function And Packages In Database

Sep 29, 2010

How to export the procedure,views,function and packages in a database, by using Export commmand.

View 2 Replies View Related

Server Utilities :: Will Schema Export All Roles / Procedures / Packages / Synonyms / Functions And Triggers

Sep 21, 2010

i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:

1) Do i need to pre-create only schema user or all the users under it.

2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?

View 1 Replies View Related

Index Is In Unusable State

Sep 1, 2010

I can see the error in alert log like

2437312:ORA-12801: error signaled in parallel query server P009
2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state

and tried to rebuild the index and i got following error.

ORA-14086: a partitioned index may not be rebuilt as a whole.

The table size for the index is large. we need to rebuild the hole index.

View 9 Replies View Related

SQL & PL/SQL :: Index Unused State

May 7, 2013

I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.

marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.

View 2 Replies View Related

PL/SQL :: Get CITY And STATE From Address

Jul 9, 2013

I have got a table where the site address is stored as follows - 

SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US 

I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.

For example: -

Following output should be shown for the above recordsCITY  
 
STATEHOLLYWOOD       FLPHILADELPHIA        PAELIZABETH       NJCALABASAS       CA

View 3 Replies View Related

Session Inactive State?

Jul 1, 2012

want to know the following

1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?

View 6 Replies View Related

SQL & PL/SQL :: RESTRICT_REFERENCE Changing A Database State

Mar 24, 2010

When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?

as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.

RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state

what is there to state as i know what my code is doing.

without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).

View 7 Replies View Related

Oracle Solid State Disk

Feb 1, 2013

I understand the architecture of solid state drives favor different types of I/O. Oracle 11g introduced the database flash cache which allows flash storage to be used as a secondary cache. With database flash cache serving as a secondary cache to my Oracle buffer cache.

My understanding is I need to config two database parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE. If i want to have certain tables/indexes to take advantage of this extra cache I would set the value KEEP within the parameter.

Is there anything that needs to be done to the tablespaces / datafiles.

View 1 Replies View Related

SQL & PL/SQL :: List Most Popular Product For Every State?

Feb 11, 2011

so here is the query : For every state, list the most popular product. Here is the query I have so far :

SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;

Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2

I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...

View 4 Replies View Related

Make Tablespace Offline In Mount State?

May 7, 2012

I started database in mount state;startup mount; alter tablespace users offline;

I tried to make tablespace offline In another case online but showing error database not open same for command alter tablespace begin backup; do think we can make tablespace offline in mount...

View 1 Replies View Related

SQL & PL/SQL :: ORA-01502 / Partition Of Index Is In Unusable State

Aug 3, 2011

after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.

View 7 Replies View Related

SQL & PL/SQL :: ORA 20000 Index Partition In Unusable State?

Jan 3, 2012

ora 20000 index partition in an unusable state. what can i do

View 6 Replies View Related

SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State

Feb 15, 2012

The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.

However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:

select GetDate ('010312', 'dd-mm-yyyy')
from dual

Here is the code I used to create the function:

create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is

vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;

View 5 Replies View Related

Recover Tablespace (Database) To Older State

Jan 30, 2013

i have the following problem:

At a test database instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.

First i've done a fullbackup with RMAN:
----------
# rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
----------
Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:
----------
# sqlplus / as sysdba
SQL> drop table TESTTABLE;
----------
After that i would start the restore and recover process by restoring and recovering the tablespace in which I deleted the table with RMAN. At first i've set the tablespace offline:
----------
SQL> alter tablespace TESTTABLESPACE offline immediate;
----------
Then I go to RMAN and start the restoring:
----------
RMAN> restore tablespace TESTTABLESPACE;
----------
Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I dropped the table from the tablespace this operation was written in the redolog files. So when i make a :
----------
RMAN> recover tablespace TESTTABLESPACE;
----------
The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?

And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved