...
VetView can be configured to work with either an Oracle 11g/ 12c databaseor newer.
Note |
---|
title | Server Hardware and Operating System Information |
---|
|
VetView does not require any specific brand or model of server or Operating System. The VetView web server uses a standard JDBC connection to the database. We recommend at least 4GB of available RAM for oracle to operate. More memory may be required dependent on your user load and transaction volume. - 4GB RAM available for Oracle ( minimum )
|
...
Oracle Installation
...
Info |
---|
Define Tablespaces for VetView tables, indexes, and Working space. We will also want to create a tablespace for temporary UVIS data used during the migration.
Currently the setup and migration scripts use 4 default tablespaces - DATA_SMALL - Permanent tablespace for VetView data
- DATA_INDEX - Permanent tablespace for VetView Indexes
- UVIS_TEMP - Permanent tablespace for UVIS data use in migration
- WORKSPACE - Temporary tablespace used for transactions
- DATA_CHARGE_ADMIN - Permanent tablespace for VetView Hospital data
The VetView application does not have any restriction on the usage of tablespace and data management. Tablespaces can be managed and organized by local DBA for optimization of data access. Code Block |
---|
language | sql |
---|
title | Example Create Permanent Tablespace |
---|
| CREATE TABLESPACE MASTER_DATA
DATAFILE 'D:\APP\JDUKE\ORADATA\TEST\MASTER_DATA.DBF'
SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/ |
Code Block |
---|
language | sql |
---|
title | Example Create Temporary Tablespace |
---|
| CREATE TEMPORARY TABLESPACE WORKSPACE
TEMPFILE 'D:\APP\JDUKE\ORADATA\TEST4\WORKSPACE.DBF'
SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/ |
Create VetView user account
The VetView application uses a single oracle account to connect to the database. The example script provided grants the VetView account rights to create and update all the objects needed to create and update the database schema. In a production environment the VetView user will only need SELECT, UPDATE and DELETE on tables and sequences. Code Block |
---|
language | sql |
---|
title | Example Create VetView Account |
---|
| CREATE USER VETVIEW IDENTIFIED BY <password> DEFAULT TABLESPACE DATA_SMALL TEMPORARY TABLESPACE WORKSPACE PROFILE DEFAULT ACCOUNT UNLOCK ;
-- 38 System Privileges for VETVIEW
GRANT CREATE ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ROLE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY VIEW TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY INDEX TO VETVIEW WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY INDEX TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY INDEX TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP TABLESPACE TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER TABLESPACE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE SESSION TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
GRANT GRANT ANY ROLE TO VETVIEW WITH ADMIN OPTION;
GRANT FLASHBACK ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE ANY VIEW TO VETVIEW WITH ADMIN OPTION;
GRANT INSERT ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT BACKUP ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT DROP ANY ROLE TO VETVIEW WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO VETVIEW WITH ADMIN OPTION;
GRANT MANAGE TABLESPACE TO VETVIEW WITH ADMIN OPTION;
GRANT CREATE TABLESPACE TO VETVIEW WITH ADMIN OPTION;
GRANT ALTER ANY ROLE TO VETVIEW WITH ADMIN OPTION;
GRANT SELECT ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
-- 2 Tablespace Quotas for VETVIEW
ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_SMALL;
ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_INDEX;
ALTER USER VETVIEW QUOTA UNLIMITED ON UVIS_TEMP;
ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_CHARGE_ADMIN; |
For migrating data from UVIS we have found the easiest way is to create a database link to a source UVIS database, and then importing data directly into the target VetView database. Code Block |
---|
language | sql |
---|
title | Example Database Link |
---|
| CREATE PUBLIC DATABASE LINK "UVIS.SOURCE.DATABASE"
CONNECT TO UVIS
IDENTIFIED BY <PWD>
USING '(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=1.0.0.1)
(PORT=1521)
)
(CONNECT_DATA=
(SID=UVISDB)
)
)';
|
|
...
Next Step
...