Error at "spacechk_ini" phase with SQL ERROR 942: ORA-00942

[Former Member]

Dear support,

We are performing upgrade from<b> 4.0B</b> (4.0B Ext kernel level 996 , windows 2000 Oracle 9i ) to <b>4.7 Ext 2.00 SR1</b>. While performing Initialization module we got below error

" No DB freespace check possible code -4 "

When i analyzed slog found below error

1. Phase SPACECHK_INI:

ERROR: Statement: SELECT "TABLESPACE_NAME", "BYTES"/1048576 FROM SYS."DBA_TEMP_FILES" WHERE "BYTES" >= 1048576

ERROR: SQL ERROR 942: ORA-00942: table or view does not exist

ERROR: Statement: SELECT "TABLESPACE_NAME", "BYTES"/1048576 FROM SYS."DBA_TEMP_FILES" WHERE "BYTES" >= 1048576

ERROR: ERROR 256: invalid cursors id

ERROR: SQL ERROR 942: ORA-00942: table or view does not exist

Can anybody suggess something on the same at earliest ?

Best Regards,

AjitR

[Former Member]

which database version you are using exactly?

peter

[Former Member]

Peter ,

as mentioned in my post

We are performing upgrade from 4.0B (4.0B Ext kernel level 996 , windows 2000 <b>Oracle 9i</b> ) to 4.7 Ext 2.00 SR1. While performing Initialization module we got below error

we are on Oracle 9.2.0.5

[Former Member]

I expect two reasons for this.

1. your database catalog is not up to date. Can yo please rund sqlplus "/ as sysdba"

SQL> select comp_id, version from dba_registry;

2. the upgrade program R3up did expect that features are used which are not yet activated in your database (temporary tablespace)

SQL> select name from v$datefile;

is the temporary tablespace datafile listed here?

regards

Peter

[Former Member]

SQL> select comp_id, version from dba_registry;

COMP_ID VERSION

-

---

-

---

CATALOG 9.2.0.1.0

CATPROC 9.2.0.1.0

SQL> select name from v$datefile;

select name from v$datefile

*

ERROR at line 1:

ORA-00942: table or view does not exist

[Former Member]

Is it right that you have installed 9.2.0.5.0 patchset?

if so, you didn't run the post installation activities.

You have to run one or two SQL-Scripts after the installation of the patchset.

For more information refer to readme.html or similar documentation in the patchset root directory.

by the way, I would recomment to use 9.2.0.8 patchset and the latest minipatch on top of it.

regards

Peter

[Former Member]

Peter

If so i will perform the same 9.2.0.8 today itself.

Let me check the results.

Best regards,

AjitR

[Former Member]

Peter,

If i have already started my database now and now to perform post upgrade for 9.2.0.5 which activities out of below i should perform after i bring my SAP down.

Run the following commands and scripts in order from the Oracle home in migrate mode on all updated databases.

Run the startup migrate command.

Note:

If you are running Oracle Real Applications Clusters, set cluster_database in the initSID.ora file to FALSE prior to starting in migrate mode.

Run the spool patch.log command.

Run the @\rdbms\admin\catpatch.sql script.

Run the spool off command.

Review the patch.log file for errors, and rerun the catpatch.sql script after correcting any problems.

Run the shutdown command.

Run the startup command.

Run the ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql script.

This step is optional, and will recompile all invalid PL/SQL packages now instead of when the packages are accessed the first time. You can use the utlrcmp.sql script to parallelize this in multiprocessor computers.

Make sure that the database and database listener are running, and all oidpatchca.bat parameters are specified correctly before you run the script:

ORACLE_BASE\ORACLE_HOME\bin\oidpatchca.bat

-connect Connect String\

-lsnrport Listener Port\

-systempwd SYSTEM Password\

-odspwd ODS Password\

-sudn Super-User DN\

-supwd Super-User Password\

-dippwd Password to register DIP Server\

-windir %windir%

In the preceding command, Password to register DIP Server must conform to the password policy in the Oracle Internet Directory server.

Perform the following steps only if you are using the Oracle Recovery Manager catalog:

rman catalog user/passwd@alias

upgrade catalog;

upgrade catalog;

[Former Member]

Peter ,

I have performed post upgrade activities and i am getting ,

SQL> select comp_id, version from dba_registry;

COMP_ID VERSION

-

---

-

---

CATALOG 9.2.0.5.0

CATPROC 9.2.0.5.0

I have observed one more thing at my database ( which is now upgraded to 9.2.0.5 as prerequisite for upgrade to 4.7)

select * from dba_temp_files;

no rows selected.

But when i run same querry to my another server which is 4.7 oracle 9.2.0.5 i get output as list of datafiles with PSAPTEMP tablespace.

I think problem might be this fixed tables/view dba_temp_files is showing no rows as PSAPTEMP tablespace.Also we cannot add any value to this table as it is fixed tabel/view.

How we can troubleshoot this?

Best Regards,

AjitR

[Former Member]

I would also like to inform in my 40 B database PSAPTEMP tablespace is dictionary managed after upgrade to 9.2.0.5 but

In another server which is 4.7 9.2.0.5 , PSAPTEMP tablespace is locally managed

May be this is the problem why my dba_temp_files showing nothing.

Best regards,

AjitR

[Former Member]

>>select * from dba_temp_files;

>> no rows selected.

is quite different compared to your original problem:

>>ERROR: Statement: SELECT "TABLESPACE_NAME", "BYTES"/1048576 FROM SYS."DBA_TEMP_FILES" WHERE "BYTES" >= 1048576

>>ERROR: SQL ERROR 942: ORA-00942: table or view does not exist

the view does now exists and R3up (or prepare) should no longer despair caused of a non existing view definition.

peter

[Former Member]

Peter ,

But prepare logs indicationg the same as it is the problem for dba_temp_files

as i have checked all other things from my side.

What could be the problem.?

Do i need to perform DMTS to LMTS for psaptemp tablespace.so that my dba_temp_files view will show me psaptemp tablespace under it?

[Former Member]

Hi

Check your tablespace.. you need to have auto increment, but before that check your drive space too. Increase the space and retry, it should be ok..

Hope this helps

Cheers

Senthil

[Former Member]

Hi Ajju,

The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

Check each of the following:

the spelling of the table or view name.

that a view is not specified where a table is required.

that an existing table or view name exists.

Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.

Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted

Reward points if helpful.

Regards

Ganesh