Omega Technical Support

Drop the O2B User. Process Description

This is a note solely for the use of the database administrator. It is recommended that technical support is contacted before anything is done.

1. Configure the start-in directory of SQL*PLUS to \OMEGAPS\DBA
2. Log into sql*plus as the database administrator SYSTEM/PASSWORD
3. Type the following at the sql prompt: SQL> DROP USER O2B CASCADE;
4. When this is completed: SQL> DROP ROLE OMEGAPS_USER; SQL> COMMIT;

The user is now dropped along with everything he owned.
To Recreate the User O2B

1. Log into sql*plus as in 2. Above.
2. Type the following at the sql prompt: SQL> @ O2BUSER.SQL

Back to Frequently Asked Questions

Removing the User O2B and his Objects.
Using the import facility to import an oracle dump (export file)

All SQL commands are assuming that you are logged into SQL*PLUS as SYSTEM/PASSWORD and that you are working within the OMEGAPS\DBA directory. Any variations to this are noted.
Removing the O2B User

1. To drop the O2B user:
* SQL> DROP USER O2B CASCADE;
* [This will take some time - 1 1/2 to 2 hours depending on database size/machine spec]
2. To drop the role of the OmegaPS users:
* SQL> DROP ROLE OMEGAPS_USER;
* [Should be instantaneous]

We have now removed all OmegaPS' tables.
Re-creating the O2B User

1. Use the script supplied with the package:
* SQL> START O2BUSER.SQL;
* [We now have recreated the O2B user]

Importing the Dump File with O2B's Objects

1. Edit the file :\OMEGAPS\DBA\OPSIMP.DEF in the following fashion: (* is the drive on which OmegaPS is loaded)
1. Open the file in notepad. (Right click and Open in Windows Explorer).
2. This is an example of a typical ORACLE Import DEF file's contents:

userid=system/manager@omegaps
buffer=32767
file=opsimp.dmp
fromuser=o2b
touser=o2b
recordlength=4096
commit=y
grants=n
ignore=y
log=imp.log
3. Change the userid to reflect your installation (add @DATABASE if you use a connect string to connect to the ORACLE instance, where DATABASE is the connect string).
4. Change the file to the name of your export file (do not enter a path - see 2. below).
5. Make sure the fromuser and touser are set to O2B.
6. Change the buffer to 1000000 (one million).
7. Save as OPSIMP2.DEF in the OmegaPS\DBA directory.
2. Put the modified DEF file AND your export file into the OmegaPS\DBA directory. This renders no need for a path to confuse the issue.
3. Go to the DOS prompt.
4. Change directory to the drive on which OmegaPS is loaded. And then change to OmegaPS\DBA (From here on I will use C:).
5. Type the following C:\OMEGAPS\DBA>IMP73 PARFILE=OPSIMP2.DEF [This will take a while.]

Ensuring that Privileges and Synonyms are Set Correctly

For OmegaPS to run correctly for "real users" one must recreate all public synonyms and grant privileges.

1. Log onto SQL*PLUS as the O2B user.
2. Type the following : SQL> START OPS_GRNT.SQL;
3. Check the log files (OPSGRANT.LOG)
4. Type the following : SQL> START PUB_SYNS.SQL;
5. Check the log files (PUB_SYN1.LOG through PUB_SYN3.LOG)

If all is OK, then this process is complete.

NOTE: There is sometimes an error on the synonyms which involves lack of space on the SYSTEM tablespace. If this is the case, increase the tablespace and re-run the scripts. IT IS OK TO RUN THE SCRIPTS MORE THAN ONCE.

Back to Frequently Asked Questions

Upgrading the OmegaPS Database from one Version of Oracle and into a Later Version of Oracle

Please Note:
We do recommend that you contact the Oracle Worldwide Customer Support Group in the first instance.
There are two options available:
A - Upgrading the existing Oracle database to a later version (refer to the Oracle upgrade documentation).
B - Moving OmegaPS environment from an older version of Oracle and into a later version of Oracle (see below).

1. Export all the objects from the existing environment for the O2B user, with all the data (Using Oracle exp utility).
2. If required, de-install the existing Oracle RDBMS (refer to the Oracle documentation).
3. Install the latest version of the Oracle RDBMS (refer to the Oracle documentation).
4. Configure the Oracle database for OmegaPS (Refer to the OmegaPS Installation Document)
1. Oracle ini file.
2. Creating the O2B user.
3. Creating the OmegaPS users.
5. Import the exp file created from step 1 into the latest version of Oracle (using the Oracle imp utility).

This is an example of a typical ORACLE Import DEF file:

userid=system/manager@omegaps
buffer=32767
file=opsimp.dmp
fromuser=o2b
touser=o2b
recordlength=4096
commit=y
grants=n
ignore=y
log=imp.log
6. Ensuring that the Privileges and Synonyms are Set Correctly For OmegaPS to run correctly for 'real users' one must recreate all public synonyms and grant privileges.
1. Log onto SQL*PLUS as the O2B user.
2. Type the following: SQL>START REP_SYNS.SQL;
3. Type the following: SQL> START OPS_GRNT.SQL;
4. Check the log files (OPSGRANT.LOG);
5. Type the following: SQL> START PUB_SYNS.SQL;
6. Check the log files (PUB_SYN1.LOG through PUB_SYN3.LOG);
7. Type the following: SQL>START COMPILE.SQL;
8. Check the COMPILE.LOG file.

If all is OK, then this process is complete.

NOTE: There is sometimes an error on the synonyms and is due to a lack of space on the SYSTEM tablespace. If this is the case, increase the tablespace and re-run the scripts.
IT IS OK TO RUN THE SCRIPTS MORE THAN ONCE.
7. Grant access to OmegaPS users to OmegaPS role.
1. Log onto SQL*PLUS as the O2B user.
2. Type the following for each user:
SQL> grant omegaps_user to test;

Where test is the name of the OmegaPS user.

Back to Frequently Asked Questions