Common Database Objects
Installation Guide
version 1.0.1
2001 March 07
©2001 Ministry of Sustainable Resource Management,
BC Government, Canada
Overview
The Common Database Objects are Oracle database objects such
as tables, views, and packages that are shared between application systems,
and do not clearly belong to any single application. Examples of this would
be the CASE-generated help table (CG_FORM_HELP) and reference code table
(CG_REF_CODES).
Requirements
The Common Database Objects require the following:
Oracle RDMBS v.8.1.6 or better (earlier versions may work)
Print Utility Application
Module Version Application
To build and install the software, you will need access to the Oracle
system
account.
The instructions presented here are for a unix platform. With a few
small changes, they should work on a Windows platform.
Installation
Uncompressing the Release
This application is delivered in a compressed tarfile:
melp.1.0.1.tar.Z
Copy this file to the location in which you would like to extract the file.
If you are following Ministry standard directory structure, this directory
would be:
/apps_ux/melp/delivery/source/versions
Uncompress the file and extract it.
zcat melp.1.0.1.tar.Z | tar xvf -
This will create a directory called 1.0.0 in the current directory with
the following structure:
| 1.0.1 |
top level directory |
| 1.0.1/docs |
documentation and installation instructions |
| 1.0.1/scripts |
installation scripts |
If you using following Ministry standard directory structures, then
you should copy the files to their correct destinations and move to the
scripts directory.
cp 1.0.1/docs/* ../docs
cp 1.0.1/scripts/* ../scripts
cd ../scripts
If you are not following these standards, then simpley move to the scripts
directory in the structure that was just expanded:
cd 1.0.1/scripts
Full Build or Patch
If this system is already built, then all you need to do is perform the
patch to add a primary key constraint to the CG_REF_CODES table; go the
the PATCH section. If this is a full build of the system from scratch,
then continue.
Creating the Tablespaces
This application uses the two common "Ministry Tools" tablespaces: MOE_TOOLS_TABLES
and MOE_TOOLS_INDEXES. These tablespaces may already exist; if so, then
you can skip this step. If these tablespaces do not already exist, they
will have to be created as outlined before. Note that you may have to change
the location of the datafile for your system.
sqlplus system/<password>@<instance>
create tablespace moe_tools_tables datafile
'/fs/u01/oracle_data/envdlvr1/moe_tools_tables01.dbf' size 10 M
online
;
create tablespace moe_tools_indexes datafile
'/fs/u01/oracle_data/envdlvr1/moe_tools_indexes01.dbf' size 2 M
online
;
exit
Creating the MELP Account
This application is built in the melp schema. The following instructions
create this schema:
sqlplus system/<password>@<instance>
create user melp identified by <password>
default tablespace moe_tools_tables
temporary tablespace temp
quota unlimited on moe_tools_indexes
quota unlimited on moe_tools_tables
;
grant connect to melp;
grant create public synonym to melp;
grant drop public synonym to melp;
grant print_mgr to melp;
exit
Drop Any Existing Synonyms
You must delete any public synonyms that already exist for any of the objects
that are about to be created. Use the following command to list out any
public synonyms and to create the commands to drop them:
sqlplus system/<password>@<instance>
set heading off
set pagesize 0
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where synonym_name in ('CG_FORM_HELP','CG_REF_CODES')
and owner = 'PUBLIC'
;
Take the output generated from this query and run it - it will drop all
the public synonyms for the named objects.
You must then do a similar thing for all the private synonyms:
select 'drop synonym '||rtrim(owner)||'.'||synonym_name||';'
from dba_synonyms
where synonym_name in ('CG_FORM_HELP','CG_REF_CODES')
and owner != 'PUBLIC'
;
Likewise, you should take this output and run it to remove all the private
synonyms.
Building the Tables, Indexes, Constraints, Packages & Synonyms
Log on to Oracle with SQL*Plus as melp, and run the melp.sql
script to create all the objects. Output will be spooled to melp.lst
sqlplus melp/<password>@<instance>
start melp.sql
exit
There should be no errors produced except for removing the old entry from
the APP_STATE table if it does not exist.
Populating the Tables With Existing Information
Now comes the harder part - taking the information from the existing tables
and loading it into the MELP tables. We will deal with each of the objects
separately, although the technique is the same.
First, list out all occurances of the CG_FORM_HELP table that are not
in the MELP account. This script needs to be run as system to ensure
that we can see all the tables. Also note that the script will build the
insert commands for you.
sqlplus system/<password>@<instance>
set heading off
set pagesize 0
select 'insert into melp.cg_form_help select * from '||
rtrim(owner)||'.cg_form_help;'
from all_tables
where table_name = 'CG_FORM_HELP'
and owner != 'MELP'
;
You should carefully review the insert commands created to ensure that
there are no "unwanted" applications what will get loaded. When you are
happy, then you should run the commands to load the information.
Now do the same thing for the CG_REF_CODES table:
sqlplus system/<password>@<instance>
set heading off
set pagesize 0
select 'insert into melp.cg_ref_codes select * from '||
rtrim(owner)||'.cg_ref_codes;'
from all_tables
where table_name = 'CG_REF_CODES'
and owner != 'MELP'
;
Don't forget to COMMIT when you've loaded the information...
commit;
exit
Removing the Old Tables
Now that the information has been loaded into the new MELP tables, the
last step is to remove the old tables. Use the following script to create
the necessary table drop commands:
sqlplus system/<password>@<instance>
set heading off
set pagesize 0
select 'drop table '||rtrim(owner)||'.'||table_name||';'
from dba_tables
where table_name in ('CG_FORM_HELP','CG_REF_CODES')
and owner != 'MELP'
;
Patching an Existing System
If the MELP system has already been installed previously (ie: with 1.0.0)
then you will need to apply the patch portion of this build only.
Step 1
Check for duplicate entries that will fail with the constraint.
sqlplus melp/<password>@<instance>
set pagesize 0
set linesize 200
select substr(rv_domain,1,25), substr(rv_low_value,1,20), count(*)
from cg_ref_codes
group by rv_domain, rv_low_value
order by 3, 2, 1
;
Ignore the listings where the count is 1 - you will need to clean out any
duplicate entries where the count is 2or higher ('problem' records will
be at the end of the report). Use Oracle Query Builder (in data edit mode)
to look at the data and delete duplicate records.
Step 2
Remove index on CG_REF_CODES table (will get built as a primary key
in the next step)
sqlplus melp/<password>@<instance>
drop index x_cg_ref_codes_1;
exit
Step 3
Build the new Primary Key index on CG_REF_CODES
sqlplus melp/<password>@<instance>
@melp.con
exit
Known Bugs
At present, there are no known bugs.
Feedback
We welcome your feedback on this product. Please feel free to contact Oracle
Support - Information Management Branch .