| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: big-picture question
Sounds like you are making a classic confiusion for someone coming from a PC database (or SQL Server) background.
Whilst you can have as many databases as you want on a machine it may well be that what you want is one (or more) databases with multiple SCHEMAS within each.
The best way to think of a schema is the definition that was given on a
similar thread a while back
A schema is one of the following (but not both)
Under the first model users get to create/manage their own database objects and cannot (unless they want to grant the privilege) see each others tables etc. Under the second all objects are owned by one account and accessed either by other database accounts or by some custom authentication mode.
This sort of thing is covered (at some length sadly) in the Oracle Concepts documentation.
-- Niall Litchfield Oracle DBA Audit Commission UK "leegold" <leegoldno-spam_at_operamail.com> wrote in message news:8vfcto$hji$1_at_bob.news.rcn.net...Received on Wed Nov 22 2000 - 04:00:19 CST
> The script below is a bit advanced for me at my
> level - but I gather the answer is yes, a user(s)
> can implement their "whole ball of wax" along
> w/other users "whole ball of wax" in one instance
> of that mounted Local database.
>
> But it's also possible to create more than one
> database instance (via the script) and delimit things
> that way - but I don't see this option/capability
> talked about in my intro texts. The 1st option -
> managing one database object for all you and other
> users would want to do, is what I see talked about.
>
> So then, e.g.. if one (1) database object. Then how do we
> say: my database is named "Grandma's Store
> Database". How does one cruise over to Grandma's db
> from the many other databases. I don't want a
> connection to 1000's of other tables, even if my
> roles and privilege do not allow it (rhetorically
> speaking). Reasking, let's say we have a PC for rent
> w/20 (UNRELATED) user's databases on it - each dB a
> mass of tables, relations, etc. What is the
> borderline BETWEEN each user's database? Is it table
> ownership? - i.e.. userA can make drop delete what
> ever...but userA can not change or SEE any table
> outside his/her ownership privileges. Now we call
> that userA's database.(?)
>
> I won't beat this question to death, I'm sure it
> will become clearer as I get more into relational
> dbs. But you answers have helped alot.
>
>
>
>
> "Frank van Bortel" <fbortel_at_home.nl> wrote in message
> news:3A240E59.EF71F1A3_at_home.nl...
> > Some of the picture must have been too small <g>: the
> > poster is using PO7.3 - so oradim80 should read oradim73,
> > and svrmgr30 should be svrmgr23.
> >
> > Furthermore does your system tablespace datafile look rather large;
> > 40M will do for 7.3PO.
> >
> > And creating a rollback segment in the system TS is not required since
7.2.
> > You can safely skip that, create your tablespaces, rollback segments,
alter
> > sys and system users to use temp as temporary tablespace, and user
system
> > to use system (or tools) as default tablespace, and run catalog and
catproc
> > then.
> >
> > Other than that, quite a useful script, altho I would have chosen
different
> > storage clauses but that is beyond the question.
> >
> > Tony Walby wrote:
> >
> > > You are eluding to a couple of things. First within one RDBMS you can
> > > have multiple (Thousands and thousands ) of schemas (User
repositories)
> > > this is the small slice of the pie that a user stores there objects
in.
> > > By default when you create a new user it is givin the system
tablespace
> > > to store data in. Not a good idea so you should create additional
> > > tablespaces following the OFA guidline which can be found on Oracles
web
> > > site.
> > >
> > > The second thing you elude to is multiple db's on a single machine.
You
> > > are alowed as many as your liscence and or disk space allow. Here is
how
> > > you do this, I am assuming you are running NT. If not there will be
> > > slight variations.
> > >
> > > 1. Create Services
> > > set ORACLE_SID=jusp # Set your sid in DOS I called mine jusp
> > > F:\ORANT805\bin\oradim80 -new -sid jusp -intpwd justpd -startmode auto
> > > -pfile G:\ORACLE\admin\justpd\pfile\initjustpd.ora #f:\orant805 is
where
> > > I installed Oracle G:\oracle\admin is where my pfile is
> > > F:\ORANT805\bin\oradim80 -startup -sid jusp -starttype
srvc,inst -usrpwd
> > > justpd -pfile G:\ORACLE\admin\justpd\pfile\initjustpd.ora
> > > These directories and files must exist. For the pfile you can copy
and
> > > rename the one from your default db my SID is jusp and database name
is
> > > justpd
> > >
> > > 2. Connect internal to the idle instance
> > > set oracle_sid=jusp
> > > svrmgr30 in 8i its svrmgrl
> > > connect internal
> > > password: justpd
> > >
> > > startup nomount pfile=G:\ORACLE\admin\justpd\pfile\initjustpd.ora
> > > create database "justpd"
> > > maxdatafiles 128
> > > maxinstances 1
> > > maxlogfiles 8
> > > maxlogmembers 4
> > > noarchivelog
> > > character set WE8ISO8859P1
> > > datafile
> > > 'G:\oradata\justpd\justpd_system01.dbf' size 80M
> > > logfile
> > > group 1 ('G:\oradata\justpd\justpd_redo0101.log'
> > > ,'H:\oradata\justpd\justpd_redo0102.log') size 1M,
> > > group 2 ('G:\oradata\justpd\justpd_redo0201.log'
> > > ,'H:\oradata\justpd\justpd_redo0202.log') size 1M;
> > >
> > > The directories must be there the create command creates the .dbf and
> > > .log files the init file must exist
> > >
> > > 3. Run the catalogues
> > > create rollback segment r00 tablespace system
> > > storage (initial 100k next 100k minextents 2 maxextents 100);
> > > alter rollback segment r00 online;
> > >
> > > @F:\orant805\rdbms80\admin\catalog.sql
> > > @F:\orant805\rdbms80\admin\catproc.sql
> > > @F:\ORANT805\Rdbms80\admin\caths.sql
> > >
> > > rem oracle replication
> > > rem @f:\ORANT805\Rdbms80\admin\catrep.sql
> > >
> > > alter package dbms_snapshot compile body;
> > > @F:\ORANT805\RDBMS80\ADMIN\CATBLOCK.SQL
> > > @F:\ORANT805\RDBMS80\ADMIN\catsnmp.sql
> > >
> > > 4. Create tablespaces
> > > alter tablespace SYSTEM
> > > default storage (initial 100K next 100K minextents 1 maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace USERS
> > > datafile
> > > 'G:\oradata\justpd\justpd_users01.dbf' size 10M
> > > AUTOEXTEND ON NEXT 1M MAXSIZE 50M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace TOOLS
> > > DATAFILE
> > > 'G:\oradata\justpd\justpd_tools01.dbf' size 10M
> > > AUTOEXTEND ON NEXT 1M MAXSIZE 20M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace RBS
> > > datafile
> > > 'H:\oradata\justpd\justpd_rbs01.dbf' size 100M
> > > default storage (initial 1024k
> > > next 1024k
> > > minextents 2
> > > maxextents 121
> > > pctincrease 0);
> > >
> > > create tablespace TEMP
> > > datafile
> > > 'H:\oradata\justpd\justpd_temp01.dbf' size 100M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 121
> > > pctincrease 0)
> > > Temporary;
> > >
> > > create tablespace DES2_DATA
> > > DATAFILE
> > > 'G:\oradata\justpd\justpd_des2_data.dbf' SIZE 50M
> > > AUTOEXTEND ON NEXT 5M MAXSIZE 100M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace RIMS_DAT
> > > DATAFILE
> > > 'G:\oradata\justpd\justpd_rims_dat.dbf' SIZE 100M
> > > AUTOEXTEND ON NEXT 50M MAXSIZE 500M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace DES2_INDEX
> > > DATAFILE
> > > 'H:\oradata\justpd\justpd_des2_index.dbf' SIZE 20M
> > > AUTOEXTEND ON NEXT 5M MAXSIZE 50M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace DES2_TMP
> > > DATAFILE
> > > 'H:\oradata\justpd\justpd_des2_tmp.dbf' SIZE 1M
> > > AUTOEXTEND ON NEXT 1M MAXSIZE 50M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace OEM1_DATA
> > > DATAFILE
> > > 'H:\oradata\justpd\justpd_oem1_data.dbf' SIZE 10M
> > > AUTOEXTEND ON NEXT 5M MAXSIZE 50M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > create tablespace RIMS_IDX
> > > DATAFILE
> > > 'H:\oradata\justpd\justpd_rims_idx.dbf' SIZE 20M
> > > AUTOEXTEND ON NEXT 10M MAXSIZE 200M
> > > default storage (initial 100k
> > > next 100k
> > > minextents 1
> > > maxextents 100
> > > pctincrease 0);
> > >
> > > alter user sys temporary tablespace TEMP;
> > > alter user system default tablespace USERS;
> > > alter rollback segment "R00" offline;
> > >
> > > This was setup for justpd you will need to rename and resize for your
> > > needs
> > >
> > > 5. Log on with sqlplus and create rollback segments.
> > > alter rollback segment r01 offline;
> > > drop rollback segment r01;
> > > create rollback segment r01
> > > tablespace system
> > > storage (initial 100K
> > > next 100K
> > > optimal 500k
> > > minextents 2
> > > maxextents 121);
> > >
> > > alter rollback segment rbs01 offline;
> > > drop rollback segment rbs01;
> > > create rollback segment rbs01 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs01 online;
> > >
> > > drop rollback segment rbs02;
> > > create rollback segment rbs02 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs02 online;
> > >
> > > drop rollback segment rbs03;
> > > create rollback segment rbs03 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs03 online;
> > >
> > > drop rollback segment rbs04;
> > > create rollback segment rbs04 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs04 online;
> > >
> > > drop rollback segment rbs05;
> > > create rollback segment rbs05 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs05 online;
> > >
> > > drop rollback segment rbs06;
> > > create rollback segment rbs06 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs06 online;
> > >
> > > drop rollback segment rbs07;
> > > create rollback segment rbs07 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs07 online;
> > >
> > > drop rollback segment rbs08;
> > > create rollback segment rbs08 tablespace RBS
> > > storage (initial 1M
> > > next 1M
> > > optimal 2M
> > > minextents 2
> > > maxextents 121);
> > > alter rollback segment rbs08 online;
> > >
> > > Before logging onto sqlplus you need to add an entry in your
> > > tnsnames.ora file and you listener.ora file you also need to start and
> > > stp your listener.
> > >
> > > Hope this helps.
> > >
> > > Tony Walby
> > >
> > > leegold wrote:
> > >
> > > > real newbie big-picture question: using ver
> > > > 7.3 - a personal/educational ver.
> > > >
> > > > I have a "local database". This is the default
> > > > database I got w/my installation and there are
> > > > of course sample users tables etc.
> > > >
> > > > can I create another local database? e.g.
> > > > local_db2 ? It's an Oracle object - right?
> > > > Therefore I should be able to create another
> > > > instance, or is it one db per local machine?
> > > >
> > > > Trying to see what's "going on here"... as it
> > > > is, the delimiter between database
> > > > implementations just seems to be a user's
> > > > ownership, connections, projects - i.e.. I own
> > > > these tables, these tables are related - these
> > > > tables I own on "LocalDatabase" is my
> > > > "database".
> > > >
> > > > I want to make my own new database instance
> > > > and be able to put it on another pc w/oracle.
> > > > What's the "big picture"? What am I not
> > > > seeing?
> > > >
> > > > Thanks
> >
> > --
> > Gtrz,
> >
> > Frank van Bortel
> >
> >
>
>
 |
 |