On our production servers we have simple function with result_cache, like this:
create or replace function f_rc(p_id number) return number result_cache is ret number; begin select t.val into ret from rc_table t where t.id=p_id; return ret; exception when no_data_found then return null; end;
/And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. "select for update" from this table with commit; 2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases:
[URL]......
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating. In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?
When executing a SELECT with JOIN in oracle error as I am having the title. If I change the JOIN to where the procedure works normally. When installing my application, it works normally for a day or two, then it only shows the end of communication error. What I do is select the following:
i am new to oracle,when i am compiling below procedure,
create or replace procedure GetTempPolicyDetails2(i_officeid in number,pr_cursor out SYS_REFCURSOR) as
L_QUEY VARCHAR2(4000):= 'select POLTMP.POLICY_ID,POLTMP.CORPORATE_NAME,POLTMP.POLICY_FROM_DATE,POLTMP.POLICY_TO_DATE, POLTMP.NETPREMIUM_WITH_SERVICE_TAX, IOF.OFFICENAME,UW.NAME,REG.REGNAME,brmstr.brokername,created_on from policies_temp POLTMP INNER JOIN ISSUINGOFFICE IOF ON IOF.ID=POLTMP.Divisionid INNER JOIN REGION REG ON REG.ID=IOF.RID INNER JOIN underwriters UW ON UW.ID=REG.UWID LEFT OUTER JOIN broker_master BRMSTR ON BRMSTR.ID=POLTMP.Broker_Name where POLTMP.Policy_Status=1 where 1=1' L_WHERE VARCHAR2(4000):='WHERE 1=1'; [code]....
I'm getting the following error on a procedure that I was able to compile until today after some changes.
ORA-06550: line 354, column 20: PLS-00103: Encountered the symbol "AND" when expecting one of the following: . then or The symbol ". was inserted before "AND" to continue.
i want create view to select all invalid objects in database.So i create this one:
CREATE OR REPLACE FORCE VIEW INVALID_OBJECTS_DETAILS ( DETAILS ) AS SELECT DISTINCT a.owner || ', ' || a.object_name FROM dba_objects a, dba_source b WHERE a.owner = b.owner AND a.object_name = b.name AND a.object_type = b.TYPE AND a.status != 'VALID' AND b.text NOT LIKE '%@%';
But I want only select invalid objects without a database link .
I am Modifying a table structure, so dependent objects(triggers,packages etc) are getting invalid. So i thought of compiling those invalid objects which are related with the modified table. I used below query to get the invalid objects,
select obj.object_name,obj.object_type from user_objects obj,all_dependencies dep where referenced_name='DEPT' and obj.object_name=dep.name and dep.owner='SCOTT' and obj.status='INVALID'
Q1)What is wrong with this query, sometimes it works sometime it doesn't.? Q2)All_dependencies : does this view show all dependent object on a table even the Invalid one's? Q3) Is there any alternative to find the dependent invalid objects or even dependent object on a table ?
Quote:by default the job runs once every 24 hours.
When you see LAST_ANALYZED being days, weeks, or months in the past do not be alarmed.
If/when the data in a table does not change, then the statistics do not need to change.
Oracle collects new statistics when enough of the data (about 10%) has changed.
Like above statement for DBMS_STATS job, is there a automatic job that runs every day to compile invalid objects for schema or we need to compile it manually ?
Recently I migrated our Oracle to new machine using exp/imp on schema basis. After import finished I had tons of invalid objects in database. I ran utlrp.sql script and lots of them got validated. Than I recompiled manually in EM those are left but two invalid objects (MGMT_JOB_UI description and body) in SYSMAN schema gave error while recompiling.
Now when I click on any scheduled jobs to edit it or view its schedule, EM throw following error:
X Error jobType - jobType page property expected
I think its related to that invalid package. The errors while compiling the specification are as follow:
Line # = 50 Column # = 1 Error Text = PL/SQL: Declaration ignored Line # = 65 Column # = 9 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared Line # = 88 Column # = 1 Error Text = PL/SQL: Declaration ignored Line # = 107 Column # = 9 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared
The Package specification is as below,
AS---------------------------------------------------------------------------------- type definitionTYPE CURSOR_TYPE IS REF CURSOR;-- Get the targets for this step, if any-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN SMP_EMD_STRING_ARRAY;-- Get the targets for this step, if any, as a comma separated string-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets_str ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN VARCHAR2;-- Get the parameters for this job, filter as specified for this jobtypePROCEDURE get_visible_params ( p_job_id RAW, p_exec_id RAW, p_params_out OUT CURSOR_TYPE);-- Get the URI for this uri_use-- see emSDK/job/dtd/UriSource.java for uri_use constantsFUNCTION get_display_uri ( p_job_type IN VARCHAR2, p_uri_use IN
find out the list of referenced objects which are dependent on INVALID objects.
I wrote the below query to get the referenced objects which depends on invalid objects.
select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies where type not in ('JAVA CLASS') AND referenced_type NOT IN('JAVA CLASS') AND (NAME, TYPE) IN ( select object_name, OBJECT_TYPE from dba_objects WHERE STATUS='INVALID')
And the output is something like below -
Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE 1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD 2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD 3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD
[code].....
Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.
We are running Oracle 11.1.0.6 on CentOS Linux at our local network. Two dozen users work from client's network to access this database. There are firewall and switches between the local network and client network. Everything run smoothly for last two years until about 10 days ago, something changed. The client on the client's network can still connect to this database, but they can not run large queries. Their client connection hangs and time out. I can see TNS-12535: TNS:operation timed out and TNS-00505: Operation timed out in the alertlog file. They even had problem to load stored procedures and views onto TOAD from your PC. All the user on the local network can work on this database normally.
We consulted network team and firewall team, none of them said they make any changes recently and have no firewall rule or others to block the traffic.
I have a strong feel that this is a network related problem. But don't know how to provide solid evidence to make network people/firewall people to buy it.
You can connect to the database but can't run query on large table/view?
I am getting below error while connecting to sqlplus.
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 22 12:47:48 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. ERROR: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
[code]....
Executed the below scripts but it didnt resolve the issue, whereas some of the SYS objects and catproc got invalid...
dbmsotpt.sql dbmsapin.sql
Now even after reexecuting the catproc.sql and utlrp...Sys objects and the catproc status is still INvalid.
I tried to manually compile the sys objects, but it didnt work.
OWNER SUBSTR(OBJECT_NAME,1,40) OBJECT_TYPE -------------------- ---------------------------------------- -------------------- SYS DBMS_XPLAN PACKAGE BODY SYS AQ$AQ_SRVNTFN_TABLE VIEW SYS DBMS_LOGREP_DEF_PROC PACKAGE SYS DBMS_LOGREP_DEF_PROC PACKAGE BODY
[code]....
how to go about making the SYS objects and catproc VALID and resolve the error which i mentioned above.
We are porting our application from HP-UX to Sun Solaris and as part of that I am trying the compile a Pro*C program in Sun Solaris using SUNWspro C++ compiler. Precompiling is failing with following error.
System default option values taken from: /u01/app/oracle/product/10.2.0/precomp/admin/pcscfg.cfg
Syntax error at line 44, column 30, file /u01/app/SUNWspro/prod/include/CC/stlport4/algorithm: Error at line 44, column 30 in file /u01/app/SUNWspro/prod/include/CC/stlport4/a lgorithm # include _STLP_NATIVE_HEADER(algorithm) .............................1 PCC-S-02014, Encountered the symbol "(" when expecting one of the following:
[code]...
Normal C++ files are getting compiled with out eny issues. This particular file is having functions written in C fashion.
Above query runs perfectly on SQL prompt.Same query, when being run in a Pro*C program gives compilation error-:
Error at specified line:
,( SELECT D.V3 FROM TABLE2 D WHERE D.V3 = C.V4) .............................1 PCC-S-02201, Encountered the symbol "D" when expecting one of the following: ( ) * + - / . @ | at, day, hour, minute, month, second, year,
What are the possbile causes of error. I am using Oracle 10g on Unix.
how to grant compile access for stored procedures..? I got execute access so that i can execute procedure from another user but not able to save/modify once i edited.
i am trying to compile using SQL developer. Error Message :Error: ORA-01031: insufficient privileges
One of my friends is facing a peculiar problem where objects are getting "Invalid" during execution I suspect it is happening as they are changing system date during their testing (time travel) which can create conflicted last_ddl_time on objects having dependencies
Consider a scenario
[1] system date is 10-06-2012 there are total 10 objects which has status as 'valid'
[2] the system date is changed to 10-07-2012 Now out of 10 Only 5 objects are compiled During execution ORA-04065,ORA-06508, ORA-06512 are observed
[3] the system date is brought back to 10-06-2012 Again during execution ORA-04065,ORA-06508, ORA-06512 are observed
suppose in step 2 objects are compiled whereas there synonyms are compiled in step 1, only thus last_ddl_time for objects will be later to that of its' synonym...
Does database validate last_ddl_time for objects having dependency during execution and then auto-compiles or invalidates the objects?
I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.
i'm on apex.oracle.com creating a mobile application. When creating a new region of type report -> Listview it results in an error if the query is too big (or returns too many columns).The error is: Error during rendering of region "Add Javascript code for Plugins". ORA-06502: PL/SQL: numeric or value error: character string buffer too small
The query i used: SELECT * FROM APEX_APPLICATION_PAGE_ITEMS
get Syntax Highlighting for the Application Builder: URL....