RE: Function
Hi Divya, you have to use dynamicsql for calling the function . First step select the function name into a variable say ls_fn_name from the table func .Then create a dynamic statement 'Select' || ls_fn_name || ' from dual ' and cpature the return value into another variable . If u are using oracle 8i , u can use execute immediate statement . for prior oracle versions use DBMS_SQL statement . Regards, Shankar -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Hi All I have stored a user defined function as Varchar field in a table. How do I execute this function. Here is the table where the function is stored. SQL select * from func; FUNCTION_NAME - Calc_radius(5) This procedure contains the following Code : create function calc_radius(r in number) return number is begin return 3.14*r*r; end; Executing this funtion at SQL Prompt give the output as SQL SELECT CALC_RADIUS(5) FROM DUAL; CALC_RADIUS(5) -- 78.5 I want to execute this function from a PL/SQL Block. I tried to store this function into a variable and then execute it. But it returns only the content of the field FUNCTION_NAME and not the value. Can anyone suggest a solution for this problem ? Regards Dpb -- 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). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
DB FILE PARALLEL WRITE
Hi, I have this DB FILE PARALLEL WRITE as the top event (right after SQL*NET,rdbms.,pmon smon) and according to the documentation this has to do with my DBWR but no mention is done on how to solve this. Is this purely another indication of poor disk performance or should I be looking somewhere else? TIA jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora-03116
we are experiencing ora-03116 error (invalid buffer length) How can we solve this . ? Urgent please . Thank you . Any Ýdea please ? bunyamin
analyze partitioned indexes
Title: analyze partitioned indexes Hi, I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes. What I'm doing is: delete from admin.tb_index_stats where index_owner = '1'; commit; FOR EACH_ROW IN (SELECT OWNER || '.' || INDEX_NAME as INDEX_NAME FROM DBA_INDEXES WHERE OWNER = '1') LOOP t_tables(t_tables.COUNT + 1) := EACH_ROW.INDEX_NAME; END LOOP; FOR i IN 1 .. t_tables.COUNT LOOP BEGIN EXECUTE IMMEDIATE 'ANALYZE INDEX ' || t_tables(i) || ' VALIDATE STRUCTURE'; Will this also work for all the partitions in a partitioned index? Or what would be a way to get all the index partitions and analyze them separately? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Re: os block size versus oracle bock size
Hi Jack and List, we have the same situation. Our DB (mainly OLTP) was built with db_block_size 8k and ext2 filesystem (Linux) has 4k block size. AFAIK 4k is max block size for ext2. Do you think it's worth to rebuild the DB with 4k block in order to adjust it to ext2's block size? I know it's quite a difficult question, so I will appreciate your general advices or thoughts. And does anyone know if direct or async options are available on ext2 ? I have little experience of working on Linux. Thanks, Ed Hi I'd say yes. For every Oracle block read the OS has to read two block which causes overhead. Jack [EMAIL PROTECTED]@fatcity.com on 23-10-2001 16:05:21 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an
Re: v$sqlarea statistics
Hi Deepak,
Can you elaborate what is consistent gets
-
Eric,
here's a high level from my understanding on this
issue:
Buffer gets {also called Logical IO's}
These happen as oracle scans blocks of data in the
buffercache(in-mem scans). Many people believe that
since these are memory reads, they are inexpensive. I
have seen the contrary in many cases and have seen
that these are the ones which take the most cpu
clycles, therby making your system CPU Bound.
Resolution of this is to tune your sql by having it
use better access paths (indexes). Also consider
de-norming in ordr to avoid too many joins
I consider this the most important metrics in
identifying bad SQL. i have seen cases where
frequently executed queries were performing millions
of LIO's and hosing up the CPU. A simple index / or
Adding hint can reduce this number by a very high
factor resulting in great gains.
DiskReads {also called physical IO's)
This obviously means that there are a lot of disk
reads required to satisfy your query. Reasons: maybe
you are using ineffcient access paths/bad sql or u
just have insuffient (small) memory to support your
app. High Diskreads is the reason that makes your
system IO bound. Resolution is again the same as
described above. In addition, one of the assumtions
here is that you have spread your datafiles/logs/cf
optimally. Also consider using the recycle buffer pool
feature to avoid an innocent FTS from flushing
everything from your cache. Obviously you cannot
always prevent any of these and some disk read are
inevitable.
hth
Deepak:
--- Erik Williams [EMAIL PROTECTED] wrote:
I am trying to identify the most harmful statements
in an application. From
the Oracle Performance and Tuning Tips and
Techniques book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number of
buffer gets and the
other looks at the statements with a high number of
disk reads. Some of the
statements appear in both lists, but some in only
one. If all of the disk
reads are moving blocks into the buffer cache, what
is the difference
between the two measures? Can anyone explain the
difference between the two
measures?
Thanks.
Erik
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
---
The contents of this e-mail are confidential to the ordinary user
of the e-mail address to which it was addressed and may also be
privileged. If you are not the addressee of this e-mail you should
not copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this e-mail in error
please notify us by telephone or e-mail the sender by replying to
this message, and then delete this e-mail and other copies of it
from your computer system. Thank you.
We reserve the right to monitor all e-mail communications through
our network.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information
RE: tool to dump out space info
Re: analyze partitioned indexes
Helmut, have you tried your code, that would be the best way to see if it works(hint: partitioned indexes work pretty much like normal indexes, in that you can analyze the table and the indexes will be analyzed also, including partitioned tables/indexes). joe Daiminger, Helmut wrote: Hi, I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes. What I'm doing is: delete from admin.tb_index_stats where index_owner = '1'; commit; FOR EACH_ROW IN (SELECT OWNER || '.' || INDEX_NAME as INDEX_NAME FROM DBA_INDEXES WHERE OWNER = '1') LOOP t_tables(t_tables.COUNT + 1) := EACH_ROW.INDEX_NAME; END LOOP; FOR i IN 1 .. t_tables.COUNT LOOP BEGIN EXECUTE IMMEDIATE 'ANALYZE INDEX ' || t_tables(i) || ' VALIDATE STRUCTURE'; Will this also work for all the partitions in a partitioned index? Or what would be a way to get all the index partitions and analyze them separately? This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Function
Dpb, within PL/SQL you can: -- declare a variable to store the result from the function func_res number; -- within the PL/SQL block, call the function and store the result: func_res := calc_radius(5); -- or, you can select calc_radius(5) into func_res from dual; You do NOT need to use the execute immediate command. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 2:20 AM To: Multiple recipients of list ORACLE-L Hi All I have stored a user defined function as Varchar field in a table. How do I execute this function. Here is the table where the function is stored. SQL select * from func; FUNCTION_NAME - Calc_radius(5) This procedure contains the following Code : create function calc_radius(r in number) return number is begin return 3.14*r*r; end; Executing this funtion at SQL Prompt give the output as SQL SELECT CALC_RADIUS(5) FROM DUAL; CALC_RADIUS(5) -- 78.5 I want to execute this function from a PL/SQL Block. I tried to store this function into a variable and then execute it. But it returns only the content of the field FUNCTION_NAME and not the value. Can anyone suggest a solution for this problem ? Regards Dpb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Code to Validate email addresses
Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: ADO.NET and Oracle 8.1.6
Try to connect with complete connect string (tnsname + names_default_domain from sqlnet.ora) regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 23. Oktober 2001 19:02 An: Multiple recipients of list ORACLE-L Betreff: ADO.NET and Oracle 8.1.6 We're getting ORA-12154: TNS:could not resolve service name I've checked the TNSNAMES.ORA and SQLNET.ORA and they seem OK. WIN2K. Using the MS Oracle ODBC driver (the Oracle ODBC driver is a no go). Anyone have any experience with this? TIA -- 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: Schoen Volker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Help with passwords
Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Code to Validate email addresses
If it is an html form, you may want to consider implementing the code as JavaScript. This is how it was done at the last company I worked for. This way, the addresses are validated long before they get to the db. This was also the case for credit card numbers. Also, this moved the processing off the db to the client machine. Unfortunately, I dont have the JavaScript code for you, but Im sure a google search will return some. Erik -Original Message- From: Oweson Flynn [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Code to Validate email addresses Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Code to Validate email addresses
Quite tricky this one...I would imagine there will be a few combinations for valid email addresses, you could try looking at the World Wide Web Consortium page www.w3.org, or some similar site for the standards documents. HTH Kev. hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 24 October 2001 14:15 To: Multiple recipients of list ORACLE-L Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RAID for a development box
We're setting up a development box that will have a number of instances on it. We won't need any backup, since we can easily re-create the databases from testing instances. I'm planning on implementing the disk storage as RAID0 - a single logical volume stripped across all the drives (6), with the stripe size set to the OS (Win2K) block size. We will only have 4 or 5 developers on the box at any one time and I want to maximize IO utilization. Any heads up here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Help with passwords
save the key then to set it back alter user username idenfified by values 'HEX KEY HERE'; joe [EMAIL PROTECTED] 10/24/01 10:10AM Hi all,I've got a little question, which i can't find simply on metalink.I've got one user with a not know password and i want to temporary changeit;s password.I know it is possible to save the hex-key in dba_users.But how can i change it back to that hex-key?I need temporary this user account and can change the password, but i wantto change it back to the original.Thx anyway,Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
AW: Help with passwords
Hi Marco, Following select will generate a alter user with old password. After generating SQL you can change password of a user. With the generated SQL ypu can set password bak to original one. SELECT 'alter user ' || username || ' identified ' || DECODE(password, NULL, 'EXTERNALLY', ' by values ' || || password || ) ||';' FROMdba_users ORDER BYusername; regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 24. Oktober 2001 16:10 An: Multiple recipients of list ORACLE-L Betreff: Help with passwords Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- 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: Schoen Volker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Help with passwords
It's just ALTER USER username IDENTIFIED BY VALUES 'hex-key-whatever'; Substituting the username and password value from dba_users as appropriate. There are scripts that save it in a file with the commands to switch it back, but it's easy enough to do manually. Simon Anderson Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- 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).
utl_file and record delimters
I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Hostname
Hi, how can I retrieve the hostname from within PL/SQL ? TIA Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Code to Validate email addresses
Hi The definition is found in RFC 2822 Internet Message Format (ftp://ftp.isi.edu/in-notes/rfc2822.txt) Oweson Flynn wrote: Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Regards Peter Gram Miracle A/S http://MiracleAS.dk Tel: +45 2527 7107 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Gram INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: RMAN: nocatalog; remove backed up archived redos
Leng, If you dont use a catalog with rman you wouldn't be able to store scripts to make automated backups and restore. Also that means that all the information about your backups will be stored in the control file of the target database, if you loose your controlfiles will loose all the information of your backups, so they will be useless. Allocate channel for delete type disk; change datafilecopy 'yourfile' delete; release channel; This would work for you Luck (Suerte) Ramon E. Estevez [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 809-565-3121 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Leng Kaing Enviado el: Tuesday, 23 October, 2001 6:10 PM Para: Multiple recipients of list ORACLE-L Asunto: RMAN: nocatalog; remove backed up archived redos Hello everyone, Env: 8i and 8.0 I've been digging around the rman manuals and metalink but can't seem to find anything decent on this so thought I'd try this forum... Firstly, what functionality do you loose when you don't use a catalog? 2ndly, how does one delete old backups? I'm backing up to disk. All is fine with the backup. But now I need to delete the backups from disk, and remove the information about the backup from the controlfile. Tried doing this but failed: -- RMAN allocate channel for delete type disk; RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: delete_05 RMAN-08500: channel delete_05: sid=15 devtype=DISK RMAN change backuppiece 70 delete; RMAN-03022: compiling command: change RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-03002: failure during compilation of command RMAN-03013: command type: change RMAN-06091: no channel allocated for maintenance (of an appropriate type) --- What's wrong here? I've allocated a channel and RMAN acknowledges that it's for delete. But can't do it. Help!! Thanks, Leng. = Leng Kaing - [EMAIL PROTECTED] Ph: +61-3-417-371-348 AUSOUG-VIC : http://www.ausoug.org/vic/ http://briefcase.yahoo.com.au - Yahoo! Briefcase - Manage your files online. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Leng=20Kaing?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Function
Hi, I guess what you want is more heading towards dynamic sql: Start with a PL/SQL block like this: DECLARE v_funcName VARCHAR2(50); v_statement VARCHAR2(255); BEGIN SELECT function_name INTO v_funcName FROM function WHERE function_name; v_statement := 'SELECT ' || v_funcName || ' FROM DUAL'; then, execute the statement, bind the column to a variable ... finito. END; cheers, Stefan Mercadante, Thomas F schrieb: Dpb, within PL/SQL you can: -- declare a variable to store the result from the function func_res number; -- within the PL/SQL block, call the function and store the result: func_res := calc_radius(5); -- or, you can select calc_radius(5) into func_res from dual; You do NOT need to use the execute immediate command. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 2:20 AM To: Multiple recipients of list ORACLE-L Hi All I have stored a user defined function as Varchar field in a table. How do I execute this function. Here is the table where the function is stored. SQL select * from func; FUNCTION_NAME - Calc_radius(5) This procedure contains the following Code : create function calc_radius(r in number) return number is begin return 3.14*r*r; end; Executing this funtion at SQL Prompt give the output as SQL SELECT CALC_RADIUS(5) FROM DUAL; CALC_RADIUS(5) -- 78.5 I want to execute this function from a PL/SQL Block. I tried to store this function into a variable and then execute it. But it returns only the content of the field FUNCTION_NAME and not the value. Can anyone suggest a solution for this problem ? Regards Dpb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: utl_file and record delimters
Include it n your select statement select emp||','||name||','||sal from emp; This would return 10,MILLS,9 Here using comma as a delimiter Ramon E. Estevez [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 809-565-3121 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de John Dunn Enviado el: Wednesday, 24 October, 2001 9:30 AM Para: Multiple recipients of list ORACLE-L Asunto: utl_file and record delimters I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: utl_file and record delimters
You can not specify the record delimiter with UTL_FILE. You can use UTL_FILE.GET_LINE to read records terminated with LF. LF will not be included in the return string. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 10:30 AM I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Code to Validate email addresses
If you want a definitive answer, this is it: http://RFC.net/rfc2822.html My suggestion would be that you don't actually allow all valid forms of addressing. The code to check that would be rather large to say the least, and the testing routine for it would not be too trivial either. Jared On Wednesday 24 October 2001 06:15, Oweson Flynn wrote: Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file and record delimters
Does utl_file not just read the file a line at a time and then it is upto you to programmatically divide the data up into it's chunks using substr etc. Kev. hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 24 October 2001 15:30 To: Multiple recipients of list ORACLE-L I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Help with passwords
The script below will generate a script that can be used to restore the userid to its original state. select 'alter user ' || username || ' identified by values ''' || password || || ' default tablespace ' || default_tablespace ||' temporary tablespace ' || temporary_tablespace || ' ;' from sys.dba_users where username = 'supply your username'; G.L.Alink @civ.utwente.To: Multiple recipients of list ORACLE-L nl [EMAIL PROTECTED] Sent by: rootcc: Subject: Help with passwords 10/24/2001 10:10 AM Please respond to ORACLE-L Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hostname
select host_name from v$instance; Jared On Wednesday 24 October 2001 07:55, Stefan Jahnke wrote: Hi, how can I retrieve the hostname from within PL/SQL ? TIA Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: providing 24*7 database ---
Title: RE: providing 24*7 database --- I couldn't verifythat the non-partitioned indexes become unusable after exchanging the partition for the normal table. In the sample I posted Isnipped the output of the queries on USER_INDEXES and USER_PART_INDEXES, but my tests showed that they remain valid. I'm curious to seewhy your results are different. Can you post the spooled output of your test? Thanks. Tony Aponte -Original Message-From: Narender Akula [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 23, 2001 10:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: providing 24*7 database --- Thanks all for the input. hi tony , Quick question ... when you exchange partititons with non partitioned table data , all indexes on non partitioned tables become unusable status right. do have to rebuild them afterevery exchnage... naren -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, 23 October 2001 05:06To: Multiple recipients of list ORACLE-LSubject: RE: providing 24*7 database --- We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first: There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition. Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he "published" objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITION TABLE tablename. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the published tables. HTH Tony Aponte ** pseudo-attachment ** SQL create table x(x1 number,x2 varchar2(50)); Table created. SQL create index xi1 on x(x1); Index created. SQL create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue)); Table created. SQL create index yi1 on y(x1) 2 local (partition yi1 ); Index created. SQL insert into x values (1,'original data from regular table'); 1 row created. SQL insert into y values (2,'original data from partitioned table'); 1 row created. SQL commit; Commit complete. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 2 original data from partitioned table SQL select * from y; X1 X2 -- -- 1 original data from regular table SQL select * from user_indexes; output snipped SQL select * from user_part_indexes; output snipped SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL select * from user_indexes; output snipped SQL select * from
Re: Code to Validate email addresses
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 9:15 AM Hi, We have implemented a form that emails reports. The user has to enter his email address. However, we are finding that a lot of users are entering incorrect and invalid email addresses. Obviously, we can do nothing if they enter an incorrect (but syntactically correct) email address. What I am looking for is: 1) A definition of the syntactically correct format of an email address (from some sort of authoritative source) check out rfc 822 : http://www.faqs.org/rfcs/rfc822.html 2) Hopefully some pl/sql code that will validate a sting to see if it is a valid email address. That's tough. To really syntactically validate an email address is really hard. There's a 3 page perl program in the backof the O'reily Matsering Regular Expressions Book that does the trick nicely. Maybe you could convert it to something in sqlj. If anyone can assist, I would appreciate it! Regards Oweson Flynn _ Tell me what you think, Captain, I'm all ears - Spock Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: George Schlossnagle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Loader questions
Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sequence pool
Hi, is there a smart way to generate IDs in chunks? getting an ID with nextval is perfectly ok most of the time, but sometimes the application would need to generate a pool of IDs and keep them for later use as I can't manipulate the currval of the sequence, I wander what the best solution would be thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
wait events v$filestat.readtim
Are the 'db file scattered read' and 'db file sequential read' waits directly related to the readtim values in v$filestat? For every 1/100th sec of v$filestat.readtim, should I see corresponding wait time reported for the 'db file scattered read' or 'db file sequential read'? If this is the case, where would I find the corresponding I/O value(s) for the 'direct path read' wait event? __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Bittel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to detect transactions being rolled back?
Is there a way to detect if a transaction is currently being rolled back? ie. If it fails part way thru or is cancelled by the user? Thanks in advance, John 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. CREDIT SUISSE GROUP and each of its subsidiaries each reserve the right to monitor 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 authorised to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lau, John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Help with passwords
this is documented in Kevin Loney's 7.3 edition of the DBA Handbook (look for become_another_user.sql) --- [EMAIL PROTECTED] wrote: Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sequence pool
Why not have the sequence increment by 20 (or something to your liking) and then in the application use the number from the sequence and the 19 numbers the sequence will skip. -Original Message- Sent: Wednesday, October 24, 2001 9:00 AM To: Multiple recipients of list ORACLE-L Hi, is there a smart way to generate IDs in chunks? getting an ID with nextval is perfectly ok most of the time, but sometimes the application would need to generate a pool of IDs and keep them for later use as I can't manipulate the currval of the sequence, I wander what the best solution would be thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Brian MacLean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORACLE 8.1.5 on NT run very slow ??
We have HP server with NT 4.0 installed. This server dedicate for ORACLE use. The ORACLE version is 8.1.5 (no patch installed). we found the performance NOT really good. I turn on the performance monitor and found the memory page fault and memory pages/sec are very high while SQL statement running (even only one user running). The Server have following configurations: HP LH 3000 2 CPU 500 Mhz 512 MB RAM 1200 swap space on C: ORACLE software and NT on C: (9GB, 7200RPM??) ORACLE data on d: (8 X 9GB RAID 5, 7200RPM??) ORACLE SGA 250MB Can anyone give me some hints why: 1. page fault very high? 2. SQL statement run very slow (I turn on TKPROF to trace and found it is NOT ORACLE SQL statement inefficient problem)? Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How long are statistics good for
John My initial tests indicated that not all tables which had any DMLs were getting into the'stale category'. I checked the ratioA/B where: A is the sum of inserts+deletes+updates from user_tab_modifications B is num_rows from user_tables (or user_tab_partitions). I observed that if this ratio exceeded 10%, package dbms_stats analyzed theobjects with 'GATHER STALE' option. (But I get error for the partitioned tables with this option). Anand [EMAIL PROTECTED] 10/23/01 05:20PM Anand,Just curious : Is there some test or other observation that you can sharewith the list about dbms_stats using 10% as a boundary for staleness?John KanagarajWhich version of Oracle are you using. In 8i you can set 'monitoring on' forthe tables and use dbms_stats to analyze stale. (Though, I am getting errorwhile using dbms_stats for the partitioned tables. So I have made a homemade version to analyze stale). As per my calculations, package dbms_statsconsiders statistics stale if all DMLs affect more than 10% of number ofrows.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: RAID for a development box
On my NT Quad Development box I have 9x2 Raid 1 for OS/Oracle Files. Then Raid 0+1 for the 6 drives for data. It isn't perfect, but works good. If you do raid 0 with 6 drives, make sure you look into a good stripe size so you actually use the girth. Otherwise 0+1,1+0 may be a better option. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, October 24, 2001 10:30 AM To: Multiple recipients of list ORACLE-L We're setting up a development box that will have a number of instances on it. We won't need any backup, since we can easily re-create the databases from testing instances. I'm planning on implementing the disk storage as RAID0 - a single logical volume stripped across all the drives (6), with the stripe size set to the OS (Win2K) block size. We will only have 4 or 5 developers on the box at any one time and I want to maximize IO utilization. Any heads up here? -- 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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 help! Recover a database on another server
We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: providing 24*7 database ---
Seemed fine when I tried it (and thanks for the idea!). The trick with the indexes is that the ones on the partitioned table have to be local, which was in the scripts provided, and the 'exchange partition' had to say 'including indexes', which was not. Adding 'including indexes' made this work like a charm for me. Jim [EMAIL PROTECTED] 10/24/01 11:25AM I couldn't verifythat the non-partitioned indexes become unusable after exchanging the partition for the normal table. In the sample I posted Isnipped the output of the queries on USER_INDEXES and USER_PART_INDEXES, but my tests showed that they remain valid. I'm curious to seewhy your results are different. Can you post the spooled output of your test? Thanks. Tony Aponte -Original Message-From: Narender Akula [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 23, 2001 10:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: providing 24*7 database --- Thanks all for the input. hi tony , Quick question ... when you exchange partititons with non partitioned table data , all indexes on non partitioned tables become unusable status right. do have to rebuild them afterevery exchnage... naren -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, 23 October 2001 05:06To: Multiple recipients of list ORACLE-LSubject: RE: providing 24*7 database --- We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first: There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition. Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he "published" objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITION TABLE tablename. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the published tables. HTH Tony Aponte ** pseudo-attachment ** SQL create table x(x1 number,x2 varchar2(50)); Table created. SQL create index xi1 on x(x1); Index created. SQL create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue)); Table created. SQL create index yi1 on y(x1) 2 local (partition yi1 ); Index created. SQL insert into x values (1,'original data from regular table'); 1 row created. SQL insert into y values (2,'original data from partitioned table'); 1 row created. SQL commit; Commit complete. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 2 original data from partitioned table SQL select * from y; X1 X2 -- -- 1 original data from regular table SQL select * from user_indexes; output snipped SQL select * from user_part_indexes; output snipped SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 --
RE: providing 24*7 database ---
Tony, If the partitioned indexes have a locality of global (not local), then they become invalid after activity on the underlying table partitions. Does Narender say whether his indexes are global or local? Cherie Machler Aponte, Tony [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] et cc: Sent by: Subject: RE: providing 24*7 database --- [EMAIL PROTECTED] om 10/24/01 10:25 AM Please respond to ORACLE-L I couldn't verify that the non-partitioned indexes become unusable after exchanging the partition for the normal table. In the sample I posted I snipped the output of the queries on USER_INDEXES and USER_PART_INDEXES, but my tests showed that they remain valid. I'm curious to see why your results are different. Can you post the spooled output of your test? Thanks. Tony Aponte -Original Message- From: Narender Akula [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 23, 2001 10:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: providing 24*7 database --- Thanks all for the input. hi tony , Quick question ... when you exchange partititons with non partitioned table data , all indexes on non partitioned tables become unusable status right. do have to rebuild them after every exchnage... naren -Original Message- From: Aponte, Tony [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 23 October 2001 05:06 To: Multiple recipients of list ORACLE-L Subject: RE: providing 24*7 database --- We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first: There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition. Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he published objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITION TABLE tablename. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the
Re: sequence pool
- Original Message - To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 18:33 Why not have the sequence increment by 20 (or something to your liking) and then in the application use the number from the sequence and the 19 numbers the sequence will skip. because the pool request will be rather rare, the rest of the time the IDs will be generated one by one what I thought of is having two sequences and split the range of possible IDs between them (i.e. the first half is handled by one-by-one sequence and the second half is handled by the pool sequence) but this seems quite lame (the relative order of IDs of the rows will be ruined) Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Loader questions
Lisa, No and no. Loader loads rows into tables. If you ask nicely, it will clean out the table completely before it does that. That's all it does. And it does it pretty well. It does not let users redefine tables. And it does not do selective update and delete based on the incoming data file. Other loading tools do that (I know DataJunction does, and others) but not Loader. Yosi YTTRI Lisa wrote: Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yosi Greenfield INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
Title: RE: Please help! Recover a database on another server sometimes the error message is a little strange... when it comes back 'media recovery complete' you can usually just do a 'alter database open;' command and not worry about resetting the logs. If you backed up the controlfiles, then you should run the script that was given to correctly bring the database back up. the trace file usually needs to be modified a little... but it's pretty simple if you look at it. http://tahiti.oracle.com/ and find the 'backup and recovery guide' -Original Message- From: Smith, Ron L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Subject: Please help! Recover a database on another server We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
Ron, Make sure you have atleast applied the archive logs between Archive old log sequence number before start of hot backup and Archive current log sequence number after end of hot backup. It may not need all of them but you need the last one for incomplete recovery.. Mohammed Ahsanuddin Oracle DBA -Original Message- Sent: Wednesday, October 24, 2001 12:40 PM To: Multiple recipients of list ORACLE-L We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Loader questions
You might ask your programmer to read the manual. However, the first might be a nice enhancement but it's not currently available. As for the second, SQL*Loader either inserts into an empty table or appends to a table with existing data. It doesn't do an implicit update. However, if your table has a column with a timestamp showing when each row was inserted then you could disable the unique constraint, append the new data via SQL*Loader, find the rows with duplicate key values, delete the older rows and re-enable the constraint. YTTRI Lisa lisa.yttri To: Multiple recipients of list ORACLE-L @cnh.com[EMAIL PROTECTED] Sent by: rootcc: Subject: SQL Loader questions 10/24/2001 12:05 PM Please respond to ORACLE-L Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAID for a development box
use the girth -- I'd love to pretend that I knew what this means but someone might call me on it. What does it mean? Each drive is 36G. Christopher Spence To: Multiple recipients of list ORACLE-L cspence [EMAIL PROTECTED] @FuelSpot.comcc: Subject: RE: RAID for a development box Sent by: root 10/24/2001 12:25 PM Please respond to ORACLE-L On my NT Quad Development box I have 9x2 Raid 1 for OS/Oracle Files. Then Raid 0+1 for the 6 drives for data. It isn't perfect, but works good. If you do raid 0 with 6 drives, make sure you look into a good stripe size so you actually use the girth. Otherwise 0+1,1+0 may be a better option. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, October 24, 2001 10:30 AM To: Multiple recipients of list ORACLE-L We're setting up a development box that will have a number of instances on it. We won't need any backup, since we can easily re-create the databases from testing instances. I'm planning on implementing the disk storage as RAID0 - a single logical volume stripped across all the drives (6), with the stripe size set to the OS (Win2K) block size. We will only have 4 or 5 developers on the box at any one time and I want to maximize IO utilization. Any heads up here? -- 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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: to find who is waiting for lock
Title: RE: to find who is waiting for lock DBA_WAITERS A table which gives information about sessions holding the lock and sessions waiting to lock the same object. rgds amar -Original Message- From: Tatireddy, Shrinivas (MED, Keane) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 16, 2001 4:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: to find who is waiting for lock Thanq samir, this will help me upto some extent, but not whole. May I have a query to get, what transactions are being peformed (mainly inserts/updts , rather i can say inserted but not commited/updated but not commited) on a table? SRinvias -Original Message- Sent: Tuesday, October 16, 2001 8:19 AM To: '[EMAIL PROTECTED]' Cc: Tatireddy, Shrinivas (MED, Keane) Shrinivas, Use the following query first to find the table name that corresponds to the particular object_id : select xidusn, object_id, session_id, locked_mode from v$locked_object; After getting the object_id from the above query, u may get the object_name holding the lock with the following query : select object_name from dba_objects where object_id = 'x'; The enqueue mechanism of the Oracle server keeps track of the users waiting for locks held by other users, the lock modes these users acquire and the order in which users requested the block. If three users want to update the same row at the same time, all of them get the shared table lock but only the first one gets the row lock. The table locking mechanism keeps track of who holds the lock and who waits for it. You can increase the number of locks available for an instance by increasing the parameters DML_LOCKS and ENQUEUE_RESOURCES. Hope this helps. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 16 October 2001 12:10 To: Multiple recipients of list ORACLE-L Hi dba's is there any table/view/query to find out who is waiting to lock a table, that is already locked by somebody? thnx in adv, srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sequence pool
Here's a kludged-together work-around. After you do your rare pool requests, drop the sequence and recreate it starting at MAX(ID)+1. There is no easier way to reset the sequence number. Marin Dimitrov To: Multiple recipients of list ORACLE-L marin.dimitr[EMAIL PROTECTED] ov cc: @sirma.bg Subject: Re: sequence pool Sent by: root 10/24/2001 12:40 PM Please respond to ORACLE-L - Original Message - To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 18:33 Why not have the sequence increment by 20 (or something to your liking) and then in the application use the number from the sequence and the 19 numbers the sequence will skip. because the pool request will be rather rare, the rest of the time the IDs will be generated one by one what I thought of is having two sequences and split the range of possible IDs between them (i.e. the first half is handled by one-by-one sequence and the second half is handled by the pool sequence) but this seems quite lame (the relative order of IDs of the rows will be ruined) Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Help with passwords
Here is a script I use all the time to run jobs for other users. It saves the current
password, sets the password to temp, connects to user, and sets it back to original
setting. Now you are logged in as the new user but their password was only changed
for less than a second. The script also uses an idea I borrowed from Steve Adam's
Ixora site to save and restore the sqlplus settings. (Thanks Steve).
Cut Here
@save_sqlplus_settings
set termout off echo off pause off
REM connect_as.sql
REM If you are currently connected as a user with 'alter any user'
REM privilege, this will connect you as any other user.
REM usage: @connect_as new_user_id
REM 11/07/1998 - John Carlson
REM 11/16/2000 - John Carlson (Oracle V8.1.6)
REM Added save and restore settings scripts which use new
REM sqlplus 'store set' command.
whenever sqlerror exit sql.sqlerror
col password NEW_VALUE save_pass
define new_user=1
SELECT password
FROM dba_users
WHERE username=upper('new_user');
whenever sqlerror continue
ALTER user new_user identified by temp;
CONNECT new_user/temp
ALTER user new_user identified by values 'save_pass';
undef 1
undef save_pass
undef new_user
set termout on
show user
@restore_sqlplus_settings
Cut Here
HTH,
John
[EMAIL PROTECTED] 10/24/01 07:10AM
Hi all,
I've got a little question, which i can't find simply on metalink.
I've got one user with a not know password and i want to temporary change
it;s password.
I know it is possible to save the hex-key in dba_users.
But how can i change it back to that hex-key?
I need temporary this user account and can change the password, but i want
to change it back to the original.
Thx anyway,
Marco
Marco Alink
Systeem- en databasebeheerder, Centrum voor Informatievoorziening,
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ
--
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: John Carlson
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Please help! Recover a database on another server
Ron, The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? I have a feeling that either you placed all the tablespaces in hot backup mode simultaneously (maybe you created a mirror, broke it and backed up the mirror) or the backups took a long time to complete and thus the redo records that recorded the fact that the tablespaces were taken out of backup mode did not find their way into the archive logs applied so far. I deduce this from the fact that you applied the archive log that was created a few seconds after the hot backup was run, BUT not the ones that were generated sometime after the hot backup completed. Unfortunately, it is the latter that contains the redo for the SYSTEM tablespace changes that occur when the hot backup completes and thus you will need to apply that last archive log... You will need to determine the archive log that was generated after the hot backup completed and re-apply it. You can do this even if you aborted the recovery process, as long as you did not disturb any other file in the recovery environment. Hope this explains! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Listen to great commercial-free christian music 24x7 at www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
Try ' recover database until time '2000/10/24 12:00:00' using backup controlfile;' That should eliminate this problem. Regards, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 12:40 PM We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Loader questions
what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa [EMAIL PROTECTED] wrote: Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Weirdness
Oracle 8.1.7.1 on HPUX 11 I have a table that I have just indexed every column. This has improved the query performance however, its going to slow down the load. Thing that has me confused is that I tried this as an IOT and it actually hurt performance. This table is joined to another table 3xs. The table I made an IOT has 2 million rows and the other table has 6 million rows. Shouldn't the IOT table have had similar performance to having every column indexed? Kimberly Smith GMD Fujitsu Database Administrator (503) 669-6050 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Please help! Recover a database on another server
the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Loader questions
That's it folks. We can all go home. Rachel says I'm not an expert. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, October 24, 2001 2:00 PM To: Multiple recipients of list ORACLE-L what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa [EMAIL PROTECTED] wrote: Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
* Oracle Financials DBA needed in Maine..
Come work for this R D Company in beautiful Coastal Maine that is search of an ERP Oracle DBA to join it's I.T. staff. This company is near the ocean, mountains, forrests, lakes, and trails in a very low cost of living area. If you love high tech challenges but you're looking for a more natural environment, this could be the opportunity you've been searching for. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. *Responsibilities: Include the installation and administration of Oracle databases and database products in concert with software engineers and system administrators. *Requirements: - B.S. in C.S. or the equivalent - At least 2 years of experience as an Oracle DBA on Unix - ERP experience..Oracle Financials highly desired - Excellent communication and teamwork skills - The ability to operate effectively in a fast-paced environment are essential - U.S. citizens or permanent residents only This position also offers: * Opportunity to become a key member of the I.T. team * Baws salary up to 80K + excellent benefits + possible sign on bonus * Excellent relocation package For immediate consideration, please send your resume as an attachment to: Bill Law, Oracle Placement Specialist OraStaff, Inc. Ph: 1-800-549-8502-Please do not call if you need sponsorship Email: [EMAIL PROTECTED] Please use job code: One/Maine//DBA/Page Note: This is only one of the many opportunities that we have available across the U.S. for candidates with Oracle skills who are U.S. citizens or permanent residents. So if this one is not a match for you, we invite you to send us your resume- as we quite possibly have the opportunity that you are seeking. We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law List Server Managed Maintained by BiJTek Solutions www.bijtek.com IT Consulting * Web Hosting * Outsourcing List Server Managed Maintained by BiJTek Solutions www.bijtek.com IT Consulting * Web Hosting * Outsourcing -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
This is driving me nuts. What am I doing wrong here?
SQL declare 2 w_a number := 0; 3 w_b number:= 0; 4 w_c char(10) := null; 5 w_d number := 0; 6 cursor v_c_t is 7 select a,c,b from civ_test; 8 begin 9 for v_c_t_row in v_c_t loop 10 :w_d := w_d + 1; 11 :w_a := 0; 12 :w_c := null: 13 :w_b := 0; 14 :w_a := select c from civ_test where v_c_t_row.a = wd; 15 :w_c := 'v-'||v_c_t_row.c; 16 :w_b := v_c_t_row.b; 17 insert into v_civ_test values (w_c, w_b, w_a, w_d); 18 end loop 19 end; 20 / Bind variable W_B not declared. SQL -- 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).
v$tempstat question
I've been trying to track down the source of high 'direct path read' and 'direct path write' waits. It appears the waits are related to locally managed temporary tablespaces. However, I'm puzzled by what is reported in v$tempstat. SQL select FILE#, READTIM, WRITETIM, AVGIOTIM, LSTIOTIM, MINIOTIM, MAXIORTM, MAXIOWTM from v$tempstat; FILE# READTIM WRITETIM AVGIOTIM LSTIOTIM MINIOTIM MAXIORTM MAXIOWTM - --- -- -- -- 1 3001 0 6393 78 2 2 252 443 636 0 1387188 How could the values for MAXIORTM (maximum time spent doing a single read) be substatially greater than those reported for READTIM (time spent doing all reads)? This situtation persists even after all of the users have logged out of the database, so it doesn't appear to be a case of the read having not yet completed. FYI: We're running Oracle 8.1.6.3 on Sun Solaris. -Ed __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Bittel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Code to Validate email addresses
Title: RE: Code to Validate email addresses
I'd like to make a suggestion for a different approach. I'm thinking of something like a reverse check of the address. Check out this web service (http://beta2.eraserver.net/webservices/mxchecker/) that accepts an email address for validation. It returns values in different formats (string, XML, etc.) I tried it the XML query and it returned the following:
?xml version=1.0 encoding=utf-8 ?
boolean xmlns=http://webservices.eraserver.net/MXCheckertrue/boolean
You can process this in PL/SQL with or without the XML development kit. If you can resolve web proxy issues with your networking folks you can use UTL_HTTP.REQUEST to call such a service. Here is a sample I ran:
Command:
select UTL_HTTP.REQUEST('http://webservices.eraserver.net/mxchecker/mxchecker.asmx/CheckEmail?accessCode=&[EMAIL PROTECTED]') from dual;
Resulting string:
?xml version=1.0 encoding=utf-8?
string xmlns=http://webservices.eraserver.net/MXCheckerOK/string
There are many such public services and some private ones with bells and whistles (availability, guarantees, encryption, etc.)
HTH
Tony Aponte
On Wednesday 24 October 2001 06:15, Oweson Flynn wrote:
Hi,
We have implemented a form that emails reports. The user has to enter his
email address.
However, we are finding that a lot of users are entering incorrect and
invalid email addresses.
Obviously, we can do nothing if they enter an incorrect (but syntactically
correct) email address.
What I am looking for is:
1) A definition of the syntactically correct format of an email address
(from some sort of authoritative source)
2) Hopefully some pl/sql code that will validate a sting to see if it is a
valid email address.
If anyone can assist, I would appreciate it!
Regards
Oweson Flynn
_
Tell me what you think, Captain, I'm all ears - Spock
Certified Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: [EMAIL PROTECTED]
***
This message may contain information which is confidential and subject to
legal privilege. If you are not the intended recipient, you may not peruse,
use, disseminate, distribute or copy this message. If you have received
this message in error, please notify the sender immediately by email,
facsimile or telephone and return and/or destroy the original message.
***
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: providing 24*7 database ---
Title: RE: providing 24*7 database --- Narender hasn't replied yet. But the sample I sent only uses local indexes since there is only one partition. I did see that Jim Conboy pointed out that I missed the 'including indexes' option. Tony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 12:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: providing 24*7 database --- Tony, If the partitioned indexes have a locality of global (not local), then they become invalid after activity on the underlying table partitions. Does Narender say whether his indexes are global or local? Cherie Machler Aponte, Tony [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] et cc: Sent by: Subject: RE: providing 24*7 database --- [EMAIL PROTECTED] om 10/24/01 10:25 AM Please respond to ORACLE-L I couldn't verify that the non-partitioned indexes become unusable after exchanging the partition for the normal table. In the sample I posted I snipped the output of the queries on USER_INDEXES and USER_PART_INDEXES, but my tests showed that they remain valid. I'm curious to see why your results are different. Can you post the spooled output of your test? Thanks. Tony Aponte -Original Message- From: Narender Akula [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 23, 2001 10:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: providing 24*7 database --- Thanks all for the input. hi tony , Quick question ... when you exchange partititons with non partitioned table data , all indexes on non partitioned tables become unusable status right. do have to rebuild them after every exchnage... naren -Original Message- From: Aponte, Tony [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 23 October 2001 05:06 To: Multiple recipients of list ORACLE-L Subject: RE: providing 24*7 database --- We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first: There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition. Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he published objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITION TABLE tablename. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the published tables. HTH Tony Aponte ** pseudo-attachment ** SQL create table x(x1 number,x2 varchar2(50)); Table created. SQL create index xi1 on x(x1); Index created. SQL create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue)); Table created. SQL create index yi1 on y(x1) 2 local (partition yi1 ); Index created. SQL insert into x values (1,'original data from regular table'); 1 row created. SQL insert into y values (2,'original data from partitioned table'); 1 row created. SQL commit; Commit complete. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 2 original data from partitioned table SQL select * from y; X1 X2 -- -- 1 original data from regular
RE: This is driving me nuts. What am I doing wrong here?
Do you need a space when declaring the variable, w_b number := 0? -Original Message- [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 2:31 PM To: Multiple recipients of list ORACLE-L SQL declare 2 w_a number := 0; 3 w_b number:= 0; 4 w_c char(10) := null; 5 w_d number := 0; 6 cursor v_c_t is 7 select a,c,b from civ_test; 8 begin 9 for v_c_t_row in v_c_t loop 10 :w_d := w_d + 1; 11 :w_a := 0; 12 :w_c := null: 13 :w_b := 0; 14 :w_a := select c from civ_test where v_c_t_row.a = wd; 15 :w_c := 'v-'||v_c_t_row.c; 16 :w_b := v_c_t_row.b; 17 insert into v_civ_test values (w_c, w_b, w_a, w_d); 18 end loop 19 end; 20 / Bind variable W_B not declared. SQL -- 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: eric harrington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: This is driving me nuts. What am I doing wrong here?
Remove the colons from the assignment statements. SQL declare 2 w_a number := 0; 3 w_b number:= 0; 4 w_c char(10) := null; 5 w_d number := 0; 6 cursor v_c_t is 7 select a,c,b from civ_test; 8 begin 9 for v_c_t_row in v_c_t loop 10 w_d := w_d + 1; 11 w_a := 0; 12 w_c := null: 13 w_b := 0; 14 w_a := select c from civ_test where v_c_t_row.a = wd; Not sure about the above, but you could: select c into w_a from civ_test where v_c_t_row.a = wd; 15 w_c := 'v-'||v_c_t_row.c; 16 w_b := v_c_t_row.b; 17 insert into v_civ_test values (w_c, w_b, w_a, w_d); 18 end loop 19 end; 20 / hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, October 24, 2001 2:31 PM To: Multiple recipients of list ORACLE-L SQL declare 2 w_a number := 0; 3 w_b number:= 0; 4 w_c char(10) := null; 5 w_d number := 0; 6 cursor v_c_t is 7 select a,c,b from civ_test; 8 begin 9 for v_c_t_row in v_c_t loop 10 :w_d := w_d + 1; 11 :w_a := 0;== change to w_a := 0; 12 :w_c := null: 13 :w_b := 0; 14 :w_a := select c from civ_test where v_c_t_row.a = wd; Not sure about the above, but you could: select c into w_a from civ_test where v_c_t_row.a = wd; 15 :w_c := 'v-'||v_c_t_row.c; 16 :w_b := v_c_t_row.b; 17 insert into v_civ_test values (w_c, w_b, w_a, w_d); 18 end loop 19 end; 20 / Bind variable W_B not declared. SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: This is driving me nuts. What am I doing wrong here?
change :w_b to w_b, :w_c to w_c and so on. you do not need to user :(colon) to assign values Rakesh [EMAIL PROTECTED] 10/24/01 02:31PM SQL declare 2 w_a number := 0; 3 w_b number:= 0; 4 w_c char(10) := null; 5 w_d number := 0; 6 cursor v_c_t is 7 select a,c,b from civ_test; 8 begin 9 for v_c_t_row in v_c_t loop 10 :w_d := w_d + 1; 11 :w_a := 0; 12 :w_c := null: 13 :w_b := 0; 14 :w_a := select c from civ_test where v_c_t_row.a = wd; 15 :w_c := 'v-'||v_c_t_row.c; 16 :w_b := v_c_t_row.b; 17 insert into v_civ_test values (w_c, w_b, w_a, w_d); 18 end loop 19 end; 20 / Bind variable W_B not declared. SQL -- 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: Rakesh Gupta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
protocol adapter error ???
Hi, I installed 815 client and server on NT 4.0. When I use svrmgrl to connect to the database, I got 12560 protocol adapter error. I already commented out all the entries in sqlnet.ora file. What should I do ??? Thank you! Janet __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW: ADO.NET and Oracle 8.1.6
Title: FW: ADO.NET and Oracle 8.1.6 (p.s. tday6 - please ignore my previous message, I hit the send button too quickly.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Are the ODBC driver and SQL*Plus using the same tnsnames.ora file? Yes, as far as I can see. I'm not sure where the MS driver gets its path to the TNSNAMES.ORA. We were able to connect using an earlier version of ADO. Errors of the kind you describe (able to connect with SQL*Plus but not another client) often resolve to misspelling of the alias or the use of a different tnsnames.ora file, in my experience. Search for the tnsnames.ora and sqlnet.ora files follows the following steps IIRC: a) look in current directory b) look in directory specified by TNS_ADMIN environment variable (if that variable is set) c) look in %ORACLE_HOME%/network/admin I would do a search on the client machine to find all tnsnames.ora files. In a multiple Oracle homes situation, I usually set the TNS_ADMIN environment variable and have the tnsnames.ora file in only one location (or, if you don't want to set the TNS_ADMIN environment variable, have one tnsnames.ora file for each oracle_home, and use the ifile= option to include the entries from another centralized file - make sure the ifile parameter includes the full path to your centralized file.) In any case, try and reduce the number of tnsnames.ora files you have on the client, as much as possible. Once you do that, make sure you can connect from SQL*Plus with username/password@tns_alias Then try ODBC. If ODBC fails, check the ODBC DNS entry. If it still fails, turn on Net8 tracing in the sqlnet.ora configuaration file (sqlnet.ora will be in the same directory as tnsnames.ora): trace_directory_client = c:\mydir trace_file_client = my_file trace_level_client = admin (can be one of off, user, admin, support) Try the ODBC connection, then turn off tracing (otherwise the trace file will become huge) and go look in it for any errors. The trace file will have a lot of information, but you can at least use it to see if Net8 found your tnsnames.ora file, and what entry it grabbed from the tnsnames.ora file.
[Q] what difference between count(0), count(1) and count(*)
Can anyone tell me what is difference between : select count(*) ... from .. select count(0) ... select coun (1) ... select count(2) ... Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: v$sqlarea statistics
Binay,
from what i understand, these type of block reads
relate to read consistancy .. meaning that if oracle
wants to read block x but it finds that it is dirty ,
it reads from the rollback segments to give you the
point in time snapshot as it existed at the time when
you had first started the query.
e.g. u fire query at 12.00 and if at 12.05, someone
changes a block and commits. Now at 12.10 your query
is still executing and it requests the above block,
oracle will attempt to read it from the rbs , therby
resulting in a consistent read.
a current mode read on the other is a direct read off
of the buffer cache.
correct me if i am wrong here guys?
Thx
Deepak
--- [EMAIL PROTECTED] wrote:
Hi Deepak,
Can you elaborate what is consistent gets
-
Eric,
here's a high level from my understanding on this
issue:
Buffer gets {also called Logical IO's}
These happen as oracle scans blocks of data in the
buffercache(in-mem scans). Many people believe that
since these are memory reads, they are inexpensive.
I
have seen the contrary in many cases and have seen
that these are the ones which take the most cpu
clycles, therby making your system CPU Bound.
Resolution of this is to tune your sql by having it
use better access paths (indexes). Also consider
de-norming in ordr to avoid too many joins
I consider this the most important metrics in
identifying bad SQL. i have seen cases where
frequently executed queries were performing millions
of LIO's and hosing up the CPU. A simple index / or
Adding hint can reduce this number by a very high
factor resulting in great gains.
DiskReads {also called physical IO's)
This obviously means that there are a lot of disk
reads required to satisfy your query. Reasons: maybe
you are using ineffcient access paths/bad sql or u
just have insuffient (small) memory to support your
app. High Diskreads is the reason that makes your
system IO bound. Resolution is again the same as
described above. In addition, one of the assumtions
here is that you have spread your datafiles/logs/cf
optimally. Also consider using the recycle buffer
pool
feature to avoid an innocent FTS from flushing
everything from your cache. Obviously you cannot
always prevent any of these and some disk read are
inevitable.
hth
Deepak:
--- Erik Williams [EMAIL PROTECTED] wrote:
I am trying to identify the most harmful
statements
in an application. From
the Oracle Performance and Tuning Tips and
Techniques book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number
of
buffer gets and the
other looks at the statements with a high number
of
disk reads. Some of the
statements appear in both lists, but some in only
one. If all of the disk
reads are moving blocks into the buffer cache,
what
is the difference
between the two measures? Can anyone explain the
difference between the two
measures?
Thanks.
Erik
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
---
The contents of this e-mail are confidential to the
ordinary user
of the e-mail address to which it was addressed and
may also be
privileged. If you are not the addressee of this
e-mail you should
not copy, forward, disclose or otherwise use it or
any part of it
in any form whatsoever. If you have received this
e-mail in error
please notify us by
Re: Please help! Recover a database on another server
why does he need a backup control file? this appears to be a straightforward case of moving databases(i mean restoring to a new host). does not look like they have done structural changes or things like that. ron do you use rman? if not then someone earlier suggested taking the datafiles off of the backup mode using the end backup command. see if that works for you. Deepak --- Rachel Carmichael [EMAIL PROTECTED] wrote: the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: [Q] what difference between count(0), count(1) and count(*)
i think count(*) gives count of all columns .. where as count(col1) gives count for col1 ignoring nulls in col1 Deepak --- aaa aaa [EMAIL PROTECTED] wrote: Can anyone tell me what is difference between : select count(*) ... from .. select count(0) ... select coun (1) ... select count(2) ... Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 8.1.5 on NT run very slow ??
aaa aaa, ( if that's your real name :) Considering that your page faults are very high, take a look at the sizes of db_block_buffers, db_block_size, and shared_pool_size for starters. 512 Meg of RAM isn't exactly an overabundance, but will perform especially badly if you've allocated all of it ( or more ) to the shared_pool and/or buffer cache. Jared aaa aaa mccdba@hotmai To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] l.com cc: Sent by: Subject: ORACLE 8.1.5 on NT run very slow ?? [EMAIL PROTECTED] om 10/24/01 09:25 AM Please respond to ORACLE-L We have HP server with NT 4.0 installed. This server dedicate for ORACLE use. The ORACLE version is 8.1.5 (no patch installed). we found the performance NOT really good. I turn on the performance monitor and found the memory page fault and memory pages/sec are very high while SQL statement running (even only one user running). The Server have following configurations: HP LH 3000 2 CPU 500 Mhz 512 MB RAM 1200 swap space on C: ORACLE software and NT on C: (9GB, 7200RPM??) ORACLE data on d: (8 X 9GB RAID 5, 7200RPM??) ORACLE SGA 250MB Can anyone give me some hints why: 1. page fault very high? 2. SQL statement run very slow (I turn on TKPROF to trace and found it is NOT ORACLE SQL statement inefficient problem)? Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Please help! Recover a database on another server
The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
Are you sure, if you don't state 'using backup controlfile' it will use the control file you usd to startup the database. Just a thot, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 3:46 PM The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 partitioned indexes
Title: RE: analyze partitioned indexes
-Original Message-
From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes.
...
- with the command analyze table compute statistics for table for all indexes all table and index partitions are analyzed
- with the command analyze index compute statistics all index partitions are analyzed
- with the command analyze index partition () compute statistics only the single index partition is analyzed
Proof:
I created a partitioned table (my_table) with a global index, a locally partitioned index, and a globally partitioned index.
I also created a view (my_view) that shows the analyze date and num_rows for the table and its indexes and partitions.
(see end of e-mail for table and view creation)
SQL analyze table my_table delete statistics ;
Table analysée.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL analyze table my_table compute statistics for table for all indexes ;
Table analysée.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1 2001/10/24 12:08:28 0
MY_INDEX2 2001/10/24 12:08:28 0
MY_INDEX2 (MY_INDEX2_P1) 2001/10/24 12:08:28 0
MY_INDEX2 (MY_INDEX2_P2) 2001/10/24 12:08:28 0
MY_INDEX3 2001/10/24 12:08:28 0
MY_INDEX3 (MY_INDEX3_P1) 2001/10/24 12:08:28 0
MY_INDEX3 (MY_INDEX3_P2) 2001/10/24 12:08:28 0
MY_TABLE 2001/10/24 12:08:28 0
MY_TABLE (MY_TABLE_P1) 2001/10/24 12:08:28 0
MY_TABLE (MY_TABLE_P2) 2001/10/24 12:08:28 0
SQL analyze table my_table delete statistics ;
Table analysée.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL analyze index my_index3 compute statistics ;
Index analysé.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3 2001/10/24 12:08:57 0
MY_INDEX3 (MY_INDEX3_P1) 2001/10/24 12:08:57 0
MY_INDEX3 (MY_INDEX3_P2) 2001/10/24 12:08:57 0
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL analyze index my_index3 delete statistics ;
Index analysé.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL analyze index my_index2 partition (my_index2_p2) compute statistics ;
Index analysé.
SQL select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
-- --
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2) 2001/10/24 12:09:42 0
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
10 ligne(s) sélectionnée(s).
-- partitioned table
create table my_table
(my_column1 char (1),
my_column2 date,
my_column3 varchar2 (4),
my_column4 raw (4)
)
partition by range (my_column1)
(partition my_table_p1 values less than ('M'),
partition my_table_p2 values less than (maxvalue)
) ;
-- index
create index my_index1 on my_table (my_column2) ;
-- partitioned index (local)
create bitmap index my_index2 on my_table (my_column3)
local (partition my_index2_p1, partition my_index2_p2) ;
-- partitioned index (global)
create index my_index3 on my_table (my_column4)
global partition by range (my_column4)
(partition my_index3_p1 values less than ('AB'),
partition my_index3_p2 values less than (maxvalue)
) ;
create view my_view as
select
table_name as object_name,
to_char (last_analyzed, 'S/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_tables
where
table_name = 'MY_TABLE'
union
select
table_name || ' (' || partition_name || ')' as object_name,
to_char (last_analyzed, 'S/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_tab_partitions
where
table_name = 'MY_TABLE'
union
select
index_name as object_name,
to_char (last_analyzed, 'S/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_indexes
where
index_name like 'MY\_INDEX%' escape '\'
union
select
index_name || ' (' || partition_name || ')' as object_name,
to_char (last_analyzed, 'S/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
RE: RAID for a development box
If you have a Raid 0 of 6 drives and set a strip size to be 16K, then the girth (or commonly referred to as stripe width) is 16k x 6 or 96K, if you write less than 96K you will only use some of the drives. With a write-back caching controller it can hold writes to speed transactions but also to be more efficient with the writes. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, October 24, 2001 1:17 PM To: Multiple recipients of list ORACLE-L use the girth -- I'd love to pretend that I knew what this means but someone might call me on it. What does it mean? Each drive is 36G. Christopher Spence To: Multiple recipients of list ORACLE-L cspence [EMAIL PROTECTED] @FuelSpot.comcc: Subject: RE: RAID for a development box Sent by: root 10/24/2001 12:25 PM Please respond to ORACLE-L On my NT Quad Development box I have 9x2 Raid 1 for OS/Oracle Files. Then Raid 0+1 for the 6 drives for data. It isn't perfect, but works good. If you do raid 0 with 6 drives, make sure you look into a good stripe size so you actually use the girth. Otherwise 0+1,1+0 may be a better option. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, October 24, 2001 10:30 AM To: Multiple recipients of list ORACLE-L We're setting up a development box that will have a number of instances on it. We won't need any backup, since we can easily re-create the databases from testing instances. I'm planning on implementing the disk storage as RAID0 - a single logical volume stripped across all the drives (6), with the stripe size set to the OS (Win2K) block size. We will only have 4 or 5 developers on the box at any one time and I want to maximize IO utilization. Any heads up here? -- 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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: SQL Loader questions
Thanks to all for reconfirming this for me. I had never heard of SQL Loader doing these things, but couldn't quite convince the programmer. What I have since found out is that once upon a time, a consultant came in and set up the job that loads their data - and left without documenting it. Lo and behold, there's a lot of other stuff the script is doing besides running SQL Loader. Thanks again for all your responses. Lisa :D -Original Message- Sent: Wednesday, October 24, 2001 1:00 PM To: Multiple recipients of list ORACLE-L what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa [EMAIL PROTECTED] wrote: Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: protocol adapter error ???
Title: RE: protocol adapter error ??? -Original Message- From: Janet Linsy [mailto:[EMAIL PROTECTED]] I installed 815 client and server on NT 4.0. When I use svrmgrl to connect to the database, I got 12560 protocol adapter error. I already commented out all the entries in sqlnet.ora file. What should I do ??? Thank you! Have you set a value for ORACLE_SID? Is there a service running for the database?
RE: Please help! Recover a database on another server
try to recreate a control file!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -Mensaje original- De: Ruth Gramolini [SMTP:[EMAIL PROTECTED]] Enviado el: Miércoles 24 de Octubre de 2001 03:06 PM Para: Multiple recipients of list ORACLE-L Asunto: Re: Please help! Recover a database on another server Are you sure, if you don't state 'using backup controlfile' it will use the control file you usd to startup the database. Just a thot, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 3:46 PM The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
Aren't they the same? If I restore the control file from the backup. There was no control file on the server before I did the restore. Ron -Original Message- Sent: Wednesday, October 24, 2001 3:06 PM To: Multiple recipients of list ORACLE-L Are you sure, if you don't state 'using backup controlfile' it will use the control file you usd to startup the database. Just a thot, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 3:46 PM The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).
RE: analyze partitioned indexes
If you analyze all of the partitions in an index (one partition at a time) is the performance of the the end result the same as it would be if you just analyzed the entire index at one time (not partition by partition). Thanks, Cherie Jacques Kilchoer Jacques.Kilchoer@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] quest.com cc: Sent by: Subject: RE: analyze partitioned indexes [EMAIL PROTECTED] 10/24/01 03:15 PM Please respond to ORACLE-L -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]] I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes. ... - with the command analyze table compute statistics for table for all indexes all table and index partitions are analyzed - with the command analyze index compute statistics all index partitions are analyzed - with the command analyze index partition () compute statistics only the single index partition is analyzed Proof: I created a partitioned table (my_table) with a global index, a locally partitioned index, and a globally partitioned index. I also created a view (my_view) that shows the analyze date and num_rows for the table and its indexes and partitions. (see end of e-mail for table and view creation) SQL analyze table my_table delete statistics ; Table analysée. SQL select * from my_view ; OBJECT_NAMEANALYZED NUM_ROWS -- -- MY_INDEX1 MY_INDEX2 MY_INDEX2 (MY_INDEX2_P1) MY_INDEX2 (MY_INDEX2_P2) MY_INDEX3 MY_INDEX3 (MY_INDEX3_P1) MY_INDEX3 (MY_INDEX3_P2) MY_TABLE MY_TABLE (MY_TABLE_P1) MY_TABLE (MY_TABLE_P2) SQL analyze table my_table compute statistics for table for all indexes ; Table analysée. SQL select * from my_view ; OBJECT_NAMEANALYZED NUM_ROWS -- -- MY_INDEX1 2001/10/24 12:08:28 0 MY_INDEX2 2001/10/24 12:08:28 0 MY_INDEX2 (MY_INDEX2_P1)2001/10/24 12:08:28 0 MY_INDEX2 (MY_INDEX2_P2)2001/10/24 12:08:28 0 MY_INDEX3 2001/10/24 12:08:28 0 MY_INDEX3 (MY_INDEX3_P1)2001/10/24 12:08:28 0 MY_INDEX3 (MY_INDEX3_P2)2001/10/24 12:08:28 0 MY_TABLE2001/10/24 12:08:28 0 MY_TABLE (MY_TABLE_P1) 2001/10/24 12:08:28 0 MY_TABLE (MY_TABLE_P2) 2001/10/24 12:08:28 0 SQL analyze table my_table delete statistics ; Table analysée. SQL select * from my_view ; OBJECT_NAMEANALYZED NUM_ROWS -- -- MY_INDEX1 MY_INDEX2 MY_INDEX2 (MY_INDEX2_P1) MY_INDEX2 (MY_INDEX2_P2) MY_INDEX3 MY_INDEX3 (MY_INDEX3_P1) MY_INDEX3 (MY_INDEX3_P2) MY_TABLE MY_TABLE (MY_TABLE_P1) MY_TABLE (MY_TABLE_P2) SQL analyze index my_index3 compute statistics ; Index analysé. SQL select * from my_view ; OBJECT_NAMEANALYZED NUM_ROWS -- -- MY_INDEX1 MY_INDEX2 MY_INDEX2 (MY_INDEX2_P1) MY_INDEX2 (MY_INDEX2_P2) MY_INDEX3 2001/10/24 12:08:57 0 MY_INDEX3 (MY_INDEX3_P1)2001/10/24 12:08:57 0 MY_INDEX3 (MY_INDEX3_P2)2001/10/24 12:08:57 0 MY_TABLE MY_TABLE (MY_TABLE_P1) MY_TABLE (MY_TABLE_P2) SQL analyze index my_index3 delete statistics ; Index analysé. SQL select * from my_view ; OBJECT_NAMEANALYZED
RE: RAID for a development box
Thanks. Now all I have to do is coach our SA past the HP auto-config. -- 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).
BLOB ???
Hi, all I have a table that has a column with BLOB data type. When I do a select I got: SP2-0678: Column or attribute type can not be displayed by SQL*Plus And I don't know how the data got inserted there. Could anyone let me know how to insert and display BLOB contents. Thank you! Andrea __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
it depends on how you created the control file that was on the backup. If you just copied the control file from disk, that would explain your problem. If you did a alter system backup controlfile to file and then backed that one up and moved it to the other server and then did a recover database until something using backup controlfile then what you did should have worked. Rachel --- Smith, Ron L. [EMAIL PROTECTED] wrote: Aren't they the same? If I restore the control file from the backup. There was no control file on the server before I did the restore. Ron -Original Message- Sent: Wednesday, October 24, 2001 3:06 PM To: Multiple recipients of list ORACLE-L Are you sure, if you don't state 'using backup controlfile' it will use the control file you usd to startup the database. Just a thot, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 3:46 PM The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
Re: BLOB ???
Andrea - That's a standard message when you perform a select * against a table with a BLOB column, or explicitly select the BLOB column via SQL*Plus. You say that you don't know how data got into the table - are you sure that data is actually in the BLOB column? You can use the DBMS_LOB.GETLENGTH() function to see if there is really any data in the rows. Brian Andrea Oracle wrote: Hi, all I have a table that has a column with BLOB data type. When I do a select I got: SP2-0678: Column or attribute type can not be displayed by SQL*Plus And I don't know how the data got inserted there. Could anyone let me know how to insert and display BLOB contents. Thank you! Andrea __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian McGraw INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Can I add redo log group diffrent from
Hi My redo log group# is 5,6,7.If I want to add more redo log group then Can I add group#9? As far I know there will be not impact on Database but let me know group view please. Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BLOB ???
Title: RE: BLOB ??? Look at the documentation for the Supplied PL/SQL Package Reference... Read the information about DBMS_LOB... That should get you started... Tim -Original Message- From: Andrea Oracle [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 5:16 PM To: Multiple recipients of list ORACLE-L Subject: BLOB ??? Hi, all I have a table that has a column with BLOB data type. When I do a select I got: SP2-0678: Column or attribute type can not be displayed by SQL*Plus And I don't know how the data got inserted there. Could anyone let me know how to insert and display BLOB contents. Thank you! Andrea __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Weirdness
Title: RE: Weirdness First off... Did you place a separate index on every column or one index that included every column? Second, either of those is methods is probably overkill... The real questions you have to ask are... Have you determined the execution plan of the statement yet? What was it with the IOT? What was it with your indexing solution? What columns are joined in the query you are talking about? What was the definition of the IOT you created? What was the definition of the segments in the Table with Index method? You need to figure out what the query needs to accomplish and either rewrite the query to be more efficient or place the proper index or indexes to improve your performance... HTH Tim -Original Message- From: Kimberly Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Weirdness Oracle 8.1.7.1 on HPUX 11 I have a table that I have just indexed every column. This has improved the query performance however, its going to slow down the load. Thing that has me confused is that I tried this as an IOT and it actually hurt performance. This table is joined to another table 3xs. The table I made an IOT has 2 million rows and the other table has 6 million rows. Shouldn't the IOT table have had similar performance to having every column indexed? Kimberly Smith GMD Fujitsu Database Administrator (503) 669-6050 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Speaking the same language
We need to communicate inside a team made of DBAs that support different database platforms: oracle, sql*server, Sybase, adabase (Am I wrong that sql*server and sybase terminology is pretty much the same?). There are some terminology differences between different databases (to start with the word database that means different in oracle and sql*server world). I have tried to compile a start terminology difference list. If you could contribute to the list you can send an e-mail to me directly, I would compile the list and send the compiled version to the list. If you don't hear back from me that probably means that the list was not very responsive :-(. Also, please let me know if you think some terminology is wrong in this list. Thanks. Djordje Jankovic [EMAIL PROTECTED] Oracle SqlServer DB2 DatabaseServer Schema Database Sys schema Master database Tablespace File group SYSTEM tablespace Primary data file other tablespaces Secondary data files Redo log file Log file Database block Page Extent Extent (8 pages - 64K). (multiple of blocks) Can be shared by logical objects. Data dictionary System catalog Data dictionary views System catalog tables dba_objects sysobjects dba_tab_columns syscolumns dba_indexes sysindexes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 8.1.5 on NT run very slow ??
Hi, If you open up task Manager on the server, and swap to the performance tab, how much physical memory is shown as available? While you're on that tab, how much total physical memory does Task Manager report? As per Jared's question, what do the following 2 queries show? select * from v$sga; select name , value from v$parameter where name = 'db_block_size'; Regards, Bruce Reardon -Original Message- Sent: Thursday, 25 October 2001 6:46 aaa aaa, ( if that's your real name :) Considering that your page faults are very high, take a look at the sizes of db_block_buffers, db_block_size, and shared_pool_size for starters. 512 Meg of RAM isn't exactly an overabundance, but will perform especially badly if you've allocated all of it ( or more ) to the shared_pool and/or buffer cache. Jared -- aaa aaa mccdba@hotmai To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] l.com cc: Sent by: Subject: ORACLE 8.1.5 on NT run very slow ?? 10/24/01 09:25 AM We have HP server with NT 4.0 installed. This server dedicate for ORACLE use. The ORACLE version is 8.1.5 (no patch installed). we found the performance NOT really good. I turn on the performance monitor and found the memory page fault and memory pages/sec are very high while SQL statement running (even only one user running). The Server have following configurations: HP LH 3000 2 CPU 500 Mhz 512 MB RAM 1200 swap space on C: ORACLE software and NT on C: (9GB, 7200RPM??) ORACLE data on d: (8 X 9GB RAID 5, 7200RPM??) ORACLE SGA 250MB Can anyone give me some hints why: 1. page fault very high? 2. SQL statement run very slow (I turn on TKPROF to trace and found it is NOT ORACLE SQL statement inefficient problem)? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Please help! Recover a database on another server
especially if you are restoring to a new server then typically i would issue rman -- restore controlfile which would get the backup from tape --- Smith, Ron L. [EMAIL PROTECTED] wrote: Aren't they the same? If I restore the control file from the backup. There was no control file on the server before I did the restore. Ron -Original Message- Sent: Wednesday, October 24, 2001 3:06 PM To: Multiple recipients of list ORACLE-L Are you sure, if you don't state 'using backup controlfile' it will use the control file you usd to startup the database. Just a thot, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 3:46 PM The control file was backed up the same time the data files were backed up. The control file from the backup is what we are using. Ron -Original Message- Sent: Wednesday, October 24, 2001 1:05 PM To: Multiple recipients of list ORACLE-L the key is when did you create the backup controlfile? Try using a backup controlfile as opposed to a copy of the controlfile. yes, there is a difference. --- Smith, Ron L. [EMAIL PROTECTED] wrote: We are trying to recover a database to another server. The backup was a hot backup. The files are all copied to the new server, along with the archive log that was created a few seconds after the hot backup was run. We bring the database up and apply the archive log and Oracle says recovery complete. We then do an alter database open resetlogs. At this point Oracle says the System file needs more recovery. Oracle said the recovery was complete! Why is it saying the system file needs more recovery? Any ideas? Thanks! Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: How to detect transactions being rolled back?
The script included will produce the following output. The key for you would be to watch the #Of Undo Blks Used column. If it's incrementing the updates, etc. are in progress. If it's decreasing, the transaction is being rolled back. Hope this helps. Wed Oct 24 page1 * ROLLBACK SEGMENTS WITH ACTIVE TRANSACTIONS Cur Cur #Of#Of Init Next Opt High Cur Ext# Blk# Ora Undo KB OfUndo RollBack Size Size #Of Size Size Size Used #Of #Of Being Being Ses TransactionStrtStrt Blks UndoRecs NameMeg Meg Ext MegMeg Meg Meg %Used Srk Trn Used Used ID Start_Time Ext#Blk# Used UsedUsed - - - --- -- - - --- --- - - -- --- - -- --- R0110.0 10.0 10 100 100.0 100 1 1 0 1 0 478 171 10/24/01 08:11:26 0 284 148 1184 12206 R0210.0 10.0 10 100 100.0 100 0 0 7 368 R0310.0 10.0 10 100 100.0 100 0 0 1 586 R0410.0 10.0 10 100 100.0 100 0 0 4 709 R0510.0 10.0 10 100 100.0 100 0 0 9 331 R0610.0 10.0 10 100 100.0 100 0 0 4 1083 R0710.0 10.0 10 100 100.0 100 0 0 4 891 R0810.0 10.0 10 100 100.0 100 0 0 0 0 2 502 R0910.0 10.0 10 100 100.0 100 0 0 4 854 R1010.0 10.0 10 100 100.0 100 0 0 7 736 SYSTEM .1.1 615 5 0 036 0 Wed Oct 24 page1 * USERS WITH ACTIVE TRANSACTIONS Oracle PgmOracle Oracle Unix User Unix Unix Session Rollback User Name PidPid ID Serial# TTY#Program Name StatusSegment -- -- -- -- --- --- --- - -- Current SQL Statement -- -- bart marge10373 10374 171 38791 sqlplus@homer (TNS V1-V3)ACTIVER01 SELECT COUNT(*) FROM STG_BOOK_INV WHERE ISBN = :b1 REM START OF FILE === set verify off set pagesize 36 set linesize 132 set pause on set pause 'Hit enter to continue' set feedback off set showmode off set echo off ttitle '* ROLLBACK SEGMENTS WITH ACTIVE TRANSACTIONS ' col owner heading 'Owner' format a6 col segment_nameheading 'RollBack|Name' format a9 col tablespace_name heading 'TableSpace' format a12 col EXTENTS heading '#Of|Ext'format 99 col SM heading 'Size|Meg' format 999 col IE heading 'Init|Size|Meg' format 99.9 col NE heading 'Next|Size|Meg' format 99.9 col OPT heading Opt|Size|Meg format 999.9 col HIGHheading High|Size|Meg format 999 col SHRINKS heading #Of|Srkformat 99 col TRANS heading Cur|#Of|Trnformat 99 col STATUS heading 'Current|Status' format a9 col curext heading 'Cur|Ext#|Being|Used' format 9990 col curblk heading 'Cur|Blk#|Being|Used' format 9990 col ef heading 'Ora|Ses|ID' format 990 col start_time heading 'Transaction|Start_Time' format a18 col start_uext heading 'Strt|Ext#' format 990 col start_ubablkheading 'Strt|Blk#' format 90 col used_ublk heading '#Of|Undo|Blks|Used' format 9990 col used_urec heading '#Of|Undo|Recs|Used' format 90 col SUK heading 'KB Of|Undo|Used' format 0 col SMU heading 'Used|Meg'format 9990 col SMUPheading '%Used' format 999 break on owner on segment_name on tablespace_name on IE on NE on EXTENTS on SM on OPT on HIGH - on SMU on SMUP on SHRINKS on TRANS on curext on curblk select ds.segment_name segment_name, drs.initial_extent / 1048576 IE, drs.next_extent / 1048576 NE, ds.extents EXTENTS, (ds.blocks * (vp.value / 1024)) / 1024 SM, vr.optsize / 1048576 OPT, vr.hwmsize / 1048576 HIGH, (vtss.s_used_ublk * (vp.value / 1024)) / 1024 SMU, (vtss.s_used_ublk / ds.blocks) * 100 SMUP, vr.shrinks SHRINKS, vr.xacts
Re: Can I add redo log group diffrent from
Seema, so long as you dont drop the current log you are fine:) adding logs will have zero impact in terms of db impact or availabilty. One of the things you could do is immediately take a backup of your control file just in case; Deepak --- Seema Singh [EMAIL PROTECTED] wrote: Hi My redo log group# is 5,6,7.If I want to add more redo log group then Can I add group#9? As far I know there will be not impact on Database but let me know group view please. Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: [Q] what difference between count(0), count(1) and count(*)
Thank you for answer. How about count(0)? From: Deepak Thapliyal [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: [Q] what difference between count(0), count(1) and count(*) Date: Wed, 24 Oct 2001 11:41:11 -0800 i think count(*) gives count of all columns .. where as count(col1) gives count for col1 ignoring nulls in col1 Deepak --- aaa aaa [EMAIL PROTECTED] wrote: Can anyone tell me what is difference between : select count(*) ... from .. select count(0) ... select coun (1) ... select count(2) ... Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Vs SQL Server / Re: ORACLE-L Digest -- Volume 2001, Number 296
http://certcities.com/editorial/columns/story.asp?EditorialsID=23 - http://www.google.com/search?as_q=num=100btnG=Google+Searchas_epq=oracle+vs.+sql+serveras_oq=as_eq=lr=as_qdr=allas_occt=anyas_dt=ias_sitesearch=safe=off ORACLE-L Digest -- Volume 2001, Number 296 -- From: Farnsworth, Dave [EMAIL PROTECTED] Date: Mon, 22 Oct 2001 12:08:32 -0500 Subject: RE: Oracle Vs SQL Server Dave, Here is a site that will give you some of the info you are looking for. http://searchdatabase.techtarget.com/home/0,,sid13,00.html Also, there are too many Dave's in the world. Better than too many Goliaths! -- 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).
Re: [Q] what difference between count(0), count(1) and
As I understand it, count(*) counts all rows. Anything other than * is treated as an expression and it returns the number of rows where that expression evaluates to not null. So what Deepak said below about count(col1) giving the count of non-null instances of col1 is correct. That also means that count(0), count(1), etc should behave the same as count(*), as 0, 1, etc are not null. Now, is count(*) less efficient in execution than count(1)? I've been asked this before, and I don't know. I would've thought count(*) and count(1) would be the same, execution-wise. Is this correct? -a [EMAIL PROTECTED] 25/10/2001 11:30:17 This message has been scanned by MAILSweeper. Thank you for answer. How about count(0)? From: Deepak Thapliyal [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: [Q] what difference between count(0), count(1) and count(*) Date: Wed, 24 Oct 2001 11:41:11 -0800 i think count(*) gives count of all columns .. where as count(col1) gives count for col1 ignoring nulls in col1 Deepak --- aaa aaa [EMAIL PROTECTED] wrote: Can anyone tell me what is difference between : select count(*) ... from .. select count(0) ... select coun (1) ... select count(2) ... Thanks. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: aaa aaa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - - Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** 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: Arn Klammer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
equivalent of LPAD in Transact SQL
List, Bit of an inappropriate question to the Oracle List. Does anybody know the SQL Server (Transact SQL) equivalent of the LPAD function. Regards Suhen
RE: SQL Loader questions (9i will let you do a merge)
For interest, 9i introduces the SQL Loader merge command which combines update and insert. So if they were reading 9i doco then they might have been partly right. Refer to Jonathan Gennick's article from Oracle Magazine - available online at http://www.oracle.com/oramag/oracle/01-sep/index.html?o51o9i.html (or the doco). Regards, Bruce Reardon -Original Message- Sent: Thursday, 25 October 2001 7:16 Thanks to all for reconfirming this for me. I had never heard of SQL Loader doing these things, but couldn't quite convince the programmer. What I have since found out is that once upon a time, a consultant came in and set up the job that loads their data - and left without documenting it. Lo and behold, there's a lot of other stuff the script is doing besides running SQL Loader. Thanks again for all your responses. Lisa :D -Original Message- Sent: Wednesday, October 24, 2001 1:00 PM what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa [EMAIL PROTECTED] wrote: Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sequence pool
We had a situation like this - we wanted to get a large batch of sequences at once with one trip to the database. We used a stored procedure that took an argument how many do you want - and it used an execute immedate select nextval into an array and passed the array back. On Wed, 24 Oct 2001 08:00:24 -0800, you wrote: Hi, is there a smart way to generate IDs in chunks? getting an ID with nextval is perfectly ok most of the time, but sometimes the application would need to generate a pool of IDs and keep them for later use as I can't manipulate the currval of the sequence, I wander what the best solution would be thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Doug C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
