there WERE no active transactions against that tablespace. The steps I took were:
as system: 1) create tablespace as an LMT 2) create table within that tablespace 3) attempt to make the tablespace read-only when that hung I logged out (which certainly killed any active transactions against that tablespace!) 4) log back in as / as sysdba 5) attempt to make that tablespace read-only No one else knows about that tablespace, it's brand-new. No one else has quota or access on the table I created. However, for completeness, I just offlined and onlined that tablespace, then tried to make it read only. It's still hanging. Oh yeah, 9.2.0.1 on Linux Rachel --- Arup Nanda <[EMAIL PROTECTED]> wrote: > Rachel, > > A TS can't become read only if there are active transactions against > it. You > must wait till they all finish or kill them. > > A word of advice - if you decide to kill the sessions, bring the > tablespace > offline and then online to flush the buffers to disk. This will > ensure that > the delayed block cleanout will not occur and the contents will never > be > searched in an undo segment. This is not absolutely necessary, nor is > docuemnted anywhere, but in an active system I have seen ORA-1555 > problems > surfacing. > > HTH. > > Arup > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, June 18, 2003 1:29 PM > > > > okay, am I missing something? > > > > I created an LMT. Created a table in it. Gave no one quota on the > > tablespace. > > > > did (both as system and sysdba) > > > > alter tablespace test_drop read only; > > > > > > and hung > > > > > > what did I forget to do? > > > > > > > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > > > that actually makes sense when you think about it, with one > question > > > -- > > > was the tablespace a dictionary-managed one or an LMT? > > > > > > If it was dictionary-managed, it makes perfect sense. The > metadata > > > about the table and the extents used in the tablespace are not > stored > > > IN that tablespace, so drop table would affect only the system > > > tablespace and the data dictionary. > > > > > > But in an LMT, the bitmap of extent usage is stored within the > > > tablespace itself, so I wonder if you could actually drop a table > > > from > > > a read-only lmt. > > > > > > Off to experiment...... > > > > > > Rachel > > > > > > --- Darrell Landrum <[EMAIL PROTECTED]> wrote: > > > > I haven't tested this but would imagine it entirely possible. > > > > What I wanted to throw out though, is somewhat of a related > > > caution. > > > > You can drop a table from a read only tablespace. I discovered > > > this > > > > in > > > > test, fortunately when I was finished testing with that table > and > > > > intentionally dropped it while the tablespace was in read only > > > mode. > > > > > > > > >>> [EMAIL PROTECTED] 06/18/03 09:49AM >>> > > > > Hi, > > > > > > > > I would like to know if it is possible and what the pitfalls > are if > > > > > > > I > > > > do > > > > the following. > > > > > > > > Partition a large table into partitions based on date. > > > > Data is only entered and read and never altered, so I would > like to > > > > move > > > > older partitions to read only tablespaces and possible read > only > > > > devices so > > > > the backup will be made quicker. > > > > > > > > Is it possible to have parttions of the same table spread > across > > > read > > > > only > > > > and read/write tablespaces? > > > > Am I correct in assuming that once you backup a read only > > > tablespace > > > > there > > > > is no need to backup the same again. (provided you don't make > it > > > > read/write > > > > add data and make it read only again).? > > > > Does anybody have a procedure already that automatically > creates > > > the > > > > new > > > > partitions let say every month? > > > > > > > > > > > > TIA > > > > > > > > Jacob A. van Zanen > > > > Oracle DBA > > > > Quant Systems Europe b.v. > > > > Tel : +31 (0) 251 - 268 268 > > > > Mobile: +31 (0) 6 51308813 > > > > Fax: +31 (0) 251 - 268 269 > > > > E-mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > Visit our web site at http://www.quantsystems.nl/ > > > > <http://www.quantsystems.nl/> > > > > > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Darrell Landrum > > > > INET: [EMAIL PROTECTED] > > > > > > > > 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: [EMAIL PROTECTED] (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). > > > > > > > > > __________________________________ > > > Do you Yahoo!? > > > SBC Yahoo! DSL - Now only $29.95 per month! > > > http://sbc.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rachel Carmichael > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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). > > > > > > __________________________________ > > Do you Yahoo!? > > SBC Yahoo! DSL - Now only $29.95 per month! > > http://sbc.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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). > > > -- > === message truncated === __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).