RE: ora-305500
Thank you, Lynda, it worked. Aleem -Original Message- Sent: Saturday, October 05, 2002 7:23 PM To: Multiple recipients of list ORACLE-L Subject:RE: ora-305500 It is the line that contains (--) in the beginning -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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).
remove move a user
Hi, what's the best method to 1) remove a user absolutely. I want to remove the user's tables, indexes, ... etc. 2) move a user absolutely to another tablespace. Thanks, Murat -- Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez. This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. does not accept legal responsibility for the contents of this message. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MURAT BALKAS 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: remove move a user
Murat, 1) remove a user absolutely. I want to remove the user's tables, indexes, ... etc. DROP USER XYZ CASCADE; 2) move a user absolutely to another tablespace. EXPORT/IMPORT in user mode. Sanjay -- Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez. This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. does not accept legal responsibility for the contents of this message. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MURAT BALKAS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: G Sanjay 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: Difference between connect internal and connect / as sysdba in sqlplus
I use Oracle 8.1.7. I tried install patchset 8.1.7.3 and there is written: ... Invoke SQL*Plus (sqlplus), connect as internal and run the following SQL scripts with event 10520 set. NOTE: This event is not intended for regular database operation and MUST be turned off as indicated below: ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; ?/rdbms/admin/catalog.sql ?/rdbms/admin/catproc.sql ?/rdbms/admin/catrep.sql (This only needs to be run if you are using symmetric/advanced replication. This is not necessary for sites using dblinks and read-only snapshots if symmetric/advanced replication is not installed) CONNECT / AS SYSDBA; update obj$ set status=5 where type#=29 and owner#!=0; commit; ... Excuse me for long quotation. Why first connect internal and second connect / as sysdba? For a multiplicity only ? If it's not, what's difference between thease? 'connect internal' is no longer supported in 9i+ 'connect / as sysdba' is the replacement. --- Mikhail Ivanov [EMAIL PROTECTED] wrote: What is difference between connect internal and connect / as sysdba in sqlplus ? __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Best regards Mikhail Ivanov W±ë zØ^¡÷âr¥9,BÅm¶ÿà (§Ú©Êëa¢³¢ÚÈ4Dæö§¢û]z¶«¸V +r5ëp¢¹z»âqëçÎwó9Öm§ÿðÃÚµÈÉÊI©Ãè( +©b~ç£X§X¬µ©ÝÁæá¢Ëb ®øzÄèDCTL¨º» ÷ë¢kaÉX§X¬¶Ç§u©Ä1¨¥ë,j ¸¬´k«¹ör+rr§¢×\ ²¥)à¡òâ²Ñ®®æ§v)í é²Æ xb)Üç^jX§yÊ'µ¨§x5%9,Bè®Ø^©¡ùX§X¬·*.Á©í¶Þ騽ç_®¢éÉ©l¢Ç§vØ^ BÏr¦jw_¢º- êâú+«b¢ybë.nÇ+¸§
how to retrieve numeric values only from a varchar2?
Hi,Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only.I have tried the following piece of pl/sql, unsuccessfullydeclarebeginfor i in 1..999loopupdate tdcr set features=db_connect.e_features(132) where label = to_char(i,'099');end loop;end;/ Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA
Re: Shutdown Immediate
Two things to check: 1. Check dba_jobs to see if any jobs are running like an analyze. I've had this happen to me. I tried to do a shutdown immediate but database is waiting for analyze job to finish (several hours). 2. Check if intelligent agent is running. You should shutdown intelligent agent before trying to do a shutdown immediate. How check for running intelligent agent ? HTH, Gerardo -- Mikhail Ivanov W±ë zØ^¡÷âr¥9,BÅm¶ÿà (§Ú©Êëa¢³¢ÚÈ4Dæö§¢û]z¶«¸V +r5ëp¢¹z»âqëçÎwó9Öm§ÿðÃÚµÈÉÊI©Ãè( +©b~ç£X§X¬µ©ÝÁæá¢Ëb ®øzÄèDCTL¨º» ÷ë¢kaÉX§X¬¶Ç§u©Ä1¨¥ë,j ¸¬´k«¹ör+rr§¢×\ ²¥)à¡òâ²Ñ®®æ§v)í é²Æ xb)Üç^jX§yÊ'µ¨§x5%9,Bè®Ø^©¡ùX§X¬·*.Á©í¶Þ騽ç_®¢éÉ©l¢Ç§vØ^ BÏr¦jw_¢º- êâú+«b¢ybë.nÇ+¸§
how to clone a DB ?
Guys , i want to clone DB1 on host HOST1 to HOST2. i have installed oracle on HOST2 with the default DB created. the disk layout on HOST1 is different from that of HOST2. the environment is 8.1.6/win2k. the files to be copied are all datafiles , log files and control files. ami right ? since the disk layout is differnet , how should i rename the datafiles/log files/control files ? can someone throw light and explain me all the steps involved in detail ? Thanx in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora 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 to retrieve numeric values only from a varchar2?
I think if you try ltrim(to_char(i,'099')); it will remove a leading blank. There is I'm sure a way of doing it explicitly with the format of the to_char but I can't remember what it is. Iain Nicoll -Original Message- Sent: Monday, October 07, 2002 10:13 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only. I have tried the following piece of pl/sql, unsuccessfully declare begin for i in 1..999 loop update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099'); end loop; end; / Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) 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 to retrieve numeric values only from a varchar2?
Use substr(to_char(i, '099'), 2). This is will truncate the first character reserved for the sign. Regards Naveen -Original Message-From: Robert Morrison [mailto:[EMAIL PROTECTED]]Sent: Monday, October 07, 2002 2:43 PMTo: Multiple recipients of list ORACLE-LSubject: how to retrieve numeric values only from a varchar2? Hi,Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only.I have tried the following piece of pl/sql, unsuccessfullydeclarebeginfor i in 1..999loopupdate tdcr set features=db_connect.e_features(132) where label = to_char(i,'099');end loop;end;/ Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA
RE: how to clone a DB ?
1. ALTER DATABASE BACKUP CONTROLFILE TO TRACE 2. Either use the hot backup technique to copy the datafiles or shutdown the DB and copy all the datafiles. 3. Copy the Datafiles and logs to the appropriate disks in HOST2 4. Create the instance using the copied PFILE and ORADIM 5. Change the names of the datafiles and redo-logs in the create controlfile statement generated by step 1 6. Start the instance without mounting - STARTUP NOMOUNT 7. Create controlfile using the create control file statement. 8. If you used the consistent set of datafiles open database resetlogs or else recover database after applying the archived redo-logs. Hope i didn't miss anything ;-) Regards Naveen -Original Message- Sent: Monday, October 07, 2002 3:03 PM To: Multiple recipients of list ORACLE-L Guys , i want to clone DB1 on host HOST1 to HOST2. i have installed oracle on HOST2 with the default DB created. the disk layout on HOST1 is different from that of HOST2. the environment is 8.1.6/win2k. the files to be copied are all datafiles , log files and control files. ami right ? since the disk layout is differnet , how should i rename the datafiles/log files/control files ? can someone throw light and explain me all the steps involved in detail ? Thanx in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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 to retrieve numeric values only from a varchar2?
Re: remove move a user
To remove a user: drop user xxx cascade; To move a user to another tablespace: Ensure user has enough quota on tablespace yyy Set user's default tablespace alter user xxx default tablespace yyy; For each table owned by xxx, alter table xxx.aaa move tablespace yyy; Dennis MURAT BALKAS wrote: Hi, what's the best method to 1) remove a user absolutely. I want to remove the user's tables, indexes, ... etc. 2) move a user absolutely to another tablespace. Thanks, Murat -- Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez. This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. does not accept legal responsibility for the contents of this message. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MURAT BALKAS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler 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 to retrieve numeric values only from a varchar2?
Thanks, that worked. Rob -Original Message- Sent: 07 October 2002 11:39 To: Multiple recipients of list ORACLE-L I think if you try ltrim(to_char(i,'099')); it will remove a leading blank. There is I'm sure a way of doing it explicitly with the format of the to_char but I can't remember what it is. Iain Nicoll -Original Message- Sent: Monday, October 07, 2002 10:13 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only. I have tried the following piece of pl/sql, unsuccessfully declare begin for i in 1..999 loop update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099'); end loop; end; / Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Morrison 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 to clone a DB ?
Pull out the docs(or read online at technet.oracle.com), all the steps you need are there. joe oraora oraora wrote: Guys , i want to clone DB1 on host HOST1 to HOST2. i have installed oracle on HOST2 with the default DB created. the disk layout on HOST1 is different from that of HOST2. the environment is 8.1.6/win2k. the files to be copied are all datafiles , log files and control files. ami right ? since the disk layout is differnet , how should i rename the datafiles/log files/control files ? can someone throw light and explain me all the steps involved in detail ? Thanx in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe 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).
Warehouse design: snowflake vs star schemas
Dear Data Warehouse Experts, Could you please share you experience with snowflake and star data models. How do you choose between them? What problems may arise? Is star schema preferred for Oracle? What is users' experience with those schemas? Which one they like more and why? Which one is easier to implement and easier ETL? Are there other patterns for DW? I would like to check/confirm/change my possibly subjective point of view to more objective perspective. I would appreciate your thoughts or links where I can review practical conclusions. TIA, Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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: BACKUP database question
Without a repository, you can't do incremental backups. You lose a lot of the functionality of rman. Have a look at the 8.1.7 Backup and Recovery Guide. It should give you some insights that you can pass on to the powers that be. Being a quasi-state agency they should be glad because rman is free. You already have it. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 4:48 PM Thanks, Tom and Ruth and others yet to reply, We to are a quasi-state agency but the Oracle licensing is under a state controlled agency and must be purchased from them. If I use my Linux/8i test platform for company business then I must purchase a license. Although a 10 named license is not that expensive, I still have to get it past the Sr.VP (also SR.VP of finance - a CPA) Like a snowball in hel*. There is some risk envolved with the disk farm concept for both boxes but I think that is the way I will have to go. I will create a rman test repository on my Linux box and get the bugs worked out before I deploy to the production environment. Of course I could use the non repository method with the controlfile entries and not have to worry about a database. Thanks, I'm still investigating. Ron [EMAIL PROTECTED] 10/04/02 03:58PM I have a small database on a separate disk which holds my recovery catalog. I would like to have it on a separate server but that won't happen. I have used the same recovery catalog for 4 years and it is onlyu ~88MB. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:31 PM List, With all of the recent discussion and the forth coming books and the upgrade here to 8i I have a question. Where do you build your RMAN repository database? If you build it in the same server as the one you are backing up then you risk the loss of everything in the event of a disk farm failure. If you created a separate server to hold the RMAN repository does it require a separate license for the oracle running on the server? We have a clustered environment with a disk farm and 2 Alpha boxes. One box will be Production and the other will be Development and they share the disk farm. If I use RMAN to backup the production box and keep it in the development database I still have all of my eggs in one disk farm. If I create a separate server on a Linux pc I need a license for the Oracle database on the pc. What methods have you used at your work location and I do not care about your licensing agreements. Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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
Re: help!! smon
Alex, If you were performing a long running function such as creating an index and lost you connection to the database then the index would reach the end of creation, find that you were not connected and completely undo the process. The indexes are created in a TEMPORARY segment before they are made perminent in the datafile. SMON has the job of cleaning up the segments that were used during the index creation. You can check the dba_extents table to see what is being cleaned up. If the number of extents on any segment is decreasing then SMON is doing work on that segment. It should give you an idea what is going on in the database. Some of the actions that SMON performs continues from where it left off when the database is shutdown and restarted. Other action will restart from the beginning and continue until completion. The description in the backup and recover handbook gives a fair idea of what is happening with SMON. I know that this answer is a little late but it could help in the future. Ron ROR mª¿ªm [EMAIL PROTECTED] 10/04/02 04:44PM Hi gurus ,,, i need check what do you doing the smon proc!!! this process have 99% of CPU somebody help me!!! @lex Lic. Alexander Ordóñez Arroyo Soporte Tru64Unix BD Oracle Caja Costarricense del Seguro Social Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED] Celular 397-0532 The truth is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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 to get rid of a column default value ?
Dan,
Thanks for the detailed explaination.
Ron
[EMAIL PROTECTED] 10/04/02 05:08PM
Setting the default to NULL changes to behavior to be the same as if a
default value had not been set. However, in dba_tab_columns, the
default_data column would indicate null. If the default value was never
set,
the column would be empty (null in type, not name).
The example below demonstrates the behavior:
create table test
(col1 char(4),
col2 char(4) default '');
insert into test values (NULL, NULL);
insert into test values ('1', NULL);
insert into test values ('2', '');
select * from test;
COL1 COL2
1
2
insert into test (col1) values ('3');
select col1, length(col1), col2, length(col2)
from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
1 4
2 4 4
3 4 4
alter table test
modify col2 default NULL;
insert into test (col1) values ('4');
select col1, length(col1), col2, length(col2)
from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
1 4
2 4 4
3 4 4
4 4
alter table test modify col2 default null
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
-- ---
TEST COL1
TEST COL2null
SQL
SQL alter table test
2 modify col2 default 'NULL';
SQL insert into test (col1) values ('5');
SQL select col1, length(col1), col2, length(col2)
2 from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
1 4
2 4 4
3 4 4
4 4
5 4 NULL4
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
-- ---
TEST COL1
TEST COL2'NULL'
-Original Message-
Sent: Friday, October 04, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Louis,
I still have not convinced myself that we have the proper answer.
If you query the DBA_TAB_COLUMNS table DATA_DEFAULT column you will
see
that the original NON default created column has a null or blank as
the
value for the data_default column. If the default is set to NULL then
the word NULL appears as the data_default value for the column. What
if
the column was a char(4) column then it would default to the
valueNULL
which is not the same as blank or nothing.
Still digging for an answer.
Ron
ROR mô¿ôm
[EMAIL PROTECTED] 10/04/02 03:21PM
Thanks Dale, Michael and Ron.
The default null is what I thought about first and it almost does
the
job. It's just that it appears like there is a default value which is
null. If I don't include the DEFAULT clause, it does nothing. I
think
I'll have to live with the default null.
At 08:29 2002-10-04 -0800, you wrote:
Louis,
I believe it is the ALTER TABLE command.
ALTER TABLE name
MODIFY ( column datatype);
match the column name and the datatype but do not include the DEFAULT
clause .
Ron
ROR mª¿ªm
[EMAIL PROTECTED] 10/04/02 11:28AM
Anyone knows how to get rid of a column default value ? I rtfm and
search
metalink with no luck.
Louis Brouillette
Analyste en informatique (DBA)
Universite du Quebec a Trois-Rivieres
Tel: (819) 376-5011 ext. 2435
Email: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Louis BROUILLETTE
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services--
RE: Shutdown Immediate
Ravi, FWIW, Some random thoughts/ideas: Most likely not the case, and not wishing to cast any aspersions to you, but I take it SYS is not using SYSTEM as temporary tablespace!. Perhaps a large batch process needs to be rolled back before shut down? If you use DBMS_JOB apparently stuck tasks on same could be source! Check to see if one or more of Oracle's background processes are eating away CPU time which might provide a clue I take it you've had a look around MetaLink? PS: When you do find the cause please post solution to the list to enlighten us :) - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] From: Ravi Kulkarni [EMAIL PROTECTED] Date: Sat, 5 Oct 2002 02:01:58 -0400 Subject: Shutdown Immediate Gurus, Solaris8/Oracle 8173-32bit. Shutdown abort works but Shutdown immediate hangs. Startup Normal is fine. No special entries in Alert log regarding smon/pmon activities-postStartup/During Shutdown. All DB Sessions/connections cleaned/killed before shutdown. Tried several times. Any Hints? TIA, Ravi. This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: Cary Millsap's course and a new article
Good article, thank you. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, October 06, 2002 10:43 PM I attended Cary's and Jeff's Hotsos Clinic last week. It was really good. I wrote a short review, which probably doesn't do the course justice, at the following URL: http://www.oreillynet.com/pub/wlg/2111 I also wrote a short article last week about an aspect of Oracle's newly-supported SQL92 join syntax that really surprised me: http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * mailto:[EMAIL PROTECTED] * 906.387.1698 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: How much memory is an oracle shadow process using
Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
View contents of global temp table
Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How much memory is an oracle shadow process using
Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Look them up in V$STATNAME. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 9:24 AM Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Backups
Tim, et.al, We use rman on 8.0.6.3 databases. One of our duvelopers was trying to delete records from a table and her query deleted everything from the table. This caused the application to fail and a point-in_time recovery was nessessary. I was given a time of 11:00AM. I had recovered the database until 11:00AM and before opening the database with resetlogs, I asked if the time was correct. After several minutes of discussion with all concerned, it was decided that actually we needed to recovery until 9:00AM. I changed the until time in the restore script, restored the database until 9:00AM and recovered it and opened it resetlogs. I had no problem with this. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 5:08 PM The one situation where a cold backup can be considered necessary is following an OPEN RESETLOGS. If you have a no data loss requirement and you are in ARCHIVELOG mode, then there is a window following an OPEN RESETLOGS where, if the media crashes prior to completing a hot backup, you could be unrecoverable. File that one under a bad day... It is not a hard-and-fast requirement however, as there is a fairly narrow set of circumstances (available since v7.3.3) where it is possible to recover using backups and archivelogs generated prior to an OPEN RESETLOGS and then continue the roll-forward using archivelogs generated after the RESETLOGS, but there several gotchas that can mess that up. It would be a gamble to rely on pulling that rabbit out of the hat... If you ever find yourself entering the command ALTER DATABASE OPEN RESETLOGS on a database that you *really* care about (should be recognized by the same shallow-breathing sweaty-palm symptoms you get when you say, ...now, just hand the gun to me, slowly...), then please get an immediate cold backup before opening the database to users. You may have to argue for it, but be sure to leave time for it when folks are asking, When will the database be back? ...other than that situation, there is no advantage of a cold backup over a hot backup; just my $0.02... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 12:33 AM Cold backup is very good because I don't have to monitor database during the cold backup, no objects will run out of space and I can enjoy a peaceful time without any chance for my beeper to go off. You must admit that a cold backup cannot guarantee you that. Unfortunately, my bosses somehow got the curious idea that they have paid big bucks for all those HP 9000 to work and not to sit idle. They even calculated a downtime cost per hour for each critical system and they are extremely reluctant to have them down for extended periods of time (1.1 TB database cannot be backed up in minutes, even with Asymmetrix). That is why they bought me a toy called OPS and why there are policies and procedures about who and how gets things in the production database. I would love to do cold backup every day from 8 PM until 7 AM and during Sunday football games but it is not very likely that my wish will come true. Starlight, starbright, first star I see tonight, I wish I may, I wish I might have the wish I wish tonight. That is my best chance to get cold backups every day. On 2002.10.04 01:38 Jared Still wrote: OK, Gene, you asked for it. :) The context of your message suggests that a hot backup is somehow more likely to be corrupted than a cold one. I hate to resurrect an old flame war, but... No, I take it back. I don't hate it a bit. ;) There aren't many occasions that call for a cold backup. I'm just curious what you believe a cold backup is buying you that a hot backup won't deliver. Jared On Thursday 03 October 2002 14:54, Gene Sais wrote: wow, never a cold backup for any os,oracle, application upgrades? i prefer to shutdown everything, backup the filesystems, let the vendor have his way. if he screws up, its much easier to restore a complete filesystem than a corrupted database. cold backups are a good thing. i sleep good at nite :) soon, rman will be another backup method in my toolbox. but when that happens, i can see hot backups going away but cold backups will still be needed on occassion. [EMAIL PROTECTED] 10/03/02 04:33PM I don't do them either, 4.5 years here. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 03, 2002 4:17 PM I haven't done nor recommended a cold backup in 3 years since I've been using Rman. Just not needed anymore. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 03, 2002 3:14 PM To: Multiple recipients of list ORACLE-L I still prefer cold backups when performing full OS backups.
Re: BACKUP database question
I also do a cold backup of the recovery catalog database. But you can put a recovery catalog in another database to backup the 'real' recovery catalog database. This seems like a lot of work to me. I do a cold backup of the recovery catalog database when I know there is no rman activity. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 5:58 PM I built the RMAN database on the Net Backup master server. It gets backed up cold. Yes, I realize I just admitted to a cold backup. I don't want RMAN backing itself up, and don't want any RMAN activity taking place during a backup. Kind of like Oracle Directory Server: You can't back it up hot either. Jared Ron Rogers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/04/2002 11:31 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:BACKUP database question List, With all of the recent discussion and the forth coming books and the upgrade here to 8i I have a question. Where do you build your RMAN repository database? If you build it in the same server as the one you are backing up then you risk the loss of everything in the event of a disk farm failure. If you created a separate server to hold the RMAN repository does it require a separate license for the oracle running on the server? We have a clustered environment with a disk farm and 2 Alpha boxes. One box will be Production and the other will be Development and they share the disk farm. If I use RMAN to backup the production box and keep it in the development database I still have all of my eggs in one disk farm. If I create a separate server on a Linux pc I need a license for the Oracle database on the pc. What methods have you used at your work location and I do not care about your licensing agreements. Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: View contents of global temp table
Title: RE: View contents of global temp table Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Subject: View contents of global temp table Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). 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: Remember me? Oracle DBA veteran considering getting certifi
FYI, I think Coriolis no longer exists. Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 9:38 AM To: Multiple recipients of list ORACLE-L certifi Paula, Your experience sounds very similar to mine which I documented on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm . I used the Exam Cram series and was very happy with them. I am booked for the 8i upgrade next week but despite using 8i for however long it has been available I cannot believe how much there is to learn. I can see myself putting off the exam once again John -Original Message- Sent: 30 September 2002 04:48 To: Multiple recipients of list ORACLE-L Sorry I didn't respond sooner - been up to my neck recovering from a bad controller. Anyway - 8i. If Mike Ault wrote a cram book for 9i upgrade I would get that one too. Please don't tell me that 8i ceritfication is retired. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Mohammad Rafiq 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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:
Re: How much memory is an oracle shadow process using
oops, sorry, didn't read your question carefully. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 10:08 AM Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Look them up in V$STATNAME. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 9:24 AM Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: How much memory is an oracle shadow process using
Thanks for the help Igor but I have managed to work that bit out for myself. I did think that the posting was reasonably comprehensive and I also mentioned what the 2 statistics were used for. Perhaps the only thing I did not mention was that these specific queries are running against an 8.1.7.3 database but I am really looking for a generic answer anyway John -Original Message- Sent: 07 October 2002 15:09 To: Multiple recipients of list ORACLE-L Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Look them up in V$STATNAME. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 9:24 AM Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Warehouse design: snowflake vs star schemas
Data modeling in a datawarehouse is there to ease and make querying faster. I have always discplined myself to use star schema and never snowflake. The Which one is easier to implement and easier ETL ? is not a good question as your data model should not be design for the ETL procecess but only for the querying. Oracle star transformation join technique is designed to handle star schema. HTH --- Alexandre Gorbatchev [EMAIL PROTECTED] a écrit : Dear Data Warehouse Experts, Could you please share you experience with snowflake and star data models. How do you choose between them? What problems may arise? Is star schema preferred for Oracle? What is users' experience with those schemas? Which one they like more and why? Which one is easier to implement and easier ETL? Are there other patterns for DW? I would like to check/confirm/change my possibly subjective point of view to more objective perspective. I would appreciate your thoughts or links where I can review practical conclusions. TIA, Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: View contents of global temp table
Rick, Within that session, you can see the data. Another session cannot see the data, however. That is why it is a temporary table. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: how to clone a DB ?
On the source database run: alter database backup controlfile to trace; Edit trace file according to the file system, save it as name.sql and run it on a target database. Change init.ora where required (e.g. number of control files, rollback segments automatically brought on line, archiving destination and so on). inka -Original Message- Sent: Monday, October 07, 2002 5:33 AM To: Multiple recipients of list ORACLE-L Guys , i want to clone DB1 on host HOST1 to HOST2. i have installed oracle on HOST2 with the default DB created. the disk layout on HOST1 is different from that of HOST2. the environment is 8.1.6/win2k. the files to be copied are all datafiles , log files and control files. ami right ? since the disk layout is differnet , how should i rename the datafiles/log files/control files ? can someone throw light and explain me all the steps involved in detail ? Thanx in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Inka Bezdziecka 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: View contents of global temp table
Title: RE: View contents of global temp table I'd disagree. I've always thought, that you can only see your "own" data in temp table, and not "other session" data. You share only table definition (not contents) with other sessions. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, October 07, 2002 10:33 AM Subject: RE: View contents of global temp table Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Subject: View contents of global temp table Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: View contents of global temp table
?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: View contents of global temp table
Title: RE: View contents of global temp table *Your session* should be able to see the rows. If you commit (or rollback) the table will be truncated. Are you commiting? Lewis Bishop --- Barclays Enable/ISS/OPTS - OracleDatabase Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: 07 October 2002 15:34 To: Multiple recipients of list ORACLE-L Subject: RE: View contents of global temp table This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Subject: View contents of global temp table Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: View contents of global temp table
You are correct. You can only see data for that session if you perserve rows Rick Igor Neyman ineyman@perce To: Multiple recipients of list ORACLE-L ptron.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: View contents of global temp table om 10/07/2002 11:00 AM Please respond to ORACLE-L I'd disagree. I've always thought, that you can only see your own data in temp table, and not other session data. You share only table definition (not contents) with other sessions. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, October 07, 2002 10:33 AM Subject: RE: View contents of global temp table Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Subject: View contents of global temp table Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS instead. Of course, this too has a bug that will report ORA-1403 on the first table in the schema, so I needed to code around that. And for all this trouble Oracle still recommends using DBMS_STATS over ANALYZE. Then fix it! sigh Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde Arr... So anyway, I am thinking, hey it's high time I start using DBMS_STATS instead of my own procedure so I kick of the following (Oracle 8.1.7.4). After the first run I have SYS and SYSTEM stats on indexes and on other schemas with NO STATS it just ignored those tables even though you can see I have GATHER EMPTY below. So I kick it off again and guess what, it starts analyzing the tables it missed the first time, including SYS and SYSTEM. Guess I am going to use DBMS_STATS.GATHER_TABLE_STATS and be a bit more specific about what I get. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: View contents of global temp table
Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: View contents of global temp table
Actually, it depends on how the GTT was created. By default, the data is not available after a transaction COMMITs. One must use 'ON COMMIT PRESERVE ROWS' to be able to see the data, within the same session, after COMMITs. - Kirti - Original Message - To: Multiple recipients of list ORACLE-L Sent: Monday, October 07, 2002 10:33 AM Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: View contents of global temp table
the 9i docs say that GLOBAL TEMPORARY is only for the session which creates it, and is either of session or transaction duration. Which means, you need to update the doc set you are looking at, it's wrong (and this is a surprise because) --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: View contents of global temp table
Title: RE: View contents of global temp table The global table is visible (Global) to all sessions, but each session can only see it's own data within the table. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Subject: RE: View contents of global temp table Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 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: View contents of global temp table
Rachel, I agree with Chris, and I don't see any contradiction, in what he said. What docs say, is that only definition (not contents) of GLOBAL TEMPORARY is visible to all sessions. And, in this case docs are correct, at least according to my experience with GLOBAL TEMPORARY tables. And their behavior didn't change in 9i. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 11:34 AM Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
RE: View contents of global temp table
Title: RE: View contents of global temp table The table may not necessarily be truncated which depends on the setting whether it gets truncated on commit(or rollback) or at the end of the session. no other session can however see the data. Regards Naveen -Original Message-From: Bishop Lewis [mailto:[EMAIL PROTECTED]]Sent: Monday, October 07, 2002 8:31 PMTo: Multiple recipients of list ORACLE-LSubject: RE: View contents of global temp table *Your session* should be able to see the rows. If you commit (or rollback) the table will be truncated. Are you commiting? Lewis Bishop --- Barclays Enable/ISS/OPTS - OracleDatabase Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: 07 October 2002 15:34To: Multiple recipients of list ORACLE-LSubject: RE: View contents of global temp table This header confirms that this email message has been swept for thepresence of computer viruses. Corporate ITTHE WOOLWICH-- Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Subject: View contents of global temp table Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cost of joins
Hi there Can someone please give me in order of preference/cost the relevant costs for the different joins. IE: This join is cheap, This is very expensive, This is bad and always avoid. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Here is a work-in-progress utility, ie, I'm posting
this on an all care, no responsibility basis.
Features include:
- will process all schemas or a nominated one
- has been deliberately restricted tables and indexes
(so if you want lobs etc, you'll need to edit it a
little)
- can run in synchronous (foreground) mode or
asynchronous ( submits itself as a dbms_job)
- can run in parallel (multiple streams done via
modulo the object_id)
- has a debugging mode
- uses dbms_space to derive a meaningful estimate size
for each segment
- records progress in v$session_longops
- doesn't go against DBA_SEGMENTS 'cos thats so slow
- cranks up sort_area_size to improve perf.
Cheers
Connor
create or replace
package system.dbstat is
procedure analyze_db (
p_owner varchar2 default null,--
if only one owner to be processed
p_debug number default 0, --
0=do work, 1=msgs+work, 2=msgs only
p_segment_type varchar2 default null, --
TABLE or INDEX
p_parallel number default 1, --
concurrency (1 means must be asych)
p_mode varchar2 default 'S', --
A=run as dbms_job, S=run synchronous
p_int1 number default 1, --
internal use only
p_int2 number default 0) ;--
internal use only
end;
/
create or replace
package body system.dbstat is
--
--
-- Routines
--
--
procedure analyze_db (
p_owner varchar2 default null,--
if only one owner to be processed
p_debug number default 0, --
0=do work, 1=msgs+work, 2=msgs only
p_segment_type varchar2 default null, --
TABLE or INDEX
p_parallel number default 1, --
concurrency (1 means must be asych)
p_mode varchar2 default 'S', --
A=run as dbms_job, S=run synchronous
p_int1 number default 1, --
internal use only
p_int2 number default 0) is --
internal use only
type varchar_list is table of varchar2(80);
v_start date := sysdate;
v_tot_count number := 0;
v_cum_count number := 0;
v_cum_bytes number := 0;
v_owner varchar_list;
v_segment_name varchar_list;
v_segment_type varchar_list;
v_partitioned varchar_list;
v_longop_rindex pls_integer;
v_longop_slno pls_integer;
v_job pls_integer;
v_job_plsql varchar2(240);
procedure process_segment(p_owner
varchar2,p_segment_name varchar2,
p_segment_type
varchar2,p_part_name varchar2 default null,
p_granularity varchar2
default 'GLOBAL') is
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_file_id number;
v_last_block_id number;
v_last_blocknumber;
v_amount_to_analyze number;
v_ana_command varchar2(500);
begin
dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name);
dbms_space.unused_space (
p_owner,
p_segment_name,
p_segment_type,
v_total_blocks,
v_total_bytes,
v_unused_blocks,
v_unused_bytes,
v_last_file_id,
v_last_block_id,
v_last_block,
p_part_name);
--
-- This gives a reasonable degree of analysis. Up to
about 10M is effectively a compute, and
-- it reduces from there, eventually down to about
0.5% for a 1G segment
-- The formula is: percent to analyze := 500 *
power(used megabytes,-1.05)
-- with a ceiling of 99.99 percent (since dbms_stats
does not allow a '100' to be passed)
--
v_amount_to_analyze :=
least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5));
if p_debug 0 then
dbms_output.put_line(p_segment_type||':
'||p_owner||'.'||p_segment_name||' '||p_part_name);
dbms_output.put_line(v_total_bytes||' bytes
allocated');
dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
bytes in use');
dbms_output.put_line('Analyze
'||nvl(v_amount_to_analyze,100)||'%');
dbms_output.put_line('-');
end if;
dbms_application_info.set_client_info('Obj:
'||v_cum_count||' '||p_owner||'.'||p_segment_name||'
'||
(v_total_bytes-v_unused_bytes)||' byt
'||nvl(v_amount_to_analyze,100)||'%');
if p_debug 2 then
dbms_application_info.set_session_longops(v_longop_rindex,
v_longop_slno,
'Analyze', 0, 0, v_cum_count, v_tot_count,
p_segment_type, 'objects');
if p_segment_type like 'TABLE%' then --
could be a table or a table partition
sys.dbms_stats.gather_table_stats(
Oracle Alert #42: possible DoS
Good morning, if it is good, which I doubt... I wonder if anyone knows details of that vulnerability. I have a listener listening on ports , which are not open on the firewall. Since an attack brings down a listener only, I do not think it is relevant in this case. Am I correct? inka -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Inka Bezdziecka 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: Shutdown Immediate
How to check if agent is running: UNIX: - from the operating system command prompt : ps -ef | grep dbsnmp or (7.3 - 8.1): lsnrctl dbsnmp_stat or (9.1 - 9.2): agentctl stat HTH, Gerardo -Original Message- Sent: Monday, October 07, 2002 3:19 AM To: Multiple recipients of list ORACLE-L Two things to check: 1. Check dba_jobs to see if any jobs are running like an analyze. I've had this happen to me. I tried to do a shutdown immediate but database is waiting for analyze job to finish (several hours). 2. Check if intelligent agent is running. You should shutdown intelligent agent before trying to do a shutdown immediate. How check for running intelligent agent ? HTH, Gerardo -- Mikhail Ivanov Ws±ë zØ^¡÷âr¥9,BÅm¶Yÿà (§Ú©Êëa¢³'¢ÚzÈ4DæSö§¢û]z¶«¸V +r5ëp¢¹z»âqëçÎwó9Öm§ÿðÃÚµÈÉÊI©Ã?è( ©b~Sç?£SX§'X¬µ©ÝÁæá¢Ëbz ®øoezÄèDCTL¨º» *÷ë¢kaSÉsSX§'X¬¶Ç§u©Ä1¨¥(tm)ë,j ¸¬´k«¹ör+rr?§¢×\²-¥-)à ¡òâ²Ñ®®æ§v)í...éz²Æ xfb)Ü-ç^jX§yÊ'µ¨§Sx5%9,Bè®Ø^©z¡ùsSX§'X¬·*.Á©í¶?Þé ¨½ç_®?~¢ésÉ©l¢Ç§vØ^ BÏr?¦jw_¢º-...êâú+(tm)«b¢yb'ë.nÇ+?¸§ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo 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).
Orace setting on NT
Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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).
ROLLBACK SEGMENT FRAGMENTATION
Hi My rollback tablespace is highly fragmented.I am thinking to do like following? -Create new rollback tablespace rbs1 -Create rollback segments -Offline all rollback segments from old rollback tablespace (rbs) -Drop rollback segments from rbs tablespace -Drop tablespace rbs -Create RBS tablespace -Create rollback segments on RBS tablespace -Drop tablespace RBS1 Is this way is good to manage? Let me know if anythings are missing please? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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).
Recommended, SGA size
List Im interested in finding out about the size of SGA . How large should it be? Is it a different size on Unix than NT Here I have a 2G database that's holding a 500M oracle dat file and the sga seems very small Comparitive to an install of 9i on Linux over the weekend the sga on the linux box was about 40M ORACLE instance started. Total System Global Area 17499404 bytes Fixed Size 70924 bytes Variable Size13156352 bytes Database Buffers 4194304 bytes Redo Buffers77824 bytes Database mounted. Database opened. SVRMGR select 17499404 * 1024 * 1024 from dual; 17499404*1 -- 1.8349E+13 Thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Connor, What version of Oracle was this coded for? Thanks, Cherie Connor McDonald To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hamcdc@yahoo. cc: co.uk Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and Sent by: SYSTEM inde [EMAIL PROTECTED] om 10/07/02 11:18 AM Please respond to ORACLE-L Here is a work-in-progress utility, ie, I'm posting this on an all care, no responsibility basis. Features include: - will process all schemas or a nominated one - has been deliberately restricted tables and indexes (so if you want lobs etc, you'll need to edit it a little) - can run in synchronous (foreground) mode or asynchronous ( submits itself as a dbms_job) - can run in parallel (multiple streams done via modulo the object_id) - has a debugging mode - uses dbms_space to derive a meaningful estimate size for each segment - records progress in v$session_longops - doesn't go against DBA_SEGMENTS 'cos thats so slow - cranks up sort_area_size to improve perf. Cheers Connor create or replace package system.dbstat is procedure analyze_db ( p_owner varchar2 default null,-- if only one owner to be processed p_debug number default 0, -- 0=do work, 1=msgs+work, 2=msgs only p_segment_type varchar2 default null, -- TABLE or INDEX p_parallel number default 1, -- concurrency (1 means must be asych) p_mode varchar2 default 'S', -- A=run as dbms_job, S=run synchronous p_int1 number default 1, -- internal use only p_int2 number default 0) ;-- internal use only end; / create or replace package body system.dbstat is -- -- -- Routines -- -- procedure analyze_db ( p_owner varchar2 default null,-- if only one owner to be processed p_debug number default 0, -- 0=do work, 1=msgs+work, 2=msgs only p_segment_type varchar2 default null, -- TABLE or INDEX p_parallel number default 1, -- concurrency (1 means must be asych) p_mode varchar2 default 'S', -- A=run as dbms_job, S=run synchronous p_int1 number default 1, -- internal use only p_int2 number default 0) is -- internal use only type varchar_list is table of varchar2(80); v_start date := sysdate; v_tot_count number := 0; v_cum_count number := 0; v_cum_bytes number := 0; v_owner varchar_list; v_segment_name varchar_list; v_segment_type varchar_list; v_partitioned varchar_list; v_longop_rindex pls_integer; v_longop_slno pls_integer; v_job pls_integer; v_job_plsql varchar2(240); procedure process_segment(p_owner varchar2,p_segment_name varchar2, p_segment_type varchar2,p_part_name varchar2 default null, p_granularity varchar2 default 'GLOBAL') is v_total_blocks number; v_total_bytes number; v_unused_blocks number; v_unused_bytes number; v_last_file_id number; v_last_block_id number; v_last_blocknumber;
Re: Orace setting on NT
There is a registry setting TNS_ADMIN under Oracle/Home. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 12:36 PM Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Does this script properly skip IOT overflow objects?
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, October 07, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Here is a work-in-progress utility, ie, I'm posting
this on an all care, no responsibility basis.
Features include:
- will process all schemas or a nominated one
- has been deliberately restricted tables and indexes
(so if you want lobs etc, you'll need to edit it a
little)
- can run in synchronous (foreground) mode or
asynchronous ( submits itself as a dbms_job)
- can run in parallel (multiple streams done via
modulo the object_id)
- has a debugging mode
- uses dbms_space to derive a meaningful estimate size
for each segment
- records progress in v$session_longops
- doesn't go against DBA_SEGMENTS 'cos thats so slow
- cranks up sort_area_size to improve perf.
Cheers
Connor
create or replace
package system.dbstat is
procedure analyze_db (
p_owner varchar2 default null,--
if only one owner to be processed
p_debug number default 0, --
0=do work, 1=msgs+work, 2=msgs only
p_segment_type varchar2 default null, --
TABLE or INDEX
p_parallel number default 1, --
concurrency (1 means must be asych)
p_mode varchar2 default 'S', --
A=run as dbms_job, S=run synchronous
p_int1 number default 1, --
internal use only
p_int2 number default 0) ;--
internal use only
end;
/
create or replace
package body system.dbstat is
--
--
-- Routines
--
--
procedure analyze_db (
p_owner varchar2 default null,--
if only one owner to be processed
p_debug number default 0, --
0=do work, 1=msgs+work, 2=msgs only
p_segment_type varchar2 default null, --
TABLE or INDEX
p_parallel number default 1, --
concurrency (1 means must be asych)
p_mode varchar2 default 'S', --
A=run as dbms_job, S=run synchronous
p_int1 number default 1, --
internal use only
p_int2 number default 0) is --
internal use only
type varchar_list is table of varchar2(80);
v_start date := sysdate;
v_tot_count number := 0;
v_cum_count number := 0;
v_cum_bytes number := 0;
v_owner varchar_list;
v_segment_name varchar_list;
v_segment_type varchar_list;
v_partitioned varchar_list;
v_longop_rindex pls_integer;
v_longop_slno pls_integer;
v_job pls_integer;
v_job_plsql varchar2(240);
procedure process_segment(p_owner
varchar2,p_segment_name varchar2,
p_segment_type
varchar2,p_part_name varchar2 default null,
p_granularity varchar2
default 'GLOBAL') is
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_file_id number;
v_last_block_id number;
v_last_blocknumber;
v_amount_to_analyze number;
v_ana_command varchar2(500);
begin
dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name);
dbms_space.unused_space (
p_owner,
p_segment_name,
p_segment_type,
v_total_blocks,
v_total_bytes,
v_unused_blocks,
v_unused_bytes,
v_last_file_id,
v_last_block_id,
v_last_block,
p_part_name);
--
-- This gives a reasonable degree of analysis. Up to
about 10M is effectively a compute, and
-- it reduces from there, eventually down to about
0.5% for a 1G segment
-- The formula is: percent to analyze := 500 *
power(used megabytes,-1.05)
-- with a ceiling of 99.99 percent (since dbms_stats
does not allow a '100' to be passed)
--
v_amount_to_analyze :=
least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5));
if p_debug 0 then
dbms_output.put_line(p_segment_type||':
'||p_owner||'.'||p_segment_name||' '||p_part_name);
dbms_output.put_line(v_total_bytes||' bytes
allocated');
dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
bytes in use');
dbms_output.put_line('Analyze '||nvl(v_amount_to_analyze,100)||'%');
dbms_output.put_line('-');
end if;
dbms_application_info.set_client_info('Obj:
'||v_cum_count||' '||p_owner||'.'||p_segment_name||'
'||
(v_total_bytes-v_unused_bytes)||' byt '||nvl(v_amount_to_analyze,100)||'%');
if p_debug 2 then
dbms_application_info.set_session_longops(v_longop_rindex,
v_longop_slno,
AW: Orace setting on NT
On NT you have two ways: 1. set Environment Varibale TNS_ADMIN 2. set registry value TNS_AMDIN (HKLM\Software\Oracle\HomeX) regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Gurelei [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 7. Oktober 2002 18:36 An: Multiple recipients of list ORACLE-L Betreff: Orace setting on NT Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Csv list variable passed to stored proc
List Is it possible to pass a comma seperated list of variables to make up a portion of the in clause in a stored procedure I have a list of variables IdList that looks like this (1,2,3,4,5,66,77,88,someothernumbers) PROCEDURE MyProc ( IdList IN VARCHAR SelectCursor OUT SelectRefCursor ) IS BEGIN OPEN SelectCursor FOR SELECT * FROMMyTable WHERE ID IN ( IdList ); EXCEPTION WHEN OTHERS THEN END MyProc; thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: View contents of global temp table
But when your session ends (for whatever reason) the data you created during the
session disappears for ever, so that each session sees the table as totally
empty at the beginning of the session.
Dick Goulet
Reply Separator
Author: Naveen Nahata [EMAIL PROTECTED]
Date: 10/7/2002 7:59 AM
The table may not necessarily be truncated which depends on the setting
whether it gets truncated on commit(or rollback) or at the end of the
session. no other session can however see the data.
Regards
Naveen
-Original Message-
Sent: Monday, October 07, 2002 8:31 PM
To: Multiple recipients of list ORACLE-L
*Your session* should be able to see the rows. If you commit (or rollback)
the table will be truncated. Are you commiting?
Lewis Bishop
---
Barclays Enable/ISS/OPTS - Oracle Database Consultant
Phone - 020 8298 3418
Mobile - 07950 380857
Email - [EMAIL PROTECTED]
-Original Message-
Sent: 07 October 2002 15:34
To: Multiple recipients of list ORACLE-L
This header confirms that this email message has been swept for the
presence of computer viruses.
Corporate IT
THE WOOLWICH
--
Rick,
it is like any other table ... if the data is committed and you have access
you can see data, else no you can't.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Monday, October 07, 2002 9:43 AM
To: Multiple recipients of list ORACLE-L
Hi All,
In session 1 I am loading data into a global temp table. Is there any way
to see contents of that table?
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN
HTML xmlns=http://www.w3.org/TR/REC-html40; xmlns:o =
urn:schemas-microsoft-com:office:office xmlns:w =
urn:schemas-microsoft-com:office:wordHEAD
META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1
TITLERE: View contents of global temp table/TITLE
META content=Word.Document name=ProgId
META content=MSHTML 5.50.4208.1700 name=GENERATOR
META content=Microsoft Word 10 name=OriginatorLINK
href=cid:[EMAIL PROTECTED]; rel=File-List!--[if gte mso 9]xml
o:OfficeDocumentSettings
o:DoNotRelyOnCSS/
/o:OfficeDocumentSettings
/xml![endif]--!--[if gte mso 9]xml
w:WordDocument
w:SpellingStateClean/w:SpellingState
w:GrammarStateClean/w:GrammarState
w:DocumentKindDocumentEmail/w:DocumentKind
w:EnvelopeVis/
w:BrowserLevelMicrosoftInternetExplorer4/w:BrowserLevel
/w:WordDocument
/xml![endif]--
STYLE@font-face {
font-family: Tahoma;
}
@font-face {
font-family: Verdana;
}
@page Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt;
mso-header-margin: 35.4pt; mso-footer-margin: 35.4pt; mso-paper-source: 0; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
mso-style-parent: ; mso-pagination: widow-orphan; mso-fareast-font-family:
Times New Roman
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
mso-style-parent: ; mso-pagination: widow-orphan; mso-fareast-font-family:
Times New Roman
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
mso-style-parent: ; mso-pagination: widow-orphan; mso-fareast-font-family:
Times New Roman
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
P.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
mso-pagination: widow-orphan; mso-fareast-font-family: Times New Roman
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
mso-pagination: widow-orphan; mso-fareast-font-family: Times New Roman
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: Times New Roman;
RE: Orace setting on NT
Yes there is. You can either set the TNS_ADMIN variable in the registry or you can set this as environment variable. The value in the Environment Variable takes precedence over the value in the registry. For registry setting start - run - regedt32 goto HKEY_LOCAL_MACHINE - Software - Oracle and then from the MENU EDIT - ADD VALUE VALUE NAME - TNS_ADMIN Data Type - REG_EXPAND_SZ Press OK and then enter the path of the TNSNAMES.ORA. Just the directory path, don't include the filename. For setting the environment variable either add a command in autoexec.bat - SET TNS_ADMIN = Director_path or right click my computer icon on the desktop, go to properties and then click on the environment tab. There click on any of the user variables and in the text fields enter the VARIABLE - TNS_ADMIN, Value- Directory_path Regards Naveen -Original Message- Sent: Monday, October 07, 2002 10:06 PM To: Multiple recipients of list ORACLE-L Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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 to retrieve numeric values only from a varchar2?
You don't need the ltrim if you use to_char( i, 'FM099') Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 10/07/02 03:38 AMcc: Please respond toSubject: RE: how to retrieve numeric values only from a varchar2? ORACLE-L I think if you try ltrim(to_char(i,'099')); it will remove a leading blank. There is I'm sure a way of doing it explicitly with the format of the to_char but I can't remember what it is. Iain Nicoll -Original Message- Sent: Monday, October 07, 2002 10:13 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only. I have tried the following piece of pl/sql, unsuccessfully declare begin for i in 1..999 loop update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099'); end loop; end; / Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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: Recommended, SGA size
I accidentially hit enter which set the message by mistake The size should have been rob@test_db - select 17499404 / 1024 / 1024 from dual; 17499404/1024/1024 -- 16.6887321 So I have a 17M SGA The 40M of 9i on Linux was without a database being built (yet) Im interested in finding out about the size of SGA . How large should it be? Is it a different size on Unix than NT Here I have a 2G database that's holding a 500M oracle dat file and the sga seems very small Comparitive to an install of 9i on Linux over the weekend the sga on the linux box was about 40M ORACLE instance started. Total System Global Area 17499404 bytes Fixed Size 70924 bytes Variable Size13156352 bytes Database Buffers 4194304 bytes Redo Buffers77824 bytes Database mounted. Database opened. SVRMGR select 17499404 * 1024 * 1024 from dual; 17499404*1 -- 1.8349E+13 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky 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: remove move a user
You may also possibly need to rebuild the indexes to a different tablespace. John Dennis M. Heisler wrote: To remove a user: drop user xxx cascade; To move a user to another tablespace: Ensure user has enough quota on tablespace yyy Set user's default tablespace alter user xxx default tablespace yyy; For each table owned by xxx, alter table xxx.aaa move tablespace yyy; Dennis MURAT BALKAS wrote: Hi, what's the best method to 1) remove a user absolutely. I want to remove the user's tables, indexes, ... etc. 2) move a user absolutely to another tablespace. Thanks, Murat -- Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez. This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. does not accept legal responsibility for the contents of this message. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MURAT BALKAS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA 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: Csv list variable passed to stored proc
What about this example: declare type m_curs_type is REF CURSOR; m_cursm_curs_type; m_str varchar2(200) := '(1,2,3,4)'; m_sql varchar2(1000); m_ret varchar2(20); begin m_sql := 'select 100 hh from dual where 1 in '||m_str; OPEN m_curs FOR m_sql; loop FETCH m_curs INTO m_ret; exit when m_curs%notfound; dbms_output.put_line(m_ret); end loop; CLOSE m_curs; end; Waleed -Original Message- Sent: Monday, October 07, 2002 12:54 PM To: Multiple recipients of list ORACLE-L List Is it possible to pass a comma seperated list of variables to make up a portion of the in clause in a stored procedure I have a list of variables IdList that looks like this (1,2,3,4,5,66,77,88,someothernumbers) PROCEDURE MyProc ( IdList IN VARCHAR SelectCursor OUT SelectRefCursor ) IS BEGIN OPEN SelectCursor FOR SELECT * FROMMyTable WHERE ID IN ( IdList ); EXCEPTION WHEN OTHERS THEN END MyProc; thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: View contents of global temp table
Title: RE: View contents of global temp table Thank you Melissa. -Original Message-From: Godlewski, Melissa [mailto:[EMAIL PROTECTED]]Sent: Monday, October 07, 2002 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: RE: View contents of global temp table The global table is visible (Global) to all sessions, but each session can only see it's own data within the table. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Subject: RE: View contents of global temp table Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- "Grabowy, Chris" [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-LHi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 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: Orace setting on NT
Hi Gene, You can set the tns_admin as a local environment variable, system environment variable or in the registry. John Gurelei wrote: Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA 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: Orace setting on NT
Yes. Go to the Control Panel, then SYSTEM, and then Environment Variables. You can set the TNS_ADMIN variable there. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, October 07, 2002 12:36 PM To: Multiple recipients of list ORACLE-L Subject:Orace setting on NT Hi . This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) thanks for any help Gene __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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 contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: ROLLBACK SEGMENT FRAGMENTATION
before you do all this by fragmented do you mean that there are a large number of free extents in the tablespace? If so, are those extents all the same size or at least a multiple of the same size? If so, why are you bothering to defragment? You would only need to defragment the rollback tablespace if you have rollback segments where the initial and next extent sizes are not multiples of each other and/or where each rollback segment has a differently sized initial and next extents that are not multiples of the extent sizes of the other rollback segments... otherwise you are not fragmented, you just have a lot of free extents that are the right size for Oracle to use. this is NOT a bad thing --- Seema Singh [EMAIL PROTECTED] wrote: Hi My rollback tablespace is highly fragmented.I am thinking to do like following? -Create new rollback tablespace rbs1 -Create rollback segments -Offline all rollback segments from old rollback tablespace (rbs) -Drop rollback segments from rbs tablespace -Drop tablespace rbs -Create RBS tablespace -Create rollback segments on RBS tablespace -Drop tablespace RBS1 Is this way is good to manage? Let me know if anythings are missing please? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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).
java, etc
Ok one of my last tasks here(besides implementing RMAN) is to tell the developers how to call an external java program from like pl/sql. (unix-based) Does this require me to have java loaded in the database(from what i've read I dont think so). Does this require me to have an external proc listener(i think so, and have a .so file?). What would be the best part of the oracle docs to be able to answer these questions for myself, with some examples if possible to show the developers? thanks, joe
RE: View contents of global temp table
Thank you Igor. When is the next NYOUG? I can't wait to give her some grief for say an hour or two...and a public floggingwith a wet noodle. -Original Message- Sent: Monday, October 07, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Rachel, I agree with Chris, and I don't see any contradiction, in what he said. What docs say, is that only definition (not contents) of GLOBAL TEMPORARY is visible to all sessions. And, in this case docs are correct, at least according to my experience with GLOBAL TEMPORARY tables. And their behavior didn't change in 9i. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 11:34 AM Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Orace setting on NT
This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) Afaik on NT or 2k oracle uses only one tnsnames.ora file its located in $ORAHOME\network\admin There is a sql.net file that dictates the precedence of files eg tns onames Eg sql.net # Generated by Oracle configuration tools. SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) Here oracle looks for the tnsnames then onames Then finally hostname bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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 Performance Tuning Class - update
I concur with Dennis. I too came off a Oracle Ed Tuning class last week and had a good instructor (who btw used John Hibbard's excellent presentation on Redo/RBS _as_well_as Cary's 'Why a 99.9% BHR is not Ok'). Maybe, just maybe, we will get there (i.e. a Non-BHR world!) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 05, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Subject: Oracle Performance Tuning Class - update List I spent last week at an official Oracle Education Oracle9i Performance Tuning Class, and here is some of the non-technical stuff I learned. - Oracle is teaching the wait interface more and more. In fact, they are updating the curriculum next month to emphasize the wait interface even more (lucky me). - Just how the wait interface is emphasized may depend quite a bit on the instructor, despite what the materials say. My observation is that our opinions are based on what we have experienced and our interpretations of those experiences. So we will probably still have some instructors that will still feel that the wait interface is a passing fad and if you really want to straighten out a database, you need to get in there and improve the BHR (Buffer Hit Ratio). - My instructor was John Hibbard. He is excellent, and I would highly recommend him. He went well beyond the class materials to providing papers he has researched and presented himself, as well as other sources, including papers from Cary Milsap and Jonathan Gennick who participate on this list. When you get through his class, you really feel you have been taken to a whole new level of Oracle knowledge. He is also heavily involved in selecting and preparing the official Oracle training materials for the courses he teaches. Besides Performance Tuning, he teaches several other Oracle classes. Most of the people in my class happened to be more experienced with Oracle, and John did a good job of answering advanced questions with some depth, but not leaving the newbies in the dust. - A funny observation on buffer hit ratio vs. wait interface. The last day of class is an opportunity to take a really screwed-up database and apply a little of what you have learned. The first scenario is titled Buffer Cache. So you run the workload assignment and STATSPACK and look at the BHR and say wow, that is bad, increase the buffer pool, and rerun the workload and STATSPACK. The BHR hasn't changed much, so the tendency is to dumbly bump the buffer pool even more and go again. Then you look down at the top 5 waits section just below on the first page of the STATSPACK report and see that the big wait item is Scattered Read. Then you go dope slap and realize this schema is missing some critical indexes and table scanning it's little heart out. I just found it ironic that some people have reported that some of the Oracle instructors emphasize the BHR too much when the first Workshop Scenario has a great example of why focusing on BHR can't solve many problems. But again, we have experience vs. interpretation of experience. A real died-in-the wool BHR fanatic would probably claim that BHR had solved the problem because the first indication that something was wrong was spotting the bad BHR, which led to other investigations. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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
RE: Shutdown Immediate
Or, if you don't want all your pagers going off because OEM thinks that all the instances on that server are down when it loses contact with the IA, on Unixishes you can: ps -ef|grep oracle$ORACLE_SID|grep -v grep ...from another session after starting the SHUTDOWN IMMEDIATE. You should see only three processes, each connected locally. If you have more than three processes, the rest should be shutting down. One of these three processes is your connection and will be the only one with a parent process ID 1. The other two are the dbsnmp processes. kill these other two processes from the Unix shell prompt. If they don't die within 30 seconds after kill, use kill -9 on each. It still may take a few seconds (up to a minute or two on our 6-way K570 with the 4 DBWRs each taking a CPU), but it will complete. This is really much simpler than I've shown. It just takes a little Unix-sense. HTH! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Molina, Gerardo [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 05, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: Shutdown Immediate [SNIP] 2. Check if intelligent agent is running. You should shutdown intelligent agent before trying to do a shutdown immediate. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Recommended, SGA size
Bob - The SGA size will vary significantly, depending on the applications you are running and the available system memory size. More users may cause your SGA requirements to increase. If the applications execute large PL/SQL packages, then more SGA will be required. Within the SGA, the buffer pool caches Oracle data blocks so you don't need to retrieve frequently used blocks from disk each time you need them. Tuning the SGA is a large subject within Oracle tuning. To start, I would recommend that you execute the STATSPACK report during peak time and read first page of the report it generates. This will help you understand if your SGA is too small. I would also recommend that you get a copy of Oracle Performance Tuning 101 by Gaja Krishna Vaidyanatha. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 07, 2002 11:36 AM To: Multiple recipients of list ORACLE-L List Im interested in finding out about the size of SGA . How large should it be? Is it a different size on Unix than NT Here I have a 2G database that's holding a 500M oracle dat file and the sga seems very small Comparitive to an install of 9i on Linux over the weekend the sga on the linux box was about 40M ORACLE instance started. Total System Global Area 17499404 bytes Fixed Size 70924 bytes Variable Size13156352 bytes Database Buffers 4194304 bytes Redo Buffers77824 bytes Database mounted. Database opened. SVRMGR select 17499404 * 1024 * 1024 from dual; 17499404*1 -- 1.8349E+13 Thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: View contents of global temp table
LOL! Chris, there is a Russian saying: You are playing with a fire :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 1:54 PM Thank you Igor. When is the next NYOUG? I can't wait to give her some grief for say an hour or two...and a public floggingwith a wet noodle. -Original Message- Sent: Monday, October 07, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Rachel, I agree with Chris, and I don't see any contradiction, in what he said. What docs say, is that only definition (not contents) of GLOBAL TEMPORARY is visible to all sessions. And, in this case docs are correct, at least according to my experience with GLOBAL TEMPORARY tables. And their behavior didn't change in 9i. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 11:34 AM Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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
Re: remove move a user
John, Thanks. I forgot to mention that. All indexes on any tables which are moved have to be rebuilt, even if the indexes stay in the same tablespace. Dennis Ora NT DBA wrote: You may also possibly need to rebuild the indexes to a different tablespace. John Dennis M. Heisler wrote: To remove a user: drop user xxx cascade; To move a user to another tablespace: Ensure user has enough quota on tablespace yyy Set user's default tablespace alter user xxx default tablespace yyy; For each table owned by xxx, alter table xxx.aaa move tablespace yyy; Dennis MURAT BALKAS wrote: Hi, what's the best method to 1) remove a user absolutely. I want to remove the user's tables, indexes, ... etc. 2) move a user absolutely to another tablespace. Thanks, Murat -- Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez. This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. Rt.Net Internet Hizmetleri Pazarlama ve Ticaret A.S. does not accept legal responsibility for the contents of this message. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MURAT BALKAS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler 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: Why Use Anydata? DBA Woes
I suspected it was being used Rachel's objections. In the past I've read many messages where DBA's have threatened to quit over such things; not so much any more as the IT job market is not great. Certainly no such insult as anydata would ever appear in the database against a DBA's say so. However, in this case, management felt the cost of rewriting the system was more than the cost of living with anydata. I'd guess many of us are faced with similar issues, We win many/most of these arguments, but lose a few. Then, we need to obtain acceptable performance in a database which is not completely designed as we would like, and develop work arounds for the problems we foresaw. This not only happens with database design, but with hardware choices as well. There are certainly hardware choices and designs too awful to overcome. It is against these we need to be most vociferous in our warnings reminding everyone that problems will compound as the user base grows. We need to draw to management's attention any bad design/hardware choice. But we also need to be honest in describing the costs of employing a bad choice. One's credibility is toilet-bound if management rules the other way, and the predicted disaster never occurs. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, October 04, 2002 3:28 PM To: Multiple recipients of list ORACLE-L we are using a generic data model (and the procedures to access the data within the model) from a third party consultant who wrote all of his work against a SQLServer database. SQLServer, and Sybase, have a datatype called 'variant' which has the equivalent functionality of the anydata datatype, i.e. the ability to store different datatype data in the same column (for this model, because it IS generic, it is possible for the data to be stored in that column to be numeric, character or date) Since we did not have the time to redesign the model (which was the whole point of hiring this consultant) we needed to go with ANYDATA. We are rewriting his procedures into PL/SQL and that is where the error is occurring. I started the discussions on the list a few months back by asking about the datatype. I was, and am, opposed to using it for this. But I am neither the data modeler for this app nor the DBA and my opinions were ignored. Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: I remember when anydata was first discussed a few months ago. I questioned how it could be part of proper database design; from what domain would the anydata column draw its values? As I recall everyone advised against its use, It is a bad idea in Access and so it is in Oracle. was the gist of the comments. One wag proposed having two fields in the database, a sequence based primary key and the anydata field. Apparently that person was too shy to rely on rowid's :) Why did you decide to use anydata? How does it benefit to your application? It strikes me as a bad idea, but I have not researched it at length. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Change Number Mismatch
Hello Folks, Oracle 7.3.4 on SunOS 2.6. We were testing out a hot backup using BCV with scripts provided by EMC. The database fails to open after the restore and claims that it needs to apply more logs to be consistent. The script performs a log switch, then select the archive_change# from v$database, and tries to recover until that change#. But when looking at v$log, I see that the log file created during the backup, has a greater high change number. After ending hot backup SQL alter system archive log current; System altered. SQL select * from v$log order by first_change#; Cut paste the last 2 entries only. GROUP#THREAD# SEQUENCE# BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -- -- -- -- -- --- 8 1 112591 62914560 3 YES ACTIVE 111828664784 10/07/02 12:36:57 9 1 112592 62914560 3 NO CURRENT 111828666582 10/07/02 12:41:20 10 rows selected. SQL select * from v$database; NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# - --- - CSOP 04/28/00 15:04:37ARCHIVELOG 111828664786 111828666580 End Paste V$database - Archive Change# = 111828666580 (Tries to recover until this SCN#, and fails). v$log - Logfile 112591.ARC has changes from 111828664784 to 111828666581. Why is there a mismatch between the two numbers? Should v$database not say 111828666581? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
It is still quirky in 9.2.0.1. Now it does not like an FBI on a table :( Check out bug# 2606697 on Metalink... - Kirti -Original Message- Sent: Monday, October 07, 2002 10:29 AM To: Multiple recipients of list ORACLE-L inde Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS instead. Of course, this too has a bug that will report ORA-1403 on the first table in the schema, so I needed to code around that. And for all this trouble Oracle still recommends using DBMS_STATS over ANALYZE. Then fix it! sigh Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Csv list variable passed to stored proc
Sure. Just change your cursor to:
PROCEDURE MyProc
(
IdList IN VARCHAR
SelectCursor OUT SelectRefCursor
)
TYPE refRS IS REF CURSOR;
SelectCursor varchar2(1000) := 'SELECT * ' ||
' FROMMyTable ' ||
' WHERE ID IN (' || IdList ||
')';
Cursor_Row MyTable%Rowtype;
IS
BEGIN
OPEN refRS FOR SelectCursor;
Fetch refRS into Cursor_Row;
EXCEPTION
WHEN OTHERS THEN
END MyProc;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, October 07, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
List
Is it possible to pass a comma seperated list of variables to
make up a portion of the in clause in a stored procedure
I have a list of variables IdList that looks like this
(1,2,3,4,5,66,77,88,someothernumbers)
PROCEDURE MyProc
(
IdList IN VARCHAR
SelectCursor OUT SelectRefCursor
)
IS
BEGIN
OPEN SelectCursor FOR
SELECT *
FROMMyTable
WHERE ID IN ( IdList );
EXCEPTION
WHEN OTHERS THEN
END MyProc;
thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: 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: Cary Millsap's course and a new article
Jonathan, Nice writeup. I like your comment on Chapter 20 of 'The Oracle8i DBA Bible'. ;) Re queueing theory: Take Craig Shalahammer's Capacity Planning course if you want to spend more time on queueing. http://www.orapub.com/cgi/genesis.cgi?p1=subp2=cp_course Jared Jonathan Gennick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/06/2002 07:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Cary Millsap's course and a new article I attended Cary's and Jeff's Hotsos Clinic last week. It was really good. I wrote a short review, which probably doesn't do the course justice, at the following URL: http://www.oreillynet.com/pub/wlg/2111 I also wrote a short article last week about an aspect of Oracle's newly-supported SQL92 join syntax that really surprised me: http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * mailto:[EMAIL PROTECTED] * 906.387.1698 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Csv list variable passed to stored proc
Bob, Yes, but you must use dynamic SQL, via either EXECUTE IMMEDIATE or the DBMS_SQL package. Another alternative, stuff the values you want check into a temporary table, use the temp table for you IN clause. Much easier. Jared Bob Metelsky [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/07/2002 09:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Csv list variable passed to stored proc List Is it possible to pass a comma seperated list of variables to make up a portion of the in clause in a stored procedure I have a list of variables IdList that looks like this (1,2,3,4,5,66,77,88,someothernumbers) PROCEDURE MyProc ( IdList IN VARCHAR SelectCursor OUT SelectRefCursor ) IS BEGIN OPEN SelectCursor FOR SELECT * FROMMyTable WHERE ID IN ( IdList ); EXCEPTION WHEN OTHERS THEN ... END MyProc; thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: View contents of global temp table
December 12th. You missed your opportunity to heckle me at the last one, as I was a presenter there. the december meeting is the last one I will be working on. I am giving up the meeting agenda work at the end of the year. heckle away. if you dare --- Grabowy, Chris [EMAIL PROTECTED] wrote: Thank you Igor. When is the next NYOUG? I can't wait to give her some grief for say an hour or two...and a public floggingwith a wet noodle. -Original Message- Sent: Monday, October 07, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Rachel, I agree with Chris, and I don't see any contradiction, in what he said. What docs say, is that only definition (not contents) of GLOBAL TEMPORARY is visible to all sessions. And, in this case docs are correct, at least according to my experience with GLOBAL TEMPORARY tables. And their behavior didn't change in 9i. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 11:34 AM Chris, you just contradicted yourself GLOBAL TEMPORARY (from the docs you quoted) is visible to all sessions, TEMPORARY is visible only to the session creating it Rachel --- Grabowy, Chris [EMAIL PROTECTED] wrote: ?? The data in a global temp table is only visible to the session that inserted the data, the other sessions cannot see the data, regardless of a commit. A quick search of the doc... GLOBAL TEMPORARY Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table. A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below). Here is the (broken up) link http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/statem3e.htm#2061078 -Original Message- Sent: Monday, October 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Rick, it is like any other table ... if the data is committed and you have access you can see data, else no you can't. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, October 07, 2002 9:43 AM To: Multiple recipients of list ORACLE-L Hi All, In session 1 I am loading data into a global temp table. Is there any way to see contents of that table? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list,
OCP (OPP number)
Hi, I just registered for oracle 8i upgrade exam and enter the promotion code OPP(Oracle preffered partner) but it never prompt to enter my company name or partner number. Do i need to take copy of OPP copy at centre or they will not see it at all?? Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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 to retrieve numeric values only from a varchar2?
Use owa_pattern. See $ORACLE_HOME/rdbms/admin/pubpat.sql Here's an example: declare tstr varchar2(100) := 'this string has 382 embedded numeric data'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\D', '', 'g'); dbms_output.put_line( tstr); end; / Jared Robert Morrison [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/07/2002 02:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:how to retrieve numeric values only from a varchar2? Hi, Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only. I have tried the following piece of pl/sql, unsuccessfully declare begin for i in 1..999 loop update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099'); end loop; end; / Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Remember me? Oracle DBA veteran considering getting certifi
Title: RE: Remember me? Oracle DBA veteran considering getting certifi Well, Amazon is sending me 8i certification books published by coriolis - h. -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Subject: RE: Remember me? Oracle DBA veteran considering getting certifi FYI, I think Coriolis no longer exists. Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 9:38 AM To: Multiple recipients of list ORACLE-L certifi Paula, Your experience sounds very similar to mine which I documented on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm . I used the Exam Cram series and was very happy with them. I am booked for the 8i upgrade next week but despite using 8i for however long it has been available I cannot believe how much there is to learn. I can see myself putting off the exam once again John -Original Message- Sent: 30 September 2002 04:48 To: Multiple recipients of list ORACLE-L Sorry I didn't respond sooner - been up to my neck recovering from a bad controller. Anyway - 8i. If Mike Ault wrote a cram book for 9i upgrade I would get that one too. Please don't tell me that 8i ceritfication is retired. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Mohammad Rafiq 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network
RE: DBA place in the business (was RE: DBA work load)
We have a similar structure Infrastructure - DBA - Sys Admin - Network Admin -Original Message- Sent: Sunday, September 29, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Hello Peter We have an infrastructure division that divides into two departments: system programming and DBA. Organization chart for us will be: CEO - CIO - Infrastructure - DBA. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 11:13 AM I've found the thread on DBA workload valuable and interesting. It endorses points made repeatedly over the past years, basically the highly variable nature of the job. This variability is giving us a small problem. Our dba work (shared between two of us) tends to function in the background, and of course because we do it so damn well (!!), our impact on the running of the organisation is pretty low. Kind of 'reverse exception' effect, if you will. There is now a desire to formalise the role of the dba function within the organisation, and nobody has the first idea of how to define, in an organisational / structural sense just how the dba role slots in. I'm talking about organsiational charts, herarchies etc, that sort of thing. Not just across the org, but particularly within the IT domain too. Specifically, dba impacts from the low-level hardware side, right up to application development, with everything in between. And that already spans several existing lines of management responsibility. Our problem has added spice as we are (trying) to operate a matrix management system, which repeatedly throws up intriguing political dimensions. Anybody ever been down this particular route? Any thoughts much appreciated, peter edinburgh * 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 * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: IT - Database (Do Not Use) 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: OCP (OPP number)/OTN20
If you are an employee of OPP then you can use this code. Enter required info on screen or call them for test appointment and if you want that discount they will ask this question there. Alternately get registered on OTN and claim 'OTN20' for 20% discount(net $100). At examination center they don't ask anything except your identity.. HTH, Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Mon, 07 Oct 2002 10:49:04 -0800 Hi, I just registered for oracle 8i upgrade exam and enter the promotion code OPP(Oracle preffered partner) but it never prompt to enter my company name or partner number. Do i need to take copy of OPP copy at centre or they will not see it at all?? Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
H...I can't see the bug. What Oracle product is it placed under? One would think Server for something like this... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 12:30 PM To: oracle list Cc: Jesse, Rich Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde It is still quirky in 9.2.0.1. Now it does not like an FBI on a table :( Check out bug# 2606697 on Metalink... - Kirti -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS instead. Of course, this too has a bug that will report ORA-1403 on the first table in the schema, so I needed to code around that. And for all this trouble Oracle still recommends using DBMS_STATS over ANALYZE. Then fix it! sigh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Man that is ridiculous. You would think Oracle would have it's act together on DBMS_STATS package by now. Since is it supposedly so superior to analyze table you would think it might actually work. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Monday, October 07, 2002 1:34 PM To: Multiple recipients of list ORACLE-L inde It is still quirky in 9.2.0.1. Now it does not like an FBI on a table :( Check out bug# 2606697 on Metalink... - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Orace setting on NT
On Nt, you can define tns_admin by right clicking on my computer and going to the properties. You can also define the variable in a command window: set tns_admin=path. Oracle will also use a tnsnames file if it exists in the current directory (or in the start in directory used by the short-cut), I think there is probably a hierarchy of sorts so that if the alias isn't found in the tnsnames in the current directory, it will look at the one in network\admin... chaim Bob Metelsky [EMAIL PROTECTED]@fatcity.com on 10/07/2002 01:24:04 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: This is a very easy question. I'm usually working with Oracle on UNIX, but today I need to connect to a remote instance from my NT desktop. Is there an equivalent to TNS_ADMIN varaiable in NT? ( I'm trying to force Oracle to use a specific tnsnames.ora file on my machine adn TNS_ADMIN variable is the way to do it on UNIX.) Afaik on NT or 2k oracle uses only one tnsnames.ora file its located in $ORAHOME\network\admin There is a sql.net file that dictates the precedence of files eg tns onames Eg sql.net # Generated by Oracle configuration tools. SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) Here oracle looks for the tnsnames then onames Then finally hostname bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
I just used the bug number window, rest all left to default. - Kirti -Original Message- Sent: Monday, October 07, 2002 2:19 PM To: Multiple recipients of list ORACLE-L inde H...I can't see the bug. What Oracle product is it placed under? One would think Server for something like this... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 12:30 PM To: oracle list Cc: Jesse, Rich Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde It is still quirky in 9.2.0.1. Now it does not like an FBI on a table :( Check out bug# 2606697 on Metalink... - Kirti -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS instead. Of course, this too has a bug that will report ORA-1403 on the first table in the schema, so I needed to code around that. And for all this trouble Oracle still recommends using DBMS_STATS over ANALYZE. Then fix it! sigh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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 to retrieve numeric values only from a varchar2?
Just when I think I'm cought up on the Oracle-installed PL/SQL packages... Seeing as OWA_PATTERN isn't in the docs (not on tahiti, anyway), is it supported??? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 1:58 PM To: Multiple recipients of list ORACLE-L Subject: Re: how to retrieve numeric values only from a varchar2? Use owa_pattern. See $ORACLE_HOME/rdbms/admin/pubpat.sql Here's an example: declare tstr varchar2(100) := 'this string has 382 embedded numeric data'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\D', '', 'g'); dbms_output.put_line( tstr); end; / Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: DBA place in the business (was RE: DBA work load)
Title: RE: DBA place in the business (was RE: DBA work load) you can write down the known heiarchy, then encompass that within a circle. add a picture of the DBA holding the sphere in his hand or better yet if you really want to get creative, place it on his back and have him poised like Atlas. There should be a minimum of 1,000 words in that picture =). -Original Message- From: IT - Database (Do Not Use) [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: DBA place in the business (was RE: DBA work load) We have a similar structure Infrastructure - DBA - Sys Admin - Network Admin -Original Message- Sent: Sunday, September 29, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Hello Peter We have an infrastructure division that divides into two departments: system programming and DBA. Organization chart for us will be: CEO - CIO - Infrastructure - DBA. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 11:13 AM I've found the thread on DBA workload valuable and interesting. It endorses points made repeatedly over the past years, basically the highly variable nature of the job. This variability is giving us a small problem. Our dba work (shared between two of us) tends to function in the background, and of course because we do it so damn well (!!), our impact on the running of the organisation is pretty low. Kind of 'reverse exception' effect, if you will. There is now a desire to formalise the role of the dba function within the organisation, and nobody has the first idea of how to define, in an organisational / structural sense just how the dba role slots in. I'm talking about organsiational charts, herarchies etc, that sort of thing. Not just across the org, but particularly within the IT domain too. Specifically, dba impacts from the low-level hardware side, right up to application development, with everything in between. And that already spans several existing lines of management responsibility. Our problem has added spice as we are (trying) to operate a matrix management system, which repeatedly throws up intriguing political dimensions. Anybody ever been down this particular route? Any thoughts much appreciated, peter edinburgh * 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 * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: IT - Database (Do Not Use) 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
Re: BACKUP database question
You can do incremental backups without a recovery catalog repository (i.e. nocatalog mode). In Oracle8i, there are only a few situations where a recovery catalog database repository is necessary -- mostly for certain catalog maintenance routines (i.e. change ... delete, change .. obsolete, etc). All of the options for backup, restore, and recovery are available whether you use a recovery catalog repository or not. Of course, there are good reasons for using a recovery catalog, but it is a long way from being a requirement, and additional Oracle9i features continue to whittle away at that... ...you can also do incremental backups when the target database itself is in noarchivelog mode, which surprised me initially, but has proven quite useful... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 7:18 AM Without a repository, you can't do incremental backups. You lose a lot of the functionality of rman. Have a look at the 8.1.7 Backup and Recovery Guide. It should give you some insights that you can pass on to the powers that be. Being a quasi-state agency they should be glad because rman is free. You already have it. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 4:48 PM Thanks, Tom and Ruth and others yet to reply, We to are a quasi-state agency but the Oracle licensing is under a state controlled agency and must be purchased from them. If I use my Linux/8i test platform for company business then I must purchase a license. Although a 10 named license is not that expensive, I still have to get it past the Sr.VP (also SR.VP of finance - a CPA) Like a snowball in hel*. There is some risk envolved with the disk farm concept for both boxes but I think that is the way I will have to go. I will create a rman test repository on my Linux box and get the bugs worked out before I deploy to the production environment. Of course I could use the non repository method with the controlfile entries and not have to worry about a database. Thanks, I'm still investigating. Ron [EMAIL PROTECTED] 10/04/02 03:58PM I have a small database on a separate disk which holds my recovery catalog. I would like to have it on a separate server but that won't happen. I have used the same recovery catalog for 4 years and it is onlyu ~88MB. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:31 PM List, With all of the recent discussion and the forth coming books and the upgrade here to 8i I have a question. Where do you build your RMAN repository database? If you build it in the same server as the one you are backing up then you risk the loss of everything in the event of a disk farm failure. If you created a separate server to hold the RMAN repository does it require a separate license for the oracle running on the server? We have a clustered environment with a disk farm and 2 Alpha boxes. One box will be Production and the other will be Development and they share the disk farm. If I use RMAN to backup the production box and keep it in the development database I still have all of my eggs in one disk farm. If I create a separate server on a Linux pc I need a license for the Oracle database on the pc. What methods have you used at your work location and I do not care about your licensing agreements. Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City
Re: Backups
I didn't say that you would have a problem during OPEN RESETLOGS. A problem might occur after the OPEN RESETLOGS if you did not grab an immediate cold backup... If you just open the database to end-users and transactions immediately after the OPEN RESETLOGS, then you have a period when it would be unrecoverable should another media failure or corruption occur before a full backup can be taken. That is, before you can complete a full backup of the database (either hot or cold), all of the archivelogs generated and saved are *useless*. After all, what good is an archivelog without a valid restored backup to overlay? That is the reason for the recommendation to get a cold backup after an OPEN RESETLOGS; so that you can be absolutely certain that any transaction committed afterwards can be recovered in the event of media failure... --- There is a certain set of circumstances where the sun, the moon, and the stars align where you can restore from backups taken *prior* to the OPEN RESETLOGS and roll forward archivelogs generated after the backup through the OPEN RESETLOGS to the point-in-time of failure, but the requirements include: * database must be version 7.3.3 or higher, so you can save the SCN of the OPEN RESETLOGS logged to the alert.log file * must have the SCN of the OPEN RESETLOGS available * the backup taken prior to the resetlogs (used in the restore) must have been a hot backup * the database instance must not have been shutdown between that hot backup and the events leading to the OPEN RESETLOGS If any one of these conditions is false, then grabbing a cold backup after an OPEN RESETLOGS is the only way to guarantee that all transactions committed after the OPEN RESETLOGS can be recovered if media failure occurs again... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 8:13 AM Tim, et.al, We use rman on 8.0.6.3 databases. One of our duvelopers was trying to delete records from a table and her query deleted everything from the table. This caused the application to fail and a point-in_time recovery was nessessary. I was given a time of 11:00AM. I had recovered the database until 11:00AM and before opening the database with resetlogs, I asked if the time was correct. After several minutes of discussion with all concerned, it was decided that actually we needed to recovery until 9:00AM. I changed the until time in the restore script, restored the database until 9:00AM and recovered it and opened it resetlogs. I had no problem with this. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 5:08 PM The one situation where a cold backup can be considered necessary is following an OPEN RESETLOGS. If you have a no data loss requirement and you are in ARCHIVELOG mode, then there is a window following an OPEN RESETLOGS where, if the media crashes prior to completing a hot backup, you could be unrecoverable. File that one under a bad day... It is not a hard-and-fast requirement however, as there is a fairly narrow set of circumstances (available since v7.3.3) where it is possible to recover using backups and archivelogs generated prior to an OPEN RESETLOGS and then continue the roll-forward using archivelogs generated after the RESETLOGS, but there several gotchas that can mess that up. It would be a gamble to rely on pulling that rabbit out of the hat... If you ever find yourself entering the command ALTER DATABASE OPEN RESETLOGS on a database that you *really* care about (should be recognized by the same shallow-breathing sweaty-palm symptoms you get when you say, ...now, just hand the gun to me, slowly...), then please get an immediate cold backup before opening the database to users. You may have to argue for it, but be sure to leave time for it when folks are asking, When will the database be back? ..other than that situation, there is no advantage of a cold backup over a hot backup; just my $0.02... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 12:33 AM Cold backup is very good because I don't have to monitor database during the cold backup, no objects will run out of space and I can enjoy a peaceful time without any chance for my beeper to go off. You must admit that a cold backup cannot guarantee you that. Unfortunately, my bosses somehow got the curious idea that they have paid big bucks for all those HP 9000 to work and not to sit idle. They even calculated a downtime cost per hour for each critical system and they are extremely reluctant to have them down for extended periods of time (1.1 TB database cannot be backed up in minutes, even with Asymmetrix). That is why they bought me a toy called OPS and why there are policies and procedures about who and how gets things in
On-Line Classes for OCA/OCP
I would like to know what classes are available on-line for the OCA/OCP Intro. to 91: SQL Exam - 1Z0-007 Thanks, Ken Janusz, CPIM
Re: Cost of joins
No such thing. What you are seeking is hard-and-fast rules (a la the rule-based optimizer), which is going obsolete and for good reason... Rather, it would be better to understand the situations in which each type of join works well and the situations in which each type of join works poorly. There is lots of good documentation on this in the Oracle Server Tuning references online at http://otn.oracle.com... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 10:04 AM Hi there Can someone please give me in order of preference/cost the relevant costs for the different joins. IE: This join is cheap, This is very expensive, This is bad and always avoid. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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: ROLLBACK SEGMENT FRAGMENTATION
Why do you think it is fragmented? Why do you think it is a problem? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 10:36 AM Hi My rollback tablespace is highly fragmented.I am thinking to do like following? -Create new rollback tablespace rbs1 -Create rollback segments -Offline all rollback segments from old rollback tablespace (rbs) -Drop rollback segments from rbs tablespace -Drop tablespace rbs -Create RBS tablespace -Create rollback segments on RBS tablespace -Drop tablespace RBS1 Is this way is good to manage? Let me know if anythings are missing please? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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 and case structure
CASE in PL/SQL serves a slightly different function, but DECODE should do the trick. Try: select acct_no, DECODE(substr(acct_no,16,1), '1','one', '2','two', 'other') as DESCR from star.kills; I also changed desc to descr, since desc is a reserved word. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Droogendyk, Harry [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Subject: SQL and case structure Listers: I've used SAS's version of SQL and it allows the coding of conditional logic in the SELECT statement: proc sql; select acct_no, case substr(acct_no,16,1) when '1' then 'one' when '2' then 'two' else 'other' end as desc from star.kills; quit; The same syntax does not work in SQL*Plus for Oracle 8. Can someone point me to the correct syntax? Secondly, any URLs for this kind of information would be most appreciated. Regards, Harry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: Cost of joins
It depends on what you're doing. The use of a join technique over another depend on how much data you need to access. If you read very few information from both tables then a nested loop is the fastest way to get data. To use a nested join at a cheap cost you need a good index on the outer table. On the other hand, if your query output contained allmost all data from both tables than an hash join or a sort merge is better than a nested loop. If you're in a DW and have data organized with dimension and fact tables than the optimiser should be using star transformation. --- Leonard, George [EMAIL PROTECTED] a écrit : Hi there Can someone please give me in order of preference/cost the relevant costs for the different joins. IE: This join is cheap, This is very expensive, This is bad and always avoid. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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 to retrieve numeric values only from a varchar2?
Supported? It's been around for several years, beginning with the first version of Oracle's App server. I guess it's supported. It seems to be bug free, and is based on std regular expression definitions. The source is included as well. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/07/2002 12:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: how to retrieve numeric values only from a varchar2? Just when I think I'm cought up on the Oracle-installed PL/SQL packages... Seeing as OWA_PATTERN isn't in the docs (not on tahiti, anyway), is it supported??? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 1:58 PM To: Multiple recipients of list ORACLE-L Subject: Re: how to retrieve numeric values only from a varchar2? Use owa_pattern. See $ORACLE_HOME/rdbms/admin/pubpat.sql Here's an example: declare tstr varchar2(100) := 'this string has 382 embedded numeric data'; begin dbms_output.put_line( tstr); -- remove the digits owa_pattern.change( tstr, '\D', '', 'g'); dbms_output.put_line( tstr); end; / Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL and case structure
Listers: I've used SAS's version of SQL and it allows the coding of conditional logic in the SELECT statement: proc sql; select acct_no, case substr(acct_no,16,1) when '1' then 'one' when '2' then 'two' else 'other' end as desc from star.kills; quit; The same syntax does not work in SQL*Plus for Oracle 8. Can someone point me to the correct syntax? Secondly, any URLs for this kind of information would be most appreciated. Regards, Harry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Droogendyk, Harry 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).
