Weid exp/imp problem
Title: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table TABELLEN 37 rows imported . . importing table TABELLEN_ZUORDNUNGEN 28 rows imported . . importing table TMP$TEST 1 rows imported . . importing table TMP_FUNKTIONS_PARAMETER 0 rows imported . . importing table TMP_FUNKTIONS_SPALTEN 0 rows imported . . importing table USEREXIT 5 rows imported . . importing table USEREXIT_TYPE 3 rows imported . . importing table ZYKLUS 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_BNGR_FK FOREIGN KEY (BNGR_ID) REFERENCES BENUTZER_GRUPPEN (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_OW_FK FOREIGN K EY (OW_ID) REFERENCES OWNER (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: unused blocks BELOW HWM - Thanks
Title: RE: unused blocks BELOW HWM - Thanks I agree with your reasoning Dennis. I have long felt that one of the problems with the list is that the original raiser of the question should have some sort of responsibility for summarising the various responses and posting a short summarised reply showing the solutions(s) that worked for him/her This type of question where various solutions were proposed is the ideal candidate for this type of summarised response. I used to be on a Unix list and that strategy was used quite well there. John -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 19:10 To: Multiple recipients of list ORACLE-L Subject: RE: unused blocks BELOW HWM - Thanks Naaah, I'm just the proverbial lazy DBA. Gene received many replies, including it can't be done. Since he happened to point out that two of them worked and produced the same results (an excellent sanity check), I thought it would benefit everyone by telling us which ones worked. I realize that we could have each tried each of the strategies and discovered the two for ourselves, but my principle is if there is an easier way, why not? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 11:35 AM To: Multiple recipients of list ORACLE-L Uh, amen. Isn't that the purpose of the list? Or is there a *third* list, one on topic, a second off topic, and a third where all the answers really are? -Original Message- Sent: Tuesday, December 18, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Gene - Now that you've gotten your answer, would you mind to post both of the methods that you found to work so that the rest of us could learn? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 7:05 AM To: Multiple recipients of list ORACLE-L Hi. This is just to thank all who replied to my post. Based on what I read, I have got two different ways of calculating that number and they seem to produce the same result. thank you all (you know who you are) = __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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).
Re: Weid exp/imp problem
Hi You are most likely importing a table with foreign key prior to importing the primary key that it points to. you can do export with constraints N (I think) . Recreate the constraints after import. Or export your tables in the right order so the primary keys get imported first (use a parfile) Jack Daiminger, Helmut [EMAIL PROTECTED]@fatcity.com on 19-12-2001 09:25:20 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table TABELLEN 37 rows imported . . importing table TABELLEN_ZUORDNUNGEN 28 rows imported . . importing table TMP$TEST 1 rows imported . . importing table TMP_FUNKTIONS_PARAMETER 0 rows imported . . importing tableTMP_FUNKTIONS_SPALTEN 0 rows imported . . importing table USEREXIT 5 rows imported . . importing tableUSEREXIT_TYPE 3 rows imported . . importing table ZYKLUS 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_BNGR_FK FOREIGN KEY (BNGR_ID) REFERENCES BENUTZER_GRUPPEN (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_OW_FK FOREIGN K EY (OW_ID) REFERENCES OWNER (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. 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. === -- 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: Weid exp/imp problem
[EMAIL PROTECTED] wrote: Hi You are most likely importing a table with foreign key prior to importing the primary key that it points to. you can do export with constraints N (I think) . Recreate the constraints after import. Or export your tables in the right order so the primary keys get imported first (use a parfile) Jack Normally, the export dumps table CREATE statements, data, CREATE INDEX, CHECK, UNIQUE and PRIMARY KEY constraints, and foreign keys come at the very end, after ALL tables. It looks indeed like some primary keys were not defined on the target box in the first place. Helmut, you should take a look to DBA_CONSTRAINTS. Daiminger, Helmut [EMAIL PROTECTED]@fatcity.com on 19-12-2001 09:25:20 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table TABELLEN 37 rows imported . . importing table TABELLEN_ZUORDNUNGEN 28 rows imported . . importing table TMP$TEST 1 rows imported . . importing table TMP_FUNKTIONS_PARAMETER 0 rows imported . . importing tableTMP_FUNKTIONS_SPALTEN 0 rows imported . . importing table USEREXIT 5 rows imported . . importing tableUSEREXIT_TYPE 3 rows imported . . importing table ZYKLUS 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_BNGR_FK FOREIGN KEY (BNGR_ID) REFERENCES BENUTZER_GRUPPEN (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_OW_FK FOREIGN K EY (OW_ID) REFERENCES OWNER (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: hard disk configuration question
First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? WRT is an acronym for With regards to ;) And the advice about installing the O.S and patches on to these drives (maybe mirrored) seems sound to me. Second. If an 8k database block size is tiny, what is a large database block size? 8k is as large as I can go without raw file systems, isn't it? I *believe* (though have never seen) that you can have a 16k block size on HP, so maybe other platforms will allow this as well.. HTH Mark -Original Message- McClure Sent: 19 December 2001 02:25 To: Multiple recipients of list ORACLE-L Well we had our meeting with the Hitachi Sales critter, and engineer. I discovered that they were trying to push a 5 disk raid array and one hot spare at us, because my IT director was really trying to squeeze them on the price of my 10 18GB disk configuration. After the sales guy and engineer went on about how their 4 separate IO paths would make our raid 5 perform as well as any mirrored pair, I got to ask a few questions. Well it turns out that their RAID performs that well with extra large database block sizes. When I pointed out that we currently have a block size of 2k(I didn't build the db), and would be rebuilding the database on the new platform with 8k blocks, he said that with a small block size like 8k you will really get the best performance out of mirrors or a 1+0. I am sure the sales guy kicked him under the table ;-). The end result is this. We will probably go with the 6 36 GB DISKS. It flat out comes down to the cost of the whole system is just a little over budget, and the drives are where the adjustment gets made. I suspect that I will have much less trouble getting a few extra disks once the system is in the building. I may also mess with things a bit and configure a 1+0 and a mirrored pair. So after that discussion, and all your helpful input I still have two questions. First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? Second. If an 8k database block size is tiny, what is a large database block size? 8k is as large as I can go without raw file systems, isn't it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure 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: Mark Leith 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: Error accessing package DBMS_APPLICATION_INFO
Increase the large_pool_size. Regards. --- Hamid Alavi [EMAIL PROTECTED] wrote: List, Do you have any idea, can I flush the memory before running the package, I got this error when I wanted to run a package. Here is teh error message: Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Weid exp/imp problem
Title: Weid exp/imp problem This error is coming because at the time of adding foreign key constraint , referencing primary key is not available. You can avoid this error by creating a similar schema in the user in which you are taking the import and then import data with parameter "ignore=Y". Kranti -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 19, 2001 1:55 PMTo: Multiple recipients of list ORACLE-LSubject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table "USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Performance problem .... HELP :-(
Title: Performance problem HELP :-( Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian CLIENT SERVER TRACE call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.04 0 0 0 0 Execute 600 0.09 0.12 0 0 0 0 Fetch 1294 2448.98 2918.79 48 83060760 1200 694 --- -- -- -- -- -- -- total 1895 2449.07 2918.95 48 83060760 1200 694 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 12 SORT (ORDER BY) 0 FILTER 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 512750 NESTED LOOPS 769296 NESTED LOOPS 1869552 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 2541882 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 487200 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 36834 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 249381 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 445 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 36 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 48 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 12 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_A_PK' (UNIQUE) 12 NESTED LOOPS 24 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE) PROD DATABASE COPY ON OUR SERVER call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.07 0.08 0 0 0 0 Execute 482 0.20 0.25 0 0 0 0 Fetch 4573 86.71 89.05 93 1450283 0 4090 --- -- -- -- -- -- -- total 5056 86.98 89.38 93 1450283 0 4090 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 848 SORT (ORDER BY) 11660 FILTER 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 25596 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 25752 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 12869 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 16078 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 26131 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 37867 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 30064 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 41800 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 11736 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 11736 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 23396 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_B_PK' (UNIQUE) 667 NESTED LOOPS 8764 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 12620 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 848 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE)
installation oracle 8.1.0.5.6
hello everyone, I have oracle tools installed on my machine (forms, reports,...), and the SGBDR oracle 8.1.5.0.6 is installed on a server, I want to install it on my machine and I wanted to know if that is not going to influence on tools Tnx Lynda Haouhach Ingénieur système SONATRACH Email : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SIM/HAOUHACH 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: Deciding what columns to partition on
Jared Still wrote: On Tuesday 18 December 2001 12:55, Stephane Faroult wrote: I have never found the argument 'you scan a single partition' very compelling when your data is properly indexed. Stephane, This is assuming that you are using global indexes on your partitioned tables. If using local indexes and searching by something other than the partition key, all indexes will be scanned if used at all. Excellent point. Which rather goes AGAINST partitioning-as-a-performance-improvement-factor. In fact, I have never managed to do without global indexes. Users always seem to want to ask the wrong questions. When I wear my DBA cap I really like partitions. Otherwise I rather like them as an alternative to bitmap indexes (so to speak). -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: temporary tablespace estimation.
Hatzistavrou Giannis wrote: Dear All, I have the following questions to make: a) In a select statement where there is no order by or group by clause but parallel query servers are used, do these servers use temporary tablespace segments? Quite likely, if the volume requires it. 'Temporary space', as the name indicates, holds data in the course of processing, before Oracle is able to reach the final result. With parallel query servers, each one fetches a partial result set, which has to be stored somewhere before everything is merged. Even if you have no 'sort', you have a 'merge' phase. If that's you were thinking of, I do not believe that you get results on the fly from each of the parallel query servers, it would make communications much too complicated between the client and the server side. When you issue a 'fetch' call, you expect it from a single source and everything needs to be streamlined somewhere. b) how can someone estimate the temporary space used having only the select statement? I wonder! -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Performance problem .... HELP :-(
Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Weid exp/imp problem
You can handle this by two ways. The first is running the imp in two times. And the second imp with rows=n and ignore=y. On the other hand, you can create all the objects with the constraints INITIALLY DEFERRED. With the precaution that you can not undo the mode of the constraint with an alter table command. Therefore, you could do it with INITIALLY IMMEDIATE the default mode, but it would hurt the performance of the import. To get the stmts of creation, you can run the imp with show=y and get all the stmts or if you have 9i you could use DBMS_METADATA package. Regards. --- kranti pushkarna [EMAIL PROTECTED] wrote: This error is coming because at the time of adding foreign key constraint , referencing primary key is not available. You can avoid this error by creating a similar schema in the user in which you are taking the import and then import data with parameter ignore=Y. Kranti -Original Message- Sent: Wednesday, December 19, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table TABELLEN 37 rows imported . . importing table TABELLEN_ZUORDNUNGEN 28 rows imported . . importing table TMP$TEST 1 rows imported . . importing table TMP_FUNKTIONS_PARAMETER 0 rows imported . . importing tableTMP_FUNKTIONS_SPALTEN 0 rows imported . . importing table USEREXIT 5 rows imported . . importing tableUSEREXIT_TYPE 3 rows imported . . importing table ZYKLUS 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_BNGR_FK FOREIGN KEY (BNGR_ID) REFERENCES BENUTZER_GRUPPEN (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_OW_FK FOREIGN K EY (OW_ID) REFERENCES OWNER (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: temporary tablespace estimation.
FYI. as far as temporary tablespace estimation is concerned I have found 28365.1 in Metalink. -Original Message- Sent: Wednesday, December 19, 2001 13:16 To: Multiple recipients of list ORACLE-L Hatzistavrou Giannis wrote: Dear All, I have the following questions to make: a) In a select statement where there is no order by or group by clause but parallel query servers are used, do these servers use temporary tablespace segments? Quite likely, if the volume requires it. 'Temporary space', as the name indicates, holds data in the course of processing, before Oracle is able to reach the final result. With parallel query servers, each one fetches a partial result set, which has to be stored somewhere before everything is merged. Even if you have no 'sort', you have a 'merge' phase. If that's you were thinking of, I do not believe that you get results on the fly from each of the parallel query servers, it would make communications much too complicated between the client and the server side. When you issue a 'fetch' call, you expect it from a single source and everything needs to be streamlined somewhere. b) how can someone estimate the temporary space used having only the select statement? I wonder! -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Hatzistavrou Giannis 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).
Startup (URGENT)
After re-starting the database, the db application seems really slow after looking into it smon seems to be taking up 50% of the cpu !! What is smon doing , is there any way to get around this ?? I have tried leaving it for a while ! Regards -- Sajid Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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: Error accessing package DBMS_APPLICATION_INFO
Sorry this is more common if you use MTS. Try increasing shared pool and shared pool reserved size if the connections are dedicated or prespawn. Regards. --- Christian Trassens [EMAIL PROTECTED] wrote: Increase the large_pool_size. Regards. --- Hamid Alavi [EMAIL PROTECTED] wrote: List, Do you have any idea, can I flush the memory before running the package, I got this error when I wanted to run a package. Here is teh error message: Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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
using sql loader it is possible to populate fields with a sequnce e.g INTO TABLE dept (deptno sequence(2, 3), but i want to use my own sequence , i.e my_seq.nextval how is this done? reagards IA -- 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).
Loader commit problem
Hi ALL, I am using direct path method of SQL *Loader. I am testing for a data file of 1 records. Here when I test at onsite there was one bad file generated with 1 bad record. My log file showed rows loaded properly and 1 record not loaded due to data errors. When I check the count of the rows loaded into the table, it gives me a count of . So except for the 1 bad record all the rows are committed in the database. But when my colleagues ran the same file using the same control file at the client site, the log file showed rows loaded properly and 1 record not loaded due to data errors, but when I checked the count from the table, the count was 0. None of the rows were committed becuase of the 1 rejected record. Why does this happen ? Why are the rows not committed as it should have been as the SQl loader commits at the end of the loading. Is this beacuse of any setting in the parameter file or any other parameters missing in the control file of the SQL loader. Your urgent help will be really help helpful Regards For Stock Quotes, Finance News, Insurance, Tax Planners, Mutual Funds... Visit http://in.finance.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Techy=20Guy?= 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: hard disk configuration question
Sorry WRT = With respect to -Original Message- Sent: Tuesday, December 18, 2001 8:25 PM To: Multiple recipients of list ORACLE-L First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Holman, Rodney 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: Deciding what columns to partition on
Stephane, Thanks for the reply. I'm not sure that I understand what you mean about containment. I definitely understand about the maintenance part however. This database has been a real bear to maintain. I can't imagine it without partitioning in order to truncate off last quarter's unneeded data. Cherie Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: Deciding what columns to partition on Sent by: [EMAIL PROTECTED] om 12/18/01 02:55 PM Please respond to ORACLE-L [EMAIL PROTECTED] wrote: We are doing a redesign of our 200-Gig data warehouse in 9i. One of the things we're thinking about is changing which columns we partition on. Cherie, I am not sure that this really relates to your problem but if I think that you should consider partitioning more in relation to physical degradation (containment) and ease of maintenance (especially the ability to truncate partitions) than in terms of pure performance, especially on a 'clean' database. I have never found the argument 'you scan a single partition' very compelling when your data is properly indexed. I have carried out experiments recently and I was surprised to discover that the best results I had were gained by partitioning on a column which was updated (allowing for row migration) and not on the one I thought was the obvious candidate. Update was unsurprisingly twice as costly as in the other cases, but given the special mix of queries is still was the best overall, especially after a lot of inserts and deletes. Experiment carefully. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SQL Loader Load Problem
Kirti: Another person recommended this approach and it worked very nicely. To bad it's not in the book. Thanks much, Ken -Original Message- Sent: Tuesday, December 18, 2001 10:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Load Problem Ken, If you are using 8i, then you can use the 'filler' fields. In this case the control file would look something like below(considering the fields are terminated by a comma and the table has columns a, b and c) load data infile 'myfile' into table mytable fields terminated by ',' (a char, b char, f3 filler char, f4 filler char, f5 filler char, f6 filler char, f42 filler char, c char ) If you are not using 8i, then you can not avoid an intermediate table, or use OS level tools to chop off the unwanted fields. HTH, - Kirti -Original Message- Sent: Tuesday, December 18, 2001 4:15 PM To: Multiple recipients of list ORACLE-L I have an input file that is delimited and contains 43 fields of various widths. I need to load only 3 fields into a DB table of 3 columns. 2 of the fields are at the beginning of the record and 1 is the very last field in the record. How can I load only these 3 cols and ignore the other 40 cols I don't need without having to use an intermediate table? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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).
Palladium Consulting
Did anyone get an e-mail from Palladium Consulting lately? Just wondering where they got my e-mail address. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem .... HELP :-(
Hi Stephane, Thanks for writing back, I would normally look at some hints or something like that but as far as I can tell it's going through the tables in the correct way. My problem is when we run it on a Production copy on my server we don't get that big number against that table. The tkprof explain shows the access path as the same in both cases. Could it be doing it differently when it actually executes? The only difference is their dba has done some sort of reorg since we got our copy of the database. Thanks again Ian -Original Message- Sent: Wednesday, 19 December 2001 21:55 To: Multiple recipients of list ORACLE-L Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Biddell, Ian 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: Deciding what columns to partition on
Waleed, We've had so much difficulty with upgrading our warehouse from 8.0.4 to 8i. We've had bugs all over the place with regard to partitioning, star transformation, etc.These problems are with just plain, vanilla tables. Our developers are very reluctant to try newer functionality because we have so many bugs already. They think that newer functionality will have even more bugs. Cherie Khedr, Waleed To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Waleed.Khedr@ cc: FMR.COM Subject: RE: Deciding what columns to partition on Sent by: [EMAIL PROTECTED] om 12/18/01 09:11 PM Please respond to ORACLE-L Did you consider partitioned IOT? Have fun, Waleed -Original Message- Sent: Tuesday, December 18, 2001 1:46 PM To: Multiple recipients of list ORACLE-L We are doing a redesign of our 200-Gig data warehouse in 9i. One of the things we're thinking about is changing which columns we partition on. Currently, we partition exclusively on date fields. This has proven extremely helpful for doing maintenance, purging old data, archiving, etc. However, I feel that we're getting minimal benefit with regard to partition pruning. Very few of our most-used queries and reports even include the date field that we are partitioning on. Instead, we usually query primarily on account number, rep number, etc. which is a much more selective column. With indexes (and little partition pruning), we are getting response times that are not fantastic but are within tolerable range. However, as the warehouse gets larger, these response times get less and less acceptable. Hence this redesign. Many of our indexes are not even locally partitioned. I can change them to be locally partitioned. The primary key ones can't be locally partitioned unless they include the columns (date) that the table is partitioned on. Although the queries that use these primary key indexes don't even include date clauses, I could still add the date field to the index so that it can be locally partitioned. However, this doesn't seem like a great idea in most cases. Currently, we seldom seem to be doing any partition pruning in our explain plans. We seem to use indexes that don't include date columns instead. I'm not sure if the indexes would work better if they had the date column in them, especially if the date column had to be the leading column in the index. Although the partitions would then be pruned, I don't think the query would perform as well as it would with a global partitioned index (or even a non-partitioned index) where the acct_no (or whatever highly selective column) is the leading column in the index. I've tried to do some testing on this but the results haven't been conclusive because I can't test in production and I don't think my test database is large enough to give correct test results. Now to my question: In this sort of situation, should I settle for being able to either use date partitioning for maintenance benefits only, or to partition by the columns that I most often query on like acct_no so that I can prune by partition. Or is there some magical hybrid situation where I can have both ease of maintenance and partition pruning for performance? If yes, what sort of magical partitioning strategy do I need to use? Thanks for any insights you can offer. Also, I've been reading Scaling Oracle8i but it's more aimed at OLTP. Can anyone offer
How to backup MTS database with RMAN
Title: How to backup MTS database with RMAN Hi! Since I'm pretty new to RMAN I have a rather basic question: is it possible to backup databases running in multithreaded server mode with RMAN? Or does this have to be dedicated server mode? $ rman target sys/@kpmgi rcvcat rman/x@admserv Recovery Manager: Release 8.1.7.2.0 - Production RMAN-06005: connected to target database: KPMGI (DBID=3995384462) RMAN-06008: connected to recovery catalog database RMAN register database; RMAN-03022: compiling command: register RMAN-03023: executing command: register RMAN-08006: database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-07005: error during channel cleanup RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery catalog RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT Do I just have to take out mts_dispatchers = (protocol=TCP) from the init.ora file? This is 8.1.7.2.0 on Sun Solaris. Thanks, Helmut
[Q] how to export sequence number?
We have ORACLE 8i running on UNIX server. I export from user1 and import to user2. I found everything look correct except sequence number not in their. Does their has way to export sequence number? Thanks. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash 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: Deciding what columns to partition on
Jared/Stephane, It is true that a lot of our queries don't lend themselves to locally partitioned indexes. I tried to convert some globally partitioned indexes to locally partitioned ones with fairly poor results. It sort of like trying to force a square peg into a round hole. I just wish that our warehouse was a nice, neat, tidy, textbook one that responded perfectly to all of the recommendations experts make in their books. Thanks guys, Cherie Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: Deciding what columns to partition on Sent by: [EMAIL PROTECTED] om 12/19/01 05:10 AM Please respond to ORACLE-L Jared Still wrote: On Tuesday 18 December 2001 12:55, Stephane Faroult wrote: I have never found the argument 'you scan a single partition' very compelling when your data is properly indexed. Stephane, This is assuming that you are using global indexes on your partitioned tables. If using local indexes and searching by something other than the partition key, all indexes will be scanned if used at all. Excellent point. Which rather goes AGAINST partitioning-as-a-performance-improvement-factor. In fact, I have never managed to do without global indexes. Users always seem to want to ask the wrong questions. When I wear my DBA cap I really like partitions. Otherwise I rather like them as an alternative to bitmap indexes (so to speak). -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
Book recommendation for 9i or 8i data warehousing
Looking for a good book on data warehousing design and physical implementation that includes recommendations on new features for 9i and 8i. I'm hoping to find something that outlines best practices based on the realities of what is out there and released right now. A lot of the functionality that is touted for data warehouses is and/or was buggy and not working as intended. I'd rather not spend a lot of time designing around features that don't really work. On the other hand, if these things are really working, what works best. I'm also interested in white papers, web sites, etc. Thanks in advance, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Weid exp/imp problem
Title: Weid exp/imp problem Helmut: This could be due to the fact that constraints on those tables have not been defined yet. Since tables are imported in Alpha order, I would suggest performing a no-data import after this one is complete. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 3:25 AM To: Multiple recipients of list ORACLE-L Subject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table TABELLEN 37 rows imported . . importing table TABELLEN_ZUORDNUNGEN 28 rows imported . . importing table TMP$TEST 1 rows imported . . importing table TMP_FUNKTIONS_PARAMETER 0 rows imported . . importing table TMP_FUNKTIONS_SPALTEN 0 rows imported . . importing table USEREXIT 5 rows imported . . importing table USEREXIT_TYPE 3 rows imported . . importing table ZYKLUS 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_BNGR_FK FOREIGN KEY (BNGR_ID) REFERENCES BENUTZER_GRUPPEN (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: ALTER TABLE BENUTZER_GRUPPEN_ZUORD ADD CONSTRAINT BNGRZ_OW_FK FOREIGN K EY (OW_ID) REFERENCES OWNER (ID) ENABLE NOVALIDATE IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: SQL Loader Load Problem
Ken, Good to know that it worked. Not sure what books you referred to, but if you deal with SQL*Loader a lot, then you may want to check out Jonathan Gennick's book titled 'Oracle SQL*Loader : The Definitive Guide' by O'Reilly. Regards. - Kirti -Original Message- Sent: Wednesday, December 19, 2001 7:20 AM To: Multiple recipients of list ORACLE-L Kirti: Another person recommended this approach and it worked very nicely. To bad it's not in the book. Thanks much, Ken -Original Message- Sent: Tuesday, December 18, 2001 10:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Load Problem Ken, If you are using 8i, then you can use the 'filler' fields. In this case the control file would look something like below(considering the fields are terminated by a comma and the table has columns a, b and c) load data infile 'myfile' into table mytable fields terminated by ',' (a char, b char, f3 filler char, f4 filler char, f5 filler char, f6 filler char, f42 filler char, c char ) If you are not using 8i, then you can not avoid an intermediate table, or use OS level tools to chop off the unwanted fields. HTH, - Kirti -Original Message- Sent: Tuesday, December 18, 2001 4:15 PM To: Multiple recipients of list ORACLE-L I have an input file that is delimited and contains 43 fields of various widths. I need to load only 3 fields into a DB table of 3 columns. 2 of the fields are at the beginning of the record and 1 is the very last field in the record. How can I load only these 3 cols and ignore the other 40 cols I don't need without having to use an intermediate table? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: SQL LOADER
IA, like this: ( col1 char , col_seq test_seq.nextval ) where test_seq is a sequence HTH Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, December 19, 2001 6:50 AM To: Multiple recipients of list ORACLE-L using sql loader it is possible to populate fields with a sequnce e.g INTO TABLE dept (deptno sequence(2, 3), but i want to use my own sequence , i.e my_seq.nextval how is this done? reagards IA -- 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: Mercadante, Thomas F 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: Revert from 8.1.6 to 8.0.5
Thanks all. Everything worked perfectly for us (we weren't using any 8i features yet). I didn't realize the migration guide had a downgrade section, but we didn't need it anyways! Best, Ed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 9:20 PM Check out the Chapter on downgrading in the 8i Migration manual The 8.1.6 manual pages are still online at http://otn.oracle.com/doc/oracle8i_816/server.816/a76957/migdowng.htm#15124 You need to remove whatever 8i features you may have implemented that are not available in 8.0.5 Depending on what changes you may have made, you may or may not be able to downgrade. Try the downgrade on reviewing the doc. It would be faster than rebuilding the database (unless the upgrade from 8.0.5 to 8.1.6 was itself a rebuild !!). Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed 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: hard disk configuration question
As someone else suggested, I have always used the internal drives for vendor software. I can always re-install it from the original medium. Other than that, temporary files, downloads, system swap space -- things that I really don't care if I have a copy of. Holman, Rodney To: Multiple recipients of list ORACLE-L rodney.holma[EMAIL PROTECTED] ncc: @lodgenet.comSubject: RE: hard disk configuration question Sent by: root 12/19/2001 07:35 AM Please respond to ORACLE-L Sorry WRT = With respect to -Original Message- Sent: Tuesday, December 18, 2001 8:25 PM To: Multiple recipients of list ORACLE-L First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Holman, Rodney 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: Startup (URGENT)
I'd guess that the database was shut down with a shutdown abort and smon is busy recovering. Sajid Iqbal siqbal To: Multiple recipients of list ORACLE-L @vianetworks.[EMAIL PROTECTED] co.uk cc: Sent by: rootSubject: Startup (URGENT) 12/19/2001 06:35 AM Please respond to ORACLE-L After re-starting the database, the db application seems really slow after looking into it smon seems to be taking up 50% of the cpu !! What is smon doing , is there any way to get around this ?? I have tried leaving it for a while ! Regards -- Sajid Iqbal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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: Different clocks for different instances.
Oracle uses the system clock. So I would have to say no. -Original Message- Pollard Sent: Tuesday, December 18, 2001 6:50 PM To: Multiple recipients of list ORACLE-L Gooday Oraclers, is it possible to have different instances, on the same dB server, operating with different clocks - ie different date/times? Environment: Compaq-Digital Tru64 Unix, Oracle 8.0.6, DS20 --- Kevin Pollard | PODBA (Pretend Oracle DBA) mailto:[EMAIL PROTECTED] | Administrative Systems Unit Phone:+61 (02) 6620 3969 | Southern Cross University FAX:+61 (02) 6626 9122 | P.O. Box 157 Room: R1-40a | Lismore NSW 2480, Australia http://staff.scu.edu.au/asu/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Pollard 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: found dead multi-threaded server
I encountered some problems also when we moved over from dedicated to MTS all of my problems it seem were being caused by the idle_time parameter in the profile when it was set to anything like 60mins or 120 mins, I would frequently get dead MTS-servers after disabling that no more problems I'm on 8.1.7.2 now on HP-UX 11.0 64 bit -Original Message- Sent: Tuesday, December 18, 2001 5:05 PM To: Multiple recipients of list ORACLE-L I've gotten these errors (most notably 16365) on numerous occasions and I'm running 8.1.7.0 I've also gotten this error on 8.1.6 with and without the patchset. The main cause of this error is a half-duplex protocol error and, according to Oracle, tends to be application related.See notes 155513.1, 300867.999 on metalink for more information. Since the upgrade has not fixed this for us, my guess is that it is an application problem (perl in our case). We only occasionally get them, but if you get them all the time, you may want to check your app. HTH, -Brian On Mon, 17 Dec 2001, prashast gujrati wrote: Hi Gurus, We have recently upgraded our DBs from old 7.3.2 version to 8.1.6.0 and have moved from dedicated to MTS. since then we are getting ORA-600s in alert.log of all the DBs with various first arguments some of which I'll list here : 15429, 15439, 16365, 16375, 17034, 17182, 17280, 17281, 17285, kohlnm120, kohcpi298 etc. These are the most frequent ones. Though the DBs are up and running these errors are being consistently logged in alert. None of the instances have crashed ever since we have upgraded. I've opened up a TAR on metalink and have been told that 8.1.6.3 patchset is to be applied. Now what I want to know is if others have also encountered these errors and if applying this patchset has solved the problems. If not then what are the other things to be done (I mean does moving to 8.1.7.3 will necessarily solve the problem ? We are reluctant as this will mean another period of downtime for the users...) Also what is the source of these errors ? Will moving into dedicated arch solve our problems ? (I somehow tend to trust this list more than Metalink !!) TIA, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian Haas 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: david hill 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).
Forms60 Server as a service and mapped drives
I configured my Forms 6i service to run as an NT service using the Local Administrator account instead of SYSTEM, because we are using mapped drives to cram more pointers into forms60_path. I logged a TAR, because when the forms server is started as a process from the command line using the local administrator account, the remote clients' browsers can run the forms applications no problem. When I run the forms server as an NT service however, I get errors on the browsers telling me that the form files could not be found. In the Oracle Support forum one technical analyst stated that changing the user for the nt service from system to another account would solve the problem, because system cannot see mapped drives. I set the user to local administrator, but the forms server still cannot find the form files. I logged a TAR, now another analyst told me I must run the forms server as a process from the command line, because the service does not see mapped drives. I asked the analyst to verify what the other analyst had posted on the Oracle forum. Has anyone successfully run the Forms Server service with mapped drives? TIA Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: recovery during refresh
You can SOMETIMES get away with copying online redo logs when in hot backup mode, IF there is no to little database activity during the hot backup step. BUT I would not do it as a general practice just for the reasons you are experiencing. CONTROL FILES AND ONLINE REDO LOG FILES SHOULD NOT BE BACKED UP WHILE THE DATABASE IS ACTIVE. Use 'alter system backup controlfile to trace' and 'alter system archive log current' to get the most recent and up to date copies of the data you need to recover/clone/restore. -Original Message- From: Tatireddy, Shrinivas (MED, Keane) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 2:20 AM To: Multiple recipients of list ORACLE-L Subject: RE: recovery during refresh Hi Gerardo, Thnq for responding. My doubt is , if I apply online logs , will it cause index corruptions. (ora - 600 error). In my last refresh , I applied online redo log. I got the message when I apply this log is Media recovery completed. After that I opened the db with resetlogs. Everything went fine. But later when the team is working that some indexes got corrupted and returning ora-600 errors. But in production db they are ok. Can you explain why this happened.? Srinivas -Original Message- Sent: Wednesday, December 19, 2001 3:50 AM To: Multiple recipients of list ORACLE-L Run the following via svrmgrl or sqlplus alter database backup controlfile to trace; This generates a trace file in udump directory. edit trace file: 1) strip out all lines prior to STARTUP NOMOUNT... 2) change all references to SID to test instance. 3) change all reference to file names to appropriate names for test instance. 4) make appropriate changes if there are any differences in number and size of online redo logs. 5) Remove these lines from end of trace file RECOVER DATABASE ALTER SYSTEM ARCHIVE LOG ALL; ALTER DATABASE OPEN; 6) copy hotbackup files only (do not copy online redo logs) to test file directories. 7) remove all online redo logs and control files for test instance. 8) make sure number of rollback segments in init.ora for test instance matches number of rollback segments in prod. 9) from svrmgrl or sqlplus @edited trace_file 10) issue following command: recover using backup controlfile until cancel; 11) when prompted for archive redo log, ftp it over from production to test (renaming it appropriately). 12) apply as many arhive redo logs that you feel you want 13) CANCEL (this ends recovery) if you get error, then you need apply more logs (see 10). 14) alter database open resetlogs; This will recreate online redo logs and startup the database; This is a proven procedure. HTH, Gerardo -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:35 PM To: Multiple recipients of list ORACLE-L Hi lists, I need to refresh a test database from production. (test1 from prod1 db) I took the hotbackup files and online redo logs. I created the controlfile using these files. After that I performed recovery. Here I applied online logs instead of archive logs. (There are 7 grous of online redo logs each contains 3 members). When I tried to open the db, it asked that datafile needs recovery. I applied online redo log instead of archive log. The next day I observed that indexes got corrupted (This msg is from another dba group who are working with this new test database) My doubt is : applying online redo logs instead of archive logs lead to this index corruption? can anybody come across such any situation. Now I need to do refresh again in another way applying archive logs. Thnx and regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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: Molina, Gerardo 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]
ERROR RUNNING BATCJ JOBS
Title: How to backup MTS database with RMAN Hi, We are running batch jobs (SELECT/INSERT) since friday and it runs fine for first 3 days and now we start getting following errors: 1) ORA-01460 SQLSTATE 42000 2) function sequence error SQLSTATE HY010 3) value out of range error - SQLSTATE 22003 We are using 9i client,9.0.1 database on sun solaris 2.8, Microsoft ODBC driver. What can be the possible cause of above errors Thanks -Harvinder
Truncated table!!index still holding space??
Hi Friends, I have two tables sized more than 2.3Gb has each index 700Mb, I truncated both tables!! I got space back of from tables, But indexes still showing its holding space(I got from dba_segments), It looks weired!! Mine is oracle7.3 in AIX. Thanks Raghu. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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).
Converting from ASCII7 to UTF8
We are planning on converting our 240-Gig data warehouse (will be 8.1.7.2 on Sun Solaris 2.6) from character set ASCII7 to UTF8. Has anybody had any experience in doing this? If you have, what pitfalls, problems, etc. did you encounter? Anything that we need to watch out for? What about longs? A particular concern for our database owners is whether this conversion will increase the size of the database. Anybody know of any anecdotal evidence either way? Thanks, Cherie -- 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: Help
Actually, there is a bug fix to 8.1.7.1. You don't really need to go to 8.1.7.2 although I wouldn't necessarily say not to. -Original Message- Sent: Tuesday, December 18, 2001 7:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: unused blocks BELOW HWM - Thanks
Those stodgy old unix folks are much civilized than us anarchic DBA's. Jared On Wednesday 19 December 2001 00:15, Hallas John wrote: I agree with your reasoning Dennis. I have long felt that one of the problems with the list is that the original raiser of the question should have some sort of responsibility for summarising the various responses and posting a short summarised reply showing the solutions(s) that worked for him/her This type of question where various solutions were proposed is the ideal candidate for this type of summarised response. I used to be on a Unix list and that strategy was used quite well there. John -Original Message- Sent: 18 December 2001 19:10 To: Multiple recipients of list ORACLE-L Naaah, I'm just the proverbial lazy DBA. Gene received many replies, including it can't be done. Since he happened to point out that two of them worked and produced the same results (an excellent sanity check), I thought it would benefit everyone by telling us which ones worked. I realize that we could have each tried each of the strategies and discovered the two for ourselves, but my principle is if there is an easier way, why not? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 11:35 AM To: Multiple recipients of list ORACLE-L Uh, amen. Isn't that the purpose of the list? Or is there a *third* list, one on topic, a second off topic, and a third where all the answers really are? -Original Message- Sent: Tuesday, December 18, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Gene - Now that you've gotten your answer, would you mind to post both of the methods that you found to work so that the rest of us could learn? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 7:05 AM To: Multiple recipients of list ORACLE-L Hi. This is just to thank all who replied to my post. Based on what I read, I have got two different ways of calculating that number and they seem to produce the same result. thank you all (you know who you are) = __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem .... HELP :-(
Did they rebuild their indexes after this reorg? It could be that they simply exported/imported the table without rebuilding the appropriate indexes? Just a thought.. Mark -Original Message- Ian Sent: 19 December 2001 12:55 To: Multiple recipients of list ORACLE-L Hi Stephane, Thanks for writing back, I would normally look at some hints or something like that but as far as I can tell it's going through the tables in the correct way. My problem is when we run it on a Production copy on my server we don't get that big number against that table. The tkprof explain shows the access path as the same in both cases. Could it be doing it differently when it actually executes? The only difference is their dba has done some sort of reorg since we got our copy of the database. Thanks again Ian -Original Message- Sent: Wednesday, 19 December 2001 21:55 To: Multiple recipients of list ORACLE-L Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Biddell, Ian 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: Mark Leith 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 Load Problem
page 121 'skipping fields you don't want to load' of g.gennick 'oracle sql*loader' book. -Original Message- Sent: Wednesday, December 19, 2001 9:16 AM To: Multiple recipients of list ORACLE-L Ken, Good to know that it worked. Not sure what books you referred to, but if you deal with SQL*Loader a lot, then you may want to check out Jonathan Gennick's book titled 'Oracle SQL*Loader : The Definitive Guide' by O'Reilly. Regards. - Kirti -Original Message- Sent: Wednesday, December 19, 2001 7:20 AM To: Multiple recipients of list ORACLE-L Kirti: Another person recommended this approach and it worked very nicely. To bad it's not in the book. Thanks much, Ken -Original Message- Sent: Tuesday, December 18, 2001 10:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Load Problem Ken, If you are using 8i, then you can use the 'filler' fields. In this case the control file would look something like below(considering the fields are terminated by a comma and the table has columns a, b and c) load data infile 'myfile' into table mytable fields terminated by ',' (a char, b char, f3 filler char, f4 filler char, f5 filler char, f6 filler char, f42 filler char, c char ) If you are not using 8i, then you can not avoid an intermediate table, or use OS level tools to chop off the unwanted fields. HTH, - Kirti -Original Message- Sent: Tuesday, December 18, 2001 4:15 PM To: Multiple recipients of list ORACLE-L I have an input file that is delimited and contains 43 fields of various widths. I need to load only 3 fields into a DB table of 3 columns. 2 of the fields are at the beginning of the record and 1 is the very last field in the record. How can I load only these 3 cols and ignore the other 40 cols I don't need without having to use an intermediate table? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). -- 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:Palladium Consulting
They got me too. But I do have to admit that it was a nicely worded and non intrusive message. Dick Goulet Reply Separator Author: Boivin; Patrice J [EMAIL PROTECTED] Date: 12/19/2001 4:50 AM Did anyone get an e-mail from Palladium Consulting lately? Just wondering where they got my e-mail address. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Performance problem .... HELP :-(
Title: Performance problem HELP :-( Hi Ian, take a careful look at fragmentation of their indexes and possible chained rows in the tables. Probably RATE_SCHEDULE_LINK_PK is a good start point Also the cardinality(estimated numbers of output rows for each step) may confuse you if their statistics is lost or obsolete for some objects Regards,Ed - Original Message - From: Biddell, Ian To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 19, 2001 1:50 PM Subject: Performance problem HELP :-( Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example.
TNS-00510: Internal limit restriction exceeded
I am getting these errors in my listener.log when trying to connect. Current connections are fine, it is only new connections getting the error. TNS-12500: TNS:listener failed to start a dedicated server process TNS-12540: TNS:internal limit restriction exceeded TNS-12560: TNS:protocol adapter error TNS-00510: Internal limit restriction exceeded 32-bit Windows Error: 8: Exec format error Do I need to change something like my processes parameter in init.ora? Any ideas?? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave 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).
brain F*rt question
List, I am having a big brain F*rt on a simple join query. I would like some input please. Two tables with common fields retnbr and saledate and other fields. I would like to select the saledate, sum of selected fields from table 1 and selected fields from table 2 for a particuler retnbr and group the results by saledate. I keep getting the summed values increased by the number of occurances in the two tables. listing of the correct output from table1 (glciwsr): SALDATE INSETTLE INRET INCASH INCOMMINBONUS -- ---- - - -- -- 01-06-2001900 0 -555-45 -11.1 01-13-2001 1800 0 -885-90 -17.7 01-20-2001300 -218 -724-4.1-14.48 01-27-2001600 0 -767-30 -15.34 listing of the correct output from table2 (glcowsr); SALDATE OLSALES OLCASH OLCOMMOLBONUS -- -- -- -- -- 01-06-2001 7470.5 -694 -504.78 -13.88 01-13-2001 8106 -1651 -547.88 -33.02 01-20-2001 7215 -865 -488.29-17.3 01-27-2001 6438.5-1085 -428.58-21.7 What I would like is the all of the columns to appear on one list with only 1 occurance of the SALDATE. listing of the query I used that gives the wrong results select a.saledate saldate, (sum(a.settlementamt) - sum(a.returnamt)) insettle, SUM(a.returnamt) inret, SUM(a.cashamt) incash, SUM(a.SALESCOMMAMT)*-1 incomm, SUM(a.CASHBONUSAMT)*-1 inbonus, sum(b.salesamt) olsales, sum(b.cashamt)*-1 olcash, sum(b.salescommamt)*-1 olcomm, sum(b.cashbonusamt)*-1 olbonus from glciwsr a , glcowsr b where a.retnbr = retlook and b.retnbr = a.retnbr and a.saledate between 'startdt' and 'endate' and b.saledate = a.saledate group by a.saledate; output from incorrect query: SALDATE INSETTLE INRET INCASH INCOMMINBONUSOLSALES OLCASH OLCOMM -- -- -- - - - -- -- -- -- 01-06-2001 4500 0 -2775 -225 -55.5 201703.5-18738 -13629.06 01-13-2001 9000 0 -4425 -450 -88.5243180 -49530 -16436.4 01-20-2001 1500 -1090 -3620 -20.5 -72.4 202020 -24220 -13672.12 01-27-2001 30000 -3835 -150 -76.7 160962.5-27125 -10714.5 As you can see the sum's are increased 5 fold. Any help in clearing the Brain F*rt would be appreciated. Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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 Load Problem
- Original Message - To: [EMAIL PROTECTED] Sent: Wednesday, December 19, 2001 11:30 AM With Oracle 7.3.4 you could use this method: LOAD DATA INFILE 'example.dat' BADFILE 'example.bad' insert INTO TABLE emp FIELDS TERMINATED BY ';' (empno POSITION(*), ename POSITION(*), job POSITION(*+2), mgr POSITION(*)) Here are the contents of data file example.dat 7782;CLARK;;;MANAGER;7839 125;Peter;;;258; - Original Message - From: Ken Janusz [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 7:15 PM Subject: SQL Loader Load Problem I have an input file that is delimited and contains 43 fields of various widths. I need to load only 3 fields into a DB table of 3 columns. 2 of the fields are at the beginning of the record and 1 is the very last field in the record. How can I load only these 3 cols and ignore the other 40 cols I don't need without having to use an intermediate table? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Paula Wachtmeister INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul 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).
sql loader-dates
Hi , In my data file i have dates in the format e.g. 12/19/2001, but when loader tries to insert this into the table , the table is expecting date in the format 19-DEC-01 so it rejects the data. What can i do to get around this? regards -- 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: SQL Loader Load Problem
That's the book I have. I have not found any other books totally for SQL*Loader. Ken -Original Message- Sent: Wednesday, December 19, 2001 8:16 AM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Load Problem Ken, Good to know that it worked. Not sure what books you referred to, but if you deal with SQL*Loader a lot, then you may want to check out Jonathan Gennick's book titled 'Oracle SQL*Loader : The Definitive Guide' by O'Reilly. Regards. - Kirti -Original Message- Sent: Wednesday, December 19, 2001 7:20 AM To: Multiple recipients of list ORACLE-L Kirti: Another person recommended this approach and it worked very nicely. To bad it's not in the book. Thanks much, Ken -Original Message- Sent: Tuesday, December 18, 2001 10:20 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Loader Load Problem Ken, If you are using 8i, then you can use the 'filler' fields. In this case the control file would look something like below(considering the fields are terminated by a comma and the table has columns a, b and c) load data infile 'myfile' into table mytable fields terminated by ',' (a char, b char, f3 filler char, f4 filler char, f5 filler char, f6 filler char, f42 filler char, c char ) If you are not using 8i, then you can not avoid an intermediate table, or use OS level tools to chop off the unwanted fields. HTH, - Kirti -Original Message- Sent: Tuesday, December 18, 2001 4:15 PM To: Multiple recipients of list ORACLE-L I have an input file that is delimited and contains 43 fields of various widths. I need to load only 3 fields into a DB table of 3 columns. 2 of the fields are at the beginning of the record and 1 is the very last field in the record. How can I load only these 3 cols and ignore the other 40 cols I don't need without having to use an intermediate table? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Redo logs lost, old backups
Hi Maser, I am not sure this work or not. Actually my suggestion is call oracle support before you do anything. Just for your reference. 1) BACKUP the current situation with the database closed. The instructions here are destructive. You are STRONGLY advised to backup the current situation before proceeding. If you do NOT do this you may lose the chance to try other options. 2) If your datafiles are from different points in time it is best to try to use system tablespace files at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database. 3) Edit your initSID.ora file and set: _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS = list of all rollback segments Comment out any 'ROLLBACK_SEGMENTS= ' clause. 4) Invoke either server manager (svrmgrl) or SQL*DBA and issue the commands below: connect internal startup mount select * from v$datafile; ... Check here that all files you want to open with are listed as ONLINE (or as SYSTEM). If not: ALTER DATABASE DATAFILE 'full_path_to_file' ONLINE; until all required files are listed as online. ... RECOVER DATABASE UNTIL CANCEL; or RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; At the prompt enter the word: Cancel ALTER DATABASE OPEN RESETLOGS; 5) If this works you should attempt to export the database IMMEDIATELY. Once you have an export the database must be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch. A database which has been opened in this way, but not rebuilt will not be supported by Oracle. Any delay in extracting the contents, or any attempt to use the system may cause irreparable damage. NOTE: Be sure to remove the init.ora parameters added in step 3 otherwise you may accidentally corrupt any new database created using the same init.ora file. 6) It is possible that the OPEN RESETLOGS may fail with an error, or that accessing the data (eg: using export) may fail with an error. In this case note down the exact error and identify any trace files produced then contact Oracle Support Services with this information. Depending on the errors it may be possible to proceed further. Joan Name: arsqaALRT.LOG arsqaALRT.LOG Type: Text Document Maser, Donna (SEA) wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) 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
Re: Performance problem .... HELP :-(
Ian, What kind of a reorg was done? So the RATE_SCHEDULE_LINK_B table has about the same number of rows in both instances? The explain plans are the same. It looks like one just has more records to access. Both could be improved by changing the sql to be more selective. Mike From: Biddell, Ian [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Performance problem HELP :-( Date: Wed, 19 Dec 2001 02:50:23 -0800 Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian CLIENT SERVER TRACE call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.04 0 0 0 0 Execute600 0.09 0.12 0 0 0 0 Fetch 1294 2448.982918.79 48 83060760 1200 694 --- -- -- -- -- -- -- total 1895 2449.072918.95 48 83060760 1200 694 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 12 SORT (ORDER BY) 0FILTER 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 512750NESTED LOOPS 769296 NESTED LOOPS 1869552 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 2541882 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 487200 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 36834 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 249381TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 445 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 36 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 48INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 12 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_A_PK' (UNIQUE) 12 NESTED LOOPS 24 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE) PROD DATABASE COPY ON OUR SERVER call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.07 0.08 0 0 0 0 Execute482 0.20 0.25 0 0 0 0 Fetch 4573 86.71 89.05 931450283 0 4090 --- -- -- -- -- -- -- total 5056 86.98 89.38 931450283 0 4090 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 848 SORT (ORDER BY) 11660FILTER 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790NESTED LOOPS 8790 NESTED LOOPS 25596 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 25752 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 12869 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 16078 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 26131 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 37867 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 30064TABLE ACCESS GOAL: ANALYZED
RE: How to backup MTS database with RMAN
Title: How to backup MTS database with RMAN Helmut, From the Oracle Documentation: To use RMAN with an MTS database: Net8 configuration varies greatly from system to system. The following procedure illustrates only one method. This scenario assumes that the following net service name in the tnsnames.ora file connects to the target database using the MTSarchitecture, where inst1 is a value of the SERVICE_NAMES initialization parameter: inst1_mts = (description= (address=(protocol=tcp)(host=inst1_host)(port1521)) (connect_data=(service_name=inst1)(server=shared)) ) 1.Create a net service name in the tnsnames.ora file that connects to the non-shared SID. For example, enter: inst1_ded = (description= (address=(protocol=tcp)(host=inst1_host)(port1521)) (connect_data=(service_name=inst1)(server=dedicated)) ) 2.Connect using SQL*Plus using both the MTS and dedicated service names to confirm the mode of each session. For example, toconnect to a dedicated session you can issue: SQL connect sys/oracle@inst1_ded Connected. SQL SELECT server FROM v$session WHERE sid = (SELECT DISTINCT sid FROM v$mystat); SERVER - DEDICATED 1 row selected. To connect to an MTS session, you can issue: SQL connect sys/oracle@inst1_mtsConnected.SQL SELECT server FROM v$session WHERE sid = (SELECT DISTINCT sid FROM v$mystat); SERVER -SHARED 1 row selected. Connect to the target database (and optionally the recovery catalog) using the dedicated service name. For example, enter: % rman target sys/oracle@inst1_ded catalog rman/rman@rcat HTH Tom Mercadante Oracle Certified Professional -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 19, 2001 8:25 AMTo: Multiple recipients of list ORACLE-LSubject: How to backup MTS database with RMAN Hi! Since I'm pretty new to RMAN I have a rather basic question: is it possible to backup databases running in multithreaded server mode with RMAN? Or does this have to be dedicated server mode? $ rman target sys/@kpmgi rcvcat rman/x@admserv Recovery Manager: Release 8.1.7.2.0 - Production RMAN-06005: connected to target database: KPMGI (DBID=3995384462) RMAN-06008: connected to recovery catalog database RMAN register database; RMAN-03022: compiling command: register RMAN-03023: executing command: register RMAN-08006: database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-07005: error during channel cleanup RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery catalog RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT Do I just have to take out mts_dispatchers = "(protocol=TCP)" from the init.ora file? This is 8.1.7.2.0 on Sun Solaris. Thanks, Helmut
RE: hard disk configuration question
Oops... Hit the send button by mistake... As I was saying... I peeked at the HPUX doc for version 8.1.7... http://docs.oracle.com/a87032/0/unixdoc/product_0/a85346.pdf And it looks like 16K is you max... Just look up the appropriate doc for the versions you are working with to determine your max... HTH Tim -Original Message- Sent: Wednesday, December 19, 2001 11:12 AM To: '[EMAIL PROTECTED]' Max block size varies by platform and Oracle version... Check out the Oracle Administrators Guide for the OS you are working with... I peeked at the HP UX doc for version 8.1.7 http://docs.oracle.com/a87032/0/unixdoc/product_0/a85346.pdf -Original Message- Sent: Wednesday, December 19, 2001 5:30 AM To: Multiple recipients of list ORACLE-L First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? WRT is an acronym for With regards to ;) And the advice about installing the O.S and patches on to these drives (maybe mirrored) seems sound to me. Second. If an 8k database block size is tiny, what is a large database block size? 8k is as large as I can go without raw file systems, isn't it? I *believe* (though have never seen) that you can have a 16k block size on HP, so maybe other platforms will allow this as well.. HTH Mark -Original Message- McClure Sent: 19 December 2001 02:25 To: Multiple recipients of list ORACLE-L Well we had our meeting with the Hitachi Sales critter, and engineer. I discovered that they were trying to push a 5 disk raid array and one hot spare at us, because my IT director was really trying to squeeze them on the price of my 10 18GB disk configuration. After the sales guy and engineer went on about how their 4 separate IO paths would make our raid 5 perform as well as any mirrored pair, I got to ask a few questions. Well it turns out that their RAID performs that well with extra large database block sizes. When I pointed out that we currently have a block size of 2k(I didn't build the db), and would be rebuilding the database on the new platform with 8k blocks, he said that with a small block size like 8k you will really get the best performance out of mirrors or a 1+0. I am sure the sales guy kicked him under the table ;-). The end result is this. We will probably go with the 6 36 GB DISKS. It flat out comes down to the cost of the whole system is just a little over budget, and the drives are where the adjustment gets made. I suspect that I will have much less trouble getting a few extra disks once the system is in the building. I may also mess with things a bit and configure a 1+0 and a mirrored pair. So after that discussion, and all your helpful input I still have two questions. First what use are the two internal drives that aren't in the disk array? Someone suggested to WRT them, and I am not familiar with that term. Again I am not sure I want to mirror them, and if they aren't redundant how useful can they really be? Second. If an 8k database block size is tiny, what is a large database block size? 8k is as large as I can go without raw file systems, isn't it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure 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: Mark Leith 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: Johnston, Tim 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
RE: Truncated table!!index still holding space??
alter index index_name rebuild HTH Mark -Original Message- Sent: 19 December 2001 14:01 To: Multiple recipients of list ORACLE-L Hi Friends, I have two tables sized more than 2.3Gb has each index 700Mb, I truncated both tables!! I got space back of from tables, But indexes still showing its holding space(I got from dba_segments), It looks weired!! Mine is oracle7.3 in AIX. Thanks Raghu. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: Mark Leith 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).
Synonyms causing a strange problem-Solved.
Greetings fellow DBAs: I just dealt with a problem I personally haven't run into before and thought that members of this list might be interested in reviewing: User-A owns all of a set of objects on Database Primary-DB including tables Table-1 through Table-100. User-B also exists on Primary-DB, has no tables of its own, but has been granted read access to some of the User A tables, specifically tables Table-1 through Table-9. A developer wants me to create and populate two tables in User-B's schema. Those tables are to be called exactly the same name and have pretty much the same data as two of the tables in User-A's schema, specifically Table-6 and Table-7. To accomplish this, he gives me an export from another database called Secondary-DB with just those two tables in it. I try to import the two tables into User-B's schema on Primary-DB. The import fails and I get the message Table or view already exists. I'm surprised at this since the User-B schema doesn't have any tables defined in it. To verify, I connect to the database as User-B and do a SQL select table_name from user_tables; SQL No rows selected. Result: There are indeed no tables in User-B's schema. Yet on attempting to import Table-6 and Table-7 into User-B's schema, it tells me it can't do so because those tables already exist! To make a long story short, the solution to this enigma is the following: I came to find out that there had been a bunch of synonyms defined, among which were these two: SQL create synonym User-B.Table-6 for User-A.Table-6; SQL create synonym User-B.Table-7 for User-A.Table-7; so that when I tried to import Table-6 and Table-7 into User-B's schema, the synonyms made Oracle think that they already existed. I dropped the two offending synonyms: SQL drop synonym User-B.Table-6; SQL drop synonym User-B.Table-7; after which the import worked just fine and all was well. Just thought some of you out there might be interested... Jim Damiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano 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).
Mabye OT: Was Re: Different clocks for different instances.
Speaking of the system clock, here's something interesting that I stumbled across a few years back... Background: For those of you who may not know, the Julian calendar was abandoned by England, in favor of the Gregorian calendar, in 1752 because of date discrepancies. To facilitate this, September 3 - 13 were eliminated. This is actually reflected in UNIX (well, Solaris anyway) by issuing the 'cal 1752' command and looking at the month of September: Jul Aug Sep S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S 1 2 3 4 1 1 2 14 15 16 5 6 7 8 9 10 11 2 3 4 5 6 7 8 17 18 19 20 21 22 23 12 13 14 15 16 17 18 9 10 11 12 13 14 15 24 25 26 27 28 29 30 19 20 21 22 23 24 25 16 17 18 19 20 21 22 26 27 28 29 30 31 23 24 25 26 27 28 29 More info. on why the switch over occurred is available at http://www.bicknell.net/books/pc1981/p_calender.htm, for those of you with morbid curiosity. Anyway - I was doing some Y2K testing a few years back, and playing with NLS_DATE_FORMAT queries when I learned this little tidbit. I decided to test the Oracle date formats because - hey - they'll be okay because they're based on Unix, right? Wrong: Issue the following query: select sysdate ,add_months(sysdate,-2988) ,add_months(sysdate,-2988) - 10 from dual; And see the following results: SYSDATE ADD_MONTHS(SYSDATE,- ADD_MONTHS(SYSDATE,- 19-DEC-2001 10:07:02 19-DEC-1752 10:07:02 09-DEC-1752 10:07:02 The third column represents a date which, technically, does not exist. : ) Brian Kimberly Smith wrote: Oracle uses the system clock. So I would have to say no. -Original Message- Pollard Sent: Tuesday, December 18, 2001 6:50 PM To: Multiple recipients of list ORACLE-L Gooday Oraclers, is it possible to have different instances, on the same dB server, operating with different clocks - ie different date/times? Environment: Compaq-Digital Tru64 Unix, Oracle 8.0.6, DS20 --- Kevin Pollard | PODBA (Pretend Oracle DBA) mailto:[EMAIL PROTECTED] | Administrative Systems Unit Phone:+61 (02) 6620 3969 | Southern Cross University FAX:+61 (02) 6626 9122 | P.O. Box 157 Room: R1-40a | Lismore NSW 2480, Australia http://staff.scu.edu.au/asu/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Pollard 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | --
RE: Help
I am still waiting for Oracle 8.1.7.3 final release for 8.1. Then start looking at 9.1, when it comes out. [EMAIL PROTECTED] 12/19/01 10:35AM Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public
Max data file size on NTFS partition
Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilbert, Ashley 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).
Resend: Re: unused blocks BELOW HWM - Thanks
This is a GREAT idea. Ed On Wednesday 19 December 2001 00:15, Hallas John wrote: I agree with your reasoning Dennis. I have long felt that one of the problems with the list is that the original raiser of the question should have some sort of responsibility for summarising the various responses and posting a short summarised reply showing the solutions(s) that worked for him/her This type of question where various solutions were proposed is the ideal candidate for this type of summarised response. I used to be on a Unix list and that strategy was used quite well there. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed 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).
utl_http package
Hello Gurus, I am working on a little project using the package utl_http, so far I have had limited success using this package. The below http call fails for some reason, it is supposed to download a page of size ~10K, i.e. 5 pieces of data, but instead it fails, I am not sure why... Any ideas ?? is it because that site is trying to plant/read a cookie or something on those lines ?? any help on this is much appreciated. declare v_htm_pieces utl_http.html_pieces ; begin pv_htm_pieces := utl_http.request_pieces('http://table.finance.yahoo.com/t?a=01b=01c=01d=01e=31f=01g=ds=ADCTy=0z=%5Eixic'); end; Peace, James Reddi _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James 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-dates
Try using the to_date command for the column data in question in your control file: ex: load data append into table xxx trailing nullcols ( column1 terminated by , to_date(:column1,'mm/dd/'), column2. ROR mª¿ªm [EMAIL PROTECTED] 12/19/01 10:45AM Hi , In my data file i have dates in the format e.g. 12/19/2001, but when loader tries to insert this into the table , the table is expecting date in the format 19-DEC-01 so it rejects the data. What can i do to get around this? regards -- 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: Ron Rogers 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).
e: using MTS on oracle OPS, running on HP-UX
Hello, Anyone out there try and implement MTS on OPS ? We are using 64-bit HP-UX, rev 11.0, and the OPS is 8.1.6.3.0. We look to go with MTS after we are at 8.1.7.2.1, sometime next month. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Paul 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: brain F*rt question
Ron Rogers wrote: List, I am having a big brain F*rt on a simple join query. I would like some input please. Two tables with common fields retnbr and saledate and other fields. I would like to select the saledate, sum of selected fields from table 1 and selected fields from table 2 for a particuler retnbr and group the results by saledate. I keep getting the summed values increased by the number of occurances in the two tables. listing of the correct output from table1 (glciwsr): SALDATE INSETTLE INRET INCASH INCOMMINBONUS -- ---- - - -- -- 01-06-2001900 0 -555-45 -11.1 01-13-2001 1800 0 -885-90 -17.7 01-20-2001300 -218 -724-4.1-14.48 01-27-2001600 0 -767-30 - 15.34 listing of the correct output from table2 (glcowsr); SALDATE OLSALES OLCASH OLCOMMOLBONUS -- -- -- -- -- 01-06-2001 7470.5 -694 -504.78 -13.88 01-13-2001 8106 -1651 -547.88 -33.02 01-20-2001 7215 -865 -488.29-17.3 01-27-2001 6438.5-1085 -428.58-21.7 What I would like is the all of the columns to appear on one list with only 1 occurance of the SALDATE. listing of the query I used that gives the wrong results select a.saledate saldate, (sum(a.settlementamt) - sum(a.returnamt)) insettle, SUM(a.returnamt) inret, SUM(a.cashamt) incash, SUM(a.SALESCOMMAMT)*-1 incomm, SUM(a.CASHBONUSAMT)*-1 inbonus, sum(b.salesamt) olsales, sum(b.cashamt)*-1 olcash, sum(b.salescommamt)*-1 olcomm, sum(b.cashbonusamt)*-1 olbonus from glciwsr a , glcowsr b where a.retnbr = retlook and b.retnbr = a.retnbr and a.saledate between 'startdt' and 'endate' and b.saledate = a.saledate group by a.saledate; output from incorrect query: SALDATE INSETTLE INRET INCASH INCOMMINBONUSOLSALES OLCASH OLCOMM -- -- -- - - - -- --- -- -- 01-06-2001 4500 0 -2775 -225 -55.5 201703.5-18738 -13629.06 01-13-2001 9000 0 -4425 -450 -88.5243180 -49530 -16436.4 01-20-2001 1500 -1090 -3620 -20.5 -72.4202020 -24220 -13672.12 01-27-2001 30000 -3835 -150 -76.7 160962.5-27125 -10714.5 As you can see the sum's are increased 5 fold. Any help in clearing the Brain F*rt would be appreciated. Ron ROR mª¿ªm Ron, Looks to me like the classical percentage computation problem. Sums are wrong because applied to the result of the join. Compute your sums in an in-line view in the FROM clause. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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-dates
Use column-name DATE mm/dd/, This should work. Also, look into getting the book SQL*Loader The Definitive Guide from O'Reilly. Hope this helps, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -Original Message- Sent: Wednesday, December 19, 2001 9:45 AM To: Multiple recipients of list ORACLE-L Subject:sql loader-dates Hi , In my data file i have dates in the format e.g. 12/19/2001, but when loader tries to insert this into the table , the table is expecting date in the format 19-DEC-01 so it rejects the data. What can i do to get around this? regards -- 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: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
Gene, You may have a longer wait than January if you are non-Solaris. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:27 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] I am still waiting for Oracle 8.1.7.3 final release for 8.1. Then start looking at 9.1, when it comes out. [EMAIL PROTECTED] 12/19/01 10:35AM Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul 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
RE: Mabye OT: Was Re: Different clocks for different instances.
Well I say, that is also the case on Tru64 as well. How pedantic is UNIX ?? -Original Message-From: Brian McGraw [mailto:[EMAIL PROTECTED]]Sent: 19 December 2001 16:16To: Multiple recipients of list ORACLE-LSubject: Mabye OT: Was Re: Different clocks for different instances.Speaking of the system clock, here's something interesting that I stumbled across a few years back... Background: For those of you who may not know, the Julian calendar was abandoned by England, in favor of the Gregorian calendar, in 1752 because of date discrepancies. To facilitate this, September 3 - 13 were eliminated. This is actually reflected in UNIX (well, Solaris anyway) by issuing the 'cal 1752' command and looking at the month of September: Jul Aug Sep S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S 1 2 3 4 1 1 2 14 15 16 5 6 7 8 9 10 11 2 3 4 5 6 7 8 17 18 19 20 21 22 23 12 13 14 15 16 17 18 9 10 11 12 13 14 15 24 25 26 27 28 29 30 19 20 21 22 23 24 25 16 17 18 19 20 21 22 26 27 28 29 30 31 23 24 25 26 27 28 29 More info. on why the switch over occurred is available at http://www.bicknell.net/books/pc1981/p_calender.htm, for those of you with morbid curiosity. Anyway - I was doing some Y2K testing a few years back, and playing with NLS_DATE_FORMAT queries when I learned this little tidbit. I decided to test the Oracle date formats because - hey - they'll be okay because they're based on Unix, right? Wrong: Issue the following query: select sysdate ,add_months(sysdate,-2988) ,add_months(sysdate,-2988) - 10 from dual; And see the following results: SYSDATE ADD_MONTHS(SYSDATE,- ADD_MONTHS(SYSDATE,- 19-DEC-2001 10:07:02 19-DEC-1752 10:07:02 09-DEC-1752 10:07:02 The third column represents a date which, technically, does not exist. : ) Brian Kimberly Smith wrote: Oracle uses the system clock. So I would have to say no. -Original Message- Pollard Sent: Tuesday, December 18, 2001 6:50 PM To: Multiple recipients of list ORACLE-L Gooday Oraclers, is it possible to have different instances, on the same dB server, operating with different clocks - ie different date/times? Environment: Compaq-Digital Tru64 Unix, Oracle 8.0.6, DS20 --- Kevin Pollard | PODBA (Pretend Oracle DBA) mailto:[EMAIL PROTECTED] | Administrative Systems Unit Phone:+61 (02) 6620 3969 | Southern Cross University FAX:+61 (02) 6626 9122 | P.O. Box 157 Room: R1-40a | Lismore NSW 2480, Australia http://staff.scu.edu.au/asu/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Pollard 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).-- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | -- 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.
Re: Problem with job
Daiminger, Helmut wrote: Hi! I created a new job in a database: variable jobno number; begin dbms_job.submit (:jobno, 'statspack.snap;', sysdate, 'trunc (sysdate,''HH24'') + trunc ((sysdate - trunc (sysdate, ''HH24''))*96+1)/96'); commit; end; The job was successfully created. But when I try to run the job, I get the following error message: BEGIN dbms_job.run(127); * ERROR at line 1: ORA-12011: execution of 1 jobs failed ORA-06512: at SYS.DBMS_IJOB, line 405 ORA-06512: at SYS.DBMS_JOB, line 267 ORA-06512: at line 1 Any idea what's wrong here? This is 8.1.7 on Sun Solaris. Thanks, Helmut Problem with job is that you know that they have failed, but you don't know why. I suggest you encapsulate your procedures in a procedure which logs what goes wrong. There are some details on how to do this in the 'Aunt Augusta' section of the Oriole site. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
Paul - How does one get 8.1.7.2.1? We ordered 8.1.7 recently from Oracle, but outside the CD just says 8.1.7. Do I need to download something? Sort of a silly question, but maybe someone else is as dumb as I. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 9:35 AM To: Multiple recipients of list ORACLE-L Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul 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
RE: Converting from ASCII7 to UTF8
Title: RE: Converting from ASCII7 to UTF8 This can be done pretty easily using SharePlex for Oracle. It's able to replicate between different character sets, platforms, DB versions.. It can also handle your long columns as well. As for the concern of database space... you'll probably gain back a lot of space, since you'll eventually be doing an import/export of the database... any fragmentation of the tables or indexes will be removed. You could even partition/index the tables differently to take advantage of the reports that are run to help optimize the system. It's even been used to convert db's from single-byte to multi-byte as well. www.quest.com/shareplex hth Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 6:50 AM To: Multiple recipients of list ORACLE-L Subject: Converting from ASCII7 to UTF8 We are planning on converting our 240-Gig data warehouse (will be 8.1.7.2 on Sun Solaris 2.6) from character set ASCII7 to UTF8. Has anybody had any experience in doing this? If you have, what pitfalls, problems, etc. did you encounter? Anything that we need to watch out for? What about longs? A particular concern for our database owners is whether this conversion will increase the size of the database. Anybody know of any anecdotal evidence either way? Thanks, Cherie -- 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: Help
About a month ago, I upgraded from 8.1.7.1.0 to 8.1.7.2.1 (actually the banner says 8.1.7.2.0). Everything is good so far. Bing -Original Message- Sent: Wednesday, December 19, 2001 9:09 AM To: Multiple recipients of list ORACLE-L Gene, You may have a longer wait than January if you are non-Solaris. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:27 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] I am still waiting for Oracle 8.1.7.3 final release for 8.1. Then start looking at 9.1, when it comes out. [EMAIL PROTECTED] 12/19/01 10:35AM Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Help
Upgrade first to 8.1.7.2.1. Don't wait. -Original Message- Sent: Wednesday, December 19, 2001 9:09 AM To: Multiple recipients of list ORACLE-L Gene, You may have a longer wait than January if you are non-Solaris. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:27 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] I am still waiting for Oracle 8.1.7.3 final release for 8.1. Then start looking at 9.1, when it comes out. [EMAIL PROTECTED] 12/19/01 10:35AM Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul 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]
RE: Encryption - Question about the key
Jared (King of all Perl and Oracle)... I have a guy I work with sitting next to me who is having some problems with getting DBI to work after an Oracle 8i (8.1.7 HPUX) upgrade from 8.0.6. Everything worked fine before the upgrade. Oracle was installed in a new home. Perl was also installed in a new location (5.6.1). He is using DBI 1.20 and attempting to install the DBD-Oracle-1.12 drivers. During Makefile.PL compile he is hitting a Unable to interpret Oracle oci build commands. Using fallback approach warning. After that pretty much everything else goes wrong. The HPUX readme file says to add in a couple of options to the Perl build to get rid of dld.sl related errors. He did that but is still getting errors for the dld.sh library. It says Can't shl_load() a library containing Thread Storage: /usr/libt/baseFAILED tests 4-5 and then a bunch more errors related to this. The Perl installation was installed after the Oracle upgrade. An earlier version of Perl was alrady installed but was generating core dumps. The initial solution was to upgrade Perl. I can post log files and such if you want. Would really appreciate some pointers on this one. Internet searches have not turned up much. Thanks, Ethan Post -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Mabye OT: Was Re: Different clocks for different instances.
Brian McGraw wrote: Speaking of the system clock, here's something interesting that I stumbled across a few years back... Background: For those of you who may not know, the Julian calendar was abandoned by England, in favor of the Gregorian calendar, in 1752 because of date discrepancies. To facilitate this, September 3 - 13 were eliminated. This is actually reflected in UNIX (well, Solaris anyway) by issuing the 'cal 1752' command and looking at the month of September: JulAugSep S M Tu W Th F SS M Tu W Th F SS M Tu W Th F S 1 2 3 4 1 1 2 14 15 16 5 6 7 8 9 10 112 3 4 5 6 7 8 17 18 19 20 21 22 23 12 13 14 15 16 17 189 10 11 12 13 14 15 24 25 26 27 28 29 30 19 20 21 22 23 24 25 16 17 18 19 20 21 22 26 27 28 29 30 31 23 24 25 26 27 28 29 More info. on why the switch over occurred is available at http://www.bicknell.net/books/pc1981/p_calender.htm, for those of you with morbid curiosity. Anyway - I was doing some Y2K testing a few years back, and playing with NLS_DATE_FORMAT queries when I learned this little tidbit. I decided to test the Oracle date formats because - hey - they'll be okay because they're based on Unix, right? Wrong: Issue the following query: select sysdate ,add_months(sysdate,-2988) ,add_months(sysdate,-2988) - 10 from dual; And see the following results: SYSDATE ADD_MONTHS(SYSDATE,- ADD_MONTHS(SYSDATE,- 19-DEC-2001 10:07:02 19-DEC-1752 10:07:02 09-DEC-1752 10:07:02 The third column represents a date which, technically, does not exist. : ) Brian Brian, Speak for yourself. The continent, much more enlightened, had switched in October 1582. England had opted out, as it seems, and jumped on the bandwagon belatedly (sounds furiously modern, doesn't it?). Check within Oracle the number of days in October 1582, you will see that it's correct (from my point of view). I don't think that you can say that the Oracle date is based on the system date for these dates, because Unix systems only see dark ages before 1st Jan 1970. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Mabye OT: Was Re: Different clocks for different instances.
Oracle uses the original start date of the Gregorian calendar in October 1582: 1* select to_date('04-OCT-1582'), to_date('04-OCT-1582')+1 from dual devp / TO_DATE('04-OCT-1582 TO_DATE('04-OCT-1582 04-OCT-1582 00:00:00 15-OCT-1582 00:00:00 Ben Schilling Telecomm Manager Office of the Commissioner of Insurance 608-266-1615 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:09 AM To: Multiple recipients of list ORACLE-L Well I say, that is also the case on Tru64 as well. How pedantic is UNIX ?? -Original Message- Sent: 19 December 2001 16:16 To: Multiple recipients of list ORACLE-L Speaking of the system clock, here's something interesting that I stumbled across a few years back... Background: For those of you who may not know, the Julian calendar was abandoned by England, in favor of the Gregorian calendar, in 1752 because of date discrepancies. To facilitate this, September 3 - 13 were eliminated. This is actually reflected in UNIX (well, Solaris anyway) by issuing the 'cal 1752' command and looking at the month of September: JulAugSep S M Tu W Th F SS M Tu W Th F SS M Tu W Th F S 1 2 3 4 1 1 2 14 15 16 5 6 7 8 9 10 112 3 4 5 6 7 8 17 18 19 20 21 22 23 12 13 14 15 16 17 189 10 11 12 13 14 15 24 25 26 27 28 29 30 19 20 21 22 23 24 25 16 17 18 19 20 21 22 26 27 28 29 30 31 23 24 25 26 27 28 29 More info. on why the switch over occurred is available at http://www.bicknell.net/books/pc1981/p_calender.htm http://www.bicknell.net/books/pc1981/p_calender.htm , for those of you with morbid curiosity. Anyway - I was doing some Y2K testing a few years back, and playing with NLS_DATE_FORMAT queries when I learned this little tidbit. I decided to test the Oracle date formats because - hey - they'll be okay because they're based on Unix, right? Wrong: Issue the following query: select sysdate ,add_months(sysdate,-2988) ,add_months(sysdate,-2988) - 10 from dual; And see the following results: SYSDATE ADD_MONTHS(SYSDATE,- ADD_MONTHS(SYSDATE,- 19-DEC-2001 10:07:02 19-DEC-1752 10:07:02 09-DEC-1752 10:07:02 The third column represents a date which, technically, does not exist. : ) Brian Kimberly Smith wrote: Oracle uses the system clock. So I would have to say no. -Original Message- Pollard Sent: Tuesday, December 18, 2001 6:50 PM To: Multiple recipients of list ORACLE-L Gooday Oraclers, is it possible to have different instances, on the same dB server, operating with different clocks - ie different date/times? Environment: Compaq-Digital Tru64 Unix, Oracle 8.0.6, DS20 --- Kevin Pollard | PODBA (Pretend Oracle DBA) mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] | Administrative Systems Unit Phone:+61 (02) 6620 3969 | Southern Cross University FAX:+61 (02) 6626 9122 | P.O. Box 157 Room: R1-40a | Lismore NSW 2480, Australia http://staff.scu.edu.au/asu/index.html http://staff.scu.edu.au/asu/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Kevin Pollard 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 http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com
Re: Max data file size on NTFS partition
Hi Ashley, Sorry, don't know exact answer, but the problem doesn't always come from OS side. There are *Oracle* limits on some platforms. It seems you use AUTOEXTEND=ON for your files. If so then you may encountered some bugs when your files had extended through size limits. At least there were some bugs in the past. I prefer disabling this option in order to get more control over db and add files when needed. All files are of equal size. Regards, Ed Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: using MTS on oracle OPS, running on HP-UX
Why do you need to use MTS? Do you run out of memory? -Original Message- Sent: Wednesday, December 19, 2001 9:09 AM To: Multiple recipients of list ORACLE-L Hello, Anyone out there try and implement MTS on OPS ? We are using 64-bit HP-UX, rev 11.0, and the OPS is 8.1.6.3.0. We look to go with MTS after we are at 8.1.7.2.1, sometime next month. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Paul 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
oracle's banner rarely agrees with the actual revision that you are running. I think that it just indicates the base rev, without the patches. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 12:26 PM To: Multiple recipients of list ORACLE-L About a month ago, I upgraded from 8.1.7.1.0 to 8.1.7.2.1 (actually the banner says 8.1.7.2.0). Everything is good so far. Bing -Original Message- Sent: Wednesday, December 19, 2001 9:09 AM To: Multiple recipients of list ORACLE-L Gene, You may have a longer wait than January if you are non-Solaris. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:27 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] I am still waiting for Oracle 8.1.7.3 final release for 8.1. Then start looking at 9.1, when it comes out. [EMAIL PROTECTED] 12/19/01 10:35AM Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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
RE: Encryption - Question about the key
Well, The database we are talking here is basically athlete's information who compete in various games. I (and others in the DBA group) can be trusted to hold the key, so that isn't a problem either. There will always one person available who will have access to key if something needs to be fixed. Thanks for the input guys ... not I have to convince the development team that they * really * need encryption. Happy holidays Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1
Re: Mabye OT: Was Re: Different clocks for different instances.
Wel. I feel enlightened. And more secure! Now the Y1752 / Y1582 bugs won't bite me either! : ) Schilling, Ben wrote: Oracle uses the original start date of the Gregorian calendar in October 1582: 1* select to_date('04-OCT-1582'), to_date('04-OCT-1582')+1 from dual devp / TO_DATE('04-OCT-1582 TO_DATE('04-OCT-1582 04-OCT-1582 00:00:00 15-OCT-1582 00:00:00 Ben Schilling Telecomm Manager Office of the Commissioner of Insurance 608-266-1615 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 11:09 AM To: Multiple recipients of list ORACLE-L Well I say, that is also the case on Tru64 as well. How pedantic is UNIX ?? -Original Message- Sent: 19 December 2001 16:16 To: Multiple recipients of list ORACLE-L Speaking of the system clock, here's something interesting that I stumbled across a few years back... Background: For those of you who may not know, the Julian calendar was abandoned by England, in favor of the Gregorian calendar, in 1752 because of date discrepancies. To facilitate this, September 3 - 13 were eliminated. This is actually reflected in UNIX (well, Solaris anyway) by issuing the 'cal 1752' command and looking at the month of September: JulAugSep S M Tu W Th F SS M Tu W Th F SS M Tu W Th F S 1 2 3 4 1 1 2 14 15 16 5 6 7 8 9 10 112 3 4 5 6 7 8 17 18 19 20 21 22 23 12 13 14 15 16 17 189 10 11 12 13 14 15 24 25 26 27 28 29 30 19 20 21 22 23 24 25 16 17 18 19 20 21 22 26 27 28 29 30 31 23 24 25 26 27 28 29 More info. on why the switch over occurred is available at http://www.bicknell.net/books/pc1981/p_calender.htm http://www.bicknell.net/books/pc1981/p_calender.htm , for those of you with morbid curiosity. Anyway - I was doing some Y2K testing a few years back, and playing with NLS_DATE_FORMAT queries when I learned this little tidbit. I decided to test the Oracle date formats because - hey - they'll be okay because they're based on Unix, right? Wrong: Issue the following query: select sysdate ,add_months(sysdate,-2988) ,add_months(sysdate,-2988) - 10 from dual; And see the following results: SYSDATE ADD_MONTHS(SYSDATE,- ADD_MONTHS(SYSDATE,- 19-DEC-2001 10:07:02 19-DEC-1752 10:07:02 09-DEC-1752 10:07:02 The third column represents a date which, technically, does not exist. : ) Brian Kimberly Smith wrote: Oracle uses the system clock. So I would have to say no. -Original Message- Pollard Sent: Tuesday, December 18, 2001 6:50 PM To: Multiple recipients of list ORACLE-L Gooday Oraclers, is it possible to have different instances, on the same dB server, operating with different clocks - ie different date/times? Environment: Compaq-Digital Tru64 Unix, Oracle 8.0.6, DS20 --- Kevin Pollard | PODBA (Pretend Oracle DBA) mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] | Administrative Systems Unit Phone:+61 (02) 6620 3969 | Southern Cross University FAX:+61 (02) 6626 9122 | P.O. Box 157 Room: R1-40a | Lismore NSW 2480, Australia http://staff.scu.edu.au/asu/index.html http://staff.scu.edu.au/asu/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Kevin Pollard 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 http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group |
RE: Help
Dennis, Well, as far as I know, you download it from Oracle's download web-site. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 12:43 PM To: Multiple recipients of list ORACLE-L Paul - How does one get 8.1.7.2.1? We ordered 8.1.7 recently from Oracle, but outside the CD just says 8.1.7. Do I need to download something? Sort of a silly question, but maybe someone else is as dumb as I. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 9:35 AM To: Multiple recipients of list ORACLE-L Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To
Perl DBI broken after 8i upgrade
Hi Ethan, It's great that you and Jared's responses are so quick. Here are the answer : - The 'make test' run without error. - The new version of perl is the first in the PATH. The old perl still exists and also in the path. When type which perl, the system rerturn the new perl - The library path are set as : SHLIB_PATH=/usr/lib:/u01/app/oracle/product/8.1.7/lib:/u01/app/oracle/produc t/8.1.7/network/lib SHLIB=/usr/lib:/u01/app/oracle/product/8.1.7/lib:/u01/app/oracle/product/8.1 .7/network/lib LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/8.1.7/lib:/u01/app/oracle/p roduct/8.1.7/network/lib All references to 8.0 are gone. I will try to search more information on the specified URL Thank you, Thang -Original Message- Sent: Wednesday, December 19, 2001 12:02 PM To: Thang Nguyen (E-mail) -Original Message- Sent: Wednesday, December 19, 2001 12:02 PM To: [EMAIL PROTECTED] Ethan, I don't have any experience on HP, so I can't help with any HP specific stuff. A couple of questions: When Perl was compiled, was 'make test' run, and did it run without error? When building DBD::Oracle: is the newer version of Perl being used and the old one gone from PATH? Is ORACLE_HOME set for 8i, is LD_LIBRARY_PATH set appropriately and all references to 8.0 gone from the environment? A few posts with the same error message on other plaforms suggest a problem with the C compiler. On Solaris for instance, the std /usr/ucb/cc will not compile Perl properly. If you ask this question on the DBI users list, someone is sure to know the anwer. You can find the list at lists.perl.com Also, search the archives. The following URL may help: http://www.xray.mpe.mpg.de/cgi-bin/w3glimpse/dbi?query=DBD+Unable+to+interpr et+OCI+errors=0case=onmaxfiles=100maxlines=30 HTH Jared -- Forwarded Message -- Date: Wed, 19 Dec 2001 11:23:11 -0600 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Jared (King of all Perl and Oracle)... I have a guy I work with sitting next to me who is having some problems with getting DBI to work after an Oracle 8i (8.1.7 HPUX) upgrade from 8.0.6. Everything worked fine before the upgrade. Oracle was installed in a new home. Perl was also installed in a new location (5.6.1). He is using DBI 1.20 and attempting to install the DBD-Oracle-1.12 drivers. During Makefile.PL compile he is hitting a Unable to interpret Oracle oci build commands. Using fallback approach warning. After that pretty much everything else goes wrong. The HPUX readme file says to add in a couple of options to the Perl build to get rid of dld.sl related errors. He did that but is still getting errors for the dld.sh library. It says Can't shl_load() a library containing Thread Storage: /usr/libt/baseFAILED tests 4-5 and then a bunch more errors related to this. The Perl installation was installed after the Oracle upgrade. An earlier version of Perl was alrady installed but was generating core dumps. The initial solution was to upgrade Perl. I can post log files and such if you want. Would really appreciate some pointers on this one. Internet searches have not turned up much. Thanks, Ethan Post --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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-06502: PL/SQL: numeric or value error: character string buffer too small
I am posting this for a co-worker. Anyone seen this before? Seems like there should be an easy answer to this. It's Oracle 8.1.7 on Redhat Linux. Thanks, Marc Perkowitz I am receiving the following Oracle error from a PL/SQL procedure when it attempts to execute a line of code like this: p_out_parameter (of type varchar2) := v_variable (of type varchar2(1023)); Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small If the length of the string stored in v_variable is greater than 256 characters I get the error, otherwise I do not. I have tried explicitly setting the type of p_out_parameter to a table column of type varchar2(2000), but I still get the error. Apparently, the out parameter has a buffer capacity of 256 characters. Does anyone know how to increase the buffer capacity? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marc Perkowitz 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: Encryption - Question about the key
No problems with this system; troubleshooting has not been an issue. The key is actually also stored in management's hidden place of choice (I have no knowledge; it's probably in all likelihood either a cleartext file in email or on a few people's hard drives.) But changing keys is something we're going to need to do, especially as attrition sets in. I had suggested keeping another column on each row as a sort of key sequence (if we convert from one key to another organically as the app uses the data) or key seeding value. You could potentially store millions of keys in a table further obfuscating the true key -- again -- the main idea here is to split the key management work into the application logic to make it more difficult to get the true key. Key management is just as tricky as all the other parts, and certainly what we're doing is a lot better than plainly storing the key in the database, but it's got its own weaknesses. I like the idea of using a hardware device to store/manage the keys -- and have all the encrypt/decrypt happen there, so the key is never sent anywhere. That's about as secure as you can get. As long as you implicitly trust that device .. and have a backup of it so there's no single point of failure.. The way I look at security is (mostly) working to keep the honest people honest. You won't *stop* the truly malicious; your best bet is to set traps to alert yourself to their presence and hope to the deity of choice that they fall for your honeypots. Steve -Original Message- Sent: Tuesday, December 18, 2001 7:49 PM To: [EMAIL PROTECTED]; Austin, Steve S This sounds great until something doesn't work properly. Bet it's difficult to toubleshoot. Has this setup given you any problems in that regard? Jared On Tuesday 18 December 2001 16:25, Austin, Steve S wrote: What we do is have the application manage the encryption keys. The DBA therefore only has access to the encrypted data. Being the DBA in this equation, I am exonerated from having easy access to the keys, and therefore exonerated when it comes time to hunt down perpetrators (well, nearly!) :). I further suggested that they split the key into parts and allow the DBA, root, and the application owner to put in parts to derive the actual key that is not stored anywhere, but exists only in the memory of the app. This did not go over well. :) We're also looking at procedures to change the keys, since any set of encrypted data is a target, and if you change the keys, it's a moving target. hope this is interesting if not amusing. sa -Original Message- Sent: Tuesday, December 18, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Believe it or not Jared, one of your script gave me following idea (the wrapper sql for decrypt/encrypt on your site). 1. I have a system users table, I can add a column to store user's key in a column that only that user has access to. 2. Create a DBA owned package to handle encryption/decryption. 3. The key will be picked up in this package and used (maybe I'll use user key is used to derive the actual key). 4. The package will be deployed as 'wrapped' in production, so by looking at dba_source you won't find much. I'll have to test this though but I think this will make it a bit more secure. The question is Can I trust myself? The answer is 'Yes. Can someone see any drawbacks? Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Austin, Steve 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).
9i
Good Afternoon I am being faced with the 'opportunity' to likely have to install 9i on our production box. I don't have a test environment on which to install. We are running on AIX. Does anyone know what issues exist with 9? Yes, I know that they are going to release another version after the first of the year, but AIX is almost as at end of the line as VMS in the release chain. Thanks in advance for any clues. April begin 666 InterScan_Disclaimer.txt M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@ M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!) M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@ M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE %;G0N#0H end -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: April Wells 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 in Package
Hi List, I spent my morning checking the package trying to figure out what the error is and couldn't find it. Need some help !! 1 -- 2 -- Paquete para Mantenimiento Tabla de Comprobantes 3 -- 4 CREATE OR REPLACE PACKAGE MANTENIMIENTO_COMPROBANTES 5 AS 6 TYPE COMPROBANTES_RECORD IS RECORD 7 ( 8 GRUPO COMPROBANTES.GRUPO%TYPE, 9 COMPANIA COMPROBANTES.COMPANIA%TYPE,10 AGENCIA COMPROBANTES.AGENCIA%TYPE,11 TIPO_COMPROBANTE COMPROBANTES.TIPO_COMPROBANTE%TYPE,12 COMPROBANTE COMPROBANTES.COMPROBANTE%TYPE,13 FECHA COMPROBANTES.FECHA%TYPE,14 AG_CUENTA COMPROBANTES.AG_CUENTA%TYPE,15 CUENTA COMPROBANTES.CUENTA%TYPE,16 DETALLE COMPROBANTES.DETALLE%TYPE,17 CONCEPTO COMPROBANTES.CONCEPTO%TYPE,18 ESTATUS COMPROBANTES.ESTATUS%TYPE,19 ESTATUS_IMPRESION COMPROBANTES.ESTATUS_IMPRESION%TYPE,20 DESC_AGENCIA AGENCIAS.DESCRIPCION%TYPE,21 CUENTA_CONTABLE DATOS_BALANCES.CUENTA_CONTABLE%TYPE,22 DESC_CUENTA CUENTAS.DESCRIPCION%TYPE,23 DESC_CLIENTE CLIENTES.NOMBRE_COMERCIAL%TYPE24 );25 --26 -- Para Seleccionar Registros de la Tabla27 --28 TYPE COMPROBANTES_REFCURSOR IS REF CURSOR29 RETURN COMPROBANTES_RECORD;30 --31 -- Para Instrucciones DML32 --33 TYPE TABLA_COMPROBANTES34 IS TABLE OF COMPROBANTES_RECORD35 INDEX BY BINARY_INTEGER;36 --37 -- Procedimiento para Seleccionar registros de la tabla38 --39 PROCEDURE SELECCIONAR40 (41 PCOMPROBANTESQRY IN OUT COMPROBANTES_REFCURSOR,42 PGRUPO IN COMPROBANTES.GRUPO%TYPE,43 PCOMPANIA IN COMPROBANTES.COMPANIA%TYPE,44 PAGENCIA IN COMPROBANTES.AGENCIA%TYPE,45 PTIPO_COMPROBANTE IN COMPROBANTES.TIPO_COMPROBANTE%TYPE,46 PCOMPROBANTE IN COMPROBANTES.COMPROBANTE%TYPE47 );48 --49 -- Procedimiento para Insertar registros de la tabla50 --51 PROCEDURE INSERTAR (PCOMPROBANTE_REC IN OUT TABLA_COMPROBANTES );52* END MANTENIMIENTO_COMPROBANTES;53 / Package created. SQL EDWrote file afiedt.buf 1 -- 2 -- Desarrollo del Paquete para Mantenimiento Tabla de Comprobantes 3 -- 4 CREATE OR REPLACE PACKAGE BODY MANTENIMIENTO_COMPROBANTES AS 5 -- 6 -- Procedimiento para Seleccionar registros de la tabla 7 -- 8 PROCEDURE SELECCIONAR 9 (10 PCOMPROBANTESQRY IN OUT COMPROBANTES_REFCURSOR,11 PGRUPO IN COMPROBANTES.GRUPO%TYPE,12 PCOMPANIA IN COMPROBANTES.COMPANIA%TYPE,13 PAGENCIA IN COMPROBANTES.AGENCIA%TYPE,14 PTIPO_COMPROBANTE IN COMPROBANTES.TIPO_COMPROBANTE%TYPE,15 PCOMPROBANTE IN COMPROBANTES.COMPROBANTE%TYPE16 ) IS17 BEGIN18 OPEN PCOMPROBANTESQRY19 FOR20 SELECT C.GRUPO, C.COMPANIA, C.TIPO_COMPROBANTE,21 C.COMPROBANTE, C.FECHA, C.AG_CUENTA,22 C.CUENTA, C.DETALLE, C.CONCEPTO,23 C.ESTATUS, C.ESTATUS_IMPRESION, A.DESCRIPCION24 DESC_AGENCIA, D.CUENTA_CONTABLE, CT.DESCRIPCION25 DESC_CUENTA, CTE.NOMBRE_COMERCIAL DESC_CLIENTE26 FROM27 COMPROBANTES C, AGENCIAS A, DATOS_BALANCES D,28 CUENTAS CT, CLIENTES CTE29 WHERE30 C.GRUPO = PGRUPO AND31 C.COMPANIA = PCOMPANIA AND32 C.AGENCIA = PAGENCIA AND33 C.TIPO_COMPROBANTE = PTIPO_COMPROBANTE AND34 C.COMPROBANTE = PCOMPROBANTE AND35 A.AGENCIA = C.AGENCIA AND36 D.GRUPO(+) = C.GRUPO AND37 D.COMPANIA(+) = C.COMPANIA AND38 D.AGENCIA(+) = C.AG_CUENTA AND39 D.CUENTA(+) = C.CUENTA AND40 CT.GRUPO(+) = D.GRUPO AND41 CT.COMPANIA(+) = D.COMPANIA AND42 CT.CUENTA(+) = D.CUENTA_CONTABLE AND43 CTE.CLIENTE = D.CLIENTE;44 END SELECCIONAR;45 --46 -- Procedimiento para Insertar registros de la tabla47 --48 PROCEDURE INSERTAR (PCOMPROBANTE_REC IN OUT TABLA_COMPROBANTES ) IS49 BEGIN50 FOR C IN 1 .. PCOMPROBANTE_REC.COUNT51 LOOP52 INSERT INTO COMPROBANTES53 (54 GRUPO, COMPANIA, AGENCIA,55 TIPO_COMPROBANTE, COMPROBANTE, FECHA,56 AG_CUENTA, CUENTA, DETALLE,57 CONCEPTO, ESTATUS, ESTATUS_IMPRESION58 )59 VALUES60 (61 PCOMPROBANTE_REC(C).GRUPO,62 PCOMPROBANTE_REC(C).COMPANIA,63 PCOMPROBANTE_REC(C).AGENCIA,64 PCOMPROBANTE_REC(C).TIPO_COMPROBANTE,65 PCOMPROBANTE_REC(C).COMPROBANTE,66 PCOMPROBANTE_REC(C).FECHA,67 PCOMPROBANTE_REC(C).AG_CUENTA,68 PCOMPROBANTE_REC(C).CUENTA,69 PCOMPROBANTE_REC(C).DETALLE,70 PCOMPROBANTE_REC(C).CONCEPTO,71 PCOMPROBANTE_REC(C).ESTATUS,72 PCOMPROBANTE_REC(C).ESTATUS_IMPRESION73 );74 END LOOP;75 EXCEPTION76 WHEN DUP_VAL_ON_INDEX THEN77 RAISE_APPLICATION_ERROR78 (-20001, 'Comprobante ya Existe !!!');79 WHEN OTHERS THEN80 RAISE_APPLICATION_ERROR81 (-20002, 'Error en Procedimiento Insercion ');82 END INSERTAR;83* END MANTENIMIENTO_COMPROBANTES;84 / Warning: Package Body created with compilation errors. SQL SHOW ERRORS PACKAGE BODY MANTENIMIENTO_COMPROBANTES;Errors for PACKAGE BODY MANTENIMIENTO_COMPROBANTES: LINE/COL ERROR -15/7 PL/SQL: SQL Statement ignored17/7 PLS-00382: expression is of wrong typeSQL Any suggestions would be appreciated it, TIA, Ramon E. Estevez [EMAIL PROTECTED] Dominican Republic 809-565-3121
RE: Max data file size on NTFS partition
I have had problems creating files above the 4G limit with NTFS. Even files above 2GB were sometimes difficult. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, December 19, 2001 11:16 AM To: Multiple recipients of list ORACLE-L Subject:Max data file size on NTFS partition Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilbert, Ashley 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: 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).
Importing Access tables in Oracle
Dear all, I have a lot of tables built in MS Access. Recently, I am trying to export the database from MS Access to Oracle 8i.From Access, I could export the tables to Oracle through Oracle ODBC driver. The good thing is that this process is relatively fast as Table struture is created automatically and I can actually see these tables and data in Toad. But if I try to execute any SQL, Oracle responds ORA-00942: table or view does not exist. I cannot figure out what is happenning. I welcome any suggestion (including not exporting through ODBC driver) -- 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: ORA-06502: PL/SQL: numeric or value error: character string b
Mark, Your friend does not say how large the variable is, being referenced by the 'out' parameter. My guess is that it is varchar2(256). Have him simply increase the size of the variable in the calling procedure to accomodate the data, and all should be well. HTH Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, December 19, 2001 1:52 PM To: Multiple recipients of list ORACLE-L buffer too small I am posting this for a co-worker. Anyone seen this before? Seems like there should be an easy answer to this. It's Oracle 8.1.7 on Redhat Linux. Thanks, Marc Perkowitz I am receiving the following Oracle error from a PL/SQL procedure when it attempts to execute a line of code like this: p_out_parameter (of type varchar2) := v_variable (of type varchar2(1023)); Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small If the length of the string stored in v_variable is greater than 256 characters I get the error, otherwise I do not. I have tried explicitly setting the type of p_out_parameter to a table column of type varchar2(2000), but I still get the error. Apparently, the out parameter has a buffer capacity of 256 characters. Does anyone know how to increase the buffer capacity? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marc Perkowitz 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: Mercadante, Thomas F 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: Max data file size on NTFS partition
Thanks for replying Ed. We did set disable AUTOEXTEND once we realized it was playing one of the starring roles in our problem. I opened TAR in Metalink and I'll post the answer when I get a response. Thanks, Ashley -Original Message- Sent: Wednesday, December 19, 2001 12:47 PM To: Multiple recipients of list ORACLE-L Hi Ashley, Sorry, don't know exact answer, but the problem doesn't always come from OS side. There are *Oracle* limits on some platforms. It seems you use AUTOEXTEND=ON for your files. If so then you may encountered some bugs when your files had extended through size limits. At least there were some bugs in the past. I prefer disabling this option in order to get more control over db and add files when needed. All files are of equal size. Regards, Ed Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: Gilbert, Ashley INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help
Dennis, There is an 8.1.7.2 superpatch which can be downloaded from Metalink's patch GUI. We have applied that here. I would assume that there is also and 8.1.7.2.1 patchset that you apply over the top of that. Cherie Machler Oracle DBA Gelco Information Network DENNIS WILLIAMS DWILLIAMS@LIFE To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: Help [EMAIL PROTECTED] m 12/19/01 11:42 AM Please respond to ORACLE-L Paul - How does one get 8.1.7.2.1? We ordered 8.1.7 recently from Oracle, but outside the CD just says 8.1.7. Do I need to download something? Sort of a silly question, but maybe someone else is as dumb as I. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 19, 2001 9:35 AM To: Multiple recipients of list ORACLE-L Hello, FYI, my understanding is that 8.1.7.2.0 has problems, and that 8.1.7.2.1 is the way to go. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 10:40 PM To: Multiple recipients of list ORACLE-L If you are running 8.1.7.1, it has memory leak problem. Need to upgrade to 8.1.7.2.0. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/18/01 5:50 PM Your SGA has become fragmented. You would not per chance be running 8.1.7 would you? If so I would go and do some searching on Metalink. I see an upgrade in your future. You could try flushing the SGA but if its what I think it is it won't help. A restart will for a while. -Original Message- Sent: Tuesday, December 18, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Any idea what this means ? Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN DBMS_APPLICATION_INFO,PL/SQL MPCODE,BAMIMA: Bam Buffer) Begin * ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,OLWEAREPORTS,PL/SQL MPCODE,BAMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 3 PasswordNotification12172001.txt: No such file or directory PasswordNotification12172001.err: No such file or directory Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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
RE: Importing Access tables in Oracle
Ensure all table names and column names in Access are in uppercase. If not they will be created in your Oracle data dictionary as lower case which is bad. To drop the tables you will need to do drop table tblFoo; as opposed to the normal drop table foo or drop table FOO not the use of the to get Oracle to see the table. - Ethan -Original Message- Sent: Wednesday, December 19, 2001 1:13 PM To: Multiple recipients of list ORACLE-L Dear all, I have a lot of tables built in MS Access. Recently, I am trying to export the database from MS Access to Oracle 8i.From Access, I could export the tables to Oracle through Oracle ODBC driver. The good thing is that this process is relatively fast as Table struture is created automatically and I can actually see these tables and data in Toad. But if I try to execute any SQL, Oracle responds ORA-00942: table or view does not exist. I cannot figure out what is happenning. I welcome any suggestion (including not exporting through ODBC driver) -- 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: Post, Ethan 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 backup MTS database with RMAN
Title: How to backup MTS database with RMAN Hi, Hulmet, I tried. To use RMAN on MTS database environment, both database server machine and the RMAN machine(the machine you run RMAN on it) should use dedicated server mode instead of shared mode. It means the tnsnames.ora file on these two machines(or one machine) should contain SERVER=DEDICATED line. Maybe only application box can use shared-mode(server=shared). example: SHAGGY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SHAGGY) (server = dedicated) ) ) Kevin Wang - Original Message - From: Daiminger, Helmut To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 19, 2001 5:25 AM Subject: How to backup MTS database with RMAN Hi! Since I'm pretty new to RMAN I have a rather basic question: is it possible to backup databases running in multithreaded server mode with RMAN? Or does this have to be dedicated server mode? $ rman target sys/@kpmgi rcvcat rman/x@admserv Recovery Manager: Release 8.1.7.2.0 - Production RMAN-06005: connected to target database: KPMGI (DBID=3995384462) RMAN-06008: connected to recovery catalog database RMAN register database; RMAN-03022: compiling command: register RMAN-03023: executing command: register RMAN-08006: database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-07005: error during channel cleanup RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery catalog RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT Do I just have to take out mts_dispatchers = "(protocol=TCP)" from the init.ora file? This is 8.1.7.2.0 on Sun Solaris. Thanks, Helmut
Re: Max data file size on NTFS partition
I don't have any problems with 5GB files (564K to be precise, LMT tablespace) on 8.1.5, NT. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 19, 2001 2:00 PM I have had problems creating files above the 4G limit with NTFS. Even files above 2GB were sometimes difficult. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Wednesday, December 19, 2001 11:16 AM To: Multiple recipients of list ORACLE-L Subject: Max data file size on NTFS partition Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilbert, Ashley 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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).
WHICH SID
Hi How do find own session id? Thanks -sEEMA _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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).
Enable Constraint
I am having problems re-enabling a foreign key constraint on a large table. The table with the foreign key constraint has 20M rows and the parent table has 1M rows. The foreign key constraint references the primary key of the parent table. I am surprised that the re-enabling is taking so long - 25minutes. I thought that for each record in the foreign key table, a lookup would be done for the existence of a corresponding primary key in the parent table. I though that this should be relatively quick due to the fact that the column is a primary key and therefore indexed. Am I missing something here? Am I being too critical of the time it takes to do 20M index lookups? Am I mistaken about how a foreign key constraint is re-enabled? Any thoughts would be much appreciated. Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik 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: Redo logs lost, old backups
Please provide an update as to whether this worked or not, I would be interested in knowing. Thanks, Ethan -Original Message- Sent: Wednesday, December 19, 2001 10:15 AM To: Multiple recipients of list ORACLE-L Hi Maser, I am not sure this work or not. Actually my suggestion is call oracle support before you do anything. Just for your reference. 1) BACKUP the current situation with the database closed. The instructions here are destructive. You are STRONGLY advised to backup the current situation before proceeding. If you do NOT do this you may lose the chance to try other options. 2) If your datafiles are from different points in time it is best to try to use system tablespace files at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database. 3) Edit your initSID.ora file and set: _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS = list of all rollback segments Comment out any 'ROLLBACK_SEGMENTS= ' clause. 4) Invoke either server manager (svrmgrl) or SQL*DBA and issue the commands below: connect internal startup mount select * from v$datafile; ... Check here that all files you want to open with are listed as ONLINE (or as SYSTEM). If not: ALTER DATABASE DATAFILE 'full_path_to_file' ONLINE; until all required files are listed as online. ... RECOVER DATABASE UNTIL CANCEL; or RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; At the prompt enter the word: Cancel ALTER DATABASE OPEN RESETLOGS; 5) If this works you should attempt to export the database IMMEDIATELY. Once you have an export the database must be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch. A database which has been opened in this way, but not rebuilt will not be supported by Oracle. Any delay in extracting the contents, or any attempt to use the system may cause irreparable damage. NOTE: Be sure to remove the init.ora parameters added in step 3 otherwise you may accidentally corrupt any new database created using the same init.ora file. 6) It is possible that the OPEN RESETLOGS may fail with an error, or that accessing the data (eg: using export) may fail with an error. In this case note down the exact error and identify any trace files produced then contact Oracle Support Services with this information. Depending on the errors it may be possible to proceed further. Joan Name: arsqaALRT.LOG arsqaALRT.LOG Type: Text Document Maser, Donna (SEA) wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California