Re: Dbf Files, Mount Points and Oracle 7

"McGill, Wayne L" wrote:
>
> I am part-time DBA for an Oracle system that for reasons
> beyond our control is frozen at 7.3.4, at least for the
> immediate future. I am having some problems and I think
> I know how to correct them but I am asking the old-timers
> to dust off their brains to tell me if my proposals are
> O.K.
>
> It started when I noticed that one of the tables had gone
> into 2 extents. Normally when this happens, I (in a nutshell),
> make a copy of the table with larger storage parameters.

   Wayne, your being a part-time DBA is certainly a valid excuse, but it's totally useless.

> But for this table, I get the following error message:
>
> ORA-01658: unable to create INITIAL extent for segment
> in tablespace ORA_DATA1
>
> I also noticed that this system has 3 mount points. When I
> look at the capacity of the disks, the first is always
> 89 percent full and the other two are 1 percent. This is
> because all the DBF files for tablespaces were created on
> the first mount point. So all the other two have are small
> control and redo-log files.
>
> So what I would like to do to fix all of this is to move
> some of the tablespaces onto the other 2 mount points. I
> would also like to create a couple of new tablespaces, one
> to store two large application-related tables and another
> to store a large table that gets dropped and recreated
> everyday (a local copy of data from an external system).
>
> So my questions are:
> 1) Is there any reason to keep all the tablespaces on one
> mount point?

  Definitely not. There is a reason, however, to keep redo log files on separate disks but mainly when your applications are pretty insert/update/delete intensive.

> 2) I know about keeping table data and indexes in different
> tablespaces but can they also be on different mount points?

  The reason about keeping data and indexes in different tablespaces (a practice which recently took a fierce beating on this list) is precisely to spread I/Os across disks. In other words, apart from a certain air of cleanliness, there is no reason to keep data and indexes in different tablespaces if they are on the same mount point.  

> 3) Any reason for not putting my 1 large temp-table into its
> own tablespace?

  None.

> 4) If I can do all of this, will anything significantly change
> in Oracle 8, 9, 10, ...?

  A bit early to say about 10, but none to my knowledge.  

Before doing anything, I suggest you monitor I/Os at the OS level, and also check with V$FILESTAT (will only show you datafiles, not activity on redo logs - but the amount of redo written is easily visible in V$SYSSTAT). This will give you an idea about how distributing your files. Then shut your database down (not necessary mandatory but probably easier), COPY the files to their final destinations, do a STARTUP MOUNT, use ALTER DATABASE to rename files (see details in the SQL Reference) and open your database. Once you are confident that everything works properly, you can remove the old (moved) files. And take a backup of everything, control files included.

HTH, Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).