Re: When to Create Database vs Schema only !!!

In article <4erug7$jsl_at_inet-nntp-gw-1.us.oracle.com>, "Bill Manry - Oracle Corp." <bmanry_at_ibmgate-aix.us.oracle.com> writes
>ddh (ddh_at_ev.state.az.us) wrote:
>>Bill, Thankyou for replying to my question. To followup...schema and
>>DB2 Database appear to be equivalent ( grouping of objects defined by a
>>high-level identifier ).
>>1. DB2...Start and Stop can be issued against the database. Is this
>>true with at the schema level, or only the instance level?
>
>You start and stop instances but not schemas. Actually "start"
>subdivides further into start (instance), mount (database), and
>open (database), but normally all three are done together. When
>you have started/mounted/opened the DB, all schemas are available
>unless they reside in offline tablespaces.
>
>>2. DB2...Tablespaces are defined as Simple, Segmented, or Partitioned.
>>Many issues relate to this choice. Is an ORA tablespace with mulitple
>>datafiles equivalent to a DB2 partitioned tablespace? Is there an
>>equivalent Segmented tablespace?
>
>The short answer is no; currently we do not have a mechanism
>for distributing data across files/devices based on logical
>(content) criteria. A degree of partitioning can be
>accomplished using a tablespace dedicated to one table and
>carefully choosing number and size of files based on table and
>freespace characteristics. But more commonly a tablespace has
>multiple files simply because the number and/or size of the
>tables within has increased.
>
>>3. If you define one big tablespace for several schemas how would one
>>recover just one or part of one schema? Is lowest level of
>>backup/recovery at the tablespace level?
>
>The lowest level of _media_ recovery is the tablespace. Media
>recovery works in conjunction with Oracle redo logging to
>allow recovery of a tablespace to the latest committed
>transaction or to a specific point in time.

Surely this is not strictly true. Oracle is clever enough to recover a partly damaged (i.e. one or more damaged files of a multi-file tablespace) tablespace. Simply by recovering the lost/damaged file(s) and rolling forward from the archived logs.

>
>There are other backup methods (such as export) which allow
>greater selectivity but generally this is slower and does not
>provide log-based recovery. With media backup and recovery we
>allow you to use OEM data movement products which often are
>highly optimized (eg: DF/DSS on MVS, Legato on UNIX, et al).
>Note that dedicating a tablespace to a single table as
>mentioned earlier is neither unreasonable nor unusual for
>large, critical tables.
>
>I think there is some decent overview material on these topics
>in the Oracle7 doc. Not written from a DB2 perspective, of
>course, but worth a browse.
>
>/b
>--
>Bill Manry - Mainframe and Integration Technologies - Oracle Corporation
>Standard disclaimer applies.

Clive Bostock

Senior Consulant
KPMG (Health Systems) Received on Sat Feb 03 1996 - 00:00:00 CET