ON CALL DBA SUPPORT

— Database blog

CATPROC Oracle Database Packages and Types INVALID After upgrade

Posted by ssgottik on 17/06/2015

STEP 01:

For validating the catproc the following steps needs to be executed:

Step 01 a  –
SQL> shutdown immediate;

Step 01 b –
SQL> startup upgrade;

Step 01 c  –

conn / as sysdba
spool catalogproc.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
Column comp_id format a10
Column comp_name format a35
Column version format a12
Column status format a10
select comp_id, comp_name, version, status from dba_registry;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS<>’VALID’ ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS;
spool off

Step 01 d –
SQL> shutdown immediate;
SQL> startup;

STEP 02:

If step 01 doesn’t works :

Reference note:782735.1

Execute the following script repeatedly while connected as SYS AS SYSDBA and perform the indicated operation until it returns ‘CATPROC can be validated now’.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = ‘CATPROC’;
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line(‘Please compile Invalid object ‘||object_name||’
Object_id ‘||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘CATPROC can be validated now’ );
end;
/

Once all of the dependent objects have been recompiled, validate CATPROC by executing the following command:
execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

In my case even after running this CATPROC did not become VALID . So when I ran the below query

column object_name format a25

SQL > select object_name, object_type, owner from dba_objects
where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
—————————— ——————- ——————————
PLAN_TABLE                         TABLE                                            SYS
PLAN_TABLE                         SYNONYM                                     PUBLIC
TOAD_PLAN_TABLE          TABLE                                             DORIS
BMC_SQLEX_PLAN_TABLE_S02   TABLE                            SYSTEM
PLAN_TABLE                        TABLE                                             SYSTEM
SQL_PLAN_TABLE_TYPE   TYPE                                             SYS
SQL_PLAN_TABLE_TYPE   SYNONYM                                  PUBLIC
PLAN_TABLE$                           TABLE                                      SYS
TOAD_PLAN_TABLE             SYNONYM                                 PUBLIC

 

This issue is caused by existence of :
– table SYS.PLAN_TABLE$
– public synonym PLAN_TABLE for the SYS.PLAN_TABLE$.
– and possibly (not always present) table SYS.PLAN_TABLE

In order to prevent the issue from occurring it is necessary to drop before starting the upgrade process. :
– the SYS owned table PLAN_TABLE$
– the public synonym PLAN_TABLE
– the SYS owned table PLAN_TABLE if it exists

As the SYS user, drop the PLAN_TABLE$, the public synonym PLAN_TABLE and if it exists
the table PLAN_TABLE in SYS schema (SYS.PLAN_TABLE is not always there) :

If you receive the following error while attempting to drop the plan table:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

ensure that the cursor_sharing parameter is set to EXACT.

This can be done at the session level using ‘alter system set cursor_sharing=exact scope=memory;’
SQL> drop table plan_table$;

Table dropped.

SQL> drop public synonym plan_table;

Synonym dropped.

if SYS.PLAN_TABLE exists :

drop table plan_table;

SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS

Then re-create the PLAN_TABLE
$ sqlplus ‘/ as sysdba’
SQL> @?/rdbms/admin/catplan.sql — creates a public plan table as a global temporary table accessible from any schema
SQL> @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
SQL> @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
SQL> @?/rdbms/admin/prvtspa.plb — reload dbms_sqlpa (Note use prvtspao.plb if prvtspa.plb is not present).
SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
PLAN_TABLE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS
PLAN_TABLE$ TABLE SYS

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: