RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Guys, User apparently can only execute one stored proc. from a Crystal Reports. There are lots of different types of queries and he would like to cut-down on the number of Crystal Reports he would need. He wishes to create a table with session i.d. as primary key that will be used to insert aggregate records (rolling averages, etc.) so that he doesn't have to do this. I am concerned about even just 12 concurrent users (maybe?), 67 rows, primary key and index inserts and deletes are unnecessarily incurring I/O. I am suggesting using DBMS_SQL but don't know if there are limitations to the type of SQL you can write - we have inline views/hints/etc.? I just think it would be a more efficient way to do the same thing. He is saying he has been burned before by code generators. I am trying to say that this is a supplied package and not really a code generator and in my experience quite flexible. Help Am I making too much of this? What about metadata tables? In otherwords, keep report name, columns involved or even store query and view name then query actually based on the metadata. Probably what the ad hoc query tools do anyway.
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Paula I don't know Crystal reports so I'm not sure what you are trying to achieve, but... 1) If you're using 8i and can fix the number of bind variables (or don't need to use them at all) then native dynamic sql (NDS) is faster and easier to code than DBMS_SQL. 2) In either, I don't know of any restrictions on the type of sql you can use. 3) If you're using 8i and want to do the thing with the table, consider using global temporary tables. They look like normal tables but the data is stored in the user's sort area (or thereabouts) so you don't need the session_id. Each session only sees the data it has inserted. Regards David Lord -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: 13 August 2002 15:19To: Multiple recipients of list ORACLE-LSubject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Guys, User apparently can only execute one stored proc. from a Crystal Reports. There are lots of different types of queries and he would like to cut-down on the number of Crystal Reports he would need. He wishes to create a table with session i.d. as primary key that will be used to insert aggregate records (rolling averages, etc.) so that he doesn't have to do this. I am concerned about even just 12 concurrent users (maybe?), 67 rows, primary key and index inserts and deletes are unnecessarily incurring I/O. I am suggesting using DBMS_SQL but don't know if there are limitations to the type of SQL you can write - we have inline views/hints/etc.? I just think it would be a more efficient way to do the same thing. He is saying he has been burned before by code generators. I am trying to say that this is a supplied package and not really a code generator and in my experience quite flexible. Help Am I making too much of this? What about metadata tables? In otherwords, keep report name, columns involved or even store query and view name then query actually based on the metadata. Probably what the ad hoc query tools do anyway. ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. **
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Yes, it looks like NDS may be able to be used - Reading Johnathon Lewis' Practical 8i with refcursor. I think this is a better approach then global temporary table. I will see if there are limitations to this using Crystal. -Original Message-From: Lord, David - CSG [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 13, 2002 11:03 AMTo: Multiple recipients of list ORACLE-LSubject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Paula I don't know Crystal reports so I'm not sure what you are trying to achieve, but... 1) If you're using 8i and can fix the number of bind variables (or don't need to use them at all) then native dynamic sql (NDS) is faster and easier to code than DBMS_SQL. 2) In either, I don't know of any restrictions on the type of sql you can use. 3) If you're using 8i and want to do the thing with the table, consider using global temporary tables. They look like normal tables but the data is stored in the user's sort area (or thereabouts) so you don't need the session_id. Each session only sees the data it has inserted. Regards David Lord -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: 13 August 2002 15:19To: Multiple recipients of list ORACLE-LSubject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Guys, User apparently can only execute one stored proc. from a Crystal Reports. There are lots of different types of queries and he would like to cut-down on the number of Crystal Reports he would need. He wishes to create a table with session i.d. as primary key that will be used to insert aggregate records (rolling averages, etc.) so that he doesn't have to do this. I am concerned about even just 12 concurrent users (maybe?), 67 rows, primary key and index inserts and deletes are unnecessarily incurring I/O. I am suggesting using DBMS_SQL but don't know if there are limitations to the type of SQL you can write - we have inline views/hints/etc.? I just think it would be a more efficient way to do the same thing. He is saying he has been burned before by code generators. I am trying to say that this is a supplied package and not really a code generator and in my experience quite flexible. Help Am I making too much of this? What about metadata tables? In otherwords, keep report name, columns involved or even store query and view name then query actually based on the metadata. Probably what the ad hoc query tools do anyway.**This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at[EMAIL PROTECTED]Any information, statements or opinions contained in this message(including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individualother than the author who is duly authorised to represent Hays.A member of the Hays plc group of companies.Hays plc is registered in England and Wales number 2150950.Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.**
RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS
Title: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Need bind variables, can't fix them yes will go with global temporary tables but understand that this is going to place I/O on system that may not be necessary. -Original Message-From: Lord, David - CSG [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 13, 2002 11:03 AMTo: Multiple recipients of list ORACLE-LSubject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Paula I don't know Crystal reports so I'm not sure what you are trying to achieve, but... 1) If you're using 8i and can fix the number of bind variables (or don't need to use them at all) then native dynamic sql (NDS) is faster and easier to code than DBMS_SQL. 2) In either, I don't know of any restrictions on the type of sql you can use. 3) If you're using 8i and want to do the thing with the table, consider using global temporary tables. They look like normal tables but the data is stored in the user's sort area (or thereabouts) so you don't need the session_id. Each session only sees the data it has inserted. Regards David Lord -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: 13 August 2002 15:19To: Multiple recipients of list ORACLE-LSubject: RE: HELP CRYSTAL/ORACLE FIGHTING APPROACH CUT DOWN REPORTS Guys, User apparently can only execute one stored proc. from a Crystal Reports. There are lots of different types of queries and he would like to cut-down on the number of Crystal Reports he would need. He wishes to create a table with session i.d. as primary key that will be used to insert aggregate records (rolling averages, etc.) so that he doesn't have to do this. I am concerned about even just 12 concurrent users (maybe?), 67 rows, primary key and index inserts and deletes are unnecessarily incurring I/O. I am suggesting using DBMS_SQL but don't know if there are limitations to the type of SQL you can write - we have inline views/hints/etc.? I just think it would be a more efficient way to do the same thing. He is saying he has been burned before by code generators. I am trying to say that this is a supplied package and not really a code generator and in my experience quite flexible. Help Am I making too much of this? What about metadata tables? In otherwords, keep report name, columns involved or even store query and view name then query actually based on the metadata. Probably what the ad hoc query tools do anyway.**This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at[EMAIL PROTECTED]Any information, statements or opinions contained in this message(including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individualother than the author who is duly authorised to represent Hays.A member of the Hays plc group of companies.Hays plc is registered in England and Wales number 2150950.Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.**
Re: We need the help from Oracle DBAs.
You will need to perform a restore and point-in-time recovery from backup. The best option, if you have other changes that need to be preserved, is to do a tablespace point in time recovery (TSPITR). This requires restoring the appropriate backup files to create another database, rolling the appropriate tablespace(s) forward to just before the bad deletes, exporting the appropriate tables, truncating those tables in production, and importing the tables exported from the TSPITR. The exact mechanism best used to do the TSPITR is dependent on your version of Oracle and how you do backups (rman in 8i has innate TSPITR capability). If you have to preserver other changes in the tables affected, then there is no such simple solution - do the steps outlines above except for the export/import. Then create table(s) in the database used for TSPITR ... as select ... from ... - getting only those records needed to be restored. Then export new table, import into the production database, and insert into the real table. There are perhaps a few other options, but this is , believe it or not, the most straightforward. You DO have a DBA don't you? If not, you will need a consultant! This isn't particularly easy for the uninitiated. It will probably not be particularly fast either - depending on quite a number of factors (space, size, prior planning for this contingency, ...). -Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 5:20 AM Hi, well, if the deletion was comitted, the only way to get your data back is by doing recovery from your latest backup. I just see that you're addressing the sql*plus group. Try [EMAIL PROTECTED], that's a DBA group. By the way, don't you have an on-site DBA ??? greetings, Stefan Jane Chen schrieb: Hi Folks: Sorry to write the mail to this group, because I haven't had the address from OracleDBA groups, and it is ergent. If someboday there can give us a tip or tell me the right group address, it would be grateful. We have problems with our datas which were stored in oracle8i. Some of our users have deleted some data which was stored by application (programmed by jsp) maybe carelessly or sth unintended. Now they want them back. The problem is, we can not see all the records in the server now, is there any methods to rollback or from the backup files the lost datas to recover? It is ergent, I need your helps ASAP. regards. jane _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp Become an ODTUGer and be a voice to Oracle! Join now at www.odtug.com/join.htm ODTUG 2002 at Caesars Palace in Las Vegas, NV, June 17-21, 2002 -- Author: Jane Chen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 ODTUG-SQLPLUS-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). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: We need the help from Oracle DBAs.
hey don .. did i see a hidden plug in there are what .. just kidding.. Jane, I had posted detailed tspitr steps to this list which i tried last week .. and it is a pain in the butt to configure.. that said rman tspitr is probably the way to go as don pointed out as compared to OS tspitr which is a totally manual process so best of luck jane .. Deepak --- Don Granaman [EMAIL PROTECTED] wrote: You will need to perform a restore and point-in-time recovery from backup. The best option, if you have other changes that need to be preserved, is to do a tablespace point in time recovery (TSPITR). This requires restoring the appropriate backup files to create another database, rolling the appropriate tablespace(s) forward to just before the bad deletes, exporting the appropriate tables, truncating those tables in production, and importing the tables exported from the TSPITR. The exact mechanism best used to do the TSPITR is dependent on your version of Oracle and how you do backups (rman in 8i has innate TSPITR capability). If you have to preserver other changes in the tables affected, then there is no such simple solution - do the steps outlines above except for the export/import. Then create table(s) in the database used for TSPITR ... as select ... from ... - getting only those records needed to be restored. Then export new table, import into the production database, and insert into the real table. There are perhaps a few other options, but this is , believe it or not, the most straightforward. You DO have a DBA don't you? If not, you will need a consultant! This isn't particularly easy for the uninitiated. It will probably not be particularly fast either - depending on quite a number of factors (space, size, prior planning for this contingency, ...). -Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 13, 2001 5:20 AM Hi, well, if the deletion was comitted, the only way to get your data back is by doing recovery from your latest backup. I just see that you're addressing the sql*plus group. Try [EMAIL PROTECTED], that's a DBA group. By the way, don't you have an on-site DBA ??? greetings, Stefan Jane Chen schrieb: Hi Folks: Sorry to write the mail to this group, because I haven't had the address from OracleDBA groups, and it is ergent. If someboday there can give us a tip or tell me the right group address, it would be grateful. We have problems with our datas which were stored in oracle8i. Some of our users have deleted some data which was stored by application (programmed by jsp) maybe carelessly or sth unintended. Now they want them back. The problem is, we can not see all the records in the server now, is there any methods to rollback or from the backup files the lost datas to recover? It is ergent, I need your helps ASAP. regards. jane _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp Become an ODTUGer and be a voice to Oracle! Join now at www.odtug.com/join.htm ODTUG 2002 at Caesars Palace in Las Vegas, NV, June 17-21, 2002 -- Author: Jane Chen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 ODTUG-SQLPLUS-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). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Help interpreting Oracle CPU usage results on NT 4.0...
Hi DBA's, We are running Oracle 805 on NT in ARCHIVELOG MODE. I run the following script to list CPU usage by thread: column program format a50 column username format a12 spool C:\WINNT\Profiles\aweatherburnedb\Desktop\DBA\NT_CPU_usage.out select p.spid thread, s.username, decode(nvl(p.background,0),1,bg.description, s.program) program, ss.value/100 CPU,physical_reads disk_io from v$process p, v$session s, v$sesstat ss, v$sess_io si, v$bgprocess bg where s.paddr=p.addr and ss.sid=s.sid and ss.statistic#=12 and si.sid=s.sid and bg.paddr(+)=p.addr order by ss.value desc; spool off This is the report: THREADUSERNAME PROGRAM CPU DISK_IO - -- - - 000C8 CASTILOL D:\orant\BIN\RWRBE60.exe 112.42132277 001AC CASTILOW ORACLE80.EXE (P000) 67.23 0 001C0 PEREZM D:\orant\BIN\RWRBE60.exe 60.11120613 001C2 CASTILOW ORACLE80.EXE (P001) 55.95 0 001DF CASTILOW ORACLE80.EXE (P002) 33.4 5 00175 CASTILOW ORACLE80.EXE (P003) 30.1 5 001E2 MKENZIEL C:\WINNT\Profiles\All Users\Desktop\sigtas.lnk 27.77257276 000DC CASTILOW C:\WINNT\Profiles\castillowm\Desktop\Sigtas Belize 12.69 40611 Sorry about the formatting. Nevertheless, what I'm trying to get a handle on is the CPU column. Notice the first row, thread 000C8 is using 112.42 ? of CPU. Looking at the SQL statement v$sesstat provides VALUE which are numbers from 0 to 438532196. This value is divided by 100 to get the CPU value. I still don't know what the number in v$sesstat VALUE represents? Also from which table is the value for Disk_IO (Physical Reads) retrieved? Thanks in advance for your time. Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Urgent help on Oracle Mail
Dear Experts I am using Oracle version 7.3.2. I want to send the output of Reports thru mail. From the doc, I came to know that I should install Oracle*Mail. Where can I get this product ORACLE*MAIL ? Can I download somewhere ? any links It is not available in the original CD which is licenced ? Please help. Thanks Regards, Richard This email message may contain privileged/confidential information and/or copyright material. It is intended only for the use of the person(s) to whom it is addressed and any unauthorised use may be unlawful. If you receive this email by mistake, please advise the sender immediately by using the reply facility in your email software and delete the material from your computer. The material contained in this message does not constitute a binding contract with any company within the MTL Instruments Group plc. Opinions, conclusions and other information in this email that do not relate to the official business of this organisation shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Help with Oracle
Hi, I want to display thedata fromfew tables into Excel sheet. How do I do that? Is there any tool available? Sanjay
RE: Help with Oracle
Hi Sanjay! You can usethe shareware tool TabExp to generate Excel compatible CSV Files. http://www.materialdreams.com/products.html Cheers DO Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sanjay KumarSent: Tuesday, March 06, 2001 2:40 AMTo: Multiple recipients of list ORACLE-LSubject: Help with Oracle Hi, I want to display thedata fromfew tables into Excel sheet. How do I do that? Is there any tool available? Sanjay