RE: GRANT REFERENCES
What SQL or PL/SQL are you trying to run? -Original Message- Sent: Thursday, November 29, 2001 12:53 PM To: Multiple recipients of list ORACLE-L Thanks for your reply Paul. Would you be kind enough to elaborate on the 3rd point. I thought I followed but what I implemented gave me this error ORA-02021: DDL operations are not allowed on a remote database. Any pointers would be most appreciated. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 10:00 AM I haven't done it with the REFERENCES privilige but with others the following will work: 1. create the same user account with the same password in the other database. 2. grant the appropriate permissions to that user. 3. refer to the table with @servicename or create a synonym with the @servicename notation. When the user refers to the object the username/password from the current instance will be passed to the other instance. -Original Message- Sent: Thursday, November 29, 2001 8:50 AM To: Multiple recipients of list ORACLE-L Hi All, I am trying to GRANT REFERENCES on a table that resides on a different database, on a different server. Is this possible and how do I go about doing it. TIA E. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Ekan Emokpae 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: Troiano, Paul (CAP, GEFA) 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: Statistics not updated
Are you seeing 'UNABLE TO EXTEND TEMP TABLESPACE...' in your alert log? -Original Message- Sent: Thursday, November 29, 2001 6:05 AM To: Multiple recipients of list ORACLE-L Hi DBAs The DB is 8.1.5 on Solaris under CBO. The statistics for some of the tables are getting updated after issuing analyze table. I tried with dbms_utility also but no result. What could be wrong? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan 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: Troiano, Paul (CAP, GEFA) 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: Multiple instances - Sun Solaris: shmsys and semsys settings
here is the excerpt from my /etc/system file, we currently have 12 instances on it. Be sure to apply the logic in the comments and not the actual values that I used. The settings will make sense to your SA. The logic comes from reading many papers on the subject (I have them around here somewhere). This is a Sun E5k with 2.5 GB memory, 10 processors running Solaris 2.6 with both 8.0.5.2.1 and 8.1.7.1.0 Oracle databases. I use the same logic on all our systems. HTH - Paul * Revised Shared Memory parameters * * Maximum shared memory size. Set to maximum allowable on * a 32-bit system. Its easier to remember the value in Hex. set shmsys:shminfo_shmmax=0x * Minimum shared memory size. Always set to 1 byte. set shmsys:shminfo_shmmin=1 * Number of shared memory identifiers to pre-allocate. Set to handle * 200 users for 6 instances or 100 users for 12 instances (1200) + * 10 background processes each (60) + 20 for Sybase + 10% (130) * = 1410 - 1500. set shmsys:shminfo_shmmni=1500 * Maximum number of shared segments per process. Set to Oracle * recommendation. However, I recall reading that only a maximum * of 10 is allowed in Oracle. ?? set shmsys:shminfo_shmseg=20 * Revised Semaphore parameters * * Number of semaphores in the system. Set to the shmmni setting x 2. * Oracle needs this extra in order to start up. set semsys:seminfo_semmns=3000 * Maximum number of semaphores per semaphore group. Set to the * maximum number of process per instance. set semsys:seminfo_semmsl=200 * Maximum number of semaphore identifiers. Must be at least semmns/semmsl * (=15) but want to allow for many semaphore groups with less * than the maximum number of semmsl. set semsys:seminfo_semmni=100 * Number of entries in the semaphore map. Pre-allocate the semaphores. * Set to semmns. set semsys:seminfo_semmap=3000 -Original Message- Sent: Wednesday, November 28, 2001 9:05 AM To: Multiple recipients of list ORACLE-L Hi - We are running a solaris server (2.6) for our develoment environment. We currently have 5 instances on the server. When we create another instance and try to start it, we get a ORA-27101: shared memory realm does not exist. The text reads 'Unable to locate shared meory realm'. We have set the following kernal parameters to what we believe to be appropriate. SHMMAX=1207959552 SHMMIN=1 SHMSEG=10 SEMMNI=100 SEMMSL=350 SEMMNS =1000 SEMOPM =100 SEMVMX=32767 Does anyone know of any other kernal parameter settings that may be causing this error? THanks - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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: Troiano, Paul (CAP, GEFA) 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-03113
You could always start a bit nicer and kill pmon, wait a couple of minutes to see if the others are brought down. They usually will as Oracle recognizes the instance failure. -Original Message- Sent: Thursday, November 29, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Why don't you want to want to take all the oracle processes down? I thought that's what you wanted to accomplish with shutdown immediate? Go ahead and issue Mladen's command and restart the database. It's the only way you can bring it down if you can't connect with svrmgrl or sqlplus. Lisa Koivu Oracle Database Monkey Fairfield Resorts, Inc. 954-935-4117 -Original Message- Sent: Thursday, November 29, 2001 10:05 AM To: Multiple recipients of list ORACLE-L Hi All! I tried shutdown immediate and get ORA-03113. Now I can't connect to database. How I can take instance down? On this HP box 10 different databases. This command was posted some time back by Mladen: ps -fu oracle|grep -v PPID|awk '{ print $2; }'|xargs kill -9 It's will take all oracle processes and listeners down which I don't want to do. I'm on Oracle 8.1.6. Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Greg Faktor 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: Troiano, Paul (CAP, GEFA) 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: GRANT REFERENCES
I haven't done it with the REFERENCES privilige but with others the following will work: 1. create the same user account with the same password in the other database. 2. grant the appropriate permissions to that user. 3. refer to the table with @servicename or create a synonym with the @servicename notation. When the user refers to the object the username/password from the current instance will be passed to the other instance. -Original Message- Sent: Thursday, November 29, 2001 8:50 AM To: Multiple recipients of list ORACLE-L Hi All, I am trying to GRANT REFERENCES on a table that resides on a different database, on a different server. Is this possible and how do I go about doing it. TIA E. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Oracle/UNIX vs. Oracle/NT
Sounds like some inverted thinking by management... justify not making the change(?). Usually you would have a cost-based analysis for making the change. I've never seen one that would justify NT (I'd love to see it if they have one). Both Solaris and Linux are alternatives to the OS-specific hardware issues since they run very well on Intel systems. However, when you price out similar Intel-based or Sparc-based hardware, the cost is the same. If they want cheaper hardware, they can buy that. It makes no difference. Since it is already a UNIX shop I am assuming there is no problem finding personnel. They sound confused. -Original Message- Sent: Tuesday, November 27, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Hello everyone, I'm certain that this is a FAQ, but I thought I would make a request here. I have a client whose management is requesting us to make a business case for keeping our Oracle on UNIX rather than on NT. I wonder if anyone can link to or provide any of the following on this subject: * whitepapers * platform selection papers * pro/con summaries to management, no matter how informal Please help in any way you can, thanks in advance, Paul --- www.pythian.com -- [EMAIL PROTECTED] -- 877-PYTHIAN Smarter than adding another team member, Pythian has new services for supplementing DBAs: get our help with monitoring, 24x7 on-call, daily verifications, storage management, performance and more. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vallee 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: Troiano, Paul (CAP, GEFA) 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: Help!: Job Description for Data Architect
How is the job market in Austin? -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 4:00 PM To: Multiple recipients of list ORACLE-L Hey, the job market isn't any better (worse?) here in Austin-by-God, TX than anywhere else. We've got a chance to hire a real talented developer (worked with him before - taught me a lot), but his salary is just a bit more than the school district is used to paying for programmers. I suggested they come up with a new job description to justify the salary (it's not that great folks - this guy just wants to stay in Austin). Wonderful idea says the boss, what should we call the position to confuse the HR folks? Data Architect I reply. Great says he, write it up and have it on my desk by 5:30. Anybody got one I can shamelessly plagiarize? David A. Barbour Oracle DBA, OCP AISD 512-414-1002 -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Which unix command
The rotation of these logs is handled by the syslog daemon. You can see what is specified to be logged to those files in /etc/syslog.conf. HTH - Paul -Original Message- Sent: Tuesday, October 30, 2001 12:55 PM To: Multiple recipients of list ORACLE-L -- Seema Singh [EMAIL PROTECTED] on 10/30/01 11:10:36 -0800 Hi There are messages.0,messages.1,messages.2,messages.3 in /var/adm directory.I want to switch messages.0 into messages.1,messages.1 into messages.2 and messages.2 into messages.3. Which Unix command is used to do this? mv -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Help: truncate lasting 2+ hours
Thanks. I rebuilt the tablespaces with larger extents sizes last night. Took some time to drop though... - Paul -Original Message- To: Multiple recipients of list ORACLE-L Sent: 8/19/01 10:25 AM lots of extents on the table, and a drop storage on the truncate will cause the truncate to run very long -- Oracle needs to release the extents to the FET$ -- which means taking each extent OFF the UET$ and adding it to FET$ -- lots of recursive SQL From: Troiano, Paul (CAP, GEFA) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Help: truncate lasting 2+ hours Date: Sat, 18 Aug 2001 21:45:26 -0800 I have a 12.5 million row table for which a truncate has been running for 2hrs. What can cause this? (Solaris 2.6, Oracle 8.1.7) Thanks in advance, - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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 FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Troiano, Paul (CAP, GEFA) 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: truncate lasting 2+ hours
I have a 12.5 million row table for which a truncate has been running for 2hrs. What can cause this? (Solaris 2.6, Oracle 8.1.7) Thanks in advance, - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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).
Invalid packages not being recompiled by Oracle
We have one package A that refers to package B. If package B's body and specification are both recompiled by user 1, package A is correctly marked as invalid. Another user, user 2, then attempts to execute package A and gets the following error stack: -- ORA-04068: existing state of packages has been discarded -- ORA-04061: existing state of package B has been invalidated -- ORA-04065: not executed, altered or dropped package B -- ORA-06508: PL/SQL: could not find program unit being called -- ORA-06512: at B, line n ORA-06512: at line 2 User 2 then issues the statement 'alter package A compile;' It compiles successfully and user 2 can now execute the package. Why does Oracle not automatically recompile package A as it should? Since user 2 was able to recompile the package in its existing state, Oracle should not have failed at doing so. User 2's session instantiation of package A (as well as all of their other instantiated packages) should have been lost when package A was invalidated. I understand that two possible options are: (1) Flush the shared pool after recompiling; and (2) Manually recompile all dependent packages (such as A). The first seems like overkill and will cause performance issues. The second, with many dependencies involved, is not an option. We need to consistently be able to recompile a package that other packages are dependent upon without a user recieving the above error stack in addition to not impacting performance or without having to recompile all the dependent packages. Thanks in advance, - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Invalid packages not being recompiled by Oracle
Thanks Lisa and others, The dbms_utility.compile_schema will work. It is a bit annoying though as this involves six schemas and hundreds of packages to be recompiled. Fortunatly I do keep control and there are no circular dependencies in the packages. I am sending out an order of dependency among the schemas to the appropriate people. The recursive error seems to have been fixed when I patched to 8.0.5.2.1. Has anyone seen it in 8.1.7? It seems to be only for one package that this happens. It is larger than the rest at 6000 lines and 250 KB Modifying the application to execute again isn't really feasible in this case. There are dozens of packages that refer to this packages. Each one would have to be modified and fully regression tested. As far as standard behavior, Oracle is supposed to (and does in all other cases except for this package) automatically recompile any package flagged as invalid at execution time. It should only raise an error when the package in question can not be recompiled. In this case, the user can 'alter package package_name compile' and all is fixed, provided the 'user' is privileged to do so. Obviously, in production there aren't many who can do this. Any thoughts as to how or what could cause this normal behavior to fail? - Paul -Original Message- Sent: Thursday, June 28, 2001 1:20 PM To: Multiple recipients of list ORACLE-L HI Paul, By chance can you use dbms_utility.compile_Schema after recompiling? Are you using it already? I know it doesn't answer your quesiton but this package is suppossed to follow the dependencies, no matter how odd they are (what you are describing below is pretty weird). HTH Lisa Koivu Database Bored Administrator Ft. Lauderdale, FL, USA -Original Message- Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L We have one package A that refers to package B. If package B's body and specification are both recompiled by user 1, package A is correctly marked as invalid. Another user, user 2, then attempts to execute package A and gets the following error stack: -- ORA-04068: existing state of packages has been discarded -- ORA-04061: existing state of package B has been invalidated -- ORA-04065: not executed, altered or dropped package B -- ORA-06508: PL/SQL: could not find program unit being called -- ORA-06512: at B, line n ORA-06512: at line 2 User 2 then issues the statement 'alter package A compile;' It compiles successfully and user 2 can now execute the package. Why does Oracle not automatically recompile package A as it should? Since user 2 was able to recompile the package in its existing state, Oracle should not have failed at doing so. User 2's session instantiation of package A (as well as all of their other instantiated packages) should have been lost when package A was invalidated. I understand that two possible options are: (1) Flush the shared pool after recompiling; and (2) Manually recompile all dependent packages (such as A). The first seems like overkill and will cause performance issues. The second, with many dependencies involved, is not an option. We need to consistently be able to recompile a package that other packages are dependent upon without a user recieving the above error stack in addition to not impacting performance or without having to recompile all the dependent packages. Thanks in advance, - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Troiano, Paul (CAP, GEFA) 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: db domain being appended to db links when global_names = fals
I actually fixed this one. The db_domain and global_names parameters had changed since the other instances were actually created. They were created with global_names=true and no db_domain specified. In this case the global_name of the db was set to xxx.world (ver. 8.05.2.1). Later the global_names was set to false and a db_domain was set. The new database was created with these new parameters. I just recreated the control files with the new settings and new name. Thanks, - Paul -Original Message- From: Troiano, Paul (CAP, GEFA) Sent: Tuesday, April 10, 2001 10:56 AM To: Multiple recipients of list ORACLE-L Subject:db domain being appended to db links when global_names = false What am I missing? When I create a database link, the db_domain is appended to the db_name even though global_names is false. - This database(dev4) was created using another (awt1) as a model. - The new dev4 database has the problem whereas awt1 does not. - I did a diff on the two init.ora files and found only path and comment differences. - They also share the same tnsnames.ora/sqlnet.ora file (same ORACLE_HOME). - I have 6 instances using the same binaries with dev4 being the only one (and newest) that has the issue. - I commented out the db_domain parameter in the init.ora with no effect (I did bounce the DB). I am using the simple version of the create database statement: create public database link dev4 using 'dev4'; Thanks in advance, - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Troiano, Paul (CAP, GEFA) 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).
INVALID Packages
I have been experiencing some packages that cannot be automatically handled by Oracle when they become invalidated. Here's the scenario: We have package1 that calls package2 which, in turn, calls package3. Under normal circumstances, if package3's specification gets recompiled, package1(?) and package2 will be marked with a status of INVALID. This flags Oracle to re-parse it and mark the status VALID (excluding any coding errors). The problem comes in to play when, every once in a while, a tester or developer will get an error 6508 - could not find program unit... The fix has been to recompile the package being called by the alter package command. You see the bigger problem here... finding the actual library being referred to! I have to jump into the developers code and walk through the execution. And yes I am trying to get the developers to do this on their own. My question is: What is preventing Oracle from re-parsing the package? It has nothing to do with system load or lack of SGA. We're running Oracle 8.0.5.2.1 on Solaris 2.6 w/latest patchset. Thanks in advance - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: INVALID Packages
I think the problem does go away if they try again. However, the users panic when they see an error message. I just got finished re-writing a section of distributed transaction code that was written so poorly Aargh! You'd think the developers could understand transactions but then you'd be wrong. I'm going home. - Paul -Original Message- Sent: Thursday, March 29, 2001 6:10 PM To: Multiple recipients of list ORACLE-L Hi Paul, If the person logs out logs back in, is the problem solved? Or if they try to execute a second time, does the problem go away? Lisa Rutland Koivu Oracle Database Administrator [EMAIL PROTECTED] NeoMedia 2201 Second St., Suite 600 Fort Myers, FL 33901, USA Phone: 941-337-3434 Fax: 941-337-3668 www.neom.com http://www.neom.com http://www.neom.com www.paperclick.com http://www.paperclick.com http://www.paperclick.com www.qode.com http://www.qode.com http://www.qode.com P a p e r C l i c k . c o m http://www.paperclick.com/home.htm http://www.paperclick.com/home.htm Enter Your PaperClick Code Here! -Original Message- Sent: Thursday, March 29, 2001 10:07 AM To: Multiple recipients of list ORACLE-L I have been experiencing some packages that cannot be automatically handled by Oracle when they become invalidated. Here's the scenario: We have package1 that calls package2 which, in turn, calls package3. Under normal circumstances, if package3's specification gets recompiled, package1(?) and package2 will be marked with a status of INVALID. This flags Oracle to re-parse it and mark the status VALID (excluding any coding errors). The problem comes in to play when, every once in a while, a tester or developer will get an error 6508 - could not find program unit... The fix has been to recompile the package being called by the alter package command. You see the bigger problem here... finding the actual library being referred to! I have to jump into the developers code and walk through the execution. And yes I am trying to get the developers to do this on their own. My question is: What is preventing Oracle from re-parsing the package? It has nothing to do with system load or lack of SGA. We're running Oracle 8.0.5.2.1 on Solaris 2.6 w/latest patchset. Thanks in advance - Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Troiano, Paul (CAP, GEFA) 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: Troiano, Paul (CAP, GEFA) 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).