Re: Replacing the pesky '
select replace('O''Reilly',,'*') from dual - work too
Igor Oussoltsev
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, October 22, 2001 6:35 PM
Gotta love Mondays,
We've been through this before, and I usually don't have a problem, but
today my brain must think it's the weekend.
Hey, I've got a bunch of names like O'Brien and O'Reilley where I need to
replace the ' symbol. I've tried every type of combination of single
quotes and slashes(escape sequence) I can think of, but I must have missed
one, because I can't get it to replace.
Any Ideas? Here's the progenitor of the whole mess.
1* select replace(lastname,' ' ') from adst where lastname like 'O%'
SQL /
ERROR:
ORA-01756: quoted string not properly terminated
SQL
David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Igor Oussoltsev
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Errormessage
Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, RIK2.VARE.LEVNR, RIK2.ART_HIERARKI_CALC.VGR, RIK2.VARE.SORTIMENT, RIK2.VARE.VARENAVN FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC WHERE RIK2.VARE.SELSKAP='11' AND RIK2.VARE.VARE_SNR=10 AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR ORDER BY VGR ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Errormessage
Seek and ye shall find oracle@tyne:/usr/oracle oerr ora 3232 03232, 0, unable to allocate an extent of %s blocks from tablespace %s // *Cause: An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value // that is greater than the tablespace's NEXT value // *Action: Increase the value of NEXT for the tablespace using // ALTER TABLESPACE DEFAULT STORAGE or decrease the value of // HASH_MULTIBLOCK_IO_COUNT. Regards Lee -Original Message- Sent: 23 October 2001 09:10 To: Multiple recipients of list ORACLE-L Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, RIK2.VARE.LEVNR, RIK2.ART_HIERARKI_CALC.VGR, RIK2.VARE.SORTIMENT, RIK2.VARE.VARENAVN FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC WHERE RIK2.VARE.SELSKAP='11' AND RIK2.VARE.VARE_SNR=10 AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR ORDER BY VGR ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Errormessage
Hi Roland, check if your temp tablespace is getting filled up (look at dba_free_space, v$sort_segment, v$sort_usage). Are you getting any other messages with this one ? Mandar. [EMAIL PROTECTED] wrote: Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, gt; RIK2.VARE.LEVNR, gt; RIK2.ART_HIERARKI_CALC.VGR, gt; RIK2.VARE.SORTIMENT, gt; RIK2.VARE.VARENAVN gt; FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC gt; WHERE RIK2.VARE.SELSKAP='11' AND gt; RIK2.VARE.VARE_SNR=10 gt; AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR gt; ORDER BY VGR gt; gt; gt; ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_2001102321319121061730690 Content-Type: text/html; charset=us-ascii PHi Roland, check if your temp tablespace is getting filled up (look at dba_free_space, v$sort_segment, v$sort_usage). Are you getting any other messages with this one ?/P PMandar./P PBRBRBI[EMAIL PROTECTED]/B/I wrote:BRBR/P BLOCKQUOTE style=BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5pxBRHalloBRBRBRBRBRBRI run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema.BRBRBRBRBRBRSELECT RIK2.VARE.EAN_NR,BRgt; RIK2.VARE.LEVNR,BRgt; RIK2.ART_HIERARKI_CALC.VGR,BRgt; RIK2.VARE.SORTIMENT,BRgt; RIK2.VARE.VARENAVNBRgt; FROM RIK2.VARE, RIK2.ART_HIERARKI_CALCBRgt; WHERE RIK2.VARE.SELSKAP='11' ANDBRgt; RIK2.VARE.VARE_SNR=10BRgt; AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENRBRgt; ORDER BY VGRBRgt;BRgt;BRgt; ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3BRBRBRThanks in advanceBRBRBRBRBRBRRolandBRBR-- BRPlease see the official ORACLE-L FAQ: http://www.orafaq.comBR-- BRAuthor: BRINET: [EMAIL PROTECTED]BRBRFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051BRSan ! ! ! Diego, California -- Public Internet access / Mailing ListsBRBRTo REMOVE yourself from this mailing list, send an E-Mail messageBRto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inBRthe message BODY, include a line containing: UNSUB ORACLE-LBR(or the name of mailing list you want to be removed from). You mayBRalso send the HELP command for other information (like subscribing)./BLOCKQUOTEBR hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/font/a/bbrBuy Music, Video, CD-ROM, Audio-Books and Music Accessories from A href=http://www.planetm.co.in;http://www.planetm.co.in/A --=_MAILER_ATTACH_BOUNDARY1_2001102321319121061730690-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Errormessage
When tablespace 3 is your temp tablespace Look into possibility of extending that one eg: autoextend, add another datafile Hth, Jeroen -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 23 oktober 2001 10:10 Aan: Multiple recipients of list ORACLE-L Onderwerp: Errormessage Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, RIK2.VARE.LEVNR, RIK2.ART_HIERARKI_CALC.VGR, RIK2.VARE.SORTIMENT, RIK2.VARE.VARENAVN FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC WHERE RIK2.VARE.SELSKAP='11' AND RIK2.VARE.VARE_SNR=10 AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR ORDER BY VGR ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rebuilding Indexes Question
I agree.
If you're really tight on space, you may need to
consider drop/create, otherwise rebuilds generally are
the way to go.
hth
connor
--- Jacques Kilchoer [EMAIL PROTECTED]
wrote: -Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
What are the advantages/disadvantages to doing a
alter index rebuild
(Oracle 8.0.X) versus dropping and recreating the
index.
My personal opinion - if your database is not
subject to any of the bugs
reported against alter index ... rebuild commands,
{some bug examples:
[BUG:1475310] ALTER INDEX .. REBUILD ONLINE/ MOVE
IOT ONLINE can corrupt the
index. See [NOTE:125149.1]
[BUG:1427002] ALTER INDEX i REBUILD
PARAMETERS('SYNC') may raise ORA-29863 /
DRG-10595 / ORA-28579 (interMedia Text)
[BUG:1573283] OERI:6033 from ALTER INDEX .. REBUILD
ONLINE PARAMETERS
('OPTIMIZE FULL')
etc...
}
alter index ... rebuild is easier to use, since you
don't have to specify
the full syntax that was used to build the index. I
don't know of any
reasons to prefer the drop index / create index
method.
Any corrections/suggestions welcome.
=
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
Some days you're the pigeon, some days you're the statue
Nokia Game is on again.
Go to http://uk.yahoo.com/nokiagame/ and join the new
all media adventure before November 3rd.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: {9i New Feature: Query Flashback }: This one is long
I think Joe's just doing it to be helpful and as a way of learning the 9i stuff... he has to play with it to be able to write about it --- Larry Elkins [EMAIL PROTECTED] wrote: As always Joe, we appreciate these updates. And if it is never ending, that's fine by me. The way you have taken care to write up the features, issues, and caveats makes me think this series is destined for a presentation? Technet also has a series going on regarding 9i features. It is fairly useful from a high level standpoint. Of course, it is Oracle's side of the story. A bit different from Joe's going through the features and giving the real story and experiences. Regards, Larry G. Elkins The Elkins Organization Inc. [EMAIL PROTECTED] 214.954.1781 -Original Message- Sent: Monday, October 22, 2001 12:05 PM To: Multiple recipients of list ORACLE-L Query Flashback This is part 3 of a what will seem to be a never-ending series on new 9i features. :) This topic follows up on last weeks on Automated Undo Management(which is a requirement for Query Flashback). What is Query Flashback? Flashback Query lets you view and repair historical data. It offers the ability to perform queries on the database as of a certain wall clock time(look under the limitations section about this) or user-specified system change number (SCN). Once the errors are identified, undoing the updates is a straightforward process that can be done without intervention from the database administrator. More importantly, the restoration can be achieved with no database downtime. Setting Up the Database for Flashback Query Use automatic undo management to maintain read consistency, rather than the older technique using rollback segments. You MUST HAVE an undo tablespace to make this work. Now did I try it with Rollback segments, nope, but based on what I've read it would make no sense to even try it as we all know that RBS get reused. Set the UNDO_RETENTION init.ora parameter to a value that represents how far in the past you might want to query(it is in seconds). If you only need to recover data immediately after a mistaken change is committed, the parameter can be set to a small value. If you need to recover deleted data from days before, you might need to say 86400 * number of days(since 60 * 60 *24 = 86400). Now keep in mind, if you tell Oracle to keep like one days worth of undo, you set the UNDO_RETENTION to 86400 and there is not enough free space in the tablespace to keep that much, then Oracle will ignore that keep time and start reusing the oldest undo. Grant EXECUTE privilege on the DBMS_FLASHBACK package to whoever needs it. Potential applications of flashback query are: Recovering lost data or undoing incorrect changes, even after the changes are committed. For example, a user who deletes or updates rows and then commits can immediately repair a mistake. Comparing current data against the data at some time in the past. For example, you might run a weekly report that shows the change from last week, rather than just the current aggregate data. Checking the state of transactional data at a particular time. For example, you might want to verify an account balance on a certain day. Important notes about query flashback Flashback Query does NOT undo anything. Flashback Query does NOT tell you what changed thats what LogMiner does(thats coming up in a few weeks). Flashback Query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables. Limitations of Flashback Query Some DDLs that alter the structure of a table, such as drop/modify column, move table, drop partition, truncate table/partition, and so on, invalidate the old undo data for the table. It is not possible to retrieve a snapshot of data from a point earlier than the time such DDLs were executed. An attempt to perform such a query will result in a ORA-1466(unable to read data, tbl definition has changed) error. This restriction does not apply to DDL operations that alter the storage attributes of a table, such as PCTFREE, INITTRANS, MAXTRANS, and so on. Operations such as adding new extents, constraints or partitions are also exempted from this restriction. * IMPORTANT
Re: Errormessage
Roland, Your qry is making a HASH join and eating up the temp tablespace. The solution is to increase the parameter HASH_MULTIBLOCK_IO_COUNT in init.ora and bounce the db once. By default this parameter is '0'. So increase the next extent parameter for the temp tablespace. alter tablespace temp default storage(next 3M); this sql stmt will alter the temp tablespace's next extent from default value to 3Megs. HTH Venkat -- On Tue, 23 Oct 2001 00:10:19 Roland.Skoldblom wrote: Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, RIK2.VARE.LEVNR, RIK2.ART_HIERARKI_CALC.VGR, RIK2.VARE.SORTIMENT, RIK2.VARE.VARENAVN FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC WHERE RIK2.VARE.SELSKAP='11' AND RIK2.VARE.VARE_SNR=10 AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR ORDER BY VGR ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Make a difference, help support the relief efforts in the U.S. http://clubs.lycos.com/live/events/september11.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: performance issue on this sql ???
I need to get the adue_cd and group by the sum of the adue_amt- amtpd based
on the decoded code ??
SELECT DECODE(adue_cd,'DS','STM','IS','INS','MS','OUI',
'PA','OD1','PN','OD2','PP','OD1')
,SUM(adue_amt - amt_pd)
FROM BSADUE
WHERE la_no = v_la_no
AND adue_cd IN ('DS','IS','MS','PA','PN','PP')
GROUP BY adue_cd
order by DECODE(adue_cd,'DS','STM','IS','INS','MS','OUI',
'PA','OD1','PN','OD2','PP','OD1')
here is my explain plan generate from TOAD.
Operation Object Name RowsBytes CostTQ In/Out
PStart PStop
SELECT STATEMENT
SORT ORDER BY
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID BSADUE
INDEX RANGE SCANBSADUE_LANO
Raymond Lee
Infopro Sdn Bhd
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raymond Lee Meng Hong
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
How to find SQL statement in SQL area
Title: How to find SQL statement in SQL area Hi! How do I find the ENTIRE SQL statement that is dogging my box? select sql_text from v$sqlarea where hash_value=123456 gives me the first part of the SQL statement; but since the statement is pretty long, the sql_text column does not contain the entire statement. Is there a way to retrieve the entire statement? I also know the session ID of the application issuing the statement. This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Problems with Intermedia
Hi Jack, I am a bit out of my depth here. I configured the listener so that it can use the INSO filter by setting the ENVS=LD_LIBRARY_PATH=/usr/local/oracle/8.1.7/ctx/lib in the listener.ora. We r using Intermedia text on 8.1.7.0 on SunOS. When I tried to check the listener by executing the command SQL exec ctx_adm.test_extproc; (by logging on as ctxsys user) I get the following error : ERROR at line 1: ORA-2: interMedia Text error: ORA-06520: PL/SQL: Error loading external library ORA-06522: ld.so.1: extprocPLSExtProc: fatal: /usr/local/oracle/product/8.1.7xibibctxx8.so: open failed: No such file or directory ORA-06512: at CTXSYS.DRUE, line 126 ORA-06512: at CTXSYS.CTX_ADM, line 287 ORA-06512: at line 1 Could u please help me resolve this ?? Do I have to put in the LD_LIBRARY_PATH explicitly too and export it ?? Also, ctxhx is the user filter executable for the INSO filter. I killed that process since it was using up almost 95% of my CPU time. Any help would be greatly appreciated. Thanks and Regards, Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 22 October 2001 17:25 To: Multiple recipients of list ORACLE-L Samir, interMedia Text, Audio, Image, Video? What version of Oracle? What platform? We need more info. We use interMedia Text heavily (Oracle 8.1.6 on Win2k) and I've never seen the ctxhx process. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Samir Sent: Monday, October 22, 2001 6:10 AM To: Multiple recipients of list ORACLE-L Hi there !! Hi there, An application running the Intermedia process ctxhx is seen to b consuming an unusually high CPU time thus preventing other applications from running. Being very new to Intermedia I do not why this is happening. Can somebody help me on how to diagnose the problem of high CPU usage by ctxhx and correct it ?? Any help will be greatly appreciated. Thanks. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to find SQL statement in SQL area
Helmut, To get the full text of the statement you will need to do a join with v$sqltext. An example: select s.sid a.address, a.buffer_gets, a.executions, t.piece, t.sql_text from v$sqlarea a, v$sqltext t, v$session s where a.ADDRESS = t.ADDRESS and a.HASH_VALUE = t.HASH_VALUE and a.hash_value = s.sql_hash_value and a.buffer_gets 6 order by 1,4; The full text of the statement will be in a number of rows (ordered by the piece column). BTW, I saw that you are also using NORAD. To get the full text of a statement in this - go in to the SQLAREA screen (about 11 or 12 buttons from the left, that looks like a disk share icon) and simply double click on the sql that you are interested in, in the grid view. This will show you the full text of the statement, and the users that are currently executing it. HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Helmut Sent: Tuesday, October 23, 2001 12:40 To: Multiple recipients of list ORACLE-L Hi! How do I find the ENTIRE SQL statement that is dogging my box? select sql_text from v$sqlarea where hash_value=123456 gives me the first part of the SQL statement; but since the statement is pretty long, the sql_text column does not contain the entire statement. Is there a way to retrieve the entire statement? I also know the session ID of the application issuing the statement. This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to find SQL statement in SQL area
How to find SQL statement in SQL area try this: select t.sql_text, t.piece, a.hash_value fromv$sqltext t, v$sqlarea a, v$session s where s.sql_address=t.address and s.sql_address=a.address order by a.address, t.piece hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 23, 2001 14:40 Hi! How do I find the ENTIRE SQL statement that is dogging my box? select sql_text from v$sqlarea where hash_value=123456 gives me the first part of the SQL statement; but since the statement is pretty long, the sql_text column does not contain the entire statement. Is there a way to retrieve the entire statement? I also know the session ID of the application issuing the statement. This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How does oracle assign ports?
Tony - All Net8 connections must use the port you assign the listener in listener.ora, or they won't connect. Are you using other Oracle products such as Application Server? I'm not familiar with how those work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 23, 2001 9:05 AM To: Multiple recipients of list ORACLE-L How does oracle assign ports to connections? I know the listener is assigned to a port - does all connections use this port for communication? I was looking into using connection manager to limit the number of ports that are assigned to user sessions, but it seems that all my connections are using the listener port? Is this the case? I thought the listener was the initial port of contact and then set up different available ports for post-initial sessions. Using Oracle 8.1.6. Thanks in advance for all your responses!! +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ Tony Barker - Senior DBA - State of Indiana +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Barker, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
os block size versus oracle bock size
hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How does oracle assign ports?
How does oracle assign ports to connections? I know the listener is assigned to a port - does all connections use this port for communication? I was looking into using connection manager to limit the number of ports that are assigned to user sessions, but it seems that all my connections are using the listener port? Is this the case? I thought the listener was the initial port of contact and then set up different available ports for post-initial sessions. Using Oracle 8.1.6. Thanks in advance for all your responses!! +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ Tony Barker - Senior DBA - State of Indiana +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Barker, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Client install from hell, solved!!
Thanks, Anita and Simon!! Your suggestion to do a custom install worked. I had to install it piece by piece. It is not the way I would want all installations to go but it worked and the client PC can connect to the database. Thanks, Dave -Original Message- Sent: Monday, October 22, 2001 6:50 AM To: Multiple recipients of list ORACLE-L Dave, I suspect this is bug 1291239 OUI DISAPPEARS WHILE INSTALLING OLE DB COMPONENTS that is not yet fixed. The workaround is to do as Simon said and install in multiple passes. 1- Choose the custom install type. Select for installation only the problem component (e.g. Oracle Provider for OLE DB). The last itemName in the installactions.log is the problem component. De-select all other components you're allowed to. This will ensure that you only install the minimum number of components possible to install the problem component. 2- After the first session is done, now restart OUI and start a new installation. This time you can select the typical install type (or any other install type). The items installed during the first pass will not be reinstalled during the second pass. BTW, both bug 1291239 and bug 1560970 appear to be related to having the Novell client installed. Per chance is that installed on the ones giving you problems? HTH, -- Anita --- [EMAIL PROTECTED] wrote: Farnsworth, Dave [EMAIL PROTECTED] on 10/19/2001 07:15:24 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) I think I've seen this before, it certainly sounds familiar... I've no idea what causes it, but I found a workaround was to only install a small number of components at a time - for some reason, that seems to work. Let us all know if you come up with a better solution. Simon Anderson I am having a problem on a couple of client installs. I have Oracle 8.1.7 to be installed on NT 4.0 SP6. The install starts out fine, I select a Orahome and then it starts to install. When it gets to 90% all of a sudden the install wizard screen just disappears without any error messages. I can see that it created a new directory and copied a bunch of files to the hard drive. I can even double click on the SQLPlus icon and it launches asking me for a username, password, and database. Then if I put the client CD back in and click on the show installed components, it tells me that there are no Oracle components installed. I look in the registry and a registry for Oracle has been created. I have done this about twenty times and get the exact same results. I even tried a different CD but that does not change things. During the install Oracle will write to a log but this log tells me nothing except what it installed, without one stinking clue as to what is causing this to puke. I get this just on two PC's with identical results. Has anyone seen this before? I can go to other PC's and install without a hitch. These PC's do work and have plenty of disk space. Any ideas, ,,,anything? Thanks, Dave __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: os block size versus oracle bock size
Hi I'd say yes. For every Oracle block read the OS has to read two block which causes overhead. Jack [EMAIL PROTECTED]@fatcity.com on 23-10-2001 16:05:21 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
identifying a database
Hi, what is the best way to uniquely identify a database? A. grant access to the user to v$database and use DBID? (how unique is the DBID?) B.generate a unique ID somehow (some PL/SQL package to use?) thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:How does oracle assign ports?
Tony, According to my understanding of this, which may or may not be absolutely true, it depends on the way that connections are established. In a standard dedicated server connection method, a user originally connects to the listener on port 1521 or whatever else you've selected. Once the initial communications is established, the listener spawns a dedicated server, assigns it a port according to the OS and bequeaths you to that port. So that in this mode you have a one for one correlation of ports and servers. In a Multi-threaded server scheme, the database starts a number of dispatchers per your settings. These each obtain a port from the OS as required and pass this onto the listener. When a user connects they get bequeathed to the first available server on a round robin basis. In this mode there is no one to one coronation of clients to servers since more than one will be communicating over the one port. In a connection manager mode, your client initially connects to the connection manager and then gets passed off to the listener. If your using connection pooling the connection manager now manages all inbound/outbound connections over one port I do believe. There is a lot of 'black magic' here as to which method is best. For the most part, according to an Oracle person I had the privilege of talking to, if you've less than 100 continuos connections to your db, use dedicated server. Between 100 and 500 MTS is best. Above 500 use connection manager with connection pooling. BTW: That conversation was a privilege since he did a pretty good job of demystifying this subject for me. And to boot his advice appears to have been dead on. Dick Goulet Reply Separator Author: Barker; Tony [EMAIL PROTECTED] Date: 10/23/2001 6:05 AM How does oracle assign ports to connections? I know the listener is assigned to a port - does all connections use this port for communication? I was looking into using connection manager to limit the number of ports that are assigned to user sessions, but it seems that all my connections are using the listener port? Is this the case? I thought the listener was the initial port of contact and then set up different available ports for post-initial sessions. Using Oracle 8.1.6. Thanks in advance for all your responses!! +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ Tony Barker - Senior DBA - State of Indiana +~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~+~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Barker, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re:identifying a database
- Original Message - To: Marin Dimitrov [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 23, 2001 17:48 Marin, Not sure I understand the question. Each database needs a unique sid on that machine. You can use the same sid on different computers, but not on the same one. According to MetaLink DBID is not a unique indicator. I don't want to mess with SIDs, what I need is a globally unique identifier of the database (or one with sufficiently small probability of being duplicated) The reason I need this is because in the application a user may read some object from a database and when he tries to save the object back I need to be sure that the database used is the same as the one the object originates from (one usually deals with few objects from different databases) So, if DBID is not unique how could I generate an almost globally unique number? (I don't quite like calling SYS_GUID() because it returns RAW) thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:identifying a database
Marin, Not sure I understand the question. Each database needs a unique sid on that machine. You can use the same sid on different computers, but not on the same one. According to MetaLink DBID is not a unique indicator. Dick Goulet Reply Separator Author: Marin Dimitrov [EMAIL PROTECTED] Date: 10/23/2001 7:30 AM Hi, what is the best way to uniquely identify a database? A. grant access to the user to v$database and use DBID? (how unique is the DBID?) B.generate a unique ID somehow (some PL/SQL package to use?) thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: os block size versus oracle bock size
WHOA, No that is not a performance problem. The Oracle block size needs to always be a multiple of the OS block size. Most operating systems today, W2K being no exception, do not simply read the number of OS blocks that you request. They always 'read ahead' in the anticipation that you'll want that data in the very near future. The amount of read ahead that is done is OS specific. Therefore if your Oracle block size is = the OS block size the operating system will be doing a lot of reading for you that is irrelevant, which can lead to a phantom performance problem namely your wasting OS reads. The idea here is to pick an Oracle block size that is appropriate for your application and a multiple of the OS block size. Also set db_file_multiblock_read_count carefully since this controls the number of blocks that Oracle requests from the OS at one time. If you need one block and have db_file_multiblock_read_count set to 100, then you get 100 blocks, if the OS allows. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 10/23/2001 7:05 AM Hi I'd say yes. For every Oracle block read the OS has to read two block which causes overhead. Jack [EMAIL PROTECTED]@fatcity.com on 23-10-2001 16:05:21 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
Re: Re:identifying a database
Marin, If you don't like SYS_GUID() returning RAW, you can use rawtohex(SYS_GUID()), it will return character string, containing hex representation. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 23, 2001 12:10 PM - Original Message - To: Marin Dimitrov [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 23, 2001 17:48 Marin, Not sure I understand the question. Each database needs a unique sid on that machine. You can use the same sid on different computers, but not on the same one. According to MetaLink DBID is not a unique indicator. I don't want to mess with SIDs, what I need is a globally unique identifier of the database (or one with sufficiently small probability of being duplicated) The reason I need this is because in the application a user may read some object from a database and when he tries to save the object back I need to be sure that the database used is the same as the one the object originates from (one usually deals with few objects from different databases) So, if DBID is not unique how could I generate an almost globally unique number? (I don't quite like calling SYS_GUID() because it returns RAW) thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ADO.NET and Oracle 8.1.6
We're getting ORA-12154: TNS:could not resolve service name I've checked the TNSNAMES.ORA and SQLNET.ORA and they seem OK. WIN2K. Using the MS Oracle ODBC driver (the Oracle ODBC driver is a no go). Anyone have any experience with this? TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: os block size versus oracle bock size
Hi All, Oracle uses direct I/O on W2K so the O/S block size is an irrelevance. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: switchover with standby database
Thanks a lot ! Does anyone have such a script , please ! Thanks ! DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, October 22, 2001 10:40 PM To: Multiple recipients of list ORACLE-L A broad process overview: Process: 1. Notification comes in of a down production. -- This needs to be very reliable. 2. Make sure production is completely down. -- Look for pmon and smon processes and see if connections are possible. 3. Bring any remaining logs over to the standby -- Use your same process you are using now or a seperate script. 4. Perform an alter database activate standby database on the standby -- simple script using svrmgrl on a unix box. 5. Apply the logs -- Recover database command 6. Shut down the listener -- lsnrctl stop 7. Copy in the new listener configuration files so that users are now pointing to the standby -- simple os copy command 8. Bring up the listener -- lsnrctl start All of this could easily be done in an os script. -Original Message- Sent: Monday, October 22, 2001 1:57 PM To: Multiple recipients of list ORACLE-L Thanks a lot Kevin ! I thought of it , hope everything will work , i have no choice actually. Regarding bullet #3 - this is exactly what i'm asking - how can i automate the open of the standby ? Thanks a lot ! -Original Message- Sent: Monday, October 22, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Keep a couple things in mind when you talk about automating a standby process: 1. You need to make sure your logs are completely up to date. 2. When you do change from live to the standby, you need to change your listeners. 3. You would need to automate the open of the standby as well. 4. You need to make sure this is foolproof. I would hate to have my standby come up prematurely. Remember, after it goes into live mode, you can not put it back as a standby. At that point, you re rebuilding the standby. -Original Message- Sent: Monday, October 22, 2001 12:50 PM To: Multiple recipients of list ORACLE-L Dear gurus ! I'm about to implement Oracle standby database using oracle 8.0.5 . I need automatic switchover in case of primary site failure , i.e. i need the standby (secondary) database to get opened automatically should the primary site fail. Is there a way to achieve this ? I know that there is automated failover capability in 8i managed standby , but we use 8.0.5 :-( Thanks in advance . Andrey -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
Re[2]: Re:identifying a database
Well, I'd suggest setting 'global_name = true' in your init.ora files. Then the database names in global_names have to be unique. Dick Goulet Reply Separator Author: Marin Dimitrov [EMAIL PROTECTED] Date: 10/23/2001 8:10 AM - Original Message - To: Marin Dimitrov [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 23, 2001 17:48 Marin, Not sure I understand the question. Each database needs a unique sid on that machine. You can use the same sid on different computers, but not on the same one. According to MetaLink DBID is not a unique indicator. I don't want to mess with SIDs, what I need is a globally unique identifier of the database (or one with sufficiently small probability of being duplicated) The reason I need this is because in the application a user may read some object from a database and when he tries to save the object back I need to be sure that the database used is the same as the one the object originates from (one usually deals with few objects from different databases) So, if DBID is not unique how could I generate an almost globally unique number? (I don't quite like calling SYS_GUID() because it returns RAW) thanx, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How long are statistics good for
We have a data-warehouse that is a combination of Snapshots and table-builds based on the snapshots. The table builds run at 4:30 am, scripts are setup to start the snapshots at 7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys and system) tables in the database. If the snapshots have been running all day, should I run an analyze before I do the table builds? as opposed to after ? At what point do the statistics on a table become no good ? when a new row is added ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Systems Analyst - Information Systems using 100% recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Statspack Question
so long as you look at the wait events, you will be looking at your database's bottlenecks, and in the world of Oracle Performance Tuning, that is all that counts. What about v$sql? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: {9i New Feature: Query Flashback }: This one is long
If he is preparing a presentation, it is definitly one I won't want to miss. Stephen Andert [EMAIL PROTECTED] 10/23/01 02:40AM I think Joe's just doing it to be helpfuland as a way of learning the 9i stuff... he has to play with it to beable to write about it--- Larry Elkins [EMAIL PROTECTED] wrote: As always Joe, we appreciate these updates. And if it is never ending, that's fine by me. The way you have taken care to write up the features, issues, and caveats makes me think this series is destined for a presentation? Technet also has a series going on regarding 9i features. It is fairly useful from a high level standpoint. Of course, it is Oracle's side of the story. A bit different from Joe's going through the features and giving the "real story" and experiences. Regards, Larry G. Elkins The Elkins Organization Inc. [EMAIL PROTECTED] 214.954.1781 -Original Message- Sent: Monday, October 22, 2001 12:05 PM To: Multiple recipients of list ORACLE-L Query Flashback This is part 3 of a what will seem to be a never-ending series on new 9i features. :) This topic follows up on last weeks on Automated Undo Management(which is a requirement for Query Flashback). What is Query Flashback? Flashback Query lets you view and repair historical data. It offers the ability to perform queries on the database as of a certain wall clock time(look under the limitations section about this) or user-specified system change number (SCN). Once the errors are identified, undoing the updates is a straightforward process that can be done without intervention from the database administrator. More importantly, the restoration can be achieved with no database downtime. Setting Up the Database for Flashback Query Use automatic undo management to maintain read consistency, rather than the older technique using rollback segments. You MUST HAVE an undo tablespace to make this work. Now did I try it with Rollback segments, nope, but based on what I've read it would make no sense to even try it as we all know that RBS get reused. Set the UNDO_RETENTION init.ora parameter to a value that represents how far in the past you might want to query(it is in seconds). If you only need to recover data immediately after a mistaken change is committed, the parameter can be set to a small value. If you need to recover deleted data from days before, you might need to say 86400 * number of days(since 60 * 60 *24 = 86400). Now keep in mind, if you tell Oracle to keep like one days worth of undo, you set the UNDO_RETENTION to 86400 and there is not enough free space in the tablespace to keep that much, then Oracle will ignore that keep time and start reusing the oldest undo. Grant EXECUTE privilege on the DBMS_FLASHBACK package to whoever needs it. Potential applications of flashback query are: Recovering lost data or undoing incorrect changes, even after the changes are committed. For example, a user who deletes or updates rows and then commits can immediately repair a mistake. Comparing current data against the data at some time in the past. For example, you might run a weekly report that shows the change from last week, rather than just the current aggregate data. Checking the state of transactional data at a particular time. For example, you might want to verify an account balance on a certain day. Important notes about query flashback Flashback Query does NOT undo anything. Flashback Query does NOT tell you what changed thats what LogMiner does(thats coming up in a few weeks). Flashback Query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables. Limitations of Flashback Query Some DDLs that alter the structure of a table, such as drop/modify column, move table, drop partition, truncate table/partition, and so on, invalidate the old undo data for the table. It is not possible to retrieve a snapshot of data from a point earlier than the time such DDLs were executed. An attempt to perform such a query will result in a ORA-1466(unable to read data, tbl definition has changed) error. This restriction does not apply to DDL operations that alter the storage attributes of a table, such as PCTFREE, INITTRANS, MAXTRANS, and so on. Operations such as adding new extents, constraints or partitions are also exempted from this restriction. *
RE: How long are statistics good for
Statistics become old after a single change is made to the object. Granted adding a single row to a 1 Million row table isn't going to effect much. But adding 100,000 rows will. So where do you draw the line? I really don't know. But 5%-10% is generally a good measure. Sometimes more sometimes less depending on the size. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, October 23, 2001 2:00 PM To: Multiple recipients of list ORACLE-L We have a data-warehouse that is a combination of Snapshots and table-builds based on the snapshots. The table builds run at 4:30 am, scripts are setup to start the snapshots at 7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys and system) tables in the database. If the snapshots have been running all day, should I run an analyze before I do the table builds? as opposed to after ? At what point do the statistics on a table become no good ? when a new row is added ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Systems Analyst - Information Systems using 100% recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Statspack Question
i would probably first use v$sqlarea instead of v$sql to be able to identify the hash values for bad ones(high lio's) and then probe v$sql using the same. Deepak --- Greg Moore [EMAIL PROTECTED] wrote: so long as you look at the wait events, you will be looking at your database's bottlenecks, and in the world of Oracle Performance Tuning, that is all that counts. What about v$sql? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Data Modelling Question
Hi List, I have a question regarding design data modelling If any of you have any Idea or similar experience It would be nice to respond, Thanks in advance all of you. This is my question, I am in the middle of designning a database and want to know if any tools or some methodology for testing the database on performance issue before starting the development phase, I prefer to do minimum change( i mean on database design) in development phase for any performance issue. Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
v$sqlarea statistics
I am trying to identify the most harmful statements in an application. From the Oracle Performance and Tuning Tips and Techniques book, I found two statements. Both are looking at the statements contained in the v$sqlarea. The first looks at statements with a high number of buffer gets and the other looks at the statements with a high number of disk reads. Some of the statements appear in both lists, but some in only one. If all of the disk reads are moving blocks into the buffer cache, what is the difference between the two measures? Can anyone explain the difference between the two measures? Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN HOTBACKUP AUTOMATED SCRIPT
Hi Can some one send me automated RMAN backup scripts please? Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN HOTBACKUP AUTOMATED SCRIPT
run {
allocate channel t1 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_fdm1_bk_level0
filesperset 2
format 'bk_%s_%p_%t'
(database);
sql 'alter system archive log current';
backup
filesperset 20
format 'al_%s_%p_%t'
(archivelog all
delete input);
}
--- Seema Singh [EMAIL PROTECTED] wrote: Hi
Can some one send me automated RMAN backup scripts
please?
Thanks
-Seema
_
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
Some days you're the pigeon, some days you're the statue
Nokia Game is on again.
Go to http://uk.yahoo.com/nokiagame/ and join the new
all media adventure before November 3rd.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: v$sqlarea statistics
Eric,
here's a high level from my understanding on this
issue:
Buffer gets {also called Logical IO's}
These happen as oracle scans blocks of data in the
buffercache(in-mem scans). Many people believe that
since these are memory reads, they are inexpensive. I
have seen the contrary in many cases and have seen
that these are the ones which take the most cpu
clycles, therby making your system CPU Bound.
Resolution of this is to tune your sql by having it
use better access paths (indexes). Also consider
de-norming in ordr to avoid too many joins
I consider this the most important metrics in
identifying bad SQL. i have seen cases where
frequently executed queries were performing millions
of LIO's and hosing up the CPU. A simple index / or
Adding hint can reduce this number by a very high
factor resulting in great gains.
DiskReads {also called physical IO's)
This obviously means that there are a lot of disk
reads required to satisfy your query. Reasons: maybe
you are using ineffcient access paths/bad sql or u
just have insuffient (small) memory to support your
app. High Diskreads is the reason that makes your
system IO bound. Resolution is again the same as
described above. In addition, one of the assumtions
here is that you have spread your datafiles/logs/cf
optimally. Also consider using the recycle buffer pool
feature to avoid an innocent FTS from flushing
everything from your cache. Obviously you cannot
always prevent any of these and some disk read are
inevitable.
hth
Deepak:
--- Erik Williams [EMAIL PROTECTED] wrote:
I am trying to identify the most harmful statements
in an application. From
the Oracle Performance and Tuning Tips and
Techniques book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number of
buffer gets and the
other looks at the statements with a high number of
disk reads. Some of the
statements appear in both lists, but some in only
one. If all of the disk
reads are moving blocks into the buffer cache, what
is the difference
between the two measures? Can anyone explain the
difference between the two
measures?
Thanks.
Erik
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Erik Williams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: v$sqlarea statistics
Some reasons for the difference: a) A single disk read could get more than 1 block b) queries may already find blocks in the cache and thus not need a disk read c) a query may revisit the same block over and over without ever going back to disk The reason we look at both is 'disk_reads' tell us what is possibly causing stress on the IO subsystem, 'buffer gets' tells us what is causing stress on cpu. hth connor --- Erik Williams [EMAIL PROTECTED] wrote: I am trying to identify the most harmful statements in an application. From the Oracle Performance and Tuning Tips and Techniques book, I found two statements. Both are looking at the statements contained in the v$sqlarea. The first looks at statements with a high number of buffer gets and the other looks at the statements with a high number of disk reads. Some of the statements appear in both lists, but some in only one. If all of the disk reads are moving blocks into the buffer cache, what is the difference between the two measures? Can anyone explain the difference between the two measures? Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Nokia Game is on again. Go to http://uk.yahoo.com/nokiagame/ and join the new all media adventure before November 3rd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN HOTBACKUP AUTOMATED SCRIPT
Hi Seema, I use this script. I have added comments so it should not be difficult to understand. Let me know if you need any clarification: the usage is -- exec_restore.ksh -d [SID] -f [rman_cmdfile] Deepak PS: i have not included command file as i think probably need only the wrapper. #!/usr/bin/ksh #set -xv # # # # TITLE : exec_restore.ksh # # # # REQUIREMENT : Define $LOG_BASE directory to specify Log Location# # # # PARAMETERS: -d specifies the SID of the target database to restore# # -f specifies the command file for RMAN Restore# # # # USAGE : exec_rman.ksh [-d SID] [-f command_file] # # # # OUTPUTS : Returns a -1 value if it encounters an error else returns 0 # # # # DESCRIPTION : This script serves as a wrapper for invoking RMAN interface # # and as such performs following tasks :# # - Validate arguments passed in from command line# # - Define Notification List and add email id's of DBA's # # - Abort if restore is already in progress for SID passed# # - Performs restore using RMAN interface # # - Checks log files and sends appropriate notifications # # # # # *** # * * # * DEFINE LOG FILES AND SPECIFY DEFAULT LOCATION FOR $LOGBASE * # * * # *** sync;sync;sync export LOG_BASE= export LOG_BASE=/opt/oracle/product/admin/sql/rman/log export EXECRMAN_LOG=$LOG_BASE/exec_restore.ksh.`date +%m.%d.%Y:%H:%M:%S`.log export WORKFILE_MULTI_CALLS=$LOG_BASE/rman_multi_call_detector.$$.log export WORKFILE_LOGFILE=$LOG_BASE/rman_logfile.`date +%m.%d.%Y:%H:%M:%S`.log touch $EXECRMAN_LOG # Main Wrapper LogFile touch $WORKFILE_LOGFILE # RMAN Generated Batch LogFile touch $WORKFILE_MULTI_CALLS # Multi Invoke Detect Mechanism # *** # * * # * DEFINE NOTIFICATION LIST (comma separated) * # * * # *** RMAN_NOTIFY='[EMAIL PROTECTED],[EMAIL PROTECTED]' # *** # * * # * DEFINE USAGE FOR SCRIPT AND GENERIC VARIABLES * # * * # *** HOSTID=`hostname` #Host Identity SENDALERT=/usr/bin/mailx #Mailing Interface export ORATAB=/var/opt/oracle/oratab #Oratab Location USAGE=\nUsage: $0 [-d SID] [-f rman_cmdfile]\n #Usage of Wrapper # *** # * * # * ACCEPT AND VALIDATE COMMAND-LINE ARGUMENTS * # * * # *** while getopts :d:f: arguments do case $arguments in d) RMAN_TARGSID=$OPTARG ;; f) RMAN_CMDFILE=$OPTARG ;; \?) print \n$OPTARG is not a valid argument print $USAGE exit -1 esac done # *** # *
RMAN: nocatalog; remove backed up archived redos
Hello everyone, Env: 8i and 8.0 I've been digging around the rman manuals and metalink but can't seem to find anything decent on this so thought I'd try this forum... Firstly, what functionality do you loose when you don't use a catalog? 2ndly, how does one delete old backups? I'm backing up to disk. All is fine with the backup. But now I need to delete the backups from disk, and remove the information about the backup from the controlfile. Tried doing this but failed: -- RMAN allocate channel for delete type disk; RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: delete_05 RMAN-08500: channel delete_05: sid=15 devtype=DISK RMAN change backuppiece 70 delete; RMAN-03022: compiling command: change RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-03002: failure during compilation of command RMAN-03013: command type: change RMAN-06091: no channel allocated for maintenance (of an appropriate type) --- What's wrong here? I've allocated a channel and RMAN acknowledges that it's for delete. But can't do it. Help!! Thanks, Leng. = Leng Kaing - [EMAIL PROTECTED] Ph: +61-3-417-371-348 AUSOUG-VIC : http://www.ausoug.org/vic/ http://briefcase.yahoo.com.au - Yahoo! Briefcase - Manage your files online. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Leng=20Kaing?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How long are statistics good for
Which version of Oracle are you using. In 8i you can set 'monitoring on' for the tables and use dbms_stats to analyze stale. (Though, I am getting error while using dbms_stats for the partitioned tables. So I have made a home made version to analyze stale). As per my calculations, package dbms_stats considers statistics stale ifall DMLsaffect more than 10% of number of rows. Anand Prakash [EMAIL PROTECTED] 10/23/01 11:00AM We have a data-warehouse that is a combination of Snapshots and table-buildsbased on the snapshots.The table builds run at 4:30 am, scripts are setup to start the snapshots at7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys andsystem) tables inthe database.If the snapshots have been running all day, should I run an analyze before Ido the table builds?as opposed to after ?At what point do the statistics on a table become no good ? when a new rowis added ?ThanksDarren--Darren Browett P.Eng This messagewas transmittedSystems Analyst - Information Systems using 100%recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Browett, Darren INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Statspack Question
Hi Greg, I had already covered the part of getting down to the SQL earlier in my response. Gaja --- Greg Moore [EMAIL PROTECTED] wrote: so long as you look at the wait events, you will be looking at your database's bottlenecks, and in the world of Oracle Performance Tuning, that is all that counts. What about v$sql? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How long are statistics good for
Anand, Just curious : Is there some test or other observation that you can share with the list about dbms_stats using 10% as a boundary for staleness? John Kanagaraj Which version of Oracle are you using. In 8i you can set 'monitoring on' for the tables and use dbms_stats to analyze stale. (Though, I am getting error while using dbms_stats for the partitioned tables. So I have made a home made version to analyze stale). As per my calculations, package dbms_stats considers statistics stale if all DMLs affect more than 10% of number of rows. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
IOUG-A Live! 2001 info
Hi, Does anyone know when IOUG-A will complete the selections for the upcoming April 2002 conference? The website wasn't very helpful, and the submissions closed more than a month back... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Listen to great commercial-free christian music 24x7 at www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
REQ: would someone please send me
The 9i new features msg i posted on Self managing undo features. I'm compiling the info to put on the oracle-dba.com website and for some reason i didnt get that one at home(or more than likely deleted it w/o filing it first). I attempted to get it off the fatcity.com website but its a beast to edit. thanks, joe -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: IOUG-A Live! 2001 info
Original message said, 4th week(thnkagiving week in the US) in Nov. joe John Kanagaraj wrote: Hi, Does anyone know when IOUG-A will complete the selections for the upcoming April 2002 conference? The website wasn't very helpful, and the submissions closed more than a month back... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Listen to great commercial-free christian music 24x7 at www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
REQ: thanks
to everyone who sent me a copy, nice to know people are filing them away:) joe -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: providing 24*7 database ---
Title: RE: providing 24*7 database --- Thanks all for the input. hi tony , Quick question ... when you exchange partititons with non partitioned table data , all indexes on non partitioned tables become unusable status right. do have to rebuild them afterevery exchnage... naren -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Tuesday, 23 October 2001 05:06To: Multiple recipients of list ORACLE-LSubject: RE: providing 24*7 database --- We use a modified version of your duplicate schema idea. But we don't have the objects in different schemas. We use partitioned objects so that we can exchange the partitions with the production tables at a scheduled time. The voodoo is that we use a single range partition of MAXVALUE and all indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter in this setup since we aren't using the features for its advantages, just to be able to swap data and index segments on the fly. I've attached a transcript showing the actual sequence but I'll give you a short explanation first: There are production tables/indexes that are used by the application, whether directly or via synonyms. There is a second set of tables with a _TEMP suffix that have duplicate structural definitions (constraints, column names and data types, etc.) The indexes also end with a _TEMP but are identical to the production ones. The only difference is that they are partitioned tables/indexes. All partitioned objects have a single range partition by a bogus column. The single partition is bounded by the MAXVALUE keyword, so all of the data is contained in one partition. Now you can manipulate the _TEMP tables at your convenience without interrupting the access tot he "published" objects. Once you have refreshed your _TEMP objects and are ready to publish the new data your would execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITION TABLE tablename. That's it. No re-pointing of synonyms, revalidating of views/stored procs./etc. The application keeps chugging along. The next execution of SQL will use the published tables. HTH Tony Aponte ** pseudo-attachment ** SQL create table x(x1 number,x2 varchar2(50)); Table created. SQL create index xi1 on x(x1); Index created. SQL create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue)); Table created. SQL create index yi1 on y(x1) 2 local (partition yi1 ); Index created. SQL insert into x values (1,'original data from regular table'); 1 row created. SQL insert into y values (2,'original data from partitioned table'); 1 row created. SQL commit; Commit complete. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 2 original data from partitioned table SQL select * from y; X1 X2 -- -- 1 original data from regular table SQL select * from user_indexes; output snipped SQL select * from user_part_indexes; output snipped SQL alter table y exchange partition y with table x; Table altered. SQL select * from x; X1 X2 -- -- 1 original data from regular table SQL select * from y; X1 X2 -- -- 2 original data from partitioned table SQL select * from user_indexes; output snipped SQL select * from user_part_indexes; output snipped SQL drop table x; Table dropped. SQL drop table y; Table dropped. SQL spool off -Original Message- From: Narender Akula [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 5:30 PM To: Multiple recipients of list ORACLE-L Subject: providing 24*7 database --- hi gurus, Our shop ( GIS oracle spatials ) attempting to provide a production database (7x 24 hours) , currently we have to offline database for users while loading of data. we donot what users to access data while loading. We are thinking of provide 24* 7 services to customers with out going offline. What are the best possible solutions ? I had few but I donot know its right direction . Possible Solutions Replication - * not possible until Oracle 9i spatial (because of
Re: IOUG-A Live! 2001 info
I talked to a friend on the selection committee, she said late November so Joe's probably got it right :) --- Joe Testa [EMAIL PROTECTED] wrote: Original message said, 4th week(thnkagiving week in the US) in Nov. joe John Kanagaraj wrote: Hi, Does anyone know when IOUG-A will complete the selections for the upcoming April 2002 conference? The website wasn't very helpful, and the submissions closed more than a month back... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Listen to great commercial-free christian music 24x7 at www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REQ: would someone please send me
um joe, I sent it to you as part of the article I compiled... check your 9i account --- Joe Testa [EMAIL PROTECTED] wrote: The 9i new features msg i posted on Self managing undo features. I'm compiling the info to put on the oracle-dba.com website and for some reason i didnt get that one at home(or more than likely deleted it w/o filing it first). I attempted to get it off the fatcity.com website but its a beast to edit. thanks, joe -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Function
Hi All I have stored a user defined function as Varchar field in a table. How do I execute this function. Here is the table where the function is stored. SQL select * from func; FUNCTION_NAME - Calc_radius(5) This procedure contains the following Code : create function calc_radius(r in number) return number is begin return 3.14*r*r; end; Executing this funtion at SQL Prompt give the output as SQL SELECT CALC_RADIUS(5) FROM DUAL; CALC_RADIUS(5) -- 78.5 I want to execute this function from a PL/SQL Block. I tried to store this function into a variable and then execute it. But it returns only the content of the field FUNCTION_NAME and not the value. Can anyone suggest a solution for this problem ? Regards Dpb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
