SQL & PL/SQL :: Compile Invalid Objects

Jul 16, 2012

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 ?

View 4 Replies


ADVERTISEMENT

Auto Compile Invalid Database Objects

Nov 11, 2013

If i modify any procedure then auto compile should happen dependended objects.

Oracle Version: 10g

View 1 Replies View Related

SQL & PL/SQL :: View To See Invalid Objects

Apr 20, 2011

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 .

View 6 Replies View Related

SQL & PL/SQL :: Query On Invalid Objects

Jul 10, 2013

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 ?

View 5 Replies View Related

SQL & PL/SQL :: Invalid Objects In SYSMAN Schema

Jul 19, 2012

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

[code]....

View 16 Replies View Related

SQL & PL/SQL :: List Of Dependencies For Invalid Objects?

Jun 11, 2013

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.

View 5 Replies View Related

Server Administration :: Cause Of Invalid Objects

May 11, 2012

I have a doubt on invalid objects.What would be the impact to database and application if there are many invalid objects in database?

View 3 Replies View Related

Server Administration :: SYS Objects And Catproc Invalid?

Mar 22, 2011

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.

View 36 Replies View Related

Server Utilities :: Objects Gets Invalid During Import

Feb 1, 2011

Why is that we recompile all invalid objects after import and how the object gets invalid during import?

View 1 Replies View Related

Server Administration :: Invalid Dba-objects - Care / Repair?

Jan 31, 2012

here are so much invalid dba_objects. Do I need to care/repair?

SELECT owner, COUNT (*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY ROLLUP (OWNER)

output
ZIM4_RO71
ZIM473
PUBLIC16
SYS35
195

View 11 Replies View Related

Server Administration :: Objects Invalid Due To Auto-compilation

Jun 27, 2012

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?

View 3 Replies View Related

Query To Identify Invalid Objects In Database Per Schema

Sep 29, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: Compiling Invalid Objects Results In ORA-03113 Error

Mar 19, 2011

I am getting the following error while compiling the invalid objects.

ERROR at line 2: ORA-03113: end-of-file on communication channel

I am trying to compile the invalid objects after importing the dump file.

View 1 Replies View Related

PL/SQL :: How To Use Toad To Compile New Package

Nov 14, 2012

I am not very familiar with Toad, if developer gives modified package, I need to replace this package and compile it . How to do it in Toad .

View 4 Replies View Related

Alter Sequence In Procedure Will Not Compile?

Dec 31, 2005

When I try to compile a procedure with this command:

alter sequence myschema.seqmessages increment by 100;

The error says "encountered symbol "ALTER" when expecting...

Is there another way to alter a sequence from a procedure? In this case, I am altering a sequence in another schema that has granted the alter and select privileges for the sequence.

View 7 Replies View Related

Forms :: FMB Do Not Compile - Segmentation Fault

Jul 15, 2011

If I copy a fmb from Linux to Windows after saving the fmb without any changes I can not compile it. I get

"ROS ERRORL -200
Segmentation fault(coredump).

I have noticed that the form has increased a lot in size.

View 20 Replies View Related

SQL & PL/SQL :: Compile Specific Packaged Procedure

Jul 20, 2012

I want to recompile a single procedure that is part of a package, without re-compiling other procedure/functions present in that package, is it possible?

View 1 Replies View Related

SQL & PL/SQL :: Cursor And Variable - Procedure Will Not Compile?

May 20, 2013

I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"

All of the query results from the cursor are correct.

create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name xx,
t1.owner || '.' || t1.TABLE_NAME uu,

[code]...

View 15 Replies View Related

Forms :: Compile Multiple Fmb Files At Once In 6i?

Feb 21, 2012

I need to compile multiple fmb files at once in FORMS6i.

View 1 Replies View Related

SQL & PL/SQL :: Continue To Get Compile Errors Attempting While Loop

Mar 19, 2010

I continue to get compile errors attempting a While Loop. Should I being using a For Loop?

DECLARE
v_item VARCHAR2(20) := &v_item;
v_cost vm_inventory.cost%TYPE;
v_expense CHAR(1) := '$';
loop_count Binary_Integer := 1000;
BEGIN
SELECT item, cost
[code]....

View 5 Replies View Related

Client Tools :: Unable To Compile The Triggers?

Jan 15, 2013

i have huge sets of triggers, when i try to compile any one of those it notifies me with below error

Error: ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg1], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

View 3 Replies View Related

Precompilers, OCI & OCCI :: How To Compile Code To SO File

Nov 19, 2010

I wrote the below C code and I don't How can compile to so file

/* Include standard IO. */
#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>
[code].......

View 3 Replies View Related

Mview Always Showing Status Needing Compile?

Sep 14, 2012

we have a routine which refreshes matrerilized view every day

dbms_mview.refresh('MVIEW_NAME>,'C') refresh ok but in user_ mviews always has status needing compile afterwarsd when compile isfine.

using 11.2.0.3

View 2 Replies View Related

PL/SQL :: Can't Compile And Execute Function (Create Or Replace)

Jun 6, 2013

I cant compile & execute this function.

create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc     
[code]....

View 6 Replies View Related

Precompilers, OCI & OCCI :: Convert Compile (o) Files To C Code?

Oct 28, 2010

I compiled my c program to o file using the flowing command,

$gcc first.c -o first.o

the source code was lost.

How can I convert the o file to C code?

View -1 Replies View Related

Precompilers, OCI & OCCI :: How To Compile Simple PRO*C Code In Linux

Nov 6, 2008

Linux 2.4.21-37.ELsmp #1 SMP Wed Sep 7 13:28:55 EDT 2005 i686 i686 i386 GNU/Linux...As you'll see I do not understand anything about PRO*C. I need to compile this PRO*C code to use it to retrieve DBMS PIPES being sent.

I'm trying to compile it using the following command:
gcc -o IAPIPE IAPIPE.c

The code - file IAPIPE.c
#include <stdio.h>
#include <string.h>

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
int status;
int msg_length;
char retval[2000];
EXEC SQL END DECLARE SECTION;
[code]....

View 3 Replies View Related

Reports & Discoverer :: Compile RDFs / REPs Not Generating

Oct 24, 2011

When i am trying to compile the rdf's, the reps's are not generating. Also while compiling these reports everytime it is asking to add the libraries and it is successfully compiling but the rep's are not generating at all.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Unable To Compile Pro C Code With Oracle 11g In Windows Env

Jul 15, 2010

I am trying to compile a .pc file. I am working with Oracle 11gR1 and windows env. I am able to create .c file from .pc file. I am able to produce .o file from .obj file. But i am not able to link it and produce .exe. My installation does not contain / precomp /demo folder.

View 7 Replies View Related

Precompilers, OCI & OCCI :: Compile ProC File - Failed Assertion

Aug 13, 2010

I am trying to compile a pro c file. Rest of the pro c file is compiling fine. But one particular file is showing following error.

INTERNAL ERROR: Failed Assertion [Code=40706]

View 11 Replies View Related

Create Trigger To Execute A Stored Procedure During Source Code Compile?

Nov 9, 2009

I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.

View 1 Replies View Related







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