Oracle-L: Re:Dbf Files, Mount Points and Oracle 7

From: <dgoulet_at_vicr.com>
Date: Fri, 01 Nov 2002 12:59:39 -0800
Message-ID: <F001.004FA5ED.20021101125939@fatcity.com>

Wayne,

First off welcome into the late 20th century. When did NAVAIR move off of Oracle 5??

Second, the problem your seeing is that there is not one contiguous chunk of space in the tablespace that will hold the table with the new storage. But as another list member noted you don't need to be concerned about multiple extents anymore. Now, should you be concerned about having everything on one mount point, yes & no. Yes because if you loose that mount point for any reason you loose the entire database. I'd spread things out so that if one mount point dies unexpectedly the entire DB does not crash, but only one portion thereof. And having a temp table mixed in with your other data is not an admirable thing to do. So yes put it in it's own tablespace.

And YES, old USAF folk love pulling at Navy anchor chains. Gets one heck of a rise out of them!! *-)

Dick Goulet
(MSgt USAF(retired))

____________________Reply Separator____________________
Author: "McGill; Wayne L" <McGillWL_at_navair.navy.mil>
Date:       11/1/2002 11:13 AM

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. 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:

Is there any reason to keep all the tablespaces on one mount point?
I know about keeping table data and indexes in different tablespaces but can they also be on different mount points?
Any reason for not putting my 1 large temp-table into its own tablespace?
If I can do all of this, will anything significantly change in Oracle 8, 9, 10, ...?

I have read different manuals and references but I have not seen anything that indicates that I cannot do what I am proposing. I have tried this solution on a test 8.1.6 database and everything seems to be working.

TIA, Wayne
McGillWL_at_naviar.navy.mil
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: McGill, Wayne L
INET: McGillWL_at_navair.navy.mil

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------