Re: big-picture question

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