RE: ADMIN PLZ REPLY - FW: !!Please Read - Oracle-L moving!!
A BIG thanks to Jared & Bruce for all your efforts guys. And as bruce says "See you in a minute on the new list". Thank you, Venu! -Original Message- Bruce A. Bergman Sent: Thursday, January 22, 2004 9:44 PM To: Multiple recipients of list ORACLE-L Yes, this is legitimate. Jared and I have been talking recently about this. This list has just outgrown what Fat City can handle. While I'd like to think that I've always provided adequate-to-good service for the list, it's never been "great", and with the list growing, and traffic growing, my concern is that I just won't be able to continue to give the list good service. It makes me sick to think that, because I really have enjoyed giving back to the Oracle-L community, and because y'all have supported ME so well in the past, but I just don't want to see anything deteriorate simply because the volume exceeds what we can handle here. The list archives here will be available as long as Fat City continues to be in existence, so those of you who are searching for old topics are quite welcome to use the facilities here. It won't be going away. Jared has always been an awesome list owner, and I know he'll continue to make sure the list is successful. This move is just an indication of the relevancy and successful growth of the Oracle-L list, and I know it will continue. I wish you all the best in your new home, and I'll see you over there in a minute. :-) thanks, bruce bergman ListMaster, Fat City Hosting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ADMIN PLZ REPLY - FW: !!Please Read - Oracle-L moving!!
Is this a genuine mail...? I'm a part of the list. -Original Message- Sent: Thursday, January 22, 2004 1:56 PM To: [EMAIL PROTECTED] Dear Oracle-L subscriber, Due to changing circumstances, the Oracle-L mailing list has found a new home. Fatcity.com has graciously hosted Oracle-L for several years, and I thank Bruce Bergman for his hard work on our behalf, but the time has come to move to a new host. Oracle-L will be hosted by freelists.org, effective immediately. In the past when this list has moved, I used a list of subscribers to automatically subscribe people to the new address, thinking I was providing a service. To avoid dealing with irate users that forgot they had subscribed (where does their mail go?) and their attorneys (don't care to hear from them again ) this new list will be 100% opt in. What this means is that you will need to subscribe to the new address if you wish to remain on the Oracle-L mailing list. This email is being sent to you once individually, and will also appear in the regular Oracle-L traffic. After a period of time (2 weeks or so) [EMAIL PROTECTED] will be shut down. At this time, I don't know for how long the Oracle-L archives at fatcity.com will be available. Instruction for subscribing to the new list are at the end of the message. I look forward to seeing you all at [EMAIL PROTECTED] Jared Still -- to subscribe: send email to [EMAIL PROTECTED] with 'subscribe' in the Subject field OR Subscribe via the web site - http://www.freelists.org/login.html To send email to the list, use this address: [EMAIL PROTECTED] You can unsubscribe from [EMAIL PROTECTED] by: send email to [EMAIL PROTECTED] with 'unsubscribe' in the Subject field OR Unsubscribe via the web site - http://www.freelists.org/login.html Documentation - http://www.freelists.org/help/ Searchable archives - http://www.freelists.org/archives/oracle-l Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL tuning...
Can anyone tell me whats wrong with the explain plan below… this update is running for quite long time…. Even without a single full-table access… Thank you in advance! UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831) UPDATE OF CCM_DEBIT_TBL SEQUENCE OF STAFFWARE_CASEID_S FILTER TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831) INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28) TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9) INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: How to drop a datafile ?
Well, you an actually move all your objects from this datafile into a different datafile (read it as different TS) and then offline drop the datafile. This will ensure that users don't get the error 'xxx.dbf file is currently inacessible' message. Cheers! Venu -Original Message- Prem Khanna J Sent: Wednesday, October 29, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Thanx Sinardy & Rachel. so,the only way is as what Sinardy said. is that so ? Regards, Jp. 29-10-2003 19:44:25, Rachel Carmichael <[EMAIL PROTECTED]> wrote: >The doc is right. You cannot drop a single datafile from a tablespace. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_job issue.
Re-submit the job…that should work… It has a long theory…!! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:00 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job issue. Hello List, I am running into weird dbms_job issue. I have a dbms_job to collect perfstat every 1 hour , job was running fine for last 8-9 months without any issue. For last 3 days job is stopping every night around 2 AM. I am not seeing any trace file, any logs in alert file. Any idea what is cuasing this. Below is the output from dba_jobs. We are on 9202 AIX 5L. JOB LAST_DATE NEXT_DATE THIS_DATE B FAILURES TOTAL_TIME -- -- -- -- - -- -- 45 18-SEP-03 02:01:02 18-SEP-03 03:00:00 N 0 175 DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Online Document for Windows XP Home edition Severity 3
Title: Message If you have reached a point where you could press the Install/DeInstall button, it means that you don’t have any problem with your Java installation/settings. Try to troubleshoot in other areas. Also, update the Forum with your success story once you are done. ~Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gudmundur Bjarni Josepsson Sent: Friday, August 22, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Online Document for Windows XP Home edition Severity 3 Try copying the contents of the CD to a directory on your hard drive. Search for all instances of symcjit.dll in that directory and below and rename them to symcjit.dll.old. Then run setup from the directory on your hard drive. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sinardy Xing Sent: 21. ágúst 2003 15:01 To: Multiple recipients of list ORACLE-L Subject: Online Document for Windows XP Home edition Severity 3 Hi all, I have new notebook, I want to install Oracle Doc 8.1.7, but Oracle fail to run when I click Install/Deinstall button Some said XP home edition cannot install Oracle, is this because Sun demand Ms to remove the JVM? Can someone help me please.. Thanks Sinardy **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Change based recovery
Fermin, I don’t want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as it’s a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if it’s a cold DB backup and recover the DB using ‘BACKUP CONTROL FILE’ option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- From: Dobson, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 8:21 AM To: Multiple recipients of list ORACLE-L Subject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR> startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size 126925024 bytes Database Buffers 131072000 bytes Redo Buffers 262144 bytes Database mounted. SVRMGR> recover database until change 10349; Media recovery complete. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Change based recovery
Fermin, Yes, It can be done!! Just with your cold backup and your archive logs. Basics: 1) Get this straight - A cold backup does not require any kind of recovery. When you restore a cold backup your DB will be old but will NOT require any recovery. 2) If you want to bring a old database to current time, you have to apply the archive logs. In case of a cold backup you cannot (read till the end) do it as your control files do not recognize newer archive logs generated after the backup was taken. The Work around is to re-create the control file using the ‘create control file’ script (see below) and then recover the database using the command ‘RECOVER DATABASE USING BACKUP CONTROL FILE’, This command will ask you for the archive logs; on supplying the archive logs, the database will be recovered further. 3) In case of a hot backup the backup is in-consistent. So after restoring a backup it WILL ask for recovery which will happen from the archive logs. This is same as the above but there is no Downtime involved in hot backups. Creating control file: The script to create the control file can be generated using the following command: SQL> ALTER DATABASE BACKUP CONTROL FILE TO TRACE; This will create a .trc file in the UDUMP directory of the database. You will have to edit it before running to create the control file. Browse thru Metalink for more info on this. Hope this helps! Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 6:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery Well I am quite de-motivated actually!! but at least it is good to know I was (partially) wrong and that I will feel safer after reading your comments, thanks! I am really in doubt now, but I remember when we were testing we did recover all datafiles (the ones that are stated in the v$datafile table) from a cold backup except control files and redolog files; we were able to restore the whole database with the commands I wrote down in my first message. If I am still wrong, will you please be kind enough to tell me which are the exact commands needed to recover the whole database from a cold backup if I have no online backups and I lose everything except for the archived logs? can it really be done? Thank you so much! Fermin. -Mensaje original- De: Venu Gopal [mailto:[EMAIL PROTECTED] Enviado el: lunes, 11 de agosto de 2003 13:44 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin, I don’t want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as it’s a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if it’s a cold DB backup and recover the DB using ‘BACKUP CONTROL FILE’ option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much f
RE: Change based recovery
Hi, When you are restoring from a cold backup you don’t have to recover and your database will be old. If you want to bring it to current time then recreate the control file and recover it using ‘RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL’; this will ask you for archive logs and then you have to supply them. Create the control file from a trace file generated on the running database using the following command: ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’; Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dobson, Lisa Sent: Thursday, August 07, 2003 5:51 PM To: Multiple recipients of list ORACLE-L Subject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR> startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size 126925024 bytes Database Buffers 131072000 bytes Redo Buffers 262144 bytes Database mounted. SVRMGR> recover database until change 10349; Media recovery complete. I would have expected it to display the names of the logs, but it doesn't, and when I check the alert log it shows 'No Media Recovery required'. Where am I going wrong? I can't understand why it won't apply the archived logs. (Too hot today and brain not working properly!) TIA. Lisa Dobson Database Analyst Home Group Ltd This message is intended only for the use of the person(s) ("Intended Recipient") to whom it is addressed. It may contain information, which is privileged and confidential. Accordingly any dissemination, distribution, copying or other use of this message or any of its content by any person other than the Intended Recipient may constitute a breach of civil or criminal law and is strictly prohibited. If you are not the Intended Recipient, please contact the sender as soon as possible. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Stephen, That's the way it's meant to work. This is how Oracle guarantees that you can grow upto the value of SGA_MAX_SIZE. Look at the case below: DAY 1: you have your sga_max set to 100m but you are currently using only 50m of it. DAY 2: you want to use all 100m and you try to increase the sga size, but some other app has used rest of the physical memory and there is no sufficient memory available, at this stage oracle has to error out saying 'insufficient memory', but in reality it does not!! In reality: On DAY 1, Oracle tries to occupy all 100m in the memory so that it doesn't have any issues in future... this way you have 100m reserved for oracle, use it whenever you want to use it. Hope this helps you... ~Venu -Original Message- [EMAIL PROTECTED] Sent: Friday, August 01, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes Variable Size 41943040 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes sga_max_size=110M and keep everything else the same. show sga: Total System Global Area 114790680 bytes Fixed Size 733464 bytes Variable Size 79691776 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. Database opened. I have raised a lengthy call on Metalink and the consultants are convinced this is normal behaviour and what you would expect. Do people agree with the metalink consultants? Maybe my expectations were to high but I thought a dynamic sga would mean I could change the amount of memory acquired by the UNIX box. All opinions welcome. I am on tru64 platform - 9.2.0.3.0 Thanks, Stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Full table scan
List, I have the following scnario: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Any comments... Thanks in advance, Venu **Disclaimer** Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited.
RE: password
Title: Message BOB, its the same on my machine... 8.1.6 on NT -Venu -Original Message-From: Bob Metelsky [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 3:56 AMTo: Multiple recipients of list ORACLE-LSubject: RE: password created a user test identified by test on 2 separate systems in db's with different names The password value was the same Can someone verify if it is the same on their system Create user test identified by test; select password from dba_users where username = 'TEST'; PASSWORD -- 7A0F2B316C212D67 -Original Message- on my db LTRACK1 SQL> select password from dba_users where username = 'TEST'; PASSWORD--7A0F2B316C212D67 bob **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Stored procedure memory problem
Thanks a lot guys! Now I have some understanding about how oracle handles cursors. Mark: can you please point me to some document or site which could put some more light on what you said below. Few hundreds of thousands was NOT typo... 10 - 50 -Venu -Original Message- Sent: Wednesday, December 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L It's important to realize that Oracle does not materialize all the rows to be returned from a cursor in memory. If you do 'select * from one_million_row_table;', at no time are all one million rows going to be in the PGA. However, there are cases where some or all of the data DOES have to be materialized, such as in a sort. This is why the sort_area_size and sort_area_retained_size parameters are important. They govern how much memory a single process can consume. Sorts larger than that threshold will automatically write to disk. If you're on 9i, you may wish to investigate pga_aggregate_target instead. You said "few hundreds of thousands"?? Is that a typo?? (Perhaps you meant "hundreds or thousands"??) Something else to consider is a shared server (aka MTS) configuration for large numbers of users in an on-line type environment, where the users have a large amount of think time. Shared server is a bad idea for batch type users, but could be a memory saver for an environment where there are thousands of users connected, but with long think times, the concurrency is limited. Hope that helps get you pointed in the right direction. -Mark On Tue, 2002-12-17 at 22:53, Venu Gopal Andem wrote: > A small question to all gurus on the list... > > I have a stored procedure (SP) which runs at specific intervals. It > fetches a list of user names and few (2 columns) of their details into > a cursor and processes them one by one in a cursor FOR loop. > > Now my question is, Is there a chance of the SP running out of memory > when the number of users increases (may be a few hundreds of > thousands). If yes how to estimate the maximum number of user names my > cursor can handle? Is there any calculation for this? How to overcome > this problem? Is there anything else that has to be taken into account > when doing this? > > Thanks in advance! > Venu G. -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
Stored procedure memory problem
A small question to all gurus on the list... I have a stored procedure (SP) which runs at specific intervals. It fetches a list of user names and few (2 columns) of their details into a cursor and processes them one by one in a cursor FOR loop. Now my question is, Is there a chance of the SP running out of memory when the number of users increases (may be a few hundreds of thousands). If yes how to estimate the maximum number of user names my cursor can handle? Is there any calculation for this? How to overcome this problem? Is there anything else that has to be taken into account when doing this? Thanks in advance! Venu G. **Disclaimer** Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited.
RE: ORA-01031 error
Title: Message Try connecting as some other user and see. You may connect SYS as SYSDBA ONLY from 9i onwards, keep that in mind! Also check whether your client supports connecting as SYSDBA, I'm not sure it does! Rgds, Venu G. OCP 8i & 9i -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 3:19 PMTo: Multiple recipients of list ORACLE-LSubject: ORA-01031 errorHi all, I tried to connect to a remote database running Oracle 9.2.0.1.0. from my Oracle Client (SQL*Plus) which is of version 8.0.5.0.0 as SYS. I get an error message :- ORA-01031 : Insufficient Privileges I tried using Password file as well, but in vain... Could someone help me in this regard. Thanks in advance.. Regards Jai **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Installing 8.1.7.3 along with 8.1.6.0
Thanks a lot for the info.. Couple of clarifications req... Will the existing 8.1.6 DB still exist after migration or will it be changed to 8.1.7.4? At the time of installation will I have to shutdown the existing DBs?? Thanks, Venu G. -Original Message- Sent: Wednesday, December 11, 2002 2:49 PM To: Multiple recipients of list ORACLE-L You can install 8.1.7 on a machine that has 8.1.6. During the install you can just migrate the database to 8.1.7 and save all the hassle. The migration process show you a screen with the 8.1.6 databases installed on the machine and allow you to choose which one you will migrate. This way you do not have to export/import/notify users etc. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, December 11, 2002 5:33 AM Hi, We have 8.1.6.0 installed on a NT server. There are 2 databases(development of size 120MB when FULL export is done) already running on the system. Now our requirement is to install 8.1.7.4 and bring one of the DB into this environment. My plan of approach is as follows, any feedback, comments are most welcome: 1) Shutdown all the existing databases 2) Install 8.1.7.3 from cd's and apply 8.1.7.4 patch to bring it to ver 8.1.7.4 3) Bring the 8.1.6 databases UP 4) Create a new database in 8.1.7.4 environment 5) Export the 8.1.6 database and import it into 8.1.7.4's database (I decided to do it with export as the DB was not very large) 6) Update the devolopers with the new DB's SID and etc. details 7) Drop the 8.1.6 database once devolopers are satisfied with the 8.1.7.4 database Questions: 1) Are there any ramifications of installing 8.1.7.4 engine on the same system where 8.1.6 is already present? Obviously in a different home. 2) Database migration in this scenario is done using EXP/IMP. Is this ok? Why? Any better alternatives? 3) Do I have to really shutdown the databases and other oracle services before starting the 8.1.7.4 installation? Thanks, Venu G. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
Installing 8.1.7.3 along with 8.1.6.0
Hi, We have 8.1.6.0 installed on a NT server. There are 2 databases(development of size 120MB when FULL export is done) already running on the system. Now our requirement is to install 8.1.7.4 and bring one of the DB into this environment. My plan of approach is as follows, any feedback, comments are most welcome: 1) Shutdown all the existing databases 2) Install 8.1.7.3 from cd's and apply 8.1.7.4 patch to bring it to ver 8.1.7.4 3) Bring the 8.1.6 databases UP 4) Create a new database in 8.1.7.4 environment 5) Export the 8.1.6 database and import it into 8.1.7.4's database (I decided to do it with export as the DB was not very large) 6) Update the devolopers with the new DB's SID and etc. details 7) Drop the 8.1.6 database once devolopers are satisfied with the 8.1.7.4 database Questions: 1) Are there any ramifications of installing 8.1.7.4 engine on the same system where 8.1.6 is already present? Obviously in a different home. 2) Database migration in this scenario is done using EXP/IMP. Is this ok? Why? Any better alternatives? 3) Do I have to really shutdown the databases and other oracle services before starting the 8.1.7.4 installation? Thanks, Venu G. **Disclaimer** Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited.