URGENT - Can you coalesce a RBS tablespace?

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

URGENT - Can you coalesce a RBS tablespace?

URGENT - Can you coalesce a RBS tablespace?

2005-09-06 - By Allen, Brandon

Reply: 1 2 3 4 5 6 7 8 9 10 >>

I've never had any problems with OPTIMAL (not optimum) in 8.1.7.4 (on HP - not
sure about Solaris). Keep in mind that OPTIMAL only shrinks the RBS when a
subsequent transaction causes inspection of the extended RBS again - it doesn't
"snap" back to it's optimal size as soon as a long transaction completes - here
is a description from the 9i doc:

When you create or alter a rollback segment, you can use the storage parameter
OPTIMAL (which applies only to rollback segments) to specify the optimal size
of the segment in bytes. If a transaction needs to continue writing rollback
information from one extent to another extent in the rollback segment, Oracle
compares the current size of the rollback segment to the segment's optimal size
. If the rollback segment is larger than its optimal size, and if the extents
immediately following the extent just filled are inactive, then Oracle
deallocates consecutive nonactive extents from the rollback segment until the
total size of the rollback segment is equal to or close to, but not less than,
its optimal size. Oracle always frees the oldest inactive extents, as these are
the least likely to be used by consistent reads.

-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On
Behalf Of Mercadante, Thomas F (LABOR)
Sent: Tuesday, September 06, 2005 10:30 AM
To: Chirag DBA
Cc: jhenslee@(protected); Oracle-L Freelists
Subject: RE: URGENT - Can you coalesce a RBS tablespace?

Optimum doesn't work too well in 8.1.7.4 (like not at all)

Shrinking them by hand is certainly not the long-term answer. Re-writing the
offending sql is the long-term answer.

__ __

From: Chirag DBA [mailto:chiragdba@(protected)]
Sent: Tuesday, September 06, 2005 1:16 PM
To: Mercadante, Thomas F (LABOR)
Cc: jhenslee@(protected); Oracle-L Freelists
Subject: Re: URGENT - Can you coalesce a RBS tablespace?

Try setting the OPTIMUM parameter, which will reduce the size of yr segment
after finishing the transaction. That can help you to get through.

But if the transactions are long, they can generate 1555 erros in yr trace
files.

Look for this too.

Regards - Chirag

On 9/6/05, Mercadante, Thomas F (LABOR) < Thomas.Mercadante@(protected)>
wrote:

Jeff,

Have you shrunk the existing rollback segments? You may have some that
are huge in size. You can shrink them and this will free up space in
the RBS tablespace.

Tom

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto: oracle-l-bounce@(protected) <mailto:oracle-l-bounce@(protected)>
] On Behalf Of Henslee, Jeff
Sent: Tuesday, September 06, 2005 12:55 PM
To: Oracle-L Freelists
Subject: URGENT - Can you coalesce a RBS tablespace?

We seem to be having troubles with some of our RBS segments. We are
unable to create new segments or extend existing ones. This is an
unusual pattern and we're trying to find a way to get people back
working again without having to add a datafile to the RBS area (if at
all possible) or restart the instance.

Options please?

Thank you.

Jeffrey C. Henslee (Chico)
Six Sigma Green Belt
Wausau Window and Wall Systems
(715) 846-3196
mailto: jhenslee@(protected) <mailto:jhenslee@(protected)>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 6.00.2800.1515" name=GENERATOR><o:SmartTagType name="place"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o
:SmartTagType
name="City"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o
:SmartTagType
name="PersonName"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType>
<STYLE>@(protected) {
font-family: Tahoma;
}
@(protected) Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV>I've
never had any problems with OPTIMAL (not optimum) in 8.1.7.4 (on HP - not sure
about Solaris).  Keep in mind that OPTIMAL only shrinks the RBS when a
subsequent transaction causes inspection of the extended RBS again - it doesn't
"snap" back to it's optimal size as soon as a long transaction completes - here
is a description from the 9i doc:</DIV>
<DIV> </DIV>
<DIV>When you create or alter a rollback segment
,
you can use the storage parameter <CODE>OPTIMAL</CODE> (which applies only to rollback segments) to specify the optimal size of the
segment in bytes. If a transaction needs to continue writing rollback
information from one extent to another extent in the rollback segment, Oracle
compares the current size of the rollback segment to the segment's optimal size
.
If the rollback segment is larger than its optimal size, and if the extents
immediately following the extent just filled are inactive, then Oracle
deallocates consecutive nonactive extents from the rollback segment until the
total size of the rollback segment is equal to or close to, but not less than,
its optimal size. Oracle always frees the oldest inactive extents, as these are
the least likely to be used by consistent reads.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left>-- --Original Message-- -- From:
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On
Behalf Of Mercadante, Thomas F (LABOR) Sent: Tuesday, September
06, 2005 10:30 AM To: Chirag DBA Cc:
jhenslee@(protected); Oracle-L Freelists Subject: RE: URGENT -
Can you coalesce a RBS tablespace? </DIV>
<DIV class=Section1>
Optimum doesn&#8217
;t work
too well in 8.1.7.4 (like not at all)<o:p></o:p>
<o:p> </o:p>
Shrinking them by
hand is certainly not the long-term answer.  Re-writing the offending
sql
is the long-term answer.<o:p></o:p>
<o:p> </o:p>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center>
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</DIV>
From: Chirag
DBA [mailto:<st1:PersonName w:st="on">chiragdba@(protected)</st1:PersonName>]
 Sent: Tuesday, September 06
,
2005 1:16 PM To: Mercadante,
Thomas F (LABOR) Cc:
jhenslee@(protected); Oracle-L Freelists Subject: Re: URGENT - Can you coalesce a
RBS tablespace?<o:p></o:p></DIV>
<o:p> </o:p>
<DIV>
Try setting the OPTIMUM parameter, which will reduce
the size of yr segment after finishing the transaction. That can help you to
get through.<o:p></o:p></DIV>
<DIV>
 <o:p></o:p></DIV>
<DIV>
But if the transactions are long, they can generate
1555 erros in yr trace files.<o:p></o:p></DIV>
<DIV>
 <o:p></o:p></DIV>
<DIV>
Look for this too.<o:p></o:p></DIV>
<DIV>
 <o:p></o:p></DIV>
<DIV>
Regards - Chirag
  <o:p></o:p></DIV>
<DIV>
On 9/6/05, Mercadante, Thomas F (LABOR) <<A
href="mailto:Thomas.Mercadante@(protected)">Thomas.Mercadante@(protected)
.state.ny.us</A>>
wrote: <o:p></o:p>
Jeff, Have you shrunk the existing rollback
segments?  You may have some that are huge in
size.  You can shrink them and this will free up space in the
RBS tablespace. Tom -- --Original Message-- -- From: <A
href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)</A>
 [mailto:<A
href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)
</A>] On Behalf Of Henslee, Jeff Sent: Tuesday, September 06, 2005 12:55
PM To: Oracle-L Freelists Subject: URGENT - Can you coalesce a RBS
tablespace? We seem to be having troubles with some of our RBS
segments.  We are unable to create new segments or extend
existing ones.  This is an unusual pattern and we're trying to
find a way to get people back working again without having to add a
datafile to the RBS area (if at all possible) or restart the instance.
 Options please? Thank you. Jeffrey C. Henslee (<st1
:City
w:st="on"><st1:place w:st="on">Chico</st1:place></st1:City>) Six Sigma
Green Belt <st1:City w:st="on"><st1:place
w:st="on">Wausau</st1:place></st1:City> Window and Wall Systems (715)
846-3196 mailto:<A
href="mailto:jhenslee@(protected)">jhenslee@(protected)
</A> -- <A
href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org
/webpage/oracle-l</A> -- <A
href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org
/webpage/oracle-l</A>
<o:p></o:p></DIV>
<o:p> </o:p></DIV></BLOCKQUOTE
></BODY>
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.</HTML>