cc:
Sent by: Subject: Re: Partitions of table read
only
[EMAIL PROTECTED
.
Darrell Landrum
[EMAIL PROTECTED]To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
p.com cc:
Sent by: Subject: Re: Partitions of table
read
Maybe because drop table is actually a modification of the data dictionary.
I recall that Oracle training thing where they have you start a long-running
select on a table in one session, then drop the table in another session,
and the select on the dropped table keeps on running OK.
It does seem
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 5:59 AM
Jack, maybe this has been covered. I seem to recall from the BR module
(knew it would prove useful sometime) that after you make a tablespace
read-only that you should
Ah... That's why the example helps... The text above the example is unclear
but the example is a little bit better...
If you find it is taking a long time for the tablespace to quiesce, it is
possible to identify the transactions which are preventing the read-only
state from taking effect.
much better -- now I understand :)
--- Johnston, Tim [EMAIL PROTECTED] wrote:
Ah... That's why the example helps... The text above the example is
unclear
but the example is a little bit better...
If you find it is taking a long time for the tablespace to quiesce,
it is
possible to
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
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
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
Jack,
We use the methods you are asking about. The partitions are created
and the table uses the range option to place the data in the correct
partition according to the date field. Each year I place the partition
in a read-only status and the back it up and place it on the shelf. RMAN
will not
Wow, great question, Rachel.
I truly didn't think of that until you asked, but, it turns out it was
LMT, uniform size, etc.
[EMAIL PROTECTED] 06/18/03 11:50AM
that actually makes sense when you think about it, with one question
--
was the tablespace a dictionary-managed one or an LMT?
If it
PROTECTED]
Sent by: [EMAIL PROTECTED]
18/06/2003 18:29
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Partitions of table read only
okay, am I missing something?
I created an LMT. Created a table in it. Gave
Rachel,
You forgot to kill all other active transactions... ;(
- Kirti
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
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
Title: Partitions of table read only
Jack,
It is possible to have some partitions of a table
read only and some read write. Possible even if they are subpartitions. They are
requird, say, in a DW environment, where the current quarter's data is read
write but the rest are read only.
You
all other active transactions in the database? or against that table?
if in the database, it will have to wait, this is a testing database
and work is going on in it.
if against that table, no one else knows anything about that table. As
far as any other user in the database knows, it doesn't
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
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
of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Partitions of table read only
Jack,
It is possible to have some partitions of a table read only and some read write. Possible even if they are subpartitions. They are requird, say, in a DW environment, where the current quarter's
Jack, maybe this has been covered. I seem to recall from the BR module
(knew it would prove useful sometime) that after you make a tablespace
read-only that you should take a backup. Recovering a database with
tablespaces that were read-write when backed up but are read-only now
requires an extra
Unfortunately, in the *database* .
- Kirti
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
all other active transactions in the database? or against that table?
if in the database, it will have to wait, this is a testing database
and work is going on in it.
if against that table, no
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
How about finding out what the session is waiting on, from v$session_wait?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, June 18, 2003 3:29 PM
there WERE no active transactions against that tablespace.
The steps I took were:
as
In order to complete an alter to read only, ALL transactions against the
database that were started before you issued that alter command must
complete before the alter will continue... From the concepts guide...
The ALTER TABLESPACE ... READ ONLY statement places the tablespace in a
that would explain it... and means I have to test it on my laptop, this
database is rarely quiet even in test
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote:
Unfortunately, in the *database* .
- Kirti
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
all other active transactions in
that would be sensible... :)
I'll try this again, from home and see -- but as Kirti says, if there
has to be no activity in the database, that would explain the problem
--- Arup Nanda [EMAIL PROTECTED] wrote:
How about finding out what the session is waiting on, from
v$session_wait?
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
Will that be all DML activity?
All the times?
In a datawarehouse?
In our data marts most activity is for just 'reading' stuff a lot of stuff
locally... Not many
active transactions.. So I can make TSs read only almost any time I want to..
- Kirti
--- Rachel Carmichael [EMAIL
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
The admin guide doesn't say no transactions in the database. In fact,
it specifically says in the tablespace:
You do not have to wait for transactions to complete before issuing
the ALTER TABLESPACE...READ ONLY statement. When the statement is
issued, the target tablespace goes into a
mostly selects... but according to what everyone is telling me, if
there are any transactions in the database at all, it will prevent me
from making it read only
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote:
Will that be all DML activity?
All the times?
In a datawarehouse?
In our data
Food for thought...
How does Oracle know that an existing transaction (which may be more
than the current statement) will not alter data in the RO Tablespace
until the transaction is completed (rollback/commit)?
--
Daniel W. Fink
http://www.optimaldba.com
Rachel Carmichael wrote:
The admin
dunno but then the docs are ambiguous aren't they? They can be read
as active in that tablespace
in any case once I bounced the database and was the only session, I
could make it read only. More importantly, and germane to the original
question, I could actually drop the table, something I
Discovery during the parse...?
[EMAIL PROTECTED] 06/18/03 09:39PM
Food for thought...
How does Oracle know that an existing transaction (which may be more
than the current statement) will not alter data in the RO Tablespace
until the transaction is completed (rollback/commit)?
--
Daniel W.
It waits for an (instance-wide) enqueue which will not succeed until the all
transactions have completed, giving an appearance of a hang.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 2:39 PM
Food for thought...
How does
34 matches
Mail list logo
