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.
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.
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 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,
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.
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?
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.
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).
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?
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.
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.
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.
