RE: Performance tuning
This is where the balancing comes into picture. If it is a bulk Insert then definitely the performance would degrade to the extent that it has to create an entry in the Index at a particular place. If there are so many indexes on this table you should visualise them in such a manner that a concatenated index could be formed to take care of all your selects on this table. The other alternative is to go in for partitioning and do the data partioning by range to that the selective data is getting indexed by the local index and not the global index on the table is getting effected. However if the inserts are huge than the Selects than dropping the index is beneficial, but if the Selects are too much that the index is beneficial. It relayy depends on the nature of the application. If the rows you want to retrieve from the table are in the range of 5% - 10% then index is beneficial else it could be better for the CBO to go in for a Full table scan. Moreover if it's a buldk insert you can disable the index at that point of time and then rebuild it online after the insert is over for that data to be used in Selects but if the inserts are happening in an OLTP application then you are the best judge, Vikas Khanna [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 07, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I have a table in my application . This table gets all inserts during one procedure and select during other . Now if I make an index on this then the first procedure gets slow and if i drop the index then the second procedure gets very slow. Is there some solution to get out of this problem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dilmohan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna 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: Table Design
One table should do the purpose and partioning would be a very useful option to go in for and the local indexes would take care of the efficiency in terms of retreival. More options to work in as one of the partitions goes down. The other nine would still be active and recovery could be carried on the damaged partition. Manageablility of maintaining 10 differennt tables and their associated objects is a big concern now a days. -Original Message- [mailto:PK_Deepa/VGIL;vguard.satyam.net.in] Sent: Wednesday, October 30, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Hello We are doing database design for a project. We have 10 distinct transactions types and the total number of records is expected to be around 5,00,000 taking all transactions together.We have normalised the tables and decided to store all of them together in a single table identified by the transaction type and other unique fields. We would like to know which option would be the best so that we can retrieve data most efficiently Option-1. Maintain 10 different tables for each transaction type (i.e 50 records will be split among 10 tables) Option-2 Store all of them together in a single table identified by the transaction type and other unique fields. Regards, Deepa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna 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: Re-creating control files with larger maxdatafile setting
KG, It will not help just by increasing the db_files parameter in the Init.ora file. Whenever you need to change MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS for your database the change is to recreate the control file and then mount and open the database. What needs to be done is : Svrmgr Alter database backup controlfile to TRACE; This would generate an ASCII text based file at the location defined by USE_DUMP_DEST in your init.ora. Edit that file in any text editor and chane the Value of MAXDATAFILES (in your case). Save the file with some name at a particular destination.eg C:\Controlfile\Control.Sql SVRMGR Shutdown immediate; Instance Shutdown. SVRMGRStartup Nomount; Instance Started. SVRMGR@C:\Controlfile\Control.Sql; Statement Processed. SVRMGR Alter database mount; Database Mounted. Svrmgr Alter database open; Database opened. Now you can create the new datafiles to expand your database. Never the parameter db_files can be more that the MADATAFILES declared in your controlfile; Thanks Vikas Khanna -Original Message- Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L George: If you want to recreate the controlfile **just to ** increase the max datafiles, you can simply increase the db_files parameter and bounce the database. This will automatically chance the max datafiles settings when you create the additional datafiles. KG -Original Message- Leonard (ZA) Sent: Thursday, September 19, 2002 12:13 AM To: Multiple recipients of list ORACLE-L Hi all Does someone maybe have a step by step guide to re-creating the control files. I have found that one the my databases the maxdatafiles has been set to small, would like to increase it now. thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Leonard (ZA) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna 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: bind variables
Hi Nicoll, The importance of bind variables, to use in OLTP application is such that if we use, we survive else one or the other day we would feel uncomfortable over the performance issues and would repent over the scalability of the application. Yes, if we use bind variables, the parser does not know how best to execute the statement but basis on rough estimates (50% values are such that the table is holding on basis the WHERE Clause),it generates many execution plans and holds the one which has lowest cost in the V$library cache because at that time the optimizer is not sure what values to bind. But in the case of DSS applications yes I do agree that these bind variables are problematic as the data is in abundance and we have to use the data skewness and its other credentials for the optimizer best to use as we are playing with abundance of data. Thanks Vikas Khanna -Original Message- Sent: 06 September 2002 19:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead
Must Read for Every Developer and DBA
Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 insert Special Characters ?
1. Use Bind Variables, explicitly in your SQL statements would resolve this problem. This would enhance the perf. as there would be one time parsing and many time executions. Hard Parse can sometimes be more memory and CPU intensive rather than the execution time any SQL statement takes. 2. If not Interested in Bind Variables then, For single Quotation marks use Chr(39) || '1234' || Chr(39) in your SQL statement to solve the problem. Vikas Khanna -Original Message- Sent: Wednesday, July 31, 2002 2:33 PM To: Multiple recipients of list ORACLE-L i presume the statement is a string that u want to insert... so u can write like this : select 'where part_no = ''1234'' and name=''guest''' from dual; check the quotes out rgds, Ams, www.medicomsoft.com |-Original Message- |From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of guess who |Sent: Wednesday, July 31, 2002 8:58 AM |To: Multiple recipients of list ORACLE-L |Subject: How to insert Special Characters ? | | |I want to insert the following characters , | |1.) ' | |2.) | |3.) | |for example i want to insert the following line as it looks... | | where part_no='1234' and name='guest' | |how to do ? | |can anyone help ... | |Regards, |Prakash. | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: guess who | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amjad Saiyed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 change nls date format.
Yes. You can do that when you are creating an instance, define the parameter nls_date_format in the init.ora file as per your requirement, then the nls_date_format for the Instance would be as supplied by you. Alter Session/Alter System would do the same at the time so defined. Vikas Khanna -Original Message- Sent: Tuesday, July 30, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Hi, The default date format for an instance is dd-mon-. Ex when issue the following statement Select to_char(sysdate) from dual; The output is 01-Jan-2002. I want to change this format to mm/dd/. According to Oracle documentation this can be changed @ sesion level by the following : Alter session set nls_date_format='mm/dd/'; ( alter system does not work). To change it @ the instance level, the following needs to be added in the init.ora file : Nls_date_format=mm/dd/. After starting the instance, when we check v$parameter, this change is reflected. But the same is not reflected while selecting data. Again when the sysdate select is issued the result does not change. The date format was not specified while installing Oracle or creating the instance, it was a default installation. Is there anyway to change this? Other Info on Database : Version Oracle 8.1.6 OS - Windows NT NLS_TERRITORY - AMERICA NLS_LANGUAGE - AMERICAN Charset WE8ISO8851 Thanks in advance Sunil Gompa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: import question
If the Export has been taken with DIRECT = Y, meaning that the SQL Evaluation buffer has to be bypassed then Import would not generate the Redo buffer while Importing back the objects and the relevant data. While Export it just bypasses the SQL Evaluation buffer,make it faster and directly put things in TTC buffer recognized by Import of Oracle If that option was not selected, frequent commit would enable the checkpoint more often and thus LGWR writing the buffers to redo logs more often. Enable a buffer size which could be substantial high, so that more number of rows could be extracted from the Export file in an array thus boosting the perf. Thanks Vikas Khanna -Original Message- Sent: Monday, July 29, 2002 5:58 PM To: Multiple recipients of list ORACLE-L Hi, We are importing a large amount of data and uit is generating a huge amount of redo..is there any way of avoiding this- unfortunately this is on an 8.0.6 database?? Any help/advice would be appreciated!! Rgds FAwzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ANALYZE question
No Impact. Analyze would just collect the latest statistics for the concerned table and the next time any query gets fired on this table the optimizer (CBO) would generate the execution plan based on these statistics. I also believe that this would be healthier sign as the CBO is generating plans as per the latest what is available and not on the stale ones. Moreover, it would be good that instead of Using Analyze you should use DBMS_STATS.gether_table_statistics stored procedure as it : 1) DBMS_STATS can run in parallel mode and hence would be faster than Analyze (which is a serial operation) 2) Only if the statistics are stale the DBMS_STATS would execute. 3) You can always Export/Import/Set the statistics from one db to another db. Thanks, Vikas Khanna -Original Message- Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Myers-Briggs - Too many off topic topics! MUST READ CAREFULLY
Dual is a dummy table which oracle has provided to retrieve the values of Environment Variables. eg. Select user from dual; Select sysdate from dual; and many other manipulations like Select 12 * 12 + 3 from dual; As the ANSI/SQL syntax requires FROM Clause so that's the reason Oracle has given you this table to retrieve exactly one row whenever required. as Select 12 * 12 + 3; would certainly give you error. You can also give : - Select 12 * 12 + 3 from emp but it would provide 14 rows instead of just one row having results as : 147 147 147 147 147 147 147 147 147 147 147 147 147 147 14 rows selected -Original Message- Sent: Wednesday, July 24, 2002 3:08 PM To: Multiple recipients of list ORACLE-L what is dual? On 23 Jul 2002 at 10:53, Farnsworth, Dave wrote: ... The rest of us are just goofs. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
MUST read Oracle Architecture - Abrief Intro
Oracle has no concept wrt. the date time of Operating System for running individually as a product. It just takes the timestamp in certain DML's while updating and inserting the rows having DATE as datatype. Nothing more than this. Oracle works on the mechanism of SCN ie. System Change Number which gets monotonically incremented one by one after every commit takes place. It has nothing to do wrt. the OS time. When the Oracle engine gets started the control file reads the location of datafiles and redo logs and the latest SCN is read and compared with those present in datafiles aand redo logs. If the SCN is not matched menas the database was abnormally shut down and need thread recovery. Smon does this task independently and roll forwards the txn's which were left in the buffer cache and were not pushed back to d.files during checkpoint process. These txn's were committed at the user end. Now the ones which were not committed would be rolled back internally by Oracle b'ground process SMON or Server Process initiated by user process and would rollback the blocks who soever touches them first. A little bit of ARCHITECTURE OF ORACLE .. Bye for now. No problems at the time lagging behind or time forwarding of the OS -Original Message- Sent: Wednesday, July 24, 2002 6:00 PM To: Multiple recipients of list ORACLE-L Oracle will continue to work fine (as it uses SCN numbers for consistency and transaction logging rather than dates). However, if you have any apps which use timestamps in the data, then I'd do some more investigation for the ramnifications on the application logic side... -Original Message- Sent: Wednesday, July 24, 2002 7:13 AM To: Multiple recipients of list ORACLE-L If you can take the database down for 1.25 hours. I will hesitate to startup the database with time less then last closing time. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 22, 2002 8:33 PM Hi Guys, I need to put one hour back for my OS(aix) So How will my database(7.3) handle this?? What steps I have to take?? Any light regarding that?? Thanks in advance peter. _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 force the DB to SKIP tx recovery ?
No, Instance recovery has to take place internally, SMON has to recover the committed transactions which were left hanging in the cache to datafiles so that the files become synchronized. After that the db gets opened and roll backwards takes its own time. It depends SMON does this job in the background and any other Server process if needs that block can rollback the uncommitted transaction and proceed with the block. If you wish to recover the instance recovery very fast then set the LOG_CHECKPOINT_INTERVAL,LOG_CHECKPOINT_TIMEOUT,FAST_START_IO_TARGET parameters to force the checkpoints quite often, this would open the instance under crash quite fast as it has to perform less work. But more the checkpoints initiated more the performance degradations as B'ground processes would be under tremendous work. Hope it clarifies. Vikas Khanna -Original Message- Sent: Tuesday, July 23, 2002 1:48 PM To: Multiple recipients of list ORACLE-L List, ...was doing a looog update, was taking forever, i rebooted the PC, as expected, the *open* of the instance is taking a very long time.. and i cannot wait... is there a way to open the DB *without* letting oracle perform the thread recovery ?? i just to open the DB data lose is acceptable.. TIA -rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 force the DB to SKIP tx recovery ?
Rahul, Instance recovery requires Rollforward and Rollbackwards. If the transaction is not committed it has to be rollbacked once the instance gets started. Let us assume that there are no committed transactions which were left in the buffer cache to be flushed back on to datafiles. This means I am left with Uncommitted txns to get them rollbackwards. This is done by SMON in the background or Server process which needs those blocks but the Instance and the db are opened and ready for use. The db is opened ie. what u wanted and that's the way Oracle does work. -Original Message- Sent: Tuesday, July 23, 2002 2:10 PM To: '[EMAIL PROTECTED]' Cc: 'Oracle List I' Vikas, even if the parameters were set for the checkpoint to happen often, all the transactions *not* commited would still be rolled back !! for example, when i shut down the PC in the middle of doing an update of 1/2 a million rows !!! (how would these parameters help?) -- From: Vikas Khanna[SMTP:[EMAIL PROTECTED]] No, Instance recovery has to take place internally, SMON has to recover the committed transactions which were left hanging in the cache to datafiles so that the files become synchronized. After that the db gets opened and roll backwards takes its own time. It depends SMON does this job in the background and any other Server process if needs that block can rollback the uncommitted transaction and proceed with the block. If you wish to recover the instance recovery very fast then set the LOG_CHECKPOINT_INTERVAL,LOG_CHECKPOINT_TIMEOUT,FAST_START_IO_TARGET parameters to force the checkpoints quite often, this would open the instance under crash quite fast as it has to perform less work. But more the checkpoints initiated more the performance degradations as B'ground processes would be under tremendous work. Hope it clarifies. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle - 32bit/64bit??
If 64 bit then Oracle9i Enterprise Edition Release 9.0.1.2.0 - 64bit Production else Oracle9i Enterprise Edition Release 9.0.1.2.0 end if; Vikas Khanna -Original Message- Sent: Tuesday, July 23, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Just log into the database using sqlplus and it will tell you /u01/app/oraclesqlplus / SQL*Plus: Release 9.0.1.0.0 - Production on Tue Jul 23 10:16:50 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.2.0 - 64bit Production With the Partitioning option JServer Release 9.0.1.2.0 - Production SQL -Original Message- Sent: 23 July 2002 00:09 To: Multiple recipients of list ORACLE-L Hi, Does anyone know how I can verify if a database is 32 or 64 bit? Thanks. elain _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Why Multiple Parses in Trace ?
If this statement is a loop and the counter works atleast twice, it would show 2 parse calls and 2 executions. You should basically put this statement in the main class and just bind the variables in the callable classes and just execute the statement. In this way it would be 1 parse call and many executions. Anyway a soft parse is not too bad as compared to Hard parse and the applications have proved them to be reasonable functioning well. The CPU and the memory consumed is significantly just 1% what was compared with the hard parse. Vikas Khanna -Original Message- Sent: Tuesday, July 23, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Change the application so that it doesn't parse twice. See www.hotsos.com/dnloads/1.Holt,Millsap2000.03.01-Scaling.pdf for details. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on Oracle(r) System Performance, Feb 9-12 Dallas -Original Message- Sent: Monday, July 22, 2002 10:43 PM To: Multiple recipients of list ORACLE-L Cary Millsap$B!$(J As for the soft parse, it also consumes cpu , how can we eleminate the softparse? Thanks. 2002-07-22 08:58:00 You wrote: ...Because the application requested two parse calls for this statement from the server. The first one was a hard parse (server had never seen the statement before), and the second one was a parse call that did not result in a hard parse. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- VIVEK_SHARMA Sent: Monday, July 22, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Qs Why is Parse = 2 in the following Query ? Solaris 8 Oracle 8.1.7 SGA = 60 MB shared_pool_size = 30 MB *** * select mesg, lchg_user_id, TO_CHAR(lchg_time,'DD-MM- HH24:MI:SS'), rcre_user_id, TO_CHAR(rcre_time,'DD-MM- HH24:MI:SS'), tran_id, TO_CHAR(tran_date,'DD-MM- HH24:MI:SS'), NVL(ts_cnt,0), sol_id, contra_acid, tran_amt||'!'||tran_crncy_code, TO_CHAR(value_date,'DD-MM- HH24:MI:SS'), tran_crncy_code, central_or_local_code, req_advc_ind, sys_gen_flg, rowid FROM TBA_REF_TRN_TBL WHERE cmd = :1 AND cust_or_card_id = :2 AND system_date_time = TO_DATE( :3 ,'DD-MM- HH24:MI:SS') AND dcc_id = :4 AND sno = :5 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse2 0.06 0.07 1 0 1 0 Execute 2 0.00 0.00 0 0 0 0 Fetch2 0.00 0.02 3 6 0 0 --- -- -- -- -- -- -- total6 0.06 0.09 4 6 1 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 40 (TBAGEN) Rows Row Source Operation --- --- 0 TABLE ACCESS BY INDEX ROWID REF_TRN_TBL 1 INDEX UNIQUE SCAN (object id 6561) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS (BY INDEX ROWID) OF 'REF_TRN_TBL' 1INDEX (UNIQUE SCAN) OF 'IDX_REF_TRN_TBL' (UNIQUE) *** * -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want
RE: To create a new datafile or to increase a existing datafile.
Title: To create a new datafile or to increase a existing datafile. In this case anything would do. Better would be to expand the already mentioned dfiles as the Sixth datafile would be placed on to some H.Disk and then I/O contention to that disk would increase. Better is to redistribute the load among the various datafiles. If you have one hard disk then it would not matter at all between the two different scenarios. Vikas Khanna -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Tuesday, July 16, 2002 1:53 PMTo: Multiple recipients of list ORACLE-LSubject: To create a new datafile or to increase a existing datafile. Hello Gurus Which is the better policy: Say you have five datafiles of 1GB in size each, now the total freespace is less than 10%. Would it be wise to increase the datafiles to 2GB or would it be better for the system to add a sixth datafile? Would appreciate any views on this point. Rgds Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. 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
RE: Dedicated connections and Memory usage
Use need to use pmap feature of Unix in order to see the real RAM associated with the processes. Vikas Khanna Oracle - DBA -Original Message- Sent: Wednesday, June 26, 2002 5:45 PM To: Multiple recipients of list ORACLE-L Need a little assistance on some concepts, so to speak.. When looking at the processes(dedicated connections) of an 816 database on a UNIX environment-the memory used for each process appears to be the same as the SGA. It's been awhile since I had to dig into this, but I thought that the dedicated connections had their own PGA(real memory of the machine). So is it coincidence that the dedicated connections (all 180 of them) are using the same amount of memory as the SGA?? What am I missing here??? TIA Greg Loughmiller Sr Manager - Enterprise Data Architecture gloughmiller (IPS) 678.893.3217 (office) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Loughmiller, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Order rows
Ther is no concept first row/ last row in any RDBMS. The concept of ROWID fails as the rows are deleted and hence inserted again. The previous ROWID's are reallocated again. The only way you can get the rows sorted out in the way they have been entered is by creating a column in the table specifying the created_Date as sysdate(). This would continuously prop up the table data wrt this column. And then you could do order by on this column to get the desired result. Regards, Vikas Khanna -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Hi All Is there any method in Oracle to capture or order the rows in a table in the order they were entered. I tried it with rowid but when a row is deleted, the rowid corresponding to this row is reassigned for a new row which is inserted into the table at a later stage. Eg. SQL select rowid,abc.* from abc order by rowid; ROWID A -- -- AAAFmYAASAAAYsqAAA100 AAAFmYAASAAAYsqAAB200 AAAFmYAASAAAYsqAAC300 I deleted one transaction. delete from abc where a=200; commit; Then I inserted two rows. insert into abc values(500); insert into abc values(600); commit; Now when I order by rowid SQL select rowid,abc.* from abc order by rowid; ROWID A -- -- AAAFmYAASAAAYsqAAA100 AAAFmYAASAAAYsqAAB600 AAAFmYAASAAAYsqAAC300 AAAFmYAASAAAYsqAAD500 I.e The values I entered last appeared second.The rowid (AAAFmYAASAAAYsqAAB ) corresponding to the row I deleted was reassigned for the last entered row (a=600). What I want is that this must be sorted in the order of its entry. Can anyone help me out. Thanks in advance Systems. -- 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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Help Urgent!!!!!!!!!
Select a.id,a.amount,ccinlineview.bamt from bb a,(Select id,sum(b.amount) bamt from cc b group by id) ccinlineview where a.id = ccinlineview.id ID AMOUNT BAMT -- -- -- 1 1000 1000 1 row selected. -Original Message-From: sultan [mailto:[EMAIL PROTECTED]]Sent: Monday, May 13, 2002 10:53 AMTo: Multiple recipients of list ORACLE-LSubject: SQL Help Urgent! Hi gurus I have two tables like this SQL select * from bb; ID AMOUNT DT-- -- -- 1 1000 10-MAY-02 SQL select * from cc; ID AMOUNT-- --- 1 200 1 300 1 500 My query like this = SQL select a.id,sum(a.amount),sum(b.amount) from bb a,cc b where 2 a.id=b.id 3 group by 4 a.id; ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 1 3000 1000 Based on the details table rows it sum up three times the master amount ,that is why it shows 3000. But my output should be like this ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 11000 1000 Anybody can help me in this issue please. Regards. syed
RE: STANDBY DATABASE QUESTION?
Original Message-From: Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]Sent: Friday, April 19, 2002 2:33 PMTo: Multiple recipients of list ORACLE-LSubject: STANDBY DATABASE QUESTION? Dear Gurus , I want to ask something .. I want to apply a standby server ,What I wonder is , if I set it up automatic , is the archive log files automatically copied to standby server , or do I move it manually . ???[Vikas Khanna]You have do transfer them manually to the standy by server. OR you can write automated scripts todo the job for you. And my second question is on my Win2000 Adv. Server C:\ drive , PAGEFILE.SYS file is nearly 2 GB , AND does not increase , Why is it so big ? I know that it is because of SWAP .But I am sure that there must not be swap since I have 8 GB ram and only 40 users use database for now. . [Vikas Khanna] Its the max limit for the size of the file which this OS support. Even if you might have 8 GB RAM what depends is your SGA Size. . Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA.
RE: 8.0.5 to 8.1.7 - export/import
Oracle Database is Portable means that it can be ported to any OS platform. Take a full Export of the database and then use the imp utility of Oracle to build the exact database what was there on the previous one. If you want to import certain objects then you need to create the tablespace with the same name as from where you took the export. If that tablespace is not existing then the default tablespace of the user in the Second database would be considered for the import. If the tablespace does exist then even if the users' default tablespace is not the one mentioned then also the object would be imported into the designated tablespace. Regards, Vikas Khanna -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 10:38 AM To: Multiple recipients of list ORACLE-L Hi It is Possible to import from NT to Solaris. The table space name should be the same otherwise build the table and then import by ignoring the errors. Ayyappan.S This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. Visit us @ www.ssiworldwide.com -- 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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
