If it is a TABLESPACE TEMPORARY, query on v$SORT_SEGMENT to
get the current size of the sort segment.
Then issue an ALTER TABLESPACE <tablespacename> DEFAULT
STORAGE (PCTINCREASE 0).
Re-query V$SORT_SEGMENT and the segment would have been released.
If there were current users in the
segment, you would still see a TEMPORARY segment in DBA_SEGMENTS, though,
and the disk-space would
not be released till those users log out.
Hemant
At 06:14 AM 09-10-03 -0800, you wrote:
All,
Is there a way to clear a TEMP
tablespace defined as Temporary? We had a couple of long-running
jobs that have totally clogged up the TEMP tablespace (54 Gig
worth) and it doesn't seem to be releasing the space. I know a db
restart will clear it.
Any other ideas?
8.1.7.4 by the
way.
thanks
Tom Mercadante
Oracle Certified Professional
- -----Original Message-----
- From: Loughmiller, Greg
[mailto:Greg.Loughmiller@cingular.com]
- Sent: Thursday, October 09, 2003 9:30 AM
- To: Multiple recipients of list ORACLE-L
- Subject: RE: SAME and separating disk and index
tablespaces
- Maybe we have been lucky. But we use the SAME methodology. We have
removed a considerable amount of "human effort" in regards to
layout of datafiles and disk layout. And based on the stats that I have
seen from the Storage team/SA's, we have *maybe* seen a 2-5%
performance hit in access time. That info needs to be confirmed-but the
disk technology has been improved so dramatically-that older "rules
of thumb" aren't necessary for *specific* environments. There
is still logical separation of tables/indexes as mentioned below.. But we
typically present a single file system for the
datafiles....
- Does this work in all cases for us - nope! But it covers a majority
of the environments and we address the *unique* environments
accordingly. But considering the number of databases and the volume
of disk space - more effective for us.
- just a comment:-)
- greg
- -----Original Message-----
- From: vikas kawatra
[mailto:vkawatra@comcast.net]
- Sent: Wednesday, October 08, 2003 8:29 PM
- To: Multiple recipients of list ORACLE-L
- Subject: RE: SAME and separating disk and index tablespaces
- Great responses ! Thanks very much ..
- -----Original Message-----
- Dave Hau
- Sent: Wednesday, October 08, 2003 3:19 PM
- To: Multiple recipients of list ORACLE-L
- Hi Gaja,
- I agree that throughput can always be improved by adding more drives to
- the striped array. However, this does not improve access time. If you
- have your tables and indexes on the same striped array, necessarily the
- two I/O's have to be done sequentially, incurring two times access time
- at a minimum. However, if you separate the two into different arrays,
- then you can access them in parallel, starting to get data from each
- disk array in 1* access time. This makes sense esp. in scenarios where
- response time is more important than throughput, and also in use cases
- where your access pattern is random rather than sequential.
- So I feel that there's a tradeoff between access time and throughput.
- If you have ten drives, and you stripe all of them into a single array
- and put both your data and indexes onto this array, you get maximum
- throughput but you're sacrificing access time for throughput. However,
- if you build two arrays each consisting of five drives, and put your
- data and indexes onto each array, you get half of the previous
- throughput, but you get better access time because now your data and
- index access can be truly in parallel.
- Regards,
- Dave
- oraperfman@yahoo.com wrote:
- > Hi Hans/Vikas,
- >
- > I tend to agree that the old draconian rule that "thou
- > shalt always separate indexes from tables" may not
- > apply any more. We used to apply that principle in the
- > past when the number of available spindles was not
- > adequate. Seems like with 256G drives in the market,
- > we are being pushed back in time, in some way!!!
- >
- > The way I look at the problem is purely from an IOPS
- > perspective. For example, if each physical disk is
- > capable of 256 IOPS (ignore the cache configured here)
- > and you have 10 disks in your volume, then the total
- > I/O capacity on this volume is 2560 IOPS. Separation
- > of objects across multiple volumes may becomes an
- > issue, only when the demand for I/O outstrips the
- > supply (in this case 2560 IOPS).
- >
- > Even then, you can always add more drives to the
- > existing volume and restripe, i.e., adding 5 more
- > drives to 10 drives increases the I/O capacity by 50%.
- > At the end of the day, the I/O sub-system does not
- > care, whether it is servicing a data segment, index
- > segment or undo segment.
- >
- > But, in certain environments, that I have dealt with,
- > there has been a need to separate heavily and
- > concurrently accessed objects (does not matter whether
- > these objects are all indexes or tables or both). This
- > may be true only for certain objects and certain
- > queries. So, please don't apply this in a blanket
- > fashion.
- >
- > Empirical data is always the best justification
- > mechnism for a configuration exercise such as this.
- > Plus, you may have partitioning and other requirements
- > such as parallelism that impact the placement and
- > availability of your data. This in turn will control
- > the number of logical volumes that need to be created.
- >
- > I think the idea and philosophy behind SAME is noble -
- > Use all available drives, so that you do not have
- > localized hot-spots. But the implementation of SAME
- > and how many volumes you need in your enviroment, is a
- > function of your custom needs based on your system and
- > application demands. When you over-simplify something,
- > you lose the flexibility. The art factor here (which
- > requires some planning) is in achieving a balance
- > between simplicity, flexibility, performance,
- > manageability and availability.
- >
- >
- > Hope that helps,
- >
- >
- > Gaja
- > --- Hans de Git <hansdegit@hotmail.com> wrote:
- >
- >>Vikas,
- >>
- >>Spend an hour on reading this usenet thread:
- >>
- >>
- >
- >
- http://groups.google.nl/groups?hl=nl&lr=&ie=UTF-8&oe=UTF-8&threadm=brjz8
- .15%24707.245%40news.oracle.com&rnum=1&prev=/groups%3Fhl%3Dnl%26lr%3D%26
- ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex
- >
- >>It will open your eyes about separating data/index.
- >>
- >>Still not sure about the redolog stream...Because of
- >>the sequential nature
- >>of redologfiles. I've read tests that 'prove' it
- >>doesn't matter much
- >>whether you separate your redolog from 'ordinary'
- >>datafiles or not. It does
- >>simplify things when you pure SAME.
- >>
- >>Regards,
- >>Hans
- >>
- >>
- >>Reply-To: ORACLE-L@fatcity.com
- >>To: Multiple recipients of list ORACLE-L
- >><ORACLE-L@fatcity.com>
- >>Date: Wed, 08 Oct 2003 09:54:30 -0800
- >>
- >>Thanks Gaja ! Does it also make sense from a
- >>performance perspective
- >>(I/O issues due to concurrent access of index and
- >>data ) to separate
- >>them or is that point moot once you apply the SAME
- >>methodology ?
- >>
- >>-----Original Message-----
- >>Gaja Krishna Vaidyanatha
- >>Sent: Wednesday, October 08, 2003 9:24 AM
- >>To: Multiple recipients of list ORACLE-L
- >>
- >>Vikas,
- >>
- >>The answer is an enthusiastic yes. This is purely
- >>from
- >>an administrative and manageability standpoint. For
- >>example, if you have INDEX and DATA segments
- >>separated
- >>in 2 different tablespaces, the backup of these
- >>tablespaces can be done INDEPENDENTLY. This is
- >>relevant, as if you were to rebuild your indexes
- >>using
- >>the NOLOGGING option between 2 backup jobs. If that
- >>were the case, then all you will need to do after
- >>the
- >>rebuild is complete, is to backup only the INDX
- >>tablespace.
- >>
- >>This is a best practice (if not a requirement) in
- >>most
- >>production shops, unless you think you can
- >>re-re-build
- >>your indexes in the event of media failure and you
- >>lose your INDX tablespace.
- >>
- >>
- >>Hope that helps,
- >>
- >>
- >>Gaja
- >>--- vikas kawatra <vkawatra@comcast.net> wrote:
- >> > Guys,
- >> >
- >> > Does it make sense to separate data and index
- >> > segments into separate
- >> > tablespaces if you create a single logical volume
- >> > and all files are
- >> > striped using the SAME methodology ?
- >> >
- >> > Thanks
- >> >
- >> > vikas
- >> >
- >> >
- >> > --
- >> > Please see the official ORACLE-L FAQ:
- >> > http://www.orafaq.net
- >> > --
- >> > Author: vikas kawatra
- >> > INET: vkawatra@comcast.net
- >> >
- >>
- >>
- >>=====
- >>Gaja Krishna Vaidyanatha
- >>Principal Technical Product Manager,
- >>Application Performance Management, Veritas
- >>Corporation
- >>E-mail : gaja@veritas.com Phone: (650)-527-3180
- >>Website: http://www.veritas.com
- >>
- >>__________________________________
- >>Do you Yahoo!?
- >>The New Yahoo! Shopping - with improved product
- >>search
- >>http://shopping.yahoo.com
- >>--
- >>Please see the official ORACLE-L FAQ:
- >>http://www.orafaq.net
- >>--
- >>Author: Gaja Krishna Vaidyanatha
- >> INET: oraperfman@yahoo.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@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.net
- >>--
- >>Author: vikas kawatra
- >> INET: vkawatra@comcast.net
- >>
- >>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@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).
- >>
- >>
- >
- > _________________________________________________________________
- >
- >>Chatten met je online vrienden via MSN Messenger.
- >>http://messenger.msn.nl/
- >>
- >>--
- >>Please see the official ORACLE-L FAQ:
- >>http://www.orafaq.net
- >>--
- >>Author: Hans de Git
- >> INET: hansdegit@hotmail.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@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).
- >
- >
- >
- > =====
- > Gaja Krishna Vaidyanatha
- > Principal Technical Product Manager,
- > Application Performance Management, Veritas Corporation
- > E-mail : gaja@veritas.com Phone: (650)-527-3180
- > Website: http://www.veritas.com
- >
- > __________________________________
- > Do you Yahoo!?
- > The New Yahoo! Shopping - with improved product search
- > http://shopping.yahoo.com
- --
- Please see the official ORACLE-L FAQ: http://www.orafaq.net
- --
- Author: Dave Hau
- INET: davehau123@netscape.net
- 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@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.net
- --
- Author: vikas kawatra
- INET: vkawatra@comcast.net
- 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@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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :
http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
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@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).
Received on Thu Oct 09 2003 - 10:24:29 CDT
