RE: Oracle Tools for Data WareHousing
Title: Message Hi Santosh, Check out these sites www.knowledgestorm.com http://technet.oracle.com/products/warehouse/contents.html http://devnet.informatica.com This should help for a start. regards, Satya Prakash -Original Message-From: Santosh Varma [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: Oracle Tools for Data WareHousing Hello list, Any site where i can find the information related to oracle tools for data warehousing... any help will be appreaciated... Thanks and Regards, Santosh
Can't find orazht.msg file
Dear all, Where can i download orazht.msg? 'Cos when i issue 'oerr ora 1562', it showed "Cannot find/home/oracle/product/8.1.6/rdbms/mesg/orazht.msg file." I'm using Oracle8.1.6 on Redhat7.2 Thanks in advance.
Re: Big SGA.......
Now you mention it, I have seen some other operating system use a reference to 'large' (4MB) pages. I have heard some comments, though, about one of the Solaris versions not being able to use shared page tables (ISM) when the SGA goes over a critical limit - but this could be highly version-dependent, relate to some inability to use large pages with ISM, or half a dozen other things. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 06:33 Linux has two patches to deal with that. One is the highpte patch which allows page table entries in high memory (i.e. after 1Gb). The other is bigpages which allows larger pages (i.e. 4mb instead of 4kb), thus fewer page table entries. Both patches are for Red Hat AS 2.1. I imagine that the proprietary UNIXes dealt with this a *long* time ago. Oracle and other long-time UNIX ISVs are pretty much having to lead the Linux vendors by the hand on such things... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
copycommit probs
Title: copycommit probs Dear All, I am trying to copy a local table to another local . I have created the link which is working when doing a select but when it comes to copying it gives an ORA-12154 eror. How can I resolve this? Kind Regards, Hatzistavrou Yannis Database Administrator SchlumbergerSema Phone ext. 478 Email: [EMAIL PROTECTED]
RE: copycommit probs
Title: copycommit probs Case resolved. Hint. Copy does only use the alias in tnsnames.ora and not the loopback db_links Kind Regards, Hatzistavrou Yannis Database Administrator SchlumbergerSema Phone ext. 478 Email: [EMAIL PROTECTED] -Original Message- From: Hatzistavrou John Sent: Thursday, March 06, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Subject: copycommit probs Dear All, I am trying to copy a local table to another local . I have created the link which is working when doing a select but when it comes to copying it gives an ORA-12154 eror. How can I resolve this? Kind Regards, Hatzistavrou Yannis Database Administrator SchlumbergerSema Phone ext. 478 Email: [EMAIL PROTECTED]
RE: Oracle Internet File System
Thank you very much. I hope this doesn't mean that future versions of
Oracle Files. neé Oracle Ifs, will only be available by purchasing Oracle
Collaboration Suite.
There is always the possibility that this is exactly what could happen, if
it hasn't already.
Notice the way the main Oracle product is divided between Standard and
Enterprise. There are certain features (spatial, for example) which are ONLY
available if an Enterprise licence is purchased. The fact that the spatial
stuff will run an a standard installation indicates (as usual) that these
divisions are often marketing-led. Energetic user groups and customers can
have influence in correcting these anomalies...
peter
edinburgh
..
Ian MacGregor
-Original Message-
Sent: Wednesday, March 05, 2003 7:37 AM
To: Multiple recipients of list ORACLE-L
iFS 1.X.X and below is alternately with the 8.1.7 database and 9iAS 1.0.2.X
9iFS 9.0.1 is with the Database CD Pack {on a seperate CD}
9iFS 9.0.2 is part of 9iAS 9.0.2 {on a seperate CD}
9iFS 9.0.3 has been renamed as Oracle Files and is part of Oracle
Collaboration Suite.
Check the MetaLink certification pages for
Internet File System [which goes upto 9.0.1]
9i Internet Application Server [where 9iFS 9.0.2 is listed under
components for 9iAS 9.0.2]
Oracle Collaboration Suite [where Oracle Files 9.0.3 is listed under
components for OCS 9.0.3]
Hemant
At 04:19 PM 04-03-03 -0800, you wrote:
I believe this is free with the Enterprise Edition of the database
server,
but I have not been able to confirm it. There is certainly no iFS
option. Am I correct here or not? Can anyone point me to an Oracle
document saying it is free.
We are looking at collaboration tools such as SharePoint which takes a
SQL
Server back end. Oracle is pushing Collaboration Suite, but I am wary of any
first release from Oracle especially in an area where their success
as been non-existent. I have not seen any specifications for what is
needed and iFS may be satisfactory.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
Hemant K Chitale
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you have received it in error
and any copying, distribution or other use of any part of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent those of the British
Geological Survey. The security of e-mail communication cannot be
guaranteed and the BGS accepts no liability for claims arising as a
result of the use of this medium to transmit messages from or to the
BGS. The BGS cannot accept any responsibility for viruses, so please
scan all attachments.http://www.bgs.ac.uk
Re: Apache and mod_plsql
I'm not sure about much regarding Oracle's license terms right now, but I have been told (while working in Oracle) that the Apache server that was built into 8.1.6 was purely there because OEM needed it (Morten - is that correct?) and that you were not allowed to use it instead of iAS. However, I cannot possibly see how anyone could come after you for using a feature in the database when no dire warnings have been issued as far as I know. So go for it. Oracle - according to rumours we heard last Monday - would ax around 30% of the US sales/consulting organisation. Well, they certainly fired a lot of people Friday, but I don't know how many. There will, by the way, be other events like that in other countries in this quarter. Perhaps even in the UK, where they recently removed 25% of Consulting in one day. Certainly in Denmark. Mogens [EMAIL PROTECTED] wrote: Morten, Regrettably my friendly License Management person has apparently left, since e-mail to his account bounces. But, his previous statement was that if the installer does NOT give you an option to install or not install a product, then your free to use it. Given that, I'd say that one is free to use the apache server that they bundle in 9i since there is no option on installing it, it just does it. The Context option is a different animal. The Installer may not let you leave it out, but your not free to use it either since it is listed. Dick Goulet Reply Separator Author: Morten Egan [EMAIL PROTECTED] Date: 3/5/2003 12:14 PM hmmm I remember something about licensing with Oracle HTTP Server. AFAIK you are NOT allowed to use it for this kind of appl. I think you need to buy the IAS. Can anyone remember the specifics around this, or am I way off here? /morten Michael Garfield Sorensen wrote: If you don't plan to have many users, I think you could make do with the Oracle HTTP Server (Powered by Apache) that comes bundled with the database from 8.1.6 and onwards. No need to rewrite any code, as it comes with mod_plsql and the PL/SQL Web Toolkit. You could also consider ChangeGroup PL/SQL Server Pages (see http://www.changegroup.biz/da/cgpsp.psp). That would require a rewrite, but it shouldn't be that hard... Regards, Michael Garfield Sorensen, CeDeT - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: 5. marts 2003 16:50 The O'Reilly book Oracle and Open Source says DBPrism is a continuation of / based on OWSKiller, and is "one of the most astonishing success stories of Java, Oracle, and open source cooperation." (p.299). They explain how to install it, how to use it, adapters you can get for it, including Cocoon (a Java publishing framework). Pat. -Original Message- Sent: Wednesday, March 05, 2003 10:06 AM To: Multiple recipients of list ORACLE-L We are running 9iAS. If I understand this correctly, 9iAS is simply Apache with a PL/SQL module from Oracle. Our management would like to save the Oracle Support dollars for 9iAS, so I am looking into alternatives. We use the PL/SQL web packages, so my options are limited unless we choose to rewrite code. My questions are: 1) Is it possible to add the (or "a") PL/SQL module to Apache without going through Oracle - or does the combination of the two simply give you 9iAS, which is an Oracle product? 2) Any comments or experience with DBPrism (http://www.plenix.com/dbprism/), which is the only possible alternative that I have found so far? I've been trying to read about modowa (http://www.sharemation.com/~dmcmahon/modowa.htm) but the server is down at the moment. Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Re: Can't find orazht.msg file
I think it is looking for the Taiwan Chinese (BIG5) error file, which you don't have. If you change your NLS_LANG environment variable to something that is available in that directory, it will probably work. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 6 Mar 2003, [big5] shuan.tay\(PCI¾G¸R³Ô\) wrote: Where can i download orazht.msg? 'Cos when i issue 'oerr ora 1562', it showed Cannot find /home/oracle/product/8.1.6/rdbms/mesg/orazht.msg file. I'm using Oracle8.1.6 on Redhat7.2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Apache and mod_plsql
About a month ago there were news they were going to hire 2000 people in India. HP has run into a bit of trouble over this, see TheRegister re. that company's customer support problems. Pat. -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]Sent: Thursday, March 06, 2003 6:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Apache and mod_plsqlI'm not sure about much regarding Oracle's license terms right now, but I have been told (while working in Oracle) that the Apache server that was built into 8.1.6 was purely there because OEM needed it (Morten - is that correct?) and that you were not allowed to use it instead of iAS. However, I cannot possibly see how anyone could come after you for using a feature in the database when no dire warnings have been issued as far as I know. So go for it.Oracle - according to rumours we heard last Monday - would ax around 30% of the US sales/consulting organisation. Well, they certainly fired a lot of people Friday, but I don't know how many.There will, by the way, be other events like that in other countries in this quarter. Perhaps even in the UK, where they recently removed 25% of Consulting in one day. Certainly in Denmark.Mogens[EMAIL PROTECTED] wrote: Morten, Regrettably my friendly License Management person has apparently left, since e-mail to his account bounces. But, his previous statement was that if the installer does NOT give you an option to install or not install a product, then your free to use it. Given that, I'd say that one is free to use the apache server that they bundle in 9i since there is no option on installing it, it just does it. The Context option is a different animal. The Installer may not let you leave it out, but your not free to use it either since it is listed. Dick Goulet Reply Separator Author: Morten Egan [EMAIL PROTECTED] Date: 3/5/2003 12:14 PM hmmm I remember something about licensing with Oracle HTTP Server. AFAIK you are NOT allowed to use it for this kind of appl. I think you need to buy the IAS. Can anyone remember the specifics around this, or am I way off here? /morten Michael Garfield Sorensen wrote: If you don't plan to have many users, I think you could make do with the Oracle HTTP Server (Powered by Apache) that comes bundled with the database from 8.1.6 and onwards. No need to rewrite any code, as it comes with mod_plsql and the PL/SQL Web Toolkit. You could also consider ChangeGroup PL/SQL Server Pages (see http://www.changegroup.biz/da/cgpsp.psp). That would require a rewrite, but it shouldn't be that hard... Regards, Michael Garfield Sorensen, CeDeT - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: 5. marts 2003 16:50 The O'Reilly book Oracle and Open Source says DBPrism is a continuation of / based on OWSKiller, and is "one of the most astonishing success stories of Java, Oracle, and open source cooperation." (p.299). They explain how to install it, how to use it, adapters you can get for it, including Cocoon (a Java publishing framework). Pat. -Original Message- Sent: Wednesday, March 05, 2003 10:06 AM To: Multiple recipients of list ORACLE-L We are running 9iAS. If I understand this correctly, 9iAS is simply Apache with a PL/SQL module from Oracle. Our management would like to save the Oracle Support dollars for 9iAS, so I am looking into alternatives. We use the PL/SQL web packages, so my options are limited unless we choose to rewrite code. My questions are: 1) Is it possible to add the (or "a") PL/SQL module to Apache without going through Oracle - or does the combination of the two simply give you 9iAS, which is an Oracle product? 2) Any comments or experience with DBPrism (http://www.plenix.com/dbprism/), which is the only possible alternative that I have found so far? I've been trying to read about modowa (http://www.sharemation.com/~dmcmahon/modowa.htm) but the server is down at the moment. Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re[2]: Duplicate online Database
Chuck, I'm inserested in this paper. Can you send it to me ? Thanks. -- Breno A. K. Magnagomailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Tuesday, March 04, 2003, 11:03:43 AM, you wrote: CH I don't see why not. Prior to RMAN I used a procedure documented in Oracle CH Magazine called database cloning that used hot backups and archived logs. I CH have a white paper on it if you're interested. I'm sure RMAN has the same CH capability. CH -- CH Chuck CH - Original Message - CH To: Multiple recipients of list ORACLE-L CH Sent: Tuesday, March 04, 2003 5:38 AM CH I need to duplicate online database Oracle 8.1.7. CH It is not possible to shutdown the primary database. CH Can I do online backup this primary database by RMAN, CH and + using archivelogs create duplicate database? CH Inconsistent backups of RMAN + archivelogs = duplicate database?? CH Thanks CH Edouard Dormidontov -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Character Set / Encrypted text question
Mark, The most frequent type of programming error leading to this is that the programmer expects that a varchar2 column can contain any binary value. This is by definition not true, varchar2 (actually all char types, including clob and long) should ONLY be used to store readable text. If you want to store binary information, you need to use one of the RAW data types. What you are seeing is probably that the database was created with one character set and your application runs with another. The database character set is set during 'create database' and can only be changed with difficulty if at all. The application character set is taken from the last part of the NLS_LANG environment. As an unfortunate side effect of the way Oracle does this, if both database and application character set is US7ASCII (which in any case is the worst possible), you can store any binary value and get it unchanged in to and out from the database. This fact has lead many to use varchar2 (or other char data types) to store binary values. This is not and never was supported, and cannot be expected to work. /Bjrn. Mark Richard wrote: Dear List, I have a question for the character set guru's out there... We are trying to store encrypted text from a Forte application into a varchar2 column in Oracle (8.1.7.4). When Forte retrieves the string a couple of the characters appear to have changed value. I assume we are experiencing some kind of character set limitation. Does anyone have any ideas about how we might work around this issue? I have included the NLS section from out init.ora section for reference. The only workaround we can think of is converting the string to hex values and reassembling these into a long string and then storing that result. Does this sound reasonable (assuming it's not a simple character set change). Please keep the help simple - I have never had to be concerned with character sets before and don't really understand all of the terminology. # NLS settings nls_date_format = "DD-MON-" nls_sort= BINARY nls_language= AMERICAN nls_numeric_characters = ".," Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Bjrn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk
RE: PLSQL stored procedure
Jeremy, The price for the TOAD Expert is quit reasonable when you consider the fact that it includes the SQLab Expert module. The stand alone SQLab Expert yearly fee was big $$$ and could only acces 1 server per license. The TOAD expert can access many servers. I changed over to the TOAD Expert and dropped the SQLab Expert module last year. Saved a lot. Ron [EMAIL PROTECTED] 03/05/03 03:34PM Does the freeware version have the debugger now??!!! If so, highly cool. Going to check right now. Hmmm, no freeware TOAD version explicitly offered on website. Can download trial version of Xpert Edition (gotta love marketing and their liberal misuse of english)... WOW! 48 megs. My, how you've grown... I seem to remember having a copy of TOAD on floppy back in the day. Might be senility setting in, though. wait_time: 20 minutes or so K, got the download, received email from quest with licenses. Ahah! Only good for 30 days. ! suppose I could point out that since a quest employee has declared the debugger as part of a freeware distribution, that maybe I shouldn't have to pay for it now, right? -Original Message- Sent: Wednesday, March 05, 2003 11:19 AM To: Multiple recipients of list ORACLE-L our go get TOAD freeware... even has a line by line debugger with watches. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 05, 2003 7:06 AM To: Multiple recipients of list ORACLE-L Look at user_source (or all_source if the procedure is in a different schema). Then select text from user_source where name = 'YOURPROCNAME'; [EMAIL PROTECTED] 03/05/03 07:34AM Hi. i need some help. i'm trying to get a stored procedure source code from the database. After a lot of reding i found : select * from user_objects where object_type='PROCEDURE'; and i've got the name and Object_id of that prodedure but i have no ideea how to get the rest :( please help, adrian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Adrian Ciocildau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How Reliable is Explain Plan in 9.2
Title: RE: How Reliable is Explain Plan in 9.2 Thanks Wolfgang ... I understand your point ... I was just trying to be a little cynical ... about these *facts* ... the very elements about which Oracle tells us to rely upon. 1. Who in their right mind would add/drop an index on a live production database without thinking 'A Ha ... dropping this unique key index shouldn't hurt my 10 gazillion row table at all.'? 2. I am now worried about optimizer taking past executions into account ... if you have a person consistently committing mistakes, and you expect this person to learn from his/her experience, what do you think will happen ?? (Stay with me ... it's been a bad morning so far ... a wrong DB was refreshed and someone decided last night to arrange a demo this afternoon.) Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: How to improve queries remotely
I have found in my testing that an inline view works just as well as a normal view . If you are unsure, run explain plan and check the OTHER column in the plan_table. It'll show what is being executed remotely. Kevin -Original Message- Sent: Wednesday, March 05, 2003 5:40 PM To: Multiple recipients of list ORACLE-L I am not a fan of views, but build remote views to have has much screening of data and processing done remotely before you return the data. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Tools
Maybe and maybe not. iSQLplus is web based and does not work 100% on pocket IE. For example you can't load scripts from it. If the 10i EM depends as heavily on java running on the client as the 9i browser based version of OEM does, that won't work on a PDA either. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 2:41 PM 9i lite is a light weight database for PDA's. I know that at one point Oracle was developing a DBA tool for PDA's, but I dont know the status of this product, or if it has been cancelled since then. But of course you can wait for EM in 10i which is browser based, so that should be able to run in the pocket version of internet explorer Regards, Morten Egan Chuck Hamilton wrote: BTW is 9i lite just a database engine for PDAs, or does it allow you to administer databases over a network from a PDA? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Morten Egan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Internet File System
I don't think iFS will be available standalone.
We are running iFS 9.0.1 [upgraded from 1.1.9] and were talking to Oracle
about upgrading -- about
whether we should go to 9.0.2 or 9.0.3. Oracle was recommending that we
go for Oracle Files
in OCS -- but we'd have to buy the OCS license. At that time, 9.0.4 was
planned and Support
told us that iFS itself would continue to be supported even after 9.0.4 but
the account manager
tried to push us towards OCS. We were presented a demo of Oracle Files and
it was
made clear to us the iFS was only the bare product -- Oracle Files
'internally uses iFS' but
has a lot of functionality added on.
Hemant
At 09:49 AM 05-03-03 -0800, you wrote:
Thank you very much. I hope this doesn't mean that future versions of
Oracle Files. neé Oracle Ifs, will only be available by purchasing Oracle
Collaboration Suite.
Ian MacGregor
-Original Message-
Sent: Wednesday, March 05, 2003 7:37 AM
To: Multiple recipients of list ORACLE-L
iFS 1.X.X and below is alternately with the 8.1.7 database and 9iAS 1.0.2.X
9iFS 9.0.1 is with the Database CD Pack {on a seperate CD}
9iFS 9.0.2 is part of 9iAS 9.0.2 {on a seperate CD}
9iFS 9.0.3 has been renamed as Oracle Files and is part of Oracle
Collaboration Suite.
Check the MetaLink certification pages for
Internet File System [which goes upto 9.0.1]
9i Internet Application Server [where 9iFS 9.0.2 is listed under
components for 9iAS 9.0.2]
Oracle Collaboration Suite [where Oracle Files 9.0.3 is listed under
components for OCS 9.0.3]
Hemant
At 04:19 PM 04-03-03 -0800, you wrote:
I believe this is free with the Enterprise Edition of the database
server,
but I have not been able to confirm it. There is certainly no iFS
option. Am I correct here or not? Can anyone point me to an Oracle
document saying it is free.
We are looking at collaboration tools such as SharePoint which takes a
SQL
Server back end. Oracle is pushing Collaboration Suite, but I am wary
of any first release from Oracle especially in an area where their success
as been non-existent. I have not seen any specifications for what is
needed and iFS may be satisfactory.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
Hemant K Chitale
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Fine Grained Access Control (FGCA)
Hi Murali, In our case since the predicats are generated dynamically and a call to the function from outside will give me the predicate being applied. Thanks to Jonathan Lews, I will also make sure now using event 10730. Regards, Madhavan http://www.dpapps.com Thanks for the response. I did get it working. However I have another question. Have you tried to capture what happens behind the scene. I turned on trace however could not trace the actual query that gets added to the SQL statement. Only the function call could be traced not the actual SQL condition Thanks Menon -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How Reliable is Explain Plan in 9.2
I agree with JL. You've never been guaranteed that the plan you generate after the fact is the same plan that executed at some prior time. If you're trying to optimize a query using explain plan and are concerned that the plan may change between when the SQL executed and when you ran your explain, turn on SQL tracing in your code. That's the only way you know you're looking at the plan that actually executed. And if you're concerened that the CBO may change the plan based on a dynamically changing environment, use plan stabilization (i.e. outlines). Chuck - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 3:04 PM I think there's a big emotional difference between unreliable and won't necessarily give you the plan under the current circumstances that it gave at the time of execution - which has been true since the utility came out. It is true, of course, that the reasons for the variation have become increasingly subtle - but in theory the DBA should still be sufficiently in control of all the necessary parameters to cater for the likely variations. Of course, when the real-time learning module get included, then we're stuffed. BTW - - system statistics in effect at the time of parse. If the system statistics get changed, existing plans do not get invalidated, but if you do an explain the cbo will use the current values I read this in the manuals recently - but the first time I tested it, I got a plan invalidation, re-parse and new execution path. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 05 March 2003 19:09 Yes, explain plan will become increasingly unreliable as the cbo takes more and more factors and current conditions current into account. Some of the factors that can change the outcome of a parse from session to session are: for Oracle 8 - different session parameters (db_file_multiblock_read_count, hash_multiblock_io_count, sort_area_size, hash_area_size) for Oracle 9i additionally - you can let Oracle dynamically set the sort_area_size and other memory parameters so you have a moving target now - bind variable peeking - the first parse determines the plan for all following sql depending on its bind value - system statistics in effect at the time of parse. If the system statistics get changed, existing plans do not get invalidated, but if you do an explain the cbo will use the current values - dynamic sampling where the optimizer tries to improve on its estimates by sampling predicate values at the time of parsing. from comments I heard, it will get worse (as far as explain differing from reality is concerned) with Oracle 10. The optimizer will try and learn from past executions of a sql and modify the plan if appropriate. At 08:35 AM 3/5/2003 -0800, you wrote: Just had a fellow tell me that explain plan in completely unreliable in 9.2 and getting accurate results requires direct SGA access on executing SQL (he is working in a RAC environment). They are running Precise, a good product, but this sounds like something a sales person told him. I can only recall that occasionally the plan executed is not the plan you see in explain plan. Anyone know the truth of this issue? Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu,
Re: How Reliable is Explain Plan in 9.2
MessageIs that something new in 9i? I don't have it in 8.1.7. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 05, 2003 4:19 PM A, that is what he is talking about, I wasn't aware of v$sql_plan. -Original Message- Sent: Wednesday, March 05, 2003 2:30 PM To: Multiple recipients of list ORACLE-L As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time. If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view. Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fudging outlines
I have an application query that I do not have the source code for. It gets a crappy execution plan. I can add a hint or two to it and significantly improve the execution plan. I want to stuff that execution plan into a stored outline so that the unhinted query uses that plan plan each time it executes. Can this be done? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tricky SQL Question
Title: Tricky SQL Question Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Oracle Bug Reports...
Interesting bug. I'm spacing on one thing though. Don't see where the ORA-1555 is coming from on their test case. Henry ***EXCERPT FROM BUG REPORT *** *** REM Create the user and the tables in question REM create user medic identified by medic; alter user medic default tablespace users temporary tablespace temp; grant dba to medic; connect medic/medic; create table myobjs1 as select * from dba_objects; create table myobjs2 as select * from dba_objects; create table myobjs3 as select * from dba_objects; declare a number; begin for a in 1..3 loop insert into myobjs1 select * from myobjs1; commit; end loop; end; / REM REM Create small rollback segment to use for these trxs, and offline all REM other non-system rollback segments REM create rollback segment small_rbs_to_use tablespace rbs storage (initial 50k next 50k optimal 200k maxextents unlimited); alter rollback segment small_rbs_to_use online; alter rollback segment all other non-system rbs here offline; REM REM Start session 1 REM connect medic/medic set time on update myobjs1 set owner='change1'; commit; REM REM Start session 2 REM connect medic/medic set time on update myobjs1 set owner='change2'; REM REM Start session 3 REM connect medic/medic set time on update myobjs1 set owner='change3'; REM REM Start the Export REM exp medic/medic direct=y tables=\(myobjs1,myobjs2,myobjs3\) log=imp1.log REM You got to be exporting myobjs1 when session 2 and session 3 error out with ORA-1555 (snapshot too old: rollback segment number ...with name SMALL_RBS_TO_USE too small). To accomplish this, startup and execute the SQL statement in the three sessions, and see how long it takes, for session 2 to fail with the ORA-1555 after it was submitted for execution. In my test case, it took a minute and 20 seconds before it failed. Therefore I launch the export session a minute and an 10 seconds after executing the SQL in session 2. -Original Message- [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 1:29 PM To: Multiple recipients of list ORACLE-L ... can be rather interesting at times, when there's time to check out a few. http://metalink.oracle.com/metalink/plsql/showDoc?db=BUGid=2666174 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
explain plans history
I found this on the 'net: http://www.databasejournal.com/features/oracle/article.php/2026601 On our servers people do ad hoc querying, but I thought it might be useful to somebody. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN archive log Back up taking long time
We are using rman to backup the 8.1.6 database. Sometime it is noticed that rman is taking huge time to back up some archivelog files . Generally it takes 20 seconds to backup one archivelog , but for some archivelog files it is taking more then 2 hrs . Did any of you noticed this problem before. Can you share your experience and ideas where should I look into . All files are of same size. Thanks, -oramagic. We have noticed Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more
FalconStor and Oracle performance
I have a potential consulting gig involving a system that includes a FalconStor storage virtualization component. I'm not (yet) familiar with this company or its technology. The database servers are RedHat Linux. RAID is supplied by a StorageTek array, and a FalconStor IPStor storage consolidation/virtualization box sits atop the StorageTek array. Apparently the StorageTek configures the disks as RAID 5, which we all know is a Bad Thing for database performance. However, the thing that puzzles me is a gradual deterioration in performance that can be corrected temporarily by rebooting the FalconStor box. The application involves imaging, and the bulk of the activity is high-volume data loading (which makes the RAID 5 configuration an even worse choice). I will, of course, be recording and analyzing wait events and statistics, but I'd also be grateful for comments from anyone who's worked with FalconStor or similar products and can suggest other things to investigate. TIA, Paul Baumgartel __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fudging outlines
Title: RE: Fudging outlines Yup ... and oracle has a note that tells you how. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Subject: Fudging outlines I have an application query that I do not have the source code for. It gets a crappy execution plan. I can add a hint or two to it and significantly improve the execution plan. I want to stuff that execution plan into a stored outline so that the unhinted query uses that plan plan each time it executes. Can this be done? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: How Reliable is Explain Plan in 9.2
Yes, the v$sql_plan is new in 9i -Original Message- Sent: Thursday, March 06, 2003 10:34 AM To: Multiple recipients of list ORACLE-L MessageIs that something new in 9i? I don't have it in 8.1.7. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 05, 2003 4:19 PM A, that is what he is talking about, I wasn't aware of v$sql_plan. -Original Message- Sent: Wednesday, March 05, 2003 2:30 PM To: Multiple recipients of list ORACLE-L As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time. If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view. Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tricky SQL Question
Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 16:49 Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Internet File System
It's part of the OCS now, I believe. OCS is apparently only available with
Named User Plus licenses, which amount to 60 dollars per NUP.
About the options you mention: Every option is only available to you if you
have bought an EE license of Oracle: RAC, Spatial, Partitioning, Data Mining,
OLAP, Advanced Security, Label Security, Diagnostics Pack, Tuning Pack, Change
Management Pack... And here's the amounts you should add for each of these
on top of the $40K per cpu license: $20K, $10K, $10K, $20K, $20K, $10K, $10K,
$3K, $3K, $3K...
At the moment Microsoft's SE costs 32% or so of Oracle's SE. Their EE costs
about 46% of Oracle's EE. Yes, we all know there are differences. But the
finance department might not care. I think a better strategy would be to
price EE as SE, then let the price of the options vary so that if you bought
a bunch of them you'd end up around the current EE price.
Mogens
Best regards,
Mogens
Robson, Peter wrote:
Thank you very much. I hope this doesn't mean that future versions of
Oracle Files. ne Oracle Ifs, will only be available by purchasing Oracle
Collaboration Suite.
There is always the possibility that this is exactly what could happen, if
it hasn't already.
Notice the way the main Oracle product is divided between Standard and
Enterprise. There are certain features (spatial, for example) which are ONLY
available if an Enterprise licence is purchased. The fact that the spatial
stuff will run an a standard installation indicates (as usual) that these
divisions are often marketing-led. Energetic user groups and customers can
have influence in correcting these anomalies...
peter
edinburgh
..
Ian MacGregor
-Original Message-
Sent: Wednesday, March 05, 2003 7:37 AM
To: Multiple recipients of list ORACLE-L
iFS 1.X.X and below is alternately with the 8.1.7 database and 9iAS 1.0.2.X
9iFS 9.0.1 is with the Database CD Pack {on a seperate CD}
9iFS 9.0.2 is part of 9iAS 9.0.2 {on a seperate CD}
9iFS 9.0.3 has been renamed as Oracle Files and is part of Oracle
Collaboration Suite.
Check the MetaLink certification pages for
"Internet File System" [which goes upto 9.0.1]
"9i Internet Application Server" [where 9iFS 9.0.2 is listed under
components for 9iAS 9.0.2]
"Oracle Collaboration Suite" [where Oracle Files 9.0.3 is listed under
components for OCS 9.0.3]
Hemant
At 04:19 PM 04-03-03 -0800, you wrote:
I believe this is free with the Enterprise Edition of the database
server,
but I have not been able to confirm it. There is certainly no "iFS"
option. Am I correct here or not? Can anyone point me to an Oracle
document saying it is free.
We are looking at collaboration tools such as SharePoint which takes a
SQL
Server back end. Oracle is pushing "Collaboration Suite", but I am wary of any first release from Oracle especially in an area where their success
as been non-existent. I have not seen any specifications for what is
needed and iFS may be satisfactory.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
Hemant K Chitale
My personal web site is : http://hkchital.tripod.com
RE: Tricky SQL Question
Title: RE: Tricky SQL Question Thanks Jonathan, I'd like to assign the tables to a group, but need to do that periodically. Also what I do is load all tables that belong to a group in a pl/sql table (bulk updates/bulk collects). That's why I don't want to do read-from-table ... do-action BTW this doesn't have to be optimal ... I am just trying to split the load ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Tricky SQL Question Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 16:49 Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Fudging outlines
Title: RE: Fudging outlines The only document I found on metalink was doc id 144194.1 which pertains to 9i. I didn't mention it in my OP but I am using 8i. Also the database I want to do this on is SE not EE so it looks like it's a moot point anyway. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, March 06, 2003 12:19 PM Subject: RE: Fudging outlines Yup ... and oracle has a note that tells you how. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Subject: Fudging outlines I have an application query that I do not have the source code for. It gets a crappy execution plan. I can add a hint or two to it and significantly improve the execution plan. I want to stuff that execution plan into a stored outline so that the unhinted query uses that plan plan each time it executes. Can this be done? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
show errors doesn't show anything ???
Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
remote / as sysdba
Hello,
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace import, where
the userid would be listed in a parfile; I have tried the same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
Thanks to any responders.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Big SGA.......
Title: Message Now you are talking, at a prestigious English public school (although at least a hundred years ago) there used to be a headmaster with 3 boys. Periodically he would hide them under a tarpaulin and hit them at random - the moral being 'life is unfair so get used to it'. Um, I did have an on topic point once upon a time, but it seems to have gone the way of english cricket -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin LangeSent: 04 March 2003 15:50To: Multiple recipients of list ORACLE-LSubject: RE: Big SGA... ... large burlap sack and a small bat -Original Message-From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 04, 2003 7:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Big SGA... duct tape -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]Sent: Monday, March 03, 2003 5:10 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Big SGA... Sybase, Schmybase, Oracle, Schmoracle -- the concepts are still the same. Developers create tables and indexes and then write SQL, thinking that the RDBMS is at fault if performance doesn't match expectations. They have to understand that the structures they have created or the queries they have written may simply be inefficient, expending too much work. I don't know how to measure that in Sybase, but I'm reasonably sure that there must be a way. I used to joke that I could get OracleERP/Appsto run on a Palm Pilot if I were permitted to reallytune the SQL. The work performed by an application is not an immutable monolith, especially with the Oracle RDBMS and all of the performance statistics it keeps. It is very much susceptible to improvement. First, they must make a reasonable attempt to *fix* the problem (by making SQL more efficient). If that doesn't work, thenthey should*accomodate* the problem by buying more hardware, increasing buffer sizes, etc. The key with the latter approachis to realize that you haven't fixed anything, only accomodated it by throwing resources at it. Pop quiz: Think of a parent with a spoiled child who is making a scene in public. How do you quiet the child? :-) - Original Message - From: Loughmiller, Greg To: Multiple recipients of list ORACLE-L Sent: Monday, March 03, 2003 2:28 PM Subject: RE: Big SGA... one little piece of information..(considered critical probably:-) ) There isn't an opportunity to use statspack... The current application is running on sybase:-) I do have other teams researching the questions you mention. its a real fun project... -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]Sent: Monday, March 03, 2003 2:02 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Big SGA... Please start using STATSPACK now to gather and keep statistics. You are certainly going to need "before" and "after" statistics to analyze. Some questions: Why does the development group think that I/O is the problem? Have they been gathering data? Have you seen it? Do you concur that their data proves that I/O is a performance problem belonging to the Oracle database? Let's assume that there is an I/O problem. There are two ways to address I/O (as stated in the YAPP report of www.oraperf.com): reduce the *cost* per I/O request or reduce the *number* of I/O requests. The former implies getting a better/faster I/O subsystem, redistributing I/O load to different volumes, etc. Not trivial. The latter implies improving the Buffer Cache Hit Ratio (BCHR) by increasing the size of the Buffer Cache or it implies making queries more efficient, so that they simply don't issue so many I/O requests (either to the Buffer Cache or to the disk). Gathering STATSPACK data and searching for the SQL statements generating the largest number of "physical I/O" requests might be illuminating for the developers. If you work with them on a one-by-one basis on tuning each of these SQL statements, you might see dramatic improvements in performance. Suggest to them that *after* you are confident that there are no tunable SQL statements, then you might
RE: Fudging outlines
Title: RE: Fudging outlines It is Note 92202.1 - How to specify hidden hints on SQL statements with a prominent disclaimer: Disclaimer: This script is provided for educational purposes only. It is NOT supported by Oracle Support Services. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. At 09:19 AM 3/6/2003 -0800, you wrote: Yup ... and oracle has a note that tells you how. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Subject: Fudging outlines I have an application query that I do not have the source code for. It gets a crappy execution plan. I can add a hint or two to it and significantly improve the execution plan. I want to stuff that execution plan into a stored outline so that the unhinted query uses that plan plan each time it executes. Can this be done? Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
RE: Tricky SQL Question -- Solved
Title: RE: Tricky SQL Question -- Solved Okay, I cracked it ... if you are interested, read on ... it is not very optimal, but close to what I want. To me 8 streams is standard, so you'd see 8 as hardcoded. Also I found that select sum(obj_last_analyze_time)/8 from statistics_info / was about 8425 (i.e. ~ 85 seconds). So I wrote this not-so-dynamic sql select group_id, sum(tm1), count(*) from( SELECT obj_owner, obj_name, tm1, case when roll_sum = 8400*1 then 1 else case when roll_sum = 8400*2 then 2 else case when roll_sum = 8400*3 then 3 else case when roll_sum = 8400*4 then 4 else case when roll_sum = 8400*5 then 5 else case when roll_sum = 8400*6 then 6 else case when roll_sum = 8400*7 then 7 else 8 end end end end end end end group_id FROM (SELECT rnum, obj_owner, obj_name, tm1, SUM (tm1) OVER (ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1 FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1 FROM statistics_info ORDER BY obj_last_analyze_time))) ) group by group_id / The output is as follows ... GROUP_ID TOT_TIME TOT_TABLES -- 1 8397 1755 2 8387 667 3 8204 135 4 7984 20 5 8954 7 6 6928 3 7 7113 2 8 11438 1 I'll probably make it dynamic enough ... inside my package ... Cheers Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jamadagni, Rajendra Sent: Thursday, March 06, 2003 1:16 PM To: '[EMAIL PROTECTED]' Subject: RE: Tricky SQL Question - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 16:49 Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Oracle Tools - OEM 4.0
And merely requires a 9iAS license (and associated DB license to run ), as I currently understand it. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Murali Vallath Sent: 06 March 2003 00:19 To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Tools - OEM 4.0 If you wait for another couple of months you may have OEM 4.0 which has a considerable amount of enhancements including supports for PDA's and the basic version comes with the enterprise edition. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: show errors doesn't show anything ???
show errors package prv_admin; show errors package body prv_admin; Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 06, 2003 1:23 PM Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: show errors doesn't show anything ???
Janet, try compiling the package body and then checking for errors. alter package body prv_admin compile; show errors; bet you a dollar the package body has errors, not the package spec. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 06, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: show errors doesn't show anything ???
You can try select * from dba_errors where name='PRV_ADMIN'; Rick Janet Linsy [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hoo.com cc: Sent by: Subject: show errors doesn't show anything ??? [EMAIL PROTECTED] om 03/06/2003 01:23 PM Please respond to ORACLE-L Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
have you got the remote login password file ?
if you set it I think it should work .
take a look at Note:1016540.6
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, March 06, 2003 7:09 PM
To: Multiple recipients of list ORACLE-L
Hello,
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace import, where
the userid would be listed in a parfile; I have tried the same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
Thanks to any responders.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MARREIROS,RUI (HP-Portugal,ex1)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: show errors doesn't show anything ???
Title: RE: show errors doesn't show anything ??? try show errors package or show errors package prv_admin from the 9.2.0 SQL PLUS Reference Manual When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, type body, dimension, or java class) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. Matt Adams - GE Appliances - [EMAIL PROTECTED] We have enough youth. How about a fountain of intelligence? -Original Message- From: Janet Linsy [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Subject: show errors doesn't show anything ??? Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
Title: RE: remote / as sysdba
As far as I know, it works like this:
You will need to set init parameter REMOTE_LOGIN_PASSWORD_FILE to EXCLUSIVE.
You then can sign on as SYS remotely, or as another user remotely if the other user has SYSDBA. See the users that have SYSDBA or SYSOPER in v$pwfile_users.
As the view name suggests, you will also need a password file for the database, which should be created with the orapwd utility.
$ orapwd
Usage: orapwd file=fname password=password entries=users
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
Once you've done all that, you can connect remotely by saying the following in SQL*Plus:
connect sys/[EMAIL PROTECTED] as sysdba
But I don't think you will ever be able to do
connect / as sysdba remotely. For one thing, the syntax in SQL*Plus is:
Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
ou logon ::= username[/password][@connect_string] | /
So the logon is either username/[EMAIL PROTECTED] or else / all by itself. How would you tell SQL*Plus which remote database you want to connect to? I tried setting TWO_TASK to the tns_alias for the database, but that didn't help. It seems to me that when you enable remote SYSDBA logins Oracle will insist on verifying a password for the SYSDBA user in the password file.
Or is there some clever trick I don't know about?
-Original Message-
From: [EMAIL PROTECTED]
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace
import, where
the userid would be listed in a parfile; I have tried the
same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises
against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
sql question ???
Hi, I got a SQL question (9i on Red Hat), commands shown below. The first sql returns 3 rows with value 1, so trim(client_company) = '', how come the 2nd sql doesn't return anything?? SQL select decode(trim(client_company), '', 1, ' ', 2, null, 3, 4) from cli_clients where cli_id in (257, 396, 727); DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4) --- 1 1 1 3 rows selected. SQL select count(*) from cli_clients where trim(client_company) = '' and cli_id in (257, 396, 727); COUNT(*) - 0 1 row selected. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Character Set / Encrypted text question
Mark, I have seen this issue here where an application scrambles/encrypts data into a character field varchar2 but it doesn't work right when moved to another environment. The source environment was using NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 but the target was using NLS_LANG=AMERICAN_AMERICA.US7ASCII. Going from an 8-bit characterset to a 7-bit characterset causes problems where the byte code value should have been properly represented by 8 bits and not 7. eg. VARCHAR2(15) field contains data ÚØÝ (dec: 218, 216, 221) comes out as U?Y when converted to US7ASCII (see SQL CONVERT function). Another app I use here does work, but it stores the values in Hex not with the character representation. Options: Used 8 bit characterset on database. Store data as hex value eg. Passwords stored in Oracle table (dba_users [sys.user$]) which is a VARCHAR2(30) field. User RAW fields. Converting the database characterset requires a database recreation. David From: Mark Richard [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Character Set / Encrypted text question Date: Wed, 05 Mar 2003 19:28:46 -0800 Dear List, I have a question for the character set guru's out there... We are trying to store encrypted text from a Forte application into a varchar2 column in Oracle (8.1.7.4). When Forte retrieves the string a couple of the characters appear to have changed value. I assume we are experiencing some kind of character set limitation. Does anyone have any ideas about how we might work around this issue? I have included the NLS section from out init.ora section for reference. The only workaround we can think of is converting the string to hex values and reassembling these into a long string and then storing that result. Does this sound reasonable (assuming it's not a simple character set change). Please keep the help simple - I have never had to be concerned with character sets before and don't really understand all of the terminology. # NLS settings nls_date_format = DD-MON- nls_sort= BINARY nls_language= AMERICAN nls_numeric_characters = ., Thanks in advance, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: david davis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: show errors doesn't show anything ???
Title: RE: show errors doesn't show anything ??? select * from user_errors where name = 'PRV_ADMIN' / - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Janet Linsy [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Subject: show errors doesn't show anything ??? Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: remote / as sysdba
Bill,
You can't do that. If you try to, you will eventually see ORA-1997
12:18:58 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL grant sysdba to
ops$jkstill;
grant sysdba to ops$jkstill
*
ERROR at line 1:
ORA-01997: GRANT failed: user 'OPS$JKSTILL' is identified externally
12:19:07 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL
You can create a remote user that can logon as sysdba remotely, but
that user must have a password.
See MetaLink Doc # 185703.1
Jared
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/06/2003 11:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:remote / as sysdba
Hello,
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace import, where
the userid would be listed in a parfile; I have tried the same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
Thanks to any responders.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Fudging outlines
The disclaimer is very important, as the script is missing a second update to exchange the hint counts between the two outlines. If you don't do this, a subsequent export/import of the outlines loses any outlines where the hint count doesn't match the number of rows in ol$hints. I've got a couple of articles on my website (also published on dbazine) which expand the issues of faking outlines into place. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 19:34 It is Note 92202.1 - How to specify hidden hints on SQL statements with a prominent disclaimer: Disclaimer: This script is provided for educational purposes only. It is NOT supported by Oracle Support Services. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What privs are required to alter a trigger owned by SYS?
Hi all, I have trigger owned by sys. It is actually an after login trigger. I want certain users to be able to enable/disable this trigger. What privs are required? I have tried alter any trigger and I still get insufficient privs when trying to enable trigger. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tricky SQL Question
Jamadagni, Rajendra wrote: Thanks Jonathan, I'd like to assign the tables to a group, but need to do that periodically. Also what I do is load all tables that belong to a group in a pl/sql table (bulk updates/bulk collects). That's why I don't want to do read-from-table ... do-action BTW this doesn't have to be optimal ... I am just trying to split the load ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Tricky SQL Question Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 16:49 Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj Raj, I have taken good note that 'elegant' is not one of your requirements :-). select decode(sign(8 - mod(rownum - 1, 14)), 1, mod(rownum - 1, 14), 7 - mod(rownum - 1, 7)) GROUP, x.owner, x.name from (select owner, name from your_table order by ana_tm desc) x; This should more or less work, even on 7.2. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fudging outlines
Title: RE: Fudging outlines Chuck, You are right, they say it is 9i, but the update statement to exchange between different plans should (I am guessing) work on 8i as well. Have you already tried it? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/outlines.htm#13135links you to 8i techniques from manuals using outln_pkg. HTH Some Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message-From: Chuck Hamilton [mailto:[EMAIL PROTECTED]Sent: Thursday, March 06, 2003 1:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Fudging outlines The only document I found on metalink was doc id 144194.1 which pertains to 9i. I didn't mention it in my OP but I am using 8i. Also the database I want to do this on is SE not EE so it looks like it's a moot point anyway. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
System tablespace Oracle 9202
Title: System tablespace Oracle 9202 List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM
Oracle Forms Reports Help!
Hi, This could be a very simple question to many of you.. but I would really appreciate if you could help me. I created Forms which call each other. And a Report from the Form. Everything seems to be good. However I am calling the Form through call_form() where I am specifying the entire path of the called form. Is there any way to specify an absolute path (just the name of the called form)?? Similarly... I am running the report with run_product() even here I am specifying the entire path of the Report. Which I don't want to... can I? One more question... How can I automatically view and print the report in pdf format?? This is really urgent! I would really appreciate your help and time. Thanks, Sesi -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sesi Odury INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What privs are required to alter a trigger owned by SYS?
Title: RE: What privs are required to alter a trigger owned by SYS? I'd rather own that trigger by someone other than *sys* ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Subject: What privs are required to alter a trigger owned by SYS? Hi all, I have trigger owned by sys. It is actually an after login trigger. I want certain users to be able to enable/disable this trigger. What privs are required? I have tried alter any trigger and I still get insufficient privs when trying to enable trigger. Thanks Rick *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: remote / as sysdba
*** Comments by BECKER, BILL Thu Mar 06, 2003 -- 03:30:49 PM
I have tried this, but it still does not work.
[EMAIL PROTECTED]select value from v$parameter
2 where name = 'remote_login_passwordfile';
VALUE
---
EXCLUSIVE
(This instance was bounced; not yet using spfiles.)
[EMAIL PROTECTED]select * from v$pwfile_users;
USERNAME SYSDB SYSOP
-- - -
SYSTRUE TRUE
I have verified that the orapwDWQ file exists in $ORACLE_HOME/dbs
with the correct unix perms.
So I connect as sys to the local instance:
[EMAIL PROTECTED]connect sys/syspassword as sysdba
Connected.
[EMAIL PROTECTED]sho user
USER is SYS
But when trying to connect to the remote instance:
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Do both instances need to have remote_login_passwordfile=EXCLUSIVE,
or just the remote instance? Am I missing something else?
*** Original message by Jacques Kilchoer [EMAIL PROTECTED]
As far as I know, it works like this:
You will need to set init parameter REMOTE_LOGIN_PASSWORD_FILE to EXCLUSIVE.
You then can sign on as SYS remotely, or as another user remotely if the
other user has SYSDBA. See the users that have SYSDBA or SYSOPER in
v$pwfile_users.
As the view name suggests, you will also need a password file for the
database, which should be created with the orapwd utility.
$ orapwd
Usage: orapwd file=fname password=password entries=users
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
Once you've done all that, you can connect remotely by saying the following
in SQL*Plus:
connect sys/[EMAIL PROTECTED] as sysdba
But I don't think you will ever be able to do
connect / as sysdba remotely. For one thing, the syntax in SQL*Plus is:
Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
ou logon ::= username[/password][@connect_string] | /
So the logon is either username/[EMAIL PROTECTED] or else / all by
itself. How would you tell SQL*Plus which remote database you want to
connect to? I tried setting TWO_TASK to the tns_alias for the database, but
that didn't help. It seems to me that when you enable remote SYSDBA logins
Oracle will insist on verifying a password for the SYSDBA user in the
password file.
Or is there some clever trick I don't know about?
-Original Message-
From: [EMAIL PROTECTED]
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace
import, where
the userid would be listed in a parfile; I have tried the
same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises
against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing
perl DBI question: fetchrow_array
I have some perl code which selects table data and write it into a file. I
have something like:
---
$dbh-{RowCacheSize} = 1;
open(DATA, $tn) || die Can't open file\n;
$dat=$dbh-prepare(select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
FUNCTION2GENE);
$dat-execute();
while(($row) = $dat-fetchrow_array) {
print DATA $row\n;
}
close(DATA);
-
I am trying to see if there is any way to speed up the process.
So here is my question:
Is fetchrow_array the fatest way to get the data?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Oracle Bug Reports...
IIRC, the ORA-1555 was in another session. Jared Henry Poras [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/06/2003 08:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle Bug Reports... Interesting bug. I'm spacing on one thing though. Don't see where the ORA-1555 is coming from on their test case. Henry ***EXCERPT FROM BUG REPORT *** *** REM Create the user and the tables in question REM create user medic identified by medic; alter user medic default tablespace users temporary tablespace temp; grant dba to medic; connect medic/medic; create table myobjs1 as select * from dba_objects; create table myobjs2 as select * from dba_objects; create table myobjs3 as select * from dba_objects; declare a number; begin for a in 1..3 loop insert into myobjs1 select * from myobjs1; commit; end loop; end; / REM REM Create small rollback segment to use for these trxs, and offline all REM other non-system rollback segments REM create rollback segment small_rbs_to_use tablespace rbs storage (initial 50k next 50k optimal 200k maxextents unlimited); alter rollback segment small_rbs_to_use online; alter rollback segment all other non-system rbs here offline; REM REM Start session 1 REM connect medic/medic set time on update myobjs1 set owner='change1'; commit; REM REM Start session 2 REM connect medic/medic set time on update myobjs1 set owner='change2'; REM REM Start session 3 REM connect medic/medic set time on update myobjs1 set owner='change3'; REM REM Start the Export REM exp medic/medic direct=y tables=\(myobjs1,myobjs2,myobjs3\) log=imp1.log REM You got to be exporting myobjs1 when session 2 and session 3 error out with ORA-1555 (snapshot too old: rollback segment number ...with name SMALL_RBS_TO_USE too small). To accomplish this, startup and execute the SQL statement in the three sessions, and see how long it takes, for session 2 to fail with the ORA-1555 after it was submitted for execution. In my test case, it took a minute and 20 seconds before it failed. Therefore I launch the export session a minute and an 10 seconds after executing the SQL in session 2. -Original Message- [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 1:29 PM To: Multiple recipients of list ORACLE-L .. can be rather interesting at times, when there's time to check out a few. http://metalink.oracle.com/metalink/plsql/showDoc?db=BUGid=2666174 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: show errors doesn't show anything ???
Title: RE: show errors doesn't show anything ??? -Original Message- From: Janet Linsy [mailto:[EMAIL PROTECTED]] I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Could it be that the user has access to compiling the procedure but no access to read the system error table? I was able to see the same result as you when I created a user that had only the system privileges create session and alter any procedure. The user is able to compile procedures but unable to see the error messages. Does a user with SELECT_CATALOG_ROLE see any errors for the procedure in DBA_ERRORS?
Re: Tricky SQL Question
Very cute - But it doesn't really cope well with a few outlying values at the top end of the range. Using double the count to invert the high/low distribution is neat - but only if the distribution is fairly smooth to start with. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 20:33 I have taken good note that 'elegant' is not one of your requirements :-). select decode(sign(8 - mod(rownum - 1, 14)), 1, mod(rownum - 1, 14), 7 - mod(rownum - 1, 7)) GROUP, x.owner, x.name from (select owner, name from your_table order by ana_tm desc) x; This should more or less work, even on 7.2. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tricky SQL Question
Title: RE: Tricky SQL Question Stephane, Nice ... very nice script ... it is very close to what I came up with. Thanks everyone Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Oracle Tools - OEM 4.0
Merely? That's like saying a man standing in front of a tidal wave will merely experience some moisture. At $20K/CPU (I'm assuming that's the number of CPUs the Oracle semi Intelligent Agent runs on), we're looking at another $440K for OEM 4.0??? Ohh, I forgot -- 9iAS comes with OID... /sarcasm Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, March 06, 2003 1:44 PM To: Multiple recipients of list ORACLE-L And merely requires a 9iAS license (and associated DB license to run ), as I currently understand it. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Murali Vallath Sent: 06 March 2003 00:19 To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Tools - OEM 4.0 If you wait for another couple of months you may have OEM 4.0 which has a considerable amount of enhancements including supports for PDA's and the basic version comes with the enterprise edition. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tricky SQL Question
Raj, I may not be offering information useful in solving your specific stats problem. If that's the case, Undskyld. However, this information is certainly useful. This link to the asktom website contains a method for dividing up large tables into ranges of rowids so that multiple sessions can efficiently process different pieces of the same object. Last month, I had the opportunity to see Mr. Kyte demonstrate this during one of his presentations. http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:763874375 0722 -Steve -Original Message- Sent: Thursday, March 06, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 16:49 Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Export data to dbf
What type of file is 'dbf'? Sergey V Dolgov [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/05/2003 09:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Export data to dbf Hello, I can't find out how to export some data (selected results) from oracle to dbf file. -- Best regards, Sergey mailto:[EMAIL PROTECTED] ICQ 160079606 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: System tablespace Oracle 9202
It was only in 9.2 that it was possible to create the system tablespace as locally managed, and rumour says that in 10i system tbs defaults to locally managed. The reason it's not default in 9.2 is most likely because..: 1. Most likely they just didn't update the DBCA default scripts to use this feature when they went from 9i R1 to 9i R2 (read: the scripts was just copied from the 9i R1 source tree) 2. The feature is brand new, so if some problems turn up, most databases wont have it set, because they are created from the DBCA. Regards, Morten Egan Godlewski, Melissa wrote: List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Morten Egan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle on a SAN.... Good, Bad or Proceed with caution?
Hello, We are currently spec'ing out hardware for our new ERP system. It is either going to be Peoplesoft or Oracle. We are also debating on using a SAN rather than attached storage. I'm just looking for any positives/negatives/rules of thumb I should be aware of when running Oracle on a SAN architecture provided that management goes that way. The SAN box we are looking at right now is the EVA3000 from HP (although this isn't a definite) not sure if this matters. TIA, -Scott Stefick ** Scott Stefick UNIX Systems Administrator Oracle Certified Professional DBA Wm. Rainey Harper College 847.925.6130 ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Scott Stefick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Internet File System -- Oracle Taketh Away?
With apologies to Tom Leherer
The project will be a big success
The files accessed through OCS
There is a very large difference between what's possibly happening with iFS and
editions and options. It has always cost more for the Enterprise Edition. The
spatial option has always cost more to license. Here a product which was free with
the EE 8.1.7 release requires licensing 9 iAS for a newer release, and licensing
Collaboration Suite for the newest release of all. If the last sentence is true, I am
disgusted.
We have built our own system for storing and searching conference presentations,
physics papers, and the like based on BLOBS, Intermedia, and our own metadata and
Oracle iAS. It has been very well received. When I began the negotiations with the
user community to upgrade and apply the necessary security patches. The main
objections were from users of the system. For example
2) Early March is an extremely poor choice for an outage of the BaBar
Publications Database. This is the height of the spring conference
season and we will be using it to distribute papers for the
La Thuile conference and both Moriond conferences in March:
17th Les Rencontres De Physique De La Vallee D'Aoste: Results
And Perspectives In Particle Physics, 9-15 Mar 2003, La Thuile,
Aosta Valley, Italy, SPIRES Conf Num: C03/03/09.1,
38th Rencontres De Moriond On Electroweak Interactions And
Unified Theories, 15-22 Mar 2003, Les Arcs, France,
SPIRES Conf Num: C03/03/15.1,
and
38th Rencontres De Moriond On QCD And High-Energy Hadronic
Interactions, 22-29 Mar 2003, Les Arcs, Savoie, France,
SPIRES Conf Num: C03/03/22.3.
--
The system is being used solely by our Bfactory collaboration looking at CP violation.
Another group working on the Gamma-ray Large Area Space Telescope is also keenly
interested and are very close to dropping their commercially built system. Our main
Technical Publications department is also interested.
There are questions on whether the home-built system can provide all that is needed.
Of course no specifications have been released! Poducts, however are being put
through an initial filtering process. I mentioned OCS with the caveat that I would be
wary of any first release from Oracle. I also apprised people of iFS noting my
belief that iFS was free to us. I may now have to retract that statement saying it is
free now, but in the future we'll need to purchase OCS licenses.
What of the people using iFS now in part because it was free for them? Will they need
to license OCS? If one purchases OCS is Oracle going to say in another couple of years
that yet another product is needed?
The Oracle readme files for quite a while have mentioned the demise of partition views
and encouraged uses of such to move to the partitioning option. I had always wondered
if when the view support was pulled whether the option would then be free. My
assumption was that it would. Now I am very unsure.
BTW the security patches were applied :)
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, March 06, 2003 2:04 AM
To: Multiple recipients of list ORACLE-L
Thank you very much. I hope this doesn't mean that future versions of Oracle Files.
neé Oracle Ifs, will only be available by purchasing Oracle Collaboration Suite.
There is always the possibility that this is exactly what could happen, if it hasn't
already.
Notice the way the main Oracle product is divided between Standard and Enterprise.
There are certain features (spatial, for example) which are ONLY available if an
Enterprise licence is purchased. The fact that the spatial stuff will run an a
standard installation indicates (as usual) that these divisions are often
marketing-led. Energetic user groups and customers can have influence in correcting
these anomalies...
peter
edinburgh
..
Ian MacGregor
-Original Message-
Sent: Wednesday, March 05, 2003 7:37 AM
To: Multiple recipients of list ORACLE-L
iFS 1.X.X and below is alternately with the 8.1.7 database and 9iAS 1.0.2.X
9iFS 9.0.1 is with the Database CD Pack {on a seperate CD}
9iFS 9.0.2 is part of 9iAS 9.0.2 {on a seperate CD}
9iFS 9.0.3 has been renamed as Oracle Files and is part of Oracle
Collaboration Suite.
Check the MetaLink certification pages for
Internet File System [which goes upto 9.0.1]
9i Internet Application Server [where 9iFS 9.0.2 is listed under
components for 9iAS 9.0.2]
Oracle Collaboration Suite [where Oracle Files 9.0.3 is listed under
components for OCS 9.0.3]
Hemant
At 04:19 PM 04-03-03 -0800, you wrote:
I believe this is free with the Enterprise Edition of the database
server,
but I have not been
Re: show errors doesn't show anything ???
Hi Janet, Try using 'show errors package package_name' or 'show errors package body package_name' ex. - SQL show errors package body DBMS_ASYNCRPC_PUSH Errors for PACKAGE BODY DBMS_ASYNCRPC_PUSH: LINE/COL ERROR - 103/27 PL/SQL: Statement ignored 103/49 PLS-00302: component 'RESULT_STARTUP_SECONDS' must be declared SQL Darrell Landrum [EMAIL PROTECTED] 03/06/03 12:23PM Hi, I have 9i on Linux Red Hat 2.4.9. A package is invalid after compiling. I did a show errors but nothing shows. How do I see where the error is ?? SQL alter package prv_admin compile; Warning: Package altered with compilation errors. SQL show errors; No errors. Thank you in advance! Janet __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: perl DBI question: fetchrow_array
try using fetchrow_arrayref and see if its faster or less resource
intensive.
On Thu, 6 Mar 2003, gmei wrote:
I have some perl code which selects table data and write it into a file. I
have something like:
---
$dbh-{RowCacheSize} = 1;
open(DATA, $tn) || die Can't open file\n;
$dat=$dbh-prepare(select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
FUNCTION2GENE);
$dat-execute();
while(($row) = $dat-fetchrow_array) {
print DATA $row\n;
}
close(DATA);
-
I am trying to see if there is any way to speed up the process.
So here is my question:
Is fetchrow_array the fatest way to get the data?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: System tablespace Oracle 9202
Title: Message Only if you use DBCA, I believe. Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Godlewski, MelissaSent: Thursday, March 06, 2003 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: System tablespace Oracle 9202 List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM
Re: Tricky SQL Question -- Solved
Very clever ! Can I make a couple of suggestions: You've got a very large number of tables in one group - and the startup time for the analyze might have a big impact on this group - so how about adding in (say) one second to the analyze type in order to cater for startup. Also - how about taking out any tables which individually take up more than the sum(all_times)/count(streams) before running the query on the rest. You might try randomising the ordering for the rest of the tables instead of ordering them by analyze time (since you have a large number and a lot use very small times) - I suspect this would help to flatten out the peaks in the timing, and make the number of tables per stream much more even - so reducing the effect of startup times. I have a very simple-minded (sub-optimal) procedural solution, but I'm trying to work out a way of expressing it non-procedurally. If I succeed I'll let you know. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 06 March 2003 19:38 Okay, I cracked it ... if you are interested, read on ... it is not very optimal, but close to what I want. To me 8 streams is standard, so you'd see 8 as hardcoded. Also I found that select sum(obj_last_analyze_time)/8 from statistics_info / was about 8425 (i.e. ~ 85 seconds). So I wrote this not-so-dynamic sql select group_id, sum(tm1), count(*) from( SELECT obj_owner, obj_name, tm1, case when roll_sum = 8400*1 then 1 else case when roll_sum = 8400*2 then 2 else case when roll_sum = 8400*3 then 3 else case when roll_sum = 8400*4 then 4 else case when roll_sum = 8400*5 then 5 else case when roll_sum = 8400*6 then 6 else case when roll_sum = 8400*7 then 7 else 8 end end end end end end end group_id FROM (SELECT rnum, obj_owner, obj_name, tm1, SUM (tm1) OVER (ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1 FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1 FROM statistics_info ORDER BY obj_last_analyze_time))) ) group by group_id / The output is as follows ... GROUP_ID TOT_TIME TOT_TABLES -- 1 8397 1755 2 8387 667 3 8204 135 4 7984 20 5 8954 7 6 6928 3 7 7113 2 8 11438 1 I'll probably make it dynamic enough ... inside my package ... Cheers Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
Title: RE: remote / as sysdba
If you want to do
connect sys/[EMAIL PROTECTED] as sysdba
then the database corresponding to TNS alias DWQ has to have remote_login_passwordfile EXCLUSIVE, and it needs to have a password file.
It is not clear to me from your e-mail if remote database DWQ has remote_login_passwordfile EXCLUSIVE.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
*** Comments by BECKER, BILL Thu Mar 06, 2003 -- 03:30:49 PM
I have tried this, but it still does not work.
[EMAIL PROTECTED]select value from v$parameter
2 where name = 'remote_login_passwordfile';
VALUE
--
-
EXCLUSIVE
(This instance was bounced; not yet using spfiles.)
[EMAIL PROTECTED]select * from v$pwfile_users;
USERNAME SYSDB SYSOP
-- - -
SYS TRUE TRUE
I have verified that the orapwDWQ file exists in $ORACLE_HOME/dbs
with the correct unix perms.
So I connect as sys to the local instance:
[EMAIL PROTECTED]connect sys/syspassword as sysdba
Connected.
[EMAIL PROTECTED]sho user
USER is SYS
But when trying to connect to the remote instance:
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Do both instances need to have remote_login_passwordfile=EXCLUSIVE,
or just the remote instance? Am I missing something else?
__
__
*** Original message by Jacques Kilchoer [EMAIL PROTECTED]
As far as I know, it works like this:
You will need to set init parameter
REMOTE_LOGIN_PASSWORD_FILE to EXCLUSIVE.
You then can sign on as SYS remotely, or as another user
remotely if the
other user has SYSDBA. See the users that have SYSDBA or SYSOPER in
v$pwfile_users.
As the view name suggests, you will also need a password file for the
database, which should be created with the orapwd utility.
$ orapwd
Usage: orapwd file=fname password=password entries=users
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
Once you've done all that, you can connect remotely by saying
the following
in SQL*Plus:
connect sys/[EMAIL PROTECTED] as sysdba
But I don't think you will ever be able to do
connect / as sysdba remotely. For one thing, the syntax in
SQL*Plus is:
Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
ou logon ::= username[/password][@connect_string] | /
So the logon is either username/[EMAIL PROTECTED] or else / all by
itself. How would you tell SQL*Plus which remote database you want to
connect to? I tried setting TWO_TASK to the tns_alias for the
database, but
that didn't help. It seems to me that when you enable remote
SYSDBA logins
Oracle will insist on verifying a password for the SYSDBA user in the
password file.
Or is there some clever trick I don't know about?
-Original Message-
From: [EMAIL PROTECTED]
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace
import, where
the userid would be listed in a parfile; I have tried the
same logons in
a parfile, and that also fails.
I found a Metalink doc that says the
O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises
against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
Re: Tricky SQL Question
Jonathan Lewis wrote: Very cute - But it doesn't really cope well with a few outlying values at the top end of the range. Using double the count to invert the high/low distribution is neat - but only if the distribution is fairly smooth to start with. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Absolutely right, and in fact Raj's solution (which I received after having posted mine) copes better with this. In fact I have already had the problem with parallel exports, and I think that the best solution would be to have one group for the 3 or 4 megatables you find in every schema, and then distribute the zillion remaining tables along the line I suggested. Something along the famous 95/5 Oracle distribution ... I guess that if you have n threads and one item represents more than 1/n minus a fudge factor it can safely be given a dedicated thread ... But it is too late for me now to do it in a single SQL statement :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
You could logon that way if Oracle allowed it.
sqlplus /@dv03 as sysdba
two different linux boxes, same OS account name on both boxes.
While the previous will result in an ORA-1997 ( sorry, you can't remotely
logon as SYSDBA ), the following works just fine:
sqlplus /@dv03
Jared
Jacques Kilchoer [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/06/2003 12:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: remote / as sysdba
As far as I know, it works like this:
You will need to set init parameter REMOTE_LOGIN_PASSWORD_FILE to
EXCLUSIVE.
You then can sign on as SYS remotely, or as another user remotely if the
other user has SYSDBA. See the users that have SYSDBA or SYSOPER in
v$pwfile_users.
As the view name suggests, you will also need a password file for the
database, which should be created with the orapwd utility.
$ orapwd
Usage: orapwd file=fname password=password entries=users
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
Once you've done all that, you can connect remotely by saying the
following in SQL*Plus:
connect sys/[EMAIL PROTECTED] as sysdba
But I don't think you will ever be able to do
connect / as sysdba remotely. For one thing, the syntax in SQL*Plus is:
Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
ou logon ::= username[/password][@connect_string] | /
So the logon is either username/[EMAIL PROTECTED] or else / all by
itself. How would you tell SQL*Plus which remote database you want to
connect to? I tried setting TWO_TASK to the tns_alias for the database,
but that didn't help. It seems to me that when you enable remote SYSDBA
logins Oracle will insist on verifying a password for the SYSDBA user in
the password file.
Or is there some clever trick I don't know about?
-Original Message-
From: [EMAIL PROTECTED]
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace
import, where
the userid would be listed in a parfile; I have tried the
same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same doc strongly advises
against setting
this to true.
So, has anyone found a way to use the / as sysdba logon remotely?
(without setting the O7 parameter to true)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: perl DBI question: fetchrow_array
1) fetchrow_arrayref is faster than fetchrow_array, as Alex has noted.
2) I see you've already set RowCacheSize. Anecdotal evidence ( not just
mine)
suggests that the diminished returns obtained by setting this 100 aren't
worth it.
3) try selectall_arrayref if you're data is not really large. 'really
large' depends
on your environment.
4) join DBI users list, found at lists.perl.org.
Jared
Alex [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/06/2003 01:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: perl DBI question: fetchrow_array
try using fetchrow_arrayref and see if its faster or less resource
intensive.
On Thu, 6 Mar 2003, gmei wrote:
I have some perl code which selects table data and write it into a file.
I
have something like:
---
$dbh-{RowCacheSize} = 1;
open(DATA, $tn) || die Can't open file\n;
$dat=$dbh-prepare(select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
FUNCTION2GENE);
$dat-execute();
while(($row) = $dat-fetchrow_array) {
print DATA $row\n;
}
close(DATA);
-
I am trying to see if there is any way to speed up the process.
So here is my question:
Is fetchrow_array the fatest way to get the data?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
The remote instance must have remote_login_passwordfile=EXCLUSIVE
Any local instances, whether or not you are logged into them, are
unimportant.
In this case:
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
I would tend to think that you're either not connecting to the database
you intend,
or you've mistyped the password.
Does connect sys/[EMAIL PROTECTED] as sysdba work?
Jared
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/06/2003 01:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: remote / as sysdba
*** Comments by BECKER, BILL Thu Mar 06, 2003 -- 03:30:49 PM
I have tried this, but it still does not work.
[EMAIL PROTECTED]select value from v$parameter
2 where name = 'remote_login_passwordfile';
VALUE
---
EXCLUSIVE
(This instance was bounced; not yet using spfiles.)
[EMAIL PROTECTED]select * from v$pwfile_users;
USERNAME SYSDB SYSOP
-- - -
SYSTRUE TRUE
I have verified that the orapwDWQ file exists in $ORACLE_HOME/dbs
with the correct unix perms.
So I connect as sys to the local instance:
[EMAIL PROTECTED]connect sys/syspassword as sysdba
Connected.
[EMAIL PROTECTED]sho user
USER is SYS
But when trying to connect to the remote instance:
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Do both instances need to have remote_login_passwordfile=EXCLUSIVE,
or just the remote instance? Am I missing something else?
*** Original message by Jacques Kilchoer [EMAIL PROTECTED]
As far as I know, it works like this:
You will need to set init parameter REMOTE_LOGIN_PASSWORD_FILE to
EXCLUSIVE.
You then can sign on as SYS remotely, or as another user remotely if the
other user has SYSDBA. See the users that have SYSDBA or SYSOPER in
v$pwfile_users.
As the view name suggests, you will also need a password file for the
database, which should be created with the orapwd utility.
$ orapwd
Usage: orapwd file=fname password=password entries=users
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
Once you've done all that, you can connect remotely by saying the
following
in SQL*Plus:
connect sys/[EMAIL PROTECTED] as sysdba
But I don't think you will ever be able to do
connect / as sysdba remotely. For one thing, the syntax in SQL*Plus is:
Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
ou logon ::= username[/password][@connect_string] | /
So the logon is either username/[EMAIL PROTECTED] or else / all by
itself. How would you tell SQL*Plus which remote database you want to
connect to? I tried setting TWO_TASK to the tns_alias for the database,
but
that didn't help. It seems to me that when you enable remote SYSDBA logins
Oracle will insist on verifying a password for the SYSDBA user in the
password file.
Or is there some clever trick I don't know about?
-Original Message-
From: [EMAIL PROTECTED]
env: Oracle 9.2.0.2 on Solaris 9.
Does anyone know of a way to use the / as sysdba logon remotely?
(to a separate Oracle instance on a separate machine)
Other remote user logons work OK.
I have tried several variations from sqlplus, such as
[EMAIL PROTECTED]connect /@DWQ as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED] as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
[EMAIL PROTECTED]connect sys/exr_sys as [EMAIL PROTECTED]
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where logon ::= username[/password][@connect_string] | /
[EMAIL PROTECTED]connect sys/[EMAIL PROTECTED]
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
I also find I cannot even connect sys/syspassword locally:
[EMAIL PROTECTED]connect sys/sys_password
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
This does work locally, but not remotely:
[EMAIL PROTECTED]connect sys/sys_password as sysdba
Connected.
I am a member of the dba group on both platforms.
I have verified that I am using the correct sys_password for sys
on the remote instance.
Eventually, I want to do a remote transportable tablespace
import, where
the userid would be listed in a parfile; I have tried the
same logons in
a parfile, and that also fails.
I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
must be true to do this, but the same
RE: System tablespace Oracle 9202
Title: Message In your create database script, make sure you have EXTENT MANAGEMENT LOCAL after the sysdate datafile clause. Here's an example - CREATE DATABASE abcUSER SYS IDENTIFIED BY xyzUSER SYSTEM IDENTIFIED BY xyzDATAFILE '/u10/oradata/abc/system01.dbf' SIZE 350M REUSEEXTENT MANAGEMENT LOCALDEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u20/oradata/abc/temp01.dbf' SIZE 8001M REUSEUNDO TABLESPACE "UNDOTBS" DATAFILE '/u30/oradata/abc/undotbs01.dbf' SIZE 10230M REUSE... -Original Message-From: Pete Sharman [mailto:[EMAIL PROTECTED]Sent: Thursday, March 06, 2003 1:34 PMTo: Multiple recipients of list ORACLE-LSubject: RE: System tablespace Oracle 9202 Only if you use DBCA, I believe. Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Godlewski, MelissaSent: Thursday, March 06, 2003 12:49 PMTo: Multiple recipients of list ORACLE-LSubject: System tablespace Oracle 9202 List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM
RE: System tablespace Oracle 9202
Title: RE: System tablespace Oracle 9202 Metalink Note:175434.1 has some interesting information on the subject. For example, it reminds you that if the SYSTEM tablespace is LOCAL, then you cannot create a tablespace with extent management DICTIONARY. You can tell the database creation assistant (DBCA) to create a locally managed system tablespace, and actually it's the default setting for DBCA in 9.2 The 9.2 SQL reference manual has the following caveat: if your compatibility parameter is set to less than 9.0 then by default tablespaces will be created with extent management dictionary. Also from the 9.2 SQL reference manual: -- If you do not specify the extent_management_clause, then Oracle interprets the COMPATIBLE setting, the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management. If the COMPATIBLE initialization parameter is less than 9.0.0, then Oracle creates a dictionary managed tablespace. If COMPATIBLE = 9.0.0 or higher: If you do not specify the DEFAULT storage_clause, then Oracle creates a locally managed autoallocated tablespace. If you did specify the DEFAULT storage_clause: If you specified the MINIMUM EXTENT clause, then Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace. If you did not specify MINIMUM EXTENT clause, then Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated. -- It's still not clear to me why, when you omit EXTENT MANAGEMENT LOCAL for the SYSTEM tablespace, it defaults to DICTIONARY in 9.2, when all other tablespaces default to LOCAL. The SQL Reference documentation doesn't seem to mention it. -Original Message- From: Morten Egan [mailto:[EMAIL PROTECTED]] It was only in 9.2 that it was possible to create the system tablespace as locally managed, and rumour says that in 10i system tbs defaults to locally managed. The reason it's not default in 9.2 is most likely because..: 1. Most likely they just didn't update the DBCA default scripts to use this feature when they went from 9i R1 to 9i R2 (read: the scripts was just copied from the 9i R1 source tree) 2. The feature is brand new, so if some problems turn up, most databases wont have it set, because they are created from the DBCA. Godlewski, Melissa wrote: List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM
Re: Oracle on a SAN.... Good, Bad or Proceed with caution?
Perhaps you'll enjoy James Morle's Sane SAN paper on www.OakTable.net - if you haven't seen it already. Best regards, Mogens Scott Stefick wrote: Hello, We are currently spec'ing out hardware for our new ERP system. It is either going to be Peoplesoft or Oracle. We are also debating on using a SAN rather than attached storage. I'm just looking for any positives/negatives/rules of thumb I should be aware of when running Oracle on a SAN architecture provided that management goes that way. The SAN box we are looking at right now is the EVA3000 from HP (although this isn't a definite) not sure if this matters. TIA, -Scott Stefick ** Scott Stefick UNIX Systems Administrator Oracle Certified Professional DBA Wm. Rainey Harper College 847.925.6130 ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
Title: RE: remote / as sysdba I forgot that you could do that. I never liked remote os authentication (is it still possible to easily fool a client into thinking you're someone else?), and I would like it even less if it allowed you to sign on as SYSDBA without a password. The best security is still having different passwords for everything, and if there are too many passwords to remember, just write them down on a post-it note stuck to your monitor. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] You could logon that way if Oracle allowed it. sqlplus /@dv03 as sysdba two different linux boxes, same OS account name on both boxes. While the previous will result in an ORA-1997 ( sorry, you can't remotely logon as SYSDBA ), the following works just fine: sqlplus /@dv03
RE: System tablespace Oracle 9202
Actually, I think the reason it's not default is because we don't introduce something as a new default (generally!) without giving you a release notice e.g. in reverse, we warned people more than one release beforehand that SVRMGRL would be removed. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Egan Sent: Thursday, March 06, 2003 1:59 PM To: Multiple recipients of list ORACLE-L It was only in 9.2 that it was possible to create the system tablespace as locally managed, and rumour says that in 10i system tbs defaults to locally managed. The reason it's not default in 9.2 is most likely because..: 1. Most likely they just didn't update the DBCA default scripts to use this feature when they went from 9i R1 to 9i R2 (read: the scripts was just copied from the 9i R1 source tree) 2. The feature is brand new, so if some problems turn up, most databases wont have it set, because they are created from the DBCA. Regards, Morten Egan Godlewski, Melissa wrote: List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Morten Egan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Internet File System -- Oracle Taketh Away?
An interesting feature of partition views in Oracle 9. If you create a thing that matches the requirement for an old v7 partition view, and set the parameter partition_views_enabled = FALSE You still find that partition elimination can occur, better than it used to in v7, and the execution plan will have the line VIEW (PARTITION) just like the good old days. So in this case, partition views seem to be deprecated because they aren't special any more. (But roll on v10) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - The Oracle readme files for quite a while have mentioned the demise of partition views and encouraged uses of such to move to the partitioning option. I had always wondered if when the view support was pulled whether the option would then be free. My assumption was that it would. Now I am very unsure. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
At one time you could set the 'ORACLE_USERNAME=SYSTEM' variable in your oracle.ini file, and log into any database as SYSTEM ( without a password ) as long as REMOTE_OS_AUTHEN=true. That was obviously some years ago, and I don't know if that is still possible. I would have hoped that such an obvious hole was plugged years ago. It seems to me that it was, but I don't recall details. Jared Jacques Kilchoer [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/06/2003 03:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: remote / as sysdba I forgot that you could do that. I never liked remote os authentication (is it still possible to easily fool a client into thinking you're someone else?), and I would like it even less if it allowed you to sign on as SYSDBA without a password. The best security is still having different passwords for everything, and if there are too many passwords to remember, just write them down on a post-it note stuck to your monitor. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] You could logon that way if Oracle allowed it. sqlplus /@dv03 as sysdba two different linux boxes, same OS account name on both boxes. While the previous will result in an ORA-1997 ( sorry, you can't remotely logon as SYSDBA ), the following works just fine: sqlplus /@dv03 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: remote / as sysdba
Title: RE: remote / as sysdba Thank you for the information. I thought the security issues were more fundamental. For example if my database has remote os authentication (with prefix OPS$), and I know that there is a user called OPS$JSTILL, then I can change the Windows Registry on my client to enable me to logon to the database as OPS$JSTILL. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] At one time you could set the 'ORACLE_USERNAME=SYSTEM' variable in your oracle.ini file, and log into any database as SYSTEM ( without a password ) as long as REMOTE_OS_AUTHEN=true. That was obviously some years ago, and I don't know if that is still possible. I would have hoped that such an obvious hole was plugged years ago. It seems to me that it was, but I don't recall details.
Re: System tablespace Oracle 9202
What platform is this? I can't be sure (because my laptop is miles away at the moment) but I think on 9.2 on NT when I used the creation assistant to generate scripts for me, SYSTEM was an lmt. hth connor --- Godlewski, Melissa [EMAIL PROTECTED] wrote: List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question ???
A ZERO length varchar is treated as NULL so your second query should be select count(*) from cli_clients where trim(client_company) is null and cli_id in (257, 396, 727); At 12:09 PM 3/6/2003 -0800, you wrote: Hi, I got a SQL question (9i on Red Hat), commands shown below. The first sql returns 3 rows with value 1, so trim(client_company) = '', how come the 2nd sql doesn't return anything?? SQL select decode(trim(client_company), '', 1, ' ', 2, null, 3, 4) from cli_clients where cli_id in (257, 396, 727); DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4) --- 1 1 1 3 rows selected. SQL select count(*) from cli_clients where trim(client_company) = '' and cli_id in (257, 396, 727); COUNT(*) - 0 1 row selected. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: System tablespace Oracle 9202
mine on Redhat Linux is LMT as well and I am certain I didn't do anything special to create it that way. At 04:43 PM 3/6/2003 -0800, you wrote: What platform is this? I can't be sure (because my laptop is miles away at the moment) but I think on 9.2 on NT when I used the creation assistant to generate scripts for me, SYSTEM was an lmt. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: System tablespace Oracle 9202
DBCA, by default in 92 will create SYSTEM as LMT. There is an option to do dictionary if you prefer. CREATE DATABASE default is a dictionary managed SYSTEM tablespace with LMT optioinal. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/6/2003 6:43 PM What platform is this? I can't be sure (because my laptop is miles away at the moment) but I think on 9.2 on NT when I used the creation assistant to generate scripts for me, SYSTEM was an lmt. hth connor --- Godlewski, Melissa [EMAIL PROTECTED] wrote: List, System tablespace for Oracle 9iR2 is defaulting to Dictionary Extent Management. I thought all tablespaces defaulted to Local in this release. Thu Mar 6 15:29:43 2003 create tablespace SYSTEM datafile '/oradb/DEV/system/system01.dbf' size 201M reuse default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online Thu Mar 6 15:29:57 2003 Completed: create tablespace SYSTEM datafile '/oradb/DEV Thu Mar 6 15:29:57 2003 create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN resync of catalog to controlfile
Joe, Thinking outside the box a little... How about if you query the recovery catalog views on RMAN repository database, using a technique of SQL-generating-RMAN? Connecting to the recovery catalog database, you could query the RC_BACKUP_CONTROLFILE view to generate a series of CATALOG CONTROLFILECOPY commands, query the RC_BACKUP_DATAFILE view to generate a series of CATALOG DATAFILECOPY commands, and query the RC_BACKUP_REDOLOG view to generate a series of CATALOG ARCHIVELOG commands. Of course, you'd have to join all these views to RC_BACKUP_SETS and RC_BACKUP_PIECES in order to get the actual filenames/file handle-names as well, so query wouldn't be quite trivial, but nothing too crazy. So, your queries in SQL*Plus against the RMAN recovery catalog database would spool out a script of RMAN commands. Then exit SQL*Plus and connect to RMAN in NOCATALOG mode to run the generated commands against the target database. I'd be concerned about the after-effect of doing this when you finally do a subsequent RESYNC CATALOG, but until I get a chance to test this sometime next week, perhaps some enterprising soul might consider giving it a try in a test environment? What do you think? -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 12:19 PM the catalog has all of the current backup info, so if i lose the repository(before taking a backup after rebuilding the controlfile), I'm SOL. I logged a tar and oracle's response is, no way to push catalog info back into the controlfile. joe Joe - I'm confused. If you rebuild the controlfile, what good is the backup information stored in the catalog? Other than maybe deciding to revert to a time before the rebuild, and you're going to need the catalog for that anyway. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Robert, and all of you other RMAN gurus. scenario 1: repository unavailable, so rman backup was done using controlfile only. upon later successful connection to repository, the backup info was pushed from controlfile to repository(YEA). scenario 2: I have to rebuild the controlfile and have a rman repository. so i do a resync in rman, rebuild the controlfile and connect back to repository, doing a resync HOPING that the controlfile gets updated with info from repository, no such luck. I did a dump of the controlfile(via alter session set events 'immediate trace name controlf level 10'), looking for the section on BACKUP SET RECORDS and BACKUP PIECE RECORDS and there is nothing there. so my question is this: is the resync only a one way push, i understand oracle's mentality about not overwriting the backup records in the controlfile since that should be the true information, but is there a way to force oracle/rman to push the repository info back into the controlfile, i've not found a solution for this. if anyone is interested in the dump files, let me know and i'll make them available on the web so you can see what I'm talking about. thanks, joe Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services
Re: RMAN resync of catalog to controlfile
Tim, I like the concept alot and will put it on my list to try out in the near future. thanks for the ideas, I've not tried any of the commands but will definitely try them. thanks, joe Tim Gorman wrote: Joe, Thinking outside the box a little... How about if you query the recovery catalog views on RMAN repository database, using a technique of SQL-generating-RMAN? Connecting to the recovery catalog database, you could query the RC_BACKUP_CONTROLFILE view to generate a series of CATALOG CONTROLFILECOPY commands, query the RC_BACKUP_DATAFILE view to generate a series of CATALOG DATAFILECOPY commands, and query the RC_BACKUP_REDOLOG view to generate a series of CATALOG ARCHIVELOG commands. Of course, you'd have to join all these views to RC_BACKUP_SETS and RC_BACKUP_PIECES in order to get the actual filenames/file handle-names as well, so query wouldn't be quite trivial, but nothing too crazy. So, your queries in SQL*Plus against the RMAN recovery catalog database would spool out a script of RMAN commands. Then exit SQL*Plus and connect to RMAN in NOCATALOG mode to run the generated commands against the target database. I'd be concerned about the after-effect of doing this when you finally do a subsequent RESYNC CATALOG, but until I get a chance to test this sometime next week, perhaps some enterprising soul might consider giving it a try in a test environment? What do you think? -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 12:19 PM the catalog has all of the current backup info, so if i lose the repository(before taking a backup after rebuilding the controlfile), I'm SOL. I logged a tar and oracle's response is, no way to push catalog info back into the controlfile. joe Joe - I'm confused. If you rebuild the controlfile, what good is the backup information stored in the catalog? Other than maybe deciding to revert to a time before the rebuild, and you're going to need the catalog for that anyway. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Robert, and all of you other RMAN gurus. scenario 1: repository unavailable, so rman backup was done using controlfile only. upon later successful connection to repository, the backup info was pushed from controlfile to repository(YEA). scenario 2: I have to rebuild the controlfile and have a rman repository. so i do a resync in rman, rebuild the controlfile and connect back to repository, doing a resync HOPING that the controlfile gets updated with info from repository, no such luck. I did a dump of the controlfile(via alter session set events 'immediate trace name controlf level 10'), looking for the section on BACKUP SET RECORDS and BACKUP PIECE RECORDS and there is nothing there. so my question is this: is the resync only a one way push, i understand oracle's mentality about not overwriting the backup records in the controlfile since that should be the true information, but is there a way to force oracle/rman to push the repository info back into the controlfile, i've not found a solution for this. if anyone is interested in the dump files, let me know and i'll make them available on the web so you can see what I'm talking about. thanks, joe Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please
Re[2]: Export data to dbf
Hello Jared, Friday, March 7, 2003, 2:09:33 AM, you wrote: JSrc What type of file is 'dbf'? I think I should use ODBC but I don't know how to do it. JSrc Hello, JSrc I can't find out how to export some data (selected results) from oracle to JSrc dbf file. -- Best regards, Sergeymailto:[EMAIL PROTECTED] ICQ 160079606 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN resync of catalog to controlfile
A facinating idea Tim no time this weekend, but I plan on playing with the idea if someone else dosen't. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/6/2003 9:23 PM Joe, Thinking outside the box a little... How about if you query the recovery catalog views on RMAN repository database, using a technique of SQL-generating-RMAN? Connecting to the recovery catalog database, you could query the RC_BACKUP_CONTROLFILE view to generate a series of CATALOG CONTROLFILECOPY commands, query the RC_BACKUP_DATAFILE view to generate a series of CATALOG DATAFILECOPY commands, and query the RC_BACKUP_REDOLOG view to generate a series of CATALOG ARCHIVELOG commands. Of course, you'd have to join all these views to RC_BACKUP_SETS and RC_BACKUP_PIECES in order to get the actual filenames/file handle-names as well, so query wouldn't be quite trivial, but nothing too crazy. So, your queries in SQL*Plus against the RMAN recovery catalog database would spool out a script of RMAN commands. Then exit SQL*Plus and connect to RMAN in NOCATALOG mode to run the generated commands against the target database. I'd be concerned about the after-effect of doing this when you finally do a subsequent RESYNC CATALOG, but until I get a chance to test this sometime next week, perhaps some enterprising soul might consider giving it a try in a test environment? What do you think? -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 12:19 PM the catalog has all of the current backup info, so if i lose the repository(before taking a backup after rebuilding the controlfile), I'm SOL. I logged a tar and oracle's response is, no way to push catalog info back into the controlfile. joe Joe - I'm confused. If you rebuild the controlfile, what good is the backup information stored in the catalog? Other than maybe deciding to revert to a time before the rebuild, and you're going to need the catalog for that anyway. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Robert, and all of you other RMAN gurus. scenario 1: repository unavailable, so rman backup was done using controlfile only. upon later successful connection to repository, the backup info was pushed from controlfile to repository(YEA). scenario 2: I have to rebuild the controlfile and have a rman repository. so i do a resync in rman, rebuild the controlfile and connect back to repository, doing a resync HOPING that the controlfile gets updated with info from repository, no such luck. I did a dump of the controlfile(via alter session set events 'immediate trace name controlf level 10'), looking for the section on BACKUP SET RECORDS and BACKUP PIECE RECORDS and there is nothing there. so my question is this: is the resync only a one way push, i understand oracle's mentality about not overwriting the backup records in the controlfile since that should be the true information, but is there a way to force oracle/rman to push the repository info back into the controlfile, i've not found a solution for this. if anyone is interested in the dump files, let me know and i'll make them available on the web so you can see what I'm talking about. thanks, joe Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Joseph S Testa Chief Technology Officer
RE: Fudging outlines
Title: RE: Fudging outlines You definitely can do it in 8i as well. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 56 Denver - Hotsos Clinic101, Mar 2527 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, Rajendra Sent: Thursday, March 06, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Fudging outlines Chuck, You are right, they say it is 9i, but the update statement to exchange between different plans should (I am guessing) work on 8i as well. Have you already tried it? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/outlines.htm#13135links you to 8i techniques from manuals using outln_pkg. HTH Some Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Chuck Hamilton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Subject: Re: Fudging outlines The only document I found on metalink was doc id 144194.1 which pertains to 9i. I didn't mention it in my OP but I am using 8i. Also the database I want to do this on is SE not EE so it looks like it's a moot point anyway.
RE: RMAN resync of catalog to controlfile
Sounds do-able and cool, don't have the time right now though... Maybe in 2 or 3 weeks though... Let me know what the outcome is... Clint -Original Message- Sent: Friday, March 07, 2003 6:09 AM To: Multiple recipients of list ORACLE-L A facinating idea Tim no time this weekend, but I plan on playing with the idea if someone else dosen't. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/6/2003 9:23 PM Joe, Thinking outside the box a little... How about if you query the recovery catalog views on RMAN repository database, using a technique of SQL-generating-RMAN? Connecting to the recovery catalog database, you could query the RC_BACKUP_CONTROLFILE view to generate a series of CATALOG CONTROLFILECOPY commands, query the RC_BACKUP_DATAFILE view to generate a series of CATALOG DATAFILECOPY commands, and query the RC_BACKUP_REDOLOG view to generate a series of CATALOG ARCHIVELOG commands. Of course, you'd have to join all these views to RC_BACKUP_SETS and RC_BACKUP_PIECES in order to get the actual filenames/file handle-names as well, so query wouldn't be quite trivial, but nothing too crazy. So, your queries in SQL*Plus against the RMAN recovery catalog database would spool out a script of RMAN commands. Then exit SQL*Plus and connect to RMAN in NOCATALOG mode to run the generated commands against the target database. I'd be concerned about the after-effect of doing this when you finally do a subsequent RESYNC CATALOG, but until I get a chance to test this sometime next week, perhaps some enterprising soul might consider giving it a try in a test environment? What do you think? -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 12:19 PM the catalog has all of the current backup info, so if i lose the repository(before taking a backup after rebuilding the controlfile), I'm SOL. I logged a tar and oracle's response is, no way to push catalog info back into the controlfile. joe Joe - I'm confused. If you rebuild the controlfile, what good is the backup information stored in the catalog? Other than maybe deciding to revert to a time before the rebuild, and you're going to need the catalog for that anyway. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L Robert, and all of you other RMAN gurus. scenario 1: repository unavailable, so rman backup was done using controlfile only. upon later successful connection to repository, the backup info was pushed from controlfile to repository(YEA). scenario 2: I have to rebuild the controlfile and have a rman repository. so i do a resync in rman, rebuild the controlfile and connect back to repository, doing a resync HOPING that the controlfile gets updated with info from repository, no such luck. I did a dump of the controlfile(via alter session set events 'immediate trace name controlf level 10'), looking for the section on BACKUP SET RECORDS and BACKUP PIECE RECORDS and there is nothing there. so my question is this: is the resync only a one way push, i understand oracle's mentality about not overwriting the backup records in the controlfile since that should be the true information, but is there a way to force oracle/rman to push the repository info back into the controlfile, i've not found a solution for this. if anyone is interested in the dump files, let me know and i'll make them available on the web so you can see what I'm talking about. thanks, joe Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
