Skip to content
April 8, 2013 / doganay

TOO MANY INVALID OBJECTS


Problem:

There are too many invalid objects (even thousands) and utlrp does not help.


Solution:

Check if an objects that has same name with schema name exists:

select * from dba_objects where owner=object_name and object_type not in (
'DATABASE LINK',
'INDEX',
'INDEX PARTITION',
'INDEX SUBPARTITION',
'TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'CLUSTER',
'DIRECTORY',
'SEQUENCE',
'SYNONYM'
);

If yes, create this trigger in order to not create any objects like this
and drop/rename existing ones:

--Mustafa DOGANAY 2013
--https://doganay.wordpress.com
CREATE OR REPLACE TRIGGER sys.trg_before_ddl_obj_eq_own
BEFORE DDL
ON DATABASE
BEGIN
IF (ora_dict_obj_owner = ora_dict_obj_name AND ora_sysevent = 'CREATE')
THEN
raise_application_error (
-20999,
'Creating Object that has same name with Schema Name not permitted');
END IF;
END;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: