Re: Partitions of table read only
I think Daniel covered this when he mentioned that a transaction can consist of many statements - some of which Oracle may not yet be aware of. Until a user decides to commit or rollback then there is a possibility that a transaction may head into that tablespace. Of course it would be nice to play god and have an option which equated to I don't care, let those transactions fail miserably. If I've misunderstood the terminology I'm terribly sorry. Darrell Landrum [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] p.com cc: Sent by: Subject: Re: Partitions of table read only [EMAIL PROTECTED] .com 19/06/2003 13:34 Please respond to ORACLE-L 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. Fink http://www.optimaldba.com Rachel Carmichael wrote: 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message
Re: Partitions of table read only
Mark, You are understanding the terminology and concepts correctly. Until the transaction completes, there is no way for the system to know which objects the transaction will access. More food for thought...I am updating tableA in the non-RO tablespace. However, there is an after-update trigger that updates tableB in the RO tablespace. Will Oracle know this at the PARSE phase? Daniel Fink Mark Richard wrote: I think Daniel covered this when he mentioned that a transaction can consist of many statements - some of which Oracle may not yet be aware of. Until a user decides to commit or rollback then there is a possibility that a transaction may head into that tablespace. Of course it would be nice to play god and have an option which equated to I don't care, let those transactions fail miserably. If I've misunderstood the terminology I'm terribly sorry. Darrell Landrum [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] p.com cc: Sent by: Subject: Re: Partitions of table read only [EMAIL PROTECTED] .com 19/06/2003 13:34 Please respond to ORACLE-L 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. Fink http://www.optimaldba.combegin: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
RE: Partitions of table read only
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 that a change to the tablespace itself would eventually happen ... maybe when it is brought back online the available storage space gets updated to reflect the disappearance of the table??? -Original Message- I could actually drop the table, something I didn't think could happen in an LMT when it was read-only. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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).
Re: Partitions of table read only
- 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 take a backup. Recovering a database with tablespaces that were read-write when backed up but are read-only now requires an extra step or two (something I never like in a recovery). Hi Dennis Backing up the tablespace files (and lets not forget the control file) is certainly not a bad idea. Something else that many don't consider is to select from all objects within the tablespace with full scans *before* making the tablespace read only. This has the effect of performing all the necessary block cleanouts (ie. for all the blocks written to disk before they could be committed and cleaned out in memory) while Oracle still can. If the tablespace is made read only and some poor blocks haven't been cleaned out, upon reading the block Oracle has no choice but to go to the rollback/undo segments in it's attempt to confirm the consistency of the block. However upon confirming that indeed the transaction is long gone and block cleanout can take place with the latest possible SCN, it can't actually perform the necessary block changes because, you guessed it, the tablespace is currently read only. This means that the overhead of checking for consistency but failing to actually perform the block cleanout continues on and on and on ... Hence the suggestion to guarantee block cleanout while the tablespace is in a position to do so (in read/write mode). Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
RE: Partitions of table read only
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. The owners of these transactions can be notified and a decision can be made to terminate the transactions, if necessary. The following example illustrates how you might identify the blocking transactions. Identify the transaction entry for the ALTER TABLESPACE...READ ONLY statement. SELECT sql_text, saddr FROM v$sqlarea,v$session WHERE v$sqlarea.address = v$session.sql_address AND sql_text like 'alter tablespace%'; SQL_TEXT SADDR alter tablespace tbs1 read only 80034AF0 The start SCN of each active transaction is stored in the V$TRANSACTION view. Displaying this view sorted by ascending start SCN lists the transactions in execution order. Knowing the transaction entry for the read-only statement, it can be located in the V$TRANSACTION view. All transactions with lesser or equal start SCN can potentially hold up the quiesce and subsequent read-only state of the tablespace. SELECT ses_addr, start_scnb FROM v$transaction ORDER BY start_scnb; SES_ADDR START_SCNB -- 800352A0 3621 -- waiting on this txn 80035A50 3623 -- waiting on this txn 80034AF0 3628 -- this is the ALTER TABLESPACE statement 80037910 3629 -- don't care about this txn After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary since no changes can be made to it. Notice it says All transactions with lesser or equal start SCN can potentially hold up the quiesce and subsequent read-only state of the tablespace... Not just the transactions against that tablespace... And, they are checking all entries in v$transaction... On the other hand, you can still alter tablespaces to read only even though your system is busy... The point is that the command will hang until all transactions that started before your alter command finish... Maybe this is a better way... 1) tx1 starts at time t1 2) You alter tablespace tbsp1 to read only at t2 (it hangs) 3) tx2 starts at time t3 and does not go against tbsp1 4) tx3 starts at time t4 and does go against tbsp1 (it fails since tbsp1 is in transitional read only) 5) tx1 completes 6) The alter to read only can not complete since tx1 was the only transaction that started before it Better? Tim -Original Message- Sent: Wednesday, June 18, 2003 9:50 PM To: Multiple recipients of list ORACLE-L 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) --- Johnston, Tim [EMAIL PROTECTED] wrote: 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 transitional read-only mode and waits for existing transactions to complete (commit or roll back). This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. Hence, in transition the tablespace behaves like a read-only tablespace for all user statements except ROLLBACK. After all of the existing transactions have either committed or rolled back, the ALTER TABLESPACE ... READ ONLY statement completes and the tablespace is placed in read-only mode. And there is a good example in the admin guide... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspa ces.htm#6884 HTH Tim -Original Message- Sent: Wednesday, June 18, 2003 3:30 PM To: Multiple recipients of list ORACLE-L there WERE no active transactions against that tablespace. The steps I took were: as system: 1) create tablespace
RE: Partitions of table read only
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 identify the transactions which are preventing the read-only state from taking effect. The owners of these transactions can be notified and a decision can be made to terminate the transactions, if necessary. The following example illustrates how you might identify the blocking transactions. Identify the transaction entry for the ALTER TABLESPACE...READ ONLY statement. SELECT sql_text, saddr FROM v$sqlarea,v$session WHERE v$sqlarea.address = v$session.sql_address AND sql_text like 'alter tablespace%'; SQL_TEXT SADDR alter tablespace tbs1 read only 80034AF0 The start SCN of each active transaction is stored in the V$TRANSACTION view. Displaying this view sorted by ascending start SCN lists the transactions in execution order. Knowing the transaction entry for the read-only statement, it can be located in the V$TRANSACTION view. All transactions with lesser or equal start SCN can potentially hold up the quiesce and subsequent read-only state of the tablespace. SELECT ses_addr, start_scnb FROM v$transaction ORDER BY start_scnb; SES_ADDR START_SCNB -- 800352A0 3621 -- waiting on this txn 80035A50 3623 -- waiting on this txn 80034AF0 3628 -- this is the ALTER TABLESPACE statement 80037910 3629 -- don't care about this txn After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary since no changes can be made to it. Notice it says All transactions with lesser or equal start SCN can potentially hold up the quiesce and subsequent read-only state of the tablespace... Not just the transactions against that tablespace... And, they are checking all entries in v$transaction... On the other hand, you can still alter tablespaces to read only even though your system is busy... The point is that the command will hang until all transactions that started before your alter command finish... Maybe this is a better way... 1) tx1 starts at time t1 2) You alter tablespace tbsp1 to read only at t2 (it hangs) 3) tx2 starts at time t3 and does not go against tbsp1 4) tx3 starts at time t4 and does go against tbsp1 (it fails since tbsp1 is in transitional read only) 5) tx1 completes 6) The alter to read only can not complete since tx1 was the only transaction that started before it Better? Tim -Original Message- Sent: Wednesday, June 18, 2003 9:50 PM To: Multiple recipients of list ORACLE-L 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) --- Johnston, Tim [EMAIL PROTECTED] wrote: 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 transitional read-only mode and waits for existing transactions to complete (commit or roll back). This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. Hence, in transition the tablespace behaves like a read-only tablespace for all user statements except ROLLBACK. After all of the existing transactions have either committed or rolled back, the ALTER TABLESPACE ... READ ONLY statement completes and the tablespace is placed in read-only mode. And there is a good example in the admin guide... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspa ces.htm#6884 HTH
Partitions of table read only
Title: Partitions of table read only 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] Visit our web site at http://www.quantsystems.nl/
Re: Partitions of table read only
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).
Re: Partitions of table read only
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).
Re: Partitions of table read only
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).
Re: Partitions of table read only
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 backup the read-only data normally so the backup time decreases. It makes the data managable and I can move the read-only partitions to an archive disk to reduce the i/o on the active disks. Ron [EMAIL PROTECTED] 06/18/03 10: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: Ron Rogers 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).
Re: Partitions of table read only
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 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). -- 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).
Re: Partitions of table read only
You're missing something, but I couldn't say what... I tried toying with the same thing, but I'm having a lazy afternoon, so I used Enterprise Manager: Set up a new tablespace, created the table, sounds the same so far... put a few rows into it, again using the nice graphical interface that avoids having to think or know what you're doing ;-) Made the tablespace read only, checked that I couldn't update the table any more. Dropped the table - gone, no complaints from the database. The tablespace map shows the segment that had been being used by the table as a temporary segment. Your suggestion about the metadata makes sense, the bitmap held in the LMT can't be the only information about that segment. When I get my brain back in gear, I'll try and work out where else to look for a better idea of what's happening. Cheers Simon Anderson Rachel Carmichael [EMAIL 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 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? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Re: Partitions of table read only
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 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] __ 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: Kirtikumar Deshpande 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).
Re: Partitions of table read only
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 can backup the read only tablespace only once, and then important as long as you never make it read write /important you can recover it. Since you are probably referring toa sort of archival system, you can follow an approach I am using here.Our requirement is to hold data online for three years, actually 12 quarters. So, in the beginning of a quarter, I make the oldest partition of the tables a table (alter table exchange partition) and make that tablespace read only. Then I "transport" the tablespace to an optical jukebox using export/transportable, and drop the tablespace. The tablespaces are named in a format with the year and quarter in their names, so they are always unique. When the time comes to use these older partitions, I simply plug them in and drop them after the rowrk is done. This makes the process transparent to the user, actually to the tools used by the user. HTH. Arup Nanda - Original Message - From: Jack van Zanen To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 18, 2003 10:49 AM Subject: Partitions of table read only 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] Visit our web site at http://www.quantsystems.nl/
Re: Partitions of table read only
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 exist. Nor does the tablespace --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: 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 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] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com __ 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).
Re: Partitions of table read only
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
Re: Partitions of table read only
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
Re: Partitions of table read only
Jack I am also using almost similar kind of Partition Management where I am keeping only six months of data. In mine configuration , I have the following requirement Daily partition created in the evening and dropping the 7day old partition.e.g I am creating Wednesday Partition on Sunday so as to have the 3days future partition available in advance. Weekly Partition Created every thursday and dropping the 15 week old partition. Same like Daily partition, I am creating 2 week Future partition in advance so as to be ready for any error correction if the automated job for Partition creations failed Monthly partition created on the First of each month and dropping 6 month old partition. I had written a package that take care of all of the Daily/Weekly/Monthly partition. Note I am using Local Indexes and had CLOB columns in the Tables. Each monthly partition is around 25 Gig and Daily partition is around 3 Gig. The package also take care of Tablespace creation for Data and Index segments as well as dropping the Tablespace for partitioned table and indexes. I am calling the Package thru Cron Job and getting the Email for success as well as failure with full syntax. I had written the package around two year back with Oracle 817 but working fine and it might need small improvement with Oracle 9i but can be done easily. Reply back to me on mine direct email Id and then I will send you the code as I don't think we can attach any message to this list Thanks Indy Johal Manager, Database Administration PR Newswire [EMAIL PROTECTED] http://www.prnewswire.com (201) 946-5687 [W] (201) 400-3960 [M] We tell your story to the world. Arup Nanda [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/18/03 02:59 PM Please respond to ORACLE-L To:Multiple recipients 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 data is read write but the rest are read only. You can backup the read only tablespace only once, and then important as long as you never make it read write /important you can recover it. Since you are probably referring to a sort of archival system, you can follow an approach I am using here. Our requirement is to hold data online for three years, actually 12 quarters. So, in the beginning of a quarter, I make the oldest partition of the tables a table (alter table exchange partition) and make that tablespace read only. Then I transport the tablespace to an optical jukebox using export/transportable, and drop the tablespace. The tablespaces are named in a format with the year and quarter in their names, so they are always unique. When the time comes to use these older partitions, I simply plug them in and drop them after the rowrk is done. This makes the process transparent to the user, actually to the tools used by the user. HTH. Arup Nanda - Original Message - From: Jack van Zanen To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 18, 2003 10:49 AM Subject: Partitions of table read only 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] Visit our web site at http://www.quantsystems.nl/
RE: Partitions of table read only
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 step or two (something I never like in a recovery). Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 18, 2003 1:59 PM To: Multiple recipients of list ORACLE-L 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 can backup the read only tablespace only once, and then important as long as you never make it read write /important you can recover it. Since you are probably referring to a sort of archival system, you can follow an approach I am using here. Our requirement is to hold data online for three years, actually 12 quarters. So, in the beginning of a quarter, I make the oldest partition of the tables a table (alter table exchange partition) and make that tablespace read only. Then I transport the tablespace to an optical jukebox using export/transportable, and drop the tablespace. The tablespaces are named in a format with the year and quarter in their names, so they are always unique. When the time comes to use these older partitions, I simply plug them in and drop them after the rowrk is done. This makes the process transparent to the user, actually to the tools used by the user. HTH. Arup Nanda - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Wednesday, June 18, 2003 10:49 AM 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: mailto:[EMAIL PROTECTED] [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: DENNIS WILLIAMS 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).
Re: Partitions of table read only
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 one else knows anything about that table. As far as any other user in the database knows, it doesn't exist. Nor does the tablespace --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Rachel, You forgot to kill all other active transactions... ;( - Kirti __ 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: Kirtikumar Deshpande 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).
Re: Partitions of table read only
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 may also send the
Re: Partitions of table read only
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 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
RE: Partitions of table read only
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 transitional read-only mode and waits for existing transactions to complete (commit or roll back). This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. Hence, in transition the tablespace behaves like a read-only tablespace for all user statements except ROLLBACK. After all of the existing transactions have either committed or rolled back, the ALTER TABLESPACE ... READ ONLY statement completes and the tablespace is placed in read-only mode. And there is a good example in the admin guide... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspa ces.htm#6884 HTH Tim -Original Message- Sent: Wednesday, June 18, 2003 3:30 PM To: Multiple recipients of list ORACLE-L 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
Re: Partitions of table read only
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 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 exist. Nor does the tablespace --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Rachel, You forgot to kill all other active transactions... ;( - Kirti __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com __ 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).
Re: Partitions of table read only
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? - 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 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
Re: Partitions of table read only
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
Re: Partitions of table read only
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 PROTECTED] wrote: 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 __ 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: Kirtikumar Deshpande 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).
Re: Partitions of table read only
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]
RE: Partitions of table read only
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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) --- Johnston, Tim [EMAIL PROTECTED] wrote: 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 transitional read-only mode and waits for existing transactions to complete (commit or roll back). This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. Hence, in transition the tablespace behaves like a read-only tablespace for all user statements except ROLLBACK. After all of the existing transactions have either committed or rolled back, the ALTER TABLESPACE ... READ ONLY statement completes and the tablespace is placed in read-only mode. And there is a good example in the admin guide... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspa ces.htm#6884 HTH Tim -Original Message- Sent: Wednesday, June 18, 2003 3:30 PM To: Multiple recipients of list ORACLE-L 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
Re: Partitions of table read only
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 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 PROTECTED] wrote: 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 __ 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: Kirtikumar Deshpande 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).
Re: Partitions of table read only
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 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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).
Re: Partitions of table read only
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 didn't think could happen in an LMT when it was read-only. --- Daniel W. Fink [EMAIL PROTECTED] wrote: 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 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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).
Re: Partitions of table read only
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. Fink http://www.optimaldba.com Rachel Carmichael wrote: 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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).
Re: Partitions of table read only
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 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 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 transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions. There were other transactions in the database, but none affecting that tablespace. The concepts guide is somewhat ambiguous and could be read either as in the database or against the tablespace since it isn't specified. Have I mentioned lately that I hate the docs? :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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: Binley Lim 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).
