Appropriate Database Creation Steps

[bookouri]

Could someone give me a basic outline of the appropriate creation of a database, tablespaces etc..

For example, say I want to create a new employee database. I install Oracle 8.1.7. I could use the installation to create a startup database. Should I? Do I always need to run the Catalog.sql, pubbld.sql? If I create a database from scratch are there other things I need to do to prepare that database for use? I've used Oracle enough to make some guesses at this point, but Ive always worked with existing databases and applications.

Once Oracle is installed do I create a user/owner for application. Say emp_owner. I give emp_owner enough rights to create tablespaces? I logon as emp_owner and create the tablespaces as that user so they belong to him? or create the tablespaces as system or sys and give emp_owner access? Then logon as emp_owner and start creating tables... etc...???

I can look up the technical details/references, I dont need someone to build a system for me, but could someone please give me a good "real world" outline of how its done the right way. The inter-relationship of the Oracle, the database, the datafiles, tablespaces, who owns what.. etc.etc.. not from so much of a technical level but more a planning level... an outline...

thanks for any input....

 

[jcote]

First of all, from what I understand, I am not shure you need another "database". If your need is to have a database for this "employee" system, you should use an existing database, and just create a owner under which the database objects of this employee database will be created.

You usually create a database for each environnement you need (ex:development, testing, QA, production). In each of these database, you will have users, or owner, or schemas (they are all names used for the same purpose) under which database objects (tables, indexes, procedures, etc) will be created. In you case, you would have a user named employee_db instance, under which you would create all the database structures you need. I usually create a table tablespace and index tablespace for each user in the database that can have database objects created under him.
In my shop, I have also created a ROLE with all the access right needed for those users (create table, create index, quota unlimited on xxxxxx tablespace, etc).

Creating a new database is very well documented in Metalink, in the Oracle web site (under support). You just have to follow the steps described. Again, you will have to figure out if that is what you need.

Be aware that each database instance means a catalog, a set of redo logs, temporary tablespace, rollback segment tablespace, rollback segments, processes (PMON,SMON,...).
Those are all resources that are needed by a new instance of a database.

I hope this was helpfull. I also hope that I did not give you information you already knew. Don't hesitate to ask question again.

Jean Cote

 

[bookouri]

>>In each of these database, you will have users, or owner, >>or schemas (they are all names used for the same purpose) >>under which database objects (tables, indexes, >>procedures, etc) will be created.
>>I usually create a table tablespace and index tablespace >>for each user in the database that can have database >>objects created under him.
>>In my shop, I have also created a ROLE with all the >>access right needed for those users (create table, create >>index, quota unlimited on xxxxxx tablespace, etc).

Thanks, this is exactly the kind of information Im hoping to get. I can read all kinds of documentation and reference stuff, what I cant get from those sources is how people put things to work in their own world. Thanks again for the input..