Sql Tuning Thoughts?
This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 0 43814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the following annotations on 01/22/2004 10:24:24 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
Table access
I am looking for a script that I can supply a table name and it returns all users that have access to it (either directly, thru system priveleges or thru roles) and what the access is. Does anybody have something like this that I can use? Thanks American Express made the following annotations on 01/13/2004 08:16:14 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
RE: Suggestions Needed: Latch free - library cache
During the Hotsos course I thought I remember hearing that the pool could be too large and that could have a negative impact on the library cache latch. Am I confusing this with something else (maybe the buffer busy event)? If true how do you go about determining the optimal size? From: "Cary Millsap" <[EMAIL PROTECTED]>@fatcity.com on 01/07/2004 07:44 PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Subject: RE: Suggestions Needed: Latch free - library cache One way is by writing applications that use persistent connections. A mid-tier program ignites at 8amJan 1, connects to the Oracle instance, and then parses all the SQL it will use for the remainder of the year. The interface to this program from the user side is via "services," like "hire an employee," "fire an employee," "look up a salary," or whatever. Thousands of users throughout the year use the services, but the only parse calls the application makes all year occurred during the first few minutes of the morning on New Year's Day. Advantages are huge if you eliminate what would otherwise have been thousands of connections/disconnections and parse calls per day. All the db gets are binds, executes, and fetches except for the first few minutes after instance start-up. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7–10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: Wednesday, January 07, 2004 6:15 PM To: Multiple recipients of list ORACLE-L Subject: Re: Suggestions Needed: Latch free - library cache how do you limit excessive soft parsing? - Original Message - From:Bobak, Mark To:Multiple recipients of list ORACLE-L Sent:Wednesday, January 07, 2004 5:59 PM Subject:RE: Suggestions Needed: Latch free - library cache Tracy, What Oracle version? If you're not patched up to the latest patchset for your release, it's always a good idea to do so, as library cache bugs seem to invariably appear in every release. Has your code changed recently? Has your usage increased recently? Finally, library cache latch contention can be a sign of excessive soft parsing (hard parsing usually causes more shared pool latch contention). Do you have session_cached_cursors set? Just some thoughts -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -Original Message- From: Tracy Rahmlow [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 5:40 PM To: Multiple recipients of list ORACLE-L Subject: Suggestions Needed: Latch free - library cache We have experienced intermittent problems (slow response time) with our oltp database today. There appears to be a large number of latch free events and the p2 parameter is indicating an issue with the library cache. Any thoughts on where to go next? American Express made the following annotations on 01/07/2004 03:36:25 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == American Express made the following annotations on 01/09/2004 07:39:30 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
Suggestions Needed: Latch free - library cache
We have experienced intermittent problems (slow response time) with our oltp database today. There appears to be a large number of latch free events and the p2 parameter is indicating an issue with the library cache. Any thoughts on where to go next? American Express made the following annotations on 01/07/2004 03:36:25 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
24 x 7 x 365
Hello, Our company would like to know whether or not Oracle supports true 24x7x365 availability for an oltp database. We currently are using the 8.1.7 enterprise edition. Does an architecture exist whereby we can upgrade the database and/or operating system and not cause an outage? Will RAC solve this issue? Are there any other areas of concerns that I should be thinking about? For example, analyzing with the validate clause and its impacts on the transaction system. Thanks American Express made the following annotations on 12/10/2003 09:41:15 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
Oracle 10g Migration
Does anybody know whether or not Oracle will support and upgrade of an 8i database to 10? Thanks American Express made the following annotations on 11/21/2003 07:28:27 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** ==
RE: Any articles/books that take relational theory and make it
"Data Modeling Essentials" by Graeme C Simsion is a very good book. American Express made the following annotations on 11/19/2003 07:36:46 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Need A Split File Option
A developer in our shop would like to read an input file and based on some field values for each record in the file split them into multiple files. The output files will be used by a 3rd party package for processing. (the package does not need the oracle database) For example: Input File: record a: WI auto ... record b: WI auto ... record c: NY auto ... record d: YY home ... Output file WI_AUTO: record a: WI auto ... record b: WI auto ... Output file NY_AUTO: record c: NY auto ... Error file: record d: YY home ... (no entry in the criteria lookup table to pick up this record therefore send it to an error file) Their solution is to load an oracle table with the input file. An additional table would contain the criteria and the name of the output file to write to. They would write a procedure to read the criteria and input table and utilize the utl_file package to create the output files. There may be 50+ output files initially and likely will grow over time. My gut tells me that this does not belong in the database, rather we should be able to split the file using C or a utility such as syncsort (which we do not have). We are currently at 8.1.7.4 on AIX 4.3.3. Is there a more elegant solution and what would it be??? Are there any open source unix utilities that may help? Thanks American Express made the following annotations on 11/13/2003 12:20:23 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Client Search Info Needed
I am looking for an efficient solution to the following: We intend to capture information about a client such as: first name - John last name - McDonald phone numer - 222.222. zip code - 4 state - FL client number - 123343 The names will be stored in mixed case for proper printing on client documents. The reps would like the flexiblity to enter the search criteria in a number of formats such as: 1) last name like mcdon* (wildcard) and first name = john 2) client number = 123343 (note: some clients do not always have their client number handy so it can not be the only available search mechanism) 3) last name = mac gregor (and locate both macgregor and mac gregor) 4) last name = kinney-jones (and locate both kinney-jones and kinney jones) How many indexes and of what type are required? Does the leading the column of an index have to be specified for the index to be used? I thought I remember hearing that that was a limitation of an older release, but that is no longer the case with 8 and up. Are there any white papers available that address the topic of client search and best practices? Thanks for your help!! American Express made the following annotations on 10/30/2003 04:11:07 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: wither Designer documentation?
I have had the same problem. I have contacted Oracle for a "user guide" with no response as of yet. I am literally printing off the help pages within Designer to address the need. It sucks 10/28/2003 10:14 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Where is the Designer 9i documentation? Not on OTN (http://otn.oracle.com/documentation/designer.html), not in download-east (http://download-east.oracle.com/docs/cd/A91773_01/ids902dl/index.htm ), not in tahiti... (http://tahiti.oracle.com ) Oracle Designer Generation seems to be the only book (Oracle Press). Oracle Designer Handbook by Pete Koletzke was published in 1998. Patrice -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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). American Express made the following annotations on 10/28/2003 11:27:28 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Cary's Book
Fyi, I just received confirmation from Amazon that my pre-order was shipped. American Express made the following annotations on 09/22/2003 10:46:31 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: SharePlex Summary
Frankly, that does not sound logical to me, but I would be extremely interested if there is any authenticity to the statement. I would ask the individual who made the statement to provide the proof. I can't stop envisioning this on the next myth list. PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: First thanks to every one who responded both on the list and to my private email: The consensus appeared to be: 1. SharePlex is overly expensive for the functionality delivered and 2. Oracle has caught up in 9i for much of the functionality 3. Some features of Oracle like IOT's may present some problems. We are on HPUX and 9i is 64 bit only on that platform. I have been told that the 64bit code imposes a 20 - 25% performance penalty vs the 32 bit version of 8.1.7. Can anyone address this from experience? Allan L. Nelson Oracle DBA M-I L.L.C. (832) 295-2238 office (832) 351-4180 fax [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> American Express made the following annotations on 08/12/2003 01:10:13 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: TOAD vs BMC's XRAY
For development, take a look at Allround Automations Pl/Sql Developer. Nice functionality and an affordable price (~$50 in bulk). http://www.allroundautomations.nl/ AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: I haven't used DBXray (yet -- maybe it's not an automated process to download a trial?), but I don't feel TOAD is the best at DB administration, especially for performance tuning/monitoring. Then again, it's been awhile since I've used TOAD's optional DBA module, since it's no longer available in the beta. For development, I think it's the best, especially at US $795/user. I've been a TOAD user since v4, when it was Jim McDaniel's freeware baby. For tracing into running SQLs I prefer using Quest's Spotlight. Aside from a slick interface (makes impressive screen shots for management-types), it is very easy to drill down into problem areas, like resource hogging sessions you mention. I still think that any tool requires knowledge of the underlying queries they use to fully understand what the tool is telling you, I just happen to prefer Spotlight's interface. If I ever get the email for the DBXray trial from BMC, I'll let you know what I think of that compared to the others. > -Original Message- > From: Fermin Bernaus [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 22, 2003 11:20 AM > To: Multiple recipients of list ORACLE-L > Subject: TOAD vs BMC's XRAY > > > > Hi there, > > I am considering buying an administration tool and > programmer's tool. The two I know are Toad and BMC's XRAY but > since I do not know them very well I have difficulties > deciding which of them is the best. What I need more is to > tune the database, trace into time and CPU consuming SQLs, a > procedure / trigger editor and debugger. > > Or maybe you know better tools than these two... any > help will be greatly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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). American Express made the following annotations on 07/22/2003 10:23:05 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ****** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Cary/Others RE: should you seperate indexes from tables in
Based on Cary's paper regarding when to use an index, would there not be value in having index tablespaces with a smaller block size vs tables using a larger block size? American Express made the following annotations on 07/16/2003 10:11:40 AM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Cary/Others RE: should you seperate indexes from tables in
of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). American Express made the following annotations on 07/15/2003 02:15:07 PM -- ** "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." ********** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: Extent allocation for objects
Fyi,
Note I was referring to the other day.
04:11 PM PST
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:
At 02:16 PM 5/19/2003 -0800, you wrote:
>Adding the initial clause appears to work fine. Thank you very much for
>your help.
>
>Thanks for the other responses. I learn new things every day.
If you specify an initial extent size that is at most as large as the
smallest uniform extent size of any of your LMTs then you'll not run into
this problem in the first placs. Consider:
SQL> select
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTLEN,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
2 from dba_tablespaces where tablespace_name in ('USERS','TOOLS')
3 /
TABLESPA INITIAL_EXTENT NEXT_EXTENT MIN_EXTLEN CONTENTS EXTENT_MAN ALLOCATIO
-- --- -- - -- -
TOOLS262144 262144 262144 PERMANENT LOCAL UNIFORM
USERS 32768 32768 32768 PERMANENT LOCAL UNIFORM
i.e. 2 tablespaces with 256K and 32K uniform extents respectively.
SQL> create table a (n number) tablespace tools;
Table created.
SQL> create table b (n number) tablespace tools storage( initial 2K);
Table created.
pollux.stats.scott> select segment_name, segment_type, tablespace_name,
initial_extent, extents, bytes
2 from user_segments where segment_name in ('A','B');
SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENTEXTENTS BYTES
-- -- -- --
ATABLE TOOLS262144 1 262144
BTABLE TOOLS 8192 1 262144
The 2K initial extent request for table b got adjusted by Oracle to the
minimum possible in the database with a 4K db_block_size, but it got
recorded in tab$ even though a 256K extent got allocated in the TOOLS LMT
according to the uniform extent size.
SQL> alter table a move tablespace users;
Table altered.
SQL> alter table b move tablespace users;
Table altered.
SQL> select segment_name, segment_type, tablespace_name, initial_extent,
extents, bytes
2 from user_segments where segment_name in ('A','B');
SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENTEXTENTS BYTES
-- -- -- --
ATABLE USERS262144 8 262144
BTABLE USERS 8192 1 32768
Because the recorded initial extent size of table b is less than the
uniform extent size of the target tablespace, it gets sized down correctly
while table a maintains it allocated size.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
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).
American Express made the following
annotations on 06/09/2003 02:26:35 PM
--
**
"This message and any attachments are solely for the intended recipient and may
contain confidential or privileged information. If you are not the intended recipient,
any disclosure, copying, use, or distribution of the information included in this
message and any attachments is prohibited. If you have received this communication in
error, please notify us by reply e-mail and immediately and permanently delete this
message and any attachments. Thank you."
**
==========
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tracy Rahmlow
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')
Hotsos Clinic 101 Feedback
Anybody take this course before. Any comments, good or bad. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: Upgrade to AIX 5.2
John/Kirti,
We are in the same boat with you. However, we were looking to first upgrade to
AIX 5L first and leave our existing 8.1.7 32-bit databases (booting the server
in 32 bit mode). In a subsequent phase we would upgrade 8.1.7 32-bit to 9.2
64-bit. The downside to this is that it will affect all databases on the
server. We would need to test the complexity/timing/risk to determine if this
is the appropriate choice. Kirti, with the option that you are leaning
towards, as I see it, you need to upgrade to 5L and then install 9.2 for 5L
(there is also a 9.2 for 4.3.3) and then migrate the databases from 9.2/4.3.3
to 9.2/5L. Do you know what effort is involved in that transition? Is it
complex/time consuming or just pointing to a new oracle_home? Thanks
02/27/2003 05:43 AM PST
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:
It appears that way. We are also pondering on this upgrade as well.
But if one takes this route, that is, running 5L in 32-bit mode, one can not
install 9i on the same server. And Oracle has not certified 8.1.7 64-bit on AIX
5L. It is not planned to be available, either.
So, we are leaning towards upgrading to 9i first on AIX 4.3.3. Next upgrade OS
to AIX 5L 5.1 and then 5.2 (some of the apps will become available on 5.2 later
this year)..
- Kirti
-Original Message-
Sent: Thursday, February 27, 2003 7:09 AM
To: Multiple recipients of list ORACLE-L
So 8.1.7 is supported on AIX 5L provided AIX is booted in 32 bit mode???
John
-Original Message-
Sent: 27 February 2003 12:24
To: Multiple recipients of list ORACLE-L
Certify - Additional Info Oracle Server - Enterprise Edition Version 8.1.7
(8i) On IBM AIX -Based Systems
Operating System: IBM AIX -Based Systems Version 5.2 (5L)
Oracle Server - Enterprise Edition Version 8.1.7 (8i)
N/A Version N/A
Status: Certified
Product Version Note:
Terminal Oracle8i release
To obtain Patch Sets from MetaLink, click the "Patches" button to the left.
Certification Note:
Existing patch sets:
8.1.7.1.0 (without JDBC),
8.1.7.1.0b (includes JAVAVM, Context and JDBC),
8.1.7.2.0
8.1.7.3.0
8.1.7.4.0
Oracle 8i 32-bit on AIX 5L (5.1 & 5.2)
Kernel modes AIX 5L introduces the option to run the AIX kernel in 64-bit
mode. This mode is not supported, as Oracle 8i uses at least one 32-bit
kernel extension. AIX-based systems must be booted with kernels in 32-bit
mode.
Warning about missing "crash" during installation of Oracle 8i.
The error message "crash: not found" may appear during execution of
rootpre.sh on AIX 5L. This warning may be ignored. Alternatively, the
warning can be avoided by creating the following script and renaming it to
"/usr/sbin/crash":
#!/bin/ksh
read input
if [ X"$input" = Xle ]; then
echo lke | /usr/sbin/kdb | \
/usr/bin/awk '/^ *[0-9]+/ {printf("LoadList entry at 0x%s\n Name:
%s\n\n",$2,$6)}
fi
April Wells
Oracle DBA
Great spirits have always encountered violent opposition from mediocre minds
-- Albert Einstein
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
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: Tracy Rahmlow
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).
Raj: RE: AIX & Oracle 9.2
Fyi, There is a version of 9.2 for AIX 4.3.3 and 5L. At least I think they are different, because the media is shipped with the os version noted as such. Have you tried this Raj? on 02/13/2003 08:19 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: don't reinstall if you already have 64bit installed, just recompile ... it should work fine. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, February 13, 2003 10:04 AM To: Multiple recipients of list ORACLE-L If I have Oracle 9.2 on AIX 4.3.3 and we upgrade to AIX 5.1, do I need to re-install the Oracle 9.2 software or will it all just work after the upgrade? Anybody know? Keith H. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry, Keith 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). (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
metadata repository
Is anybody aware of a freeware metadata repository tool that is configurable enough to capture metadata attributes that the business would value? For example, to capture information about columns such as length, aliases, valid values ... Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: Oracle Support Alert 223399.1 on Export Bug 2598387
Raj, There appears to be 2 related bugs 2410612 & 2598387 with different workarounds. When you state you already made the change what change are you referring to? on 02/04/2003 06:53 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: We already made the change even though we use buffer at 64K Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, February 04, 2003 9:29 AM To: Multiple recipients of list ORACLE-L I noticed that Alert 223399.1 had been added to the 9iRelease2 and 8.1.7 Support Status and Alerts summaries a few days ago. The bug described is quite significant. Last weekend I had upgraded an 8.1.7.2 database to 9.2.0.2 using export-import [as we were upgrading the OS and rebuilding the RAID-5 arrays and re-organising the file systems at the same time] and I can't just go back and verify each of the multi-million-row tables. Look up bug 2598387 -- it's scary. Hemant Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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). (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
Export/Import Error and Validation !!!
We attempted to reorg a table and data corruption resulted. We have isolated the issue and currently have a tar open with Oracle. But basically, when we export the table with a buffer=10485760 the import process corrupts the data. The row count matches, however some fields that initially were null now contain data (there were some other odd data issues in addition). No errors were produced during either the export or import process. When we used a buffer=65536 the table data was correct. This is reproducable. Has anybody seen this before? (AIX 4.3.3 / Oracle 8.1.7.3) Can the buffer size be set too high? In addition, are there any audits that we can employ to catch such an error? For example, row counts were the same prior and post the process. Are there other things that can be checked. How do others validate that a reorg was successful? We were thinking about executing a data compare through sql of the old and newly reorged table. This seems excessive and not practical for very large tables. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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: Statspack Viewer
http://www.geocities.com/alexdabr/ I have not seen any comments about this product from anyone yet. I am not sure if it is too new, unknown or what. Again, I am looking for any comments from anyone who has used or evaluated it. Thanks Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: What is the URL for this product? RWB -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
Statspack Viewer
Anybody using this product? What are your thoughts about it? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow 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).
N-tier data access and security
We are heading down the N-tier path, (browser, websphere app server, oracle database). What is the best method to setup this architecture for security and the logic? 1) Should the business logic be physically separate from the data access on the app server? And if yes, where should the data access component reside? (database or app server) How does batch affect the decision? What variables should we be considering when making this decision? 2) How should the database connection be established from the app server to the database? Should it use a generic account on the app server with the password encrypted in a file? In addition, does anybody have any good white papers or urls? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: CA's AutoSys
We purchased Autosys when it was owned by Platinum and continue to use it today for all of our production jobs. The server and clients are running on AIX 4.3.3. and we have had no problems with the product. @fatcity.com on 10/18/2002 08:19 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Run Screaming in the opposite direction. Product was a semi-disaster at two previous job sites. Failed to run jobs when scheduled, failed to detected when dependent jobs failed. Just plain didn't work well. Of course this was when Platinum owned the product, who knows, maybe CA improved it. (although I would doubt it). Matt Adams - GE Appliances - [EMAIL PROTECTED] Write a poem about a haircut! But lofty, noble, tragic, full of love, treachery, retribution, quiet heroism in the face of certain doom! Six lines, cleverly rhymed, and every word beginning with the letter s! -Original Message- Sent: Friday, October 18, 2002 11:50 AM To: Multiple recipients of list ORACLE-L To All, I'll admit this is somewhat off-topic, but since the project is going to handle all scheduled batch jobs that bang on our PeopleSoft Oracle database, I figured I'd ask if anyone has an opinion preferably from experience using the product. The Complete A^^holes are here today to do a demo install so any input you all have would be appreciated. Thanks in advance Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Data Modeling: Good, Bad & Ugly
Our current practice today: a developer asks a DBA for a table and they create it (no modeling is done). I believe that someone within our IT should be doing some logical modeling (& conceptual??) and turning that over to the DBA for the physical modeling. I am looking for some thoughts related to the following as well as any other helpful comments you may have in general: Is the exercise of modeling common practice within your organization? And for all tables (ie temp and work types)? What is your process? Who is responsible for the logical model and physical model? (developer, business analyst, data architect, dba) What model is used for modeling data? (orm, er, uml) And what notation? (IE, Barker, ...) What case tool? Are there any good papers out there comparing the pros/cons of available tools? Anybody have any experience with universal data models? Specifically the use of Len Silvestons Finally, has anyone used the cbt product called infostructor ( http://www.agpw.com) for training and what are your thoughts on it? I really appreciate any info that you can provide. Since the existing process is so easy (from the developers point of view), you can imagine the headache of trying to implement a little more structure in the design. I believe that since we are relatively small today, we can get away with poor planning, but someday as we grow it will come back to haunt us. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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! Scrolling within VB app
I have a developer that would like to display 'x' amount of records on a vb screen. Additional, buttons would be added to the screen for paging forward or paging backwards. What options do we have for doing this? rowid, rownum Thanks alot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Online vs offline backups
If you create a new database and do a full online backup (this also includes future regular full online backups) is it ever necessary to do a full offline backup? I have heard conflicting answers, regarding this and am looking for some clarification. Don't 24x7 applications always have to execute offline backups? Are offline backups done for peace of mind, and if yes why? What issues are introduced if only offline backups are issued. In addition, we are using BMCs Sql BackTrack product and I am not sure if it is a requirement of the tool that an offline backup be periodically performed. If you know that as well, please share. Thanks everybody -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
3-tier architecture
We're looking to implement a 3-tier architecture with websphere, java and oracle. My question is where should the code be deployed for oltp apps and batch processes? Are the business rules(components) deployed on the middle tier and that same logic (if applicable within batch) deployed as java within the database? It would appear to me that batch would execute faster if the source resides in the database rather than the app server, but that we are gaining a maintenance(deployement) issue for the performance gain. Should the business rules be deployed within java and the data access be written in pl/sql. Any thoughts?? What are others doing today and are there any papers available regarding these issues? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Infostructor Pro
Anybody every use this product? Good, bad, Located at www.agpw.com. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Prod problem, please help!!! Any resolution?
Hi, We have applied an AIX patch to our system, based upon Oracle's recommendation (APAR IY22308). We are currently in a "wait and see" mode. My confidence in this being the solution will be increased greatly if we can make it through next Monday without the problem. (Mondays are a high-load day for us and that is when the issue usually rears its ugly head). Thanks for asking Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Hi, Just wondering if you made any progress? Let us know. Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Prod problem, please help!!!
Hi, It has been confirmed that we are not running out of space due to the archiver. To:Tracy Rahmlow@AMEX cc: My initial stab in the dark is that if this DB is in archive mode, that the archiver process could be halting additional transactions due to lack of space in the archive destination. Tracy Rahmlow wrote: > > I have opened a TAR with Oracle and am waiting a resolution. In the meantime, > I am wondering if anybody else has any ideas while I wait. > > Specifics: >IBM AIX 4.3 >Rdms 8.1.7.3 >Database ~75g >OLTP database with approximately 500 dedicated connections and 500 shared connections with Oracle's MTS. > > Problem: > The database hangs, and no user is able to connect to the instance, except locally through srvmgrl. Even within svrmgrl, we are unable to select anything from > the > database without the query hanging. However, we can abort the instance (shutdown abort) and start it up again just fine. This has happened on 4-22, 4-29 & > 4-30 in the > early afternoon. Usually, this is also our peak busy rate for the week. We are executing MTS for 4 applications, all other applications connect through > dedicated server. > The alert log contains a message unable to start a shared server process. This week it was #41 and last week it was #25. Normally, we do not exceed 5 shared > servers. Another thing I noticed is that there is no time allocated to any of the newly created shared servers. It is as if, it can not process any work > through existing shared > servers and decides to allocate another one, until finally it freezes. I am not sure if this is a MTS problem because I would suspect that I should be able > to establish > a dedicated server connection. And I can not. I think that this is just a symptom of the underlying problem. It would appear to me that we are running out > of a resource, > however our sysadms do not see any resource problems. Does anybody have any ideas how to debug this? Thanks > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Am I sure? Of course I'm sure. I could be wrong, but I'm sure for now! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Prod problem, please help!!!
Hi, I am unable to query from the database when it hangs up. I am only able to execute the shutdown abort command. To:[EMAIL PROTECTED] cc:Tracy Rahmlow@AMEX Tracy, You can start by using the query below to determine what the sessions are actually waiting on: select s.username username, e.event event, s.sid, e.p1text, e.p1, e.p2text, e.p2, e.wait_time, e.seconds_in_wait, e.state from v$session s, v$session_wait e where s.username is not null and s.sid = e.sid -- skip sqlnet idle session messages and e.event not like '%message%client' order by s.username, upper(e.event); Jared "Tracy Rahmlow" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 04/30/2002 12:21 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Prod problem, please help!!! I have opened a TAR with Oracle and am waiting a resolution. In the meantime, I am wondering if anybody else has any ideas while I wait. Specifics: IBM AIX 4.3 Rdms 8.1.7.3 Database ~75g OLTP database with approximately 500 dedicated connections and 500 shared connections with Oracle's MTS. Problem: The database hangs, and no user is able to connect to the instance, except locally through srvmgrl. Even within svrmgrl, we are unable to select anything from the database without the query hanging. However, we can abort the instance (shutdown abort) and start it up again just fine. This has happened on 4-22, 4-29 & 4-30 in the early afternoon. Usually, this is also our peak busy rate for the week. We are executing MTS for 4 applications, all other applications connect through dedicated server. The alert log contains a message unable to start a shared server process. This week it was #41 and last week it was #25. Normally, we do not exceed 5 shared servers. Another thing I noticed is that there is no time allocated to any of the newly created shared servers. It is as if, it can not process any work through existing shared servers and decides to allocate another one, until finally it freezes. I am not sure if this is a MTS problem because I would suspect that I should be able to establish a dedicated server connection. And I can not. I think that this is just a symptom of the underlying problem. It would appear to me that we are running out of a resource, however our sysadms do not see any resource problems. Does anybody have any ideas how to debug this? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Tracy Rahmlow 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).
Prod problem, please help!!!
I have opened a TAR with Oracle and am waiting a resolution. In the meantime, I am wondering if anybody else has any ideas while I wait. Specifics: IBM AIX 4.3 Rdms 8.1.7.3 Database ~75g OLTP database with approximately 500 dedicated connections and 500 shared connections with Oracle's MTS. Problem: The database hangs, and no user is able to connect to the instance, except locally through srvmgrl. Even within svrmgrl, we are unable to select anything from the database without the query hanging. However, we can abort the instance (shutdown abort) and start it up again just fine. This has happened on 4-22, 4-29 & 4-30 in the early afternoon. Usually, this is also our peak busy rate for the week. We are executing MTS for 4 applications, all other applications connect through dedicated server. The alert log contains a message unable to start a shared server process. This week it was #41 and last week it was #25. Normally, we do not exceed 5 shared servers. Another thing I noticed is that there is no time allocated to any of the newly created shared servers. It is as if, it can not process any work through existing shared servers and decides to allocate another one, until finally it freezes. I am not sure if this is a MTS problem because I would suspect that I should be able to establish a dedicated server connection. And I can not. I think that this is just a symptom of the underlying problem. It would appear to me that we are running out of a resource, however our sysadms do not see any resource problems. Does anybody have any ideas how to debug this? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Pinhitratio vs gethitratio
Can someone explain why a pinhitratio would be higher than a gethitratio in the v$librarycache for the namespace = 'SQL AREA'? I would think it would be the opposite. Our current gethitratio is 87.98 and the pinhitratio is 95.10. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Poll & Questions
Clarification: My initial question was not really asking how to do it. But more it was trying to find out what other shops do. In addition, since the production database is large what benefits/costs can I present to justify cloning a fullsized database to an acceptance database? For example, I think it is far easier to copy the entire database rather than extract some subset of it for creating a sizable acceptance database. Also, I think we would obtain more accurate timings for queries in an exact copy rather than a subset of data. What else? 03/13/2002 10:38 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: We do a similar refresh (as mentioned by Kirti) daily using hotbackup and archivelogs and it is named DAYOLD. Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, March 13, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Kirti, Why not using "hot" backup + archived log files ? Just wondering, if there is any specific reason. Igor Neyman, OCP DBA [EMAIL PROTECTED] (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
Poll & Questions
We currently have a production, system and development database here. The system and development databases are purged periodically and reloaded with lookup data. The developers are then responsible for entering transactional data in both regions. I am looking to follow the same practice for development, however I would like to clone my production database directly to the system test database. The production database is ~75G. Management does not want to commit $ to a full sized system database. Costs outweigh the benefits. I would like to sway them. HOW? Please give me your costs/benefits of doing this. In addition, what is the norm (if there can be one) in other shops. Does utopia exist? ps. One of the biggest reasons for this database would be for benchmarking, timings, stress-testing. I realize I can copy the production stats, but that won't give me a good execution time. Do others load a subset of data (say 25%) and then extrapolate to a total time? Is that even necessarily accurate to do? I have my doubts. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
MTS on 8.1.7.5
Anybody using multi-threaded server on 8.1.7? I tried it in the past with poor results on 7.3.4 and thought about giving it another chance (actually we are having memory issues due to the upgrade from 7.3.4 to 8.1.7) Any good websites with detailed information/scripts for monitoring? TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: # of datafiles per tablespace
Using Sql Backtrack for backups, you are able execute the backups of multiple datafiles in parallel. Therefore, it will be faster to backup 4-1g files rather than 1-4g file if you have the necessary hardware in place. 4:58 AM PST Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Hi Kathy, The only thing I can think of for your original question is a bad guess on the file size. They guessed 500 mb, ran out of file space, added a 50 mb file to the Tablespace, ran out again added 50 mb again. never ran out again. Reading other dba's minds is so much fun :-) John [EMAIL PROTECTED] wrote: That being said is there anything wrong with having one 4G data file for a tablespace. I personally cannot think of any. There were the days when 2G was the limit but that sure isn't the case anymore. The only thing I can think of is for backups. However, I am always going to backup on at least the tablespace level so if I have one file or multiple files I still need to get them all. I don't know if RMAN has some special feature that turns out it makes sense to backup just one data file of a tablespace that has multiple data files but I sure can't think of any good reason. I just randomly picked RBS but I am seeing the same case on data tablespaces as well. -Original Message- Carmichael Sent: Monday, March 04, 2002 6:29 PM To: Multiple recipients of list ORACLE-L no reason. I can see creating multiple files under those conditions only because you want to keep files to a specif ic size. Now, I did once find that the rollback datafiles were a bottleneck on a system I had. So we built TWO rollback tablespaces, with datafiles on different mount points etc and the rollback segments divided between the two tablespaces. cleared up that bottleneck like a dream other than that though.. why? --- Kimberly Smith <[EMAIL PROTECTED]> wrote: OK, I know we had the debate already but lets have another go at it. Say you got a tablespace, lets call it RBS and its for rollbacks. Now, for what reason would you create a 500M file and 4 50M files for this puppy as opposed to just one file. I just cannot see the reasoning for this at all. None. Natta. Zilch. So educate me please if someone out there knows a legit reason they would do this. Lets assume for the sake of argument that disk size and mount point size is not a limitation. Space available to me on any one mount point is unlimited. ___ Kimberly Smith Portland, OR [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Dedicated server memory usage 8.1.7 vs 7.3.4
We just recently migrated some production databases from 7.3.4 to 8.1.7.3 on AIX. We have noticed that the amount of memory per dedicated server process spiked from about 400k to 2m. Anybody have any ideas about why this occurs? Is this normal? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: UTL_FILE.INVALID_PATH Exception
We had a very similiar problem here. Not sure what caused it, but by stopping
and restarting the listener, the issue went away. Go figure.
ps. we are also on AIX, with a mix of 8.1.7.2 and 7.3.4
02/09/2002 06:03 PM PST
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:
Hi -
I have a procedure procA owned by userAwhich calls the utl_file
procedure to open and read a text file. The procA procedure
functions properly when it is run in SQL*Plus from the server.
However, when the procA procedure is executed from a client PC using
SQL*Plus (or SQLNavigator), the UTL_FILE.FOPEN call fails with the
INVALID_PATH exception.
The following is the line that fails in procA:
PCARD_FILE := UTL_FILE.FOPEN ('/u/schmitt/out', 'PC020204.TXT',
'R');
The following is the results of select name, value from v$parameter
where name = utl_file_dir:
NAMEVALUE
utl_file_dir /u/schmitt, /u/schmitt/out, /tmp
We are running Oracle 8.1.7 on an IBM RS/6000 with AIX.
The privileges on the /u/schmitt/out
directory are drwxrwxrwx and the PC020204.TXT privileges are set to
-rwxrwxr--.
This same procedure worked just fine when executed from either the
client or the server in an Oracle 8.1.5 instance on this same server
using the same files and the same directory.
Any ideas why this procedure fails when it is executed from the
client PC but not when it is executed from the server? Any ideas
what changed in Oracle 8.1.7 when trying to use the UTL_FILE
package?
Thanks in advance for your help.
Janet.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janet Schmitt 265-3334
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: Tracy Rahmlow
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).
Execution: Remote vs Local
We have several scripts that use sqlplus to execute procedures. The question is what are the pros/cons of connecting locally and executing (ie setting the ORACLE_SID and connecting as /) or remotely (/@sid). The reason I ask is that we also use Oracle reports and have that installed in a different directory than the rdbms binaries. And since you can't connect locally for reports, we are thinking about using the Oracle reports variables and always connecting remotely whether or not it is a report or procedure that is executed. This would save from switching back and forth between variable sets. When all we really want is access to either sqlplus or the reports binaries.Any thoughts are very much appreciated. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
DESIGN Question: 1 or many tables and other thoughts
My question deals with what is the best practice in creating lookup tables. Many of these tables contain very limited information such as "code & description". For example: Agency 1 - Jones Inc 2 - Ratfield Co Source 1 - phone 2 - mail 3 - internet Is it better to create 1 composite table to contain this data or have separate tables for each? If a composite table is the way to go, how far do you take it? Ie, what if the entity has more than just 2 columns worth of data. What is the best way to enforce that valid values are actually stored in the table, since foreign keys can't be used for these composite table types? (For example, if a sales table has a source column, how should you enforce that valid sources are actually stored in the row?) In addition, can anyone recommend either a good website/book/course that has practical/useful design tips? Everywhere I read it states how important design is to the performance of the application, however frankly all I see normally is references to ratios, counts, blah, blah, blah ... Not that they aren't important but I just never see enough information re: design concepts, tips, tricks... (Maybe I am just missing something) While I'm at it, we are also looking to implement design reviews in our shop. First though, we need to set some expectations for presentation to the review board. As a starting point for creating the expecations, does anyone have a checklist of items to consider/address in these discussions? One other thing, while I am thinking about it. I have seen references to first complete the logical design, then the physical. Do most shops actually go through the logical phase(red tape as I'm told) or does reality set in (like it does here) and developers jump right into the physical design? Just wondering if I am alone. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
DESIGN Question: 1 or many tables and other thoughts
My question deals with what is the best practice in creating lookup tables. Many of these tables contain very limited information such as "code & description". For example: Agency 1 - Jones Inc 2 - Ratfield Co Source 1 - phone 2 - mail 3 - internet Is it better to create 1 composite table to contain this data or have separate tables for each? If a composite table is the way to go, how far do you take it? Ie, what if the entity has more than just 2 columns worth of data. What is the best way to enforce that valid values are actually stored in the table, since foreign keys can't be used to these table types? (For example, if a sales table has a source column, how should you enforce that valid sources are actually stored in the row?) In addition, can anyone recommend either a good website/book/course that has practical/useful design tips? Everywhere I read it states how important design is to the performance of the application, however frankly all I see normally is references to ratios, counts, blah, blah, blah ... Not that they aren't important but I just never see enough information re: design concepts, tips, tricks... (Maybe I am just missing something) While I'm at it, we are also looking to implement design reviews in our shop. First though, we need to set some expectations for presentation to the review board. As a starting point for creating the expecations, does anyone have a checklist of items to consider/address in these discussions? One other thing, while I am thinking about it. I have seen references to first complete the logical design, then the physical. Do most shops actually go through the logical phase(red tape as I'm told) or does reality set in (like it does here) and developers jump right into the physical design? Just wondering if I am alone Have a great weekend -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
SQL BackTrack 3.3
We are currently using the noincremental option within Sql BackTrack and considering the enabling of it. Has anybody gone through the process? Were there any issues and did you notice reduced space usage as well as faster backup times? Any reasons why you would not enable the incremental backup option? (I realize it does matter how many blocks are updated) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Architecture Options
We are currently looking to create some new systems within our shop and struggling to choose what the architecture should look. Everything from 2-tier/n-tier, choice of middleware(whatever that is,since there appears to be many definitions), presentation(browser based vs windows gui), app servers, web servers, languages to use(vb, java, c++ ...) and when (eg write batch in pl/sql procedures w/i the db or separate components that reside outside of the database) yada yada yada. Does anybody know of any sites where we can go to obtain more information(points to consider, pros/cons) of particular tools/languages/models to aid us in making our decisions. Or am I living in a dreamworld? In other words, if there were no barriers(cost, training...) how would you design the archicture to support an oltp application of approximately 500 users, yet provide flexibility, scaleability and all those buzzwords. In addition, what would the architecture look like for integrating a datawarehouse with an oltp environment? Oh, by the way we have decided to continue to use Oracle, that is not on the table. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Lookup table design thoughts needed
We are currently looking at rewriting our entry system and one issue that I am looking for some feedback involves the use of lookup tables and populating/editing screens. We are looking at creating a generic table that contains all the valid entries for each drop-down list. For example, we may display a list of valid states for the user to select. The proposed "edit" table contains a row for each state with the following columns as an example: table_name: address column_name: state_cd code: WI description: Wisconsin In addition, we have situations on the screen where a user may select option 'a' in a drop-down list, but can not choose option 'c,d or f' in a different drop down list. Any suggestions for designing a flexible system that would incorporate issues like the above. We have been considering either "hard-coding" the edits within the screen as well as creating a "rules/validation" table that would incorporate these edits. How practical is a rules table? (We do have situations where we may have multiple entries to validate to each other). I realize these are very broad questions, so I am looking for generic theories that may be applied that are flexible for adapting to changes within the business. What else should I consider? It appears as if there are several ways to skin the cat how do we go about choosing the best method for our situation. In addition, does anybody know of any good websites/books that contain relational design strategies, tips ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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 NEEDED: Datafile header info
Background: We have 2 databases (7.3.4.5) on aix 4.3.3. When executing the strings command (strings system.dbf | pg) on one of the database's datafiles it returns the database and the oracle_home location. For example: prdr:/optc/oracle/rdbms/7.3.4 The other database does not display this information. What controls whether or not this is written to the header? This came up as an issue because we currently use Sql BackTrack and during a restore process(logical extraction) it restores needed datafiles and attempts to create a temporary database so that it can extract out data. In the process it fails to create the control files because I believe it is using this information in the datafile to locate the sqadef.dbf file within the oracle_home/dbs directory to verify whether or not the instance is currently running. One database works because it can not find the sgadef file and it also does not have the oracle_home info in the header. The other database fails with an ora-09782 sfifi: another instance has the same database mounted. (this database has the oracle_home in the header). Anyone run across this issue before? Does this sound correct? And what controls whether or not the info is written to the header? Why do we have mixed results? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Datafile header info needed
Background: We have 2 databases (7.3.4.5) on aix 4.3.3. When executing the strings command (strings system.dbf | pg) on a datafile one returns the database and the oracle_home location. For example: prdr:/optc/oracle/rdbms/7.3.4 The other database does not display this information. What controls whether or not this is written to the header? This came up as an issue because we currently use Sql BackTrack and during a restore process(logical extraction) it restores needed datafiles and attempts to create a temporary database so that it can extract out data. In the process it fails to create the control files because I believe it is using this information in the datafile to locate the sqadef.dbf file within the oracle_home/dbs directory to verify whether or not the instance is currently running. One database works because it can not find the sgadef file and it also does not have the oracle_home info in the header. The other database fails with an ora-09782 sfifi: another instance has the same database mounted. (this database has the oracle_home in the header). Anyone run across this issue before? Does this sound correct? And what controls whether or not the info is written to the header? Why do we have mixed results? Why, why, why? Thanks and at least its friday. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Looking for 24 X 7 Design Considerations
We currently have 15+ databases (7.3.4 & 8i using IBM AIX and HACMP) that do not have a 24x7 restriction. Now, management is looking to bring in new products that will need to be 24X7. They are looking for costs to determine the viability of such a decision. I have no 24x7 experience and am looking for ideas or options to consider. [At least initially they are stating there can be no downtime for maintenance (upgrades/reorgs)] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Fast Refresh of Snaphots Locking?
When we do a fast refresh of a snapshot and the number of entries in the log are high (750,000+) our users complain about poor performance for approximately a 5 - 10 minute period. (This occurs in the database where the master resides) As of yet I have not been notified in a timely manner to investigate as it is occuring. I thought there was no locking on the master site, so my initial reaction is that this is not the issue. It is also strange that it is a short period of time. I believe much shorter than the time it takes to refresh the snapshot. Also, only appears to be an issue with a large (in our shop) number of transactions in the snapshot log. No complaints any other time. Has anybody seen any threshold limit for refreshing incrementally (fast)? Is there locking occuring on dictionary tables which is extended in length due to the number of transactions? Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Snapshot Refresh Process ??
At times it is faster to do a complete refresh of an oracle snapshot rather than an incremental (fast). My question is how do you know when to do the complete? For example, there are times when a user will run a conversion and they may update 10% of the rows in the master table. If I do a fast refresh it may take 4 hours but if I did a complete it takes 2 hours. I have not seen a pattern in determing the breakeven point. For example, one table we may update 20% of the rows and the incremental is faster other times we update 5% of the rows in a different table and the complete would have been an better option. (Don't quote me on the percentages, just trying to make a point) I remember seeing a reference to this issue that it was addressed at ioug conference years ago, but I did not find anything there. So again the question: What factor(s) should be considered to determine which process(fast or complete) will provide for a faster refresh for higher than normal updates to a master table? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Naming Standards - Thoughts Needed
Were looking to develop naming standards within our organization and I am wondering what others use. Is there a formal process similiar to ofa? Do shops typically use underscores or case? (policy_number / PolicyNumber) What about abbreviating? Enforcement processes ? Other considerations? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Design Question - Thoughts Needed
Please see prior post. The formatting seems to be hosed up. -- Forwarded by Tracy Rahmlow on 06/25/2001 03:15 PM --- Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: I think I may have confused some, regarding my previous post. The coverage and amount is specific to the vehicle. It is not a set amount. For example: Method #1 Vehicle/Coverage Table Seq Year Type Annual_Miles Comp_Ded Comp_Prem Towing_Ded Towing_PremPip_Ded Pip_Limit Pip_Prem 197 Jeep 1000 500 25.25 5010"NULL" "NULL" "NULL" 299 Truck 7000 1000 20.50 "NULL" "NULL" "NULL" "NULL" "NULL" 3 98 Car 5000 1000 15.00 0 30 250 10 35 Method #2 Vehicle Table Seq Year Type Annual_Miles 197 Jeep 1000 299 Truck 7000 398 Car 5000 Coverage Table Seq Covg_Id Covg_Ded Covg_Limit Covg_Prem 1A(Comp) 500 "NULL" 25.25 1B(Towing) 50"NULL" 10 2A 1000 "NULL" 20.50 3A 1000 "NULL" 15 3B 0 "NULL" 30 3C(Pip) 250 10 35 The 1000 comp deductible does not equate to the same premium for all vehicles. We also have one coverage where 3 columns are necessary to capture the covg info. There is a deductible, limit and premium associated with it. So do we stick another column in the coverage table to capture for only this coverage? The saga continues. Now I understand when they say modeling is an art and not a science. I was much better in science. (Thus my struggles) -- Forwarded by Tracy Rahmlow on 06/25/2001 01:52 PM --- 06/25/2001 10:18 AM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, I would fight tooth and nail for a separate auto_coverage table that is a child to each parent auto record. (It actually represents a many-to-many relationship between the auto table and the available_coverage table which lists all coverages available). When you argue about performance problems during reporting, it pales in comparison to performance problems if, say, the coverage name changes, or if you want to know "Who/how many people have just towing". In my example, you query the child table for the coverage code that = towing. In your example, you query all parent records. This is the real reason why normalization is required in relational design. Break everything down into it's smallest parts so you can query and update just the atomic item you need. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, June 25, 2001 1:16 PM To: Multiple recipients of list ORACLE-L We are looking to redesign our current system and a question popped up that I am struggling with. (our business is auto insurance). Each vehicle on a policy may have 1 or many coverages.For example, comprehensive, collision, towing, property damage ... A couple of things: 1- some coverages will not apply because they are state specific. 2 - some coverages will not be chosen by the insured. Therefore, should I have a vehicle table with each coverage and its premium as separate specific columns or should I add a coverage table which will contain the coverage and premium. Under the later example if I have 10 coverages and I want to print them on the insured's declaration page don't I have a possible performance problem with up to 10 i/os. Where as with the first example I only have 1 read but possible space wastage. I know in 1st normal form you should remove repeating groups, in my case is a coverage a repeating group? Where do you draw the line? For example, clients may have multiple phone numbers although I don't see many examples where the phone numbers are split into another table. Please share any thoughts that may make my decision easier. Thanks Just an fyi, in our current design the coverages have been stored with the vehicle. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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')
Design Question - Additional Info
I think I may have confused some, regarding my previous post. The coverage and amount is specific to the vehicle. It is not a set amount. For example: Method #1 Vehicle/Coverage Table Seq Year Type Annual_Miles Comp_Ded Comp_Prem Towing_Ded Towing_PremPip_Ded Pip_Limit Pip_Prem 197 Jeep 1000 500 25.25 5010"NULL" "NULL" "NULL" 299 Truck 7000 1000 20.50 "NULL" "NULL" "NULL" "NULL" "NULL" 3 98 Car 5000 1000 15.00 0 30 250 10 35 Method #2 Vehicle Table Seq Year Type Annual_Miles 197 Jeep 1000 299 Truck 7000 398 Car 5000 Coverage Table Seq Covg_Id Covg_Ded Covg_Limit Covg_Prem 1A(Comp) 500 "NULL" 25.25 1B(Towing) 50"NULL" 10 2A 1000 "NULL" 20.50 3A 1000 "NULL" 15 3B 0 "NULL" 30 3C(Pip) 250 10 35 The 1000 comp deductible does not equate to the same premium for all vehicles. We also have one coverage where 3 columns are necessary to capture the covg info. There is a deductible, limit and premium associated with it. So do we stick another column in the coverage table to capture for only this coverage? The saga continues. Now I understand when they say modeling is an art and not a science. I was much better in science. (Thus my struggles) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Design Question - Thoughts Needed
I think I may have confused some, regarding my previous post. The coverage and amount is specific to the vehicle. It is not a set amount. For example: Method #1 Vehicle/Coverage Table Seq Year Type Annual_Miles Comp_Ded Comp_Prem Towing_Ded Towing_PremPip_Ded Pip_Limit Pip_Prem 197 Jeep 1000 500 25.25 5010"NULL" "NULL" "NULL" 299 Truck 7000 1000 20.50 "NULL" "NULL" "NULL" "NULL" "NULL" 3 98 Car 5000 1000 15.00 0 30 250 10 35 Method #2 Vehicle Table Seq Year Type Annual_Miles 197 Jeep 1000 299 Truck 7000 398 Car 5000 Coverage Table Seq Covg_Id Covg_Ded Covg_Limit Covg_Prem 1A(Comp) 500 "NULL" 25.25 1B(Towing) 50"NULL" 10 2A 1000 "NULL" 20.50 3A 1000 "NULL" 15 3B 0 "NULL" 30 3C(Pip) 250 10 35 The 1000 comp deductible does not equate to the same premium for all vehicles. We also have one coverage where 3 columns are necessary to capture the covg info. There is a deductible, limit and premium associated with it. So do we stick another column in the coverage table to capture for only this coverage? The saga continues. Now I understand when they say modeling is an art and not a science. I was much better in science. (Thus my struggles) -- Forwarded by Tracy Rahmlow on 06/25/2001 01:52 PM --- 06/25/2001 10:18 AM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, I would fight tooth and nail for a separate auto_coverage table that is a child to each parent auto record. (It actually represents a many-to-many relationship between the auto table and the available_coverage table which lists all coverages available). When you argue about performance problems during reporting, it pales in comparison to performance problems if, say, the coverage name changes, or if you want to know "Who/how many people have just towing". In my example, you query the child table for the coverage code that = towing. In your example, you query all parent records. This is the real reason why normalization is required in relational design. Break everything down into it's smallest parts so you can query and update just the atomic item you need. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, June 25, 2001 1:16 PM To: Multiple recipients of list ORACLE-L We are looking to redesign our current system and a question popped up that I am struggling with. (our business is auto insurance). Each vehicle on a policy may have 1 or many coverages.For example, comprehensive, collision, towing, property damage ... A couple of things: 1- some coverages will not apply because they are state specific. 2 - some coverages will not be chosen by the insured. Therefore, should I have a vehicle table with each coverage and its premium as separate specific columns or should I add a coverage table which will contain the coverage and premium. Under the later example if I have 10 coverages and I want to print them on the insured's declaration page don't I have a possible performance problem with up to 10 i/os. Where as with the first example I only have 1 read but possible space wastage. I know in 1st normal form you should remove repeating groups, in my case is a coverage a repeating group? Where do you draw the line? For example, clients may have multiple phone numbers although I don't see many examples where the phone numbers are split into another table. Please share any thoughts that may make my decision easier. Thanks Just an fyi, in our current design the coverages have been stored with the vehicle. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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:
Design Question - Thoughts Needed
We are looking to redesign our current system and a question popped up that I am struggling with. (our business is auto insurance). Each vehicle on a policy may have 1 or many coverages.For example, comprehensive, collision, towing, property damage ... A couple of things: 1- some coverages will not apply because they are state specific. 2 - some coverages will not be chosen by the insured. Therefore, should I have a vehicle table with each coverage and its premium as separate specific columns or should I add a coverage table which will contain the coverage and premium. Under the later example if I have 10 coverages and I want to print them on the insured's declaration page don't I have a possible performance problem with up to 10 i/os. Where as with the first example I only have 1 read but possible space wastage. I know in 1st normal form you should remove repeating groups, in my case is a coverage a repeating group? Where do you draw the line? For example, clients may have multiple phone numbers although I don't see many examples where the phone numbers are split into another table. Please share any thoughts that may make my decision easier. Thanks Just an fyi, in our current design the coverages have been stored with the vehicle. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Kismeta Validator
Is anybody using this product for the enforcement of naming standards? I have tried to contact the company for info with no response. Not sure if they are still in business or not. Or does anybody have the trial key? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Snapshot Logs Explanation Needed
If you do a snapshot refresh then the snapshot log should be empty(correct?). Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is complete. Thus eliminating the need to do a complete refresh of the snapshot. -- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM --- PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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: Tracy Rahmlow 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: Snapshot Logs Explanation Needed
Hi Anita, Ironically that is just what I am trying to do. (ie implement a storage plan which will address the issues discussed in the paper) To your other point, I understand oracle's position, but if I can prohibit changes to the master during the reorg/rebuild, is this still an issue? -- Forwarded by Tracy Rahmlow on 05/31/2001 12:37 PM --- Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, You may know that there were no modifications to the master table made from the time the snapshot log was dropped until it was recreated, but Oracle can't take that chance, otherwise data could get out of sync. That's why you have to either recreate your snapshot or do a complete refresh if the snapshot log is recreated. To avoid recreating the snapshots I would ask why you need to reorg your master tables (i.e. what do you hope to accomplish by this)? If you're doing it for defragmentation reasons, I suggest you check out the excellent white paper "How to stop defragmenting and start living..." to see if this is really necessary. http://www.vampired.net/articles/files/stopfrag.zip HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > I would like to reorg many tables which have > snapshots associated with them. I > understand that if the master table is dropped the > snapshot log is also > dropped. When a log is dropped, oracle states that > you need to do a complete > refresh of the affected snapshot. My question is > why? If you do not allow > users to access the database with the master table, > then you should not have > any transactions that would be lost. Why can't you > create a new log and > continue to do a fast refresh? I am trying to > avoid having to recreate all > the snapshots due to the size and number that we > have. Any thoughts? Thanks > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: Tracy Rahmlow 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: Snapshot Logs Explanation Needed
If you do a snapshot refresh then the snapshot log should be empty(correct?). Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is complete. Thus eliminating the need to do a complete refresh of the snapshot. -- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM --- PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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: Tracy Rahmlow 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: Snapshot Logs Explanation Needed
Hi Anita, Ironically that is just what I am trying to do. (ie implement a storage plan which will address the issues discussed in the paper) To your other point, I understand oracle's position, but if I can prohibit changes to the master during the reorg/rebuild, is this still an issue? -- Forwarded by Tracy Rahmlow on 05/31/2001 12:37 PM --- Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, You may know that there were no modifications to the master table made from the time the snapshot log was dropped until it was recreated, but Oracle can't take that chance, otherwise data could get out of sync. That's why you have to either recreate your snapshot or do a complete refresh if the snapshot log is recreated. To avoid recreating the snapshots I would ask why you need to reorg your master tables (i.e. what do you hope to accomplish by this)? If you're doing it for defragmentation reasons, I suggest you check out the excellent white paper "How to stop defragmenting and start living..." to see if this is really necessary. http://www.vampired.net/articles/files/stopfrag.zip HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > I would like to reorg many tables which have > snapshots associated with them. I > understand that if the master table is dropped the > snapshot log is also > dropped. When a log is dropped, oracle states that > you need to do a complete > refresh of the affected snapshot. My question is > why? If you do not allow > users to access the database with the master table, > then you should not have > any transactions that would be lost. Why can't you > create a new log and > continue to do a fast refresh? I am trying to > avoid having to recreate all > the snapshots due to the size and number that we > have. Any thoughts? Thanks > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: Tracy Rahmlow 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).
Database Links
We have several large "look-up" tables that we use in development as well as in production environments. The data is the same in both environments. I am looking for some comments regarding whether or not we store duplicate data in each environment or should we allow the development users to access the table in production through a database link. Below, I have listed some issues with both of these processes and am looking for further input. Thanks Duplicate table in production and development (either through export/import or snapshots): Cons additional storage is need process needed to keep tables in sync Pros reduced network traffic Access table in production through a database link in development: Cons additional network traffic possibility of poorly tuned adhoc sql executing in a production environment Pros only one copy of table do not need an ongoing process to keep the tables in sync -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
Snapshot Logs Explanation Needed
I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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).
