Hello. At the moment I am trying to create a new database over the existing one. I heard about the limitations of the XE version more than once, but still it was a sin not to try to create a new database under a different instance name (oracle_sid = ORCL) as a training. What I actually did. Then I decided to try to query the system database tables of the ORCL instance, but here’s the error that this table does not exist and I don’t understand why:

select * from DBA_TABLESPACES; select * from DBA_TABLESPACES * ERROR at line 1: ORA-00942: table or view does not exist

So I thought that if you restart the Oracle service, which runs an instance of ORCL. I did, but the problem did not disappear. Therefore, then I tried to run a script that, as I understand it, creates all the standard system objects, tables, views, etc. It is called catproc.sql. I launched it. But some database objects were invalid for recording. Again, for some reason, some objects were created with errors. Therefore, in the hope that all objects will be overwritten, I decided to re-launch this script, but the following happened:

 SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catproc.sql DOC>###################################################################### DOC>###################################################################### DOC> The following PL/SQL block will cause an ORA-20000 error and DOC> terminate the current SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC>#

And I need, as it were, to remove everything that was created by the catproc.sql script, so that when it is restarted, it will overwrite all the system objects. How to do it ? And what should I do to ensure that all system objects, views, tables are recorded without errors? Please tell me, otherwise I don’t know where to dig.

  • And you under SYS podkkuchilis to the database? - Mike Nov.
  • @Mike, yes, yes. sqlplus sys as sysdba. Do you mean it? Or do you need to connect to sys in another way? - Minion Skywalker

1 answer 1

Guys.

It turns out that it is a solution. Usually, if you have launched a script with full-fledged creation of a database with system "good", not sequentially according to the instructions (For example, first you run catproc.sql when you had to create the object catalog first using the catalog.sql script, and then run the script catproc.sql), then to clear the database from this "good" I launched catupgrd.sql, which dumps all this by upgrading the database version to the same one so that you can run these scripts in the correct order without sending an ORA-20000 error. However, there is one thing. In order to upgrade your Oracle version from the same version to the same one, you need to have a different release number when upgrading, otherwise the next time you run the catproc.sql update script, the ORA-20000 will always fly out. This is not a very convenient way, but in this way at least you can erase several times the contents of procedures, packages, and other types of objects and features of the Oracle system of the current version several times instead of totally erasing the entire database and instance to recreate the newly added version of the product to the product folder and transfer your current database to it, so also pre-change the value of the path in the ORACLE_HOME environment variable.