Re: ora 1575?
Are you doing a lot of sorting with a small sort_area_size set? That's what Note:33567.1 in Metalink seems to point to. Since this involves the ST enqueue it is probably also a good idea to see if you can switch to locally managed tablespaces. Switch temp over first. You might want to run a query like this to see what proportion of your sorts are in memory: SELECT a.value Disk Sorts, b.value Memory Sorts, round((100*b.value) / decode((a.value + b.value),0,1, (a.value + b.value)),2) Pct Memory Sorts FROM v$sysstat a, v$sysstat b WHERE a.name = 'sorts (disk)' and b.name = 'sorts (memory)'; Regards, Chris Gait on 8/12/03 12:44 PM, Gurelei at [EMAIL PROTECTED] wrote: Hi all: I'm seeing the ora-01575 error in the alert logfile. The article on the metalink refers to the parameter which I think is obsolete in the ORacle version we are running (8.1.7). What does this error refer to? Any thoughts? references? thanks gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: books recommendations
Jonathan, Yes, please. I'd like the patch as well as the upgrade subscription path so I can paste '10' on as well. I'd also like installer insurance in case the patch hasn't been fully QA'd and breaks two things while fixing one. Seriously, I recommend the book because it explains a lot of features better than anyone else and has an excellent format for quick refreshers on some of the new features as well as in-depth material. Everyone on the list who doesn't already have Jonathan's book (you know who you are!), buy it now! I look forward to meeting you next week in Austin. Regards, Chris Gait On 3 Feb 2003 at 9:06, Jonathan Lewis wrote: Date sent: Mon, 03 Feb 2003 09:06:53 -0800 To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Okay - The official line - Every now and again I think that maybe I could just produce a 9i supplement. But seriously, there isn't a hope. After all, it might encourage Larry to bring forward the launch of Oracle 10. However, for a small fee, I can supply current owners of the book with a patch. This can be stuck over the 8 on the cover - thus modifying the title to Practical Oracle. (Of course, you should always ensure you have a backup copy before applying any patch) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 February 2003 15:53 And of course Jonathan Lewis' Practical Oracle8i is another must-have, though we are all waiting for the 9i edition (nudge, nudge). I seem to remember Jonathan cursing the publisher for putting a version on the book - the vast bulk of the material being relevant for all of 8i and 9i. However, I may be unfairly misquoting him, so I'll leave the authorative comments to him. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: books recommendations
There are a lot of good items on your list. Harrison is the definitive work on Oracle SQL tuning, though it is also worthwhile to pick up the O'Reilly pocket SQL tuning book as well. Robert Freeman's books are uniformly excellent. Geoff Ingram's book was a pleasure to read and contains a great deal of valuable material. It takes a combined approach to high availability and tuning that is refreshingly practical. No Oracle library should be without Tom Kyte's book: http://www.amazon.com/exec/obidos/tg/detail/-/1861004826/qid=1044277898/sr=5- 1/ref=cm_lm_asin/102-1411589-1874536?v=glance And of course Jonathan Lewis' Practical Oracle8i is another must-have, though we are all waiting for the 9i edition (nudge, nudge). If you go to Amazon.com and enter 'Oracle performance' in the search window my list of recommended books should pop up somewhere on the right side of the page. Regards, Chris Gait We are buying books for DBA group and developers . It will be great if u can provide ur feedback about following books: Oracle SQL High-Performance Tuning (2nd Edition) by Guy Harrison High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability [DOWNLOAD: ADOBE READER] by Geoff Ingram Oracle9i High-Performance Tuning with STATSPACK by Donald K. Burleson, Don Burleson Oracle9i RMAN Backup Recovery by Robert G. Freeman, Matthew Hart Oracle 9i New Features by Robert G. Freeman Oracle9i DBA Handbook by Kevin Loney, Marlene Theriault Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: error 472
Ora-472 is a very non-specific PMON error. You've probably already worked
through this with support using a TAR, but if you still are having problems and
wanted to post the following it would help:
The stack trace from your pmon traces (they'll either be in bdump or udump, and by
stack trace I mean just the section of the trace near the beginning with lines like
this:
- Call Stack Trace -
calling call entryargument values in hex
location type point(? means dubious value)
ssexhd + 256 ksedst 1D15 ? 0 ? A ? 0 ?
ssexhd +8 ssexhd 7F7F8678 ? 4011CA78 ?
4011E170 ? 4011CAE8 ?
Adr: 0xC01D072C Adr: 0xC020FE10 7F7F82C0 ? 55 ? 400FE9A0 ?
400F4B38 ?
nioqrc + 596 Adr: 0xC01D072C 7F7F82C0 ? 55 ? 400FE9A0 ?
400F4B38 ?
nioqrc +8 nioqrc 400EF530 ? 47 ? 7F7F6B44 ?
The version of the RDBMS, OS, etc.
Regards,
Chris Gait
On 22 Nov 2002 at 10:19, April Wells wrote:
Date sent: Fri, 22 Nov 2002 10:19:18 -0800
To: Multiple recipients of list ORACLE-L ORACLE-
[EMAIL PROTECTED]
Send reply to: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
There is nothing vaguely resembling anything helpful in either the core dump
or the PMON file. That is where the boat load of metalink docs say to
start, but there is nothing in there that is in any way indicative of what
is going on.
Driving me nuts! There is no real commonality in when it crashes.
April Wells
Oracle DBA
Great spirits have always encountered violent opposition from mediocre minds
-- Albert Einstein
-Original Message-
Sent: Friday, November 22, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Did you look in the PMON trace file? Does anything appear there that might
shed some light on the problem?
RF
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you.
-Original Message-
Sent: Friday, November 22, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
I have cloned an 8.1.7.0 database from another instance (clone doc from
metalink for Apps) on AIX 4.3.3. I don't THINK this is connected to the
clone or (directly) to apps.
Since I brought the database back up after the clone successfully, it keeps
crashing with pick a process terminating instance due to error 472
It comes back when I start it.
The interval between startup and crash appears arbitrary.
What can be accomplished while the database is up is logical stuff... I can
run apps jobs, I can update and select from tables, I can do ddl, I can
manually log switch.
Metalink has BOAT LOADS of docs on this happening, but I haven't found
anything that I am permitted to see that has a resolution. I have an Itar,
but that is not yet yielding any information.
Has anyone seen similar problems, and (if so) IS there a resolution?
Thanks in advance for any advice
ajw
April Wells
Oracle DBA
Great spirits have always encountered violent opposition from mediocre minds
-- Albert Einstein
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(-O;6UU;FEC871I
M;VXL(EN8VQU9EN9R!A='1A8VAM96YTRP@:7,@W1R:6-T;'D@8V]N9FED
M96YT:6%L(%N9!F;W(@=AE(EN=5N95D('5S92!O9B!T:4@861DF5S
MV5E(]N;'D[(ET(UA2!A;'-O(-O;G1A:6X@')O')I971AGDL('!R
M:6-E('-E;G-I=EV92P@;W(@;5G86QL2!PFEV:6QE9V5D(EN9F]R;6%T
M:6]N+B!.;W1I8V4@:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-U
MF4L(1IW1R:6)U=EO;BP@9ESV5M:6YA=EO;BP@=7-E+!OB!C;W!Y
M:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA;B!T
M:4@:6YT96YD960@F5C:7!I96YT(ES('-TFEC=QY('!R;VAI8FET960@
M86YD(UA2!B92!I;QE9V%L+B!)9B!Y;W4@:%V92!R96-E:79E9!T:ES
M(-O;6UU;FEC871I;VX@:6X@97)R;W(L('!L96%S92!N;W1I9GD@=AE('-E
M;F1EB!I;6UE9EA=5L2!B2!R97!L2!E+6UA:6PL(1E;5T92!T:ES
M(-O;6UU;FEC871I;VXL(%N9!D97-TF]Y(%L;!C;W!I97,N( T*#0I#
M;W)P;W)A=4@4WES=5MRP@26YC+B!H87,@=%K96X@F5AV]N86)L92!P
MF5C875T:6]NR!T;R!E;G-UF4@=AA=!A;GD@871T86-H;65N=!T;R!T
M:ES(4M;6%I;!H87,@8F5E;B!S=V5P=!F;W(@=FER=7-ERX@5V4@W!E
M8VEF:6-A;QY(1IV-L86EM(%L;!L:6%B:6QI='D@86YD('=I;P@86-C
M97!T(YO(')EW!O;G-I8FEL:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE
M9!AR!A(')EW5L=!O9B!S;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@
M6]U('1O(-AG)Y(]U=!Y;W5R(]W;B!V:7)UR!C:5C:W,@8F5F;W)E
M(]P96YI;F@86YY(%T=%C:UE;G0N#0H-E1H92!I;F9OFUA=EO;B!C
M;VYT86EN960@:6X@=AIR!C;VUM=6YI8V%T:6]N+!I;F-L=61I;F@871T
RE: Data Purging Strategy
Another poor man's solution would be to unload the tables into flat files and attach to them as needed using Oracle's external table feature from 9i. That solution should hold for quite a while into the future since the external table function is very much like SQL*Loader, which is so integral to so many systems that Oracle is not going to think about making it 'go away'. You would still run into problems if there is some substantive change that makes the external tables from 9i invalid, but that still leaves you with flat files that you can load back into the DB with SQL*Loader. Chris Gait On 6 Nov 2002 at 6:43, Conboy, Jim wrote: Date sent: Wed, 06 Nov 2002 06:43:38 -0800 To: Multiple recipients of list ORACLE-L ORACLE- [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California A poor man's solution might be to load the offline database with appropriate data, then do a tablespace export and store the results on CD labelled by date. Restoring needed data would entail a tablespace import of stuff from the appropriate CD into the offline DB. I'm sure here's some gotchas involved but some variation on that theme might work. Jim -Original Message- Sent: Wednesday, November 06, 2002 8:49 AM To: Multiple recipients of list ORACLE-L This is a data-archival requirement, not a data-purge requirement. It only resembles a purge requirement based on the multiple-database-migration strategy you outlined. There are alternatives... Depending on the volume of data in your database and your availability requirements, implementing table- and index-partitioning will likely be crucial. One strategy is to have the most-active tables partitioned by a date column and have different sets of these partitions reside in time-variant tablespaces. With this arrangement, you can archive data to tape by simply setting the archived tablespaces to READ ONLY and then migrating them to tape-based (instead of disk-based) file-systems and bringing them back online. Legato has this file-system technology (recently purchased) and there is a share-ware product called SAMFS which is an HSM (hierarchical storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By setting tablespaces to READ ONLY it becomes very easy to move them from disk to tape while retaining them within the same original database, simplifying the task of later retrieval (which is really important). Of course, Oracle's partitioning option is enormously expensive, but in this case it is a matter of the upfront license costs (with reduced downstream implementation costs due to simplicity) versus a large downstream application-development cost. In this situation, I think roughly offsets everything. Since I'm not spending the money, I can afford such a calculation... :-) With the various storage technologies available, a single database can straddle several simultaneously, optimizing performance or cost as needed. Some files might reside on solid-state NVRAM disk, some on SAN-based disk, some on NAS-based storage, and then finally reside in archive media file-systems such as tape or magneto-optical based HSM file-systems. - Original Message - To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com Sent: Wednesday, November 06, 2002 2:13 AM Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you
RE: Elementary question about triggers
You could use VPD (virtual private database, AKA fine-grained access control AKA row-level security) to control exactly which rows a given user can delete based on a function using the business rule which determines which rows they are authorized to delete. This works from 8i onward, but there are some nasty 'gotchas', so it shouldn't be done casually (example: use of VPD on a table without authorizing SYSTEM to select from all rows can result in the rows of the table not getting exported). Regards, Chris Gait On 15 Oct 2002, at 1:10, Charu Joshi wrote: Date sent: Tue, 15 Oct 2002 01:10:50 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California It is meant to be a 'selective' deletion i.e. some rows should be allowed to delete where as others shouldn't be. Thanks for your replies. Regards, Charu. -Original Message- Sent: Monday, October 14, 2002 8:30 PM To: Multiple recipients of list ORACLE-L Why not just remove delete authority from anyone not authorized to delete from it ?? -Original Message- Sent: Monday, October 14, 2002 2:34 PM To: Multiple recipients of list ORACLE-L CREATE OR REPLACE TRIGGER temp_trig BEFORE DELETE ON temp_tab FOR EACH ROW BEGIN RAISE_APPLICATION_ERROR(-2,'Do not delete ...'); END; / Bala. -Original Message- Sent: Monday, October 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dear Listers, I hope you won't mind a question that (I think) looks unfit for this list: Can we prevent a delete trigger from deleting a row? To make it clear, here's the code for a trigger: CREATE OR REPLACE TRIGGER temp_trig BEFORE DELETE ON temp_tab FOR EACH ROW ??? END; What statement should I write in the PL/SQL block, that will prevent the deletion of the row. Thanks regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Balakrishnan Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is
Oracle Version Numbers Explained
There was some discussion recently (in the thread on Apache and iAS) on version numbers of Oracle and how confusing they have become. Here is a document that explains the version numbers, at least for the RDBMS. It lost it's authorship along the way, so forgive me for not crediting it. If you have ever wondered what an Oracle release number really means; included below is the break down and how to find out what you are running via a query against a Data Dictionary view. It has changed a little starting with Release 9.2. For the Release number: 9.2.0.1.0 , the significance of each number (reading from left to right) is shown below: Number Significance__ 9 1 Major database release number 2 1 Database maintenance release number 0 1 Application server release number 1 1Component specific release number 0 1Platform specific release number ( Starting with release 9.2, maintenance releases of Oracle are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.) Major Database Release Number This is the most general identifier. It represents a major new edition (or version) of the software that contains significant new functionality. Database Maintenance Release Number This digit represents a maintenance release level. Some new features may also be included. Application Server Release Number This digit reflects the release level of the Oracle9i Application Server (Oracle9iAS). Component Specific Release Number This digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases. Platform Specific Release Number This digit identifies a platform specific release. Usually this is a patch set. Where different platforms require the equivalent patch set, this digit will be the same across the effected platforms. How to Check Your Current Release Number: To identify the release of the Oracle database server that is currently installed and to see the release levels of other Oracle components you are using, query the Data Dictionary view PRODUCT_COMPONENT_VERSION. Here is a sample query. (Yes, the old instructor part of me wants your Sql*Plus session to be readable, so included are 3 Sql*Plus commands before the query since each of these column widths are 65.:-) ) COL PRODUCT FORMAT A40 COL VERSIONFORMAT A15 COL STATUS FORMAT A15 SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCTVERSION STATUS --- --- NLSRTL 9.2.0.1.0 Production Oracle9i Enterprise Edition 9.2.0.1.0 Production PL/SQL 9.2.0.1.0 Production TNS for Solaris: 9.2.0.1.0 Production Optionally, you can query against the V$VERSION view to see component-level information as well. (Other product release levels may increment independently of the database server.) Regards, Chris Gait -- 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).
RE: Cost vs Rule
Here's a convincing stat for your architect: There will be no RBO in a future version of Oracle. That version is not far away (conjecture is perhaps as early as 10). As often occurs, this is a case of do what we say, not what we do, since RBO is still used in the data dictionary tables. However that is a very special case of RBO for a primarily clustered, legacy schema that is in the process of being migrated to CBO. You can bet that when the data dictionary moves to CBO, RBO is going to go away. Now if you want to tie your application to a feature that severely limits the new features you can use, reduces scalability (forget bitmap indexes, partition pruning, function-based indexes, etc.), and is strongly discouraged by the vendor, then anchor yourself to the rock of RBO. Just make sure you don't have a short chain and a rising tide. While your architect is at it, make sure they use lots of the LONG datatype. It's really inconvenient and not long for this world too. And by the way, CBO works better than RBO in the great majority of cases. You need to investigate why your PK index is not being picked up in your query, not toss the entire current technology and fall back to heuristic optimization. As someone else mentioned, a hint is just a comment when viewed by other RDBMS. You can and should use hints and init.ora parameters to tune server and optimizer behavior. Regards, Chris Gait On 13 Mar 2002, at 7:08, Magaliff, Bill wrote: Date sent: Wed, 13 Mar 2002 07:08:25 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California yes, everything analyzed. sr tech arch has decided he wants to use RBO due to predictability in production. not much I can do at this point, unless I can really come up with convincing stats -Original Message- Sent: Wed, March 13, 2002 6:34 AM To: Multiple recipients of list ORACLE-L Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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
RE: RE: Manager decrees his data warehouse design. Help!
Patrice, You write: What is the meaning of relational in relational database again? Apparently not what you think. The relational in 'relational database' comes from the term relation--a certain type of mathematical table in set theory. It has nothing at all to do with relationships (the confusing term chosen for the connections between relations in a RDBMS). Thus the hideous monstrosity described in this thread is not non-relational because it is stuffed into a single table (relation), however it is also NOT normalized and NOT usable. One could, however, argue that this makes it non-relational in that it is so lacking in normalization that it cannot be distinguished from a flat file. The boss described here is similar in many ways to a flat file--obtuse, limited, stubborn and out-of-date technologically. But I would rather deal with a flat file any day than with such a flat- headed person. Regards, Chris Gait On 27 Feb 2002, at 12:28, Boivin, Patrice J wrote: Date sent: Wed, 27 Feb 2002 12:28:25 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California What is the meaning of relational in relational database again? Good grief. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- 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).
RE: Anybody against using views?
View positives: They simplify what the developer and user see, thus making SQL logically simple You can put hints on a view, thus forcing the optimizer to use the view as you know best. This can sometimes be the only way to optimize a third party application that puts all its SQL in a black box. View negatives They're just stored SQL, so the impression that they are some kind of table can lull users and developers into thinking that it's perfectly alright to nest the heck out of them in statements...which is bad in almost all cases They hide the reality of underlying physical objects. Sometimes when you carve away all the views you find that you should redesign the tables themselves to allow for cleaner SQL. And finally one item that is neither positive nor negative. A materialized view (aka snapshot) can get you massive performance gains if used well, but that's more like a table than it is a view. -Original Message- Sent: Thursday, February 21, 2002 9:18 AM To: Multiple recipients of list ORACLE-L We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- -- 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).
Re: ERR: ORA-04031 unable to allocate
Take a look at Metalink note :1076835.6. You may be running out of underlying OS user process memory. Changing some kernel parameters may help. One other thing to check: Is this query going parallel (i.e. is the degree of any of the underlying objects 1 and you are seeing parallel query process slaves showing up on the server?). Regards, Chris Gait On 14 Feb 2002, at 16:03, Baker, Barbara wrote: Date sent: Thu, 14 Feb 2002 16:03:32 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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: 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: Performance Tuning
Okay, I'll plug my own list of recommendations on Amazon. It should come up from this URL: http://www.amazon.com/exec/obidos/tg/listmania/list-browse/- /VL8CI2YJANX1/qid=1013704150/sr=5-1/ref=sr_5_1/103-8633316- 6595843 If it doesn't, just enter 'Oracle performance tuning' in a search on Amazon.com and look to the right. My list usually pops up. If you just get one book on performance, go with Performance 101. If you're getting two books, get 101 and Tom Kyte's book. Regards, Chris Gait On 30 Jan 2002, at 2:25, [EMAIL PROTECTED] wrote: Date sent: Wed, 30 Jan 2002 02:25:19 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Hi Everyone I am interested in purchasing some exceptionally good book on Oracle Performance Tuning. Can anyone suggest me some very good book on Oracle Tunning worth purchasing Binay Kumar Oracle Cerified DBA London *** Binay Kumar Focus 3 -Technical Support P O Nedllyod London 0044 207 441 1648 *** --- The contents of this e-mail are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee of this e-mail you should not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. If you have received this e-mail in error please notify us by telephone or e-mail the sender by replying to this message, and then delete this e-mail and other copies of it from your computer system. Thank you. We reserve the right to monitor all e-mail communications through our network. -- 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: 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: Database Performance Question
Srini, If you have 400 concurrent users (2000 total users, with about 400 concurrent sessions typical for the busy time of the day), then you should probably be using MTS instead of dedicated connections. Using Microsoft pooling in COM may work out, but please check with Oracle support on whether you are encountering a bug. What version of Oracle are you on? This will determine if you may be encountering the bug. If you are encoutering the bug, the COM is not closing down connections quickly enough, running your system out of resources. You are probably short on memory to start with if you are running that many users dedicated on an (NT? WIN2K?) box. This could also very well be a problem with the size of the data being returned, but you will need to eliminate networking/COM connection issues as well as the problems in the database. The suggestion made to look at V$SYSTEM_EVENT is right on the money. That should determine where the main problem is: sorts, I/O, memory, etc. As you can tell from the replies, this kind of thing is a little difficult to diagnose without getting a lot of information up front. Regards, Chris Gait On 29 Jan 2002, at 9:35, [EMAIL PROTECTED] wrote: Date sent: Tue, 29 Jan 2002 09:35:41 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Kirti: Thanks for the insight. Here are my Answers: 1. I feel database is the problem because when only a subset of the total data is used the performance is excellent (with one tenth of the total data). The no. of users are the same. Only amount data that is retrieved is enormous (almost 10 times) when we have the performance problem. 2. Partioning may/may not help as it still has to traverse through all of the partitions to get the data required in order to satisfy the join condition. May improve response time but not significantly. 3. I will look into this as well. 4. Connections on the database side are DEDICATED and on the web side they are shared.(Using Microsoft COM objects, have certain set of database connections as POOL that are shared by different users.) Please let me know what you think... Srini -- 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).
Re: Centralized Alert Log Monitoring
While the script approach is excellent, and you have gotten several good answers on how to use it, might I suggest looking into event triggers? If you are running 8i or above (not 8.1.5, it's buggy on this), you can create a database level trigger ON ERROR. If you have Java turned on for your system you can have Oracle email you the error directly. Otherwise you can use a daemon on the system to pick up the email duties. There are a few errors that are ignored, but they are the kind of thing that should be ignored (like programs passing characters to number parameters). The only errors this is going to miss are catastrophic, i.e. when you get a core dump that kills SMON the trigger won't fire because the instance is already down. For that kind of thing only OS scripts will help. Regards, Chris Gait -- 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).
RE: Operating Systems
There has been a version of Oracle for Solaris on Intel available for some time. Unfortunately that time is coming to an end. There will not be any Solaris Intel Oracle 9, so 8i is the end of the line for the platform. Regards, Chris Gait -- 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).
RE: When optimizer reevaluate SQL statement
I'm afraid I can't agree with the statement that two different users submitting the same SQL cause a hard parse. It is my understanding of the process that part of the parsing occurs when another user is submitting an identical SQL statement, but this does not constitute a hard parse, just a check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! Please do not post Off Topic to this List !! If the Oracle executing user is different, the execution plan will be different due to possibility of different objects and security. Different sessions under the same user can share using bind variables. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:25 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris: If you are executing it under a different user the statement will be reparsed. I'm puzzled. I always thought that different users submitting the identical SQL statement would use the same plan and not need to be reparsed. I understood that to be part of the reasoning behind bind variables and the big advantage of cursor sharing. Please explain. Jon Walthour -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walthour, Jon (GEAE, Compaq) 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: Christopher Spence 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: 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).
Seeking SQL*Reportwriter 1.0 antiques
A client of mine is moving off some ancient hardware (with ancient software) and needs for the short term to run SQL*Reportwriter 1.0 for DOS, then convert those files to version 1.1 of the same. If anybody is an antique collector and still has either the install disks or runtime of these programs I would be very grateful if you could give me a copy. They are licensed, so copying is not a problem. Regards, Chris Gait -- 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).
Re: Analyze schema1.mytable from schema2 in a stored procedure
The key to the whole deal, as it turns out, being that the ANALYZE
ANY privilege must be granted explicitly in order to work in a stored
procedure. I tested this with a procedure for analyzing a table and
not even sys had sufficient privileges. But once I granted 'analyze
any' to a user they were able to run the procedure without
complaint. Once again the 'explicit grant, not from a role' troll has
appeared.
Regards,
Chris Gait
On 10 May 2001, at 8:35, Bill Pribyl wrote:
[EMAIL PROTECTED] wrote:
I have two schemas: schema1 and schema2. I need to analyze
schema1.mytable from a stored procedure owned by schema2. Schema1 has
granted ALL on mytable to Schema2.
From the Administrator's Guide: To analyze a table, cluster, or index, you
must own the table, cluster, or index or have the ANALYZE ANY system
privilege. So your grant of ALL doesn't really help. Apparently, schema2 has
received ANALYZE ANY..
Logged into SQLPLUS as Schema2, these
both work:
ANALYZE TABLE SCHEMA1.MYTABLE COMPUTE STATISTICS;.
EXECUTE DBMS_DDL.ANALYZE_OBJECT
('TABLE','SCHEMA1','MYTABLE','COMPUTE');
However, when schema2 executes a stored procedure with this line
DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE')
I get an insufficient privilege error.
By default, the stored procedure rights model is owner's rights, in which
case privileges at run time are determined by the privileges of the owner of
the stored procedure. (Actually, the privileges are determined at compile
time, but if privs change, the procedure has to get recompiled before you run
it). In your case, the owner of the stored procedure would need to be able to
execute the procedure successfully in order for others who have execute
privilege on it to succeed. I don't know who owns your procedure, but imagine
it's schema3. Then this should work:
CONNECT system/manager
GRANT ANALYZE ANY TO schema3;
CONNECT schema3/passwd
CREATE PROCEDURE whatever
AS
BEGIN
DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE');
END;
/
GRANT EXECUTE ON whatever TO schema2;
...and schema2 should be good to go.
If the procedure was created with invoker's rights (AUTHID CURRENT_USER,
introduced in 8.1.6 as I recall), then at run time the privileges of the
invoker apply. (Generally, invoker's rights applies only to tables, but I
tested your case and it seems to apply here as well.) Since you said schema2
can run the commands from the command line, recompiling with invoker's rights
ought to work...
CONNECT system/manager
GRANT ANALYZE ANY TO schema2;
CONNECT schema3/passwd
CREATE PROCEDURE whatever
AUTHID CURRENT_USER
AS
BEGIN
DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE');
END;
/
GRANT EXECUTE ON whatever TO schema2;
...again, schema2 should be good to go.
Good luck
Bill
--
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).
RE: Insert Cardinalities into the data dictionary directly
If the production environment is already available, you could use DBMS_STATS.EXPORT_TABLE_STATS on the production table and import those stats into your development instance. Regards, Chris Gait On 18 Apr 2001, at 11:00, Murali Vallath wrote: Thanks for the feedback, I am coming from the Oracle Rdb world, where these row counts/cardinialities could be inserted into the data dictionary to simulate the optimizer behaviour similar to a production environment. I see your point, this could be a negative impact to the optimizer. Thanks for the input.] Murali -- 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).
Re: Database Startup Trigger is not pinning all packages
As others have pointed out on this thread, I was wrong, and you
can in fact pin packages in a startup trigger that have not been
loaded into memory. I was confusing this with functions and
procedures (and cursors) which have to be in memory before they
are pinned and will raise an error if not already loaded. I'm not sure
how far back pinning packages like this goes, but I tested on 8.1.6
and it works.
I did however try to reproduce your error and found how to get the
trigger working. I had to explicitly grant execute to sys for the
packages before they would load from the startup trigger.
Regards,
Chris Gait
On 7 Feb 2001, at 8:50, Lindsay Stoddard wrote:
Greetings,
I have a database startup trigger which is created from the sys account.
The trigger is only designed to pin some packages in the shared pool.
However, the trigger is only pinning the packages that belong to the sys
account. I am getting the following error in the alert log file when it
tries to pin packages which belong to other users:
Registered presentation http://admin on database startup
Error in executing triggers on database startup
*** 2001-02-07 09:00:48.644
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at line 21
The following is a sample of the trigger:
CREATE OR REPLACE TRIGGER database_startup
AFTER STARTUP ON DATABASE
BEGIN
sys.dbms_shared_pool.keep('sys.standard');
sys.dbms_shared_pool.keep('sys.dbms_sys_sql');
sys.dbms_shared_pool.keep('sys.dbms_sql');
sys.dbms_shared_pool.keep('sys.dbms_utility');
sys.dbms_shared_pool.keep('sys.dbms_standard');
sys.dbms_shared_pool.keep('sys.dbms_output');
sys.dbms_shared_pool.keep('sys.dbms_pipe');
sys.dbms_shared_pool.keep('ctxsys.driutl');
sys.dbms_shared_pool.keep('ctxsys.driutl');
sys.dbms_shared_pool.keep('oas_public.htp');
sys.dbms_shared_pool.keep('oas_public.htp');
END;
/
All of the commands in the trigger can be executed successfully when logged
into sqlplus as the sys user with the following syntax:
BEGIN
sys.dbms_shared_pool.keep('oas_public.htp');
sys.dbms_shared_pool.keep('oas_public.htp');
. . .
END;
/
Because of this I am wondering why the trigger errors out.
If anyone can help me I greatly appreciate it. Oracle Support was not much
help.
Thanks,
Lindsay Stoddard
ACS - GSG
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lindsay Stoddard
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:
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: COPY command dieing - help
I ran into something like this on a client's system recently. They had a LONG in the table, and that won't come over with the COPY command. If that's the case here, then you need to use the TO_LOB() function to take it from LONG to CLOB on the fly. As sometimes happens, the error message can be misleading. Regards, Chris Gait On Thu, 1 Feb 2001, Shaw, John B wrote: I am attempting to copy a table from a 8.1.6 db to 8.1.7. Most of the tables have come across successfully. One table is having a problem. The command: copy from v55/v55@int to user/pwd@frog create DMF using select * from DMF@int; This table gives ora-00911 error (invalid character ? where? - this same command worked on many other tables). I manually created the table in the local db and tried to do an append of the data copy from v55/v55@int to user/pwd@frog append DMF using select * from DMF@int; This causes sqlplus and sqlworksheet to die (this is on NT0 anybody have an idea of what's going on? -- 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).
Re: Database Startup Trigger is not pinning all packages
I would need to double-check on your error, but the usual problem with pinning packages at startup is that they haven't been loaded into memory (standard and some of SYS's other packages load almost at the initial moment of startup). Try to put some code into the trigger to call the packages first, then load them. One approach is to put in dummy function in the user packages that returns null and call that function, thus pulling the package into memory. Regards, Chris Gait -- 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).
