Re: Create 1 or multiple databases for class

Sunny Siu wrote:
>
> We start using Oracle 7.2 for a class of 60 students this summer semester.
> Each student in the class is going to create its table and program in
> embedded SQL/C.
>
> option 1. Create database for each student.
>
> option 2. Create just one database for the whole class,
>
> --------------------------------------------------------------------
> Question 1. What are the pros and cons of option 1 and option 2?
>
> Question 2. In option2, can each student create table with the same name,
> like "CREATE TABLE LAB1 ...."? How about writing programs
> to access the database?
>
> Question 3. Any security issue I need to pay attention in option 2?
>
> Your suggestion is appreciated.
>
> --
> --Sunny Siu
> Unix system admin., ACS
> University of Texas at Arlington
> siu_at_uta.edu

Unless the class actually requires the students to do stuff like starting and stopping the database or modifying system parameters, I would strongly suggest that you create one database and have the students share it.

Every database that you create will have a certain amount of disk overhead for the system tablespace. If you create separate tablespaces for rollback segments and for temporary segments, those will also be duplicated. Sixty sets of those will add up to quite a bit of space.

Each database that you create will be opened by an instance. The instance consists of the shared memory and operating system background processes that are used to access the database. Sixty instances would be sixty SGAs ( a small SGA is about 4m, so that would mean you would need at least 240m of memory). Each instance has at least 4 background processes, which also take up memory and cpu resources.

Depending on the speed of your machine, it may take from 30 minutes to over an hour to create a database. Multiply that by 60, and to get an idea of how long it would take to set up 60 databases (although you could copy the database and rename it, which is faster but more complex). Creating a new user account in an existing database takes 10 seconds.

So, unless you have a huge machine and you want to spend a lot of time creating databases, keep it under a single database.

Oracle is designed to allow for multiple concurrent users. Users with normal privileges cannot access data from others users unless they are explicitly given access.

You can create the same table name under different user accounts. There are some object names that must be unique across the database, such as public synonyms and roles, but that shouldn't mess you up.

Hope this helps

Bob Swisshelm
Eli Lilly and Company Received on Fri Jun 14 1996 - 00:00:00 CEST