I don't know, the system in which I
-alter tablespace sales_data read only
-test some queries
-drop table readtest
-alter tablespace read write
had 12+ sessions, and at least 3 were writing data, including one of my
own.
I'm not saying that what is being presented isn't true, just that you
still have the opportunity for this to work.
>>> [EMAIL PROTECTED] 06/18/03 06:25PM >>>
ARGH!
Well, that pretty much kills the idea of using this for my data
warehouse as there is always activity in it. Dang!
Okay, I'll try it from my laptop as I can control users there :)
- Daniel Fink <[EMAIL PROTECTED]> wrote:
> Rachel,
> It is not active transactions against that tablespace, it is
active
> transactions. Yup, period! As soon as all the active transactions
> complete, the tablespace will complete altering itself.
>
> Dan
>
> Rachel Carmichael wrote:
> >
> > 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
>
=== message truncated ===> begin:vcard
> n:Fink;Daniel
> tel;cell:303.808.3282
> tel;work:303.272.3225
> x-mozilla-html:TRUE
> adr:;;;;;;
> version:2.1
> email;internet:[EMAIL PROTECTED]
> title:DB Services Lead
> x-mozilla-cpt:;-4832
> fn:Daniel Fink
> end:vcard
>
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).
--
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).
Received on Wed Jun 18 2003 - 19:42:25 CDT
