RE: Inserts are taking time !
Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 long did job run?
Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like "last time". Thanks
universal installer problem under win2k
Hi, did anyone successfully download the 9iDevelopmentSuite from OTN? I did so yesterday and the build process seemd to work correctly. But wanting to install the software I have a problem with the Universal installer. When I press the continue button on the initial screen the installer just aborts. I tried to use the installer from the 901 database release but the installer tells me I need a more current release. Is it a problem in my download - any corruption or is it a problem in the installer itself? The version of the installer is 2.1.0.10.0. Antje Sackwitz -- 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: How long did job run?
LAST_DATE or LAST_SEC from USER_JOBS JP On Thursday 05 September 2002 10:43, you wrote: Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like last time. Thanks -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sampling V$SESSTAT
How about this for a strategy? I'm only suggesting this because its what I implemented on a site some time back where the requirement was we want to get stats, but you cannot store anything on our database (so statspack et al was prohibited). So we ended up with a stealth storage approach: We dumped into a database pipe (one row from v$sesstat = one message) the non-zero stats. Then every 'x' mins, we read back from the pipe, spit out anything that has a positive delta from the current v$sesstat, and store the current set back into the pipe. In this way, we got close to reasonable stats, the shortfalls being a few data inconsistencies with sid's that get reused across sampling periods. The pipe also nicely handles the db shutdown issue (simply because the pipe disappears so you counters get automatically reset) hth connor --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: I want to start sampling this table, however collecting data on the 200+ statistics for each session would produce a prohibitively large result. I'm trying to pare the 225 statistics to something more reasonable, but I cannot decide which ones to discard and which to record. Does anyone have a listing of the most useful statistics that they would like to share? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Inserts are taking time !
Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: How long did job run?
SELECT to_char(LAST_DATE, 'DD-MON- HH:MI:SS')FROM DBA_JOBS WHERE job = job_id. This will give you the last date and the time when a particular job was successfully executed Naveen -Original Message-From: Vladimir Barac [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 2:13 PMTo: Multiple recipients of list ORACLE-LSubject: How long did job run? Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like "last time". Thanks
RE: ssh client for Windoze
I use PuTTY as well.. It's freeware though.. -Original Message- [EMAIL PROTECTED] Sent: 04 September 2002 18:19 To: Multiple recipients of list ORACLE-L TeraTerm (does not support ssh2) Hummingbird Reflection PuTTY (current fave) Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Peter Barnett [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Subject: ssh client for Windoze We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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: How long did job run?
Hi I think in your job you have to add some code that does an entry in a table before and after it runs so if you schedule a procedure than you can add some code to the procedure before: insert into table table values ( scheduled procedure , sysdate,null) commit; after: update table set end_time = sysdate where name = 'scheduled procedure' and end_time is null; commit; Jack Vladimir Barac [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: How long did job run? 05-09-2002 10:43 Please respond to ORACLE-L Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like last time. Thanks === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
RE: How to speed up import
Are there any Limitations with using the 2 exp parameters :- DIRECT=Y RECORDLENGTH=65535 -Original Message- Sent: Wednesday, September 04, 2002 7:09 PM To: Multiple recipients of list ORACLE-L First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance. HTH... - Kirti Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage (if rollback segments are okay, do not use this). 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes and such, I keep the quota on the target tablespace to 0 to make it fail during import (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
Row Length of Index ?
How can it be Obtained ? Does the principle which Applies to Table also apply to index ? SQL select AVG(nvl(vsize(1st Key Field),1)+ nvl(vsize(2nd Key Field),1)+ ...) FROM Table Containing respective Index; Where (1st Key Field,2nd Key Field) give the Definition of the Index -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 long did job run?
You can find it from last_date column from that table: SQL alter session set nls_date_format='dd.mm. hh24:mi:ss'; SQL select LAST_DATE from dba_jobs; LAST_DATE---05.09.2002 12:39:31 HTH Jorma -Original Message-From: ext Vladimir Barac [mailto:[EMAIL PROTECTED]]Sent: 05 September, 2002 11:43To: Multiple recipients of list ORACLE-LSubject: How long did job run? Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like "last time". Thanks
OT: recommend nice hotel convenient to Open World
Hi, Can anyone recommend nice,convenient hotel to Open World. I have never been there and need recommendations. Please respond off-list. Thanks Rick -- 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).
java stored procedures
This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to speed up import
Not that I am aware of. But I can not rule out existence of bugs in some versions of 8i. - Kirti -Original Message- Sent: Thursday, September 05, 2002 4:45 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Are there any Limitations with using the 2 exp parameters :- DIRECT=Y RECORDLENGTH=65535 -Original Message- Sent: Wednesday, September 04, 2002 7:09 PM To: Multiple recipients of list ORACLE-L First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance. HTH... - Kirti Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage (if rollback segments are okay, do not use this). 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes and such, I keep the quota on the target tablespace to 0 to make it fail during import (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: ssh client for Windoze
SecureCRT works best out of the ones I've looked at... http://www.vandyke.com/products/securecrt/ Andy. -- Andrew Sit Systems Engineer DataMirror Corporation + 1 905 415 0310 (O) + 1 416 839 9908 (M) -Original Message- Sent: Thursday, September 05, 2002 5:48 AM To: Multiple recipients of list ORACLE-L I use PuTTY as well.. It's freeware though.. -Original Message- [EMAIL PROTECTED] Sent: 04 September 2002 18:19 To: Multiple recipients of list ORACLE-L TeraTerm (does not support ssh2) Hummingbird Reflection PuTTY (current fave) Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Peter Barnett [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Subject: ssh client for Windoze We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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: Andrew Sit INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Temp problem
Which Oracle version are you using, Peter? Regards, 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] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 7:08 PM To: Multiple recipients of list ORACLE-L Subject:RE: Temp problem add space to the temp tablespace. ALTER TABLESPACE... Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Peter R [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Subject: Temp problem Hi friends, My temp tablespace is showing full!! I try to wakeup smon thru oradebug wakeup 6; but getting error..I also used alter tablespace temp storage(pctincrease 0);..But it could't release space...any ideas to avoid bouncing the database??? tia peter. _ 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: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: 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: Inserts are taking time !
Jared, This stuff for fun was great!!! http://www.computerworld.com/departments/opinions/letters/0,10817,73890,00.h tml :) - Kirti -Original Message- Sent: Wednesday, September 04, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Some of us have work to do, you know ;) Not me, I'm independently wealthy. I just stay up late to do this stuff for fun. Jared ;) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Inserts are taking time ! Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -- 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).
Export question on excluding certain schema
Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? 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! *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.*2
RE: Row Length of Index ?
Hi Vivek, You should take into account the space for storing rowids...and index can be compressed. validate index your_index; Select lf_rows_len from index_stats; Regards, Ed How can it be Obtained ? Does the principle which Applies to Table also apply to index ? SQL select AVG(nvl(vsize(1st Key Field),1)+ nvl(vsize(2nd Key SQL Field),1)+ ...) FROM Table Containing respective Index; Where (1st Key Field,2nd Key Field) give the Definition of the Index -- 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: ssh client for Windoze
Or how about PocketAdmin? Maybe not for Windows, but it *will* run on Embedix (http://www.embedix.com/) ;) Shameless Plug Also available from Cool-Tools ;P /Shameless Plug Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -==Non illigitamus carborundum==- -Original Message- Sent: 05 September 2002 14:23 To: Multiple recipients of list ORACLE-L SecureCRT works best out of the ones I've looked at... http://www.vandyke.com/products/securecrt/ Andy. -- Andrew Sit Systems Engineer DataMirror Corporation + 1 905 415 0310 (O) + 1 416 839 9908 (M) -Original Message- Sent: Thursday, September 05, 2002 5:48 AM To: Multiple recipients of list ORACLE-L I use PuTTY as well.. It's freeware though.. -Original Message- [EMAIL PROTECTED] Sent: 04 September 2002 18:19 To: Multiple recipients of list ORACLE-L TeraTerm (does not support ssh2) Hummingbird Reflection PuTTY (current fave) Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Peter Barnett [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Subject: ssh client for Windoze We are looking for an ssh client for our desktops. Our early testing has been with a product from F-Secure which has some major configuration issues when used in an environment connecting to multiple Unix servers and multiple instances. Open source is out of the question. Has anyone used an Windows ssh client in a large environment (50 +/- Unix servers, 300+ instances) that they would recommend? = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 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: Andrew Sit INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Replication question
Paul - I don't know multimaster replication, so bear with me. I am thumbing through my copy of Oracle Distributed Systems by Charles Dye. Does your question relate to how propagation is controlled? I think propagation is controlled by scheduled jobs. Take a look at the procedure DBMS_DEFER_SYS.SCHEDULE_PUSH, which the book says Schedules an automatic push of the deftran queue to the specified master database. Hope this helps, it may irritate someone that really knows the answer to your question into replying. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 04, 2002 8:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Export question on excluding certain schema
Thanks Kirti ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, September 05, 2002 9:26 AM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Raj, The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. This is as simple as it gets! - Kirti -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? 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! *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.*2
RE: Any woraround for this ....?
Title: Message What is the line of code at line 119? Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
RE: Export question on excluding certain schema
Raj, The answer is: Yes. You will need to edit catexp.sql to rebuild internal view to exclude interested owner#. This is as simple as it gets! - Kirti -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? 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! -- 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: Replication question
Paul, The procedures are executed by a special internal trigger. These triggers are also NOT dropped by catrepr.sql (yes, I found out the hard way!). It is documented in metalink. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Wednesday, September 04, 2002 9:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Any woraround for this ....?
Title: Message Muths, What is the purpose of having the table data dumps? If you're just going to use them to load data back into Oracle, why not just use table level export/import. -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 1:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Any woraround for this ? Thanks. I did as you said but now with the following error. It writes for the first table and then this error crops. SQL exec extractBEGIN extract; END; *ERROR at line 1:ORA-20001: 1User-Defined ExceptionORA-06512: at "SCOTT.EXTRACT", line 119ORA-06512: at line 1 Am i missing some thing? Rgds, Muths - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 7:18 PM Subject: RE: Any woraround for this ? Remove all the dbms_output calls and use utl_file. It should get around this error, and its not possible to get the below error with utl_file so you must have missed something when you tried it the first time. HTH, Beth -Original Message-From: Muthaiah, VSNL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: Any woraround for this ? Hi, I'm writing a procedure/script for extract the data of all the tables in a schema. When I am trying to spool/write into a file, I am getting the following error. ORA-20001: -2ORA-2: ORU-10028: line length overflow, limit of 255 bytes per lineORA-06512: at "SCOTT.EXTRACT", line 115ORA-06512: at line 1 First I tried to spool to a file.But got the error line length overflow. I have tried using the UTL_FILE option also. But getting the same error. Can anyone in the list has any work around for this? Thanks in Advance, Muths
RE: Replication question
Title: RE: Replication question Great book, I have it too! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Subject: RE: Replication question Paul - I don't know multimaster replication, so bear with me. I am thumbing through my copy of Oracle Distributed Systems by Charles Dye. Does your question relate to how propagation is controlled? I think propagation is controlled by scheduled jobs. Take a look at the procedure DBMS_DEFER_SYS.SCHEDULE_PUSH, which the book says Schedules an automatic push of the deftran queue to the specified master database. Hope this helps, it may irritate someone that really knows the answer to your question into replying. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 04, 2002 8:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
Optimal DBA Library and recommendations to similar published material.
Hello All I was reading Dan Finks site, Particullary SQLScriptingSorceryPaper http://www.orcldba.com/library.html And found the material very interesting. Id like to get a recommendation for an advanced sql book which covers material similar to Dan site which feature creative problem solving tips I was going to mail Dan directly to find what text book he would recommend, but figured others would benefit form the recommendations. Most of the books I have barely go beyond joining 2 tables. Id like to get some recommendations for the *best* sql reference/techniques. I've learned allot for Dans site but really like to have a physical book Thanks Bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: java stored procedures
Rachel, Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). I don't have any gotchas but some tidbits AFAIK. You'll need the SQLJ, JDBC drivers, and JDK running machine used for development and your 8i running the JServer option as well Net8 or above. You'll be stuck with JDK 1.2.X as it is the common JDK supported by 8.17 and 9i versions of SQLJ. Cheers, Thom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farrell, Thomas M.Mr. NGB-ARNG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 question on excluding certain schema
Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:43 AM To: Multiple recipients of list ORACLE-L Is it possible to exclude certain sys type schema when we do export? We are (will be) migrating from 8161 to 9201 and would like to exclude MDSYS and CTXSYS from (8161) export. Is there a simple way to achieve this?? 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! -- 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: Configuring 734 and 817 on same machine.
Title: Configuring 734 and 817 on same machine. We have 7.3.4 and 8.1.6 working on the same machine with no problems. 7.3.4 was installed first and then 8.1.6. Listener is 8.1.6. Do not forget to shut down ALL 7.3.4 services and processes before installing 8.1.6. Yechiel AdarMehish - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Friday, August 30, 2002 10:28 AM Subject: Configuring 734 and 817 on same machine. Hello Gurus, Does anyone have a link to a resource which describes the Installation and configuration of Oracle 734 and Oracle 817 on the same Windows 2000 machine. The configuration of the listeners is my main concern. Any help on this is very much appreciated. Regards Denham Eva DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Contract Forwarding, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: How to speed up import
If believe that prior to 8i if you had a table with long or lob data and you used direct=y, only the DDL was exported. The data was left behind. In 8i and above, Oracle switches from direct to conventional and exports both the ddl and data for tables containing long/lob data. You will get a warning in the export log. steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 5:48 AM Are there any Limitations with using the 2 exp parameters :- DIRECT=Y RECORDLENGTH=65535 -Original Message- Sent: Wednesday, September 04, 2002 7:09 PM To: Multiple recipients of list ORACLE-L First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance. HTH... - Kirti Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage (if rollback segments are okay, do not use this). 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes and such, I keep the quota on the target tablespace to 0 to make it fail during import (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: java stored procedures
Actually, Java is not so bad idea. Aurora does pretty much the same thing as PL/SQL and it is very hard to get to OS or network because security doesn't let you. On the plus side, bad programs in Java are more portable then the ones in PL/SQL. IDE software for Java is better suited for duhvelopers then the one for PL/SQL. You will have to increase Java pool and you will have to increase system tablespace because your developers will start abusing loadjava almost instantly and start loading .jar files (like for XML, for instance). Also, your CPU and RAM usage will go up fairly quickly so you might consider convincing your boss to finally do that HW upgrade. Expect attempts to use thin JDBC driver. The nice features of the thin driver are a) It's written entirely in Java, which makes it much slower then an optimized C code like OCI driver and, therefore, preferred by duhvelopers in the name of generality and universality. Stupidity is another very universal thing. b) The thin driver can only use dedicated server connection which does miracles for load balancing. There is connection pooling, which also requires dedicated server to work. Because it cannot use MTS and TNS in general, thin driver must have a hard-wired connection string written in the code or parameter file, which apparently doesn't diminish generality and universality. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Subject: java stored procedures This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ALTER TABLE MOVE command causes table to grow
Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Replication question
Thanks, John. Do you know what mechanism causes the internal triggers to fire? Are the internal triggers created by catrep.sql, or by DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for each table? Does the RDBMS have to determine whether --- John Weatherman [EMAIL PROTECTED] wrote: Paul, The procedures are executed by a special internal trigger. These triggers are also NOT dropped by catrepr.sql (yes, I found out the hard way!). It is documented in metalink. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Wednesday, September 04, 2002 9:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Configuring 734 and 817 on same machine.
Title: Configuring 734 and 817 on same machine. Note that 7.3.4 was never supported on Windows 2000. -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Configuring 734 and 817 on same machine. We have 7.3.4 and 8.1.6 working on the same machine with no problems. 7.3.4 was installed first and then 8.1.6. Listener is 8.1.6. Do not forget to shut down ALL 7.3.4 services and processes before installing 8.1.6. Yechiel AdarMehish - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Friday, August 30, 2002 10:28 AM Subject: Configuring 734 and 817 on same machine. Hello Gurus, Does anyone have a link to a resource which describes the Installation and configuration of Oracle 734 and Oracle 817 on the same Windows 2000 machine. The configuration of the listeners is my main concern. Any help on this is very much appreciated. Regards Denham Eva DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Contract Forwarding, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
ldap compatibility
List Can anyone tell me how to set up a version 8.1.7 oracle client to use a version 9.2.0 OID (ldap) server, or vice-versa? Every time I try it (using the net configuration assistant, ugh) I get a message saying that the directory service '...does not contain the required Oracle schema or the schema is the wrong version.' It works fine if the client and server are the same version and I can find nothing in the FM about it. I'm beginning to really hate these oracle GUIs grrr TIA David Lord -- David Lord Senior DBA, Hays Consulting Solutions -- Tel: +44 (0)29 2054 4013 Email: [EMAIL PROTECTED] Fax: +44 (0)29 2069 2464 ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Inserts are taking time !
Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. But if this auto-commit is the issue than why first 10K records are inserted quickly. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
Re: ALTER TABLE MOVE command causes table to grow
But that's 10% per block. If all of the blocks had used the pctfree area then would have a lot of growth. [EMAIL PROTECTED] 9/5/02 12:08:30 PM Had an annoying surprise last week. A table had grown unexpectedly largeand I scheduled a time over the weekend to move it to its own tablespacefrom my "medium" tablespace. The table ended up growing 50%. I had anticipated it might grow somewhatgiven the PCTFREE of 10% but freeing up that space in the blocks should, atmost, have caused it to grow by 10% (assuming that 10% was completely full).Does anyone have ideas as to why it would have grown by so much? Indexesare in a different tablespace and the only other change was from an extentsize of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2Solaris 2.6Thanks,Jay Miller-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Miller, Jay INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: java stored procedures
You'll be stuck with JDK 1.2.X as it is the common JDK supported by 8.17 and 9i versions of SQLJ. However, the 9iR2 OUI will ask for JDK Home for JDK/SDK Ver 1.3.1. Check Note# 201110.1 on Metalink. - Kirti -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Rachel, Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). I don't have any gotchas but some tidbits AFAIK. You'll need the SQLJ, JDBC drivers, and JDK running machine used for development and your 8i running the JServer option as well Net8 or above. You'll be stuck with JDK 1.2.X as it is the common JDK supported by 8.17 and 9i versions of SQLJ. Cheers, Thom -- 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: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Subject: RE: Export question on excluding certain schema Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] *2 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. *2
RE: Replication question
The jobs are used to propogate the captured transactions. The transactions are captured by the packages called by the internal triggers. PAX, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, September 05, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Paul - I don't know multimaster replication, so bear with me. I am thumbing through my copy of Oracle Distributed Systems by Charles Dye. Does your question relate to how propagation is controlled? I think propagation is controlled by scheduled jobs. Take a look at the procedure DBMS_DEFER_SYS.SCHEDULE_PUSH, which the book says Schedules an automatic push of the deftran queue to the specified master database. Hope this helps, it may irritate someone that really knows the answer to your question into replying. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 04, 2002 8:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Question about database and service name
Hi list, Reading the OU manuals Oracle 9i DBA Fundamentals II , in chapter 2 page 12, found 1-) An oracle database is represented to clients as a service. 2-) A database can have one or more services associated with it. 3-) A database can be presented as multiple services and a service can be implemented as multiple database instances. For the No. 2 : I interpret that I can have 2 or 3 or 4 services for just ONE DB. Is that correct ?. How can I do that ? For the No. 3: I interpret that I can refer to the same DB with differents names. As far as I understand, the DB instance name is unique, it can't be changed. So how can I create several services names for one DB. Or is just a trick in the TNSNAMES.ORA and LISTENER.ORA files. Please can anyone give some light in that, I am totally confused !!! Ramon E. Estevez[EMAIL PROTECTED]809-565-3121
any tips on migrating from 7.3.4 to 8.1.7
Hello All, I am currently involved in a migration project which involves combining two disparate databases into a single Oracle Engine. One of the databases currently lives on Oracle 7.3.4 and the other is 8.1.5 (both desupported by Oracle). My task is to upgrade to a single instance of 8.1.7 (and eventually 9.2.x). Are there any road bumps that I should be aware of before undertaking this endeavor? Any tricks/tips are appreciated. Thank you in advance Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fail Over Options
I heard that there is a network switch or router that does dynamic translation of IP addresses. So you can always connect to one address and change the translation in the switch/router in case of fail over. This should be transparent to your thin jdbc client. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 11:18 PM Ricjard is correct. jdbc thin client does not allow transparent fail over. The thick client reads a tnsnames.ora file which is where the fail over actually occurs. There are multiple server names for the same service name. If the first server in the search is down, it falls through to the next server. jdbc is a straight connection to a single database. No read of tnsnames.ora. Pete --- Ji, Richard [EMAIL PROTECTED] wrote: Dennis, One thing you can't do with Thin driver is Transparent Application Failover which is what Peter wants to do I believe. Richard -Original Message- Sent: Wednesday, August 28, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Peter - A couple of issues come to mind: 1) So you are saying that connection failover is your prime interest at this stage? I was thinking database failover when I saw your question. 2) So what is wrong with thin client? At my site we are tending to go with thin so we don't have to install SQL*Net client on every app server. That was what attracted us to Java in the first place. Everything I've seen says that for some applications thin has better performance and for others OCI is better. Personally I like having options. 3) How committed to Oracle is your site? If you have a heavy Oracle commitment, you may be able to stick it to IBM's attitude. Traditionally IBM has had an attitude of use all of our stuff. This goes back to when they were the Microsoft of the computer industry. Nobody worries about IBM monopolizing the industry today. Obviously they are learning to shed that parochial attitude in order to succeed today. I'll bet if you complained up the IBM management chain about this person's attitude you might see a change for the better. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 27, 2002 5:49 PM To: Multiple recipients of list ORACLE-L Dennis, This is a auite of products. The prime offender is WebSphere, an IBM product. The IBM consultants have been particularly difficult to deal with. According to them, nothing Oracle ever produced works. The project specifications call for transparent fail over which is possible using Oracle's thick client. Unfortunately, WebSphere does not seem to be able to use anything but jdbc thin client. If we can't get this to work, we will likely go to something like Veritas Cluster Server. Replication provided a solution that had already paid for. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Peter - Have you looked at Oracle Standby Server? Which App server are you using? Most of them claim to help you with this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 27, 2002 3:38 PM To: Multiple recipients of list ORACLE-L We are looking at fail over options for databases in a 7x24 e-commerce environment. Currently, we are using advanced replication. It works but it is labor intensive. We have also discovered that the application can only use jdbc rather than the Oracle thick client. In essence, this replication is now manual fail over. Would someone who has made java and the thick client work provide some pointers to getting it to work? Thanks, = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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]
RE: java stored procedures
Mladen, Thanks -- one thing I can be sure of is that they won't start loading files, at least in staging and production, because I haven't given them (nor will the data center do so) permission. Everything has to do through either me or change control procedures. Or both. I knew about the java pool. This is a brand new system so asking for a hardware upgrade is not likely. They want to call this function every time someone does a search on the site. This goes back to the discussion I had a week or so ago about the diacritical marks, the developer thinks people will be entering them... cut and paste? Not likely. Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: Actually, Java is not so bad idea. Aurora does pretty much the same thing as PL/SQL and it is very hard to get to OS or network because security doesn't let you. On the plus side, bad programs in Java are more portable then the ones in PL/SQL. IDE software for Java is better suited for duhvelopers then the one for PL/SQL. You will have to increase Java pool and you will have to increase system tablespace because your developers will start abusing loadjava almost instantly and start loading .jar files (like for XML, for instance). Also, your CPU and RAM usage will go up fairly quickly so you might consider convincing your boss to finally do that HW upgrade. Expect attempts to use thin JDBC driver. The nice features of the thin driver are a) It's written entirely in Java, which makes it much slower then an optimized C code like OCI driver and, therefore, preferred by duhvelopers in the name of generality and universality. Stupidity is another very universal thing. b) The thin driver can only use dedicated server connection which does miracles for load balancing. There is connection pooling, which also requires dedicated server to work. Because it cannot use MTS and TNS in general, thin driver must have a hard-wired connection string written in the code or parameter file, which apparently doesn't diminish generality and universality. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Subject: java stored procedures This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: Optimal DBA Library and recommendations to similar published
One of the challenges in writing that paper/presentation was the lack of material available. Much of what you see was gleaned from oracle documentation, white papers, presentations, metalink, orafaq.com, orageeks.com, colleagues, questions at presentations, material from classes, and my own 'I wonder if this is possible?'. There is also a very healthy dose of things I have learned over the years, but have forgotten the source. I have used 'Oracle The Complete Reference' as my main training text and am happy with it, of course it covers more than just SQL. I spent several hours looking through books like 'SQL For Smarties', but was not very impressed (I don't recall exactly why...its been a couple of years). There are several books that were published since I first wrote the paper. 'Mastering Oracle SQL' and 'Oracle SQL:The Essential Reference' have pretty good reviews at amazon.com. Dan Fink -Original Message- Sent: Thursday, September 05, 2002 7:48 AM To: Multiple recipients of list ORACLE-L material. Hello All I was reading Dan Finks site, Particullary SQLScriptingSorceryPaper http://www.orcldba.com/library.html And found the material very interesting. Id like to get a recommendation for an advanced sql book which covers material similar to Dan site which feature creative problem solving tips I was going to mail Dan directly to find what text book he would recommend, but figured others would benefit form the recommendations. Most of the books I have barely go beyond joining 2 tables. Id like to get some recommendations for the *best* sql reference/techniques. I've learned allot for Dans site but really like to have a physical book Thanks Bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: java stored procedures
We have several developers using java stored procedures. No real 'gotchas' other than making sure all of the java versions are correct. Java is essentially dumbed down C++. Why folks want to go to the extra steps coding baffles me since PL/SQL is much more powerful inside the database. But, there is a lot to be said for portability. Especially, when the final envirnoment is yet to be determined. --- Rachel Carmichael [EMAIL PROTECTED] wrote: This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Function-Based Index not working
Hello
I think that the amount of records you read is also taken
into account.
If you run a query that selects ALL the records in the
tables
it is ALWAYS more efficient to do full table scan then to
access
by index.
Yechiel AdarMehish
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:23
PM
Subject: Re: Function-Based Index not
working
Hi All,
Thanks a lot to you all. At lastI got the
function-based index working properly.
This is whatI noticed :-
Have to alter session/system for :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
And
+ can't use IS NULL IS NOT NULL
clause.
+ can't use Like operator.
Regards,
Marul.
- Original Message -
From:
Marul Mehta
To: Multiple
recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 6:33
PM
Subject: Re: Function-Based Index not
working
Hi Naveen,
Thanks a lot for the efforts you are putting in
for me for such a simple problem, but unfortunately, for me all the tips and
tricks are not solving the problem.
Now these are my current
statistics:-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;+ alter session set
DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in
employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though
its not necessary)
+ analyze table employees compute
statistics;
+ alter index upper_ix
rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57
Card=4001 Bytes=2
0005)
1 0
SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
2 1 TABLE ACCESS (FULL) OF
'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert
more records in the table.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple
recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58
PM
Subject: RE: Function-Based Index not
working
See the table's size is very small. Till it atleast 2 times the
value of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use
index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNTto
one.
Insert lots
of values in the table. You can make a procedure to insert random
characters into the table, and then put it in a big loop. Analyze table
and thn run the same query.
It should work
naveen
-Original
Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the
execution plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees
values('A');
+ Insert into employees
values('B');
+ analyze table employees compute statistics;
+
select last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name); 2 3Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=3 Card=2
Bytes=2
)
1 0 SORT (ORDER BY)
(Cost=3 Card=2 Bytes=2) 2
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1
Card=2 Bytes=
2)
Even after using the hint no change in the plan :-
+select /* INDEX employees(upper_ix) */ last_name FROM
employees WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the
index which is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple
RE: Replication question
I once noticed this code which is executed (for each column?) when you create a table. Would it be related to replication? BEGIN 2. /* NOP UNLESS A TABLE OBJECT */ 3. IF dictionary_obj_type = 'TABLE' THEN 4. sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,dictionary_obj_name,'LOCK'); 5. END IF; 6. END; Paul Baumgartel [EMAIL PROTECTED]@fatcity.com on 09/05/2002 11:38:25 AM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Thanks, John. Do you know what mechanism causes the internal triggers to fire? Are the internal triggers created by catrep.sql, or by DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for each table? Does the RDBMS have to determine whether --- John Weatherman [EMAIL PROTECTED] wrote: Paul, The procedures are executed by a special internal trigger. These triggers are also NOT dropped by catrepr.sql (yes, I found out the hard way!). It is documented in metalink. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Wednesday, September 04, 2002 9:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PCTUSED - when is block added to freelist?
So my understanding from this is that a) A table has a PCTUSED of 40 which is changed to 75 b) A block is 60% free c) This block will be added to the freelist if an update or delete affects it (it was not added when the PCTUSED was changed). d) So my next question (which I may have to try and test myself): Would a global update on the table that didn't change anything move the blocks that fit the new PCTUSED criteria to the freelist? For example: update big_table set column1=column1; This would touch every block, at the end of the update the block would be below the new PCTUSED, but no data actually changes. Would this make the block available for inserts? Hmm, this is getting interesting! Jay Miller -Original Message- Sent: Wednesday, September 04, 2002 12:33 PM To: Multiple recipients of list ORACLE-L See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick Miller, Jay JayMiller@TDWater To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] house.com cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of
RE: java stored procedures
b) The thin driver can only use dedicated server connection which does miracles for load balancing. Where did you get that? Thin driver works with MTS. There are configuration issues working with MTS that only thin driver encounters. Richard Ji -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 question on excluding certain schema
Raj - Great! That one is beyond me, but Kirti is certainly the expert, so were I you, I'd try his suggestion. Good luck. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: Fail Over Options
Veritas Cluster Server does this. I understand 9i RAC does this as well, but I do not have any hands on experience with it. Pete --- Yechiel Adar [EMAIL PROTECTED] wrote: I heard that there is a network switch or router that does dynamic translation of IP addresses. So you can always connect to one address and change the translation in the switch/router in case of fail over. This should be transparent to your thin jdbc client. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 11:18 PM Ricjard is correct. jdbc thin client does not allow transparent fail over. The thick client reads a tnsnames.ora file which is where the fail over actually occurs. There are multiple server names for the same service name. If the first server in the search is down, it falls through to the next server. jdbc is a straight connection to a single database. No read of tnsnames.ora. Pete --- Ji, Richard [EMAIL PROTECTED] wrote: Dennis, One thing you can't do with Thin driver is Transparent Application Failover which is what Peter wants to do I believe. Richard -Original Message- Sent: Wednesday, August 28, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Peter - A couple of issues come to mind: 1) So you are saying that connection failover is your prime interest at this stage? I was thinking database failover when I saw your question. 2) So what is wrong with thin client? At my site we are tending to go with thin so we don't have to install SQL*Net client on every app server. That was what attracted us to Java in the first place. Everything I've seen says that for some applications thin has better performance and for others OCI is better. Personally I like having options. 3) How committed to Oracle is your site? If you have a heavy Oracle commitment, you may be able to stick it to IBM's attitude. Traditionally IBM has had an attitude of use all of our stuff. This goes back to when they were the Microsoft of the computer industry. Nobody worries about IBM monopolizing the industry today. Obviously they are learning to shed that parochial attitude in order to succeed today. I'll bet if you complained up the IBM management chain about this person's attitude you might see a change for the better. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 27, 2002 5:49 PM To: Multiple recipients of list ORACLE-L Dennis, This is a auite of products. The prime offender is WebSphere, an IBM product. The IBM consultants have been particularly difficult to deal with. According to them, nothing Oracle ever produced works. The project specifications call for transparent fail over which is possible using Oracle's thick client. Unfortunately, WebSphere does not seem to be able to use anything but jdbc thin client. If we can't get this to work, we will likely go to something like Veritas Cluster Server. Replication provided a solution that had already paid for. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Peter - Have you looked at Oracle Standby Server? Which App server are you using? Most of them claim to help you with this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 27, 2002 3:38 PM To: Multiple recipients of list ORACLE-L We are looking at fail over options for databases in a 7x24 e-commerce environment. Currently, we are using advanced replication. It works but it is labor intensive. We have also discovered that the application can only use jdbc rather than the Oracle thick client. In essence, this replication is now manual fail over. Would someone who has made java and the thick client work provide some pointers to getting it to work? Thanks, = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
RE: Function-Based Index not working
Not
necessarily... Cary's IOUG-A presentation covers this very well. One scenario is
where the high water mark is set artificially high, and there are far more
blocks allocated than actually contain data. In this case, a FTS will be reading
far too many empty blocks.
-Original Message-From: Yechiel Adar
[mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002
10:19 AMTo: Multiple recipients of list ORACLE-LSubject:
Re: Function-Based Index not working
Hello
I think that the amount of records you read is also
taken into account.
If you run a query that selects ALL the records in the
tables
it is ALWAYS more efficient to do full table scan then
to access
by index.
Yechiel AdarMehish
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:23
PM
Subject: Re: Function-Based Index not
working
Hi All,
Thanks a lot to you all. At lastI got the
function-based index working properly.
This is whatI noticed :-
Have to alter session/system for
:-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
And
+ can't use IS NULL IS NOT NULL
clause.
+ can't use Like operator.
Regards,
Marul.
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002 6:33
PM
Subject: Re: Function-Based Index not
working
Hi Naveen,
Thanks a lot for the efforts you are putting
in for me for such a simple problem, but unfortunately, for me all the
tips and tricks are not solving the problem.
Now these are my current
statistics:-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;+ alter session set
DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in
employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though
its not necessary)
+ analyze table employees compute
statistics;
+ alter index upper_ix
rebuild;
Making autotrace on
+ set autotrace traceonly
explain
Fired the
query:
SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57
Card=4001
Bytes=2
0005)
1
0 SORT (ORDER BY) (Cost=57 Card=4001
Bytes=20005) 2 1
TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert
more records in the table.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002
4:58 PM
Subject: RE: Function-Based Index
not working
See the table's size is very small. Till it atleast 2 times the
value of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use
index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNTto
one.
Insert
lots of values in the table. You can make a procedure to insert random
characters into the table, and then put it in a big loop. Analyze table
and thn run the same query.
It should work
naveen
-Original
Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the
execution plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees
values('A');
+ Insert into employees
values('B');
+ analyze table employees compute statistics;
+
select last_name FROM
employees WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name); 2 3Elapsed:
00:00:00.00
RE: Replication question
Paul, Dispite being internal the triggers are triggers just like any others. INSERT, UPDATE, DELETE all fire the package (all the transactions you want to move). I have NEVER been clear on the relationship between these triggers and user defined ones, sence you suposedly can't control the order multiple PRE-INSERT triggers, for instance, fire. The internals are created by DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT, which is why the catrepr.sql doesn't know about them. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Thursday, September 05, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Thanks, John. Do you know what mechanism causes the internal triggers to fire? Are the internal triggers created by catrep.sql, or by DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for each table? Does the RDBMS have to determine whether --- John Weatherman [EMAIL PROTECTED] wrote: Paul, The procedures are executed by a special internal trigger. These triggers are also NOT dropped by catrepr.sql (yes, I found out the hard way!). It is documented in metalink. HtH, John P Weatherman Database Administrator Replacements Ltd. -Original Message- Sent: Wednesday, September 04, 2002 9:04 PM To: Multiple recipients of list ORACLE-L When DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT is executed to build the necessary underpinnings for multimaster replication of an object, it creates a package called object_name$RP. This package contains code to be run when rows are inserted, updated, or deleted. There are, however, no trigges in the owning schema, nor in that of the replication administrator. What, then, is the mechanism by which the procedures in this package are called? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: General Replication question
Hello Ed We are using replication for one application, Dealing room. This is synchronous replication between 2 computers sitting in the same room connected by dedicated cable. The target is to have up to date second database in case of machine failure. I got lost quickly in the manual and finally did the right thing. Called Oracle support and paid for in site consulting. The guy came over and after 6-7 hours had a script that generate replication for a schema. I put it in production about 1 year ago and no problems since. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 26, 2002 6:58 PM I'm curious, based on a discussion I had with a DBA here at work, how many people use the replication features of Oracle. I often see replication listed as one of the selling points of Oracle, but it's also very hard to get a class on replication because they are always closing classes for poor registration. How common is replication (basic or advanced)? It makes more sense to use simple snapshots than DB links for what we are doing, but given that our support from Oracle has been TERRIBLE with snapshot problems, I now wonder if anyone uses them. We are switching to db links, but that can pose potential performance issues with, for example, joins across the db link. Best, Ed -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 long did job run?
SQL desc dba_jobs; NameNull?Type --- JOB NOT NULL NUMBER LOG_USERNOT NULL VARCHAR2(30) PRIV_USER NOT NULL VARCHAR2(30) SCHEMA_USER NOT NULL VARCHAR2(30) LAST_DATEDATE LAST_SEC VARCHAR2(8) THIS_DATEDATE THIS_SEC VARCHAR2(8) NEXT_DATE NOT NULL DATE NEXT_SEC VARCHAR2(8) TOTAL_TIME NUMBER BROKEN VARCHAR2(1) INTERVALNOT NULL VARCHAR2(200) FAILURES NUMBER WHAT VARCHAR2(4000) NLS_ENV VARCHAR2(4000) MISC_ENV RAW(32) INSTANCE NUMBER SQL You need to look more carefully. There is a column called LAST_DATE. Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: Vladimir Barac [SMTP:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 3:43 AM To: Multiple recipients of list ORACLE-L Subject: How long did job run? Hello to everyone Is it possible to find LAST execution time of a job? DBA_JOBS has column TOTAL_TIME, but I need something like last time. Thanks -- 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: query for detect redundant index
Fink, Thanks a lot - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 03, 2002 4:53 PM Subject: RE: query for detect redundant index I presume 'redundant' means that a column in the leading position of an index is also in the leading position of another index. Here is a quick and dirty SQL script to generate this information. Unfortunately, it repeats the information, but it does give you the information. SQLbreak on table_name on column_nameSQL l 1 select ic1.table_name, 2 ic1.column_name, 3 ic1.index_name, 4 ic1.column_position, 5 ic2.index_name, 6 ic2.column_position, 7 decode(ic1.column_position, 8 ic2.column_position, 'Redundant') redundancy 9 from user_ind_columns ic1,10 user_ind_columns ic211 where ic1.table_name = ic2.table_name12 and ic1.column_name = ic2.column_name13* and ic1.index_name != ic2.index_nameSQL / TABLE_NAME COLUMN_NAME INDEX_NAME COLUMN_POSITION INDEX_NAME COLUMN_POSITION REDUNDANC-- --- --- --- --- EMP EMPNO PK_EMP 1 IX_EMP 1 Redundant IX_EMP 1 PK_EMP 1 Redundant ENAME IX_EMPNAME 1 IX_EMP 2 IX_EMP 2 IX_EMPNAME 1 -Original Message-From: Adriano Freire [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 03, 2002 11:15 AMTo: Multiple recipients of list ORACLE-LSubject: query for detect redundant index HI all, Do you've any pl/sql for detect redundant index? thanks, Adriano Freire
RE: java stored procedures
Rachel I agree with Mladen. Java as a programming language is good. It has a good long-range forecast compared with alternatives. We are getting ready to dump 20 years our company has invested in COBOL on one system. Making sure you write your code in a language that will be around 20 years from now is more important than most of us know. As to writing database stored procedures in Java, the situation is a little more iffy. Compared with PL/SQL, most reports I hear is that usually PL/SQL is faster. No surprise since PL/SQL is doing exactly what it was developed to do. If speed is critical, I would say go with PL/SQL. If speed isn't so critical, then a Java servlet on an application server may be fast enough. Which means the value of Java stored procedures may not be as great as you might assume. This is what I have figured out so far, look forward to opinions of others. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Actually, Java is not so bad idea. Aurora does pretty much the same thing as PL/SQL and it is very hard to get to OS or network because security doesn't let you. On the plus side, bad programs in Java are more portable then the ones in PL/SQL. IDE software for Java is better suited for duhvelopers then the one for PL/SQL. You will have to increase Java pool and you will have to increase system tablespace because your developers will start abusing loadjava almost instantly and start loading .jar files (like for XML, for instance). Also, your CPU and RAM usage will go up fairly quickly so you might consider convincing your boss to finally do that HW upgrade. Expect attempts to use thin JDBC driver. The nice features of the thin driver are a) It's written entirely in Java, which makes it much slower then an optimized C code like OCI driver and, therefore, preferred by duhvelopers in the name of generality and universality. Stupidity is another very universal thing. b) The thin driver can only use dedicated server connection which does miracles for load balancing. There is connection pooling, which also requires dedicated server to work. Because it cannot use MTS and TNS in general, thin driver must have a hard-wired connection string written in the code or parameter file, which apparently doesn't diminish generality and universality. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Subject: java stored procedures This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you
Re: Question about database and service name
You can easily create more than one service name for a database in TNSNAMES.ORA: just create different names with the same definition. I'm aware that a service can consist of multiple databases, but I've never implemented it and am not sure how it's done. --- Ramon E. Estevez [EMAIL PROTECTED] wrote: Hi list, Reading the OU manuals Oracle 9i DBA Fundamentals II , in chapter 2 page 12, found 1-) An oracle database is represented to clients as a service. 2-) A database can have one or more services associated with it. 3-) A database can be presented as multiple services and a service can be implemented as multiple database instances. For the No. 2 : I interpret that I can have 2 or 3 or 4 services for just ONE DB. Is that correct ?. How can I do that ? For the No. 3: I interpret that I can refer to the same DB with differents names. As far as I understand, the DB instance name is unique, it can't be changed. So how can I create several services names for one DB. Or is just a trick in the TNSNAMES.ORA and LISTENER.ORA files. Please can anyone give some light in that, I am totally confused !!! Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 __ 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: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Optimal DBA Library and recommendations to similar published
Dan, Nice Web Site :) I liked the 'DUAL page' very much. Thanks for all the efforts in preparing it. I have sent the link to our DBAs Developers. Cheers! - Kirti -Original Message- Sent: Thursday, September 05, 2002 11:24 AM To: Multiple recipients of list ORACLE-L published One of the challenges in writing that paper/presentation was the lack of material available. Much of what you see was gleaned from oracle documentation, white papers, presentations, metalink, orafaq.com, orageeks.com, colleagues, questions at presentations, material from classes, and my own 'I wonder if this is possible?'. There is also a very healthy dose of things I have learned over the years, but have forgotten the source. I have used 'Oracle The Complete Reference' as my main training text and am happy with it, of course it covers more than just SQL. I spent several hours looking through books like 'SQL For Smarties', but was not very impressed (I don't recall exactly why...its been a couple of years). There are several books that were published since I first wrote the paper. 'Mastering Oracle SQL' and 'Oracle SQL:The Essential Reference' have pretty good reviews at amazon.com. Dan Fink -Original Message- Sent: Thursday, September 05, 2002 7:48 AM To: Multiple recipients of list ORACLE-L material. Hello All I was reading Dan Finks site, Particullary SQLScriptingSorceryPaper http://www.orcldba.com/library.html And found the material very interesting. Id like to get a recommendation for an advanced sql book which covers material similar to Dan site which feature creative problem solving tips I was going to mail Dan directly to find what text book he would recommend, but figured others would benefit form the recommendations. Most of the books I have barely go beyond joining 2 tables. Id like to get some recommendations for the *best* sql reference/techniques. I've learned allot for Dans site but really like to have a physical book Thanks Bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky 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).
RE: Export question on excluding certain schema
NO! NO! NO! Please... I am not an expert. I just remembered this trick that I learned from the real experts (like Anjo, X$KGopal ...) I simply passed on what I had learned from others. Cheers! - Kirti -Original Message- Sent: Thursday, September 05, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Raj - Great! That one is beyond me, but Kirti is certainly the expert, so were I you, I'd try his suggestion. Good luck. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Dennis, I received a reply from Kirti that by tweaking catexp81.sql file we could just achieve that. This is possible because all we have to do is tweak a view that dictates what will be exported. It looks like that will solve my problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 10:58 AM To: Multiple recipients of list ORACLE-L Raj - Assuming you are using an export parameter file (file.prms). Instead of the FULL=Y parameter, use OWNER=(USER1,USER2,USER3, . . . Unfortunately export doesn't offer the capability to say full except for mdsys, ctxsys, if that is your question. You can also do a full export and then selectively import the desired schemas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: 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: ALTER TABLE MOVE command causes table to grow
Title: RE: ALTER TABLE MOVE command causes table to grow you didn't mention the PCT_INCREASE of this segment. -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject: ALTER TABLE MOVE command causes table to grow Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ALTER TABLE MOVE command causes table to grow
Jay: I would also wonder that the PCTINCREASE was on the table and the indexes. 10% PCTFREE is fine, but does lead to a significant number of empty blocks. What is your PCTUSED? If small, you will have lots of free space within blocks. Just a thought. Don't let your disk person know this happened as they may try to sell you more hardware. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject:ALTER TABLE MOVE command causes table to grow Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: java stored procedures
oh we are definitely Oracle/Sun, it's just that the servers are just now being installed --- Peter Barnett [EMAIL PROTECTED] wrote: We have several developers using java stored procedures. No real 'gotchas' other than making sure all of the java versions are correct. Java is essentially dumbed down C++. Why folks want to go to the extra steps coding baffles me since PL/SQL is much more powerful inside the database. But, there is a lot to be said for portability. Especially, when the final envirnoment is yet to be determined. --- Rachel Carmichael [EMAIL PROTECTED] wrote: This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ 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: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: any tips on migrating from 7.3.4 to 8.1.7
Sebastian: I would definitely make sure that sufficient backups of both Oracle Homes and databases are taken before anything occurs. I would also start with the 8.1.5 database, converting it to 8.1.7. This process is straight forward and does not require a significant amount of work. As far as the 7.3.4 database, the migration assistant can be used to solve most of the migration tasks that must be performed. Additional tasks may be necessary depending on the size of the database, etc. If you are trying to convert into a single database, be careful not to wipe out any data from either environment. If this is the case, then convert to a single database and migrate to 9.0.2. I would also convert to a single Oracle Listener for both databases, using 8.1.7 and then finally 9.2. Hope this helps. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject:any tips on migrating from 7.3.4 to 8.1.7 Hello All, I am currently involved in a migration project which involves combining two disparate databases into a single Oracle Engine. One of the databases currently lives on Oracle 7.3.4 and the other is 8.1.5 (both desupported by Oracle). My task is to upgrade to a single instance of 8.1.7 (and eventually 9.2.x). Are there any road bumps that I should be aware of before undertaking this endeavor? Any tricks/tips are appreciated. Thank you in advance Sebastian DiFelice DBA/Database Analyst Thomson Intelligence Data (617)856-1587 www.intelligencedata.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DiFelice, Sebastian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Anyone seen any independent performance .....
studies on what happens to performance as indexes keep getting added to tables while inserting data ?? Thank You in advance for your time. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 loads - what is the name of the counterpart that exports
Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anyone seen any independent performance .....
Title: RE: Anyone seen any independent performance . I don't remember where but the results of the study were like following ... if the cost of inserting one row to a table is 1 unit and if you have 5 indexes on the table than total cost of inserting 1 row to the table is 1 (cost of inserting a row) + 3*5 (5 indexes) - 16 So total cost is about 16 units. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 2:08 PM To: Multiple recipients of list ORACLE-L Subject: Anyone seen any independent performance . studies on what happens to performance as indexes keep getting added to tables while inserting data ?? *2 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. *2
RE: Function-Based Index not working
Even when the high-water mark thing isnt a problem, its sometimes more efficient to read every row in a table through an index than via a full-table scan. If youre curious, try this. Create a table with two columns, key and value, and insert one row with key=1, value=x. Create an index on key. Then alter session set events 10046 trace name context forever, level 8; select * from onerow; /* just to make sure its cached */ select * from onerow; select * from onerow where key=1; /* just to make sure its cached */ select * from onerow where key=1; exit; Now look at your trace data. Youll find that the full-table scan of this table is both cheaper and faster through the index. The age-old advice from many SQL tuning experts is badly wrong when they tell you never to index small tables. For applications that execute a lot of small-table queries, the performance impact really adds up. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Fink, Dan Sent: Thursday, September 05, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: Function-Based Index not working Not necessarily... Cary's IOUG-A presentation covers this very well. One scenario is where the high water mark is set artificially high, and there are far more blocks allocated than actually contain data. In this case, a FTS will be reading far too many empty blocks. -Original Message- From: Yechiel Adar [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 10:19 AM To: Multiple recipients of list ORACLE-L Subject: Re: Function-Based Index not working Hello I think that the amount of records you read is also taken into account. If you run a query that selects ALL the records in the tables it is ALWAYS more efficient to do full table scan then to access by index. Yechiel Adar Mehish - Original Message - From: Marul Mehta To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 4:23 PM Subject: Re: Function-Based Index not working Hi All, Thanks a lot to you all. At lastI got the function-based index working properly. This is whatI noticed :- Have to alter session/system for :- + alter session set QUERY_REWRITE_ENABLED=TRUE; + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; + alter session set optimizer_mode=FIRST_ROWS; And + can't use IS NULL IS NOT NULL clause. + can't use Like operator. Regards, Marul. - Original Message - From: Marul Mehta To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 6:33 PM Subject: Re: Function-Based Index not working Hi Naveen, Thanks a lot for the efforts you are putting in for me for such a simple problem, but unfortunately, for me all the tips and tricks are not solving the problem. Now these are my current statistics:- + alter session set QUERY_REWRITE_ENABLED=TRUE; + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; + alter session set optimizer_mode=FIRST_ROWS; + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1; This procedure writes 180,000 records in employeees table + execute bulk_insert Analyzing table and rebuilding index (though its not necessary) + analyze table employees compute statistics; + alter index upper_ix rebuild; Making autotrace on + set autotrace traceonly explain Fired the query: SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name); Elapsed: 00:00:00.00 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001 Bytes=2 0005) 1 0 SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005) 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 By tes=20005) Any clues what is happening? Should I insert more records in the table. TIA, Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 4:58 PM Subject: RE: Function-Based Index not working See the table's size is very small. Till it atleast 2 times the value of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index. Set the value of DB_FILE_MULTIBLOCK_READ_COUNTto one. Insert lots of values in the table. You can make a procedure to insert random characters into the table, and then put
Re: Question about database and service name
Mr. Estevez: The service names are defined in the init.ora using the service_names parameter. You can have several names separated by commas and white space to represent service names. The Listener can listen for a specific service name if you list it in the listener.ora file. Oracle8, 8i and 9i databases are self registering with the listener so the listener will be aware of what name(s) the database will answer to. To uniquely identify a database instance you can use the four parameters of db_name, db_domain, instance_name and service_names in the init.ora file. By default , the service name is the db_name and the db_domain combined. The db_name has traditionally been equal to the SID. The instance name can be longer and more meaningful, especially if you are running in parallel server mode. I hope this clears this up somewhat. Check the Oracle9i Database Reference (A96536-01) or the Oracle8i equivalent. RWB Ramon E. Estevez [EMAIL PROTECTED]@fatcity.com on 09/05/2002 11:38:26 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi list, Reading the OU manuals Oracle 9i DBA Fundamentals II , in chapter 2 page 12, found 1-) An oracle database is represented to clients as a service. 2-) A database can have one or more services associated with it. 3-) A database can be presented as multiple services and a service can be implemented as multiple database instances. For the No. 2 : I interpret that I can have 2 or 3 or 4 services for just ONE DB. Is that correct ?. How can I do that ? For the No. 3: I interpret that I can refer to the same DB with differents names. As far as I understand, the DB instance name is unique, it can't be changed. So how can I create several services names for one DB. Or is just a trick in the TNSNAMES.ORA and LISTENER.ORA files. Please can anyone give some light in that, I am totally confused !!! Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 -- 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).
OCP - Oracle 9i upgrade study Materials
Greetings, From time to time I saw many emails regarding the website, books and other study materials for OCP 9i upgrade. Unfortunately I did not save those emails. If any of you have any information could you please forward it. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sql loader loads - what is the name of the counterpart that exports
No such beast. But you can roll your own... :) Tom Kyte has a page that directly addresses this: http://govt.oracle.com/~tkyte/flat/index.html -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:05 PM exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Philip Douglass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ALTER TABLE MOVE command causes table to grow
pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OBJECT CREATION PROCEDURE
Hi, Can any one sned me standared procedure to create new objects? Thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- 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).
RE: Question about database and service name
Ramon: Let me try these one at a time. 1. True. To a client desktop or device, a database environment is presented as a service (flooky name). The service is a TNS Service via Net8/Oracle*Net. 2. Using multiple service names within the tnsnames.ora file and multiplexing within the listener.ora file, a database can have multiple service names point to it. This is common where application-specific code names are used as TNS Service Names. This is also useful for failover whereas services are on different machines. 3. This one sounds like RAC or OPS where the database can be implemented as multiple services and a service is implemented as multiple instances. In either case, a database instance can only represent one database at a time. That is why service names must be unique within a specific server. Hope this helps. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: Question about database and service name Hi list, Reading the OU manuals Oracle 9i DBA Fundamentals II , in chapter 2 page 12, found 1-) An oracle database is represented to clients as a service. 2-) A database can have one or more services associated with it. 3-) A database can be presented as multiple services and a service can be implemented as multiple database instances. For the No. 2 : I interpret that I can have 2 or 3 or 4 services for just ONE DB. Is that correct ?. How can I do that ? For the No. 3: I interpret that I can refer to the same DB with differents names. As far as I understand, the DB instance name is unique, it can't be changed. So how can I create several services names for one DB. Or is just a trick in the TNSNAMES.ORA and LISTENER.ORA files. Please can anyone give some light in that, I am totally confused !!! Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
Re: Sql loader loads - what is the name of the counterpart that exports
It's called SQL Plus. Set the heading off, pagesize = 0, linesize = 200, set record delimiter = ',' or '|' and set feedback off; and termout on. This should produce an ASCII file once you supply your own query. RWB ltiu [EMAIL PROTECTED]@fatcity.com on 09/05/2002 01:05:07 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ALTER TABLE MOVE command causes table to grow
Hi Stephen, PCT Increase 0, the indexes are in a different tablespace. PCT Used was 40, I just recently increased it to 75. Do you know if the MOVE command moves blocks as they currently exist or if it behaves like an export/import? If the latter (which was my assumption) I'd expect that a low PCTUSED would actually shrink the table since each block in the new tablespace would fill up entirely except for the PCTFREE. Jay -Original Message- Sent: Thursday, September 05, 2002 1:55 PM To: Multiple recipients of list ORACLE-L Jay: I would also wonder that the PCTINCREASE was on the table and the indexes. 10% PCTFREE is fine, but does lead to a significant number of empty blocks. What is your PCTUSED? If small, you will have lots of free space within blocks. Just a thought. Don't let your disk person know this happened as they may try to sell you more hardware. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject:ALTER TABLE MOVE command causes table to grow Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Anyone seen any independent performance .....
Title: RE: Anyone seen any independent performance . (1) one for the table insert. + (3) 1 for the header block, 1 for the branch block and 1 for the leaf block * number of indexes I think Dave Ensor mentioned something like that in a presentation. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, September 05, 2002 1:21 PM Subject: RE: Anyone seen any independent performance . I don't remember where but the results of the study were like following ... if the cost of inserting one row to a table is 1 unit and if you have 5 indexes on the table than total cost of inserting 1 row to the table is 1 (cost of inserting a row) + 3*5 (5 indexes) - 16 So total cost is about 16 units. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 2:08 PM To: Multiple recipients of list ORACLE-L Subject: Anyone seen any independent performance . studies on what happens to performance as indexes keep getting added to tables while inserting data ??
Re: Sql loader loads - what is the name of the counterpart that exports
So Oracle thinks that people will only move into Oracle and not out of Oracle. Which makes me think. Is there a utility available in other DB's that can extract Oracle data out in plain text? To put this question in another way, how do you transfer data between different database vendors? Are there utilities out there that allows you to export and import data to and from other types of databases - Oracle to DB2 to MS SQL to Oracle ? Thanks. ltiu Philip Douglass wrote: No such beast. But you can roll your own... :) Tom Kyte has a page that directly addresses this: http://govt.oracle.com/~tkyte/flat/index.html -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:05 PM exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Function-Based Index not working
Hi,
Maybe: NULL values are not indexed, so only way to verify the query
condition is to do a full table scan and filter, however for the same reason
the use of the index would be more logically as that's were all the not null
colums are ??? Beats me
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, September 03, 2002 11:43 PM
There are quite a few restrictions on function-based indexes.
The Oracle SQL guide lists them all. Have you checked to
ensure that you're following all the rules?
Jared
On Saturday 31 August 2002 07:53, Marul Mehta wrote:
Even after giving the hint its not working.
I guess you can't have IS clause and Like with function-based index.
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28 PM
Subject: RE: Function-Based Index not working
I think everythying is fine. Did you try index hint? try that and see.
if that also doesn't work, then either we are missing something or the
Optimizer thinks so
Naveen
-Original Message-
From: Marul Mehta [mailto:[EMAIL PROTECTED]]
Sent: Saturday, August 31, 2002 6:33 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Function-Based Index not working
Hi Naveen,
Thanks a lot for the efforts you are putting in for me for such a
simple problem, but unfortunately, for me all the tips and tricks are
not
solving the problem. Now these are my current statistics :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
+ alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though its not necessary)
+ analyze table employees compute statistics;
+ alter index upper_ix rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
ORDER BY UPPER(last_name); Elapsed: 00:00:00.00
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001
Bytes=2
0005)
10 SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert more records in the
table.
TIA,
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58 PM
Subject: RE: Function-Based Index not working
See the table's size is very small. Till it atleast 2 times the
value
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
Insert lots of values in the table. You can make a procedure to
insert random characters into the table, and then put it in a big loop.
Analyze table and thn run the same query.
It should work
naveen
-Original Message-
From: Marul Mehta [mailto:[EMAIL PROTECTED]]
Sent: Saturday, August 31, 2002 4:03 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the execution plan shows full
table scan :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
+ Insert into employees values('A');
+ Insert into employees values('B');
+ analyze table employees compute statistics;
+
select last_name
FROM employees WHERE UPPER(last_name) IS NOT NULL
ORDER BY UPPER(last_name); 23
Elapsed: 00:00:00.00
Execution Plan
--
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2
Bytes=2 )
10 SORT (ORDER BY) (Cost=3 Card=2 Bytes=2)
21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
Bytes= 2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM
employees
WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the
index which is created.
TIA,
Marul.
-
Correlated subquery performance in 8i 9i
Learnt the following from Gaja's seminar last week. So just wanted to pass this on: Inline views works better than correlated subqueries in 8i. But things have changed in 9i. Gaja proved to us by showing a tkprof output. This is because Oracle has changed their logic while processing a correlated subquery. HTH! Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 loads - what is the name of the counterpart that exports
OK. Very good. Wow!! Yes, this is what I am looking for. Thank you very much. ltiu [EMAIL PROTECTED] wrote: It's called SQL Plus. Set the heading off, pagesize = 0, linesize = 200, set record delimiter = ',' or '|' and set feedback off; and termout on. This should produce an ASCII file once you supply your own query. RWB ltiu [EMAIL PROTECTED]@fatcity.com on 09/05/2002 01:05:07 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ANSI Isolation Levels
Intro: There are 4 defined ANSI isolation levels: 1) read uncommitted; 2) read committed; 3) repeatable read; 4) serializable. By default Oracle implements the read committed (2) isolation level. Oracle can implement the serializable isolation level but not the repeatable read isolation level. Questions: I'm looking for a summary document of how the various database engines implement ANSI SQL transaction management. For performance reasons, is the read committed isolation level the most commonly implemented default by the various database vendors? (From what I gather it is also the default for Sybase, SQLServer and PostgreSQL.) Is the read committed isolation level the most practical? Has anyone ever administered a database or application with a different isolation level and why? Is there any summary document of transaction features for all the database vendors? Theoretically and Academically yours, Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ALTER TABLE MOVE command causes table to grow
Title: RE: ALTER TABLE MOVE command causes table to grow just to be certain we are on the same page, you mention uniform sizing which is on the tablespace level, so I want to make sure the PCT_INCREASE you provided was pulled from dba_segments. if so then i'd say a bit more info would need to have some light shed on it persay was there much DML put against this table last week. a PCT_FREE of 10% wouldn't be such a good idea for a table with varying length column data. -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L Subject: RE: ALTER TABLE MOVE command causes table to grow pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: java stored procedures
Last time I checked, it was using the old SQL*Net 1.1 syntax, namely hostname:sid:port and the server was always a dedicated one, especially if the connection pooling was turned on. -Original Message- From: Ji, Richard [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 1:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: java stored procedures b) The thin driver can only use dedicated server connection which does miracles for load balancing. Where did you get that? Thin driver works with MTS. There are configuration issues working with MTS that only thin driver encounters. Richard Ji -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 loads - what is the name of the counterpart that e
Click on Dump Tables To Flat File at http://www.cybcon.com/~jkstill/util/ - Kirti -Original Message- Sent: Thursday, September 05, 2002 2:12 PM To: Multiple recipients of list ORACLE-L exports OK. Very good. Wow!! Yes, this is what I am looking for. Thank you very much. ltiu [EMAIL PROTECTED] wrote: It's called SQL Plus. Set the heading off, pagesize = 0, linesize = 200, set record delimiter = ',' or '|' and set feedback off; and termout on. This should produce an ASCII file once you supply your own query. RWB -- 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).
connection output on clone instance
Title: connection output on clone instance can anyone guide me in changing the connect output: Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the Partitioning option JServer Release 8.1.7.2.0 - Production Where Production will be replaced with TEST
BMC Obacktrack
Hi all, I'm testing BMC Backtrack v 3.30 on Dynix 4.5.2 and experiencing some strange behavior of the tool. I have deleted a datafile and use the tool to restore it from the backup. When I let the tool to do a restore, everything runs great and fast. When however I have the tool generate a script and run that script manually, it attempts to restore ALL the datafiles (even though it was generated to only restore one). Has anyone experienced this before and is there something I can do about it? thanks Gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Export question on excluding certain schema
Title: RE: Export question on excluding certain schema
Also, by default, in Oracle 8.1.6 MDSYS and CTXSYS are already excluded from the export, so no editing of catexp.sql is needed to do what the original poster wanted to do.
# grep MDSYS $ORACLE_HOME/rdbms/admin/catexp.sql
u$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
u1$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
s$.owner not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
AND u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS')
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
The answer is: Yes.
You will need to edit catexp.sql to rebuild internal view to exclude
interested owner#.
-Original Message-
Is it possible to exclude certain sys type schema when we do
export? We are
(will be) migrating from 8161 to 9201 and would like to
exclude MDSYS and
CTXSYS from (8161) export.
RE: Sql loader loads - what is the name of the counterpart that exports
Hate to say it but the M$ DTS utility works really nice for moving data between different platforms. You can move data directly from DB2 to Oracle if you want. It is not good for the very huge tables though. But if you need a quick transfer I can have a DTS setup in a minute or two. Dave -Original Message- Sent: Thursday, September 05, 2002 2:04 PM To: Multiple recipients of list ORACLE-L exports So Oracle thinks that people will only move into Oracle and not out of Oracle. Which makes me think. Is there a utility available in other DB's that can extract Oracle data out in plain text? To put this question in another way, how do you transfer data between different database vendors? Are there utilities out there that allows you to export and import data to and from other types of databases - Oracle to DB2 to MS SQL to Oracle ? Thanks. ltiu Philip Douglass wrote: No such beast. But you can roll your own... :) Tom Kyte has a page that directly addresses this: http://govt.oracle.com/~tkyte/flat/index.html -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:05 PM exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Correlated subquery performance in 8i 9i
I also discovered that at Gaja's seminar. Isn't that special? What will Oracle do for us next? Jared BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 12:11 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Correlated subquery performance in 8i 9i Learnt the following from Gaja's seminar last week. So just wanted to pass this on: Inline views works better than correlated subqueries in 8i. But things have changed in 9i. Gaja proved to us by showing a tkprof output. This is because Oracle has changed their logic while processing a correlated subquery. HTH! Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: java stored procedures
Why not use intermedia? Jared Rachel Carmichael [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 06:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:java stored procedures This really is my week for asking for help. We have a project lead/developer here who wants to use a java stored procedure, wrapped in a PL/SQL function, to implement a search function on the site. Besides the fact that this is the first I've heard of the request, and that I think he is reinventing the wheel in what he wants to do in this procedure (normalize text data that we already GET normalized elsewhere), and that we are supposed to go into QA testing by the end of the month and he STILL hasn't locked down the schema etc Personal prejudices aside, I've heard vague rumblings that Java in the database is not optimal. We'll be in 9iR2, although he's developing against an 8.1.7 database (don't ask, I'm not responsible for that database other than to provide him with schema ddl, there IS no real development server here). Before I categorically say no or yes, are there any gotchas I need to look out for? Thanks Rachel __ 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: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Sampling V$SESSTAT
MacGregor, Ian A. wrote: I want to start sampling this table, however collecting data on the 200+ statistics for each session would produce a prohibitively large result. I'm trying to pare the 225 statistics to something more reasonable, but I cannot decide which ones to discard and which to record. Does anyone have a listing of the most useful statistics that they would like to share? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] Out of the top of my head : db block gets consistent gets (both summed up to get the logical gets) CPU used by this session memory sorts disk sorts I guess that this and session events should give a reasonably fair idea of who are the big users. -- Regards, Stephane Faroult Oriole Software -- 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).
off topic: OCP exam registration
Hi ALL! I just spoke with Prometric and they told me if I give them my OTN number they will give me discount for OCP exam. I'm the member of OTN , but I don't have any number. Someone know where I can get this number? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 loads - what is the name of the counterpart that exports
Dave: Your moving data from relational to relational DB. What about from hierarchical to relational? Thanks, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:36 PM exports Hate to say it but the M$ DTS utility works really nice for moving data between different platforms. You can move data directly from DB2 to Oracle if you want. It is not good for the very huge tables though. But if you need a quick transfer I can have a DTS setup in a minute or two. Dave -Original Message- Sent: Thursday, September 05, 2002 2:04 PM To: Multiple recipients of list ORACLE-L exports So Oracle thinks that people will only move into Oracle and not out of Oracle. Which makes me think. Is there a utility available in other DB's that can extract Oracle data out in plain text? To put this question in another way, how do you transfer data between different database vendors? Are there utilities out there that allows you to export and import data to and from other types of databases - Oracle to DB2 to MS SQL to Oracle ? Thanks. ltiu Philip Douglass wrote: No such beast. But you can roll your own... :) Tom Kyte has a page that directly addresses this: http://govt.oracle.com/~tkyte/flat/index.html -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:05 PM exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: KENNETH 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).
PCTFREE PCTUSED
Hi Can some one suggest what would be normal PCTFREE and PCTUSED for following type of tables? TYPE A: High rate of insert/delete but less update TYPE B: High rate of update but less insert/delete TYPE C: Large objects used for read mostly less DML operations Type D: High rate of DML operations Thanks in advance -seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: java stored procedures
No, besides the hostname:sid:port syntax, you can also use the long connect string jdbc:oracle:thin:@(description=(address=(host= ... syntax. And it works with both MTS and dedicated server. Richard Ji -Original Message- Sent: Thursday, September 05, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Last time I checked, it was using the old SQL*Net 1.1 syntax, namely hostname:sid:port and the server was always a dedicated one, especially if the connection pooling was turned on. -Original Message- From: Ji, Richard [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 1:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: java stored procedures b) The thin driver can only use dedicated server connection which does miracles for load balancing. Where did you get that? Thin driver works with MTS. There are configuration issues working with MTS that only thin driver encounters. Richard Ji -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
