Re: What is maximum size of an Oracle data base?

Bob Withers wrote:
>
> Can anyone tell me what the maximum size of a single Oracle data base is?
> I heard once that it was 250MB but then I hear people talk about multi-
> gig data bases. Is that a single data base or multiple linked data
> bases? I sure appreciate it is someone could clear this up for me.
>
> Thanks much,
> Bob
>

Your question implies that you have not spent much time with Oracle. Rather than give the short answer (since many will probably give that), I'll give the longer "concept" answer...

Look at it this way - not 100% accurate, but it certainly helps some get a better feeling for Oracle basics:

1. An INSTANCE is the set of processes (code, memory and CPU cycles) necessary to control one set of databases. In most current cases I have run across, there is a single instance. It can be seen as a 'database computer'.

Recent developments allow multiple instances - esp. across several computers - to work together. Conceptually equivalent to mutiple processors in a computer (eg: 2-4 Pentium Pro in a box).

2) A TABLESPACE is the (set of) files controlled by an instance. Look at that as a 'database disk' - all access to data is done via tablespace. It is fixed in size at create time and can only be resized through maintenance (ALTER command).

A tablespace can span one or more physical drives and each file can take as much of a drive as is desired - up to OS limits or Oracle limits. In older versions of Oracle, the limits were based on some structure limitations (block size and other factors) which tended to keep the Oracle side in the lower multi-Gig range. This has been removed in 7.3

An instance can have multiple tablespaces. Again there were limits, but they have been relaxed.

A tablespace is a useful I/O distribution mechanism.

3) A DATABASE is a collection of TABLES and other objects that are usually related. Normally they are owned by a single schema (userid).

Depending on the time of day, etc. you can view a database as a 'database path' or a database userid', similar to the 'path' or 'userid' in unix or other large multiuser OSs.

If there are limits to the number of objects a schema may own, or the number of schemas in a database, I have not hit them.

Some schemas I have seen own more than 700 tables scattered across 1-10 tablespaces. I have seen instances with 10+ of these schemas.

An instance will control at least one database and usually more. (Personally I look at the objects owned by SYS - equivalent to unix' root - to be a database, just the same as I view the OS of a computer to be a very special application.)

4) A TABLE is the basic unit of storage definition. Last I saw, a table could have 255 fields, one of which could be a 'LONG' (MEMO in some other databases) which can contain up to 2G of data.

Tables are stored in tablespaces and may be viewed as 'database files'.  

Tables are accessed using a 'schema.table_name' notation, just as files are frequently accessed via a 'path/file_name' notation. If you access the instance using the userid of the schema (equivalent to cd to path), you can drop the 'schema.' part of the access; similarily if you have appropriate synonyms (equivalent to adding the path in your $PATH) you can usually drop the 'schema.' part of the access.

Thus, putting it all together, an Oracle instance can be used to control several databases, each of which can be multi-gigabyte, and into the terabyte range.

Hope this (rather log-winded concept explanation) helps /Hans Received on Mon Oct 07 1996 - 00:00:00 CEST