oracle events...
Does anyone know what these oracle events are and how the wait times can be reduced. This is an extract from statspack output on 9.2.0.4 on HP 11.11 Top 5 Timed Events~~ % TotalEvent Waits Time (s) Ela Time --- pipe put 780 3,904 44.65wait for unread message on broadcast channel 3,016 3,034 34.70PX Deq: Signal ACK 33,937 678 7.76db file scattered read 6,575,215 499 5.70PX qref latch 222 200 2.29 -Want to chat insttantly with your online friends? Get the FREE Yahoo! Messenger
Re: query taking a long time to run via sqlnet
I have included the following in my sqlnet.ora
trace_file_client = osstrace_timestamp_client = truetrace_level_client = 16
Am no expert on interpreting the trace file and Oracle are not being very helpful at the moment.
"M.Godlewski" [EMAIL PROTECTED] wrote:
Zabair,
Have you tried trace?
Did you check trace route to see how many hops it makes to the database server?Zabair Ahmed [EMAIL PROTECTED] wrote:
I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.
DECLARE p_xml_in LONG(32760); p_xml_out LONG(32760);BEGIN p_xml_in := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN OTHERS THEN dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));RAISE;END;/
The same query when I run it via a client/server connection takes fore ever to run, it's been almost 2hrs and it's still running
I wonder what I could do to get to the bottom of this query taking a very long time to execute using a client/server connection.
No errors in the alert.log so far.
Oracle 9.2.0.4
HP-UX11
Connection is TCP/IP.
TIA
Zabair
Want to chat instantly with your online friends?Get the FREE Yahoo! Messenger
Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuardWant to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
query taking a long time to run via sqlnet
I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.
DECLARE p_xml_in LONG(32760); p_xml_out LONG(32760);BEGIN p_xml_in := 'ITEMUSER_IDD3846/USER_IDGUTO_ID/GUTO_IDACTIONGIVEUP/ACTIONACTION_DATE28/10/2003/ACTION_DATEMEDIA_TYPE13/MEDIA_TYPEAGENT_IDA001/AGENT_IDVERIFIER_ID/VERIFIER_IDCAMPAIGN_IMPACT/CAMPAIGN_IMPACTCAMPAIGN_CODE/CAMPAIGN_CODESALES_CHANNEL1/SALES_CHANNELNEW_OCCUPANTICE_CUSTOMER_ID/ICE_CUSTOMER_IDBUSINESS_FLAGN/BUSINESS_FLAGTAKEON_DATE29/10/2003/TAKEON_DATESALUTATION/SALUTATIONFIRST_NAME/FIRST_NAMEINITIALS/INITIALSLAST_NAME/LAST_NAMEFULL_NAMENew
Customer/FULL_NAME/NEW_OCCUPANTCUSTOMERICE_CUSTOMER_ID383700/ICE_CUSTOMER_IDPREMISE_ID4115853/PREMISE_IDBUSINESS_FLAGN/BUSINESS_FLAGNO_NEW_CONTACT_REASON99/NO_NEW_CONTACT_REASONRESP_NEW_ADDRN/RESP_NEW_ADDRCALLBACK_DATE/CALLBACK_DATECALLBACK_COMMENTS/CALLBACK_COMMENTSACCOUNTPREMISE_ID4115853/PREMISE_IDFINAL_BILL_TO_OFFICEY/FINAL_BILL_TO_OFFICECORRECT_ADDRESSY/CORRECT_ADDRESSNAMEW
Smith/NAMECUST_NOT_FLAGY/CUST_NOT_FLAGBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEPRODUCTPRODUCT_CODEE/PRODUCT_CODEBUSINESS_FLAGN/BUSINESS_FLAGREFERENCE952117400012/REFERENCEKEY_TYPE1/KEY_TYPESERVICE_REFERENCE576931001022/SERVICE_REFERENCEREASON/REASONGET_READINGN/GET_READINGCONTINUE_SUPPLYN/CONTINUE_SUPPLYESTIMATE_BILLN/ESTIMATE_BILLRAISE_FINAL_INVOICEY/RAISE_FINAL_INVOICEPAYMENT_CARD_REQDN/PAYMENT_CARD_REQDPAYMENT_VALUE/PAYMENT_VALUESYSTEM_CODEJ/SYSTEM_CODE/PRODUCT/ACCOUNT/CUSTOMER/ITEM';
pkg_ice_guto.sp_perform_guto(p_xml_in, p_xml_out);
EXCEPTIONWHEN OTHERS THEN dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));RAISE;END;/
The same query when I run it via a client/server connection takes fore ever to run, it's been almost 2hrs and it's still running
I wonder what I could do to get to the bottom of this query taking a very long time to execute using a client/server connection.
No errors in the alert.log so far.
Oracle 9.2.0.4
HP-UX11
Connection is TCP/IP.
TIA
Zabair
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
ORA-7445
Hello
Am getting an ORA-7445 and have raised a iTAR with Oracle but so far they have not hot to the bottom of the problem, so I thought I'll try this list!
When I run a package in sqlplus I get the follwing error:-
ERROR at line 1:ORA-03113: end-of-file on communication channel
The following error is reported in the alert.log:
ORA-07445: exception encountered: core dump [eoref_resolve_xdesc()+244] [SIGBUS] [unknown code] [0x000C0] [] []
Here is an extract of the trace file:-
ORA-07445: exception encountered: core dump [eoref_resolve_xdesc()+244] [SIGBUS] [unknown code] [0x000C0] [] []Current SQL statement for this session:BEGIN ICE.SP_ICE_UPD_SPECIAL_NEEDS('CUSTOMER ID="1298743" ACCOUNT_REF="710616344068" _TYPE="1"SPECIAL_NEED TYPE="16" NEW="Y" DATE="27/10/2003" COMMENTS=""/SPECIAL_NEED/CUSTOMER'); END;- PL/SQL Call Stack - object line object handle number namec0003b7f8aa8 0 package SYS.XMLPARSERCOVERc0003b7fa738 67 package body SYS.XMLPARSERc0003c923338 65 procedure ICE.SP_ICE_UPD_SPECIAL_NEEDSc0003c8a8b60 1 anonymous block
Has anyone seen or some across such a problem, any help/pointers would be greatly appreciated.
Oracle 9.2.0.4 64 Bit
HPUX 11.
TIA
ZabairWant to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
One for bash experts....
I've got the following script that works fine on most flavours of unix apart from Linux...I think I must be going mad as I can't see where the problemis
I get the following error when i invoke it.
oracle ukwsv71 usedb uktst233
bash: nawk: command not found
bash: nawk: command not found
bash: nawk: command not found
bash: nawk: command not found
bash: nawk: command not found
bash: nawk: command not found
bash: nawk: command not found
ORACLE_HOME = [/oracle/app/oracle] ?
TIA
#--# File: usedb# System: Information System# Description: This validates and sets up the oracle environment# based on the database name.# Parameters: database name upper or lower case# # Notes: "This should be bomb proof but so were
many #embasies!"# It runs in the current shell . ## Korn/Bash command line usage # # . /proj/oracle/scripts/usedb ukprd33## Bourne command line usage # # usedb=ukprd33 . /proj/oracle/scripts/usedb# # Interactive use Korn/Bourne or Bash# ---# . /proj/oracle/scripts/usedb#oracle ukwsv71 cat
usedb#--# File: usedb# System: Information System# Description: This validates and sets up the oracle environment# based on the database name.# Parameters: database name upper or lower case# # Notes: "This should be bomb proof but s!
o were
many embasies!"# It runs in the current shell . ## Korn/Bash command line usage # # . /proj/oracle/scripts/usedb ukprd33## Bourne command line usage # # usedb=ukprd33 . /proj/oracle/scripts/usedb# # Interactive use Korn/Bourne or Bash# ---# . /proj/oracle/scripts/usedb## Using an alias Korn or Bash#
---# alias usedb='. /proj/oracle/scripts/usedb'# usedb uktst33# or# usedb## Date Who Comments# === # 20-08-98 MJE (Kentlong Ltd.) Glaxowellcome# 28-04-98 MJE Updated for HPUX# 10-06-99 MJE Hunt for oratab back in looking at listener is # unreliable. Looks for the tnsnames file in
standard# place. Removes all environment setings and oracle # path seting at the start.# 08-07-99 MJE Changed to work in bourne shell from the command line.# 30-11-99 IAF Test for Oracle 8.1.5 so as to set up LD_LIBRARY_PATH# to point to additional directory /usr/ucblink.# 06-03-00 MJE Changed code to ignore links when looking for oratab# 07 Aug 2001 M Sabet Fixed the problem with !
the new
format of the combined # merged fSB and fGW tnsnames file. This script basically stopped working.# But now it should cope with both the old and new combined formats. It is# always best to stick to just one format and indentation for the tnsnames# entries if possible. It makes it easier to work with.##--
# Set up names for Information and error processingNODENAME=`uname -n`SCRIPT=$0INFO=$NODENAME"::usedb: "ERROR=$NODENAME"::usedb: Error "
# If the operating system is HP-UX then the $NAWK command = awk else use $NAWK.# Under HP-UX $NAWK does not exist but the $NAWK functions are included with awkif [ `uname -s|awk '{print substr($0,1,3)}'` = "HP-" ] ; then NAWK=awkelse NAWK=nawkfi
if [ -z "$usedb" ] ; then usedb=$1fi
unset OTAB OTABFILE TNSNAMES VNAM LOCATION TWO_TASK ORACLE_SID ORACLE_HOME
# If the database name has not been passed in on the command linewhile [ -z "$usedb" ] ; do echo "Please enter database name e.g. UKTST01 " read usedb echodone
# Fiddle abort loopwhile true ; do
# Find the ORATAB fileOTAB=`find /etc /var/opt -type f -name oratab -print 2/dev/null`if [ -z "$OTAB" ] ; then error "The ORATAB file was not found." unset OTAB OTABFILE TNSNAMES VNAM LOCATION usedb breakfi
# Strip multiple /usr/local/bin (s) from the path and make sure /usr/local/bin# is firstPATH="/usr/local/bin:"`echo $PATH|sed 's?/usr/local/bin??g'`
# Strip all $ORACLE_HOME/bin (s) in oratab from the pathORACLE_HOMES=`awk -F: '/\*/ {next} /#/ {next} /^$/ {next} {print $2}' $OTAB` for ORACLE_HOME in $ORACLE_HOMESdo PATH=`echo $PATH|sed 's?\:'$ORACLE_HOME'\/bin??g'`done
# Replace the two sets of Colons :: with : due to removal of other bitsPATH=`echo $PATH|sed 's?\:\:?:?g'`
# Remove last char if the last char is a colonPATH=`echo $PATH|awk '{ if (substr($0,length(),1) == ":") print substr($0,1,len`
export PATH
# Set the ORACLE_HOME to the first entry in the oratabORACLE_HOME=`awk -F: '/#/ {next} /^$/ {next} {print $2}' $OTAB|head -1`
# Locate the tnsnames fileif [ -z "$TNS_ADMIN" ] ; then# Look in standard place TNSNAMES=$ORACLE_HOME/network/admin/tnsnames.oraelse# TNS_ADMIN overrides the standard position of tnsnames TNSNAMES=$TNS_ADMIN/tnsnames.ora fiunset ORACLE_HOME
if [ ! -f "$TNSNAMES" ] ; then echo $ERROR "$TNSNAMES not found, exiting ..." unset OTAB OTABFILE TNSNAMES VNAM LOCATION usedb
ORA-12547: TNS:lost contact
Any Oracle Apps DBA out there.. Am getting the following error when am trying to install 11.5.8 on Redhat 8. The error messgae in the adcrdb_PROD.txt is as follows:- ORA-12547:TNS:lost contact SP2-0640 Not connected. Am getting the error when rapwiz is running adcrdb.sh - creating the database controlfile. I know this is a connectivity issue to the instance. but how do I resolve it. Thanks Yahoo! Plus - For a better Internet experience
submitting statspack.snap through dbms_job
Quick question, I want to submit statspack.snap at 10:00am and 14:00pm through dbms_job, it's friday afternoon and my head is hurting me from a heavy night and the manuals are as clear as mud. ThanksYahoo! Plus - For a better Internet experience
Checkpoints
The following parameters are set in the init.ora:- NAME VALUE-- ---log_checkpoint_interval 25600log_checkpoint_timeout 0log_checkpoints_to_alert TRUE This means that a checkpoint will happen every 12Mb of redo being filled, os block size is 512. The size of the redo logs are 50Mb, as a solution,I recommend setting the log_checkpoint_interval to at least 50Mb to reduce the occurrences of these checkpoints. What am noticing in the alert log is that a checkpoint is happening every minute at peak times, this is clearly putting considerable overhead on the lgwr. Oracle 8.0.6.3.0 on Solaris 5.8 Anyone got any thoughts on the above or experienced checkpoints going mad on 8.0.6. TIA With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Re: Checkpoints
Hi Daniel Log switches are happening every 20-25mins during peak times and yes the ckpt process is started automatically. ZabairWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
RE: Oracle Names Server 8.1.7 on HP 11
Hello Stephen, sorry for the delay in reply, the names respository is located on a different server as compared to the names server. I would be interested in the white paper, it be great if you could send it over. Thanks Zabair "Karniotis, Stephen" [EMAIL PROTECTED] wrote: Zabair: Interesting problem. Where is the names server repository located? We have implemented the Dynamic Discovery Option of the Oracle Names Server. Additionally, we implemented multiple names servers and used the internal replication facility to keep them in sync. I will send you a white paper on how to configure this if you want. I believe it has already been posted by Jared (am I correct Jared) on the orafaq.net web site. Let me know. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message-From: Zabair Ahmed [mailto:[EMAIL PROTECTED]Sent: Thursday, February 20, 2003 12:19 PMTo: Multiple recipients of list ORACLE-LSubject: Oracle Names Server 8.1.7 on HP 11 We've currently got Names Server running on 4 host boxes. If one Names server is down, the client is configured to automatically attempt to connect to the next one in the list. We had a problem recently were, if we issued a tnsping from any of the clients we got the following message. TNS-03505 - Failed to resolve name This implies that our client PC is unable to resolve the name and hence was not able to connect to the database. Usually this implies that something is wrong with all our Oracle Names servers. Inorder to resolve this problem, I had to kill the Names Server on each of the 4 boxes and restart it. The Names servers had somehow lost connection to the Oracle Names repository database, although the servers appear to be attached to the database. I reckon we canreduce the chances of this problem occuring again, by adding a second Oracle Names database repository in our database cluster. This means, that if the NAMES servers lose connection to one repository, they can fall back on the second database without any loss of service. What have other people done with their Names Server and respository and do they see any draw backs with the above. Sorry for the long email. TIA With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Oracle Names Server 8.1.7 on HP 11
We've currently got Names Server running on 4 host boxes. If one Names server is down, the client is configured to automatically attempt to connect to the next one in the list. We had a problem recently were, if we issued a tnsping from any of the clients we got the following message. TNS-03505 Failed to resolve name This implies that our client PC is unable to resolve the name and hence was not able to connect to the database. Usually this implies that something is wrong with all our Oracle Names servers. Inorder to resolve this problem, I had to kill the Names Server on each of the 4 boxes and restart it. The Names servers had somehow lost connection to the Oracle Names repository database, although the servers appear to be attached to the database. I reckon we canreduce the chances of this problem occuring again, by adding a second Oracle Names database repository in our database cluster. This means, that if the NAMES servers lose connection to one repository, they can fall back on the second database without any loss of service. What have other people done with their Names Server and respository and do they see any draw backs with the above. Sorry for the long email. TIAWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
SQL and TEMP segments
Quick question, I want to find out what sql is currently using my temporary segments. Is their a simple solution. Am getting the ORA-1652 for my locally managed temporary tablespace and I would like to find out what sql is causing this. My temp tablespace is currently 1.2GB. Oracle 8.1.7.3 on HPUX 11. TIAWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Oracle TEMPORARY SEGMENT
Is their any way of identifying which user/process is holding onto a temporary segment. I've got a 50Mb temporary tablespace and there is a temporary segment which is 47Mb which is not being freed up by SMON. And, if i can't identify who is holding onto this TEMP segment, is their a way in whichI can delete it andstop the alert log being flooded withORA-1652. As I say the SMON is failing to clean up this TEMP segment and it's been on-going for a number of days and I don't have the option of bouncingthe database. Oracle 8.1.7.3 on HP-UX11. TIAWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
RE: Oracle TEMPORARY SEGMENT
Hi Jerry, I tried your suggestion of altering the pctincrease to a non-zero value, although the original temp segment has disappeared from the dba_segments. I now have a different temp segment which is lingering around in dba_segments. Zabair Whittle Jerome Contr NCI [EMAIL PROTECTED] wrote: Hi, This is what I use to clean up a temp tablespace. The tablespace is named 'temp' and it is normally set to PCTINCREASE of 10. You will need to insert the correct values for your temp tablespace. Altering the tablespace a little wakes us SMON which will clean things up if it can. alter tablespace temp default storage(pctincrease 0); alter tablespace temp default storage(pctincrease 10); 50Mb seems rather small for a temp tablespace. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Zabair Ahmed [SMTP:[EMAIL PROTECTED]] Is their any way of identifying which user/process is holding onto a temporary segment. I've got a 50Mb temporary tablespace and there is a temporary segment which is 47Mb which is not being freed up by SMON. And, if i can't identify who is holding onto this TEMP segment, is their a way in whichI can delete it andstop the alert log being flooded withORA-1652. As I say the SMON is failing to clean up this TEMP segment and it's been on-going for a number of days and I don't have the option of bouncingthe database. Oracle 8.1.7.3 on HP-UX11. TIA _ With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs http://uk.yahoo.com/mail/tagline_xtra/?http://uk.docs.yahoo.com/mail_storage.htmlWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Oracle 8i (8.1.7) for Windows XP Home Edition
Quick question, has anyone managed to sucessfully install 8.1.7 on Windows XP Home Edition. I have read on metalink that this is not officially supported by Oracle, but wanted to know if anyone has managed to install it for test purposes on their home/work PC's tia Zabair -- 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).
No Subject
HELP _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
Listener problems
Has anybody come across the following error message in the listener.log, and know what the resolution is. TNS-12500: TNS:listener failed to start a dedicated server process TNS-12540: TNS:internal limit restriction exceeded TNS-12560: TNS:protocol adapter error TNS-00510: Internal limit restriction exceeded Solaris Error: 12: Not enough space What is this Solaris Error 12? I have nothing set in the listener.ora or sqlnet.ora (in fact this dosn't even exist) files. 8.1.7 on Solaris 5.6 regards Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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: Function based index - insufficient priveleges
You need to set a couple of parameters in the init.ora, these are query_rewrite_enabled = true query_rewrite_integrity = trusted and you need to grant the privilege QUERY_REWRITE to the user. HTH. Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
How much TEMP space is needed for index creation
OK, i've got a table with 302068428 rows. Im in the process of creating a nonunique index on this table, how much space do i need in the TEMP tablespace for the creation of this index. Name Null?Type - - SEQUENCEIDNOT NULL NUMBER(20) TRANSACTIONCODEVARCHAR2(1) DISTRICT VARCHAR2(3) CUSTOMERACCOUNTVARCHAR2(8) CUSTOMERSUBACCOUNT VARCHAR2(3) SUBSCRIBERSEQNUM VARCHAR2(7) EFFECTIVEDATE VARCHAR2(8) PRICEABLEITEMIDVARCHAR2(5) BASICPRICEABLEITEMID VARCHAR2(5) SERVICESTATUSCODE VARCHAR2(2) SERVICINGSALESFORCEID VARCHAR2(8) SERVICINGSALESFORCELVL VARCHAR2(1) SUPPLSERVSTARTDATE VARCHAR2(8) ORIGSUPPLSERVSTARTDT VARCHAR2(8) PRICEABLEITEMTYPE VARCHAR2(2) INPLANIND VARCHAR2(1) BILLFREQUENCY VARCHAR2(1) MAILBOXNAMEVARCHAR2(30) MAILBOXLANGUAGEVARCHAR2(2) PAGERTYPE VARCHAR2(1) APPACCESSMAILBOXNO VARCHAR2(10) TELEANSWLANUAGE1 VARCHAR2(2) TELEANSWLANUAGE2 VARCHAR2(2) TELEANSWLANUAGE3 VARCHAR2(2) SERVICESEQNUM VARCHAR2(7) SWITCHRELATEDIND VARCHAR2(1) EXPIRATIONDATE VARCHAR2(8) UPLOADDATE DATE D2SUBIDNUMBER SWPRODRELIDNUMBER D_EFFECTIVEDATEDATE D_EXPIRATIONDATE DATE The non-unique index will be on these columns, SEQUENCEID, D_EFFECTIVEDATE, D_EXPIRATIONDATE, SERVICESTATUSCODE, TRANSACTIONCODE The sort area size is set to 1000. How do i work out how much space is needed. TIA Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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: ORA-06553: PLS-707: unsupported construct or internal error [2603]
Thanks to those who replied, the LD_LIBRARY_PATH was set in the profile for oracle, unsetting this did the trick. Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
ORA-06553: PLS-707: unsupported construct or internal error [2603]
Has anybody seen the following error on logon to sqlplus sqlplus dbu/dbu SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 10 14:29:27 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-06553: PLS-707: unsupported construct or internal error [2603] Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-06553: PLS-707: unsupported construct or internal error [2603] Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL show user USER is DBU SQL There are a number of invalid views belonging to SYS. SYS.GV_$ARCHIVED_LOG SYS.GV_$BACKUP_DATAFILE SYS.GV_$BACKUP_PIECE SYS.GV_$DATAFILE_COPY SYS.GV_$DISPATCHER_RATE SYS.GV_$LOCK_ SYS.GV_$MTS SYS.GV_$PROXY_ARCHIVEDLOG SYS.GV_$PX_SESSTAT SYS.V_$DISPATCHER_RATE SYS.V_$BACKUP_SET SYS.V_$BACKUP_PIECE SYS.V_$ARCHIVED_LOG SYS.GV_$TEMP_EXTENT_MAP SYS.GV_$SYSTEM_PARAMETER SYS.GV_$SORT_USAGE SYS.GV_$SGASTAT SYS.GV_$SESSION SYS.V_$SESSION SYS.V_$ROLLNAME SYS.V_$PROXY_DATAFILE SYS.V_$PROXY_ARCHIVEDLOG SYS.V_$MTS SYS.V_$LOADPSTAT SYS.GV_$PROXY_DATAFILE SYS.GV_$PARAMETER SYS.GV_$MLS_PARAMETERS SYS.GV_$LOADPSTAT SYS.GV_$DELETED_OBJECT SYS.GV_$BACKUP_SET When i try to recompile these i get the following error messge SQL alter VIEW SYS.V_$BACKUP_PIECE compile; alter VIEW SYS.V_$BACKUP_PIECE compile * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00904: invalid column name Anybody seen this before i had a quick look in Metalink but couldn't find anything useful. Oracle 8.1.7.0.0 on Solaris 5.6 TIA Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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: ORA-06553: PLS-707: unsupported construct or internal error [2603]
Stefan I've recently inherited this database and the developers who have used the database in the past say that it worked fine and they have had no problems. My first reaction was the same as yours but i'm assured by the developers that this database has worked fine before. The problem is the previous DBA has left and there is no documentation on the database. From: Stefan Jahnke [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ORA-06553: PLS-707: unsupported construct or internal error [2603] Date: Thu, 10 May 2001 06:31:12 -0800 zabair ahmed schrieb: Has anybody seen the following error on logon to sqlplus sqlplus dbu/dbu SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 10 14:29:27 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-06553: PLS-707: unsupported construct or internal error [2603] Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-06553: PLS-707: unsupported construct or internal error [2603] Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL show user USER is DBU SQL There are a number of invalid views belonging to SYS. SYS.GV_$ARCHIVED_LOG SYS.GV_$BACKUP_DATAFILE SYS.GV_$BACKUP_PIECE SYS.GV_$DATAFILE_COPY SYS.GV_$DISPATCHER_RATE SYS.GV_$LOCK_ SYS.GV_$MTS SYS.GV_$PROXY_ARCHIVEDLOG SYS.GV_$PX_SESSTAT SYS.V_$DISPATCHER_RATE SYS.V_$BACKUP_SET SYS.V_$BACKUP_PIECE SYS.V_$ARCHIVED_LOG SYS.GV_$TEMP_EXTENT_MAP SYS.GV_$SYSTEM_PARAMETER SYS.GV_$SORT_USAGE SYS.GV_$SGASTAT SYS.GV_$SESSION SYS.V_$SESSION SYS.V_$ROLLNAME SYS.V_$PROXY_DATAFILE SYS.V_$PROXY_ARCHIVEDLOG SYS.V_$MTS SYS.V_$LOADPSTAT SYS.GV_$PROXY_DATAFILE SYS.GV_$PARAMETER SYS.GV_$MLS_PARAMETERS SYS.GV_$LOADPSTAT SYS.GV_$DELETED_OBJECT SYS.GV_$BACKUP_SET When i try to recompile these i get the following error messge SQL alter VIEW SYS.V_$BACKUP_PIECE compile; alter VIEW SYS.V_$BACKUP_PIECE compile * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00904: invalid column name Anybody seen this before i had a quick look in Metalink but couldn't find anything useful. Oracle 8.1.7.0.0 on Solaris 5.6 TIA Zabair Hi, has your dictionary been created correctly during database creation ? Did you run catalog and catproc and whatever else you need without any errors ? Looks like some stuff is missing. -- Regards, Stefan Jahnke BOV AG @:D2 Vodafone, Abt.: FIBM -- 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). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
No Subject
Hello Does anybody know what the following ora 600 relates to, ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [], [] Wed May 2 20:51:16 2001 Errors in file /opt/oracle/CRMVAN01/admin/udump/crmvan01_ora_29802.trc: ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [] Wed May 2 20:51:21 2001 Errors in file /opt/oracle/CRMVAN01/admin/udump/crmvan01_ora_29802.trc: ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [], [] Wed May 2 20:51:26 2001 Errors in file /opt/oracle/CRMVAN01/admin/udump/crmvan01_ora_29802.trc: ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [], [] I got loads of these in the alert log last night when we had a long running query. Everything seems to be ok now but i'm curious what this is. 8.1.7 on Solaris 2.6 Thanks Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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: Sun/Oracle Best Practices URL
Thanks Roa Zabair From: "Rao, Maheswara" [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Sun/Oracle Best Practices URL Date: Tue, 20 Feb 2001 07:30:25 -0800 Zubair, Check this url. http://www.sun.com/software/solutions/blueprints/0101/SunOracle.pdf;$session id$Z2EDDYQAABYJRAMTA1FU45Q Rao [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 20, 2001 3:21 AM To: Multiple recipients of list ORACLE-L Hi Where can i get this Sun/Oracle Best Practices paper. TIA Zabair Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
