Re: Datawarehousing using Oracle8i over multiple servers

Terabytes is fine - there are several Oracle databases that size in place already (I've designed a couple of them).

It depends how you expect to use the files but Oracle 8 gives you two main options (and Larry Ellison will love you for wanting to use Oracle as a file system) which incorporate files as data in the database.

1. BFILES - a structured way for Oracle to hold pointers to operating system files; but this allows users to delete the file from the operating system without Oracle knowing about it.
2. LOBs - Binary, or Character large objects where you transfer the files into the database. The LOBs are then just 'ordinary' data, subject to the usual protection, consistency etc. rules of Oracle.

When using LOBS (and you had better use 8.1 from the start) you are likely to end up with 3 tablespaces per 'set' of files -

1. Tablespace for the table holding the structured data and the 'LOB Locator'
2. Tablespace for the LOBs themselves
3. Tablespace for indexes into the structured table

Your thought of 'giving each user their own tablespace' is worth pursuing. In my comments above this equates to each user owning a 'set' of files - thus having their own table and 3 tablespaces.

In this scenario a hot backup could be taken one user at a time.

One of the problems with databases this large is the backup strategy - ideally you need to be able to make as much of the data read-only as possible so that the regular backup is as small as possible. Whether or not you can do this is a subject to user requirements, and this might lead to each user having two tables (e.g. current work, archived work) or more.

Obviously, if you use the BFILE option, you have a much smaller database, and the problem of backup is much more manageable, and yes you could then use NFS mounted discs for the actual files - but not for any part of the database itself.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

bhan_at_pointcast.com wrote in message <7r7m71$rk0$1_at_nnrp1.deja.com>...
>Thanks for you reply. What I mean by servers is actual machines. I am
>planning on storing terabytes of data in one single database. If you
>say that there is a limit, then I may just use a file system over NFS
>rather than storing these small files in the database.
>
>I pretty much want to create a system where a user can store all of
>their files. And a database would be perfect because we would be able
>to have corresponding fields in the DB with information about the file.
>The amount of files a user can store on this database is infinite, so
>the tablespace must be big. Would it be smart to just give each user a
>tablespace for themselves?
>
>I think using a DB that has records that point to an NFS tree would be
>smarter rather than storing the files in the Database.
Received on Thu Sep 09 1999 - 02:33:35 CDT