what's the exact meaning of those messages?
Hi, I found the following message from the alert.log: Sun Dec 29 22:35:42 2002 ARC0: Beginning to archive log# 3 seq# 126 ARC0: Failed to archive log# 3 seq# 126 Sun Dec 29 22:35:42 2002 ARCH: Completed archiving log# 3 seq# 126 It seemed the ARC didn't work in the begining of archive, but worked finally after a while, I got lots of those messages from alert.log file. what's the exact meaning of those messages? My Oracle is 8.1.7.3.0. and OS is aix 5.1 Thanks in advance. Jim [EMAIL PROTECTED] 2002-12-30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
hi, Wish u a very New Year Sudhakar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sudhakar Reddy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Physical reads
Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Physical reads
Pradeep: It is NOT number of read request to the I/O sub-system. It is number of blocks read from the disk. Note that the physical read is not necessarily a physical read. There are chances the block could be from Memory (file system buffer cache). This happens when you have quick IO and/or a large file system buffer cache. So, in general 'physical reads' are the number of blcoks read in to the buffer cache from the disk !! --- [EMAIL PROTECTED] wrote: Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Physical reads
it means no. of DB blocks read from the disk. Regards Naveen -Original Message- Sent: Monday, December 30, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Physical reads
If positive, yes ... - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 30 Dec 2002 01:23:44 Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Physical reads -oops
Ooops. Read too fast. Blocks. You may be interested in checking against V$FILESTAT, where you have both actual 'numbers of requests' of reads/writes and 'numbers of blocks'. Dismally close on some databases, BTW. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 30 Dec 2002 01:23:44 Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: what's the exact meaning of those messages?
It's just that it couldn't complete archiving immediately, so it will do it asap. That delay (which seems very small from the numbers you show) can be decreased by either making archiving faster (faster disks? network? whatever...) or by archiving less (yeah, right, that sounds easy to do!). Mogens Jim wrote: Hi, I found the following message from the alert.log: Sun Dec 29 22:35:42 2002 ARC0: Beginning to archive log# 3 seq# 126 ARC0: Failed to archive log# 3 seq# 126 Sun Dec 29 22:35:42 2002 ARCH: Completed archiving log# 3 seq# 126 It seemed the ARC didn't work in the begining of archive, but worked finally after a while, I got lots of those messages from alert.log file. what's the exact meaning of those messages? My Oracle is 8.1.7.3.0. and OS is aix 5.1 Thanks in advance. Jim [EMAIL PROTECTED] 2002-12-30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: year 2059 problem
UNIX stores time as amount of seconds passed since the 1st of January 1970. Since it is 32-bit value in modern Unices, it can hold up to 2,147,483,648 or approximatively 68 years. The counteroverflows on 19th of January 2038 at 3:14:07 AM. People believe that all the hardware will be 64-bit by that time. But apparently it is not the case in Nepal. Oracle, internally can handle dates well beyond that. It is definitely W2K's deficiency. Any other 32-bit platform will have it too. Looks like the only solution is to move to a 64-bit platform/OS. For more information see: http://www.gsp.com/2038/ Hope it helps. Nic. - Original Message - From: Amit Nargotra To: Multiple recipients of list ORACLE-L Sent: Monday, December 30, 2002 6:28 AM Subject: year 2059 problem This strange problem we are facing while implemting Oracle Based ERP at Nepal for asian paints. Nepal follows Hindu calender, as per the hindu calender the current year is 2059. as we try to install oracle 9i keeping year as 2059. oracle gets installed, but it is unable to create database.(OS is windows - 2000 server) even D2K is not running on 2059. we have tried different years and we found that till 2035 oracle is able to create database after 2035 it fails to create database. we have contacted oracle india also. they have said this is windows related problem. can any body help us out. Thanks in advance.
9i RAC and Backup/Recovery....
Title: 9i RAC and Backup/Recovery Hey guys- Anyone out there got any gotchas related to backup with RMAN within a 9i RMAN environment? Or better yet-any test scenarios that folks would like to see Thanks! Greg
Re: what's the exact meaning of those messages?
Check your backup script to see if it forces log switches (alter system archive log). If it does, you may be hitting bug 1377090. See note 119547.1. It is a message that you can ignore if it occurs during the alter system.. command. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 12/30/02 02:33AM Hi, I found the following message from the alert.log: Sun Dec 29 22:35:42 2002 ARC0: Beginning to archive log# 3 seq# 126 ARC0: Failed to archive log# 3 seq# 126 Sun Dec 29 22:35:42 2002 ARCH: Completed archiving log# 3 seq# 126 It seemed the ARC didn't work in the begining of archive, but worked finally after a while, I got lots of those messages from alert.log file. what's the exact meaning of those messages? My Oracle is 8.1.7.3.0. and OS is aix 5.1 Thanks in advance. Jim [EMAIL PROTECTED] 2002-12-30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Deleting some or all the child records from the child tables and
Hi List, How do I delete all/few lower level records based on the column value of the parent table through SQL or Pl/SQl? If so, could you please send me the SQL queries for the same? Please note that I cannot enforce the 'ON DELETE CASCADE' rule on the foreign key constraints. Any help in this regard is very much appreciated. Thanks and Regards, Ranganath WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:Killed status
First, find the 'killed' session is v$session and match the paddr with the addr in v$process to find the spid(system process id). I assume your running some sort of Unix, so then run a kill -9 on that spid. Now, take what you found in v$session and find the dumb bunny who has turned off their client workstation before closing their Oracle session blow their head off. The main reason that I've found over the last 10 years for this situation to exist is some a^hole powers off their workstation of else kills the process on their workstation while the database is processing some statement for them. Oracle wants to send them the message of the status of their command and session, but can't since their not listening any more. People like this will hold locks forever, and even prevent anything other than a shutdown abort from succeeding. People like this get somewhat of a different handling for me. First I kill off their session by the above means, which will wake pmon up and cause it to rollback everything rather quickly, and their account gets locked until their manager requests that it be re-opened. Haven't had to wield that axe around here for some time now. Probably because it's known about and everyone does a polite shutdown. AHHH, cooperation is so nice!! Dick Goulet Reply Separator Author: =?big5?B?c2h1YW4udGF5XChQQ0m+R7hSs9RcKQ==?= [EMAIL PROTECTED] Date: 12/25/2002 7:48 PM Dear all DBAs, Is there any way to remove the lock of table which status is already marked as killed? No matter how many times i try to kill the session, it still marked as killed, but it still won't release the lock. Thanks in advance. !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=big5 META content=MSHTML 6.00.2800.1106 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff DIVFONT face=MingLiu size=2Dear all DBAs,/FONT/DIV DIVFONT face=²OcúÅé size=2/FONTnbsp;/DIV DIVFONT face=²OcúÅé size=2Is there any way to remove the lock of table which status is already marked as killed?/FONT/DIV DIVFONT face=²OcúÅé size=2No matter how many times i try to kill the session, it still marked as killed,/FONT/DIV DIVFONT face=²OcúÅé size=2but it still won't release the lock./FONT/DIV DIVFONT face=²OcúÅé size=2/FONTnbsp;/DIV DIVFONT face=²OcúÅé size=2Thanks in advance./FONT/DIV/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
columns with primary key constraint
Good Morning List, I am trying to build a query to display the following for any table column_name is_pk --- - COLUMN_1(PK-1) COLUMN_2(PK-2) COLUMN_3 COLUMN_4 COLUMN_5 COLUMN_6 I can get the two columns with (PK) with... select utc.column_name ,decode(ucc.column_name,null,null,' (PK-'||ucc.position||')') is_pk from user_tab_columns utc, user_cons_columns ucc, user_constraints uc where utc.table_name = 'TAB_A' anduc.constraint_type = 'P' andutc.table_name = uc.table_name anduc.constraint_name (+) = ucc.constraint_name andutc.column_name (+) = ucc.column_name order by utc.column_id but can't seem to get the columns not part of the PK. I suspect I am missing an outer-join somewhere, but can't seem to figure it out. If possible I would like to put an (FK) next to columns that have a FK constraint as well. Thanks. = Steve Haas Opus Consultants, LLC 860.408.1512 (office/fax) 860.651.9475 (home) [EMAIL PROTECTED] [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Enqueue Waits in Oracle Financials
John, I know this is an old topic, but Oracle Support proposed a patch and we finally got it tested. It looks like this enqueue wait goes away with the application of concurrent processing rollup patchset C 2385942. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 8:25 AM To: Multiple recipients of list ORACLE-L Subject: Enqueue Waits in Oracle Financials I noticed a lot of enqueue wait events in our 11i database. I ran some queries and was able to determine the process that is incurring these waits. I dutifully did a set event 10046 and examined the trace file. I've also queried v$lock. I've figured out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out exactly what we are waiting for. Although by monitoring the current SQL statement for the offending process, I see that it does a SELECT FOR UPDATE in the FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock, which I see, but I don't think it explains the UL lock. I've seen examples on how to interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance. I believe that the offending process is the Internal manager, but I would like to understand a little more about what is occurring. Is this a typical problem in 11i? I guess the ICM may issue user defined locks, then just waits for a certa! in! amount of time. I would guess that all 11i databases have a high number of enqueue waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64. Thank you, Jay Sample output from the trace: WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0 WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0 WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0 So if I check out p1 I see a UL lock mode 2: SQL run 1 SELECT chr(bitand(1431044098,-16777216)/16777215)|| 2 chr(bitand(1431044098, 16711680)/65535) Lock, 3 to_char( bitand(1431044098, 65535) )Mode 4* from dual Lo M -- - UL 2 cut **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
Re: year 2059 problem
Hi I have just tested with 2037 on a linux (2.4.18) with rdbms 9.2.0.2 and this works. Lyndon Tiu wrote: Hmmm, anyone tried Linux Oracle with year 2059? -- Lyndon Tiu On Sunday 29 December 2002 08:28 pm, Amit Nargotra wrote: This strange problem we are facing while implemting Oracle Based ERP at Nepal for asian paints. Nepal follows Hindu calender, as per the hindu calender the current year is 2059. as we try to install oracle 9i keeping year as 2059. oracle gets installed, but it is unable to create database.(OS is windows - 2000 server) even D2K is not running on 2059. we have tried different years and we found that till 2035 oracle is able to create database after 2035 it fails to create database. we have contacted oracle india also. they have said this is windows related problem. can any body help us out. Thanks in advance. -- Peter Gram, Miracle A/S Phone : +45 2527 7107, Fax : +45 4466 8856 mailto:[EMAIL PROTECTED] - http://MiracleAS.dk
Re: Re:Killed status
All righty, guys thanks for the reply. - Original Message - To: shuan.tay(PCI¾G¸R³Ô) [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 8:46 PM First, find the 'killed' session is v$session and match the paddr with the addr in v$process to find the spid(system process id). I assume your running some sort of Unix, so then run a kill -9 on that spid. Now, take what you found in v$session and find the dumb bunny who has turned off their client workstation before closing their Oracle session blow their head off. The main reason that I've found over the last 10 years for this situation to exist is some a^hole powers off their workstation of else kills the process on their workstation while the database is processing some statement for them. Oracle wants to send them the message of the status of their command and session, but can't since their not listening any more. People like this will hold locks forever, and even prevent anything other than a shutdown abort from succeeding. People like this get somewhat of a different handling for me. First I kill off their session by the above means, which will wake pmon up and cause it to rollback everything rather quickly, and their account gets locked until their manager requests that it be re-opened. Haven't had to wield that axe around here for some time now. Probably because it's known about and everyone does a polite shutdown. AHHH, cooperation is so nice!! Dick Goulet Reply Separator Author: =?big5?B?c2h1YW4udGF5XChQQ0m+R7hSs9RcKQ==?= [EMAIL PROTECTED] Date: 12/25/2002 7:48 PM Dear all DBAs, Is there any way to remove the lock of table which status is already marked as killed? No matter how many times i try to kill the session, it still marked as killed, but it still won't release the lock. Thanks in advance. !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=big5 META content=MSHTML 6.00.2800.1106 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff DIVFONT face=MingLiu size=2Dear all DBAs,/FONT/DIV DIVFONT face=²OcúÅé size=2/FONTnbsp;/DIV DIVFONT face=²OcúÅé size=2Is there any way to remove the lock of table which status is already marked as killed?/FONT/DIV DIVFONT face=²OcúÅé size=2No matter how many times i try to kill the session, it still marked as killed,/FONT/DIV DIVFONT face=²OcúÅé size=2but it still won't release the lock./FONT/DIV DIVFONT face=²OcúÅé size=2/FONTnbsp;/DIV DIVFONT face=²OcúÅé size=2Thanks in advance./FONT/DIV/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?UTF-8?Q?shuan.tay\=28PCI=E9=84=AD=E7=A5=BA=E5=8B=9B\=29?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Was: Rebuilding Indexes, Now: KEEP INDEX
but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I
Any Collaboration Suite or 9iAS listservers?
Does anyone know of any Collaboration Suite or 9iAS specific listservers? I tried subscribing to the webcys_l listserver but it appears to be inactive. TIA Michael D. Gilly Sr. email: [EMAIL PROTECTED]
Setting up raw partition on Linux
Dear List: I am not an unix admin nor I know a lot about Linux, however I need to setup RAC on Linux, I am confused about setting up raw partitions for RAC. On the Linux installation Disk setup screen, I can create new partition, however I don't know what type I should use for the raw partition, none of types ext2, ext3, raid, swap and vfat seem to be appropriate. Any ideas?? Any reference link to know more about this?? Jos Yahoo! Greetings - Send your seasons greetings online this year!
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
Yes, but at least the index definition will be preserved so that you could do a simple ALTER INDEX ... REBUILD rather than finding and firing off a script. Particularly useful if you have a lots of partitioned indexes. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 9:19 AM but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]:
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
yes but :) It's a partitioned index. Yes, the partition goes into an UNUSABLE state. If I drop the constraint without keep index and without saving off the statement to rebuild it properly, I drop the ENTIRE index and I end up with a non-partitioned index in the schema owner's default tablespace when I rebuild the constraint. So if I use KEEP INDEX, yes I'll need to rebuild the partition, but I won't have to rebuild the entire index and I won't have to save off the SQL to rebuild it properly. As the number of rows grows, rebuilding the entire index becomes time-prohibitive. Of course, I've already written that SQL statement, but that was fun. I'd still rather do the work properly and in a more efficient manner. --- Connor McDonald [EMAIL PROTECTED] wrote: but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it
RE: Those Pesky Little Audit Files (ora_99999.aud)
Depending on the OS, we had created a batch job to simply delete these aud files after a specific amount of time. You may want to implement a similar process. 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: Thursday, December 26, 2002 8:59 PM To: Multiple recipients of list ORACLE-L Subject:Re: Those Pesky Little Audit Files (ora_9.aud) IIRC, these files are generated whenever someone logs in as sysdba or internal. I don't know of any way to stop them. --- Kevin Lange [EMAIL PROTECTED] wrote: I thought I had these files stopped but apparently not. Is there somone out there who can tell me how to stop the Audit files from appearing in the audit_file_dest ??? I thought if I set the audit_trail to false then these would stop as well ... Apparently not. Anyone have an idea how to turn them off ?? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). 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.net -- 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: 9i RAC and Backup/Recovery....
I don't know from experience, but I have been told be those who have used it, that it is pretty much like 8i. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 7:56 AM Hey guys- Anyone out there got any gotchas related to backup with RMAN within a 9i RMAN environment? Or better yet-any test scenarios that folks would like to see Thanks! Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
Memory leak in OCI call
HI. I am using OCIConnectionPoolCreate to establish Connection with database. When i run through purifier its showing Leak. The traces are as follows MLK: 128 bytes leaked at 0x1b7c830 This memory was allocated from: malloc [rtlib.o] sltspcinit [sltspc.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] MLK: 104 bytes leaked at 0x1bec0b0 This memory was allocated from: malloc [rtlib.o] sltsmxi[sltsm.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] Please help me in this... Thanks in Advance -Naren. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 8.1.7 with *what* version of Portal??
Add one more thing, you need to make sure to install oracle jvm and intermedia before you install the portal. otherwise, portal won't work without these feafures. Joan Hemant K Chitale wrote: Portal is part of iAS. iAS 1.0.2.X, preferably 1.0.2.2.2, runs Portal 3.0.9.X.X [base version 3.0.9.8.0] against a database running 8.1.7.X I'm not sure if iAS 2.x which has been renumbered to iAS 9.0.2 runs against 8.1.7. I believe it should. To answer your question, you'd need to install Portal from iAS. This installs the Apache/Jserv, and portal product files in the Apache ORACLE_HOME and Portal database schema into the database, which should be running from a seperate ORACLE_HOME, even if on the same server. Hemant At 05:38 PM 27-12-02 -0800, you wrote: we have 8i (8.1.7.1) running at our shop and one of our developers wants to use WebDB (what I understand is now Portal). in checking OTN and other places, I can't figure out what version of Portal (or WebDB) I should be installing, nor where I can get it. can anyone tell me what version I should be trying to install, where I can get it and where any resources on installation and configuration might be located? it is my understanding that Portal is a collection of packages that work with our existing database and doesn't need anything extra. is that also truy? aside from the raw 8i database, will I need to install anything else (yes, we have apache running ok)? Deb Carbide Systems Group _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle Developer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rebuilding Indexes...
Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Memory leak in OCI call
Are you making any calls to OCIConnectionPoolDestroy anywhere in your program? HI. I am using OCIConnectionPoolCreate to establish Connection with database. When i run through purifier its showing Leak. The traces are as follows MLK: 128 bytes leaked at 0x1b7c830 This memory was allocated from: malloc [rtlib.o] sltspcinit [sltspc.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] MLK: 104 bytes leaked at 0x1bec0b0 This memory was allocated from: malloc [rtlib.o] sltsmxi[sltsm.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] Please help me in this... Thanks in Advance -Naren. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Oracle.exe , CPU running at near 85%
Rick, Shot in the dark =8-) If Context (a.k.a. InterMedia) is involved I've seen a CPU pegged at 90% with a web app doing a dynamic text query using CONTAINS and a '-' minus operator. They actually didn't know about the '-' query op...they ended up searching the text string input and replacing '-' with '\-' and the problem dis-appeared. This was 8.1.7.0.0 BTW on Win2K. I found the problem using v$session joined to v$sqlarea. Luckily only the suspect query was running at the time. There are also spin problems with Net8 and many OCI multi-threaded connections. Just guessingI just got back from Christmas in Cuba and I'm trying to exercise my brain...it hurts =8-0 HTH Jeff Herrick On Fri, 27 Dec 2002 [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 on NT 4.0 Oracle.exe is running at about 85% CPU utilization. What can I check to see why that is the case? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeff Herrick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tales Of Big Hammer #10046 (AKA event 10046)
Title: Tales Of Big Hammer #10046 (AKA event 10046) On Friday our application support person called me and said User is getting a ORA-1403 error in one form. I should mention that this is complex. And I need a solution immediately. So, I gave them the hammer (with the sql string to put in pre-form trigger in the form for the 546th time, they never save their emails, especially from a DBA). They set the event and ran the form. Now the entire support group recently had to face me for an hour-long discussion on TKPROF (AKA the Hammer) and debugging techniques, so they read the tracefile and obediently tried to find a line with 'ERR=1043' but couldn't find any. So I looked at the trace file, and sure it was, it didn't show the error, but after one SQL it showed a 'SQL*Net break/reset to client' event followed by a ROLLBACK. I directed them to that statement. This person was happy that they found the culprit statement, but couldn't find what was going wrong. So they went through the presentation notes and used dbms_support.start_trace(true,true); statement and ran the form again. This time, as the trace dumped the bind values, they found the set of values on which the query returned 1403, and got back to the developer with a solid case. So, once again ... if you have 10046 hammer, most problems are like nails. 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! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: columns with primary key constraint
Hi Steve, Are you looking for something like this?: SELECT utc.table_name, utc.column_name ,DECODE(NVL(ucct.cln, ' '), ' ',' ', '(PK-'||ucct.pos||')') is_pk FROM user_tab_columns utc, ( select uc.table_name tn, ucc.constraint_name cn, ucc.column_name cln, ucc.position pos FROM user_cons_columns ucc, user_constraints uc WHERE ucc.constraint_name = uc.constraint_name ANDuc.constraint_type = 'P' ) ucct WHERE utc.table_name = ucct.tn (+) AND utc.column_name = ucct.cln (+) AND utc.table_name = 'tab_name' ORDER BY ucct.pos / Regards, Charu -Original Message- Sent: Monday, December 30, 2002 2:39 PM To: Multiple recipients of list ORACLE-L Good Morning List, I am trying to build a query to display the following for any table column_name is_pk --- - COLUMN_1(PK-1) COLUMN_2(PK-2) COLUMN_3 COLUMN_4 COLUMN_5 COLUMN_6 I can get the two columns with (PK) with... select utc.column_name ,decode(ucc.column_name,null,null,' (PK-'||ucc.position||')') is_pk from user_tab_columns utc, user_cons_columns ucc, user_constraints uc where utc.table_name = 'TAB_A' anduc.constraint_type = 'P' andutc.table_name = uc.table_name anduc.constraint_name (+) = ucc.constraint_name andutc.column_name (+) = ucc.column_name order by utc.column_id but can't seem to get the columns not part of the PK. I suspect I am missing an outer-join somewhere, but can't seem to figure it out. If possible I would like to put an (FK) next to columns that have a FK constraint as well. Thanks. = Steve Haas Opus Consultants, LLC 860.408.1512 (office/fax) 860.651.9475 (home) [EMAIL PROTECTED] [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
rebuilding indexes, the tests
Okay, I did 4 different tests, as follows: in each test I created a partitioned table. Test 1 -- create a unique partitioned index, then create a primary key constraint with the same columns Test 2 -- create a non-unique partitioned index, then create a primary key constraint with the same columns Test 3 -- create a primary key constraint with the using index clause, partitioned Test 4 -- same as Test 3 In each case I then loaded valid data via insert and checked that for Test 1 and Test 2 the indexes were being used to enforce the constraint. They were. All partitions were in a USABLE state. I then loaded, via sqlloader direct=true, one row that would cause one of the partitions to become UNUSABLE. I then disabled the primary key constraint. For Test 4 only, I did the disable using the keep index clause. Next I checked for the existence of the index. In Test 2 (non-unique index) and Test 4 (keep index clause) the index remained. In Test 1 (unique index) and Test 3 (without the keep clause), the index disappeared. For both Test 2 and Test 4, I still needed to rebuild the index partition after I removed the duplicates to make the partition USABLE again. so the conclusion is, if I want not to lose the entire partitioned index, I need either a non-unique index to enforce the constraint or, for 9i only, the keep index clause when I disable the constraint. Since, in our case, the constraints have already been built using the INDEX TABLESPACE clause, I'll use the keep index clause when I have to remove duplicates. Thanks to all who responded and boy did I have fun proving this out :) Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Rebuilding Indexes...
The space saving is good if you are not planning on doing much more with those indexes (ie dml). The space saving might be very very bad if there is lots of dml to come. If you're indexes reached equilibrium during normal operation of (say) 70% used, and then you rebuild them with (say) pctfree 10, they pop up to 90% used and thus the saved space. But if normal operations continue, they will eventually drift out to 70% used again and what's worse, you might get a bucketload of index block splits during the process...could be a nasty hit on dml performance hth connor --- [EMAIL PROTECTED] wrote: Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: columns with primary key constraint
Charu, This is exactly what I was looking for. I will try to modify this now to include an (FK) for any column with a foreign key constraint. Thanks for the extra pair of eyes. Happy New Year all! Steve --- Charu Joshi [EMAIL PROTECTED] wrote: Hi Steve, Are you looking for something like this?: SELECT utc.table_name, utc.column_name ,DECODE(NVL(ucct.cln, ' '), ' ',' ', '(PK-'||ucct.pos||')') is_pk FROM user_tab_columns utc, ( select uc.table_name tn, ucc.constraint_name cn, ucc.column_name cln, ucc.position pos FROM user_cons_columns ucc, user_constraints uc WHERE ucc.constraint_name = uc.constraint_name ANDuc.constraint_type = 'P' ) ucct WHERE utc.table_name = ucct.tn (+) AND utc.column_name = ucct.cln (+) AND utc.table_name = 'tab_name' ORDER BY ucct.pos / Regards, Charu -Original Message- Sent: Monday, December 30, 2002 2:39 PM To: Multiple recipients of list ORACLE-L Good Morning List, I am trying to build a query to display the following for any table column_name is_pk --- - COLUMN_1(PK-1) COLUMN_2(PK-2) COLUMN_3 COLUMN_4 COLUMN_5 COLUMN_6 I can get the two columns with (PK) with... select utc.column_name ,decode(ucc.column_name,null,null,' (PK-'||ucc.position||')') is_pk from user_tab_columns utc, user_cons_columns ucc, user_constraints uc where utc.table_name = 'TAB_A' anduc.constraint_type = 'P' andutc.table_name = uc.table_name anduc.constraint_name (+) = ucc.constraint_name andutc.column_name (+) = ucc.column_name order by utc.column_id but can't seem to get the columns not part of the PK. I suspect I am missing an outer-join somewhere, but can't seem to figure it out. If possible I would like to put an (FK) next to columns that have a FK constraint as well. Thanks. = Steve Haas Opus Consultants, LLC 860.408.1512 (office/fax) 860.651.9475 (home) [EMAIL PROTECTED] [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:
sudhakar Reddy wrote: hi, Wish u a very New Year Sudhakar You are not taking risks :-). Very wise. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: columns with primary key constraint
Title: RE: columns with primary key constraint
SELECT /*+ no_merge(utc) no_merge(ucct.uc) no_merge(ucct.ucc) */
utc.owner
,utc.table_name
,utc.column_name
,DECODE(ucct.constraint_type, 'P',DECODE(NVL(ucct.cln, ' '), ' ',' ','(PK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_pk
,DECODE(ucct.constraint_type, 'R',DECODE(NVL(ucct.cln, ' '), ' ',' ','(FK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_fk
FROM DBA_TAB_COLUMNS utc,
(SELECT uc.owner, uc.table_name tn, ucc.constraint_name cn, ucc.column_name cln,
ucc.position pos, uc.constraint_type
FROM DBA_CONS_COLUMNS ucc,
DBA_CONSTRAINTS uc
WHERE ucc.owner = uc.owner
AND ucc.constraint_name = uc.constraint_name
AND uc.constraint_type IN ('P','R')) ucct
WHERE utc.table_name = ucct.tn (+)
AND utc.column_name = ucct.cln (+)
AND utc.owner = ucct.owner
AND utc.OWNer = 'OWNER'
ORDER BY utc.owner, utc.table_name, ucct.pos
/
Just an improvement on Charu's script ...
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: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: columns with primary key constraint
Charu,
This is exactly what I was looking for.
I will try to modify this now to include an
(FK) for any column with a foreign key
constraint.
Thanks for the extra pair of eyes.
Happy New Year all!
Steve
--- Charu Joshi [EMAIL PROTECTED] wrote:
Hi Steve,
Are you looking for something like this?:
SELECT utc.table_name, utc.column_name
,DECODE(NVL(ucct.cln, ' '), ' ',' ',
'(PK-'||ucct.pos||')') is_pk
FROM user_tab_columns utc,
(
select uc.table_name tn,
ucc.constraint_name cn, ucc.column_name cln,
ucc.position pos
FROM user_cons_columns ucc,
user_constraints uc
WHERE ucc.constraint_name =
uc.constraint_name
AND uc.constraint_type = 'P'
) ucct
WHERE utc.table_name = ucct.tn (+)
AND utc.column_name = ucct.cln (+)
AND utc.table_name = 'tab_name'
ORDER BY ucct.pos
/
Regards,
Charu
-Original Message-
Sent: Monday, December 30, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L
Good Morning List,
I am trying to build a query to display the
following for any table
column_name is_pk
--- -
COLUMN_1 (PK-1)
COLUMN_2 (PK-2)
COLUMN_3
COLUMN_4
COLUMN_5
COLUMN_6
I can get the two columns with (PK) with...
select utc.column_name
,decode(ucc.column_name,null,null,'
(PK-'||ucc.position||')') is_pk
from user_tab_columns utc,
user_cons_columns ucc,
user_constraints uc
where utc.table_name = 'TAB_A'
and uc.constraint_type = 'P'
and utc.table_name = uc.table_name
and uc.constraint_name (+) =
ucc.constraint_name
and utc.column_name (+) = ucc.column_name
order by utc.column_id
but can't seem to get the columns not part of
the
PK. I suspect I am missing an outer-join
somewhere, but can't seem to figure it out.
If possible I would like to put an (FK) next
to
columns that have a FK constraint as well.
Thanks.
=
Steve Haas
Opus Consultants, LLC
860.408.1512 (office/fax)
860.651.9475 (home)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Steven Haas
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list
and web hosting services
-
To REMOVE yourself from this mailing list, send
an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in
the message BODY, include a line containing:
UNSUB ORACLE-L
(or the name of mailing list you want to be
removed from). You may
also send the HELP command for other
information (like subscribing).
*
Disclaimer
This message (including any attachments)
contains
confidential information intended for a
specific
individual and purpose, and is protected by
law.
If you are not the intended recipient, you
should
delete this message and are hereby notified
that
any disclosure, copying, or distribution of
this
message, or the taking of any action based on
it,
is strictly prohibited.
*
Visit us at http://www.mahindrabt.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Charu Joshi
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list
and web hosting services
-
To REMOVE yourself from this mailing list, send
an E-Mail message
this doesn't look right
As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: this doesn't look right
Title: RE: this doesn't look right I think it is to accommodate sys.attrcol$.name column ... In our 9202, it has references to Spatial objects ... 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: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, December 30, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Subject: this doesn't look right As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: this doesn't look right
Rachel DBA_CONS_COLUMNS is a view. DECODE is applied against attrcol$.name -- which is varchar 4000. Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Rachel Carmichael wrote: As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: this doesn't look right
It might be for a new feature, two_page_long_columnames=true ;-) Rodd On Mon, 2002-12-30 at 14:23, Rachel Carmichael wrote: As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Rodd Holman [EMAIL PROTECTED] Romans 1:16-17 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: this doesn't look right
Varchar2(4000) is coming from sys.attrcol$ table. dba_tab_columns does not have a join this table. I am sure there are people on this list who'll enlighten me about contents of sys.attrcol$ table. select u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,sys.obj$ o, sys.attrcol$ ac where c.owner# = u.user# and c.con# = cd.con# and cd.type# != 12 /* don't include log groups */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+) -Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 12:23 PM As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Shaleen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: this doesn't look right
Looking at contents of attrcol$ , name column can have more than 30 characters since it seems to be joining 2 attributes OBJ#INTCOL# NAME -- -- -- 45149 41 USER_DATA.AGENT_ADDRESS 45149 42 USER_DATA.AGENT_PROTOCOL 45149 43 USER_DATA.ORIGINAL_MSGID 45149 44 USER_DATA.SUB_NAME 45149 45 USER_DATA.SUB_NAMESPACE - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 30, 2002 2:05 PM Varchar2(4000) is coming from sys.attrcol$ table. dba_tab_columns does not have a join this table. I am sure there are people on this list who'll enlighten me about contents of sys.attrcol$ table. select u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,sys.obj$ o, sys.attrcol$ ac where c.owner# = u.user# and c.con# = cd.con# and cd.type# != 12 /* don't include log groups */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+) -Shaleen - Original Message - From: Rachel Carmichael [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 12:23 PM Subject: this doesn't look right As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Shaleen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: EXCESSIVE PGA MEMORY 9i
Title: RE: EXCESSIVE PGA MEMORY 9i Guys, Installed a database - currently doing an upgrade to 9.0.2.2 following instructs in README that says to open migrate then run catpatch.sql. The database is using excessive amounts of memory for process global area. I reduced pga_aggregate_target from Oracle's default setting to 10M - read something about using this stops the use of sort_area_size. Any pro/cons on using pga_aggregate_target, experiences with sizing of it. Thanks, Paula
Re: Rebuilding Indexes...
Ditto. Conner did a better job of stating it than I was going to. :) Jared On Monday 30 December 2002 11:08, Connor McDonald wrote: The space saving is good if you are not planning on doing much more with those indexes (ie dml). The space saving might be very very bad if there is lots of dml to come. If you're indexes reached equilibrium during normal operation of (say) 70% used, and then you rebuild them with (say) pctfree 10, they pop up to 90% used and thus the saved space. But if normal operations continue, they will eventually drift out to 70% used again and what's worse, you might get a bucketload of index block splits during the process...could be a nasty hit on dml performance hth connor --- [EMAIL PROTECTED] wrote: Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET:
Re: Tales Of Big Hammer #10046 (AKA event 10046)
Title: Tales Of Big Hammer #10046 (AKA event 10046) Generally you won't find "err=1403" text in the raw ".trc" file. Instead, if you carefully examine the FETCH lines, you'll see "r=0" (i.e. zero rows returned) in amongst all the other statistics. Very very difficult to catch and often requires a Vulcan mind-meld to the application over several hours of careful perusal(something best left to Vulcans)... Great job! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, December 30, 2002 10:43 AM Subject: Tales Of Big Hammer #10046 (AKA event 10046) On Friday our application support person called me and said "User is getting a ORA-1403 error in one form. I should mention that this is complex. And I need a solution immediately." So, I gave them the hammer (with the sql string to put in pre-form trigger in the form for the 546th time, they never save their emails, especially from a DBA). They set the event and ran the form. Now the entire support group recently had to face me for an hour-long discussion on TKPROF (AKA the Hammer) and debugging techniques, so they read the tracefile and obediently tried to find a line with 'ERR=1043' but couldn't find any. So I looked at the trace file, and sure it was, it didn't show the error, but after one SQL it showed a "'SQL*Net break/reset to client'" event followed by a ROLLBACK. I directed them to that statement. This person was happy that they found the culprit statement, but couldn't find what was going wrong. So they went through the presentation notes and used "dbms_support.start_trace(true,true);" statement and ran the form again. This time, as the trace dumped the bind values, they found the set of values on which the query returned 1403, and got back to the developer with a solid case. So, once again ... if you have 10046 hammer, most problems are like nails. 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!
Merry Christmas Happy New Year 2003
hi, Merry Christmas and Happy New Year 2003 God Bless You All Po Min -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: root INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: this doesn't look right
I know it's a view. I'm just curious as to why one is varchar2(30) and one is varchar2(4000) when the column names in a constraint are the same ones in a table and should, in theory, be the same size --- Vladimir Begun [EMAIL PROTECTED] wrote: Rachel DBA_CONS_COLUMNS is a view. DECODE is applied against attrcol$.name -- which is varchar 4000. Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Rachel Carmichael wrote: As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Memory leak in OCI call
Yes I am using OCIConnectionPoolDestroy. -Naren Tim Gorman [EMAIL PROTECTED] on 12/30/2002 11:03:41 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Narendra Donthineni/HSSBLR) Are you making any calls to OCIConnectionPoolDestroy anywhere in your program? HI. I am using OCIConnectionPoolCreate to establish Connection with database. When i run through purifier its showing Leak. The traces are as follows MLK: 128 bytes leaked at 0x1b7c830 This memory was allocated from: malloc [rtlib.o] sltspcinit [sltspc.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] MLK: 104 bytes leaked at 0x1bec0b0 This memory was allocated from: malloc [rtlib.o] sltsmxi[sltsm.c] kpucpcreate[kpucp.c] OCIConnectionPoolCreate [oci8.c] Please help me in this... Thanks in Advance -Naren. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: this doesn't look right
ah, that goes along with the you can make the file name any length now feature in Windows :) --- Rodd Holman [EMAIL PROTECTED] wrote: It might be for a new feature, two_page_long_columnames=true ;-) Rodd On Mon, 2002-12-30 at 14:23, Rachel Carmichael wrote: As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net Rodd Holman [EMAIL PROTECTED] Romans 1:16-17 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: this doesn't look right
while it's interesting that one view has the column name coming from attrcol$ while the other comes from col$, that doesn't answer the basic question of WHY --- Shaleen [EMAIL PROTECTED] wrote: Varchar2(4000) is coming from sys.attrcol$ table. dba_tab_columns does not have a join this table. I am sure there are people on this list who'll enlighten me about contents of sys.attrcol$ table. select u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,sys.obj$ o, sys.attrcol$ ac where c.owner# = u.user# and c.con# = cd.con# and cd.type# != 12 /* don't include log groups */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+) -Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 12:23 PM As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Shaleen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: this doesn't look right
but the name you see in dba_cons_columns doesn't show the user_data. part! --- Shaleen [EMAIL PROTECTED] wrote: Looking at contents of attrcol$ , name column can have more than 30 characters since it seems to be joining 2 attributes OBJ#INTCOL# NAME -- -- -- 45149 41 USER_DATA.AGENT_ADDRESS 45149 42 USER_DATA.AGENT_PROTOCOL 45149 43 USER_DATA.ORIGINAL_MSGID 45149 44 USER_DATA.SUB_NAME 45149 45 USER_DATA.SUB_NAMESPACE - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 30, 2002 2:05 PM Varchar2(4000) is coming from sys.attrcol$ table. dba_tab_columns does not have a join this table. I am sure there are people on this list who'll enlighten me about contents of sys.attrcol$ table. select u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,sys.obj$ o, sys.attrcol$ ac where c.owner# = u.user# and c.con# = cd.con# and cd.type# != 12 /* don't include log groups */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+) -Shaleen - Original Message - From: Rachel Carmichael [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 12:23 PM Subject: this doesn't look right As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Shaleen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: this doesn't look right
In case of object tables, the constraints are defined on the most primitive of the objects. For instance, you have a table MYTAB defined on an object type MYTYPE1 which references in MYTYPE2. An attribute of MYTYPE2, ATTR1 has the constraint defined as not null. In this case the column_name in dba_cons_columns will say MYTAB.MYTYPE1.MYTYPE2 to show the hierarchy. Since each of these values could be 30 chars long, with the dots and double quotes, the column_name could easily be very long. Therefore, VARCHAR2(4000). HTH Arup From: Rodd Holman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: this doesn't look right Date: Mon, 30 Dec 2002 14:53:56 -0800 MIME-Version: 1.0 Received: from newsfeed.cts.com ([209.68.248.164]) by mc9-f34.bay6.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 30 Dec 2002 15:34:11 -0800 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id PAA46221;Mon, 30 Dec 2002 15:33:50 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0052468B; Mon, 30 Dec 2002 14:53:56 -0800 Message-ID: [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Rodd Holman [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 30 Dec 2002 23:34:11.0258 (UTC) FILETIME=[F4A239A0:01C2B05B] This still begs the question why varchar(4000) in the underlying table if column names are only allowed to be 30? Is this table used for other objects that can have a longer name? If so what? Rodd On Mon, 2002-12-30 at 16:08, Shaleen wrote: Varchar2(4000) is coming from sys.attrcol$ table. dba_tab_columns does not have a join this table. I am sure there are people on this list who'll enlighten me about contents of sys.attrcol$ table. select u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos# from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,sys.obj$ o, sys.attrcol$ ac where c.owner# = u.user# and c.con# = cd.con# and cd.type# != 12 /* don't include log groups */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+) -Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 12:23 PM As part of the process of making sure that null/not null constraints are consistent across development/staging/production, I'm looking at the dba_cons_columns table. Now, as far as I know, column_names are limited to 30 characters, correct? If so, why is the column_name column in dba_cons_columns (9.2.0.1) a varchar2(4000)? I realize that the extra space isn't used and is never allocated so it's not like this is wasteful. But it's inconsistent with everything else, since dba_tab_columns has a column_name column of varchar2(30). anyone know why this is like this? Rachel __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net Rodd Holman [EMAIL PROTECTED] Romans 1:16-17 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ MSN 8: advanced junk mail protection and 3 months FREE*.
Re: this doesn't look right
Rachel Carmichael wrote: I know it's a view. I'm just curious as to why one is varchar2(30) and one is varchar2(4000) when the column names in a constraint are the same ones in a table and should, in theory, be the same size Rachel, I've replied Rodd's message with self-explanatory example, it's in this thread: http://www.mail-archive.com/oracle-l@fatcity.com/msg61930.html Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
