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


ADVERTISEMENT

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

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

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

Convert SQL Server DB To Oracle 10g - Optimizing View And Its Objects?

Oct 8, 2010

I'm converting a SQL Server db to Oracle 10g. So far it's going pretty well. However, I've hit a significant performance snag trying to run queries against the converted view whose SQL is below. In SQL Server it runs pretty quickly, 10-15 secs. This query presently returns about 1.7 million records. code below, and assuming I've done nothing to optimize the database or the objects involved,

BS
------------------
CREATE VIEW DST_TMP_VIEW_ACCT_XACTN_CRN AS
SELECT CUSTACCNUM,
FUNDID,

[code]...

View 2 Replies View Related

Invalid Materialized View?

Aug 20, 2013

some of MVs become invalid (yes, we did app upgrade over the weekend). I looked on every object reference in the MV, all permissions - can't find anything.I dropped MVs and created them no errors. Status changed to VALID. I decided to re-COMPILE them (because I saw public synonyms for MVs as INVALID). After compilation status changed back to INVALID.Oracle 11.2, Solaris 10.

View 2 Replies View Related

Security :: How To Find Out Date View Status Changed To Invalid

Mar 10, 2011

We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.

View 4 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

SQL & PL/SQL :: Oracle View Have Dynamic View Function?

Jul 28, 2010

Are oracle view have Dynamic view function?

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

Aug 12, 2013

what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?

View 7 Replies View Related

SQL & PL/SQL :: Roles Given To Objects

Feb 27, 2011

I have granted execute, select, insert, update, delete privilege on objects to roles. Now i want to check status. we have around 2000 objects.

Require out put should be like this.

Object_name Object_Type Role_Granted
----------- ----------- ------------
Table1 Table ABC_ROLE
Table2 Table CDE_ROLE
PROCE1 PROCEDURE PROC_ROLE
Func1 FUNCTION FUN_ROLE

View 3 Replies View Related

SQL & PL/SQL :: Constraints On Objects?

Mar 5, 2012

Ive created a scenario to what im trying to achieve here so ignore how its set-up attribute wise.

CREATE TYPE object_obj AS OBJECT (
obj_id NUMBER,
NAME VARCHAR2(20),
age NUMBER) NOT FINAL;
/

CREATE TYPE object_ext UNDER object_obj (
course_name VARCHAR2(20));

/
CREATE TYPE object_ext2 UNDER object_obj (
location VARCHAR2(20);
/

CREATE TABLE object_tab OF object_obj(obj_id PRIMARY KEY);
/

Now for the course_name i need to make sure it can only be on of these three values ('Science','Math','English'). I can't find a way to apply this constraint without making a table. But then that creates another issue of how to insert the data from the main supertype (object_obj) and i only want the object_tab table and view the subtypes via a select query.

View 28 Replies View Related

Get SYS Objects Access?

Oct 26, 2010

What is the minimum access level ( or grant) needed be able to alter user defined (non sys or system) Stored Procedure in residing in SYS schema.

Example - User A need to alter SP sys.myStoredProc.

View 7 Replies View Related

PL/SQL :: Backup All DB Objects

Jun 19, 2013

I need a script to generate the source of every db object to an sql file with object name as file name, if it is a table then that_table.sql if view then that_view.sql like that, but my schema has around 2k objects. i am using toad but as the number of objects are more i am not able to generate source script files for all objects.

View 3 Replies View Related

PL/SQL :: What Are Objects Using PGA Memory

Feb 5, 2013

1)collections uses pga memory ,does globle tem table used in sp also uses PGA memory.

2)and does type of table of object type uses pga memory.

View 1 Replies View Related

Moving Objects From One Tablespace To Another?

Feb 4, 2013

I need to prepare script to move all objects from one tbs to another tbs. Should I move all the objects individually using "alter table" Command. I got all the objects information using "DBA_SEGMENTS" view.

I have more number of tables,indexes in that tablespaces.

I can not use exp for tablespace backup.

View 4 Replies View Related







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