Re: connect to the databases without using tnsnames.ora?
Thanks all for the answers. We do use Oracle Names here. Cc Harvest. --- [EMAIL PROTECTED] wrote: Neither sqlnet.ora or tnsnames.ora are required. You can connect to a database by specifying the full connect string. This will work from sqlplus: connect system/manager@(description=(address=(protocol=tcp)(host=remedydev)(port=1521))(connect_data=(sid=orcl))) It works, but is not terribly convenient. I don't imagine your dba was doing this though. Could it be that Oracle Names servers are being used? They don't require tnsnames.ora files. Jared CC Harvest [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 06:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:connect to the databases without using tnsnames.ora? Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
connect to the databases without using tnsnames.ora?
Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Index questions
Thanks Don, Your explanation is very helpful. --- Don Granaman [EMAIL PROTECTED] wrote: Bitmapped indexes will be fast for the queryagainst the 'Y's, but a bitmapped index may not be appropriate if you are getting lots of DML activity on this. Another option is to use NULL instead of the vast majority value ('N' - in this case) and create an index. The nulls won't be indexed, so the index will be small. This works best when there are relatively few non-majority values and they are the ones most critically/commonly queried. A hint MAY be required to make the CBO use the index. This obviousy won't help on queries against 'N' though. Example, ORDERS table with a STATUS column - 98% 'Closed', 2% ('New' | 'Pending' | 'Whatever...'). Change the logical to interprete NULL as 'Closed' and create an index on ORDERS.STATUS. Queries for open orders - ('Closed' | 'Pending' | 'Whatever...') become very fast. Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 15, 2002 2:58 PM If we have a column of 'Y','N' values, the index will not help with CBT(usually most of the values are 'N'). My question is : how about bitmap indexes? Should it help a lot, or just a little bit? Thanks, Chris Harvest __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index questions
If we have a column of 'Y','N' values, the index will not help with CBT(usually most of the values are 'N'). My question is : how about bitmap indexes? Should it help a lot, or just a little bit? Thanks, Chris Harvest __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Index questions
sorry, CBO(Cost-based Optimizer). --- Greg Moore [EMAIL PROTECTED] wrote: What's CBT? If we have a column of 'Y','N' values, the index will not help with CBT(usually most of the values are 'N'). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Quick one about Standby database
You can try. I use the XP as the primary and Win2000 as the standy database machine. It works fine. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Pablo - Usually you create the standby database by copying all the data files from the production system. Then it is an identical system, so you can apply the archive logs from production. With different systems, you couldn't copy the data files. I'm not even sure you could move the archive logs over and use them. I think you are blocked on this idea unless someone that has actually done this can explain how they accomplished it. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 29, 2002 3:36 PM To: Multiple recipients of list ORACLE-L I've read that Oracle does not advise (or may be support) to have a standby database on a plataform different to the original one. I have a database on Solaris and I'd like to create a standby for this db over HP. Can that be done? I KNOW IT'S NOT SUPPORTED. All I want to know is if anyone has tested it. TIA ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CLOB or not?
Oracle has varchar2 limit of 4000. So basically for any columns of over 4000 characters, we need to use CLOB(BLOB). My question is : what's the advantage/disadvantage? Any database design issue(I need to have a separate tablespace). And those columns will also be accessed from web via JDBC calls. Thanks for your input. CC Harvest __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: export the schema exclude two tables?
Claim,Jared,Peter,Rajesh: Thanks for the reply. I just try a different method. exp the two tables to a dump, truncate the tables, exp the rest to another dump. imp the two tables back to the database, ship the secpdn dump to the customer. The customer does not like that two tables, because there are two big and the data is useless. Thanks all. Chris --- [EMAIL PROTECTED] wrote: I tried a different way once - with the PL/SQL extensions to export package. It isn't described in the docs, but there is a file (dbmsexp.sql) in rdbms/admin directory. Basically for each table that needs special treatment, you insert a row in sys.expact$ and identify the PL/SQL code you want to call before or after the table is exported. I had PL/SQL functions that renamed the tables (rename emp to a_emp). It sort of worked; exp renamed the tables before copying the rows and then raised an ORA-942 error because the table wasn't found and then continued with the next table in the schema. One problem was that the exp dmp file still contained the create table statement for the excluded tables. The solution to that was to pre-create dummy tables on the target system with the same names... It was a while ago maybe in Oracle7.3. Right now I can't remember why I did it. Is there a limit on the number of tables you can list for the exp tables parameter? Maybe the issue was to speed up the exp or limit the size of the exp dump file? With a complete export, you can still create dummy tables on the target with the same names as the tables you want to exclude and run imp with ignore=n. This at least excludes certain tables from the imp (if not from the exp). Chaim [EMAIL PROTECTED]@fatcity.com on 01/24/2002 06:50:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table creation scripts, possibly some exutab tables to say obj$.name != Table1, Table2. Hic !! Nooo. I did not say that ;-) What I say is, include all the tablenames except the two that you do not need in your parfile. Regards Raj CC Harvest [EMAIL PROTECTED]@fatcity.com on 01/24/2002 04:55:22 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Anyone knows how to do it in Oracle8.1.7? __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858
export the schema exclude two tables?
Anyone knows how to do it in Oracle8.1.7? __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 set DBWR_WRITE_PROCESSES in Oracle8i?
Thanks Kirti and Paul for the reply. The instance is up for only 3 days. We know there are very heavy inserts in the database. Yes, There are high waits for 'buffer busy waits' event in V$SYSTEM_EVENT view. But I didn't see 'buffer busy waits' in V$SESSION_EVENT. I may try to increase the freelist to 12(I don't know whether it helps, anyway we have 12 Java threads insert data into the same table concurrently). Thanks, Chris --- Deshpande, Kirti [EMAIL PROTECTED] wrote: How long has the instance been up? It appears that the application is trying to read the same data blocks /or more sessions are trying to update the same data block or the free lists may need to be set properly to support concurrent INSERTs. So, before changing anything for DBWR process, I would suggest to check the top wait events from V$SYSTEM_EVENT view. You may see high waits for 'buffer busy wait' event. If so, I would trace it further using V$SESSION_EVENT and V$SESSION_WAIT (P1, P2 values) to find out the 'hot' segment and the file. You may have to review the pctfree, freelists, extent sizes etc. to address 'buffer busy waits'. For all you know, it could be just an application issue in scheduling the processes properly. And to answer your question about setting DBWR_WRITE_PROCESSES(actually the parameter is DB_WRITER_PROCESSES) you may want to check out Note# 97291.1 on Metalink. It has some good information. HTH, - Kirti -Original Message- Sent: Tuesday, January 15, 2002 7:16 PM To: Multiple recipients of list ORACLE-L Hi, gurus: The database is oracle8.1.7.2.1 on win2k machine with 1 CPU and two hard drives. When I query v$waitstat, I got the following: CLASS COUNT TIME -- -- -- data block 246901435 132690159 segment header 4869 8018 undo block 20986 38476 undo header 1895 1556 seems like I need to set DBWR_WRITE_PROCESSES to a higher number, what number should I choose, 2 or bigger? Also, what other problems can you see from the last query, other than we need to add some more RBS? Thanks, Chris Harvest. __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to set DBWR_WRITE_PROCESSES in Oracle8i?
Hi, gurus: The database is oracle8.1.7.2.1 on win2k machine with 1 CPU and two hard drives. When I query v$waitstat, I got the following: CLASS COUNT TIME -- -- -- data block 246901435 132690159 segment header 4869 8018 undo block 20986 38476 undo header 1895 1556 seems like I need to set DBWR_WRITE_PROCESSES to a higher number, what number should I choose, 2 or bigger? Also, what other problems can you see from the last query, other than we need to add some more RBS? Thanks, Chris Harvest. __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Return VArray to Java through JDBC?
Does anyone has the similar experience? I plan to write a stored procedure to return 3 arrays. It works fine in the database. But if I call it from Java codes, I got tons of errors. (the JDBC drivers is from Web Logic, not from Oracle). It would be wonderful if anyone is willing to share the codes. Merry Christmas to you all. CC __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: RE: Where is the memory gone?
greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of === message truncated === __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Freelist question.
I have some stats tables to collect statistics. So I have to insert about 500K records into each table per day. It seems like it has some contention CLASS COUNT data block 1298 free list 0 How do I determine how many freelist for these tables? There will be insert-intensive on these tables, will be archived/deleted after a few weeks. Thanks, Chris Harvest __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally managed tablespace
!! Please do not post Off Topic to this List !! Anyone here tried to use those locally managed tablespace? Some DBA here persuade me to use the locally managed TS for the rollback segment,tables, indexes, temp tablespace Can you tell me what are the benefits of using the locally managed tablespace, any disadvantages? Thanks in advance. Chris Harvest. Creative Consulting. __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle8i Statspack
!! Please do not post Off Topic to this List !! Hi, Gurus: Did you try the Statspack? Will this new feature increase the performance a lot? how about the dbms_stats pachage? Thanks in advance. Chris Harvest. Creative Consulting. __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: High DML Table - Suggestions??
I have a big table too, about 16GB. It is a vert static one(customer table), will add data once in it, but queried all the time, and do the segmentations against it all the time. It also has 12 indexes. Right now , the performance is ok. Any special consideration for such a big table. Thanks, Harvest. --- Christian Trassens [EMAIL PROTECTED] wrote: Don't put it in a buffer keep because of the amount of consistent gets the table could have. It seems to be a high volatile table. Maybe it is too late but you should look on the initrans, freelist of the table. Also if the table has foreign keys, talk with Development to leave the table without them. I should consider to partition the tablemaybe a hash one. Regards. --- Walter K [EMAIL PROTECTED] wrote: Hi, I have a table that is going to have a large amount of inserts, updates and deletes performed against it daily. Approximately 1,000,000 transactions per day (some single-record, some multi-record). The table is ~100Mb in size. I'm looking for some suggestions on what I can do to have the most optimal I/O for the table. I've been doing a little reading about buffer pools. Is assigning this table to a KEEP pool a practical approach or is that not going to buy me anything because DML is involved? Does anyone have any other suggestions? Unfortunately, I can't put the table on a dedicated disk and I am stuck with Raid-5 currently. Any suggestions would be appreciated. Thanks in advance! -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.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). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Comparison of Oracle and SQL server
I remember someone on this list posted a link before, if anyone still has the links, could you post it again? Thanks, CC Harvest __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Update Query
Seems like Oracle Doesn't have to the query like this: update tablea set firstname=tableb.firstname, lastname=tableb.lastname where tableb.id = tablea.id; I can have one, it works if it has a match for the two tables, otherwise the two columns updated to null: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id); Then the following one works, but very ugly: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id) where exists( select 'x' from tableb where tableb.id=tablea.id) Any suggestions? TIA Chris Harvest __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
wrong setting of sort_area_size
we had a customer of the sort_area_size=1000 in their database, the temp table space is 256MB, the db_block_size is 8k. They have some performance problem, I think it's probably related to the setting of sort_area_size(we have only one user). So what's the benefits/disadvantage of too big sort_area_size? Thanks, Chris __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Standard vs Enterprise
We had a customer who are using Oracle8i Enterprise now , and they try to use Oracle8i Standard instead. I don't know why they try to do this(maybe standard cost less??). Anyway, is it easy to accomplish? TIA. Chris. __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Parallel Query Option
I am trying to tune a 30GB database and to configure the initSID.ora files to use PQO. From my understand, they are some back requirements: multiple CPUs of the machine, stripe the tablespace datafiles, etc. If I only have 3 disk drive, do I have to stripe the datafiles to 3 disks, index files to 3 disks? Anyone has the real example of initSID.ora file to share? Thanks, Chris Harvest __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Parallel Query Option
I have 29 physical disks on Sun Solaris 2.8(just talked to SA). And they are all striped. But I have only 1 logical drive. So I cannot stripe on the Oracle side. I have 4 CPUs. And I have a big database with 20GB. And that's a static table, but we have intensive selects on this one(It's our customer info table, and we have to do segmentation from different dimension). Thank you all for the input. Chris Harvest. --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Hi ! Can you tell me how many CPUs you have and the number of PHYSICAL DISKs -Original Message- Sent: Friday, April 20, 2001 9:41 PM To: Multiple recipients of list ORACLE-L I am trying to tune a 30GB database and to configure the initSID.ora files to use PQO. From my understand, they are some back requirements: multiple CPUs of the machine, stripe the tablespace datafiles, etc. If I only have 3 disk drive, do I have to stripe the datafiles to 3 disks, index files to 3 disks? Anyone has the real example of initSID.ora file to share? Thanks, Chris Harvest __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Standard vs Enterprise
I got the following answers from Oracle support: You can convert from Enterprise Edition to standard edition. You need to shutdown and backup the database and software. -- De-install 817 Enterprise Edition. -- install 817 Standard Editon -- start the database and run catalog.sql and catproc.sql --- Rocky Welch [EMAIL PROTECTED] wrote: Are you sure? I read that the data dictionaries are not compatible. I know to go from standard to enterprise you must take an export, load the software, create a new instance, then import. --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Hi ! The downgrade (if you call it) is pretty simple and straight forward. Just shutdown the database (and take backups!!) Remove the EE and Install the SE and create the service and restart the database . -Original Message- Sent: Friday, April 20, 2001 9:26 PM To: Multiple recipients of list ORACLE-L We had a customer who are using Oracle8i Enterprise now , and they try to use Oracle8i Standard instead. I don't know why they try to do this(maybe standard cost less??). Anyway, is it easy to accomplish? TIA. Chris. __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rocky Welch INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Design
Thanks Dick and Lisa for answering my question. I think I am going to either let the file auto-extend, or will try a smaller file as a start. I found my temp tablespace is too small(1GB) because it seems like takes forever to rebuild an index with nologging. I have 11 indexes on this tables, and it took me tons hours to do the index rebuilding. My application is a mixed system with 10% batch processing, and 90% OLAP. But we need the 10% batch processing part should be really fast. Thanks, Chris --- [EMAIL PROTECTED] wrote: Chris, First let me say that I have a TON of respect for Mike and count him as a friend. That said, I also take exception to many of his pronouncements from a practical, not theoretical, point of view. Given infinite resources, like disk space and memory and CPU, he does have it absolutely right. But in the real world there is infinite nothing. The first item on my list here it to look at what temp space is used for. It's mainly used for sorting, grouping, and distinct operations. These are the normal things that involve temp segments, and in a day to day operation that will consume an amount of space. The other item their used for is index building, which is not a normal day to day operation. Therefore the need for an extremely large temp tablespace is a sporadic and plan able event. Second, comes the question of the purpose of the database. If your building an OLTP system then temp usage is going to be even less since the majority of actions will affect few rows at one time. If it's a data warehouse on the other hand then data mining operations tend to make extreme use of temp for group and sort operations, but even so the amount of data being processed will not hit the extremes and when it does it's most likely bogus in the first place. My favorite in this vein is our CIO who let loose a Cartesian product query just because he forgot to join the fact table to the other tables. In this case the lack of temp space brought the query to a halt quickly and mercifully. OK, so where should you go? Well, I'll get into our DB's which range from our 150GB data warehouse to our 200GB operational data store. The former has 1GB of temp storage for normal operations. The latter gets along very well on 400MB of temp space. Both have a 14GB disk area that they share as required for those monster index rebuilds. Where you go from here is a lot of personal decision. I recommend starting small working your way up as necessary. The easiest way to do that is to enable auto-extend. Dick Goulet Reply Separator Author: CC Harvest [EMAIL PROTECTED] Date: 4/12/2001 12:05 AM What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858
RE: Temporary Tablespace Design
Kirti: The server is solely used for the Oracle Database and it has 2GB Ram. I didn't increase sort_area_size too much because I thought the table is so big, and probablay I could not run them in the memory. That's why I just try the temp tablespace. Thanks, Chris --- "Deshpande, Kirti" [EMAIL PROTECTED] wrote: I would suggest that you increase (as much as possible) sort_area_size and sort_area_retained_size for your session when building indexes to minimize temporary tablespace use. Making temporary tablepspace of type temporary and adjusting default initial next extent size can also help. HTH.. - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: CC Harvest [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 12, 2001 11:23 AM To: Multiple recipients of list ORACLE-L Subject:Re:Temporary Tablespace Design Thanks Dick and Lisa for answering my question. I think I am going to either let the file auto-extend, or will try a smaller file as a start. I found my temp tablespace is too small(1GB) because it seems like takes forever to rebuild an index with nologging. I have 11 indexes on this tables, and it took me tons hours to do the index rebuilding. My application is a mixed system with 10% batch processing, and 90% OLAP. But we need the 10% batch processing part should be really fast. Thanks, Chris --- [EMAIL PROTECTED] wrote: Chris, First let me say that I have a TON of respect for Mike and count him as a friend. That said, I also take exception to many of his pronouncements from a practical, not theoretical, point of view. Given infinite resources, like disk space and memory and CPU, he does have it absolutely right. But in the real world there is infinite nothing. The first item on my list here it to look at what temp space is used for. It's mainly used for sorting, grouping, and distinct operations. These are the normal things that involve temp segments, and in a day to day operation that will consume an amount of space. The other item their used for is index building, which is not a normal day to day operation. Therefore the need for an extremely large temp tablespace is a sporadic and plan able event. Second, comes the question of the purpose of the database. If your building an OLTP system then temp usage is going to be even less since the majority of actions will affect few rows at one time. If it's a data warehouse on the other hand then data mining operations tend to make extreme use of temp for group and sort operations, but even so the amount of data being processed will not hit the extremes and when it does it's most likely bogus in the first place. My favorite in this vein is our CIO who let loose a Cartesian product query just because he forgot to join the fact table to the other tables. In this case the lack of temp space brought the query to a halt quickly and mercifully. OK, so where should you go? Well, I'll get into our DB's which range from our 150GB data warehouse to our 200GB operational data store. The former has 1GB of temp storage for normal operations. The latter gets along very well on 400MB of temp space. Both have a 14GB disk area that they share as required for those monster index rebuilds. Where you go from here is a lot of personal decision. I recommend starting small working your way up as necessary. The easiest way to do that is to enable auto-extend. Dick Goulet Reply Separator Author: CC Harvest [EMAIL PROTECTED] Date: 4/12/2001 12:05 AM What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-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: What kind of date format?
Prakash, Thanks for the email. I think I will have to try to create one varchar2 field, then drop it after the data loaded. Thanks, Chris --- "Bala, Prakash" [EMAIL PROTECTED] wrote: Chris, Since Oracle cannot handle milliseconds, one of my colleaque wrote a stored procedure to manipulate the date and then dump the sybase data into a flat file and used sqlldr to load into Oracle. Another solution will be load this date into a varchar2 column and use a combination of substr and to_date to ignore the milliseconds. Will that work? Prakash -Original Message- Sent: Wednesday, April 04, 2001 9:56 PM To: Multiple recipients of list ORACLE-L I used bcp to get the database from a sybase database, and plan to load the data back to Oracle. The only problem is that I got the following defualt date format from Sybase: Apr 3 2001 10:29:47:000AM What's the corresponding date format in Oracle? ('Mon dd hh:mi:ss:???AM') And did anyone migrate the database from Sybase or SQL Server to Oracle before? Thanks, Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What kind of date format?
I used bcp to get the database from a sybase database, and plan to load the data back to Oracle. The only problem is that I got the following defualt date format from Sybase: Apr 3 2001 10:29:47:000AM What's the corresponding date format in Oracle? ('Mon dd hh:mi:ss:???AM') And did anyone migrate the database from Sybase or SQL Server to Oracle before? Thanks, Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 is faster??
I have the following scripts: insert into table select * from table2 ; So if use the about bulk statement in my application, and the table2 is big, say 10 million records, my concern is that it's going to fail because of the possible rollback segments failure. So then I have to use PL/SQL to create a cursor and commit every 5 records. What's the disadvantage of this?Will it be much slower than a bulk insert? Can I do it another way: create a stored procedure for this bulk insert, then pin this procedure in memory, does it still have RBS problem? Anyone has similar experience? Thanks in Advance, Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Drop a column in a 9 million records table
The database is Oracle8.1.7. I used the syntax: alter table foo drop column bar; Why it gives run out of RBS error? Is alter table a DDL? How does it use RBS? TIA Chris Harvest __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).