FAQ
I am creating a stored proc that will export a tablespace. One task it needs
to perform is to place the tablespace(s) in read only mode to make a copy.
Based upon the application and proc logic, there should not be any
transactions against objects in the ts. However, if there are, the ALTER
TABLESPACE command will wait until the transaction is completed. I would
rather have the ALTER TABLESPACE command fail immediately. If I cannot do
that, I would like to be able to test for locks on objects in the tablespace
(figured that one out, but it is rather kludgy).


Is there a method to force an immediate failure of ALTER TABLESPACE
READ ONLY if it cannot be immediatly completed?
Is there a clean method/proc to determine if the ALTER TABLESPACE command
will work?


Dan Fink

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: Dan.Fink_at_mdx.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_at_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).

Search Discussions

  • MacGregor, Ian A. at Oct 9, 2002 at 9:33 pm
    Just a slight correction it will wait until any transaction against the entire database, not just the tablespace is completed.


    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L

    I am creating a stored proc that will export a tablespace. One task it needs to perform is to place the tablespace(s) in read only mode to make a copy. Based upon the application and proc logic, there should not be any transactions against objects in the ts. However, if there are, the ALTER TABLESPACE command will wait until the transaction is completed. I would rather have the ALTER TABLESPACE command fail immediately. If I cannot do that, I would like to be able to test for locks on objects in the tablespace (figured that one out, but it is rather kludgy).


    Is there a method to force an immediate failure of ALTER TABLESPACE READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE command will work?


    Dan Fink

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: MacGregor, Ian A.
    INET: ian_at_SLAC.Stanford.EDU

    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_at_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).
  • Chaim.Katz_at_Completions.Bombardier.com at Oct 9, 2002 at 9:43 pm
    Here's an even more kludgy guess. Maybe you can execute the alter
    tablespace in the pl/sql job queue. If it runs too long (how long is too
    long?), then you know that it's waiting (i.e., failing), and you could do
    something appropriate action?

    "Fink, Dan" @fatcity.com on 10/09/2002 04:48:54 PM

    Please respond to ORACLE-L_at_fatcity.com

    Sent by: root_at_fatcity.com

    To: Multiple recipients of list ORACLE-L
    cc:

    I am creating a stored proc that will export a tablespace. One task it
    needs to perform is to place the tablespace(s) in read only mode to make a
    copy. Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the ALTER
    TABLESPACE command will wait until the transaction is completed. I would
    rather have the ALTER TABLESPACE command fail immediately. If I cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE
    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE command
    will work?

    Dan Fink

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Chaim.Katz_at_Completions.Bombardier.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_at_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).
  • Deshpande, Kirti at Oct 10, 2002 at 4:38 am
    And with that correction, it seems checking for active transactions (in
    v$transaction) would address this.


    However, by the time one gets a 'green' light from v$transaction and issues
    alter tablespace... there is the slight possibility of someone starting a
    new transaction locally or just selecting over a dblink...


    Too bad that the new 'transitional read-only' mode does not allow a graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to make the
    command fail...


    I would be interested in learning how you tackle this issue as I am also
    trying to implement TTS in some of my databases.


    Thanks.


    Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L

    Just a slight correction it will wait until any transaction against the
    entire database, not just the tablespace is completed.


    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L

    I am creating a stored proc that will export a tablespace. One task it needs
    to perform is to place the tablespace(s) in read only mode to make a copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the ALTER
    TABLESPACE command will wait until the transaction is completed. I would
    rather have the ALTER TABLESPACE command fail immediately. If I cannot do
    that, I would like to be able to test for locks on objects in the tablespace
    (figured that one out, but it is rather kludgy).


    Is there a method to force an immediate failure of ALTER TABLESPACE
    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE command
    will work?


    Dan Fink

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Deshpande, Kirti
    INET: kirti.deshpande_at_verizon.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_at_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).
  • Rachel Carmichael at Oct 10, 2002 at 11:08 am
    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using the
    consistent=y export parameter in conjunction with the tablespace
    export?

    "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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).
  • Ruth Gramolini at Oct 10, 2002 at 2:13 pm
    Why don't you just to a consistant export? That will not take any
    transaction after the start of the export, at leasst this is how I
    understand it.

    Ruth.
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, October 09, 2002 4:48 PM
    I am creating a stored proc that will export a tablespace. One task it needs
    to perform is to place the tablespace(s) in read only mode to make a copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the ALTER
    TABLESPACE command will wait until the transaction is completed. I would
    rather have the ALTER TABLESPACE command fail immediately. If I cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE
    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE command
    will work?

    Dan Fink
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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_at_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).
  • Fink, Dan at Oct 10, 2002 at 3:38 pm
    It is not the export per se that causes the problem. It is the copying of
    the datafile that is the issue. The tablespace must be made read only so
    that the datafile can be copied in a consistent version. I can understand
    (and support) no active tx in the tablespace, but why the whole (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to an
    ODS, why does it matter if Joe Accountant is adding an expense report in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no further write
    operations (DML statements) are allowed against the tablespace. Existing
    transactions that modified the tablespace are allowed to commit or rollback.
    Once
    all transactions (in the database) have completed, the tablespace becomes
    read-only."

    I love how Oracle buries a very important consideration in the very last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
    other options. The application architecture is such that I am pretty certain
    very bad things would happen if I tried to but the database in restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L

    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using the
    consistent=y export parameter in conjunction with the tablespace
    export?

    "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
  • Fink, Dan at Oct 10, 2002 at 4:03 pm
    That would be one option, but we are trying to avoid the overhead of the
    import process as we move the data to a new database. This process will
    happen daily and transporting a tablespace and plugging in a datafile is
    perceived to be faster (has not been tested yet).

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 8:14 AM
    To: Multiple recipients of list ORACLE-L

    Why don't you just to a consistant export? That will not take any
    transaction after the start of the export, at leasst this is how I
    understand it.

    Ruth.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
  • Henry Poras at Oct 10, 2002 at 5:09 pm
    But can you know for sure if an open txn will hit your tablespace until the
    txn is closed?

    Henry

    -----Original Message-----
    Sent: Thursday, October 10, 2002 11:39 AM
    To: Multiple recipients of list ORACLE-L

    It is not the export per se that causes the problem. It is the copying of
    the datafile that is the issue. The tablespace must be made read only so
    that the datafile can be copied in a consistent version. I can understand
    (and support) no active tx in the tablespace, but why the whole (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to an
    ODS, why does it matter if Joe Accountant is adding an expense report in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no further write
    operations (DML statements) are allowed against the tablespace. Existing
    transactions that modified the tablespace are allowed to commit or rollback.
    Once
    all transactions (in the database) have completed, the tablespace becomes
    read-only."

    I love how Oracle buries a very important consideration in the very last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
    other options. The application architecture is such that I am pretty certain
    very bad things would happen if I tried to but the database in restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L

    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using the
    consistent=y export parameter in conjunction with the tablespace
    export?

    "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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.com
    --
    Author: Henry Poras
    INET: hporas_at_etal.uri.edu

    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_at_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).
  • Fink, Dan at Oct 10, 2002 at 6:04 pm
    D'OH!

    You are absolutely correct. I completely overthought the issue and missed
    the obvious.

    Thanks Henry

    -----Original Message-----
    Sent: Thursday, October 10, 2002 11:09 AM
    To: Multiple recipients of list ORACLE-L

    But can you know for sure if an open txn will hit your tablespace until the
    txn is closed?

    Henry

    -----Original Message-----
    Sent: Thursday, October 10, 2002 11:39 AM
    To: Multiple recipients of list ORACLE-L

    It is not the export per se that causes the problem. It is the copying of
    the datafile that is the issue. The tablespace must be made read only so
    that the datafile can be copied in a consistent version. I can understand
    (and support) no active tx in the tablespace, but why the whole (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to an
    ODS, why does it matter if Joe Accountant is adding an expense report in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no further write
    operations (DML statements) are allowed against the tablespace. Existing
    transactions that modified the tablespace are allowed to commit or rollback.
    Once
    all transactions (in the database) have completed, the tablespace becomes
    read-only."

    I love how Oracle buries a very important consideration in the very last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
    other options. The application architecture is such that I am pretty certain
    very bad things would happen if I tried to but the database in restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L

    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using the
    consistent=y export parameter in conjunction with the tablespace
    export?

    "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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.com
    --
    Author: Henry Poras
    INET: hporas_at_etal.uri.edu

    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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
  • Rachel Carmichael at Oct 10, 2002 at 7:25 pm
    ah! transportable tablespaces? you did't SAY that

    "Fink, Dan" wrote:
    It is not the export per se that causes the problem. It is the
    copying of
    the datafile that is the issue. The tablespace must be made read only
    so
    that the datafile can be copied in a consistent version. I can
    understand
    (and support) no active tx in the tablespace, but why the whole
    (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to
    an
    ODS, why does it matter if Joe Accountant is adding an expense report
    in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read
    only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no
    further write
    operations (DML statements) are allowed against the tablespace.
    Existing
    transactions that modified the tablespace are allowed to commit or
    rollback.
    Once
    all transactions (in the database) have completed, the tablespace
    becomes
    read-only."

    I love how Oracle buries a very important consideration in the very
    last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do
    have
    other options. The application architecture is such that I am pretty
    certain
    very bad things would happen if I tried to but the database in
    restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L


    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in
    read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using
    the
    consistent=y export parameter in conjunction with the tablespace
    export?


    --- "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink

    __________________________________________________
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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).
  • Fink, Dan at Oct 10, 2002 at 8:44 pm
    That information is not relevant and should be ignored. I would expect YOU
    to know as much!

    -----Original Message-----
    Sent: Thursday, October 10, 2002 1:25 PM
    To: Multiple recipients of list ORACLE-L

    ah! transportable tablespaces? you did't SAY that

    "Fink, Dan" wrote:
    It is not the export per se that causes the problem. It is the
    copying of
    the datafile that is the issue. The tablespace must be made read only
    so
    that the datafile can be copied in a consistent version. I can
    understand
    (and support) no active tx in the tablespace, but why the whole
    (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to
    an
    ODS, why does it matter if Joe Accountant is adding an expense report
    in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read
    only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no
    further write
    operations (DML statements) are allowed against the tablespace.
    Existing
    transactions that modified the tablespace are allowed to commit or
    rollback.
    Once
    all transactions (in the database) have completed, the tablespace
    becomes
    read-only."

    I love how Oracle buries a very important consideration in the very
    last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do
    have
    other options. The application architecture is such that I am pretty
    certain
    very bad things would happen if I tried to but the database in
    restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L


    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in
    read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using
    the
    consistent=y export parameter in conjunction with the tablespace
    export?


    --- "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One task
    it needs
    to perform is to place the tablespace(s) in read only mode to make a
    copy.
    Based upon the application and proc logic, there should not be any
    transactions against objects in the ts. However, if there are, the
    ALTER
    TABLESPACE command will wait until the transaction is completed. I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER TABLESPACE

    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink

    __________________________________________________
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
  • Rachel Carmichael at Oct 11, 2002 at 2:23 am
    yes it's relevant, it explains why you can't use a consistent export
    only to move the data and have to copy the tablespace as well. It also
    explains why the tablespace has to be in read-only mode.

    "Fink, Dan" wrote:
    That information is not relevant and should be ignored. I would
    expect YOU
    to know as much!

    -----Original Message-----
    Sent: Thursday, October 10, 2002 1:25 PM
    To: Multiple recipients of list ORACLE-L


    ah! transportable tablespaces? you did't SAY that


    --- "Fink, Dan" wrote:
    It is not the export per se that causes the problem. It is the
    copying of
    the datafile that is the issue. The tablespace must be made read only
    so
    that the datafile can be copied in a consistent version. I can
    understand
    (and support) no active tx in the tablespace, but why the whole
    (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to
    an
    ODS, why does it matter if Joe Accountant is adding an expense report
    in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read
    only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no
    further write
    operations (DML statements) are allowed against the tablespace.
    Existing
    transactions that modified the tablespace are allowed to commit or
    rollback.
    Once
    all transactions (in the database) have completed, the tablespace
    becomes
    read-only."

    I love how Oracle buries a very important consideration in the very
    last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do
    have
    other options. The application architecture is such that I am pretty
    certain
    very bad things would happen if I tried to but the database in
    restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L


    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in
    read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using
    the
    consistent=y export parameter in conjunction with the tablespace
    export?


    --- "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active
    transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow
    a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I
    am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction
    against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One
    task
    it needs
    to perform is to place the tablespace(s) in read only mode to
    make
    a
    copy.
    Based upon the application and proc logic, there should not be
    any
    transactions against objects in the ts. However, if there are,
    the
    ALTER
    TABLESPACE command will wait until the transaction is completed.
    I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER
    TABLESPACE
    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink

    __________________________________________________
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    === message truncated ===

    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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).
  • Fink, Dan at Oct 11, 2002 at 6:14 pm
    First, I ommitted the;) when replying to Rachel's post. I was just
    funnin...

    Second, in this case TTS will not work. While we can 99.99% guarantee that
    there will be no tx against the tablespace, there will be active tx in the
    database and we cannot guarantee that they will not cause the RO command to
    wait.

    Interestingly, it is possible to offline the tablespace while there are
    active tx (even against objects in the ts). If you can offline and then
    online with active tx, why not alter it to RO? Perhaps, once again, I am
    missing the obvious.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 8:23 PM
    To: Multiple recipients of list ORACLE-L

    yes it's relevant, it explains why you can't use a consistent export
    only to move the data and have to copy the tablespace as well. It also
    explains why the tablespace has to be in read-only mode.

    "Fink, Dan" wrote:
    That information is not relevant and should be ignored. I would
    expect YOU
    to know as much!

    -----Original Message-----
    Sent: Thursday, October 10, 2002 1:25 PM
    To: Multiple recipients of list ORACLE-L


    ah! transportable tablespaces? you did't SAY that


    --- "Fink, Dan" wrote:
    It is not the export per se that causes the problem. It is the
    copying of
    the datafile that is the issue. The tablespace must be made read only
    so
    that the datafile can be copied in a consistent version. I can
    understand
    (and support) no active tx in the tablespace, but why the whole
    (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate it to
    an
    ODS, why does it matter if Joe Accountant is adding an expense report
    in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read
    only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no
    further write
    operations (DML statements) are allowed against the tablespace.
    Existing
    transactions that modified the tablespace are allowed to commit or
    rollback.
    Once
    all transactions (in the database) have completed, the tablespace
    becomes
    read-only."

    I love how Oracle buries a very important consideration in the very
    last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do
    have
    other options. The application architecture is such that I am pretty
    certain
    very bad things would happen if I tried to but the database in
    restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L


    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in
    read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about using
    the
    consistent=y export parameter in conjunction with the tablespace
    export?


    --- "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active
    transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from v$transaction and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not allow
    a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0 to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as I
    am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction
    against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One
    task
    it needs
    to perform is to place the tablespace(s) in read only mode to
    make
    a
    copy.
    Based upon the application and proc logic, there should not be
    any
    transactions against objects in the ts. However, if there are,
    the
    ALTER
    TABLESPACE command will wait until the transaction is completed.
    I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in the
    tablespace
    (figured that one out, but it is rather kludgy).

    Is there a method to force an immediate failure of ALTER
    TABLESPACE
    READ ONLY if it cannot be immediatly completed?
    Is there a clean method/proc to determine if the ALTER TABLESPACE
    command
    will work?

    Dan Fink

    __________________________________________________
    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    === message truncated ===

    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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_at_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).
  • Rachel Carmichael at Oct 11, 2002 at 7:05 pm
    thanks :)

    it sort of makes sense that you can offline a tablespace with an active
    transaction in it. You can offline a rollback segment with an active
    transaction in it, no new transactions can start in that rbs but the
    one(s) there will finish.

    With the tablespace, if I remember what I read in the docs correctly
    (and if the docs are correct), when you offline a tablespace, the
    active transactions in it continue to process as long as they do not
    try to modify a block in that tablespace. Once they try that, the
    transaction dies.

    Hm, that doesn't sound right...... maybe it's if the blocks from that
    tablespace are still in the buffer cache?

    Okay, found it in the 9ir2 Concepts manual, quoted below:

    When a tablespace goes offline, Oracle does not permit any subsequent
    SQL statements to reference objects contained in that tablespace.
    Active transactions with completed statements that refer to data in
    that tablespace are not affected at the transaction level. Oracle saves
    rollback data corresponding to those completed statements in a deferred
    rollback segment in the SYSTEM tablespace. When the tablespace is
    brought back online, Oracle applies the rollback data to the
    tablespace, if needed.

    When a tablespace goes offline or comes back online, this is recorded
    in the data dictionary in the SYSTEM tablespace. If a tablespace is
    offline when you shut down a database, the tablespace remains offline
    when the database is subsequently mounted and reopened.

    You can bring a tablespace online only in the database in which it was
    created because the necessary data dictionary information is maintained
    in the SYSTEM tablespace of that database. An offline tablespace cannot
    be read or edited by any utility other than Oracle. Thus, offline
    tablespaces cannot be transposed to other databases.

    "Fink, Dan" wrote:
    First, I ommitted the;) when replying to Rachel's post. I was just
    funnin...

    Second, in this case TTS will not work. While we can 99.99% guarantee
    that
    there will be no tx against the tablespace, there will be active tx
    in the
    database and we cannot guarantee that they will not cause the RO
    command to
    wait.

    Interestingly, it is possible to offline the tablespace while there
    are
    active tx (even against objects in the ts). If you can offline and
    then
    online with active tx, why not alter it to RO? Perhaps, once again, I
    am
    missing the obvious.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 8:23 PM
    To: Multiple recipients of list ORACLE-L


    yes it's relevant, it explains why you can't use a consistent export
    only to move the data and have to copy the tablespace as well. It
    also
    explains why the tablespace has to be in read-only mode.


    --- "Fink, Dan" wrote:
    That information is not relevant and should be ignored. I would
    expect YOU
    to know as much!

    -----Original Message-----
    Sent: Thursday, October 10, 2002 1:25 PM
    To: Multiple recipients of list ORACLE-L


    ah! transportable tablespaces? you did't SAY that


    --- "Fink, Dan" wrote:
    It is not the export per se that causes the problem. It is the
    copying of
    the datafile that is the issue. The tablespace must be made read only
    so
    that the datafile can be copied in a consistent version. I can
    understand
    (and support) no active tx in the tablespace, but why the whole
    (*#(&*$#
    database? If I need to take INVOICE_1999 tablespace and migrate
    it
    to
    an
    ODS, why does it matter if Joe Accountant is adding an expense report
    in the
    EXPENSE_2002 ts?

    In the Oracle doc, it lists the requirements for making a ts read
    only. On
    the next page it states (verbatim from doc)

    "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 transitional read-only mode in which no
    further write
    operations (DML statements) are allowed against the tablespace.
    Existing
    transactions that modified the tablespace are allowed to commit
    or
    rollback.
    Once
    all transactions (in the database) have completed, the tablespace
    becomes
    read-only."

    I love how Oracle buries a very important consideration in the
    very
    last
    line of a paragraph!

    We are on 9ir1, so the TABLESPACE parameter is not helpful, but
    we
    do
    have
    other options. The application architecture is such that I am pretty
    certain
    very bad things would happen if I tried to but the database in
    restricted
    mode.

    Dan

    -----Original Message-----
    Sent: Thursday, October 10, 2002 5:09 AM
    To: Multiple recipients of list ORACLE-L


    so if it's waiting for any active transaction, I guess you could put
    the database in restricted mode until existing transactions complete.
    Of course, that sort of defeats the purpose of putting it in
    read-only
    so other people can access it.

    um, 9ir2 has an export parameter of "tablespace", if you want it
    "read-only" so nothing changes while you export it, how about
    using
    the
    consistent=y export parameter in conjunction with the tablespace
    export?


    --- "Deshpande, Kirti" wrote:
    And with that correction, it seems checking for active
    transactions
    (in
    v$transaction) would address this.

    However, by the time one gets a 'green' light from
    v$transaction
    and
    issues
    alter tablespace... there is the slight possibility of someone
    starting a
    new transaction locally or just selecting over a dblink...

    Too bad that the new 'transitional read-only' mode does not
    allow
    a
    graceful
    exit... Per the Admin Guide one must set compatible to < 8.1.0
    to
    make the
    command fail...

    I would be interested in learning how you tackle this issue as
    I
    am
    also
    trying to implement TTS in some of my databases.

    Thanks.

    - Kirti

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 4:34 PM
    To: Multiple recipients of list ORACLE-L


    Just a slight correction it will wait until any transaction
    against
    the
    entire database, not just the tablespace is completed.

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Wednesday, October 09, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L


    I am creating a stored proc that will export a tablespace. One
    task
    it needs
    to perform is to place the tablespace(s) in read only mode to
    make
    a
    copy.
    Based upon the application and proc logic, there should not be
    any
    transactions against objects in the ts. However, if there are,
    the
    ALTER
    TABLESPACE command will wait until the transaction is
    completed.
    I
    would
    rather have the ALTER TABLESPACE command fail immediately. If I
    cannot do
    that, I would like to be able to test for locks on objects in
    the
    tablespace
    (figured that one out, but it is rather kludgy).
    === message truncated ===

    Do you Yahoo!?
    Faith Hill - Exclusive Performances, Videos & More
    http://faith.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_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_at_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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 9, '02 at 8:48p
activeOct 11, '02 at 7:05p
posts15
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase