gusdev-gusdev

[Gusdev-gusdev] DBA stuff

[pjm <pj...@sa...>]

Deborah,

our head DBA, Martin Widlake, has written a script (see below) that may need to
go into CVS. It created the six users for the six schemas. Also this quote from
Martin;

"I checked the view creation scripts and it looks like that for each view the
owner is stated but the owner of the table it is on is not. However, in every
case the table belongs to the same schema and no other schema will have that
table in it, so is should work fine."

Might be worth specifying the schemas?

I am about to run the table creation scripts and will let you know how I get on.
I have changed USER3 to be &1 so it can fit in with our stuff easily.

Paul.

-- cre_gus_new_users.sql
-- create the users needed for GUS30
-- Needs to be run from a suer which has create user system priv
-- (Run from a user with DBA role!)
--
drop user core cascade;
drop user corever cascade;
drop user dots cascade;
drop user dotsver cascade;
drop user sres cascade;
drop user sresver cascade;
--
set echo on
spool cre_gus_new_users.lst
create user core identified by core
temporary tablespace temp_01
default tablespace users
/
grant connect to core;
grant resource to core;
--
create user corever identified by corever
temporary tablespace temp_01
default tablespace users
/
grant connect to corever;
grant resource to corever;
--
create user dots identified by dots
temporary tablespace temp_01
default tablespace users
/
grant connect to dots;
grant resource to dots;
--
create user dotsver identified by dotsver
temporary tablespace temp_01
default tablespace users
/
grant connect to dotsver;
grant resource to dotsver;
--
create user sres identified by sres
temporary tablespace temp_01
default tablespace users
/
grant connect to sres;
grant resource to sres;
--
create user sresver identified by sresver
temporary tablespace temp_01
default tablespace users
/
grant connect to sresver;
grant resource to sresver;
spool off
set echo off
--

Re: [Gusdev-gusdev] DBA stuff

[<cra...@SN...>]

Hi Paul-

Thanks to Martin for creating that script; it occurred to me sometime yesterday
that you'd have to create the users first, but it slipped my mind before I got
a chance to do something about it.  It should definitely be added to CVS, although 
we'll probably want to do substitutions similar to those you've applied to the
CREATE TABLE script i.e., substituting &1 for 'temp_01' and &2 for 'users'.  It's
also possible that people will want to choose different passwords for the various
users.  Finally, something we haven't really considered yet is how we would set up
two distinct copies of "GUS 3.0" on the same Oracle instance.  In other words, what
naming convention should be adopted to 1. tell it apart from other copies of GUS 
3.0-compliant databases and 2. make it clear which Oracle users/schemas (core, 
corever, sres, etc.)  are part of the same [GUS 3.0 not Oracle] instance.

For example, here are a couple of possibilities:

1. A GUS 3.0 "development" database used to test new features:
    core-dev
    corever-dev
    sres-dev
    etc.

2. On second thoughts it's probably better to use a common prefix for sorting
   purposes:
     dev-core
     dev-corever
     dev-sres
     etc.

It makes things a little more verbose but otherwise I don't see how we're going to
be able to maintain different GUS 3.0 instances in the same Oracle instance (which
I'm assuming is something we'll want to try at some point, if only to create an
empty test copy of the latest schema before checking it into CVS.)  I *believe* that
the Perl object layer should take the name of the "core" database as an argument,
and it should be able to figure out the names of the remaining dbs by looking at
core.tableinfo.  What does everyone else think about this?

Jonathan

-- 
Jonathan Crabtree
Center for Bioinformatics, University of Pennsylvania
1406 Blockley Hall, 423 Guardian Drive Philadelphia, PA 19104-6021
215-573-3115