Re: Datawarehousing using Oracle8i over multiple servers

Jonathan,

Thanks for the great input on this topic. It seems like the bfile option would be easier to administer and scale. The lobs option sounds like the entire database including tablespaces needs to reside on one huge machine, and trying to "stripe" the database over multiple machines might not be manageable in this case. The bfile option will work better in my case I guess.

Thanks for the input again.

In article <936862801.21872.1.nnrp-02.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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.
>
> a) 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.
>
> b) 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 -
>
> a) Tablespace for the table holding the structured
> data and the 'LOB Locator'
> b) Tablespace for the LOBs themselves
> c) 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.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 09 1999 - 12:35:17 CDT