Re: ALTER TABLESPACE

The thrust of the original post was that TEMP TABLESPACE was insufficient and needed to be increased. I was not confident I could do that and not blitz the database and appealed to the newsgroup for advice, technique and technical references.

This is a summary of the responses. Kudos, acknowldegements, and great praise to Lun Wing San from Hong Kong; Thomas Kyte from Oracle Government Technology Group; and Simon Holt from Britannia Zinc for their cogent explanations.

The basics:
Temp tablespace exists for the creation of terporary segments during indix creation, sorting activity, etc. Each user must have access to a temporary tablespace. Temp tablespace should be large enough to handle all of the temporary jobs that come to it. A rule of thumb is to make the temp tablespace twice the size of the largest table.

If there are a lot of users, it may be advantageous to have multiple temporary tablespaces and divide the users equitably among them. Thus users 1-25 may have access TEMP_ONE, users 26-50 use TEMP_TWO, etc as needed. Be sure the division of users is based on tablespace usage and not simply numbers of people.

Finally, the command for the timorous DBA to ALTER TABLESPACE is

        ALTER TABLESPACE temp_space ADD DATAFILE <filespec>

My thanks to the contributors and my apologies if I've bollixed up the explanations.

Gus Spier

Gus Spier wrote:
>
> Merry Christmas to all -
>
> My outfit has recently won the contract to support a customer's Oracle
> database. They then turned around and pointed to ME and said, "Oh, by
> the way, you're the new DBA."
>
> We didn't design, build, or populate this database. I'm still
> discovering unpopulated tables that appear to be legacies from the
> database' original application.
>
> Now it appears that heavy user activity has increased the size of the
> tables beyond original expectations. Queries do not run and error
> messages to the effect that TEMP TABLESPACE cannot ?extend? are returned.
> It appears I need to increase the TEMP TABLESPACE.
>
> My informal contact with the previous custodians of this database says,
> "We just did an ALTER TABLESPACE and gave it a new file name." My
> cursory review of the tech manuals indicate that the problem may be more
> involved than that.
>
> My confidence level is low. Can someone please list the actions the
> well-read DBA would take to increase TEMP TABLESPACE? Or better yet,
> would the group cite the manual/chapter in the shelves of Oracle
> documentation that will give me the background and walk me through this
> process?
>
> Oracle 7.1.6.2 on DEC Alpha 2000 4/200 running DEC/OSF1.
>
> I truly appreciate any assistance. E-mail response preferred, I will
> summarize and repost to the group.
>
> Gus
>
> gus@
Received on Sun Dec 22 1996 - 00:00:00 CST