Re: Data Purging Strategy
Hi Tim We just signed a contract for external storage system from EMC and the configuration is going to be: Regular servers - connect as Nas Database servers - connect as San. If I remember correctly Nas use SCSI connections while San use fiber. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 1:43 AM In response to a post on data purging Tim Gorman wrote some on SAN-based disk, some on NAS-based storage. Can someone please explain the differences between these technologies please. My understanding that a SAN is a group of disks which are available on a network and are not 'owned' by a server and have no direct cables into a server. I also understood NAS to be network based disk (duh!) Please correct, clarify, or comment as needed; I don't recall ever having seen a formal definition for either acronym: * SAN (storage area network): storage-arrays connected by dedicated high-speed interconnects (i.e. SCSI, SSA, FC-AL, etc) managed by a dedicated server, including switches and routers to provide storage for one or multiple storage clients (i.e. what we tend to call servers)... * NAS (network-attached storage): storage that is hosted by (i.e. mounted on) a dedicated, special-purpose server and made available to network clients via IP protocols like NFS, Samba, etc across general-purpose IP networks. For NAS, think dedicated NFS server or dedicated file server or the like and you've got the idea... There are so many technologies mixed into SANs that I find it difficult to generalize. It is probably more appropriate to define NAS first and then say SANs are everything else in networked storage, but I thought I'd try it the hard way... Further generalizing: * SANs are capable of faster and more sustainable I/O throughput rates, but more complex and more expensive * NAS are economical, easy to administer, and easy to implement, but provide lower sustained I/O throughput rates For this reason, I don't see the question as an either-or proposition (i.e. either all SAN or all NAS). They are each point-solutions along a continuum, as illustrated in the strategy in my previous reply. Data passes through a life-cycle, just like anything else. Requirements for storage and retrieval can change during that life-cycle... - ... continuum .. there's a high-class word I've been itching to use . has the potential to become as hoity-toity and annoying as paradigm and juxtaposition, though... :-) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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.com -- Author: Yechiel Adar 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: Data Purging Strategy
Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: End of Communication Channel Error
take a look at your kernel params - may be there is not sufficient segment space kr mr [EMAIL PROTECTED] 11/01/02 21:20 PM Forget this. Found the error. WARNING: EINVAL creating segment of size 0x0a086000 fix shm parameters in /etc/system or equivalent -Original Message- Sent: Friday, November 01, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Hey Gang; Anyone here familliar with an End Of Communication Channel error every time I try and Build a database ?? I am trying to build an 8.1.7.0 database on a Sun Sparc 20 running Solaris 9. I have a feeling it might be settings on the box itself, but thought I would ask the list just in case anyone had any insight. Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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.com -- Author: Kevin Lange 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.com -- Author: Markus Reger 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: Oracle to Excel
This is how I do it: 1)Set-up an ODBC data source using either the Oracle or MS ODBC driver. 2)In Excel, use the Date-Get External Data menu option 3)Specify the ODBC driver 4)In the Wizard, select the table columns, where and sort or Untick the box and use MS Query where you can type in your own query using the SQL button 5)The data is returned into the spreadsheet Steve Parker E Mail: [EMAIL PROTECTED] --
Re: SQL Brain Teaser Challenge
Note 1009345.6 on MetaLink provides a solution similar to the one proposed with an index. Jared On Tuesday 05 November 2002 09:24, Orr, Steve wrote: Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Data Purging Strategy
just don't slip in that 32 year old Macallans! that one is MINE :) --- Mark Leith [EMAIL PROTECTED] wrote: Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
SPIN_COUNT - 8i obsolete? What now?
Title: SPIN_COUNT - 8i obsolete? What now? Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: SPIN_COUNT - 8i obsolete? What now?
Denham Eva wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Increase caffeine_level. Seriously, is it a problem? A real bottleneck? You should begin to worry if you do not have worse contention elsewhere. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Data Purging Strategy
Oh no, I wouldn't dream of it! That is being saved for your up and coming UK tour :) It's matured even more now as well.. ;) -Original Message- Carmichael Sent: 07 November 2002 10:59 To: Multiple recipients of list ORACLE-L just don't slip in that 32 year old Macallans! that one is MINE :) --- Mark Leith [EMAIL PROTECTED] wrote: Sounds fair enough to me.. ;) -Original Message- [EMAIL PROTECTED] Sent: 06 November 2002 18:55 To: Multiple recipients of list ORACLE-L That reminds me: Mark, your annual stipend is due. Make it a case of Glenmorangie this time, Sherry finish. :) Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Data Purging Strategy Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
RE: SPIN_COUNT - 8i obsolete? What now?
Eva, the spin_count parameter became _spin_count in 8i. I believe that because it's a hidden parameter it's now not possible to change it using ALTER SYSTEM. Regards, Mike Hately -Original Message- Sent: Thursday, November 07, 2002 11:33 AM To: Multiple recipients of list ORACLE-L Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: Oracle to Excel
Title: Oracle to Excel Laura, Sorry about that - instead of using chr(10), use chr(09). (10) is line feed. (09) is tab. also - don't forget to : set trimspool on - it gets rid of trailing spaces! good luck! Tom Mercadante Oracle Certified Professional -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 4:00 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle to Excel Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column. Below is an example (1 record is shown): 53-041-02 0 2 AL POWER CO 51271 27-Nov-01 173.52 447-57 D412 173.52 226 DONNELL 43000 00014.04.D412 It looks like it thinks it is a carriage return instead of a tab. Any ideas? Laura -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle to Excel Laura, lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format. You can do this yourself as follows: in SqlPLus select col1||chr(10)||col2||chr(10) from table. the chr(10) is the tab character. spool the results to a output.xls open the file using excel. the tab character is the default column delimiter. You should see all your data in cells. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 06, 2002 1:49 PMTo: Multiple recipients of list ORACLE-LSubject: Oracle to Excel I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura
RE: Shared_pool
Jos, select value from v$parameter where name='shared_pool_size' will tell you the size of the shared pool; select * from v$sgastat where name like '%free%' and pool like '%shared%' will tell you how is free. Then you can calculate the percentage free. Hope this helps. Govind -Original Message- Sent: Wednesday, November 06, 2002 8:43 PM To: Multiple recipients of list ORACLE-L On Wed, Nov 06, 2002 at 04:34:28PM -0800, Jos Someone wrote: List, How do I find out how full my shared_pool is? -- Diagnosing and Resolving Error ORA-04031 Type: Note Doc ID: 146599.1 Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i Type: Note Doc ID: 62143.1 good reading! === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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.com -- 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: SQL Brain Teaser Challenge
based on the date it was written, that was before hints were available for use. so it makes sense to include the dummy comparison to the indexed column in the where clause as described in the note. You mean Oracle sometimes thinks of these things BEFORE we do? nah! --- Jared Still [EMAIL PROTECTED] wrote: Note 1009345.6 on MetaLink provides a solution similar to the one proposed with an index. Jared On Tuesday 05 November 2002 09:24, Orr, Steve wrote: Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SPIN_COUNT - 8i obsolete? What now?
there is a point at which you begin to tune for the sake of tuning and not because you are relieving a problem. if you are not getting reports of performance problems, why are you trying to fix it? the old adage if it ain't broke, don't fix it applies here. --- Denham Eva [EMAIL PROTECTED] wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SPIN_COUNT - 8i obsolete? What now?
Title: RE: SPIN_COUNT - 8i obsolete? What now? Already running on 100% caffeine_level saturation :) Maybe your right, the contention could be elsewhere, the other issue I have been investigating is LGWR process? With increasing the LOG_BUFFER from 512 to a Meg in size, as I have at times very high redo log switch, however they average about 3-4 an hour? Anyway the idea is to keep investigating. Regards Denham -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: SPIN_COUNT - 8i obsolete? What now? Denham Eva wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Increase caffeine_level. Seriously, is it a problem? A real bottleneck? You should begin to worry if you do not have worse contention elsewhere. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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). DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Out of procee memory problem ..
This is the error message come whenever a process run in which number of rows inserted into a big table (~1400 rows/min), and when this process (called from forms) inserted around 4,50,000 entries in the table it fails and flushes the following message: ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call heap , user umc) ORA-06512 at OBJ.PS_MATCH, Line 1298 ORA-06512 at line 1 PS: Down the database and up it again solve the problem but the reason is unknown to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Satyendra K Khare 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).
full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: Out of procee memory problem ..
Title: RE: Out of procee memory problem .. what is 4,50,000 ? -Original Message- From: Satyendra K Khare [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 8:34 AM To: Multiple recipients of list ORACLE-L Subject: Out of procee memory problem .. This is the error message come whenever a process run in which number of rows inserted into a big table (~1400 rows/min), and when this process (called from forms) inserted around 4,50,000 entries in the table it fails and flushes the following message: ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call heap , user umc) ORA-06512 at OBJ.PS_MATCH, Line 1298 ORA-06512 at line 1 PS: Down the database and up it again solve the problem but the reason is unknown to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Satyendra K Khare 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: full exp/imp of user to new tablespace; same user
Markham, Richard wrote: I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. If all the objects are already stored in a single tablespace the simplest way is enough. Otherwise you are going to have a problem since everything will be created in the default tablespace (if there is enough space). INDEXFILE is not a perfect solution either because it 'forgets' indices associated with constraints (UNIQUE, PRIMARY KEY). The simplest way is, if you can afford it, to size generously your tablespace so that everything fits in. Then move all indices you want elsewhere out through ALTER INDEX ... REBUILD which is reasonably fast and also works on indices associated with constraints. It's easy to generate the query to do it by querying DBA_SEGMENTS. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SPIN_COUNT - 8i obsolete? What now?
Title: RE: SPIN_COUNT - 8i obsolete? What now? Ouch! :) Rachel, It's not a case of me looking for problems. I was surprised to find this occuring so maybe doing something about it would improve the system alittle. I am looking into the redo log performance because I am unsure that the redo log switches are normal as they can be very high during office hrs as high as 60 switches in an hr, but having said that the switches avrg out at 3-4 switches an hour. So all the obvious are to be looked into ie checkpoints etc. From your line of thought in the email below, I am reading into it that you don't find this to be an issue, which is great. I will then just carry on checking all the other possible parameters and stats, until I am happy that the redo logs are as healthy as they can be. In my defence I did try the to change the parameter on my test system first. Hope that explains my line of thought and modus operandi. Regards Denham -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 3:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: SPIN_COUNT - 8i obsolete? What now? there is a point at which you begin to tune for the sake of tuning and not because you are relieving a problem. if you are not getting reports of performance problems, why are you trying to fix it? the old adage if it ain't broke, don't fix it applies here. --- Denham Eva [EMAIL PROTECTED] wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender,
RE: Out of procee memory problem ..
Hi, This is a bug in Oracle. Apply a latest patch on it. Regards Darshan Singh -Original Message- Khare Sent: Thursday, November 07, 2002 2:34 PM To: Multiple recipients of list ORACLE-L This is the error message come whenever a process run in which number of rows inserted into a big table (~1400 rows/min), and when this process (called from forms) inserted around 4,50,000 entries in the table it fails and flushes the following message: ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call heap , user umc) ORA-06512 at OBJ.PS_MATCH, Line 1298 ORA-06512 at line 1 PS: Down the database and up it again solve the problem but the reason is unknown to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Satyendra K Khare 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.com -- Author: Darshan Singh 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: Out of procee memory problem ..
version of oracle? On Thu, Nov 07, 2002 at 05:33:59AM -0800, Satyendra K Khare wrote: This is the error message come whenever a process run in which number of rows inserted into a big table (~1400 rows/min), and when this process (called from forms) inserted around 4,50,000 entries in the table it fails and flushes the following message: ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call heap , user umc) ORA-06512 at OBJ.PS_MATCH, Line 1298 ORA-06512 at line 1 PS: Down the database and up it again solve the problem but the reason is unknown to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Satyendra K Khare 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: SPIN_COUNT - 8i obsolete? What now?
Eva, just to correct myself from my earlier post it's entirely possible to set _spin_count using ALTER SYSTEM. It may be a sensible measure to try if you're having serious contention issues. Steve Adams has some 'spin_count' scripts that you might find useful at http://www.ixora.com.au/scripts/latches.htm http://www.ixora.com.au/scripts/latches.htm Regards, Mike Hately -Original Message- Sent: Thursday, November 07, 2002 1:29 PM To: Multiple recipients of list ORACLE-L Already running on 100% caffeine_level saturation :) Maybe your right, the contention could be elsewhere, the other issue I have been investigating is LGWR process? With increasing the LOG_BUFFER from 512 to a Meg in size, as I have at times very high redo log switch, however they average about 3-4 an hour? Anyway the idea is to keep investigating. Regards Denham -Original Message- mailto:sfaroult;oriole.com ] Sent: Thursday, November 07, 2002 1:59 PM To: Multiple recipients of list ORACLE-L -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Richard: if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED). Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken. Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line). If you want Indexes in a separate TS, then here's what I do: 1) set all as listed above, but make sure the user has unlimited quotas on both data and index TS 2) import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in 3) import again with only the indexfile option, which will create a sql script 4) Edit the indexfile to remove stuff you don't want. . .I usemacros in my text editor to remove (in this order): ** all the lines beginning with "REM ...xRows imported" ** "REM CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement); these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out. ** all remaining instances of the string "REM " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly. this leaves you with clean sql to just create constraints and indexes 5) do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want. 6) remove the line at the top that says "connect username" - you should connect first b4 running the script 7) save the file! 8) run the script to create all the indexes HTH bill -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 8:59 AMTo: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
RE: Oracle to Excel
Title: Oracle to Excel Thank you so much!! This works like a charm. Laura -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 6:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Laura, Sorry about that - instead of using chr(10), use chr(09). (10) is line feed. (09) is tab. also - don't forget to : set trimspool on - it gets rid of trailing spaces! good luck! Tom Mercadante Oracle Certified Professional -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column. Below is an example (1 record is shown): 53-041-02 0 2 AL POWER CO 51271 27-Nov-01 173.52 447-57 D412 173.52 226 DONNELL 43000 00014.04.D412 It looks like it thinks it is a carriage return instead of a tab. Any ideas? Laura -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Laura, lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format. You can do this yourself as follows: in SqlPLus select col1||chr(10)||col2||chr(10) from table. the chr(10) is the tab character. spool the results to a output.xls open the file using excel. the tab character is the default column delimiter. You should see all your data in cells. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Oracle to Excel I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura
RE: Out of procee memory problem ..
450,000 rows (four hundred and fifty thousands) In Indian subcontinent it is written as 4 lacs 50 thousands where 1 lac(lakh most commonly used) is equal to one hundred thousands... Hope this will calrify. The person who put his problem was from India. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 07 Nov 2002 06:04:05 -0800 what is 4,50,000 ? -Original Message- Sent: Thursday, November 07, 2002 8:34 AM To: Multiple recipients of list ORACLE-L This is the error message come whenever a process run in which number of rows inserted into a big table (~1400 rows/min), and when this process (called from forms) inserted around 4,50,000 entries in the table it fails and flushes the following message: ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call heap , user umc) ORA-06512 at OBJ.PS_MATCH, Line 1298 ORA-06512 at line 1 PS: Down the database and up it again solve the problem but the reason is unknown to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Satyendra K Khare 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). _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: M Rafiq 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: SPIN_COUNT - 8i obsolete? What now?
Title: RE: SPIN_COUNT - 8i obsolete? What now? log_buffer has nothing to do with log switches. Just with checkpointing and some other things I can't think of right now. Size of online redo logfiles does. Jack -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: donderdag 7 november 2002 14:29To: Multiple recipients of list ORACLE-LSubject: RE: SPIN_COUNT - 8i obsolete? What now? Already running on 100% caffeine_level saturation :) Maybe your right, the contention could be elsewhere, the other issue I have been investigating is LGWR process? With increasing the LOG_BUFFER from 512 to a Meg in size, as I have at times very high redo log switch, however they average about 3-4 an hour? Anyway the idea is to keep investigating. Regards Denham -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: SPIN_COUNT - 8i obsolete? What now? Denham Eva wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Increase caffeine_level. Seriously, is it a problem? A real bottleneck? You should begin to worry if you do not have worse contention elsewhere. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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). DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: full exp/imp of user to new tablespace; same user
-Original Message- I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. I have done many export/imports of user schemas without any problems in getting the entire schema. You shouldn't need to do anything special. To change the objects to a new tablespace: Drop the objects from the old tablespace; set the new tablespace to be the schema's default tablespace; set the schema's quota on the old tablespace(s) to be 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: SPIN_COUNT - 8i obsolete? What now?
Title: RE: SPIN_COUNT - 8i obsolete? What now? additional factors: log_checkpoint_timeoutlog_checkpoint_intervalfast_start_io_target90% size of smallest redo log alter system checkpoint -Original Message-From: Jack van Zanen [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: SPIN_COUNT - 8i obsolete? What now? log_buffer has nothing to do with log switches. Just with checkpointing and some other things I can't think of right now. Size of online redo logfiles does. Jack -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: donderdag 7 november 2002 14:29To: Multiple recipients of list ORACLE-LSubject: RE: SPIN_COUNT - 8i obsolete? What now? Already running on 100% caffeine_level saturation :) Maybe your right, the contention could be elsewhere, the other issue I have been investigating is LGWR process? With increasing the LOG_BUFFER from 512 to a Meg in size, as I have at times very high redo log switch, however they average about 3-4 an hour? Anyway the idea is to keep investigating. Regards Denham -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: SPIN_COUNT - 8i obsolete? What now? Denham Eva wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Increase caffeine_level. Seriously, is it a problem? A real bottleneck? You should begin to worry if you do not have worse contention elsewhere. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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). DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: SPIN_COUNT - 8i obsolete? What now?
Denham, Sorry, I didn't mean to make you wince! It's just a case of seeing way too many DBAs spending way too much time tuning things that really aren't a problem. Cary Millsap talks about this often. Find the business problem and tune that. It's possible that something that shows up as very slow when you look at the overall system is actually something that runs rarely and tuning it will give you no noticeable improvement. If the log switches are happening that frequently, you might look into resizing them. I'd first look at what is going on during office hours that's causing them to fill so fast. Rachel --- Denham Eva [EMAIL PROTECTED] wrote: Ouch! :) Rachel, It's not a case of me looking for problems. I was surprised to find this occuring so maybe doing something about it would improve the system alittle. I am looking into the redo log performance because I am unsure that the redo log switches are normal as they can be very high during office hrs as high as 60 switches in an hr, but having said that the switches avrg out at 3-4 switches an hour. So all the obvious are to be looked into ie checkpoints etc. From your line of thought in the email below, I am reading into it that you don't find this to be an issue, which is great. I will then just carry on checking all the other possible parameters and stats, until I am happy that the redo logs are as healthy as they can be. In my defence I did try the to change the parameter on my test system first. Hope that explains my line of thought and modus operandi. Regards Denham -Original Message- Sent: Thursday, November 07, 2002 3:18 PM To: Multiple recipients of list ORACLE-L there is a point at which you begin to tune for the sake of tuning and not because you are relieving a problem. if you are not getting reports of performance problems, why are you trying to fix it? the old adage if it ain't broke, don't fix it applies here. --- Denham Eva [EMAIL PROTECTED] wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). _ DISCLAIMER This message
RE: SQL Brain Teaser Challenge
Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj. Steve -Original Message- Sent: Wednesday, November 06, 2002 5:23 AM To: Multiple recipients of list ORACLE-L Directly from TFM Notes on Hierarchical Queries: If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy. -Original Message- Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: full exp/imp of user to new tablespace; same user
Title: full exp/imp of user to new tablespace; same user Extra security could be to give zero quota on other tablespaces. -Original Message-From: Markham, Richard [mailto:[EMAIL PROTECTED]]Sent: donderdag 7 november 2002 14:59To: Multiple recipients of list ORACLE-LSubject: full exp/imp of user to new tablespace; same user I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips.
Space management failures on autoextend datafiles
I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: full exp/imp of user to new tablespace; same user
And if all else fails (not that the advice already given will!), we have a tool that can manipulate the DDL for the users objects VERY quickly and easily, through a rule based change (e.g. Modify ALL tables storage clauses within USERS schema to TABLESPACE): http://www.cool-tools.co.uk/products/dbatool.html It's free, have at it! HTH Mark -Original Message- Sent: 07 November 2002 14:44 To: Multiple recipients of list ORACLE-L Extra security could be to give zero quota on other tablespaces. -Original Message- Sent: donderdag 7 november 2002 14:59 To: Multiple recipients of list ORACLE-L I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: SQL Brain Teaser Challenge
Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid You can also use sys_connect_by_path feature in 9i which gives you the entire hierarchy path (can be used in 8i with the above undoc parameter) For eg: select sys_connect_by_path(parent_id,'/') from treenode start with parent_id = 0 connect by prior id = parent_id will give you a output like 0/1/9 0/1/2 0/1/2/4 ...etc As always setting an
why is this happening
The following query is causing the following error ERROR at line 1: ORA-03232: unable to allocate an extent of 22 blocks from tablespace 3 select count(l.processid) from tmslog l, tmslogtimeout t where l.processid = t.processid and l.statifiedflag='Y' and t.processcompleteflag='Y' Tablespace #3 is temp, 800 MB, 128K extent size locally managed. The user is also set to use temp. If I do a alter session set sort_area_size=20M then it completes. Currently the sort_area_size is set via the init file as 5 mb. Ideas ? George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 07 November 2002 15:24 PM To: Multiple recipients of list ORACLE-L Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj. Steve -Original Message- Sent: Wednesday, November 06, 2002 5:23 AM To: Multiple recipients of list ORACLE-L Directly from TFM Notes on Hierarchical Queries: If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy. -Original Message- Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3
RE: Space management failures on autoextend datafiles
Dan, is your datafile approaching (or past) the maxsize setting? AUTOEXTEND functionality won't grow a file beyond this but I believe it's possible to manuallly resize the file beyond the MAXSIZE figure. That would explain the apparent anomaly. regards, Mike Hately -Original Message- Sent: Thursday, November 07, 2002 3:39 PM To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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).
CLOB inserts
One of my developers is testing a process that reads a CLOB, modifies it, and inserts it to another table. The reading and modifying goes very quickly, but the inserts take a long time. Looking at the main Wait Events I'm seeing a lot of direct path read (lob) and direct path write (lob) which seems to imply that a lot of the delay is i/o related. Does anyone have any suggestions to speed up the inserts? The developer has been playing around with chunk size but without any improvement. Oracle 8.1.7.2 Solaris 2.6 Here are the main wait events from statspack. The SQL*Net message from dblink is from a different process that was running at the same time: SQL*Net message from dblink 381,218 0 0 0 689.4 SQL*Net message to dblink 381,216 0 0 0 689.4 db file scattered read 11,034 0 0 0 20.0 direct path read (lob) 1,233 0 0 0 2.2 log file parallel write 696 0 0 0 1.3 direct path write (lob) 548 0 0 0 1.0 db file sequential read 217 0 0 0 0.4 db file parallel write170 0 0 0 0.3 control file parallel write 161 0 0 0 0.3 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Space management failures on autoextend datafiles
never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Re: Space management failures on autoextend datafiles
I've heard about the same problem yesterday (while attending Ann Arbor OUG conference), unfortunately - no solution, I think, they opened TAR. BTW, it was very good conference, great presentations by Jonathan Lewis. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 10:39 AM Subject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: full exp/imp of user to new tablespace; same user
Title: RE: full exp/imp of user to new tablespace; same user well now I can say I'm spoiled. I was given more than enough information to to accomplish the task (done) and a new tool to boot! =) Thanks alot everyone. -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: full exp/imp of user to new tablespace; same user And if all else fails (not that the advice already given will!), we have a tool that can manipulate the DDL for the users objects VERY quickly and easily, through a rule based change (e.g. Modify ALL tables storage clauses within USERS schema to TABLESPACE): http://www.cool-tools.co.uk/products/dbatool.html It's free, have at it! HTH Mark -Original Message- Sent: 07 November 2002 14:44 To: Multiple recipients of list ORACLE-L Extra security could be to give zero quota on other tablespaces. -Original Message- Sent: donderdag 7 november 2002 14:59 To: Multiple recipients of list ORACLE-L I have a full export of user to where I need to import all his objects/grants but into a NEW tablespace. What's the easiest way I can assure that ~everything~ is imported. Will I need to use a combination of INDEXFILE and other procedures or will a full export; drop tablespace; create new tablespace; alter user's default tablespace; full import suffice for all objects? Thanks for any tips. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: SQL Brain Teaser Challenge
To add another point, somebody else (the thread is too long for this lazy git to go back and search who! ;P) mentioned that the access plans *could* change if #3 were the solution you go for - however this can be overcome with the use of stored outlines.. my 2 pence :) Mark -Original Message- Sent: 07 November 2002 15:49 To: Multiple recipients of list ORACLE-L Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY
Re: Space management failures on autoextend datafiles
Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: SPIN_COUNT - 8i obsolete? What now?
Thanks Rachel. For Denham: Changing SPIN_COUNT from its default value is usually a Very Bad Thing. Your sleeps are caused by lots of competition for one or more latches. The best way to stop those sleeps from impacting response time is to cut out the unnecessary competition. Because of how we're all taught to tune SQL (by eliminating physical I/Os and then quitting), almost every system in the world suffers from unnecessarily excessive numbers of latch acquisition attempts. If the competition is for the shared pool or a library cache latch, then your application parses too much. For more details, see Scaling applications to massive user counts at www.hotsos.com/catalog. If the competition is for a cache buffers chains or cache buffers lru chain latch, then your application reads too many blocks from the buffer cache. For more details, see Why you should focus on LIOs instead of PIOs at www.hotsos.com/catalog. Yes, increasing SPIN_COUNT can reduce the number of sleeps, but it does so by wasting more user-mode CPU doing an operation called a busy wait. See the LIO/PIO paper mentioned earlier for details about what increasing SPIN_COUNT does to Oracle's latch acquisition algorithm. Rachel's right: Instead of worrying whether there are too many sleeps on your system, decide whether the business benefit of improving the performance of one or more specific business functions will be worth the effort. Direct performance improvement decisions by what's important to the BUSINESS, not by what some V$ table says might be wrong with your system. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Carmichael Sent: Thursday, November 07, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Denham, Sorry, I didn't mean to make you wince! It's just a case of seeing way too many DBAs spending way too much time tuning things that really aren't a problem. Cary Millsap talks about this often. Find the business problem and tune that. It's possible that something that shows up as very slow when you look at the overall system is actually something that runs rarely and tuning it will give you no noticeable improvement. If the log switches are happening that frequently, you might look into resizing them. I'd first look at what is going on during office hours that's causing them to fill so fast. Rachel --- Denham Eva [EMAIL PROTECTED] wrote: Ouch! :) Rachel, It's not a case of me looking for problems. I was surprised to find this occuring so maybe doing something about it would improve the system alittle. I am looking into the redo log performance because I am unsure that the redo log switches are normal as they can be very high during office hrs as high as 60 switches in an hr, but having said that the switches avrg out at 3-4 switches an hour. So all the obvious are to be looked into ie checkpoints etc. From your line of thought in the email below, I am reading into it that you don't find this to be an issue, which is great. I will then just carry on checking all the other possible parameters and stats, until I am happy that the redo logs are as healthy as they can be. In my defence I did try the to change the parameter on my test system first. Hope that explains my line of thought and modus operandi. Regards Denham -Original Message- Sent: Thursday, November 07, 2002 3:18 PM To: Multiple recipients of list ORACLE-L there is a point at which you begin to tune for the sake of tuning and not because you are relieving a problem. if you are not getting reports of performance problems, why are you trying to fix it? the old adage if it ain't broke, don't fix it applies here. --- Denham Eva [EMAIL PROTECTED] wrote: Hello Guru's I have run a script which indicates that of the three redo log latches two had occurences of sleeps. To overcome this usually you increase SPIN_COUNT by 50% or so. When trying to ALTER SYSTEM SET SPIN_COUNT = 3000; Present system is set as 2000. I get the following error. * ERROR at line 1: ORA-25138: SPIN_COUNT initialization parameter has been made obsolete Now what? what can I do to overcome the sleeps issues? Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your
RE: System Tablespace and Autoextend
I've been running with autoextend on (though limited to 2Gig) and never had a problem. -Original Message- Sent: Thursday, October 24, 2002 5:26 PM To: Multiple recipients of list ORACLE-L I run my SYSTEM tablesaces in autoextend, and have for some time. I run them that way from the point of database creation and have never had a problem. There were some problems with autoextend in earlier versions of 8 (and I think they managed to migrate to early 8i versions as well) with 2GB boundaries, but those have all been corrected. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. Â -Original Message- Sent: Thursday, October 24, 2002 4:46 PM To: Multiple recipients of list ORACLE-L Sam - I haven't made the system tablespace autoextend because I can't easily recover the space if it overextends. I would rather take the risk that something hits an error from a lack of space in the system tablespace. With other tablespaces you can always rebuild the tablespace if you need to. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 24, 2002 1:27 PM To: Multiple recipients of list ORACLE-L Hello All, I have heard several times that if the SYSTEM tablespace runs out of space and needs to autoextend (assuming autoextend is turned on for the data file), then you run the risk of the database crashing and of data dictionary corruption. I have never personally encountered this problem, so I have no experience on what actually does happen. I looked in metalink for documents on this, but turned up nothing. Does anybody have experience on the dangers of allowing the SYSTEM tablespace to autoextend and also any documents on Metalink or OTN that describe this problem? We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle versions are running on Windows NT (or Windows 2000). Thanks for any feedback. Sam Bootsma, OCP [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Bootsma 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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.com -- Author: Miller, Jay 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: SQL Brain Teaser Challenge
Hi Steve/Jared, It was coincidence that just when Steve posted this we were trying to flatten the hierarchy and PL/SQL was the only option and I was doing some reserach and stumbled upon this parameter and then I used to it to generate output using the sys_connect_by_path and then all I had to do was parse the output and then populate the level columns based on that to get my complete hierarchy You are right Jared, once you set the parameter, most of the things (except the siblings as its a new keyword only since 9i I think) start working as in 9i but with a undoc parameter. Regards, Madhavan http://www.dpapps.com On Thu, 07 Nov 2002 07:49:22 -0800, Orr, Steve [EMAIL PROTECTED] said: Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur 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).
shareplex core dumps - help
Hi all, We are having a shareplex issue and I am hoping someone here with shareplex knowledge can help me out. I don't know much about shareplex. Basically we are replicating to a target table which is partitioned by date. And partitions that are 3 days old will be set to read-only because there are not suppose to be anything new. But occasionally, some record with timestamp older than 3 days creep up, so of course shareplex couldn't put the record into the target partition because it's read-only. Now the problem is shareplex core dumps when this happens. I am very surprised that we couldn't have a rule that tells shareplex if you can't replicate then either discard the record or put it elsewhere. Core dump seems to be a extreme reaction to this. unfortunately this is hosted and ran by a hosting company and I have no shareplex here to test or verify. Any help is appreciated. Thanks Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard 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: SQL Brain Teaser Challenge
And surprise, surprise... per Metalink, OWS is unwilling to support the undocumented parameter approach. -Original Message- Sent: Thursday, November 07, 2002 9:14 AM To: Multiple recipients of list ORACLE-L To add another point, somebody else (the thread is too long for this lazy git to go back and search who! ;P) mentioned that the access plans *could* change if #3 were the solution you go for - however this can be overcome with the use of stored outlines.. my 2 pence :) Mark -Original Message- Sent: 07 November 2002 15:49 To: Multiple recipients of list ORACLE-L Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about
RE: suggestion w/c platforms to choose from...
Grace - Well if they want to build all you mentioned in-house, order taking , purchase order and accounting module,hr then I wouldn't worry too much about server sizing because it'll take them forever to get all that done. Well, I just couldn't avoid tossing that remark in, please excuse it. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:34 PM To: Multiple recipients of list ORACLE-L this will be build in-house... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 11:56 PM Grace - Amen to Yechiel's first rule. At the VERY least, warn your management that they need to consider a second system for the datamart. This way, if it works terribly, you don't look dumb. My experience is mainly Unix, so 200 OLTP users on an NT box sounds iffy from a throughput point of view. I would tend to go with a small 4-CPU Solaris server, especially if this is a critical application. From what I've heard, and I will defer to list members that have more experience here, 200 ERP users are near the upper limit of Windows-based systems, and if you ended up undersizing the system or you add users faster than expected, you might run out of headroom. A lot would depend on what your site's experience base is. If you are used to maintaining high-availability NT systems, then that might work well for you. If you care to mention which ERP system you will be using for the order taking, PO, HR, GL, etc., you may garner advice specific to that system. Many of us support ERP systems of different stripes and it is possible that different ERP systems behave better on some platforms. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:49 AM To: Multiple recipients of list ORACLE-L First rule: Do not mix OLTP and datamart. Datamart access is very heavy and complex SQL's that will impact your OLTP system. I think that an NT with 2-4 CPUs and a lot of memory will do (a not so educated guess). Controller with a lot of cache memory and fast disks (15,000 rpm). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 3:48 AM database will be used for oltp and data mart # of users -- 200 very critical, since order taking , purchase order and accounting module,hr will run on it.. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 9:03 PM As a rule buy the biggest, meanest, fault tolerance, with gigabytes of memory and terabytes of disk storage that you can buy. If you will provide more data about: 1) The size of the database 2) How many users 3) How critical is the system 4) The use of the system - data warehouse, OLTP etc then you will probably get a more specified answer. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 2:13 PM hi, can anyone suggestion w/c platform should i used to run oracle? wat are the things to consider in choosing platform? thanks Best regards, Grace Lim MIS Department Suy Sing Comm'l Corp. T- (632)-2474134 F- (632)-2474160 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: grace 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.com -- Author: Yechiel Adar 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.com --
RE: SharePlex core dumps - help
Title: RE: SharePlex core dumps - help Call Quest Software technical support. They will be more than happy to help you. I think there is already a patch out to fix this. Just call 1-800-306-9329 and once you speak with an operator, ask to place a support call for SharePlex. Nick -Original Message- From: Ji, Richard [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 8:35 AM To: Multiple recipients of list ORACLE-L Subject: shareplex core dumps - help Hi all, We are having a shareplex issue and I am hoping someone here with shareplex knowledge can help me out. I don't know much about shareplex. Basically we are replicating to a target table which is partitioned by date. And partitions that are 3 days old will be set to read-only because there are not suppose to be anything new. But occasionally, some record with timestamp older than 3 days creep up, so of course shareplex couldn't put the record into the target partition because it's read-only. Now the problem is shareplex core dumps when this happens. I am very surprised that we couldn't have a rule that tells shareplex if you can't replicate then either discard the record or put it elsewhere. Core dump seems to be a extreme reaction to this. unfortunately this is hosted and ran by a hosting company and I have no shareplex here to test or verify. Any help is appreciated. Thanks Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard 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: Space management failures on autoextend datafiles
Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
TEMP/RBS LMTs (was: Convert TEMP tablespace...)
10M temp TS? You must have quite a nicely controlled working environment, Jared! ;) But your example does bring up a question: For TEMP and RBS LMTs, does the Goldilocks Rule (128K/4M/128M uniform extents, post version 7.x) follow? I had created my TEMP and RBS with 1MB uniform before knowing about Goldilocks and the infamous How to Stop Defragmenting and Start Living white paper. And that paper is a little vague about TEMP and RBS LMTs, other than to say that there should be between 1024 and 4096 extents in each. Thoughts? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:Jared.Still;radisys.com] Sent: Wednesday, November 06, 2002 3:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: Convert TEMP tablespace from datafiles to tempfiles create temporary tablespace temp2 tempfile '/u01/oradata/dv01/temp2.dbf' size 10m extent management local uniform size 128k; HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: System Tablespace and Autoextend
FWIW I'd go with Dennis here. I don't like AUTOEXTEND on the SYSTEM tablespace. (In fact I'm not overenamoured of AUTOEXTEND on any datfile, except maybe on dev and sandbox databases). If the SYSTEM tablespace isn't used for rollbacks (apart from the SYSTEM rollback) or temporary segments and the auditing information is written to it's own tablespace then I can't think of a set of circumstances that would cause the SYSTEM tablespace to rapidly fill up. System upgrade/migrations are the exception to this rule but in general a DBA would plan ahead for those occasions anyway. -Original Message- Sent: Thursday, November 07, 2002 4:29 PM To: Multiple recipients of list ORACLE-L I've been running with autoextend on (though limited to 2Gig) and never had a problem. -Original Message- Sent: Thursday, October 24, 2002 5:26 PM I run my SYSTEM tablesaces in autoextend, and have for some time. I run them that way from the point of database creation and have never had a problem. There were some problems with autoextend in earlier versions of 8 (and I think they managed to migrate to early 8i versions as well) with 2GB boundaries, but those have all been corrected. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration -Original Message- Sent: Thursday, October 24, 2002 4:46 PM Sam - I haven't made the system tablespace autoextend because I can't easily recover the space if it overextends. I would rather take the risk that something hits an error from a lack of space in the system tablespace. With other tablespaces you can always rebuild the tablespace if you need to. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 24, 2002 1:27 PM Hello All, I have heard several times that if the SYSTEM tablespace runs out of space and needs to autoextend (assuming autoextend is turned on for the data file), then you run the risk of the database crashing and of data dictionary corruption. I have never personally encountered this problem, so I have no experience on what actually does happen. I looked in metalink for documents on this, but turned up nothing. Does anybody have experience on the dangers of allowing the SYSTEM tablespace to autoextend and also any documents on Metalink or OTN that describe this problem? We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle versions are running on Windows NT (or Windows 2000). Thanks for any feedback. Sam Bootsma, OCP [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: Space management failures on autoextend datafiles
Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Metrics server
I'm asking this for my counterpart he posted it on the message board, I'm seeing if we can get abetter responce from the mailing list We currently have oracle applications 11.5.7 installed on a single node. A secondary server has been setup to run the forms. Metrics server and client have been started on the primary node. Forms server and metrics client have been started on the secondary node. The secondary server was setup with a backup and restore from the primary server. appsweb.cfg in $OA_HTML/bin has been modified on both servers. We are getting a FRM-92050 when trying to use the secondary server. If this the correct method to setup the second server? Is appsweb.cfg the only file that needs to be modified? John P. Mac Isaac Database Administrator Standard Aero Ltd. Winnipeg, MB. Canada R3H 1A1 Phone (204) 788-2127 [EMAIL PROTECTED] NOTICE OF CONFIDENTIALITY - THIS MESSAGE IS INTENDED ONLY FOR THE ADDRESSEES. It may contain privileged, confidential or proprietary information. Any unauthorized disclosure, use, copying or distribution of the contents of this message or the taking of any action on the contents of this message is strictly prohibited. If received in error, please promptly notify the sender and delete the original and all copies from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mac Isaac, John 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: System Tablespace and Autoextend
I use AUTOEXTEND and it has been extremely helpful to me in managing growth. However, my policy is not to use autoextend for SYSTEM, rollback tablespace, or temp tablespace. SYSTEM (for me) is relatively stable. The only time I have significant growth in SYSTEM is during an upgrade. For rollback and temp tablespaces, I don't want to throw away diskspace on unreasonable or abnormal usage. If I grow these spaces, I've made certain that it is necessary. Kip Bryant |FWIW I'd go with Dennis here. I don't like AUTOEXTEND on the SYSTEM |tablespace. |(In fact I'm not overenamoured of AUTOEXTEND on any datfile, except maybe on |dev and sandbox databases). |If the SYSTEM tablespace isn't used for rollbacks (apart from the SYSTEM |rollback) or temporary segments and the auditing information is written to |it's own tablespace then I can't think of a set of circumstances that would |cause the SYSTEM tablespace to rapidly fill up. |System upgrade/migrations are the exception to this rule but in general a |DBA would plan ahead for those occasions anyway. |-Original Message- |Sent: Thursday, November 07, 2002 4:29 PM |To: Multiple recipients of list ORACLE-L |I've been running with autoextend on (though limited to 2Gig) and never had |a problem. |-Original Message- |Sent: Thursday, October 24, 2002 5:26 PM |I run my SYSTEM tablesaces in autoextend, and have for some time. I run them |that way from the point of database creation and have never had a problem. |There were some problems with autoextend in earlier versions of 8 (and I |think they managed to migrate to early 8i versions as well) with 2GB |boundaries, but those have all been corrected. |RF |Robert G. Freeman - Oracle OCP |Oracle Database Architect |CSX Midtier Database Administration |-Original Message- |Sent: Thursday, October 24, 2002 4:46 PM |Sam - | I haven't made the system tablespace autoextend because I can't easily |recover the space if it overextends. I would rather take the risk that |something hits an error from a lack of space in the system tablespace. With |other tablespaces you can always rebuild the tablespace if you need to. |Dennis Williams |DBA, 40%OCP |Lifetouch, Inc. |[EMAIL PROTECTED] |-Original Message- |Sent: Thursday, October 24, 2002 1:27 PM |Hello All, |I have heard several times that if the SYSTEM tablespace runs out of space |and needs to autoextend (assuming autoextend is turned on for the data |file), then you run the risk of the database crashing and of data dictionary |corruption. I have never personally encountered this problem, so I have no |experience on what actually does happen. |I looked in metalink for documents on this, but turned up nothing. Does |anybody have experience on the dangers of allowing the SYSTEM tablespace to |autoextend and also any documents on Metalink or OTN that describe this |problem? |We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle |versions are running on Windows NT (or Windows 2000). |Thanks for any feedback. |Sam Bootsma, OCP |[EMAIL PROTECTED] |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Hately, Mike (NESL-IT) | 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.com -- 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: Convert TEMP tablespace from datafiles to tempfiles
Jesse, I did the same thing last week on our sandbox system using the method others have prescribed. There is a note 140913.1 covering a LMT bug in 8.1.6. under OpenVMS. You might want to double check to make sure no equivalent problem exists on your platform. Ron, As Jared pointed out, it's the Temporary tablespace (not a tablespace with temporary contents) that permits local extent management. In 8.1.7 I'm sure you've already tried: create tablespace temp_contents datafile '/oracle/.../temp.data1' size 128M temporary extent management local uniform size 4M; or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7 will let you assign a Permanent LMT as temporary_tablespace for a user, but won't let you create temporary segments there. Mike -Original Message- Sent: Wednesday, November 06, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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.com -- Author: Hand, Michael T 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).
When will Oracle 10i be out?
Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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: shareplex core dumps - help
Sounds like a bug. If SharePlex gets an error applying SQL to the target, the table should get marked out-of-sync and Shareplex should go on it's merry way. Call Shareplex Support - they are usually very responsive. - Jerry -Original Message- Sent: Thursday, November 07, 2002 11:35 AM To: Multiple recipients of list ORACLE-L Hi all, We are having a shareplex issue and I am hoping someone here with shareplex knowledge can help me out. I don't know much about shareplex. Basically we are replicating to a target table which is partitioned by date. And partitions that are 3 days old will be set to read-only because there are not suppose to be anything new. But occasionally, some record with timestamp older than 3 days creep up, so of course shareplex couldn't put the record into the target partition because it's read-only. Now the problem is shareplex core dumps when this happens. I am very surprised that we couldn't have a rule that tells shareplex if you can't replicate then either discard the record or put it elsewhere. Core dump seems to be a extreme reaction to this. unfortunately this is hosted and ran by a hosting company and I have no shareplex here to test or verify. Any help is appreciated. Thanks Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard 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.com -- Author: Cunningham, Gerald 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: When will Oracle 10i be out?
I heard that the white paper has not yet been published internally... -Original Message- Sent: Thursday, November 07, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- Author: Wong, Bing 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: System Tablespace and Autoextend
Title: RE: System Tablespace and Autoextend I have read complaints about SYS.IDL% objects having PCTINCREASE 50 and space problems when they extend, in the forums. I went to check and mine are already set to 0 along with 2147483645 MAXEXTENTS. I created a TAR and verified with Oracle that altering storage options for sys/system objects was supported as long as they were not decreased below the defaults. The exception was that if you have a large MAXEXTENTS on the object then you could decrease PCTINCREASE or set it to 0. I have checked NEXTEXTENT values and there are some objects which aren't ready to extend but when/if they do (probably result of migration) they will consume 250mb which to me is obviously excessive. I haven't taken any action of course after all it is SYSTEM!, but PCTINCREASE of 50 on objects that have grown a couple times does inevitably invite space consumption issues at some point in the future. -Original Message- From: Hately, Mike (NESL-IT) [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: System Tablespace and Autoextend FWIW I'd go with Dennis here. I don't like AUTOEXTEND on the SYSTEM tablespace. (In fact I'm not overenamoured of AUTOEXTEND on any datfile, except maybe on dev and sandbox databases). If the SYSTEM tablespace isn't used for rollbacks (apart from the SYSTEM rollback) or temporary segments and the auditing information is written to it's own tablespace then I can't think of a set of circumstances that would cause the SYSTEM tablespace to rapidly fill up. System upgrade/migrations are the exception to this rule but in general a DBA would plan ahead for those occasions anyway. -Original Message- Sent: Thursday, November 07, 2002 4:29 PM To: Multiple recipients of list ORACLE-L I've been running with autoextend on (though limited to 2Gig) and never had a problem. -Original Message- Sent: Thursday, October 24, 2002 5:26 PM I run my SYSTEM tablesaces in autoextend, and have for some time. I run them that way from the point of database creation and have never had a problem. There were some problems with autoextend in earlier versions of 8 (and I think they managed to migrate to early 8i versions as well) with 2GB boundaries, but those have all been corrected. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration -Original Message- Sent: Thursday, October 24, 2002 4:46 PM Sam - I haven't made the system tablespace autoextend because I can't easily recover the space if it overextends. I would rather take the risk that something hits an error from a lack of space in the system tablespace. With other tablespaces you can always rebuild the tablespace if you need to. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 24, 2002 1:27 PM Hello All, I have heard several times that if the SYSTEM tablespace runs out of space and needs to autoextend (assuming autoextend is turned on for the data file), then you run the risk of the database crashing and of data dictionary corruption. I have never personally encountered this problem, so I have no experience on what actually does happen. I looked in metalink for documents on this, but turned up nothing. Does anybody have experience on the dangers of allowing the SYSTEM tablespace to autoextend and also any documents on Metalink or OTN that describe this problem? We are running Oracle versions 7.3.4, 8.0.5, 8.1.7, and 9.2. All our Oracle versions are running on Windows NT (or Windows 2000). Thanks for any feedback. Sam Bootsma, OCP [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: Data Purging Strategy
FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: When will Oracle 10i be out?
Actually what I have heard is that nothing has been published externally. Oracle recommends to contact sales rep on info concerning future releases Rick Wong, Bing bing.wong@Ingram To: Multiple recipients of list ORACLE-L Micro.com [EMAIL PROTECTED] Sent by:cc: [EMAIL PROTECTED]Subject: RE: When will Oracle 10i be out? 11/07/2002 01:33 PM Please respond to ORACLE-L I heard that the white paper has not yet been published internally... -Original Message- Sent: Thursday, November 07, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- Author: Wong, Bing 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.com -- 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: When will Oracle 10i be out?
Are you done with 9i? :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 1:09 PM Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- Author: Igor Neyman 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: When will Oracle 10i be out?
The day we all finish upgrading our databases to 9i. Sunil Nookala DBA Dell Corp. -Original Message- Sent: Thursday, November 07, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- 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: Data Purging Strategy
Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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
Re: TEMP/RBS LMTs (was: Convert TEMP tablespace...)
10M? Hardly. That was just a test script to make sure the syntax was correct. 10m just happens to be created very quickly. The sort_area_size equal or be a multiple of your TEMP extent size. Jared On Thursday 07 November 2002 09:18, Jesse, Rich wrote: 10M temp TS? You must have quite a nicely controlled working environment, Jared! ;) But your example does bring up a question: For TEMP and RBS LMTs, does the Goldilocks Rule (128K/4M/128M uniform extents, post version 7.x) follow? I had created my TEMP and RBS with 1MB uniform before knowing about Goldilocks and the infamous How to Stop Defragmenting and Start Living white paper. And that paper is a little vague about TEMP and RBS LMTs, other than to say that there should be between 1024 and 4096 extents in each. Thoughts? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:Jared.Still;radisys.com] Sent: Wednesday, November 06, 2002 3:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: Convert TEMP tablespace from datafiles to tempfiles create temporary tablespace temp2 tempfile '/u01/oradata/dv01/temp2.dbf' size 10m extent management local uniform size 128k; HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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:RE: When will Oracle 10i be out?
Heck, Would you people kindly give some of us time to get fully up on 8i first!! Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/7/2002 11:08 AM The day we all finish upgrading our databases to 9i. Sunil Nookala DBA Dell Corp. -Original Message- Sent: Thursday, November 07, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Oracle crash produces no logs or dumps
Greetings all - I've had 3 of the several databases I run crash in the past 2 months without producing any logs or dumps. I have BACKGROUND_DUMP_DEST, etc. set. What else can I set so that Oracle is more verbose when it encounters a problem? For instance, today, some users filled up the rollback segments on a 8.1.7.0 database, producing a ORA-1650. Right after that, I could not connect to the database via sqlplus. Via svrmgrl, I got Oracle not available when doing a test query. Shutdown abort in srvmgrl returned a success message, but several server process continued to run. After about 10 minutes of trying to diagnose it, I gave up and rebooted the box. I am running Oracle 8.1.5.1 and 8.1.7.0 on Solaris 7. Thanks. -- Tom Hetrick [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tom Hetrick 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: Convert TEMP tablespace from datafiles to tempfiles
Mike, Perhaps the reason that I was confused is that I did not phrase the question correctly. If you create a tablespace to be used as the temporary tablespace for users and create it as type temporary ( segments used by implicit sorts to handle order by clause) you can not use the LMT clauses. If you create a temporary tablespace that is only in existence for the duration of the session you can use the LMT clauses. Does this mean that you have to recreate the temporary tablespace after a reboot? Another point about LMT's. I like them and use them in the majority of my tablespaces. When you choose the uniform size it can be what ever you choose to work comfortably with the data size of the table. If I remember correctly( working through the gray hair again) a temporary extent should be equall to or a multiple of the sort area size so it does not create unnecessary thrashing when trying to fit a sort into the temp. Am I correct in what I remember? THanks. Ron [EMAIL PROTECTED] 11/07/02 12:53PM Jesse, I did the same thing last week on our sandbox system using the method others have prescribed. There is a note 140913.1 covering a LMT bug in 8.1.6. under OpenVMS. You might want to double check to make sure no equivalent problem exists on your platform. Ron, As Jared pointed out, it's the Temporary tablespace (not a tablespace with temporary contents) that permits local extent management. In 8.1.7 I'm sure you've already tried: create tablespace temp_contents datafile '/oracle/.../temp.data1' size 128M temporary extent management local uniform size 4M; or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7 will let you assign a Permanent LMT as temporary_tablespace for a user, but won't let you create temporary segments there. Mike -Original Message- Sent: Wednesday, November 06, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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.com -- Author: Hand, Michael T 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.com -- 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: RE: When will Oracle 10i be out?
Title: RE: RE: When will Oracle 10i be out? 8i? You luck dog. I'm still trying to get about 15 systems up to 7.3 (from 7.2) Matt Adams - GE Appliances - [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 2:35 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: When will Oracle 10i be out? Heck, Would you people kindly give some of us time to get fully up on 8i first!! Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/7/2002 11:08 AM The day we all finish upgrading our databases to 9i. Sunil Nookala DBA Dell Corp. -Original Message- Sent: Thursday, November 07, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Has anyone heard when Oracle will be releasing version 10i? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pall, Tom [Contractor] 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Space management failures on autoextend datafiles
I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: Issue on disaster recovery
Title: RE: RE: When will Oracle 10i be out? Guys, To save time we thought we could simply copy Oracle from one host with version Solaris 2.8 to another host with version 2.6. The error we are getting is: ld.so.1: svrmgrl: fatal: libgen.so.1: open failed: No such file or directory Killed and I cannot open any databases. Can I fix this by relinking? Why is this file being called? My LD_LIBRARY_PATH looks correct and on the previous host this file existed so it must be a Solaris 2.8 library (so says system admin.). Do I have to reinstall Oracle software for this to work? Can I just copy over the file or what if it was the same version of Solaris? Thanks, Paula
Changing column format
I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Issue on disaster recovery
Paula - When you say you copied Oracle from one host to another, what do you mean? Did you copy the Oracle data files or the Oracle binaries? I am not familiar with those two Solaris versions, and you didn't say which Oracle version this is, but you might check the certification matrix to make sure this Oracle version will work on both systems. I'm assuming you considered installing Oracle on the other system and decided to save time. Just a couple of off-the-top ideas. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Guys, To save time we thought we could simply copy Oracle from one host with version Solaris 2.8 to another host with version 2.6. The error we are getting is: ld.so.1: svrmgrl: fatal: libgen.so.1: open failed: No such file or directory Killed and I cannot open any databases. Can I fix this by relinking? Why is this file being called? My LD_LIBRARY_PATH looks correct and on the previous host this file existed so it must be a Solaris 2.8 library (so says system admin.). Do I have to reinstall Oracle software for this to work? Can I just copy over the file or what if it was the same version of Solaris? Thanks, Paula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Issue on disaster recovery
Title: RE: RE: When will Oracle 10i be out? I tried a relink and I think that fixed it. -Original Message-From: Stankus, Paula G Sent: Thursday, November 07, 2002 3:18 PMTo: '[EMAIL PROTECTED]'Subject: RE: Issue on disaster recovery Guys, To save time we thought we could simply copy Oracle from one host with version Solaris 2.8 to another host with version 2.6. The error we are getting is: ld.so.1: svrmgrl: fatal: libgen.so.1: open failed: No such file or directory Killed and I cannot open any databases. Can I fix this by relinking? Why is this file being called? My LD_LIBRARY_PATH looks correct and on the previous host this file existed so it must be a Solaris 2.8 library (so says system admin.). Do I have to reinstall Oracle software for this to work? Can I just copy over the file or what if it was the same version of Solaris? Thanks, Paula
RE: Issue on disaster recovery
Title: RE: Issue on disaster recovery It was Solaris 2.8 to Solaris 2.6 and I copied the Oracle binaries and datafiles for one database a small one. It does save time and relinking did seem to resolve the issue as relinking with the new version of Solaris caused Oracle to point to the correct libraries from the OS-side. -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 3:45 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: Issue on disaster recovery Paula - When you say you copied Oracle from one host to another, what do you mean? Did you copy the Oracle data files or the Oracle binaries? I am not familiar with those two Solaris versions, and you didn't say which Oracle version this is, but you might check the certification matrix to make sure this Oracle version will work on both systems. I'm assuming you considered installing Oracle on the other system and decided to save time. Just a couple of off-the-top ideas. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: Issue on disaster recovery Guys, To save time we thought we could simply copy Oracle from one host with version Solaris 2.8 to another host with version 2.6. The error we are getting is: ld.so.1: svrmgrl: fatal: libgen.so.1: open failed: No such file or directory Killed and I cannot open any databases. Can I fix this by relinking? Why is this file being called? My LD_LIBRARY_PATH looks correct and on the previous host this file existed so it must be a Solaris 2.8 library (so says system admin.). Do I have to reinstall Oracle software for this to work? Can I just copy over the file or what if it was the same version of Solaris? Thanks, Paula
RE: Changing column format
David - First, export the table the way it is now. Next, what are the end-user implications of this change? Are users querying this? Adding/changing records? Do you have applications that depend on this that must be changed at the same time? The simple answer is to alter table drop primary key, then alter table add primary key (look the exact syntax up in the manual), which will work unless there is a great danger of users adding duplicate records during the time the table doesn't have a primary key. Also are there any other tables that rely on this primary key for referential integrity? Also, depending on the urgency, you may want to create a unique index on username to make sure you don't have any duplicates before you drop your current primary index. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 2:34 PM To: Multiple recipients of list ORACLE-L I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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.com -- 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: Space management failures on autoextend datafiles
Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Space management failures on autoextend datafiles I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Re: Changing column format
Nguyen, David M wrote: I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David David, Are you sure that you want username to be primary key, instead of unique (and possibly not null) ? IMHO a userid is a better potential PK than a username. If you already have tables referencing this one, it would be better to create a unique constraint on username - BTW you can define a FK on a unique constraint. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
HOW TO FIND FREQUNT OBJECT ACCESS
Hi How to check which are those functions/procedures/packages etc applications are using more frequntly? THx -Seema _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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: Can any one tell me how can i read parameter info from RDF(Re
You'll have to open the report using Oracle Developer (Reports). If it is on Unix I usually ftp it to my local workstation and open it there. Jay Miller x48355 -Original Message- Sent: Tuesday, November 05, 2002 1:13 AM To: Multiple recipients of list ORACLE-L definition file) Can any one tell me how can i read parameter info from RDF(Reports definition file) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Changing column format
Title: RE: Changing column format David, I suggest that you don't. There are many Jim Jones in the world. How are you going to handle that? Is this field really your primary key and related to other tables or do you just need to make sure there are no duplicate names? If so, create a unique constraint instead. If you must, first make sure that there is not already a duplicate name. SELECT username, count(username) FROM your_table_name GROUP BY username HAVING count(username) 1; If you have any records returned, you need to fix your data before creating the primary key. Same thing with null values. If the SQL below returns a number other than zero, you need to put something in the null values before creating the primary key. SELECT count(username) FROM your_table_name where username = Null; To drop the primary key: ALTER TABLE your_table_name DROP PRIMARY KEY CASCADE; To create a primary key: ALTER TABLE your_table_name ADD PRIMARY KEY (username); Personally, I think you are going to regret doing this. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Nguyen, David M [SMTP:[EMAIL PROTECTED]] I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David
CPU Problem
Hi List, We are facing with some CPU problem (cpu 100% usage!), Just want to get some idea from you guys, any clue what can cause this problem: Here is a copy of some part of ora.ini This happen during usage of some long query with subquery, this can be the cause of problem or just some wrong setting in ini file. I appreciate your help. compatible = 8.1.7.4 db_block_buffers = 1 db_block_lru_latches = 4 db_block_size = 8192 db_file_multiblock_read_count = 16 hash_area_size = 2048000 java_pool_size = 20971520 large_pool_size = 614400 log_buffer = 262144 log_checkpoint_interval = 1 log_checkpoint_timeout = 1800 max_enabled_roles = 30 open_cursors = 300 optimizer_index_caching = 90 optimizer_index_cost_adj = 35 os_authent_prefix = processes = 100 session_cached_cursors = 100 shared_pool_size = 134217728 sort_area_retained_size = 262144 sort_area_size = 262144 timed_statistics = TRUE Hamid Alavi Office 818 737-0526 Cell818 416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Data Purging Strategy
Thomas, Jay Here is my thought for your consideration, evisceration. A fundamental principle of backup and recovery is that you can only consider a backup to be good if you've tested a recovery. Why not apply this principle to data archiving? In other words, when you upgrade to a new Oracle version, as part of the testing process, load each of the ancient archives. That may be a good time to re-store the archive again using the new Oracle version. More work, but you've proven that the new Oracle version can successfully read the old archives. If it can't then you deal with that issue right then, perhaps by reading them again with the current Oracle version or something. If you put the old data out on archive tapes and seriously expect to retrieve it, this is the only practice that makes sense to me. On the other hand, if recovery is only a maybe thing, then by all means just toss it out on tape and let the tapes gather cobwebs. I do that at home all the time. I want to toss something, but I'm not sure, so I stick it in the garage in case I change my mind. But if I feel this is something valuable and delicate I certainly wouldn't take for granted recovery from the garage after several years. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy
RE: Space management failures on autoextend datafiles
maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Changing column format
David, First, check the username column to insure that there are no duplicate entries. If there are, you cannot make it a primary key. Second, remove the current primary key constraint as there can only be one primary key constraint per table. Third, create the new primary key constraint. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 1:34 PM To: Multiple recipients of list ORACLE-L I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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.com -- Author: Fink, Dan 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: Clone Production Server to Stand by Server on 8.1.7 on Win 2k
You don't need to rebuild when a datafile is added. Once the recovery fails you just issue the create datafile command on the standby. Jay Miller -Original Message- Sent: Tuesday, November 05, 2002 12:34 PM To: Multiple recipients of list ORACLE-L 2k First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared Arif Khan (GWL) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Changing column format
Title: RE: Changing column format 1. Backup Table 2. ALTER TABLE user DROP PRIMARY KEY CASCADE; This will drop the constraint and delete all foreign key relationships to userid. 3. ALTER TABLE user ADD CONSTRAINT username_pk PRIMARY KEY (username) USING INDEX TABLESPACE USER_INDEX; This will create a new primary key constraint on username IF the username field is unique and does not contain nulls. The second line is optional. Laura -Original Message- From: Nguyen, David M [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 2:34 PM To: Multiple recipients of list ORACLE-L Subject: Changing column format I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Changing column format
David, You can alter table drop constraint to get rid of the current pk and then create a new one using the column you desire. Ron [EMAIL PROTECTED] 11/07/02 03:33PM I create a table to store user account information and set userid column to be primary key. I now want to set username to be primary key instead of userid, how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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.com -- 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: Changing column format
David Try this... alter table tablename drop primary key; alter table tablename add constraint tbl_pkey primary key (username ); Viral From: "Nguyen, David M" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Changing column format Date: Thu, 07 Nov 2002 12:33:39 -0800 I create a table to store user account information and set "userid" column to be primary key. I now want to set "username" to be primary key instead of "userid", how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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). The new MSN 8: smart spam protection and 2 months FREE* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai 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).
[no subject]
Hello fokls. First, I do not and have never worked for Oracle. I thought I'd show you some funny and interesting comments from the Oracle source tree that I've been made privy to. Here is today's installment. /* retch, gag, choke. If this bit is off, oracle doesn't break/reset the ttc pipe in the event of an error. When oracle is running an NPI execution, there may well be data left in the pipe when an error occurs at type-check time since the current crop of bind variables are sent along with the execute function block . Since this bit is always cleared, the data stays in the pipe and fouls things up on the next transmit. So...don't clear the bit here if executing an OEXEC (or an OALL, which may execute an OEXEC). Instead, let opiobv clear it. This means that any execute error will mean a break/reset, even if there is no data left in the pipe. Tough luck, campers */ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraclegeek 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: Data Purging Strategy
Well, if worst comes to worst we can always install an earlier version on a box and import it there. But the reason we can't get more storage approved still has me shaking my head... -Original Message- Sent: Thursday, November 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Jay, just make sure you are not around when, after several Oracle upgrades, and they want to import one of these files back that they discover that the current release of import can no longer read the older version of the .dmp file. now what are these senior damagers going to do? blame the DBA, that's what! duck and cover... duck and cover... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, November 07, 2002 1:55 PM To: Multiple recipients of list ORACLE-L FWIW, what we just implemented (because senior management refuses to approve additional storage on the grounds that making the database larger will affect performance - aaargh!) is 1) Confirmed with business how long data needs to be online for various tables (they're all partitioned so that makes it a lot easier) 2) Export partitions older than that once/month (this is generated off a table that lists each partitioned table and how long data should be kep) 3) After confirming that all export files are valid we drop the old partitions (this will be done by script but is being done manually for the first few months) 4) Leave dmp files on server for 2 end of months (our end of month backup tapes are stored for 7 years) 5) Maintain a table in database saying what exported partitions are on what date's tapes And I really long for the days in this company when senior management made technical decisions by asking the technical people instead of just making things up... Jay Miller -Original Message- Sent: Wednesday, November 06, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Someone asked about this 3 weeks ago. Here's my take on archiving data. I don't expect everyone to agree with this, but nonetheless, I have an opinion. :) Here's an email from last month. You can undoubtedly find some other ideas on this by searching the archives of this list at fatcity.com Jared == I'm not a proponent of purging data. Unless of course, you expect to never see it again. That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's Dark Ages II: When the Digital Data Die http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 01:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Data Purging Strategy Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network
RE: CPU Problem
Hamid - Does the system performance become sluggish for other users during this time? My offhand guess is that the subquery is repeatedly scanning data blocks it has loaded in memory. Have you used EXPLAIN PLAN? Can you run STATSPACK during this situation? Or if you find it easier, examine V$SESSION_WAIT or V$SESSION_EVENT to see what your system waits are. Bear in mind that you must check the difference in values over a time interval, something STATSPACK will do for you. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Hi List, We are facing with some CPU problem (cpu 100% usage!), Just want to get some idea from you guys, any clue what can cause this problem: Here is a copy of some part of ora.ini This happen during usage of some long query with subquery, this can be the cause of problem or just some wrong setting in ini file. I appreciate your help. compatible = 8.1.7.4 db_block_buffers = 1 db_block_lru_latches = 4 db_block_size = 8192 db_file_multiblock_read_count = 16 hash_area_size = 2048000 java_pool_size = 20971520 large_pool_size = 614400 log_buffer = 262144 log_checkpoint_interval = 1 log_checkpoint_timeout = 1800 max_enabled_roles = 30 open_cursors = 300 optimizer_index_caching = 90 optimizer_index_cost_adj = 35 os_authent_prefix = processes = 100 session_cached_cursors = 100 shared_pool_size = 134217728 sort_area_retained_size = 262144 sort_area_size = 262144 timed_statistics = TRUE Hamid Alavi Office 818 737-0526 Cell818 416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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.com -- 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: TEMP/RBS LMTs (was: Convert TEMP tablespace...)
Does the SORT_AREA_SIZE not being a multiple of TEMP extent size have that much impact if disk sorts are only 0.03% (3/100ths of 1 percent) of total sorts? My numbers are according to V$SYSSTAT. What resource is affected? Disk? Memory? CPU? Beer? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jared Still [mailto:jkstill;cybcon.com] Sent: Thursday, November 07, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Subject: Re: TEMP/RBS LMTs (was: Convert TEMP tablespace...) 10M? Hardly. That was just a test script to make sure the syntax was correct. 10m just happens to be created very quickly. The sort_area_size equal or be a multiple of your TEMP extent size. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Clone Production Server to Stand by Server on 8.1.7 on Win 2k
Right. Stand-by is exactly what you are trying to simulate. The archive logs will reproduce 99% of what happens in the production environment. In addition, it will provide for a quicker recovery. AT From: Miller, Jay [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Clone Production Server to Stand by Server on 8.1.7 on Win 2k Date: Thu, 07 Nov 2002 13:23:58 -0800 MIME-Version: 1.0 Received: from newsfeed.cts.com ([209.68.248.164]) by mc4-f38.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Thu, 7 Nov 2002 13:59:00 -0800 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id NAA59309;Thu, 7 Nov 2002 13:58:01 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004FE57A; Thu, 07 Nov 2002 13:23:58 -0800 Message-ID: [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Miller, Jay [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 07 Nov 2002 21:59:00.0266 (UTC) FILETIME=[E0B940A0:01C286A8] You don't need to rebuild when a datafile is added. Once the recovery fails you just issue the create datafile command on the standby. Jay Miller -Original Message- Sent: Tuesday, November 05, 2002 12:34 PM To: Multiple recipients of list ORACLE-L 2k First off, your process is doing a lot of unnecessary work. Standby databases are available in 7.3.4. I believe that would be somewhat simpler than your current procedure. I haven't tried it though, so I could be wrong. Even with your current procedure, you don't need to copy all of the files, most of the time anyway. Build your standby database, put it in recovery mode, and just keep applying archive log files to it. You may need to rebuild when a datafile is added to a tablespace, not sure. In 8i, why not just use standby database? Jared Arif Khan (GWL) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 06:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Clone Production Server to Stand by Server on 8.1.7 on Win 2k Hello We currently have two Identical servers (identical in terms of both Hardware and SW). We run Oracle 7.3.4.0 (Workgroup server) on Win NT 4 on both the servers. We call them as Production Server and Stand by server. The Stand by server is passive in nature (i.e. does not do anything). Every night a batch process shuts down Oracle instance on both the machines and copies over all the files (Data, log, ctl etc) from Production Server to the Stand by server (Drive to drive, directory to directory...) In case the Production Server fails, we simply switch over the users (with a different alias to the stand by server) and they are back in business. Now, we are thinking of migrating to 8.1.7, however while trying to install this version, one needs to specify a Global name which I believe has to be unique on the network. So will the same process that I used to run (i.e. copy all files over from production to stand by ) work??? I guess both my servers will now have to have separate/unique Global Names. Is there any other approach that any of you can suggest??? TIA Arif -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arif Khan (GWL) 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: Changing column format
Title: RE: Changing column format I believe "username" herewould be a unique identifier. In most systems, username must be unique (at least within a particular domain). If yours is a single domain system, David, then having two Jim Joneses would not be the problem. There is another, more database-specific reason not to use the username field as the primary key: username(I assume) has semantic meaning, andm further, I assume, could change. For example, let's assume my username is "adonahue". Later I get a promotionand I want avanity username of "adam". Let's also assume your database consists of several tables, many of which reference the user table by username. In this case, updating the username will require updating ALL rows in all tables to reflect the new name. (That is, the data structure becomes denormalized if username is the primary key.) If you use userid, you can simply update the user table referenced by the corresponding userid, and no further changes would be required in child tables. Jerry's suggestion is best: userid as the primary (surrogate) key, and a non-null unique constraint on username to prevent duplicate names within the same system. Adam -Original Message-From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 4:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Changing column format David, I suggest that you don't. There are many "Jim Jones" in the world. How are you going to handle that? Is this field really your primary key and related to other tables or do you just need to make sure there are no duplicate names? If so, create a unique constraint instead. If you must, first make sure that there is not already a duplicate name. SELECT username, count(username) FROM your_table_name GROUP BY username HAVING count(username) 1; If you have any records returned, you need to fix your data before creating the primary key. Same thing with null values. If the SQL below returns a number other than zero, you need to put something in the null values before creating the primary key. SELECT count(username) FROM your_table_name where username = Null; To drop the primary key: ALTER TABLE your_table_name DROP PRIMARY KEY CASCADE; To create a primary key: ALTER TABLE your_table_name ADD PRIMARY KEY (username); Personally, I think you are going to regret doing this. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Nguyen, David M [SMTP:[EMAIL PROTECTED]] I create a table to store user account information and set "userid" column to be primary key. I now want to set "username" to be primary key instead of "userid", how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David
Re: CPU Problem
Hamid Alavi wrote: Hi List, We are facing with some CPU problem (cpu 100% usage!), Just want to get some idea from you guys, any clue what can cause this problem: Here is a copy of some part of ora.ini This happen during usage of some long query with subquery, this can be the cause of problem or just some wrong setting in ini file. I appreciate your help. You won't reduce CPU usage by changing parameters. Rewrite the query. It's like magic. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: HOW TO FIND FREQUNT OBJECT ACCESS
Hi Seema, V$db_object_cache has a column called executions. John Seema Singh wrote: Hi How to check which are those functions/procedures/packages etc applications are using more frequntly? THx -Seema _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA 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).
OT: Oracle Embraces the Grid
http://computerworld.com/hardwaretopics/hardware/story/0,10801,75700,00.html Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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).
