ORA-1575
Hi All, Oracle 8.0.5 AIX 4.3.3 We have a database that showed an ORA-1575 every minute for 30 minutes this morning. This was just after startup when a whole bunch of batch processes kick in as well Documentation just says retry the operation (smon tried every minute and eventually succeeded) * ORA-01575 timeout waiting for space management resource Cause: Failed to acquire necessary resource to do space management. Action: Retry the operation. ** My feeling is that I can safely dismiss the idea that this is a serious matter, but would like to run this one by you guys as I have not had this before. TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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: Transferring data from one table to another
Thank you all SQL*Plus COPY command seems most suitable, I will try it out today. Aleem -Original Message- Sent: Monday, August 12, 2002 7:33 PM To: Multiple recipients of list ORACLE-L Subject:Re: Transferring data from one table to another Did you check out the SQL*Plus COPY command? Specifically in conjunction with SET ARRAYSIZE and SET COPYCOMMIT settings... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, August 12, 2002 1:23 AM > Thank you, Amjad, > The problem is that then I have to write a procedure for each of the tables. > I was looking for something that could be set at database level and would > apply to every table. > > Aleem > > -Original Message- > Sent: Monday, August 12, 2002 10:43 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Transferring data from one table to another > > well if u wanna commit after 1000 records u could very well use a cursor > and within the loop keep a counter which will indicate the no. of records > inserted...upon reaching 1000 records just commit and reinitialize the > counter.. > > i have written the "Pseudo" code below: > > declare > cursor c1 is > SELECT * from schema2.abc; > cntr number := 0; > begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000) then > cntr := 0; > commit; > end if; > end loop; > /* the following commit is 4 last set of records that might not b commited*/ > commit; > end; > > rgds, > Ams. > www.medicomsoft.com > > > > -Original Message- > Sent: Monday, August 12, 2002 8:23 AM > To: Multiple recipients of list ORACLE-L > > > Hi, > > We are transferring data from one table in a schema to another table in > another schema with identical fields using > INSERT INTO schema1.abc (SELECT * from schema2.abc) > The source table has 1.6 million records. The tablespace increases to > consume full disk space and yet seems to be demanding more so the operation > doesn't complete. > > Is there a possibility to process commit after every 1,000 records? > Is there any other way of doing it? > > TIA! > > Aleem > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Abdul Aleem > 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: Amjad Saiyed > 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: Abdul Aleem > 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: Tim Gorman 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: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Se
Re: Unix Solaris forum.
try www.sun-managers.org -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom "The People, The Experience, The Vision" = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. "Marul Mehta" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 13-08-2002 01:03 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Fax to: Subject:Re: Unix Solaris forum. I looked for www.sunmangers.org but it doesnt work? Can you please re-check the reference. Thanks. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, August 11, 2002 9:18 PM > www.sunmangers.org will do it for you > > Cheers > > > -- > = > Peter McLarty E-mail: [EMAIL PROTECTED] > Technical ConsultantWWW: http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 3461 > Brisbane, AustraliaMobile: +61 (0)402 094 238 > Facsimile: +61 (0)7 3303 3048 > = > A great pleasure in life is doing what people say you cannot do. > > - Walter Bagehot (1826-1877 British Economist) > = > Mincom "The People, The Experience, The Vision" > > = > > This transmission is for the intended addressee only and is confidential > information. If you have received this transmission in error, please > delete it and notify the sender. The contents of this e-mail are the > opinion of the writer only and are not endorsed by the Mincom Group of > companies unless expressly stated otherwise. > > > > > > > "Chuan Zhang" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 12-08-2002 01:03 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Fax to: > Subject:Unix solaris forum. > > > > > Hi, DBAs, > > Could anyone recommend a good unix solaris discussion/news group for me? > > > Thanks, > > Chuan > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle vs. DB2
Mike, Here is pro-IBM :) report: http://www-3.ibm.com/software/data/pubs/papers/orac91vsdb272/orac91vsdb272.p df Alexandre > Hi Everyone! > > Well, there's been a lot of Oracle vs. Microsoft traffic on the > list, but now my Manglement wants a similar comparison to IBM's > DB2. > > Does anyone know of web sites or locations where there are > documented objective comparisons between Oracle and DB2? I'm > faced with answering buzzwords like 'Future Market Position', > 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and > 'Platform Compatibility'. > > Any references are appreciated. > > Thanks, > Mike > > --- > === > Michael P. Vergara > Oracle DBA > Guidant Corporation > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Vergara, Michael (TEM) > 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: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-12571 error while EXPORT
Guys, i am on 816/win2k. while exporting a table with 6.5 lac records,i found the error below in my TRC file.The EXP process did not end. it was going on and on ksedmp: internal or fatal error ORA-12571: TNS:packet writer failure why is it so ? what causes this problem and how do i resolve this ? TIA. __ Give your Company an email address like ravi @ ravi-exports.com. Sign up for Rediffmail Pro today! Know more. http://www.rediffmailpro.com/signup/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora 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).
SAN
All I have a meeting tomorrow where I am going to point out why SAN and Oracle does not go very well together. Here are my thoughts. Can you pick holes in this argument, modify it or suggest any changes TIA Babu SAN and Oracle ? Conflicting IO behavior * There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files * Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. * SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) * SANs do *Read Ahead*. If one block is requested, they read more than one blocks *while at the disk* hoping that the same process will request the other blocks some time soon. Here is the conflict. * When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. * Why *read ahead* will cause a conflict : * The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+-+-+-| | | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | | 3.4 | | | | | | | | | | | | | |-+-+-+-+-+-+-+-+-+-| * The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) * Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. * The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never *read ahead* intelligently. Why the buffer of SAN has very little impact w.r.t Oracle read performance? * Oracle has its own buffering for all IO types * DBWR reads and writes uses the DB Buffer Cache * LGWR uses the Log buffer * Db buffer Cache is managed by a LRU Algorithm (Touchcount from 9I). * Bulk of the IO done by Oracle is Logical IO (LIO) and not Physical IO (PIO). * Assume the buffer cache hit ratio is 80%. This means that only 20% of the IO calls are PIO. Only 20% of the calls ever hit the SAN's cache. Since this 20% is probably the least requested/never requested data (going by Oracle's LRU algorithm) , its quite likely that the SAN's buffers don't have this either. * Given that Oracle is going to cache even this 20% in its buffers, the next PIO call is going to be for something totally different ? which is not there in the SAN's buffer. * Couple this with the read-ahead (discussed earlier), Our SAN's buffer is now populated with lots of data that Oracle might never use a PIO to retrieve. * Thus the SAN's buffer can never really provide to Oracle the data it reads most ? Its already there in Oracle. To be fair, SAN's huge buffers will come as a boon to small databases ? where the entire database can be cached in the SAN's buffers. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Explain Plan Question
No, it isn't correct. Explain plan gives you precisely what it should, and that is the access plan. Here is an excerpt from the documentation: The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan. It shows the following information: * An ordering of the tables referenced by the statement * An access method for each table mentioned in the statement * A join method for tables affected by join operations in the statement * Data operations like filter, sort, or aggregation In addition to the row source tree, the plan table contains information about the following: * Optimization, such as the cost and cardinality of each operation * Partitioning, such as the set of accessed partitions * Parallel execution, such as the distribution method of join inputs *** So, running the explain plan takes into account nothing. It displays the chosen access path. If you're not satisfied, you can always use subtle hints. Oracle optimizer takes everything into account and always gives you the ideal execution plan (or at least, it will, starting with the version 99i). On 2002.08.12 17:13 Jay Wade wrote: > Hello: > I have to looking through Metalink but am unable to find the following. > > Does running Explain Plan take into Account the Cost Generated By using > PL/SQL Functions? > > For example Select * from EMP where EMPID=FUNCTION_GET_ID; > > I do not believe that it does since the Function SQL does not seem to be > included in the Plan Output. > > Is this correct? > > Regards, > Jay > > _ > Send and receive Hotmail on your mobile device: http://mobile.msn.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Wade > 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). > -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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).
EMC and AIX resources
I'll be starting a new job next month and consequently starting at the beginning of a learning curve for some products. Just wondering if anyone has any good resources or comments for what to watch for with AIX (most of my experience so far has been with Sun Solaris. I do know about http://bhami.com/rosetta.html ), and EMC Symmetrix. I know I've seen a bunch of comments in the past of DBA vs SA preferences with EMC, but I haven't saved them (time to hit the archives). Thanks. Henry
Use Raw partition or LVM in rac installation?
hi, I am trying to setup a RAC database with two DELL 6650 and a disk array, with two eth card, one for public network and the other for heart beating message and sga data transfer. I have two questions: 1. For oracle ceritified combinations, there should be 2 gigabytes network card and one fiber channel switch , which is used to transfer sga data between instances. In my case, i do not have more than 2 nodes, so i directly interconnect the two nodes with general network line, is it ok to let it work with the cache fusion? 2. I am using redhat advanced server, and i am not familier with this product. In my default installation, there is no LVM manager. I read several document talking about install RAC on linux, all using LVM manager. So, shall i use logical volumn manager, or directly use raw partitions? Thanks for your advice. Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chaos 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: LMT and what is the Bitmap Header Size?
Rajesh.Rao£¬ hi, you can dump the file header and look at its contents, it can be easily found out. 2002-08-08 10:36:00 You wrote: >I have been struggling to find the right answer to the question: What is >the bitmap header size for a uniform extents LMT? 64K, 1 block, 2 >blocks > >I find one note on Metalink (Note: 111666.1) that says its 64K . > >Mr.Jonathan Lewis says " A quirky little detail about bitmap sizes also >came up recently on the Oracle-L mailing list. If you define a very small >tablespace - in this context 'very small' means something between 5 blocks >and '64K plus one extent' then Oracle will give you a bitmap of just one >block - which still allows you to grow the file quite comfortably, of >course." > >Then another example on Metalink (Note: 109630.1) which goes: > > SQL> create >tablespace mult > 2 datafile > >'/oracle2/OFA_base/u02/oradata/V816/mult1.dbf' > size 100k, > 3 > >'/oracle2/OFA_base/u02/oradata/V816/mult2.dbf' > size 100k > 4 extent >management local uniform size 50K; > > Tablespace created. > > SQL> select > >tablespace_name,file_id,block_id,blocks > 2 from >dba_free_space > 3 where >tablespace_name='MULT' ; > > TABLESPACE_NAME > FILE_ID > BLOCK_ID BLOCKS > >-- -- > -- >-- > MULT >15 > 4 25 > MULT >16 > 4 25 > > BLOCK_ID=4 : In an >ordinary datafile, the > first block where >to store data is >block >2. Therefore, two more > blocks are >reserved for the header > >bitmap of the locally managed > datafile. > BLOCKS=25 : The >datafile has a size of 100K > which equals 50 >blocks in this > >database => >There >are only 25 blocks (local > uniform size >50K)left for data > >starting at block n°4, then 22 > blocks are unused. > > => Header bitmap = > 2 blocks for each datafile > > > > >Moi Confused. Whats the definite word on this? Should be something like if >the datafile size is x, then its n, else its some other number. > >Thanks >Raj
T3's, NetApps, Tuning, Wife's Opinion and other fun
Hi All, Well after 6 wekks of testing here is the basic way to operate SUN T3's as efficiently as possible. Be prepared for arguments with High priests from the cult of SAME. SUN T3's are fiber attached hardware RAID 5 arrays with a "modern cache". The hardware engineers argue that if you need more I/Os/sec just add another array as a "concatenated volume". The theory being the hardware is intelligent enough to use the cache to increase throughput. It actually works as they claim. Never did explain why it wasn't a single point of failure in the end though. My hardware was 3 4810's, each with 4 - 8 cpus, each with 4 - 8GB, 2 - 4 bricks per machine First insist that multiple bricks be mounted on at least 2 mount points. (D2 and D3). DO NOT USE the forcedirectio option. I don't know why but I have been unable to take less than a 40% throughput hit with it turned on. And I don't care what other people say, no matter how much respect I have for them Insist on at least one JBOD for oracle binaries and configs Insist on at least one JBOD for redo logs (D1) This a bare minimum. One set of redo on D1 One set of redo on D2 Archive logs, Rollback and Temp on D3 All data files where needed on D2 Next Level up Add another JBOD for redo and move redo on to it Move Rollback and Temp to D2 At this point to get more throughput you have to take the JBOD to raw devices. Or try forcedirectio on these devices :) If even better performance is needed, more JBOD, for rollback and redo. If more disk spaces is needed, get another brick. Which leads me to the recent discussion on "proper way to tune" Huh? Why make it so complex? Tuning from a blue collar DBA perspective: Assess the machine first No matter what your ratios or what your waiting for: sar to see if the machine is ever pinned vmstat to see your queues and paging iostat to see disk activity top at timed intervals to catch rogue jobs read your logs and config files Then talk to the users Is the "system slow" or is it specific jobs?" log on run ratio reports and query v$system_event Any ratio that is out of range needs to be tuned: Especially disk sorts to memory sorts For the infamous buffer cache ratio: < 10% throw memory at it > 97% take memory away For wait states here's a quick drive through for those who look at the number and say "Yeah but what do they mean" Time WaitTotal Time Average Event # Waits Timeout In HndrdsTime -- - --- -- SQL*Net more data to client # 0 680421.005 SQL*Net message to client # 0 17590 0.000 SQL*Net message from client # 0 3953399703 35.511 - These are all communication to the client. ignore db file sequential read39562523 0 12300885.311 - Data read, 0.0003 seconds average wait, ignore. This number will climb if - IO is bottlenecked or inappropriate (ie using FTS for joins) rdbms ipc message 12440441 ### 2774129387 222.993 - Internal machine communication ignore db file scattered read 12264223 0 6202885.506 - Data read, 0.0005 seconds average wait, ignore. This is higher due to type of read. - Increase in this time indicates an IO bottleneck log file parallel write 4724477 67 2212249.468 log file sequential read2097709 0 1712615.816 - Redo logs, with 2 pure raw JBOD I have got this down to about 0.25 hundredths buffer busy waits 1548548 0 408235.264 - Memory latch contention 0.0002 seconds ignore - If Timeout or average increase, need to determine why contention is increasing control file parallel write 669234 0 376491.563 pmon timer 662092 662074 203382329 307.181 - Internal waits ignore direct path read 573442 0 423920.739 - Reads from tempfiles (sorting). Each segment is 10M in this db so ignore. log file sync551716 15 459036.832 - See redo above. db file parallel write 201166 0 610793 3.036 - writing updates and inserts 0.003 seconds ignore undo segment extension 100516 100507 27 0.000 - Don't know what this is,
Testing
Gene Sais <[EMAIL PROTECTED]> wrote: another suggestion:disable default roles.grant create session to all users.use application to enable roles with password.hth,gene>>> [EMAIL PROTECTED] 08/09/02 01:58PM >>>Why to find who and when. the best thing is to restrict the access.-Original Message-Sent: Friday, August 09, 2002 10:44 PMTo: Multiple recipients of list ORACLE-LUse a logon trigger to capture everything from v$session and you can look atprogram name etc...it will be pretty easy to figure out who and when.Something like this in the trigger...select distinct sid into l_sid from v$mystat;insert into session_log (select * from v$session where sid = l_sid;Ethan Postperotdba (AIM), epost1 (Yahoo)-Original Me! ! ssage-Sent: Friday, August 09, 2002 11:49 AMTo: Multiple recipients of list ORACLE-LFolks,Before I go off re-inventing the wheel once again I'll ask the group isanyone has tried this before. What I have is a request from damanagement totell them when someone connects to our PeopleSoft database using the schemausername, but outside of PeopleTools. The reason is that there have beensome"unexplained" changes to data that have occurred over the last month that iscausing a pile of concern. It is believed that someone who has the schemapassword is using SQL*Plus or Toad to update the data when they should notbedoing so. Now auditing connects for the schema account is not a problem,butdetermining which are suspicious and which are due to the damned PeopleSoftpanel processor I can't see a way around easily from sys.aud$. Anyone elsebeenthere, done that??Dick Goulet-- Please see the ! ! official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, EthanINET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo! ! REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com --Author: Naveen NahataINET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command ! ! for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Gene SaisINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Yahoo! Digital How To - Get the best out of your PC!
RE: Houston, do I have a problem?
Rachel, This has not gotten to the level of getting it in 'black & white' or to the 'beating' level, yet. That's why my boss wanted to just review the database and see if this thing will scale when we add more Applications and of course, hundreds of more users. More digging will be required to find problematic areas, if any, and if some one complains that there is such-and-such problem. Currently, no one is complaining, as the batch processes finish while we are asleep and the on-line load has been pretty light. But there is no guarantee that it will be so in the near future. And looking at this database at this time was a good idea from my boss, and getting someone else, (not the primary/secondary DBA) to do that is even better (for him, that is ;) Thanks. - Kirti -Original Message- Sent: Sunday, August 11, 2002 8:38 PM To: Multiple recipients of list ORACLE-L Kirti, Get it in WRITING that you are not allowed to change anything. So that when they start to beat on you (okay, I know your boss, HE won't beat but HIS boss might) you are covered. You have my sympathies... I've worked under similar conditions (Rachel, we are giving you 750GB for your databases... oh yeah, RAID 5) Rachel --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > This is not a joke.!!! > > This is from a business critical production database that I was asked > to > 'review' past Friday. > > The report is from v$system_event taken at 10:30am, Aug 9, 2002. > The server (and database) was bounced on Aug 4, 2002 at 9:20am. > > This was the 1st time I was logging into this database. > > SQL> / > > EVENT TOTAL_WAITS TOTAL_TIMEOUTS > TIME_WAITED > AVERAGE_WAIT > --- --- -- > --- > > control file parallel write 143933 0 > 4080356626 > 28349.0001 > db file scattered read 12540695 0 > 1.2254E+10 > 977.107332 > buffer busy waits 10740450 36 > 8193235928 > 762.839167 > SQL*Net message from client 180769027 0 > 9.9561E+10 > 550.761199 > db file sequential read 298968127 0 > 1.1839E+11 > 395.99129 > enqueue 13500 6435 > 2036785 > 150.872963 > SQL*Net more data from client 52227948 0 > 4093231165 > 78.3724294 > free buffer waits16 4 > 795 > 49.6875 > log file switch completion 804 43 > 16263 > 20.2276119 > log buffer space977 0 > 5409 > 5.53633572 > control file single write17 0 > 51 > 3 > db file parallel write 1749695 0 > 2935317 > 1.67761638 > db file parallel read 8149 0 > 13484 > 1.65468156 > log file single write 1024 0 > 701 > .684570313 > latch free 20070341616763 > 1054137 > .525221297 > log file sync 1366242560 > 526049 > .385033545 > SQL*Net message from dblink 1514480 0 > 451351 > .298023744 > log file sequential read 405415 0 > 82877 > .204425095 > SQL*Net break/reset to dblink10 0 >2 > .2 > log file parallel write 2025192 7 > 293332 > .144841576 > SQL*Net break/reset to client 28113 0 > 3221 > .114573329 > db file single write320 0 > 36 > .1125 > SQL*Net more data from dblink447044 0 > 11375 > .025444923
RE: SAN issues
Babu, If you have not already done so, please also review a paper by James Morle : Sane SAN http://www.scaleabilities.com/whitepapers.shtml - Kirti -Original Message- Sent: Monday, August 12, 2002 6:45 PM To: Multiple recipients of list ORACLE-L Babu, Nice comprehensive list of things to consider with a SAN, Just a couple of thoughts. > ® Oracle requests DBWR-1 for IDX1 and waits. DBWR-1 makes a Unix IO > call and waits for Unix to return data. Unix talks to SAN and SAN starts > reading from the disk. Assume that it takes 3 seconds to read the entire > IDX1. SAN starts returning data in chunks to Unix and Unix gives it back to > Oracle. Data is read from Disk by server processes, not by DBWR. > ® Now a slightly bigger picture. There are 6 processes trying to read > the data from six different tables. This occurs regardless of the type of storage system, so I'm not sure it really belongs in a list of SAN specific concerns. > ® Lets forget all this buffering, caches etc. Assume we have 10 disks > in two LUNs. Both the LUNs share the 10 disks. Each of this LUN is made > visible to Unix as a mountpoint. The DBA uses one mountpoint for indexes > and one mountpoint for tables. You can have this same kind of configuration problem with any disk storage manager. Don't forget the management issue with SANs. SA's love them because it greatly reduces the amount of work they must do to manage storage. They can be properly configured from a database point of view, at least as far as distribuing IO is concerned, you just need to make it known that you would like some input on it's configuration. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/12/2002 01:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:SAN issues All I an trying to get our management understand the issues related to SAN. These are my thoughts. Let me know what you think about it... (PS : Apologies if you recv this twice. I posted it but I never saw it come through the list and so I posted again) Babu SAN Issues SAN and Oracle ? Conflicting IO behavior ® There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files ® Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. ® SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) ® SANs do "Read Ahead". If one block is requested, they read more than one blocks "while at the disk" hoping that the same process will request the other blocks some time soon. Here is the conflict. ® When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. ® Why "read ahead" will cause a conflict : ® The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+- +-+-| | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | 3.4 | | | | | | | | | | | | |-+-+-+-+-+-+-+- +-+-| ® The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) ® Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. ® The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never "read a
RE: Houston, do I have a problem?
Greg, Yes, it's way too much. There are three control files. - Kirti -Original Message- Sent: Monday, August 12, 2002 6:23 PM To: Multiple recipients of list ORACLE-L > >control file parallel write 143933 0 4080356626 > >28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Deshpande, Kirti 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: Houston, do I have a problem?
Greg, That's exactly the problem--you can't tell whether it's a problem or not! If the instance has been up for a couple of days, then it's probably a big deal. If the instance has been up for several months, then it's probably far less of a big deal. ...Unless a disproportionate amount of all that waiting time has been inflicted upon a really small number of programs. You just can't tell from system-wide data! You can't extrapolate detail from an average. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Sent: Monday, August 12, 2002 6:23 PM To: Multiple recipients of list ORACLE-L > >control file parallel write 143933 0 4080356626 > >28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Cary Millsap 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: Houston, do I have a problem?
Cary, Thank you very much for a wonderful response. I have not yet talked to any users or developers regarding the performance of this database. I just shared with my boss (and with the list) what I found in my brief 'review' of the database. There are no reported performance related problems that need to be fixed right away. But he was concerned as we will be loading this database soon to support a couple of more Applications. One of which will have more OLTP transactions than the other. Currently the database has heavy DML activity only during the batch processes. There is very minimal DML via on-line (Intranet). From our auto-scheduler (AutoSys) system, I have found out that there are a *number* of batch jobs that run *concurrently* against this database in the batch window. That would be the next area to 'review' as to what these jobs do and how they do it. For all we know, it could just be a scheduling problem! I am not paying much attention to the stats at system level as they do not mean much, at this time. I will be watching the batch processes and most probably consider using Statspack and make use of Sparky (http://www.hotsos.com/products/sparky/) to dig deeper. I am not going to reach any conclusion based solely on what I saw in v$system_event. That was just my first step to see what kinds of waits this database had encountered, just as some check the hit ratio first ;-) But I must say, based on those stats, that the I/O subsystem is being stressed quite a bit. Regards, - Kirti -Original Message- Sent: Monday, August 12, 2002 1:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the "wait interface" as they are from v$sys.) Don't lose hope if you look at Kirti's note and wonder, "so what's the point?" You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the "secret constants" (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than "normal systems" at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is "heinously longer than normal." 2. If you know the "secret list" of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it "shouldn't be doing." Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming "heinously longer-than-normal" elapsed times, or even if it is called "heinously too often," does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the "right kinds of service in the right proportions?" How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a "normal, healthy" profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's "worst performance problems" and then find out that their work really didn't make a noticea
RE: set sql*trace VB/Crystal
Barb, To get all the data you might need for the session, use the 10046 level 8 tracing attribute available through the various means described at www.hotsos.com/dnloads/1/10046a. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Barbara Sent: Monday, August 12, 2002 3:23 PM To: Multiple recipients of list ORACLE-L List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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: SAN issues
Babu, Nice comprehensive list of things to consider with a SAN, Just a couple of thoughts. > ® Oracle requests DBWR-1 for IDX1 and waits. DBWR-1 makes a Unix IO > call and waits for Unix to return data. Unix talks to SAN and SAN starts > reading from the disk. Assume that it takes 3 seconds to read the entire > IDX1. SAN starts returning data in chunks to Unix and Unix gives it back to > Oracle. Data is read from Disk by server processes, not by DBWR. > ® Now a slightly bigger picture. There are 6 processes trying to read > the data from six different tables. This occurs regardless of the type of storage system, so I'm not sure it really belongs in a list of SAN specific concerns. > ® Lets forget all this buffering, caches etc. Assume we have 10 disks > in two LUNs. Both the LUNs share the 10 disks. Each of this LUN is made > visible to Unix as a mountpoint. The DBA uses one mountpoint for indexes > and one mountpoint for tables. You can have this same kind of configuration problem with any disk storage manager. Don't forget the management issue with SANs. SA's love them because it greatly reduces the amount of work they must do to manage storage. They can be properly configured from a database point of view, at least as far as distribuing IO is concerned, you just need to make it known that you would like some input on it's configuration. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/12/2002 01:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:SAN issues All I an trying to get our management understand the issues related to SAN. These are my thoughts. Let me know what you think about it... (PS : Apologies if you recv this twice. I posted it but I never saw it come through the list and so I posted again) Babu SAN Issues SAN and Oracle ? Conflicting IO behavior ® There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files ® Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. ® SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) ® SANs do "Read Ahead". If one block is requested, they read more than one blocks "while at the disk" hoping that the same process will request the other blocks some time soon. Here is the conflict. ® When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. ® Why "read ahead" will cause a conflict : ® The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+-+-+-| | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | 3.4 | | | | | | | | | | | | |-+-+-+-+-+-+-+-+-+-| ® The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) ® Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. ® The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never "read ahead" intelligently. Why the buffer of SAN has very little impact w.r.t Oracle read performance? ® Oracle has its own buffering for all IO types ® DBWR reads and writes uses the DB Buffer Cache ® LGWR uses the Log buffer ® Db buffer Cache is ma
Re: Oracle vs. DB2
-- "Vergara, Michael (TEM)" <[EMAIL PROTECTED]> on 08/12/02 14:38:19 -0800 > Hi Everyone! > > Well, there's been a lot of Oracle vs. Microsoft traffic on the > list, but now my Manglement wants a similar comparison to IBM's > DB2. > > Does anyone know of web sites or locations where there are > documented objective comparisons between Oracle and DB2? I'm > faced with answering buzzwords like 'Future Market Position', > 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and > 'Platform Compatibility'. www.ibm.com -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Houston, do I have a problem?
> >control file parallel write 143933 0 4080356626 > >28349.0001 Well, this is the top wait. Isn't 283 seconds to finish writing to all the control files a little much? How many control files are there, anyway? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle vs. DB2
For what I've read, globally the 2 databases are equal in performance, reliability and functionnalities. Larryh E as many times said that it's only competition in the database market is DB2. I guess it really depends on your environment. Of course Oracle works on more OS (used to be anyway), but which big organisation only have one DB ? All big companies I've worked have many DB. I would be interested by any non-partial comparison between Oracle and DB2. --- "Vergara, Michael (TEM)" <[EMAIL PROTECTED]> a écrit : > Hi Everyone! > > Well, there's been a lot of Oracle vs. Microsoft > traffic on the > list, but now my Manglement wants a similar > comparison to IBM's > DB2. > > Does anyone know of web sites or locations where > there are > documented objective comparisons between Oracle and > DB2? I'm > faced with answering buzzwords like 'Future Market > Position', > 'T.C.O. - Cost Effectiveness', 'Demonstrated > Technology', and > 'Platform Compatibility'. > > Any references are appreciated. > > Thanks, > Mike > > --- > === > Michael P. Vergara > Oracle DBA > Guidant Corporation > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Vergara, Michael (TEM) > 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
Hello, I have a question regarding method of index access(full vs. range) that oracle uses when I use different join methods (Hash vs. Nested loop). Here are the some details about the environment. Server : Oracle Server EE ver 8.1.7.2 OS : HP UX 11.0 hash_area_size : 4194304 hash_join_enabled : TRUE hash_multiblock_io_count: 64 The tables, indexes are all analyzed fully (no estimates) with dbms_stat. The following two scenarios are executed back-to-back and are reproducable. The parameter value to the SQL could be any month in a format like 200207, 200201 etc.. When I use nested loop join the index on large table is ranged scaned, but when I use the hash join the full scan on the index is done. My question is why is the behviour of index scan different? Why there is no partition pruning when the table is hash-joined? The details related to the objects is at the end of the message. Thanks for your help. Regards Viral. NESTED LOOP SQL === select --+use_nl(cm fd) index(fd) fd.* from copa_mdo_srce_sys_map cm ,fact_dmnsn_q3 fd where fd.srce_sys_id = cm.srce_sys_id and cm.mdo_id = 'NA' and fd.due_perd = LAST_DAY(TO_DATE('&1', 'MM')) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1333 Card=54869 Bytes5925852) 1 0 NESTED LOOPS (Cost=1333 Card=54869 Bytes=5925852) 2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9 Bytes=54) 3 1 PARTITION RANGE (ITERATOR) 4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=148 Card=73158 Bytes=7462116) 5 4 INDEX (RANGE SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQ UE) (Cost=30 Card=73158) HASH JOIN SQL === select --+use_hash(cm fd) index(fd) fd.* from copa_mdo_srce_sys_map cm ,fact_dmnsn_q3 fd where fd.srce_sys_id = cm.srce_sys_id and cm.mdo_id = 'NA' and fd.due_perd = LAST_DAY(TO_DATE('&1', 'MM')); Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13103 Card=54869 Byt es=5925852) 1 0 HASH JOIN (Cost=13103 Card=54869 Bytes=5925852) 2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 C ard=9 Bytes=54) 3 1 PARTITION RANGE (ALL) 4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=13101 Card=73158 Bytes=7462116) 5 4 INDEX (FULL SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQU E) (Cost=11693 Card=73158) Table/index designs === desc fact_dmnsn_q3 Name Null?Type - - FACT_ID NOT NULL NUMBER(15) CAPTL_ID VARCHAR2(15) CUST_IDVARCHAR2(15) FUNC_IDVARCHAR2(15) GENRC_DMNSN_1_ID VARCHAR2(15) GENRC_DMNSN_1_TYPE_ID NUMBER(4) GENRC_DMNSN_2_ID VARCHAR2(15) GENRC_DMNSN_2_TYPE_ID NUMBER(4) GENRC_DMNSN_3_ID VARCHAR2(15) GENRC_DMNSN_3_TYPE_ID NUMBER(4) GEO_ID VARCHAR2(15) LEGAL_ENT_ID VARCHAR2(15) MEASR_ID VARCHAR2(15) MM_HYBRD_IDVARCHAR2(15) ORG_ID VARCHAR2(15) PROD_IDVARCHAR2(15) PROFT_CTR_ID VARCHAR2(15) SITE_IDVARCHAR2(15) TIME_PERD_ID NOT NULL VARCHAR2(15) TRADE_CHANL_ID VARCHAR2(15) FACT_TYPE_CODENOT NULL VARCHAR2(2) ISO_CRNCY_CODE_CHARVARCHAR2(3) SRCE_SYS_ID NOT NULL NUMBER(15) LYOUT_ID NUMBER(4) FACT_QLTY_CODE VARCHAR2(1) MKT_CLASS_CODE VARCHAR2(1) DEMND_PLAN_CUST_GRP_CODE VARCHAR2(10) TIME_PERD_TYPE_CODE NOT NULL VARCHAR2(4) TIME_PERD_END_DATENOT NULL DATE DUE_PERD NOT NULL DATE ROW_ORIGN VARCHAR2(1) PRTTN_CODE VARCHAR2(15) PARNT_FACT_ID NUMBER(15) DELIV_RCVD_ID NUMBER(10) ORIG_UNIT_ID NUMBER(11) SQL:idwsp3 -> desc copa_mdo_srce_sys_map Name Null?Type -
Oracle vs. DB2
Hi Everyone! Well, there's been a lot of Oracle vs. Microsoft traffic on the list, but now my Manglement wants a similar comparison to IBM's DB2. Does anyone know of web sites or locations where there are documented objective comparisons between Oracle and DB2? I'm faced with answering buzzwords like 'Future Market Position', 'T.C.O. - Cost Effectiveness', 'Demonstrated Technology', and 'Platform Compatibility'. Any references are appreciated. Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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: set sql*trace VB/Crystal
Try to find out the sid and serial# of her session. >From a dba user use "exec dbms_system.set_sql_trace_in_session(sid, serial#,true); Babu "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on 08/12/2002 03:23:23 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete performance
Title: RE: Delete performance I would use your method to CTAS but combine it with partitioning in order to overcome the unavailability issue. The new table would be a single-partition (MAXVALUE) object that would enable the use of EXCHANGE feature. I posted a nugget a while back describing the use of a one-partition table (and indexes) and then swapped the underlying segment with a normal table on the fly. The catch is the licensing cost for partitioning. But we already had it for it's intended use and this "availability" feature was icing on the cake. This method is replaced by 9i's online reorg feature but we got a good 3 years out of it. Tony Aponte Home Shopping Network, Inc. -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 4:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Delete performance If the table can be "unavailable" for a very brief period of time while this is happening, I'd suggest performing a PARALLEL NOLOGGING CREATE TABLE AS SELECT to perform this mass deletion. Use a WHERE clause in the SELECT portion of the CTAS that picks up all the rows you want to keep, which is the logical negation of the WHERE clause you already have for the DELETE. Advantages: faster (INSERT operations are always faster than UPDATE or DELETE), using NOLOGGING is possible (faster, reduce overall impact on system), no undo is generated (faster, reduce one possible point of failure), and if you had any ambitions to re-build the table (i.e. get rid of chained rows, move to locally-managed tablespace), that gets done too. Same for the associated indices (rebuild them in parallel, nologging, compute stats). Also, the original table can be renamed and saved in case it's ever needed (for fast "rollback", for example)... Disadvantages: if table cannot be unavailable for the last-second RENAME operation when the "old" table is swapped for the "new", which would invalidate any associated PL/SQL stored objects and open cursors, then this won't work. However small that window of unavailability may be, sometimes you just can't go there... Just an idea... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, August 01, 2002 8:03 AM > Hi all: > > Someone at my shop wants to delete about 20% of roes > in a table (20 rows out of a million). He wants to > set a commit frequency (like every 1000 records or > so) > to keep the rbs under control. I am not aware of any > easy way to do it other then writing a procedure, but > I may be missing something here. Is there any simple > way to accomplish this? > > Also I have suggested instead of deleting 20% of the > rows, create a new table as a select and insert the > rest of the rows into it (then rebuild the indices and > rename). This can be done in nologging mode, without > redo logs and rbs segments. Is this a good idea to > try? > > thanks for any info > > Gene > > __ > Do You Yahoo!? > Yahoo! Health - Feel better, live better > http://health.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Gurelei > 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: Tim Gorman 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: Houston, do I have a problem?
Cary, Thank you very much for a wonderful response. I have not yet talked to any users or developers regarding the performance of this database. I just shared with my boss (and with the list) what I found in my brief 'review' of the database. There are no reported performance related problems that need to be fixed right away. But he was concerned as we will be loading this database soon to support a couple of more Applications. One of which will have more OLTP transactions than the other. Currently the database has heavy DML activity only during the batch processes. There is very minimal DML via on-line (Intranet). From our auto-scheduler (AutoSys) system, I have found out that there are a *number* of batch jobs that run *concurrently* against this database in the batch window. That would be the next area to 'review' as to what these jobs do and how they do it. For all we know, it could just be a scheduling problem! I am not paying much attention to the stats at system level as they do not mean much, at this time. I will be watching the batch processes and most probably consider using Statspack and make use of Sparky (http://www.hotsos.com/products/sparky/) to dig deeper. I am not going to reach any conclusion based solely on what I saw in v$system_event. That was just my first step to see what kinds of waits this database had encountered, just as some check the hit ratio first ;-) But I must say, based on those stats, that the I/O subsystem is being stressed quite a bit. Regards, - Kirti -Original Message- Sent: Monday, August 12, 2002 1:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the "wait interface" as they are from v$sys.) Don't lose hope if you look at Kirti's note and wonder, "so what's the point?" You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the "secret constants" (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than "normal systems" at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is "heinously longer than normal." 2. If you know the "secret list" of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it "shouldn't be doing." Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming "heinously longer-than-normal" elapsed times, or even if it is called "heinously too often," does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the "right kinds of service in the right proportions?" How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a "normal, healthy" profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's "worst performance problems" and then find out that their work really didn't make a noticea
RE: Brain cramp on analytical functions and grouping.
I pivoted the result set on the WO column. This example works for up to 12 distinct values for the CP column. I don't know if you need to pivot it again to get back to the original result set but at least it gives you the sort order you described. HTH Tony Aponte Home Shopping Network, Inc. create table work_orders (WO VARCHAR2(7),CP VARCHAR2(7))insert into work_orders values ('W859674','A120003')insert into work_orders values ('W859674','A120004')insert into work_orders values ('W859674','A120006')insert into work_orders values ('W838796','A12')insert into work_orders values ('W838796','A120003')insert into work_orders values ('W844656','A12')insert into work_orders values ('W844656','A120004')insert into work_orders values ('W849769','A12')insert into work_orders values ('W849769','A120004')insert into work_orders values ('W858835','A12')insert into work_orders values ('W858835','A120003')insert into work_orders values ('W880717','A120003')insert into work_orders values ('W880717','A120006')commit SELECT g1 ,MAX(DECODE(line_no,01,value,NULL)) A, MAX(DECODE(line_no,02,value,NULL)) B, MAX(DECODE(line_no,03,value,NULL)) C, MAX(DECODE(line_no,04,value,NULL)) D, MAX(DECODE(line_no,05,value,NULL)) E, MAX(DECODE(line_no,06,value,NULL)) F, MAX(DECODE(line_no,07,value,NULL)) G, MAX(DECODE(line_no,08,value,NULL)) H, MAX(DECODE(line_no,09,value,NULL)) I, MAX(DECODE(line_no,10,value,NULL)) J, MAX(DECODE(line_no,11,value,NULL)) K, MAX(DECODE(line_no,12,value,NULL)) L FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no FROM (SELECT wo g1,cp value from work_orders) ) GROUP BY g1 ORDER BY 2,3,4,5,6,7,8,9,10,11,12,13,1 G1 A B C D E F G H I J K LW838796 A12 A120003 W858835 A12 A120003 W844656 A12 A120004 W849769 A12 A120004 W859674 A120003 A120004 A120006 W880717 A120003 A120006 -Original Message-From: Jesse, Rich [mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 07, 2002 2:35 PMTo: Multiple recipients of list ORACLE-LSubject: Brain cramp on analytical functions and grouping.OK, my brain hurts. A dev wants a query to return in a peculiar sort orderon 8.1.7.2, but I'm having no luck. He needs groups of rows sorted by thewhole of their key values. That doesn't sound right, so maybe an example:Table ARI WO CP RC RN1 W859674 A120003 3 12 W859674 A120004 3 23 W859674 A120006 3 34 W838796 A12 2 15 W838796 A120003 2 26 W844656 A12 2 17 W844656 A120004 2 28 W849769 A12 2 19 W849769 A120004 2 210 W858835 A12 2 111 W858835 A120003 2 212 W880717 A120003 2 113 W880717 A120006 2 2In an attempt to breakdown the problem, I added columns RC and RN as"COUNT(*) OVER (PARTITION BY WO)" and "ROW_NUMBER() OVER (PARTITION BY WOORDER BY CP)", respectively. I also added the row spacing here for clarity.The dev would like the group of WO W858835, rows 10 and 11, immediatelyafter WO group W838796 because the groups have the same number of rows (RC)and same values of CP within the groups.MIN and MAX would work in this case, but if the groups are larger than twoit's no guarantee of order. What I was thinking is a report column thatwould be the concatonation of all the CPs for the group, but since it'sVARCHAR2 and not numeric, I'm not sure how that could be accomplished.Any suggestions, including favorite beers, is more than welcome.TIA!Rich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Jesse, Rich INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: set sql*trace VB/Crystal
What connection are they using? If they are using Oracle Object Of OLE I think there is a parameter that can be set. >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: set sql*trace VB/Crystal >Date: Mon, 12 Aug 2002 13:08:23 -0800 > >If you want to see the trace do the following connected to the appropriate >database: >SQL> show parameters dump; > >NAME TYPEVALUE > --- -- >background_core_dump string partial >background_dump_dest string /opt/oracle/admin/ods/bdump >core_dump_dest string /opt/oracle/admin/ods/cdump >max_dump_file_size string UNLIMITED >shadow_core_dump string partial >user_dump_dest string /opt/oracle/admin/ods/udump > >I believe it is under user_dump_dest - and is constrained by the >max_dump_file_size. > >You will then need to use tkprof commands to format *.trc file. To check >it >is correct trace file can grep session id or even bit of SQL she used that >would be specific to her session. > >Most of the Oracle references have tkprof examples, so does metalink and >cdrom with oracle doc. > >-Original Message- >Sent: Monday, August 12, 2002 4:03 PM >To: '[EMAIL PROTECTED]' > > >I don't know vb either. However the trace file is likely generated on the >server-side. She would not see the output. She might want to try alter >session set autotrace on; instead. That way she should see the results. >That is how it works in SQL*PLUS. Otherwise, you will have to send her the >trace file from the server - you guys will get quickly tired of that. >'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily >show you - but I believe that is related to partitioning and parallelism. > >-Original Message- >Sent: Monday, August 12, 2002 4:23 PM >To: Multiple recipients of list ORACLE-L > > > >List: >We have a crystal report performing badly. (No! ,you say. You're shocked!) >The report has a visual basic front end. > >Our developer wants to set sql trace in the VB code. It's not working. >When I tkprof her trace file, all that's in there is the "ALTER SESSION SET >SQL_TRACE TRUE" command. > >Is there some trick here? I don't know VB at all, so I don't know how to >advise her. She looked on the Microsoft site, but it was not helpful. > >Thanks for any help! > >Barb > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Baker, Barbara > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade 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: set sql*trace VB/Crystal
You can see the sql generated by the report in Crystal, so take that sql and run it in sqlplus to see the access plan. You can also check in v$sqltext the select run by the report. --- "Baker, Barbara" <[EMAIL PROTECTED]> a écrit : > > List: > We have a crystal report performing badly. (No! ,you > say. You're shocked!) > The report has a visual basic front end. > > Our developer wants to set sql trace in the VB code. > It's not working. > When I tkprof her trace file, all that's in there is > the "ALTER SESSION SET > SQL_TRACE TRUE" command. > > Is there some trick here? I don't know VB at all, > so I don't know how to > advise her. She looked on the Microsoft site, but > it was not helpful. > > Thanks for any help! > > Barb > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal If you want to see the trace do the following connected to the appropriate database: SQL> show parameters dump; NAME TYPE VALUE --- -- background_core_dump string partial background_dump_dest string /opt/oracle/admin/ods/bdump core_dump_dest string /opt/oracle/admin/ods/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /opt/oracle/admin/ods/udump I believe it is under user_dump_dest - and is constrained by the max_dump_file_size. You will then need to use tkprof commands to format *.trc file. To check it is correct trace file can grep session id or even bit of SQL she used that would be specific to her session. Most of the Oracle references have tkprof examples, so does metalink and cdrom with oracle doc. -Original Message- From: Stankus, Paula G Sent: Monday, August 12, 2002 4:03 PM To: '[EMAIL PROTECTED]' Subject: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Explain Plan Question
Hello: I have to looking through Metalink but am unable to find the following. Does running Explain Plan take into Account the Cost Generated By using PL/SQL Functions? For example Select * from EMP where EMPID=FUNCTION_GET_ID; I do not believe that it does since the Function SQL does not seem to be included in the Plan Output. Is this correct? Regards, Jay _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade 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: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SAN issues
All I an trying to get our management understand the issues related to SAN. These are my thoughts. Let me know what you think about it... (PS : Apologies if you recv this twice. I posted it but I never saw it come through the list and so I posted again) Babu SAN Issues SAN and Oracle ? Conflicting IO behavior ® There are four types of IO in Oracle 1.Random Reads (RR) ? DBWR - Using indexes 2.Sequential Reads (SR) ? DBWR - Full table scans 3.Random Writes (RW) ? DBWR ? Writing dirty blocks 4.Sequential Writes (SW) ? LGWR, Arch ? Writing redo logs and Redo Archival + Control files ® Bulk of any Oracle database's IO is done in RR, SR and RW. If SW is very high it denotes configuration problems. ® SAN (or for that matter any RAID device) is configured for writing or reading large chunks at a time. The stripe size on most SANs and RAID devices are 256K or more. Compare this to the Oracle block size of 4k/8k in most databases (going upto 32K in datawarehouses) ® SANs do "Read Ahead". If one block is requested, they read more than one blocks "while at the disk" hoping that the same process will request the other blocks some time soon. Here is the conflict. ® When ever Oracle does a RR, SR or RW it writes randomly and not sequentially. It will read/write a particular block at a time in case of RR and RW and 'x' blocks (where x = dbfile_multi_block_read_count) in case of SR. Therefore only during SR will Oracle use the entire stripe width. In all other cases, The difference in the stripe width and db_block_size will be excess IO. ® Why "read ahead" will cause a conflict : ® The internal structure of a datafile could be as follows. The file consists of 10 blocks. These are occupied by 3 tables. The blocks shown below are numbered using table_name.block_number |-+-+-+-+-+-+-+-+-+-| | | | | | | | | | | | | 1.1 | 1.2 | 2.1 | 3.1 | 3.2 | 3.3 | 2.2 | 1.3 | 2.3 | 3.4 | | | | | | | | | | | | |-+-+-+-+-+-+-+-+-+-| ® The first block on the datafile is the first block of table 1, second block is the second block of table 1, the third block is the first block of table 2 and so on.. (For simplicity sake, I am assuming Oracle will allocate space in blocks and not in extents) ® Now assume Oracle requests the first block of table 1. Assume read ahead is set to three blocks (three blocks will be read instead of 2 blocks). In this case the SAN will read 2.1, 3.1,3.2. ® The blocks 3.1 and 3.2 will be entirely useless as Oracle is never going to read it. SAN cannot tell that the block 2.2 that Oracle might possible request next is the 7th block in the datafile and so it can never "read ahead" intelligently. Why the buffer of SAN has very little impact w.r.t Oracle read performance? ® Oracle has its own buffering for all IO types ® DBWR reads and writes uses the DB Buffer Cache ® LGWR uses the Log buffer ® Db buffer Cache is managed by a LRU Algorithm (Touchcount from 9I). ® Bulk of the IO done by Oracle is Logical IO (LIO) and not Physical IO (PIO). ® Assume the buffer cache hit ratio is 80%. This means that only 20% of the IO calls are PIO. Only 20% of the calls ever hit the SAN's cache. Since this 20% is probably the least requested/never requested data (going by Oracle's LRU algorithm) , its quite likely that the SAN's buffers don't have this either. ® Given that Oracle is going to cache even this 20% in its buffers, the next PIO call is going to be for something totally different ? which is not there in the SAN's buffer. ® Couple this with the read-ahead (discussed earlier), Our SAN's buffer is now populated with lots of data that Oracle might never use a PIO to retrieve. ® Thus the SAN's buffer can never really provide to Oracle the data it reads most ? Its already there in Oracle. ® To be fair, SAN's huge buffers will come as a boon to small databases ? where the entire database can be cached in the SAN's buffers. SAN or no SAN ? Why will performance be affected if we have indexes and tables on the same disk ® Lets forget all this buffering, caches etc. Assume we have 10 disks in two LUNs. Both the LUNs share the 10 disks. Each of this LUN is made visible to Unix as a mountpoint. The DBA uses one mountpoint for indexes and one mountpoint for tables. ® Since we have used all the 10 disks for both the LUNs, the structure of one disk can look like this. The first two blocks are a chunk of LUN1 where we have the table TAB1. The next three blocks
Re:set sql*trace VB/Crystal
Barb, More than likely VB is spawning Crystal in a separate database session, therefore the alter session command will not work. You could have her start the report & then use top sessions to extract the sql and explain plan from the DB. Or you could extract the sql from the crystal report & go from there. Dick Goulet Reply Separator Author: "Baker; Barbara" <[EMAIL PROTECTED]> Date: 8/12/2002 12:23 PM List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
set sql*trace VB/Crystal
List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
odbc/jdbc question
We are running Oracle 8.1.7 on AIX. Our users want to use ODBC & JDBC products to connect to the database. Are there any security issues we should know about? Any other issues? Thanks. Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Armstrong-Champ 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: Naming Convention - server code
Title: RE: Naming Convention - server code Guys, So we talked about table naming conventions how about procedures, packages and functionsBTW, my organization did not want to drop type of object in name - except for tables. I know arguments against but for includes - preexisting standards and help for newer DBA's esp. during quick diagnostic exercise. GENERAL NAMING CONVENTIONS . Part of data model validation should be to ensure that no RDBMS reserved words are used. . Recommended that models be checked against these standards as soon and as automatically as possible within design process to avoid unnecessary rework. . Should have a common list of modifiers. . Should have a common list of descriptors. . Construction rules: - underscores can be used for clarity TRIGGERS-prefix+modifier(s)+table name FUNCTIONS-prefix+description of function STORED PROCEDURES-prefix+description - description should contain clear description of process implemented. PACKAGES-prefix (pkg?) + description.
Case Statements, Distributed Queries, ORA-22804 Errors
When one tries to use a case statement, okay it's really a function, over a database link Oracle objects: ORA-22804 remote operations not permitted on object tables or user-defined type columns Cause: An attempt was made to perform queries or DML operations on remote object tables or on remote table columns whose type is one of object, REF, nested table or VARRAY. Action: Remove the reference to remote tables in the statement === There are no object tables, varrays or anything of that ilk involved in the statement. Why the error? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Trial License
Alexandre, Here is some of the text you refer to: Trial Programs Included With Orders We may include additional programs with an order which may be used for trial purposes only. What this says to me is that Oracle _may_ include additional programs which may be used for trial purposes for 30 days. It does not apply to the main download. Otherwise, what would be the point of charging people $199 under the same licence terms (Technet Tracks) to have all releases in twelve months sent to them? Cheers, John In message <[EMAIL PROTECTED]>, Alexandre Gorbatchev <[EMAIL PROTECTED]> writes >John, >I have quoted OTN(Oracle Tech Net). That's the same. >If you take a further look down at "few other things", you'll see the text I >quoted. Two conditions together limit the usage only for development and >prototyping and only for period of 30 days. >Btw, the download site of www.oracle.com is technet.oracle.com >(otn.oracle.com). > >Alexandre >- Original Message - >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >Sent: Friday, August 09, 2002 11:38 PM > > >> When you said OTN, I thought you meant Oracle Technet. >> >> If you look at the this site: >> >> http://technet.oracle.com/software/htdocs/devlic.html?/software/products/ >> oracle9i/htdocs/winsoft.html >> >> You will find the licence asks you to agree you're not someone the US >> doesn't like and then to accept the Technet licence agreement. Which >> includes the following: >> >> License Rights >> We grant you a nonexclusive, nontransferable limited license to use the >> programs only for purposes of developing and prototyping your >> applications, and not for any other purpose. If you use the >> applications you develop under this license for any internal data >> processing or for any commercial or production purposes, or you want to >> use the programs for any purpose other than as permitted under this >> agreement, you must contact us, or an Oracle reseller, to obtain the >> appropriate license. We may audit your use of the programs. Program >> documentation is either shipped with the programs, or documentation may >> accessed online at http://otn.oracle.com/docs. >> >> Ownership and Restrictions >> We retain all ownership and intellectual property rights in the >> programs. The programs may be installed on one computer only, and used >> by one person in the operating environment identified by us. You may >> make one copy of the programs for backup purposes. >> >> You may not: >> ·use the programs for your own internal data processing or for any >> commercial or production purposes, or use the programs for any purpose >> except the development and prototyping of your applications; >> ·use the applications you develop with the programs for any internal >> data processing or commercial or production purposes without securing an >> appropriate license from us; >> ·remove or modify any program markings or any notice of our proprietary >> rights; >> ·make the programs available in any manner to any third party; >> ·use the programs to provide third party training; >> ·assign this agreement or give or transfer the programs or an interest >> in them to another individual or entity; >> ·cause or permit reverse engineering or decompilation of the programs; >> ·disclose results of any program benchmark tests without our prior >> consent; or, >> ·use any Oracle name, trademark or logo. >> >> And a few other things... >> >> So basically you can use it for development, free of charge, as long as >> you don't put applications you develop into production use at your site >> or anyone elses. >> >> I think the terms you were quoting refer to Trial licences from >> www.oracle.com's shop, not OTN. >> >> Cheers, >> >> John Thomas >> >> >> In message <[EMAIL PROTECTED]>, Alexandre >> Gorbatchev <[EMAIL PROTECTED]> writes >> >Here is the quotes: >> >. >> > >> >Trial Programs Included With Orders >> >We may include additional programs with an order which may be used for >trial >> >purposes only. You will have 30 days from the delivery date to evaluate >> >these programs. Any use of these programs after the 30 day trial period >> >requires you to obtain the applicable license. Programs licensed for >trial >> >purposes are provided "as is" and we do not provide technical support or >any >> >warranties for these programs. >> > >> >. >> > >> >Doesn't that mean that any use (limited to development and prototyping in >> >other paragraph above this one) is for 30 days only? Or this limitation >is >> >only for "additional programs?" What does "additional programs" mean? >> > >> >Alexandre >> >- Original Message - >> >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >> >Sent: Thursday, August 08, 2002 9:04 PM >> > >> > >> >> Unless I misread something, or the licence has changed, a Technet >> >> download licence is an indefinite development licence for use on one >> >> computer. >> >> >> >> It is only when your app goes
RE: OPS 7.3.4 - how to ?
What platform? What OS? Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, August 12, 2002 1:08 PM To: Multiple recipients of list ORACLE-L Dear gurus ! I have to create an OPS 7.3.4 database (yes, there are still Oracle7 installations out there). Actually we are migrating our current OPS DB to another storage (EMC). So , the software is installed , i just need to create the DB. I can not find which scripts to run after the "create database" (i.e. catproc.sql , catexp.sql). Does anyone have a list of such scripts to run for OPS , please? Also , are there any known gotchas for OPS 7.3.4 setup ? I plan to export the exisiting DB, to create the new DB (with the same name of the DB and the instances as the original ones) and then to import. Is it OK ? TIA. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
Oracle releasing clustered file system code The move is designed to allow better management of databases on Linux server clusters. http://computerworld.com/newsletter/0%2C4902%2C73404%2C0.html?nlid=AM -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: E business Suite 11i - for Apps DBA's
All documentation, including trm info, is in the CD pack (UD$40), orderable from oraclestore.oracle.com. (click on CD packs, choose platform, choose the product (oracle apps r11i 7 cd pack) Venkat Somusetty Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Venkat Somusetty 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).
OPS 7.3.4 - how to ?
Dear gurus ! I have to create an OPS 7.3.4 database (yes, there are still Oracle7 installations out there). Actually we are migrating our current OPS DB to another storage (EMC). So , the software is installed , i just need to create the DB. I can not find which scripts to run after the "create database" (i.e. catproc.sql , catexp.sql). Does anyone have a list of such scripts to run for OPS , please? Also , are there any known gotchas for OPS 7.3.4 setup ? I plan to export the exisiting DB, to create the new DB (with the same name of the DB and the instances as the original ones) and then to import. Is it OK ? TIA. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: SQL*Loader : How can I load the Line Feed as part of data
Hi Rachel, I have attached the sample data and controlfile, which we are using to load the data. It is loading the line feed characters('\x0d\x0a') as it is. Oracle should store it as Line Feed while loading through SQL*Loader. When I select this column it should displays something like this. i.e. a line feed instead of \x0d\x0a. But currently it does not happen that way as SQL*Loader loads '\x0d\x0a' as it is. I may be missing something. I searched in manual and Metalink but not able fix my problem. Thanks, Ashoke When the test is conducted in the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction Atrial Pacing Minimum Interval and Manual Therapy Atrial 50 Hz Induction Instruction Buffer Byte 7 and 9.\x0d\x0a When the test is conducted in the Ventricle,this parameter encodes to Manual Therapy 50 Hz Induction Ventricular Pacing Minimum Interval and \x0d\x0a Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and 5. and when we select this column -Original Message- Sent: Monday, August 12, 2002 10:58 AM To: Multiple recipients of list ORACLE-L check the utilities documentation, specifically the Field List section and the "terminated by" clause.. you can override the default terminator of line feed. You can also concatenate multiple lines together --- "Mandal, Ashoke" <[EMAIL PROTECTED]> wrote: > Greetings, > > We have one column in a table and this column need to store data with > Line Feed, denoted by \x0d\x0a in the following row. This data comes > from Sybase database. If \x0d\x0a cannot be loaded as Line Feed > through the SQL*Loader then how can we do it? > > Any help is appreciated. > Thanks, > Ashoke > > Data file contains the following one record : > > crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in > the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction > Atrial Pacing Minimum Interval and \x0d\x0a Manual Therapy Atrial 50 Hz > Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is > conducted in the Ventricle, this parameter encodes to Manual Therapy > 50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a > Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and > 5.|373441192 > > > Sample Control File : > LOAD DATA > INFILE param.dat "str X'0d0a'" > BADFILE param.bad > append into TABLE param_ashoke > TRAILING NULLCOLS > (DEVICE CHAR(80) TERMINATED BY "|" > ,PARAM CHAR(80) TERMINATED BY "|" > ,PARAM_TYPE CHAR(20) TERMINATED BY "|" > ,SW_NOTE CHAR(4000) TERMINATED BY "|" > ,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY "|" > ,FW_VAR_BASE CHAR(80) TERMINATED BY "|" > ,FW_VAR_MEMBER CHAR(80) TERMINATED BY "|" > ,BIT_OFFSET CHAR(38) TERMINATED BY "|" > ,BIT_SIZE CHAR(38) TERMINATED BY "|" > ,FW_ENCODE_DECODE CHAR(20) TERMINATED BY "|" > ,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY "|" > ,ALIAS CHAR(80) TERMINATED BY "|" > ,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE) > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mandal, Ashoke > 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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke 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
RE: Transferring data from one table to another
Peter, CTAS is the way to go with large datasets - CTAS in parallel (assuming multiple CPUs) and you will be good to go. Jack --- [EMAIL PROTECTED] wrote: > CTAS with nologging. Could create a simple script > to do this. > > -Original Message- > Sent: Monday, August 12, 2002 11:18 AM > To: Multiple recipients of list ORACLE-L > > > Iam planning to copy 18-40Million rows thru CTAS!! > My question is which one > is efficient, CTAS or using cursor in pl/sql > Procedure!! > > thanks > peter. > > > >From: Abdul Aleem <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >Subject: RE: Transferring data from one table to > another > >Date: Sun, 11 Aug 2002 23:23:19 -0800 > > > >Thank you, Amjad, > >The problem is that then I have to write a > procedure for each of the > >tables. > >I was looking for something that could be set at > database level and would > >apply to every table. > > > >Aleem > > > > -Original Message- > >Sent:Monday, August 12, 2002 10:43 AM > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Transferring data from one table to > another > > > >well if u wanna commit after 1000 records u could > very well use a cursor > >and within the loop keep a counter which will > indicate the no. of records > >inserted...upon reaching 1000 records just commit > and reinitialize the > >counter.. > > > >i have written the "Pseudo" code below: > > > >declare > > cursor c1 is > > SELECT * from schema2.abc; > >cntr number := 0; > >begin > > for c1_abc in c1 loop > > insert into schema1.abc values contained in > c1_abc; > > cntr := cntr +1; > > if (cntr = 1000)then > > cntr := 0; > > commit; > > end if; > > end loop; > >/* the following commit is 4 last set of records > that might not b > >commited*/ > >commit; > >end; > > > >rgds, > >Ams. > >www.medicomsoft.com > > > > > > > >-Original Message- > >Sent: Monday, August 12, 2002 8:23 AM > >To: Multiple recipients of list ORACLE-L > > > > > >Hi, > > > >We are transferring data from one table in a schema > to another table in > >another schema with identical fields using > >INSERT INTO schema1.abc (SELECT * from schema2.abc) > >The source table has 1.6 million records. The > tablespace increases to > >consume full disk space and yet seems to be > demanding more so the operation > >doesn't complete. > > > >Is there a possibility to process commit after > every 1,000 records? > >Is there any other way of doing it? > > > >TIA! > > > >Aleem > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >-- > >Author: Abdul Aleem > > 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: Amjad Saiyed > > 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: Abdul Aleem > > 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). > > > > > _ > Join the world's largest e-mail service with MSN > Hotmail. > http://www.hotmail.com > > -- > Please see the official ORA
RE: RMAN: How to restore backup to a different box
Helmut, Check out the newname command that can be used when duplicating a database set newname for datafile 1 TO '$ORACLE_HOME/dbs/newdb_data_01.f'; set newname for datafile 2 TO '$ORACLE_HOME/dbs/newdb_data_02.f'; set newname for datafile 3 TO '$ORACLE_HOME/dbs/newdb_data_11.f'; set newname for datafile 4 TO '$ORACLE_HOME/dbs/newdb_data_12.f'; set newname for datafile 5 TO '$ORACLE_HOME/dbs/newdb_data_21.f'; set newname for datafile 6 TO '$ORACLE_HOME/dbs/newdb_data_22.f'; HTH John -Original Message- Sent: 12 August 2002 17:24 To: Multiple recipients of list ORACLE-L Helmut - I am struggling with this myself. What is your goal? Are you just trying to create a test database, or trying to perform a disaster recovery test? For creating a test database, take a look at the RMAN DUPLICATE command. As to your specific question of changing the file path, the RMAN SWITCH command should be able to do what you need. Another idea is to have your Unix system administrator create the file paths you need so that you don't have to perform any changes in RMAN. For example, if you need a path /u01, create a file path or mount point with that name on the test system. One less thing to wrestle with in RMAN. I do not find that RMAN takes kindly to changes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Houston, do I have a problem?
Joe, This was just a 'review' of the top waits in the database. I can not draw any conclusions as to what the problem is, yet. But, the I/O subsystem appears to be stressed a bit. Why, who, when and how will not be visible at this level. Thanks. - Kirti -Original Message- Sent: Sunday, August 11, 2002 7:13 PM To: Multiple recipients of list ORACLE-L Kirti, since i'm still not up to speed on the "Wait event concept". What should i see as a problem in your report. thanks, joe Deshpande, Kirti wrote: >This is not a joke.!!! > >This is from a business critical production database that I was asked to >'review' past Friday. > >The report is from v$system_event taken at 10:30am, Aug 9, 2002. >The server (and database) was bounced on Aug 4, 2002 at 9:20am. > >This was the 1st time I was logging into this database. > >SQL> / > >EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED >AVERAGE_WAIT >--- --- -- --- > >control file parallel write 143933 0 4080356626 >28349.0001 >db file scattered read 12540695 0 1.2254E+10 >977.107332 >buffer busy waits 10740450 36 8193235928 >762.839167 >SQL*Net message from client 180769027 0 9.9561E+10 >550.761199 >db file sequential read 298968127 0 1.1839E+11 >395.99129 >enqueue 13500 6435 2036785 >150.872963 >SQL*Net more data from client 52227948 0 4093231165 >78.3724294 >free buffer waits16 4 795 >49.6875 >log file switch completion 804 43 16263 >20.2276119 >log buffer space977 05409 >5.53633572 >control file single write17 0 51 >3 >db file parallel write 1749695 0 2935317 >1.67761638 >db file parallel read 8149 0 13484 >1.65468156 >log file single write 1024 0 701 >.684570313 >latch free 20070341616763 1054137 >.525221297 >log file sync 1366242560 526049 >.385033545 >SQL*Net message from dblink 1514480 0 451351 >.298023744 >log file sequential read 405415 0 82877 >.204425095 >SQL*Net break/reset to dblink10 0 2 >.2 >log file parallel write 2025192 7 293332 >.144841576 >SQL*Net break/reset to client 28113 03221 >.114573329 >db file single write320 0 36 >.1125 >SQL*Net more data from dblink447044 0 11375 >.025444923 >SQL*Net more data to client11770996 0 75680 >.006429362 >control file sequential read 554851 03261 >.005877254 >SQL*Net more data to dblink1076 0 5 >.00464684 >buffer deadlock1045 1029 1 >.000956938 >SQL*Net message to dblink 1514485 0 456 >.000301092 >SQL*Net message to client 180769119 0 48736 >.000269604 > >29 rows selected. > >SQL> > >Here i
RE: RMAN: How to restore backup to a different box
Helmut - I am struggling with this myself. What is your goal? Are you just trying to create a test database, or trying to perform a disaster recovery test? For creating a test database, take a look at the RMAN DUPLICATE command. As to your specific question of changing the file path, the RMAN SWITCH command should be able to do what you need. Another idea is to have your Unix system administrator create the file paths you need so that you don't have to perform any changes in RMAN. For example, if you need a path /u01, create a file path or mount point with that name on the test system. One less thing to wrestle with in RMAN. I do not find that RMAN takes kindly to changes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Transferring data from one table to another
Do you want to copy or move ? If move then partition the target table and do an exchange partition, is the faster way to move data. --- "Ji, Richard" <[EMAIL PROTECTED]> a écrit : > How about turn off logging and drop indexes on the > target table. Do insert > with the APPEND hint. Re-create index. > > -Original Message- > Sent: Monday, August 12, 2002 11:18 AM > To: Multiple recipients of list ORACLE-L > > > Iam planning to copy 18-40Million rows thru CTAS!! > My question is which one > is efficient, CTAS or using cursor in pl/sql > Procedure!! > > thanks > peter. > > > >From: Abdul Aleem <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >Subject: RE: Transferring data from one table to > another > >Date: Sun, 11 Aug 2002 23:23:19 -0800 > > > >Thank you, Amjad, > >The problem is that then I have to write a > procedure for each of the > >tables. > >I was looking for something that could be set at > database level and would > >apply to every table. > > > >Aleem > > > > -Original Message- > >Sent:Monday, August 12, 2002 10:43 AM > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Transferring data from one table to > another > > > >well if u wanna commit after 1000 records u could > very well use a cursor > >and within the loop keep a counter which will > indicate the no. of records > >inserted...upon reaching 1000 records just commit > and reinitialize the > >counter.. > > > >i have written the "Pseudo" code below: > > > >declare > > cursor c1 is > > SELECT * from schema2.abc; > >cntr number := 0; > >begin > > for c1_abc in c1 loop > > insert into schema1.abc values contained in > c1_abc; > > cntr := cntr +1; > > if (cntr = 1000)then > > cntr := 0; > > commit; > > end if; > > end loop; > >/* the following commit is 4 last set of records > that might not b > >commited*/ > >commit; > >end; > > > >rgds, > >Ams. > >www.medicomsoft.com > > > > > > > >-Original Message- > >Sent: Monday, August 12, 2002 8:23 AM > >To: Multiple recipients of list ORACLE-L > > > > > >Hi, > > > >We are transferring data from one table in a schema > to another table in > >another schema with identical fields using > >INSERT INTO schema1.abc (SELECT * from schema2.abc) > >The source table has 1.6 million records. The > tablespace increases to > >consume full disk space and yet seems to be > demanding more so the operation > >doesn't complete. > > > >Is there a possibility to process commit after > every 1,000 records? > >Is there any other way of doing it? > > > >TIA! > > > >Aleem > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >-- > >Author: Abdul Aleem > > 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: Amjad Saiyed > > 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: Abdul Aleem > > 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). > > > > > _ > Join the world's largest e-mail service with
RE: Transferring data from one table to another
Title: RE: Transferring data from one table to another CTAS with nologging. Could create a simple script to do this. -Original Message- From: Peter R [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Subject: RE: Transferring data from one table to another Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. >From: Abdul Aleem <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Transferring data from one table to another >Date: Sun, 11 Aug 2002 23:23:19 -0800 > >Thank you, Amjad, >The problem is that then I have to write a procedure for each of the >tables. >I was looking for something that could be set at database level and would >apply to every table. > >Aleem > > -Original Message- >Sent: Monday, August 12, 2002 10:43 AM >To: Multiple recipients of list ORACLE-L >Subject: RE: Transferring data from one table to another > >well if u wanna commit after 1000 records u could very well use a cursor >and within the loop keep a counter which will indicate the no. of records >inserted...upon reaching 1000 records just commit and reinitialize the >counter.. > >i have written the "Pseudo" code below: > >declare > cursor c1 is > SELECT * from schema2.abc; >cntr number := 0; >begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000) then > cntr := 0; > commit; > end if; > end loop; >/* the following commit is 4 last set of records that might not b >commited*/ >commit; >end; > >rgds, >Ams. >www.medicomsoft.com > > > >-Original Message- >Sent: Monday, August 12, 2002 8:23 AM >To: Multiple recipients of list ORACLE-L > > >Hi, > >We are transferring data from one table in a schema to another table in >another schema with identical fields using >INSERT INTO schema1.abc (SELECT * from schema2.abc) >The source table has 1.6 million records. The tablespace increases to >consume full disk space and yet seems to be demanding more so the operation >doesn't complete. > >Is there a possibility to process commit after every 1,000 records? >Is there any other way of doing it? > >TIA! > >Aleem >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Abdul Aleem > 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: Amjad Saiyed > 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: Abdul Aleem > 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). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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
Re: RMAN: How to restore backup to a different box
Check the RMAN docs; the process for doing this is well described there. Basically, you either use a parameter in the init file (when you can do wildcard path changes) or use set commands in the rman recover script. Glenn Stauffer On Monday 12 August 2002 11:48 am, you wrote: > Hi! > > I need to restore a RMAN backup (full db backup) of our production database > to a test machine, which has a different file system layout. > > Production box: /u01, /u02, /u03, /u04, /u05 > > On the test box, all the data files need to be restored under /export > (i.e. /export/u01, /export/u02 etc.) > > How do I achieve this using RMAN? > > This is 8.1.7 on Solaris. > > Thanks, > Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Stauffer 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).
Error while invoking Email from PL/SQL
Hi All, I am trying to send an Email using PL/SQL. (Oracle 8.1.7 and Solaris 5.8). I tried to run the initplsj.sql and got the following error. Can you guys tell me what should I do to correct this? SQL> @initplsj.sql call dbms_java.set_output(1) * ERROR at line 1: ORA-06576: not a valid function or procedure name call dbms_java.loadjava('-resolve plsql/jlib/plsql.jar') * ERROR at line 1: ORA-06576: not a valid function or procedure name And then I tried to run loadjava -user sys/*** plsql.jar, again to no avail Thanks in advance K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: karthikeyan S 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: SQL*Loader : How can I load the Line Feed as part of data
check the utilities documentation, specifically the Field List section and the "terminated by" clause.. you can override the default terminator of line feed. You can also concatenate multiple lines together --- "Mandal, Ashoke" <[EMAIL PROTECTED]> wrote: > Greetings, > > We have one column in a table and this column need to store data with > Line Feed, denoted by \x0d\x0a in the following row. This data comes > from Sybase database. If \x0d\x0a cannot be loaded as Line Feed > through the SQL*Loader then how can we do it? > > Any help is appreciated. > Thanks, > Ashoke > > Data file contains the following one record : > > crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in > the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction > Atrial Pacing Minimum Interval and x0d0a Manual Therapy Atrial 50 Hz > Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is > conducted in the Ventricle, this parameter encodes to Manual Therapy > 50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a > Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and > 5.|373441192 > > > Sample Control File : > LOAD DATA > INFILE param.dat "str X'0d0a'" > BADFILE param.bad > append into TABLE param_ashoke > TRAILING NULLCOLS > (DEVICE CHAR(80) TERMINATED BY "|" > ,PARAM CHAR(80) TERMINATED BY "|" > ,PARAM_TYPE CHAR(20) TERMINATED BY "|" > ,SW_NOTE CHAR(4000) TERMINATED BY "|" > ,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY "|" > ,FW_VAR_BASE CHAR(80) TERMINATED BY "|" > ,FW_VAR_MEMBER CHAR(80) TERMINATED BY "|" > ,BIT_OFFSET CHAR(38) TERMINATED BY "|" > ,BIT_SIZE CHAR(38) TERMINATED BY "|" > ,FW_ENCODE_DECODE CHAR(20) TERMINATED BY "|" > ,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY "|" > ,ALIAS CHAR(80) TERMINATED BY "|" > ,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE) > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mandal, Ashoke > 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!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Transferring data from one table to another
How about turn off logging and drop indexes on the target table. Do insert with the APPEND hint. Re-create index. -Original Message- Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. >From: Abdul Aleem <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Transferring data from one table to another >Date: Sun, 11 Aug 2002 23:23:19 -0800 > >Thank you, Amjad, >The problem is that then I have to write a procedure for each of the >tables. >I was looking for something that could be set at database level and would >apply to every table. > >Aleem > > -Original Message- >Sent: Monday, August 12, 2002 10:43 AM >To:Multiple recipients of list ORACLE-L >Subject: RE: Transferring data from one table to another > >well if u wanna commit after 1000 records u could very well use a cursor >and within the loop keep a counter which will indicate the no. of records >inserted...upon reaching 1000 records just commit and reinitialize the >counter.. > >i have written the "Pseudo" code below: > >declare > cursor c1 is > SELECT * from schema2.abc; >cntr number := 0; >begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000)then > cntr := 0; > commit; > end if; > end loop; >/* the following commit is 4 last set of records that might not b >commited*/ >commit; >end; > >rgds, >Ams. >www.medicomsoft.com > > > >-Original Message- >Sent: Monday, August 12, 2002 8:23 AM >To: Multiple recipients of list ORACLE-L > > >Hi, > >We are transferring data from one table in a schema to another table in >another schema with identical fields using >INSERT INTO schema1.abc (SELECT * from schema2.abc) >The source table has 1.6 million records. The tablespace increases to >consume full disk space and yet seems to be demanding more so the operation >doesn't complete. > >Is there a possibility to process commit after every 1,000 records? >Is there any other way of doing it? > >TIA! > >Aleem >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Abdul Aleem > 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: Amjad Saiyed > 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: Abdul Aleem > 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). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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 l
RMAN: How to restore backup to a different box
Title: RMAN: How to restore backup to a different box Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut
RE: process size limitation on HP-UX
Title: process size limitation on HP-UX maxdsiz kernel parameter (default 64 Meg). Regards -Original Message-From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: segunda-feira, 12 de Agosto de 2002 15:48To: Multiple recipients of list ORACLE-LSubject: process size limitation on HP-UX When 32 bit Oracle (8.0.5) is running under hp-ux 11 (64 bit), is the shadow process size limited by the 'maxdsiz' kernal parameter (defaults to 64 Meg) or the 'maxdsiz_64' kernel parameter, which default to 1 Gig? Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
Re: interMedia Text
As you are running 8.1.7 you should not need the extproc_connection_data entry. What you need is that the LD_LIBRARY_PATH includes $ORACLE_HOME/ctx/lib in the environment *before* you start the Listener. The listener.ora entries are the regular entries for the database SIDs. Hemant At 03:48 AM 12-08-02 -0800, you wrote: >Dear List, >We have :- Solaris SunOs 5.8, Oracle 8.1.7 >I have created three databases using the dbassist tool and I included, as >one of the installation options, InterMedia. >Having read the installation guides (including post installation for Oracle >InterMedia) and the Oracle interMedia Text - 8.1.5 Overview "Post >Installation Setup" I have the following questions :- >1) Having amended the "listener.ora" and "tnsnames.ora" files I'm not sure >if I have done this correctly because the "Post Installation Setup" refers >to adding ONE entry (for extproc_connection_data) in the tnsnames.ora file >for A database. >But I have three, so I have entered this SID specific info. three times, is >this correct ? >2) Having amended the listener.ora file to contain three extra entries is >this correct ? >3) I remember one of the NET8 classes I took, where the instructor insisted >that we amend these files with great caution and in particular to the layout >of entries. e.g. the number of spaces etc. etc. Well the new entries I added >don't conform exactly to the already existing entries, here is a snip of my >listener.ora file >SID_LIST_LISTENER (SID_LIST (SID_DESC (SID_NAME LSExtProc) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (PROGRAM xtproc) > ) > (SID_DESC (GLOBAL_DBNAME ATREP) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (SID_NAME ATREP) > ) > (SID_DESC (GLOBAL_DBNAME QPROD01) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (SID_NAME QPROD01) > ) > (SID_DESC (GLOBAL_DBNAME QTEST01) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (SID_NAME QTEST01) > ) > (SID_DESC SID_NAME p_agt1) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 >7/ctx/lib) > (PROGRAM xtproc) > ) > (SID_DESC SID_NAME p_agt2) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 >7/ctx/lib) > (PROGRAM xtproc) > ) > (SID_DESC SID_NAME p_agt3) > (ORACLE_HOME u01/app/oracle/product/8.1.7) > (ENVS D_LIBRARY_PATH01/app/oracle/product/8.1 >7/ctx/lib) > (PROGRAM xtproc) > ) > ) >You can see that the SID_DESC entries layout are slightly different that >those earlier, is this OK? > >Thanks in advance, and sorry in advance if any of the above questions are >dumb, but I'm a bit stuck on this! > >best regards, > >Ron > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: MCUK > 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). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale 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: Transferring data from one table to another
Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. >From: Abdul Aleem <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Transferring data from one table to another >Date: Sun, 11 Aug 2002 23:23:19 -0800 > >Thank you, Amjad, >The problem is that then I have to write a procedure for each of the >tables. >I was looking for something that could be set at database level and would >apply to every table. > >Aleem > > -Original Message- >Sent: Monday, August 12, 2002 10:43 AM >To:Multiple recipients of list ORACLE-L >Subject: RE: Transferring data from one table to another > >well if u wanna commit after 1000 records u could very well use a cursor >and within the loop keep a counter which will indicate the no. of records >inserted...upon reaching 1000 records just commit and reinitialize the >counter.. > >i have written the "Pseudo" code below: > >declare > cursor c1 is > SELECT * from schema2.abc; >cntr number := 0; >begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000)then > cntr := 0; > commit; > end if; > end loop; >/* the following commit is 4 last set of records that might not b >commited*/ >commit; >end; > >rgds, >Ams. >www.medicomsoft.com > > > >-Original Message- >Sent: Monday, August 12, 2002 8:23 AM >To: Multiple recipients of list ORACLE-L > > >Hi, > >We are transferring data from one table in a schema to another table in >another schema with identical fields using >INSERT INTO schema1.abc (SELECT * from schema2.abc) >The source table has 1.6 million records. The tablespace increases to >consume full disk space and yet seems to be demanding more so the operation >doesn't complete. > >Is there a possibility to process commit after every 1,000 records? >Is there any other way of doing it? > >TIA! > >Aleem >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Abdul Aleem > 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: Amjad Saiyed > 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: Abdul Aleem > 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). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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: Transferring data from one table to another
Did you check out the SQL*Plus COPY command? Specifically in conjunction with SET ARRAYSIZE and SET COPYCOMMIT settings... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, August 12, 2002 1:23 AM > Thank you, Amjad, > The problem is that then I have to write a procedure for each of the tables. > I was looking for something that could be set at database level and would > apply to every table. > > Aleem > > -Original Message- > Sent: Monday, August 12, 2002 10:43 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Transferring data from one table to another > > well if u wanna commit after 1000 records u could very well use a cursor > and within the loop keep a counter which will indicate the no. of records > inserted...upon reaching 1000 records just commit and reinitialize the > counter.. > > i have written the "Pseudo" code below: > > declare > cursor c1 is > SELECT * from schema2.abc; > cntr number := 0; > begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000) then > cntr := 0; > commit; > end if; > end loop; > /* the following commit is 4 last set of records that might not b commited*/ > commit; > end; > > rgds, > Ams. > www.medicomsoft.com > > > > -Original Message- > Sent: Monday, August 12, 2002 8:23 AM > To: Multiple recipients of list ORACLE-L > > > Hi, > > We are transferring data from one table in a schema to another table in > another schema with identical fields using > INSERT INTO schema1.abc (SELECT * from schema2.abc) > The source table has 1.6 million records. The tablespace increases to > consume full disk space and yet seems to be demanding more so the operation > doesn't complete. > > Is there a possibility to process commit after every 1,000 records? > Is there any other way of doing it? > > TIA! > > Aleem > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Abdul Aleem > 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: Amjad Saiyed > 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: Abdul Aleem > 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Link Error - More Info
Thanks Jared. Looks like I will have to use some other method besides copy. It would have been good since it had the auto-commit on it. Oh well. -Original Message- Sent: Friday, August 09, 2002 8:18 PM To: Multiple recipients of list ORACLE-L SQL> set arraysize 1 SQL> set copycommit 1000 SQL> copy from kgel/vinotamu@nxtp - > insert nxtp.temp_mgh - > using - > select * from rrs$.temp_mgh Array fetch/bind size is 1. (arraysize is 1) Will commit after every 1000 array binds. (copycommit is 1000) Maximum long size is 80. (long is 80) select * from rrs$.temp_mgh * Error in SELECT statement: ORA-01002: fetch out of sequence Kevin, This appears to be a well known problem. Two entries from MetaLink: SQL*Plus Technical Forum From: Gorm Heilskov 12-Jun-01 19:58 Subject: ORA-01002: fetch out of sequence for Copy statement ORA-01002: fetch out of sequence for Copy statement I receive an ORA-01002 when trying to use the copy statement on an 8.0.4 database on Netware from an 8.1.7 client. The copy statement runs fine on an 8.1.7 database on Windows 2000 using an 8.1.7 client. It also works fine using an 8.0.5 client. What is preventing the copy statement from working? From: Oracle, Anil Shenoy 15-Jun-01 07:32 Subject: Re : ORA-01002: fetch out of sequence for Copy statement Hi, A bug with no 644413 has been filed on this and has been fixed in 8.1.5 and 8.0.6.1. I cannot file a backport request as 8.0.4 is desupported. However you can use the workaround as below 1) Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE' ie: SQL> create database link connect to identified by 2) Create a new table using 'CREATE TABLE' ie: SQL> create table as select * from @ or 2) Insert data into an existing table using 'INSERT' ie: SQL> insert into select * from @ Regards, Anil Oracle Support Services Bookmark Fixed font Go to End Doc ID: Note:110364.1 Subject: Workaround for ORA-1002 on COPY COMMAND from 8.Xto 8.X Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 26-MAY-2000 Last Revision Date: 22-JAN-2002 Problem Description: You are using the COPY command to copy data from one 8.x database to another 8.x database. You receive an ORA-01002 error. ORA-01002: fetch out of sequence Cause: This may be caused by fetching from a 'select for update' cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error. You see there are several bugs on the issue but not all of them are included in patchsets or have fixes. In this example you are using COPY from 8.1.6 to 8.0.6: Testcase: SQL> select INSTANCE_NAME from v$instance; INSTANCE_NAME V816 SQL> select INSTANCE_NAME from v$instance@V806; INSTANCE_NAME V806 SQL> copy from [EMAIL PROTECTED] - > insert copy2 using select * from copy1; select * from copy1 * Error in SELECT statement: ORA-1002: fetch out of sequence Solution Description: = Use the following Workaround: 1. Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE' SQL> create database link connect to identified by @ 'Password' using 'SID'; 2. Create a new table using 'CREATE TABLE' SQL> create table as select * from @ - OR - 2. Insert data into an existing table using 'INSERT' SQL> insert into select * from @ Example: SQL> select INSTANCE_NAME from v$instance@V806; INSTANCE_NAME V806 SQL> create table copy806 as select * from copy1@V806; Table created. SQL> insert into copy806 select * from copy1@V806; 64 rows created. References: === [BUG:903258] ORA-1002 COPYING FROM A REMOTE DATABASE Search Words: = ORA-1002 SQL*Plus . Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists ---
Re: Unix Solaris forum.
I looked for www.sunmangers.org but it doesnt work? Can you please re-check the reference. Thanks. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, August 11, 2002 9:18 PM > www.sunmangers.org will do it for you > > Cheers > > > -- > = > Peter McLarty E-mail: [EMAIL PROTECTED] > Technical ConsultantWWW: http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 3461 > Brisbane, AustraliaMobile: +61 (0)402 094 238 > Facsimile: +61 (0)7 3303 3048 > = > A great pleasure in life is doing what people say you cannot do. > > - Walter Bagehot (1826-1877 British Economist) > = > Mincom "The People, The Experience, The Vision" > > = > > This transmission is for the intended addressee only and is confidential > information. If you have received this transmission in error, please > delete it and notify the sender. The contents of this e-mail are the > opinion of the writer only and are not endorsed by the Mincom Group of > companies unless expressly stated otherwise. > > > > > > > "Chuan Zhang" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 12-08-2002 01:03 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Fax to: > Subject:Unix solaris forum. > > > > > Hi, DBAs, > > Could anyone recommend a good unix solaris discussion/news group for me? > > > Thanks, > > Chuan > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta 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: How tocheck the dir existence on a remote server
Srinivas - how about this? #!/bin/ksh if rsh srvr27 'ls /export/home/oracle' then echo "existing" else echo "not existing" fi -Original Message- Sent: Monday, August 12, 2002 5:53 AM To: Multiple recipients of list ORACLE-L Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo "existing" else echo "not existing" fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: STEVE OLLIG 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: E business Suite 11i - for Apps DBA's
The 11.5.6 and 11.5.7 versions of the TRMs have recently been made available online at http://etrm.oracle.com/pls/etrmlatest/etrm.etrmnav.show These include the ERDs for each module. Complicated reading =) HTH, Mike Hately Oracle DBA -Original Message- Sent: 12 August 2002 14:13 To: Multiple recipients of list ORACLE-L Maria, Unless things really changed with 11i, Oracle has never HAD an ERD for the Apps. That's one of the reasons customization is such an incredible chore. As for the data dictionary, the Technical Reference Manuals can be ordered from Oracle. Be prepared, they are hideously expensive. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Monday, August 12, 2002 2:48 AM To: Multiple recipients of list ORACLE-L Hello Application DBA's I'd like to know where I can get/buy E-business Suite 11i documentation with the ERD and data dictionary of the tables. Thank you so much. -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike 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*Loader : How can I load the Line Feed as part of data
Greetings, We have one column in a table and this column need to store data with Line Feed, denoted by \x0d\x0a in the following row. This data comes from Sybase database. If \x0d\x0a cannot be loaded as Line Feed through the SQL*Loader then how can we do it? Any help is appreciated. Thanks, Ashoke Data file contains the following one record : crm1d1p1|50 Hz Interval|Software Group|When the test is conducted in the Atrium, this parameter encodes to Manual Therapy 50 Hz Induction Atrial Pacing Minimum Interval and x0d0a Manual Therapy Atrial 50 Hz Induction Instruction Buffer Byte 7 and 9.\x0d\x0aWhen the test is conducted in the Ventricle, this parameter encodes to Manual Therapy 50 Hz Induction Ventricular Pacing Minimum Intervaland \x0d\x0a Manual Therapy 50 Hz Induction Instruction Buffer Byte 3 and 5.|373441192 Sample Control File : LOAD DATA INFILE param.dat "str X'0d0a'" BADFILE param.bad append into TABLE param_ashoke TRAILING NULLCOLS (DEVICE CHAR(80) TERMINATED BY "|" ,PARAM CHAR(80) TERMINATED BY "|" ,PARAM_TYPE CHAR(20) TERMINATED BY "|" ,SW_NOTE CHAR(4000) TERMINATED BY "|" ,SW_NOTE_CHECKSUM CHAR(38) TERMINATED BY "|" ,FW_VAR_BASE CHAR(80) TERMINATED BY "|" ,FW_VAR_MEMBER CHAR(80) TERMINATED BY "|" ,BIT_OFFSET CHAR(38) TERMINATED BY "|" ,BIT_SIZE CHAR(38) TERMINATED BY "|" ,FW_ENCODE_DECODE CHAR(20) TERMINATED BY "|" ,BIT_LEVEL_TRANS CHAR(80) TERMINATED BY "|" ,ALIAS CHAR(80) TERMINATED BY "|" ,GLOBAL_ID CHAR(38) TERMINATED BY WHITESPACE) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke 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).
process size limitation on HP-UX
Title: process size limitation on HP-UX When 32 bit Oracle (8.0.5) is running under hp-ux 11 (64 bit), is the shadow process size limited by the 'maxdsiz' kernal parameter (defaults to 64 Meg) or the 'maxdsiz_64' kernel parameter, which default to 1 Gig? Matt Adams - GE Appliances - [EMAIL PROTECTED] My computer beat me at chess, but I won when it came to kick boxing.
RE: Statspack archiving
Just a quick note to thank everyone for the contributions to this and the ODBC stuff I posted recently. Regards Lee -Original Message- Sent: 10 August 2002 20:53 To: Multiple recipients of list ORACLE-L The original question on this thread was for an automated "purge" for STATSPACK. I wrote this stored procedure based on the v8.1.7 version of the standard "sppurge.sql" script. I'd use that script, except I don't like the way it is called (i.e. range of SNAP_IDs). This stored procedure figures out the range of SNAP_IDs based on the parameter indicating the number of days of data to retain... Hope this helps -- as always, no warranties! --- begin included SQL*Plus script -- /** * File: sppurpkg.sql * Type: SQL*Plus script * Author: Tim Gorman (Evergreen Database Technologies, Inc.) * Date: 18Oct01 * * Description: * SQL*Plus script containing DDL commands to create the package * SPPURPKG, intended for use with STATSPACK from Oracle database * versions 8.1.7 and above. Adapted from the "sppurge.sql" script * which is included with standard STATSPACK v8.1.7, it is easier * to use because it can be called automatedly from the DBMS_JOB * package (instead of interactively as with "sppurge.sql") and it * takes only the number of days of STATSPACK data to retain * (instead of prompting for a begin/end range of SNAP_IDs, like * "sppurge.sql") * * After the package is created, then this script will submit the * procedure "SPPURPKG.RUN(14)" (i.e. purge data older than 14 * days) to run once per day. You may want to modify this, * depending on the volume of activity on the database(s) being * monitored by STATSPACK and the amount of storage you are * prepared to allocate to the PERFSTAT schema... * * Modifications: */ set echo on feedback on timing on verify on spool sppurpkg connect perfstat show user show release set termout off create or replace package SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER); -- end SPPURPKG; / set termout on show errors set termout off create or replace package body SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER) is -- cursor get_snaps(in_days IN INTEGER) is select s.rowid, s.snap_id, s.dbid, s.instance_number from stats$snapshot s, sys.v_$database d, sys.v_$instance i where s.dbid = d.dbid and s.instance_number = i.instance_number and s.snap_time < trunc(sysdate) - in_days; -- errcontext VARCHAR2(100); errmsg VARCHAR2(1000); save_module VARCHAR2(48); save_action VARCHAR2(32); -- begin -- errcontext := 'save settings of DBMS_APPLICATION_INFO'; dbms_application_info.read_module(save_module, save_action); dbms_application_info.set_module('SPPURPKG.PURGE', 'begin'); -- errcontext := 'open/fetch get_snaps'; dbms_application_info.set_action(errcontext); for x in get_snaps(in_days_older_than) loop -- errcontext := 'delete (cascade) STATS$SNAPSHOT'; dbms_application_info.set_action(errcontext); delete from stats$snapshot where rowid = x.rowid; -- errcontext := 'delete "dangling" STATS$SQLTEXT rows'; dbms_application_info.set_action(errcontext); delete from stats$sqltext where (hash_value, text_subset) not in (select /*+ hash_aj(ss) */ hash_value, text_subset from stats$sql_summary ss ); -- errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows'; dbms_application_info.set_action(errcontext); delete from stats$database_instance i where i.instance_number = x.instance_number and i.dbid= x.dbid and not exists (select 1 from stats$snapshot s where s.dbid= i.dbid and s.instance_number = i.instance_number and s.startup_time= i.startup_time ); -- errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows'; dbms_application_info.set_action(errcontext); delete from stats$statspack_parameter p where p.instance_number = x.instance_number and p.dbid= x.dbid and not exists (select 1 from stats$snapshot s where s.dbid= p.dbid and s.instance_number = p.instance_number ); -- errcontext := 'fetch/close get_snaps'; dbms_application_info.set_action(errcontext); -- end loop; -- errcontext := 'restore saved settings of DBMS_APPLICATION_INFO'; dbms_application_info.set_module(save_module, save_action); -- exception -- when OTHERS then errmsg := sqlerrm; dbms_application_info.set_module(save_module, save_action); raise_application_error(-2, errcontext || ': ' || errmsg); -- end PURGE; -- end SPPURPKG; / set termout on show errors variable jobno number; begin dbms_job.submit(:jobno, 'sppurpkg.purge(14);', sysdate+(1/1440), 'SYSDATE+1', TRU
Re: Unix Solaris forum.
...it's www.sunmanagers.org (rather than a forum for well-lit livestock feeders :-) )... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, August 11, 2002 10:18 PM > www.sunmangers.org will do it for you > > Cheers > > > -- > = > Peter McLarty E-mail: [EMAIL PROTECTED] > Technical ConsultantWWW: http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 3461 > Brisbane, AustraliaMobile: +61 (0)402 094 238 > Facsimile: +61 (0)7 3303 3048 > = > A great pleasure in life is doing what people say you cannot do. > > - Walter Bagehot (1826-1877 British Economist) > = > Mincom "The People, The Experience, The Vision" > > = > > This transmission is for the intended addressee only and is confidential > information. If you have received this transmission in error, please > delete it and notify the sender. The contents of this e-mail are the > opinion of the writer only and are not endorsed by the Mincom Group of > companies unless expressly stated otherwise. > > > > > > > "Chuan Zhang" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 12-08-2002 01:03 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Fax to: > Subject:Unix solaris forum. > > > > > Hi, DBAs, > > Could anyone recommend a good unix solaris discussion/news group for me? > > > Thanks, > > Chuan > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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: Transferring data from one table to another
Abdul In terms of committing every so many records, investigate the SQL*Net COPY command if you are not familiar with it. For transferring this many records, you may also want to investigate CREATE TABLE AS SELECT . . . . NOLOGGING Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, August 11, 2002 11:23 PM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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: DENNIS WILLIAMS 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).
What Oracle versions are supported on MS2000?
Can anyone tell me what Oracle versions are supported on MS2000? Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: E business Suite 11i - for Apps DBA's
Maria, Unless things really changed with 11i, Oracle has never HAD an ERD for the Apps. That's one of the reasons customization is such an incredible chore. As for the data dictionary, the Technical Reference Manuals can be ordered from Oracle. Be prepared, they are hideously expensive. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Monday, August 12, 2002 2:48 AM To: Multiple recipients of list ORACLE-L Hello Application DBA's I'd like to know where I can get/buy E-business Suite 11i documentation with the ERD and data dictionary of the tables. Thank you so much. -- Maria Aurora VT de la Vega OCP Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega 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: John Weatherman 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).
CLOB columns
Hi Guys, I am using a CLOB column in my database. Is there any way to fetch and see the entire content of the CLOB field? For (eg) If I have stored my resume in the CLOB colum (in a text/HTML format) then is there any way to display the entire content in the database or in some other editor? I tried DBMS_LOB.READ(locator_var,amount_var,offset_var,output_var) and DBMS_LOB.OPEN(locator_var,1); K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: karthikeyan S 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: Lock table table_name in exclusive mode - Performance gain?
>case, the app is very bad and we (me and another member on the list) >were tasked to pull it out of the ditch (my 42nd day straight on this on >back to back to back, etc 100/hr weeks, I want to hear the violins!). If >we were to have written it, there wouldn't be the need for some of these >large updates, violins only if you are NOT getting paid an hourly consulting fee. Otherwise, yea, you are working hard but getting paid for your time :) and yeah, knowing you and that other member of the list, it would have been done properly the first time as for that methodology on the update -- VERY slick! I like it, will keep this note to remind me when it's my turn to have to deal with someone else's code ... --- Larry Elkins <[EMAIL PROTECTED]> wrote: > All my comments are with regards to 8i. Might do things differently > with 9i > ;-) > > Familiar with the technique for doing large deletes? For example, you > want > to delete 40 million rows from a 100 million row table. It can often > times > be much more effective to do a CTAS (or insert append into an > existing > object) in parallel excluding the rows you want to delete. You can > then > truncate the source and throw the rows back in, or drop and rename > (taking > care of priv's and possible synonyms), or exchange partition, > whatever. > > The same technique can be applied to "updates". Numerous examples > where this > approach has been used with great success, I'll use one. In this > particular > example, we have a partitioned table, 162 million rows in a > partition, and > need to update 30 million rows in that partition with values from > another > table (bad, bad app, if designed correctly such a step wouldn't even > be > needed). We also have a "holding" table with the same structure. > We'll do an > insert append in parallel (implying append) outer joining to the > table > providing the values (using HJ). Use a decode to know whether or not > to > retain the value or if it should be "updated" if you found a matching > row. > Then, simply do an exchange partition no validate swapping your > "hold" table > with the partition that was to be updated. With the no validate it's > basically a dictionary operation not even having to verify the > values. Boom, > there you go, a big update done very quickly. And then truncate the > hold > table (paying attention to next extent issues after parallel insert > and ways > around them). In another recent example, we had to update a column > with a > constant for all rows in a 109 million row table (don't ask). This > type > insert and swap approach allowed it to be done in 10 to 12 minutes. > > So you might be able to apply similar techniques to your situation. > In our > case, the app is very bad and we (me and another member on the list) > were > tasked to pull it out of the ditch (my 42nd day straight on this on > back to > back to back, etc 100/hr weeks, I want to hear the violins!). If we > were to > have written it, there wouldn't be the need for some of these large > updates, > etc. But we don't have the luxury of completely rewriting the whole > thing > right now, so we apply the "update / delete becomes an insert and > exchange > partition approach" to selected areas experiencing severe performance > issues. And it works well. We had one process (cursor based of course > in the > coder's infinite wisdom updating 1 row at a time and committing every > 1000 > rows) that projected, by the rate of rows updated, to take 52.4 years > to > complete ;-). Now it takes 15 minutes. > > Just an idea that might be applicable in your situation. It's a > little > different, but not really much different than the CTAS (or insert > append) > approach that folks use for mass deletes. It's the same concept just > applied > to updates. And you can extend it to inserts / deletes. Don't know > you > situation, but maybe you do it in one statement. Seriously, I took a > few > thousand lines package doing multiple updates/deletes down to a > single > insert statement outer joining some tables and an exchange partition. > > Oh well, I'm delirious from a lack of sleep so the above might be a > bit > rambling. But I hope you get the idea. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > > [EMAIL PROTECTED] > > Sent: Saturday, August 10, 2002 12:38 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Lock table table_name in exclusive mode - Performance > gain? > > > > > > Anyone do any bench marking, know of any papers, or using "lock > table > > table_name in exclusive mode" to get a performance boost. > > > > I'm trying to figure out how to do 90,000,000 operations > > (add/change/delete) on the same table/partitions in a 4 hour > > period, and it > > looks like lighting will have to strike twice in the same place for > it to > > happen. > > > > Any other suggestions on how to cut down o
RE: Houston, do I have a problem?
Cary, Your last paragraph is *GREAT* advice. Every DBA should take it out, print it large, and paste it to the top of their screen. Focusing first on the highest priority item in the business model will win you the biggest supporters in the organization. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, August 12, 2002 2:38 AM To: Multiple recipients of list ORACLE-L This is an interesting report. I think the responses to it are even more interesting. One response admits confusion (which I think is a completely fair reaction). Another zeroes in knowingly on some specific details. If everyone had time to respond, I would expect a rash of differing opinions about what you should do first to fix this system... This kind of game is a fundamental part of using system-wide performance data. (The various ratio problems are just as relevant for system-wide data collected from the "wait interface" as they are from v$sys.) Don't lose hope if you look at Kirti's note and wonder, "so what's the point?" You cannot see everything that's wrong with a system from a report like this. I think in fact that you can know only two things from a v$system_event report: 1. If you know the "secret constants" (see www.hotsos.com/dnloads/1/constants), then you can see whether the database is spending heinously longer than "normal systems" at doing things. In this report, I would propose that an average single-block read latency of 9.7 seconds (977.107332 centiseconds), for example, is "heinously longer than normal." 2. If you know the "secret list" of things that databases should and shouldn't do, then you can see whether a database is doing a lot of things that it "shouldn't be doing." Databases, for example, shouldn't need to wait very often for 'buffer busy waits' waits, 'enqueue' waits, or 'latch free' waits. (Where's the url for *this* secret list? It's so simple that you don't really need one. Database should spend most of their time either idle, providing CPU service, or doing physical I/O. Not much else.) Sure, knowing these two things is worth something, but it leaves lots of good questions unanswered (*essential* questions, actually): a. Even if an Oracle kernel event is consuming "heinously longer-than-normal" elapsed times, or even if it is called "heinously too often," does it really matter? What if the event is called predominantly by unimportant business processes, and the long latencies don't impact anything important? Then you would be wasting your time fixing it (instead of fixing something important first). If you assume an event is important because it's prominent in a system-wide data collection and you then fix a huge performance problem, then you were actually just lucky. It won't happen this way every time. b. What if the database is providing the "right kinds of service in the right proportions?" How can you tell whether it's spending more time than it *could* have spent? For example, just because a program spends 90% of its response time on the CPU and 10% on a disk (kind of a "normal, healthy" profile), it is *not* okay if the response time is 10 hours when it should be 6 seconds. It's not the proportions that are important; it's the absolute response time. So... Is the HDS disk array a problem? Probably. But, it's possible--*likely*, actually--that an analyst could fix all the problems shown here and still have really slow applications. Why? Because several essential-but-slow programs on this system might not spend significant amounts of their response time waiting on any of the top 10 events in this list. We see it pretty often: people fix their system's "worst performance problems" and then find out that their work really didn't make a noticeable end-user impact. (I'm confident that Kirti won't end up in this trap, but that's because I trust him to exercise intuition and experience far beyond the scope of what can be learned from his v$system_event data.) The "wait interface" is an important tool, because it "finally" (well, since over ten years ago) allows us to see where a program spends its time. But to use that tool to see how a whole system has spent its time since instance startup has the same limitations as any other method that relies upon system-wide aggregated data. So, what should you look at to avoid performance improvement project ambiguities? Session-level data. Which session? As I mentioned last week, I believe the analyst should focus first upon sessions whose performance improvement would most significantly improve the business. That, in my opinion, is The Big Secret. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Kirti Sent: Sunday, August 11, 2002 5:43 PM To: Multiple recipi
RE: Physical Database Layout
Hi Manav, Sorry for a late reply. Actually I had left it for gurus to reply, and moreover I was enjoying the weekends! Since you are working on enterprise application, surely the organization which will use u application will have a DBA. So you only need to be concerned about the table structure, because all other things(relatd to physical layout of the DB) can be changed during installation time. Still just for ur knowledge a few pointers(a bit simplified). Again not an exhaustive list, just a few things which immediately come to my mind: * Always use Locally Managed Tablespaces(saves a lot of headache) * Try to spread ur data evenly to different tablespaces, so that those tablespaces can be on different disks * Keep index and data seperate * DB block size depends upon the data the queries are supposed to fetch. Larger the data more the DB Block size, lesser the data, smaller the block size * Don't worry about things like rollback segments because they can be added later by the production DBA * If your DB is going to be used more for insert/updates rather than for queries, use less indexes as indexes have an overhead while insert/update/delete * Write queries carefully, Use bind variables as much as possible. Read a bit about SQL tuning to know more abt the art of writing queries HTH Naveen -Original Message- Sent: Saturday, August 10, 2002 10:04 PM To: Multiple recipients of list ORACLE-L Hi Naveen, Thanks for taking the time out to reply. Yes I agree most of the programmers do not bother even to optimize the queries they write, but things get a tad complicated when you are responsible for an enterprise application and you are concerned with each aspect of the system. I understand optimization is an iterative process, but unless the physical layout of the DB itself is not right, the iterative cycle of optimization will not result in too much of an improvement. What i am looking for is a set of directives that need to be considered during the physical design, such as whether to use local tablespaces, the number of rollback segments, db block size, etc.. Thanks, Manav. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 09, 2002 10:58 AM > Hi Manav, > > I'm not a guru, but a programmer turned DBA. I appreciate your concern for > optimum DB design, because most of the programmers don't even care for the > optimization of SQL Queries they write. > > Oracle gives you a lot of control over the way you want to optimize your DB, > but that also means that you have to learn a lot of things(a lot!). > > The gurus in this list are so experienced that they know the Paramemters and > other things like people like you and me remember our friends' phone numbers. > It all comes with experience. > > As for you, I'll suggest that if you can't get a DBA and want to do a few > things yourself(which a DBA should be doing), you read some material on > Oracle Architecture and basics. > > As a programmer, all you can do is > > 1. Have a good normalized design > 2. Write your SQL queries properly > > Apart from that, most of the optimization is a DBAs job and you should stick > to whatever programming language you work on and learn more about that. > > There is lots of tuning material available on the net also. Just read it and > skip whatever you don't understand(you'll not understand most of them, and so > do I ) > > Good Luck, > Naveen > > -Original Message- > Sent: Friday, August 09, 2002 6:53 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > Unlike the gurus on the list, I'm just a programmer who happens to ensure > the DB structure is correct/valid, should hold its ground if an external > (read customer's) DBA goes through it with a microscope. Its not always > possible to have a full time DBA suggesting the layout, and moreover, most > of the programmers are expected to 'know' RBDMS anyway! > > I was hoping if the gurus here can provide me a list of parameters to be > taken into consideration (while doing the database design) to ensure that > the phsycial database layout itself is optimized for performance. Or if > anyone can just point me in the right direction, it'll help me a lot. > > Btw, Oracle has more than 200 initialization parameters, all affecting its > working in some or the other way. Do the gurus keep all of them at their > finger-tips? > > TIA, > Manav. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Manavendra Gupta > 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
interMedia Text
Dear List, We have :- Solaris SunOs 5.8, Oracle 8.1.7 I have created three databases using the dbassist tool and I included, as one of the installation options, InterMedia. Having read the installation guides (including post installation for Oracle InterMedia) and the Oracle interMedia Text - 8.1.5 Overview "Post Installation Setup" I have the following questions :- 1) Having amended the "listener.ora" and "tnsnames.ora" files I'm not sure if I have done this correctly because the "Post Installation Setup" refers to adding ONE entry (for extproc_connection_data) in the tnsnames.ora file for A database. But I have three, so I have entered this SID specific info. three times, is this correct ? 2) Having amended the listener.ora file to contain three extra entries is this correct ? 3) I remember one of the NET8 classes I took, where the instructor insisted that we amend these files with great caution and in particular to the layout of entries. e.g. the number of spaces etc. etc. Well the new entries I added don't conform exactly to the already existing entries, here is a snip of my listener.ora file SID_LIST_LISTENER (SID_LIST (SID_DESC (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (PROGRAM = extproc) ) (SID_DESC (GLOBAL_DBNAME = RATREP) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = RATREP) ) (SID_DESC (GLOBAL_DBNAME = CQPROD01) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = CQPROD01) ) (SID_DESC (GLOBAL_DBNAME = CQTEST01) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (SID_NAME = CQTEST01) ) (SID_DESC = (SID_NAME = ep_agt1) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = ep_agt2) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = ep_agt3) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (ENVS = LD_LIBRARY_PATH=/u01/app/oracle/product/8.1 7/ctx/lib) (PROGRAM = extproc) ) ) You can see that the SID_DESC entries layout are slightly different that those earlier, is this OK? Thanks in advance, and sorry in advance if any of the above questions are dumb, but I'm a bit stuck on this! best regards, Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MCUK 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: Bitmap Header in a Uniform LMT?
I have been looking at LMT recently I created a 10001M datafile at 8K block size for a 64K locally managed extents Noticed from dba_free_space that the largest contiguous free space was 3968M Dumped the file header and got the same results as Paul / Jeremiah but I did note that for a 8k block size there is a file header block, and then a space header block followed by 6 lmt bitmap blocks before the data blocks start I assume the figure of 3968M is the maximum no of bits that can be stored in single block and therefore the datafile for the sizes listed above could be a maximum of 3968M * 6? Am I on the correct track? John -Original Message- Sent: 09 August 2002 21:54 To: Multiple recipients of list ORACLE-L Raj - My apologies for being late with a reply. Your latest message prompted me to recall that Jeremiah Wilton investigated this (or a similar aspect) back in April. I went into Google and typed "LMT bitmap headers" (without the quotes), and it retrieved the discussion. I have inserted it below for your review in case you were not aware of that discussion. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g38Lpc327439 for <[EMAIL PROTECTED]>; Mon, 8 Apr 2002 17:51:38 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA08313; Mon, 8 Apr 2002 14:58:27 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0043EF61; Mon, 08 Apr 2002 13:03:20 -0800 Message-ID: <[EMAIL PROTECTED]> Date: Mon, 08 Apr 2002 13:03:20 -0800 To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> X-Comment: Oracle RDBMS Community Forum X-Sender: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Following on from my previous note: Jeremiah, >From your bitmap control, You have FF occurring 3 times followed by 3F which is 255, 255, 255, 63 which is 0011 So, least signficant bit first, 1100 which is used, used, ... (30 times) , free, free This corresponds with the first: 30 (the bit before the first free bit) Paul -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: '[EMAIL PROTECTED]' >From the 'Data Management and Storage Internal" notes, Bitmapped Tablespace File Structure A new bitmapped tablespace file has the following structure: File Header 1 block Bitmapped File Space Header 1 block Head portion of of Bitmap BlocksN blocks Useful file blocks U units (A unit is a number of blocks) Tail portion of Bitmap Blocks M blocks If a Unit = B blocks, then the total file size = 1 + 1 + N + U*B + M Bitmapped File Space Header .. (lots to type, I can if you really need it) Bitmap blocks have 2 parts : Bitmap control structure Vector Dump The fields in the bitmap control structure are: RelFNo: Relative file number to which the bitmap belongs BeginBlock: Which block number does the first bit represent Flag: Zero for permanent files, one for temp files First: Where to start looking for the free space (bit before first free bit) Free: Number of free slots (bits) in the bitmap (not the file) To read the bitmap, take each two-byte pair, least significant bit first. If there are not eight bits, pad to eight bits with zeroes. Hence 0x0F = 15 = . When written least significant bit first, the bitmap looks like this --> used, used, used, used, free, free, free, free Scanning for the first free extent will start at the 4th bit. HTH Paul -Original Message- Sent: Monday, April 08, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3,
RE: How tocheck the dir existence on a remote server
Try this rsh server_name "[[ -d /tmp ]] && echo "Is there" || echo "Is not there"" Works for me HTH Lee -Original Message- Sent: 12 August 2002 11:53 To: Multiple recipients of list ORACLE-L Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo "existing" else echo "not existing" fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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). * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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: /etc/system on Sun Solaris 8 for 8i and 9i
try reducing the number of processes in the 9i init.ora to see if you can start the database. if the error message is about "semaphore" or "semaphore sets" you will need to increase the semmni, semmns and semmnu parameters. Then you need to reboot the Sub box. The OS-specific documentation will explain what the semaphores are used for. --- [EMAIL PROTECTED] wrote: > When I start the 9i2 instance, the 8i3 instance could only establish > 2 max. > Unfortunetly, I forgot to log the error message. Yesterday, when I > checked > the error message on Oracle Doc, that sayed to be change the > /etc/system. > Please advice.. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, August 12, 2002 11:48 AM > > > > How do you know it ran out of resource? What's the error message > and when > > does this appear? During startup, during heavy use or when it's > just idle? > > > > ltiu > > > > On Sunday 11 August 2002 20:08, you wrote: > > > Dear All, > > > I've Sun machine that run Oracle 8.1.7. Recently I installed new > oracle > 9i2 > > > 64-bit on the same machine. But unfortunetly the old instance > (8.1.7) is > > > run out resource. I must set the /etc/system to higher value. But > it is > > > still the same. > > > Could you please someone give me advice about it, and what is the > impact > > > for my Sun Machine if I increase the /etc/system value?? Below is > my > > > /etc/system. > > > > > > thanks > > > > > > Ahmadsyah.Alghozi.Nugroho > > > ps: I'm feel sorry for my english.. :( > > > > > > === /etc/system === > > > set hme:hme_adv_autoneg_cap=0 > > > set hme:hme_adv_100fdx_cap=1 > > > set hme:hme_adv_100hdx_cap=0 > > > set hme:hme_adv_10hdx_cap=0 > > > set hme:hme_adv_10hdx_cap=0 > > > set ge:ge_adv_1000autoneg_cap=0 > > > set ge:ge_adv_1000fdx_cap=1 > > > set ge:ge_adv_1000hdx_cap=0 > > > forceload: drv/vxdmp > > > forceload: drv/vxio > > > forceload: drv/vxspec > > > forceload: sys/semsys > > > set semsys:seminfo_semmsl = 500 > > > set semsys:seminfo_semmap = 10 > > > set semsys:seminfo_semmni = 1200 > > > set semsys:seminfo_semmns = 5000 > > > set semsys:seminfo_semmnu = 30 > > > set semsys:seminfo_semopm = 100 > > > set semsys:seminfo_semume = 10 > > > set semsys:seminfo_semusz = 96 > > > set semsys:seminfo_semvmx = 32767 > > > set semsys:seminfo_semaem = 16384 > > > forceload: sys/shmsys > > > set shmsys:shminfo_shmmax = 4294967295 > > > set shmsys:shminfo_shmmni = 800 > > > set shmsys:shminfo_shmmin = 1 > > > set shmsys:shminfo_shmseg = 400 > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: ltiu > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how to check the existence of a dir on remote server
UNIX or Windows? What protocol to connect? etc. J{ On Monday 12 August 2002 12:48, you wrote: > __ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
SET ORACLE-L NOMAIL __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How tocheck the dir existence on a remote server
Hello All, Solaris: 2.8 Can somebody tell me how find whether a directory/file is existing on a remote server. I used the folloiwng script, but did not work. rsh if [ -d srvr27:/export/home/oracle ] then echo "existing" else echo "not existing" fi I put rsh after the [ -d . but that also did not work. I am presently on srvr28 and checking for the dir existtence on srvr27. Thanks in advance, Srinivas __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how to check the existence of a dir on remote server
__ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Transferring data from one table to another
Hi Aleem, well cant u pass the table name dynamically to the procedure for all the tables in the schema within an outer loop... n e ways iam not sure abt the database setting but logically speaking there shld b some property setting for some autocommit... rgds, Ams. -Original Message- Sent: Monday, August 12, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Thank you, Amjad, The problem is that then I have to write a procedure for each of the tables. I was looking for something that could be set at database level and would apply to every table. Aleem -Original Message- Sent: Monday, August 12, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Subject:RE: Transferring data from one table to another well if u wanna commit after 1000 records u could very well use a cursor and within the loop keep a counter which will indicate the no. of records inserted...upon reaching 1000 records just commit and reinitialize the counter.. i have written the "Pseudo" code below: declare cursor c1 is SELECT * from schema2.abc; cntr number := 0; begin for c1_abc in c1 loop insert into schema1.abc values contained in c1_abc; cntr := cntr +1; if (cntr = 1000)then cntr := 0; commit; end if; end loop; /* the following commit is 4 last set of records that might not b commited*/ commit; end; rgds, Ams. www.medicomsoft.com -Original Message- Sent: Monday, August 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are transferring data from one table in a schema to another table in another schema with identical fields using INSERT INTO schema1.abc (SELECT * from schema2.abc) The source table has 1.6 million records. The tablespace increases to consume full disk space and yet seems to be demanding more so the operation doesn't complete. Is there a possibility to process commit after every 1,000 records? Is there any other way of doing it? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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: Amjad Saiyed 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: Abdul Aleem 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: Amjad Saiyed 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: TO_CHAR got one space in front
check out the 'fm' format clause ie to_char(x,'fm'); hth connor --- shuan <[EMAIL PROTECTED]> wrote: > Hi all, > > Have you guys ever try this before: > > 1* select to_char(1.6,'0.') from dual > SQL> / > > TO_CHAR(1.6 > --- > 1.6000 > > Notice that got one space in front of "1.6000"? > > I'm using Oracle 8.0.5 in Linux. > > Thanks in advance. > = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: /etc/system on Sun Solaris 8 for 8i and 9i
When I start the 9i2 instance, the 8i3 instance could only establish 2 max. Unfortunetly, I forgot to log the error message. Yesterday, when I checked the error message on Oracle Doc, that sayed to be change the /etc/system. Please advice.. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, August 12, 2002 11:48 AM > How do you know it ran out of resource? What's the error message and when > does this appear? During startup, during heavy use or when it's just idle? > > ltiu > > On Sunday 11 August 2002 20:08, you wrote: > > Dear All, > > I've Sun machine that run Oracle 8.1.7. Recently I installed new oracle 9i2 > > 64-bit on the same machine. But unfortunetly the old instance (8.1.7) is > > run out resource. I must set the /etc/system to higher value. But it is > > still the same. > > Could you please someone give me advice about it, and what is the impact > > for my Sun Machine if I increase the /etc/system value?? Below is my > > /etc/system. > > > > thanks > > > > Ahmadsyah.Alghozi.Nugroho > > ps: I'm feel sorry for my english.. :( > > > > === /etc/system === > > set hme:hme_adv_autoneg_cap=0 > > set hme:hme_adv_100fdx_cap=1 > > set hme:hme_adv_100hdx_cap=0 > > set hme:hme_adv_10hdx_cap=0 > > set hme:hme_adv_10hdx_cap=0 > > set ge:ge_adv_1000autoneg_cap=0 > > set ge:ge_adv_1000fdx_cap=1 > > set ge:ge_adv_1000hdx_cap=0 > > forceload: drv/vxdmp > > forceload: drv/vxio > > forceload: drv/vxspec > > forceload: sys/semsys > > set semsys:seminfo_semmsl = 500 > > set semsys:seminfo_semmap = 10 > > set semsys:seminfo_semmni = 1200 > > set semsys:seminfo_semmns = 5000 > > set semsys:seminfo_semmnu = 30 > > set semsys:seminfo_semopm = 100 > > set semsys:seminfo_semume = 10 > > set semsys:seminfo_semusz = 96 > > set semsys:seminfo_semvmx = 32767 > > set semsys:seminfo_semaem = 16384 > > forceload: sys/shmsys > > set shmsys:shminfo_shmmax = 4294967295 > > set shmsys:shminfo_shmmni = 800 > > set shmsys:shminfo_shmmin = 1 > > set shmsys:shminfo_shmseg = 400 > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ltiu > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).