Re: COPY command in 9i
Hello Connor. Tested this on 9.0.1.1. Works fine when selecting from the same database. (The only 9 version db we have.). When trying to access version 8.1.6 db got -1002 (trying to fetch after set is finished) no matter what array size I set. Yechiel Adar, Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 04, 2002 11:48 PM Can anyone perform a similar test for me ? Some really cute results here: SQL select * from v$version; BANNER Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production PL/SQL Release 9.0.1.2.1 - Production CORE9.0.1.2.0 Production TNS for 32-bit Windows: Version 9.0.1.2.0 - Production NLSRTL Version 9.0.1.2.0 - Production SQL conn system/manager Connected. SQL COPY FROM SYSTEM/MANAGER@DB9 - CREATE DEMO (BLAH) - USING SELECT 'X' FROM DUAL; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80) ERROR: ORA--18971416: Message -18971416 not found; product=RDBMS; facility=ORA (Now there's an interesting error number!) SQL set arraysize 1 SQL set long 5 SQL COPY FROM SYSTEM/MANAGER@DB9 - CREATE DEMO (BLAH) - USING SELECT 'X' FROM DUAL; Array fetch/bind size is 1. (arraysize is 1) Will commit when done. (copycommit is 0) Maximum long size is 5. (long is 5) Table DEMO created. 1 rows selected from SYSTEM@DB9. 1 rows inserted into DEMO. 1 rows committed into DEMO at DEFAULT HOST connection. (So it looks like arraysize etc could be the workaround - but there is no rhyme or reason to this - in other tests it worked UNTIL I changed arraysize. Similarly, straight after the above, I repeat the test to a different table name...) SQL COPY FROM SYSTEM/MANAGER@DB9 - CREATE DEMO2 (BLAH) - USING SELECT 'X' FROM DUAL; Array fetch/bind size is 1. (arraysize is 1) Will commit when done. (copycommit is 0) Maximum long size is 5. (long is 5) ERROR: ORA-03114: not connected to ORACLE No such problems on Solaris 9.0.1.3 - can anyone reproduce under NT 9.0.1.2 if they've got it? Charming eh? = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Yechiel Adar 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).
Re: defining rules
I think that you should create an after row triggerthat will activate a package that will check the values and activate whatever procedures you need. This way you have only one trigger and you control what to do in whatever order you need. Yechiel Adar, Mehish - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, April 06, 2002 9:18 AM Subject: defining rules I have a bugging problem, I will try to explain it... We need to store rules into the databse ... example Rule 1 says : if the name of a person = 'A' , age is 20 , state ='YSA' the execute procedure1 Another rule says Rule 2 : if the name of a person = 'A' , age is 20 the execute procedure2 Now if we try to save a person record as follows Name ='A' and age = 23 then both the rules would be executed. Now my problem, there could be hundreds of such rules, so when I save the person record, how do I find which rule to be applied What is the best optimum method to store these rules in the database and what is the best solution to find which rule to be applied.. Please note that these are not the only parameters that could be there in the rules...
Re: Favourite Urban Myth
Well, I do not know UNIX but I wanted today to see what products are installed on one NT server which has: 8.1.6, 8.1.7, 9.0.1 and OEM all installed. I activated the oracle installer and got an error: You can not update the registry - Installed aborted (some thing like this:-)) ). At least on Coffee ( NT - No Tea ) machine I need administrator privilege. Yechiel Adar, Mehish. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 04, 2002 6:19 PM The DBA needs root privileges on the server This is one of my interview questions. Dave -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: Yechiel Adar 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).
Using OID
Hello list We intend to implement OID as replacement for tnsnames (at first). Pit falls, Real time experience etc...??? Yechiel Adar, Mehish. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 01, 2002 11:34 PM And good luck when something (like OiD on 9.0.1.2) eventually gives you grief and there's nothing even remotely close to your problem on Metaclink. Oracle Support won't touch it. Been there, done that. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Joseph S Testa [mailto:[EMAIL PROTECTED]] Sent: Monday, April 01, 2002 3:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: Installing Oracle 9.0.1 on Red Hat Linux 7.2 first time you get the error, open a new window go to $ORACLE_HOME/bin look for genclntsh vi the file, looking for -z defs, remove that part, the -z defs ./genclntsh then hit retry, all should be good. joe [EMAIL PROTECTED] wrote: If you have successfully installed Oracle 9.0.1 on Red Hat 7.2, please let me know what steps you had to take to make it work. I've installed the compatibility libs and glibc stub patch for Oracle 8i, and have successfully installed 8.1.7. The 9i install consistently fails on the ins_plsql.mk makefile. I'll check on MetaLink some more. And yes, I know 7.2 is not yet certified, but it appears from the forums on MetaLink that others have done this. Thanks, 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 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: Yechiel Adar 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 help resolving report generation performance problem...
Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate. I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5. Our database is OLTP with reporting. It is a small database (exported data is about 150 MB). I would appreciate your recommendations and advice. Thanks in advance, Denmark Weatherburne _ Chat with friends online, try MSN Messenger: http://messenger.msn.com Report_SQL.zip Description: Zip compressed data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip compressed data
Re: Please help resolving report generation performance problem...
Denmark Weatherburne wrote: I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. Ak for your money back. You are right, usually SQL is the reason. But attaching documents is a bad idea when posting to a mailing list. Try to get hold of a copy of Harrison's book on SQL tuning, and rewrite your reports. -- 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 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).
Re: Please help resolving report generation performance problem...
You mentioned a number of things you have tried to tune the database, so it just may (probably is) the SQL. Rewriting might be the best solution (depending), but you need your stuff now. If you can't tune the SQL, try tuning the execution. If you can run a trace of the report session, use tkprof to get an explain plan, perhaps the judicious addition of indexes might help you in the short term.David A. BarbourOracle DBA, OCPAISD512-414-1002Denmark Weatherburne [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]04/07/2002 10:18 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: Please help resolving report generation performance problem... Hi DBA's,I've been trying to isolate the bottleneck with our Oracle database.I work as an Oracle DBA for the Government of a developing country (Belize).Recently, as it is income tax time, the department has to reconcile allwitholdings by the employer with their payment receiptrecords. This involves some data entry and a report generation byemployer (witholder) which lists all witholdings by each employee.This report can generate lots of pages depending on the number of employees.In some cases, the report has to be run overnight, as it takes too long(several hours) to generate.I've tried giving more resources to Oracle. I've tried creating a copyof the production database on another machine to use only for generatingreports. I've increased the size and number of rollback segments. I've tunedsome parameters. However, I have not observedany significant improvement in the report generation performance.I know tuning the SQL might be required, however, I don't have muchexperience in this area. The SQL statemements were written by consultantswho have long left. We do have the source code though.We are running Oracle 8.0.5.2.1 on NT 4.0The NT server is a Dell 4400 with Dual CPU and 1GB RAMWe are using hardware RAID 5.Our database is OLTP with reporting.It is a small database (exported data is about 150 MB).I would appreciate your recommendations and advice.Thanks in advance,Denmark Weatherburne_Chat with friends online, try MSN Messenger: http://messenger.msn.com Report_SQL.zip Description: Zip archive The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip archive
Re: Please help resolving report generation performance problem...
Denmark, Sifting through the clues in your email, it is almost dead certain that the SQL statements in your overnight reports must be tuned. Adding rollback segments has no effect on reports (usually). Modifying initialization parameters is unlikely to affect such specific problems also. To isolate the most egregiously awful SQL statements, the best tool is to query the V$SQLAREA view and sort by the value in the column BUFFER_GETS (a.k.a. logical reads or reads against buffers in the SGA's Buffer Cache) and the column DISK_READS (a.k.a. physical reads or misses against the Buffer Cache resulting in actual I/O to disk). I usually like to do this with a query similar to the following: SELECTSQL_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; The query looks at the information currently in the V$SQLAREA view (which is a present-time snapshot of the contents of the Shared SQL Area cache in the Library Cache of the Shared Pool of the SGA). Because V$SQLAREA shows the contents of a cache and because caches may get flushed depending on the activity in the system, I would recommend running this query either during the reports you are having trouble with or soon after they complete. Be sure to let the reports run for a bit and build up some history; don't run this report immediately after they are started... The ORDER BY clause sorts the report with an emphasis on queries that produce a lot of cache misses on the Buffer Cache. However, high numbers of BUFFER_GETS should not be disregarded either, as millions of BUFFER_GETS can produce poor performance too. Thus, instead of just saying ORDER BY DISK_READS DESC, BUFFER_GETS DESC, I find it useful to simply weight the physical reads as 100x heavier than logical reads, allowing consideration of both factors... If this query on V$SQLAREA takes too long or if it returns too much information (the latter condition is almost certain), then you may want to include a WHERE clause that filters out the inoffensive little SQL statements that are not a problem. For example: SELECTSQL_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA WHEREBUFFER_GETS 1000 AND DISK_READS 10 ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; Of course, you may want to play with those thresholds a little depending on how busy your system is. Set the values in the WHERE clause too high, and you might filter everything out. Set them too low and you'll still get too much data returned... I've got (what I think is) a nice version of this basic query on my website at www.EvDBT.com/library.htm. It is entitled TOP_STMT2 and it should be located about 2/3rds the way down that page. It can be used either as a PL/SQL stored procedure (i.e. script top_stmt2.sql is the DDL to create the report and run_top_stmt2.sql is the SQL*Plus script to run the stored procedure) or as an anonymous PL/SQL block (i.e. SQL*Plus script temp_top_stmt2.sql). If you chose the route involving the creation of the stored procedure TOP_STMT2, then the DDL script top_stmt2.sql would need to be run either as SYS (or INTERNAL) or they would need to be run under users who have been granted explicit SELECT permissions on five V$ views: V$SESSION, V$SQLAREA, V$SQLTEXT, V$SYSSTAT, and V$THREAD. If you don't have access to the SYS schema to create the stored procedure or grant the explicit SELECT permissions, then the anonymous PL/SQL block script temp_top_stmt2.sql is a handy alternative... The report coming out of the TOP_STMT2 report shows the percentage share of total instance physical and logical reads, allowing some idea of the impact of each SQL statement on overall system load. Using these reports, once you have identified the most awful SQL statements, you can now use tools like SQL*Plus's AUTOTRACE facility or the TKPROF report to tune them. This is a huge topic, but Guy Harrison's book on High Performance SQL Tuning in Oracle covers it very nicely, I think. The very best tool you can use (for free) is the STATSPACK package (shipped with v8.1.x but available for v8.0.x also) and then post-process the not-very-useful STATSPACK report using the YAPP performance profiler available (for free) on the www.oraperf.com website. If you don't have time or inclination to install STATSPACK, then the www.oraperf.com site will accept BSTAT/ESTAT reports. This combination (STATSPACK and YAPP) will give you an extremely accurate image of performance on your system. But this is a topic for another email, sometime... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 07, 2002 12:18 PM Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize).
anyone see my DBA_AUDIT_TRAIL ???
Hi guys, Please help me solved this SQL show user USER is SYS SQL select owner, object_name, object_type from all_objects where object_name = 'DBA_AUDIT_TRAIL'; OWNER OBJECT_NAMEOBJECT_TYPE -- -- -- SYSDBA_AUDIT_TRAILVIEW PUBLIC DBA_AUDIT_TRAILSYNONYM SQL desc DBA_AUDIT_TRAIL ERROR: ORA-24372: invalid object for describe Why I can not describe my DBA_AUDIT_TRAIL ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing 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).
