Re: Should percent increase higher than 0 in 817?
I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Table CACHE/NOCACHE
Cannot tell for sure. Maybe - but there are lots of other queries with FTS executed thoughout the week. There appears to be no change to x$bh for these table blocks (including buf#) . -Ravi. --- AK [EMAIL PROTECTED] wrote: Did u do a full scan of table again after nocache setting ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 12:20 PM Guys, Help me figure this one out. Was helping a colleague diagnose slow response time (8.1.7/Solaris running Peoplesoft ). x$bh showed 102,248 out of 170,000 buffers belonged to a single table, which he said he cached explicitly. He did NOCACHE (on my suggestion) on the large table. I still find that the table is in buffer cache (even Buff# haven't changed - starts with buf#=1 - not sure if this means LRU end) even after a week. DB cannot be bounced since it is production. Do you know of any reason why it is not flushed out of cache when table is altered to NoCache? Thanks, Ravi. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Do Not Call
Title: RE: Do Not Call Hey, yeah, it COULD have been a LISP Eliza...! -Original Message- From: TOMPKINS, MARGARET To: Multiple recipients of list ORACLE-L Sent: 7/1/2003 4:40 PM Subject: RE: Do Not Call Yeah. It was either that or a Turing machine. Maggie -Original Message- From: Thater, William [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 01, 2003 4:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do Not Call -Original Message- From: TOMPKINS, MARGARET [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 01, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do Not Call That's only on the GUI side. The backend is an Excel spreadsheet with embedded FORTRAN calls. That gets interfaced with all the assembler code. :-) [Shrek] running BAP on TOS on the old GE box, eh? -- Bill Shrek Thater ORACLE DBA BAARF Party member #25 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Through the release of atomic energy, our generation has brought into the world the most revolutionary force since prehistoric man's discovery of fire. This basic force of the universe cannot be fitted into the outmoded concept of narrow nationalisms. For there is no secret and there is no defence; there is no possibility of control except through the aroused understanding and insistence of the peoples of the world. We scientists recognise our inescapable responsibility to carry to our fellow citizens an understanding of atomic energy and its implication for society. In this lies our only security and our only hope - we believe that an informed citizen will act for life and not for death. - Albert Einstein The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
Re: Microsoft VS Oracle (again)
- Original Message - Any comments? Yes. got that one last year. Still got it. Installed the doco in my PC at work so I could read it in peace and quiet (!). Installed the s/w as well so I could play with it. Went straight to their recovery manual. Basically, it reads like this: restore all db files apply saved logs No partial recovery, no online recovery, no PITR, nothing. Put me off the whole thing straight away. But if I may quote dubya: make no mistake, they'll fix it. And then it will be quite interesting... Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: nt script
After fix this problem. I am done with this. LEARN PERL.
[EMAIL PROTECTED] wrote:
Learn Perl and forget this ugly MS batch stuff.
It's such a kludge.
Seefelt, Beth [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/01/2003 02:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: nt script
FIND or FINDSTR should work equally well, I think. Maybe it depends on
your exact platform.
-Original Message-
Sent: Tuesday, July 01, 2003 5:07 PM
To: Multiple recipients of list ORACLE-L
Hi...
I could not follow this thread, so, I do not know the
current status...
anyway... I tested this and it works...
1.- First, I saw that you asked for: string called ORA
and your errorlevel statement failed.
2.- Second, the script sent by: Seefelt, Beth looks
like it works (I did not test it), EXCEPT that you
HAVE to change this line:
FIND ORA-03313 psprcsrv_psnt_%MMDD%.log
with this one:
FINDSTR ORA-03313 psprcsrv_psnt_%MMDD%.log
HTH
JL
there you go... (modify at your own needs...)
copy con test.cmd
findstr %1 alert_develop.log
if errorlevel 1 goto no
if errorlevel 0 goto si
:no
echo no se encontr#8804; la cadena: %1
goto fin
:si
echo si se encontr#8804; la cadena: %1
:fin
ctrl-Z
--- Seefelt, Beth [EMAIL PROTECTED]
wrote:
That actually makes it alot easier
FOR /F tokens=1-5 delims=/, %%i in ('date/t') DO
SET MMDD=%%j%%k
if NOT EXIST psprcsrv_psnt_%MMDD%.log goto no_file
FIND ORA-03313 psprcsrv_psnt_%MMDD%.log
if errorlevel 1 ( echo 'there is a match'
d:\start_proc.bat )
goto :EOF
:no_file
echo Can't find todays log!
exit /b 2
-Original Message-
Sent: Tuesday, July 01, 2003 3:51 PM
To: Multiple recipients of list ORACLE-L
Bob,
I tested it out, it works on one hard code file. If
I set thisfile it
works. But in reality, I need to find today's log,
the log file name
convention is always goes psprcsrv_psnt_0701.log
with the date suffix to
the end. I can't hard code the file name in the bat
file. Is there
anyway how to get around with this?
Thanks,
Joan
Bob Metelsky wrote:
Don't run it line by line
Save it to a file called myfind.bat
Then call from a command prompt
C:\ myfind.bat
Or click the batch file, be sure to leave the
pause so you can see an
error if any. Once its working, you can then
implemet blat as someone
suggested
Here is a version using variables
##
[myfind.bat]
:: Find example by BMetelsky on
Friday-April-11-2003-9:22:53 AM
:: This script searches thefile for a string and
if it finds it, sends
an email message to dbamail
:: 0 =is found
echo off
set themess=A Database SERVICE on %computername%
is NOT running on
%date% at %time%
set [EMAIL PROTECTED]
set blatfile=D:\dbmon\blattext.txt
set thefile=D:\dbmon\runit.log
set subject=A DB SERVICE IS NOT RUNNING
set [EMAIL PROTECTED]
echo starting %blatfile%
find ERROR: %thefile% NUL
IF ERRORLEVEL 1 GOTO no
IF NOT ERRORLEVEL 1 GOTO yes
:no
goto end
:yes
echo A db service is down on
%computername%%blatfile%
blat D:\dbmon\blattext.txt -subject %subject%
-to %dbamail% -i
%from% -body %themess%
goto end
:end
@exit
rem echo %errorlevel%
##
Hi Bob, thanks for the reply. I added your code
and tested
still no good. I tested the case without a
match. Still go to run.
Joan
D:\oracle\adminFORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log
-d+0 -cCMD
/c echo @FILE PSPRCSRV_PSNT_0630.log
PSPRCSRV_PSNT_0701.log
D:\oracle\adminecho on
D:\oracle\adminFORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log
-d+0 -cCMD
/c type d:\pslog
[EMAIL PROTECTED]|findstr ORA-03113
D:\oracle\admin\test.log
D:\oracle\adminrem FORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log
-d+0 -cCMD
/c type d:\p
[EMAIL PROTECTED]|findstr ORA-03113
NUL
D:\oracle\adminIF ERRORLEVEL 1 GOTO no
D:\oracle\adminIF NOT ERRORLEVEL 1 GOTO
RUN_PROC
D:\oracle\adminrem d:\psfm\start_proc_sched.bat
D:\oracle\adminecho run
run
D:\oracle\adminpause
Press any key to continue . . .
D:\oracle\admingoto end
D:\oracle\adminecho finished
finished
D:\oracle\adminrem if errorlevel 1
@d:\psfm\start_proc_sched.bat
Bob Metelsky wrote:
echo off
@cls
find ORA-124 C:\yourlog.txt NUL
IF ERRORLEVEL 1 GOTO no
IF NOT ERRORLEVEL 1 GOTO yes
:no
ECHO NOT FOUND
goto end
:yes
ECHO String IS found
Re: Should percent increase higher than 0 in 817?
Historically, two values of PCTINCREASE were practical: 0 - keep same extent size 100 - double extent size Have Fun :) Gaja Krishna Vaidyanatha wrote: Greetings, Non-zero PCTINCREASE causes unnecessary free space fragmentation in your tablespaces and should be avoided. Especially in 8.1.7, where there is no need for this, given that locally-managed tablespaces are supported, where PCTINCREASE is NOT even relevant. If you use One of the historical reasons for having a non-zero PCTINCREASE in the Oracle6 and Oracle7 days, was to urge SMON to automatically coalesce free space, at the end of the file, especially after an object is dropped. In 7.3, the alter tablespace XXX coalesce command was introduced, which provided on-demand coalescing even with PCTINCREASE at 0. If you have dictionary-managed tablespaces try to convert to locally-managed tablespaces. Otherwise, use a PCTINCREASE of 0, use uniform extent sizes at the tablespace and DO NOT use extent-sizing at the object-level. Cheers, Gaja --- [EMAIL PROTECTED] wrote: Everything I have seen says no. I did the following. Set PCTINCREASE = 0 Set my initial and next extents to 5m so they are uniform. A woman I work with swears there is Oracle documentation that in 817 PCTINCREASE should be greater than 0 because the default is 50. ORA-03232 unable to allocate an extent of string blocks from tablespace string 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com
RE: Oracle DBA 9i Books ever needed.
Not a problem, Cary :) I was pleasently surprised to see Chris Lawson's new book at Borders. Did a fair amount of reading (3 chapters), while waiting for Harry Potter's book to go on sale by 12:01 AM on June 21! (also, wondered what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Chris has indeed done a very nice job. I should have bought the book that night. It's now on order. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: I'll second the recommendation of Christopher Lawson's new book. Very nice. grinBy not mentioning Gaja and Kirti's work explicitly in the first sentence should not be considered a withholding of endorsement. My opinion of their work is--hey!--right on the cover./grin Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, July 01, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Rajuveera Are you seeking a book on DBA (administration) or performance tuning or real time database design? The more specific the question, the more likely the answer will match your needs. If you have some books, don't be shy about mentioning them so you don't just get recommendations that include books you already have. Since most of us collect books, if you say what you need that isn't included in your existing books, we can recommend books that cover different material. For performance tuning, a great book to start with is: Oracle 101 Performance Tuning by Jaga Krishna Vaidyanatha, Kirtikumar Deshpande, and John Kostelac. I have also been impressed with: The Art and Science of Oracle Performance Tuning by Christopher Lawson. This is very new, just published. Both of these books emphasize the fact that successful performance tuning begins with a correct philosophy. With a mistaken philosophy you can spend a lot of time and may even think you are being successful. There are two aspects to performance tuning, tuning the system and tuning your SQL. Which is more important will vary by your situation and as a competent DBA you need to understand both. For example, some packaged applications do not allow you to tune the SQL at all. But often tuning SQL statements can yield great gains in performance. As to the other topics you mention, perhaps it would be best if you reply with more specifics as to your situation. Sitting here in the U.S., it is rather difficult for me to tell what books are available to you in Hyderabad. Can you buy books from www.amazon.com? Getting a book that applies to both 8i and 9i is easy, in my experience. When a new Oracle version arrives, publishers often brush up an existing book with some new information so they can place the new version number on the book. It takes awhile for authors to absorb the impact that the changes can have on the daily work of a DBA and produce a book that incorporates these changes. I'm just trying to accurately represent the situation, not being critical. Fortunately when Oracle creates a new version, 99% of the features already there still work. Just familiarize yourself with the main feature differences between versions and you'll be fine with any book. I will be on vacation for several days, so if you are sleeping when this reply is posted, I will be unable to reply. However, there are others on this list that can provide far more competent assistance than I, even including some book authors. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:40 AM To: Multiple recipients of list ORACLE-L Dear Friends, I am willing to buy Oracle DBA 9i books. ,I need your suggestion on this. I got information friends from this list that the book (Practical Oracle 8i - Jonathan Lewis.) is practically good. Even I am also looking for the same kind of book, But I want it for both 8i and 9i. It should be more practical oriented , performance tuning and real time database design and problems. The book should be more practical oriented . What abt performance tuning 101 techniques , Oracle 9i DBA handbook. What are the best books for Oracle DBA (Intermediate level ) available in INDIA , HYDERABAD. Any URL / best sites also will be helpful. Thanks Rajuveera __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To
Re: controlfile backup obsolete above 2 days
MetaLink Bug 2458246 (fixed in 9.2.0.2) DENNIS WILLIAMS wrote: Offhand it sounds to me like you may have uncovered an error. I would search metalink and if you don't find an error matching this situation, I would file a TAR. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, June 20, 2003 9:34 PM To: Multiple recipients of list ORACLE-L Hello list sorry to trouble you all so much, I tried all that I stated below on another database I created, and as long as I don't use an spfile , no problem. As soon as I start using an spfile , I always get my backup shown as obsolete , irrespective of the number of days (the value of n) I specify in ''report obsolete recovery window of n days; '' . Also a few more details , previously i was getting the problem on a 9.2.0.1.0 enterprise edition database in noarchivelog mode. The problem I stated above is on a 9.2.0.1.0 enterprise edition database in archivelog mode with automatic archiving enabled. All this on win32. - Original Message - To: [EMAIL PROTECTED] Sent: Saturday, June 21, 2003 00:28 : Hello list : I was having problems with my controlfile backups being reported as : obsolete ; : I have narrowed down the problem to the number of days specified in : the recovery window . : : 1. My control_file_record_keep_time=7 : : 2. Suppose I start rman and there are no backups yet. (This is : confirmed by the commands : : delete noprompt force backup ; : delete nopromt force copy ; : list backup ; (This shows no o/p confirming that there are no : backups ) : list backup of controlfile ; (This shows no o/p confirming that there : are no backups ) : : 3. All the rman configuration settings are at their default values . : I did not modify any , and just to be sure I checked using 'show all : ;' : : 4. Now I backup the database : : backup database ; : : As expected it creates a single set with a single piece whose name is : in the o/p: : : channel ORA_DISK_1: finished piece 1 at 21-Jun-2003 00:13:01 : piece handle=D:\ORACLEXP\ORA92\DATABASE\03EQ6ARH_1_1 comment=NONE : channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28 : Finished backup at 21-Jun-2003 00:13:01 : : : 5. I confirm this by using 'list backup ;' and : list backup of controlfile ; : : 6. NOW THE PROBLEM IS : : : : RMAN report obsolete recovery window of 1 days ; : : no obsolete backups found : : : RMAN report obsolete recovery window of 2 days ; : : no obsolete backups found : : : : RMAN report obsolete recovery window of 3 days ; : : Report of obsolete backups and copies : Type KeyCompletion TimeFilename/Handle : - -- -- : Backup Set3 21-Jun-2003 00:12:52 : Backup Piece 3 21-Jun-2003 00:12:52 : D:\ORACLEXP\ORA92\DATABASE\03EQ6ARH_1_1 : : Why is this happening for all values above 2 days ? : Please advise : . : -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chip INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Production Database stays up; copies will not recover - Resol
Thanks Tanel, All of the resetlog_change#'s where the same. The problem has been resolved, with 2 likely sources. The disk mirror set on the affected mount points were completely rebuild, and (my mistake) we used a backup controlfile instead of a copy of an active one to recover. I could have sworn I implemented the use of a backup controlfile in this process years ago, but the code doesn't lie. Mike -Original Message- Sent: Tuesday, July 01, 2003 4:30 PM To: Multiple recipients of list ORACLE-L Hi! Oracle can only guarantee database consistency and sanity when it's underlying layers like OS and disk controllers do not fail. Try: Startup mount; select distinct resetlogs_change# from v$datafile_header; Do you see only one distinct change#? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 8:34 PM Tru64 5.1 8.1.7.3 We had a disk controller fail last week, but redundant hardware let the system stay up until it could be replaced over the weekend. The production database restarted without any problems, trace files, or alert log errors. The problem is that the reporting copy of production, taken from a disk mirror break (hot backup, mounted on a seperate system) will not recover. I get an ORA-1190 'controlfile or data file 1 is from before the last RESETLOGS' but the last resetlogs was over a month ago. First attempt to solve was to use a slightly older copy of the SYSTEM datafile from an earlier backup disk mirror break (hot backup). Results were the same. I then assumed something was wrong with the controlfile, so I tried to rebuild that. No luck, the Create controlfile command returned a ORA1189 'file is from a different RESETLOGS than previous files' against file#2 (even though I replaced the file# 1's datafile with the original [neither copy of #1 was opened]). My worries are two-fold; 1) that there is a subtle error in the control file or SYSTEM datafile that only an attempted recovery exposes and 2) any backup taken after the hardware failure last week will not be viable. Any suggestions as how to proceed to either prove or disprove this. Thanks, Mike Hand Polaroid Corp P.S. I had hoped to go on vacation at the end of the week leaving a stable database behind (for a change). This transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
AW: printing out of Pl/SQL
Von: Goulet, Dick [mailto:[EMAIL PROTECTED]
Frank,
I've not done dll calls from Oracle as well, we use
HP-UX and Linux, which I believe is much simpler. Anyhow,
along a similar lane, here's a snippet of extproc C code that
I've written to run an SQR from a PL/SQL call:
char *run_sqr(char *prog, char *uname)
{ FILE *sout = NULL;
FILE *lg = NULL;
char cmd[1000];
char bfr[200];
char *rval = NULL;
.
Mornin' Dick,
Thank you, it's very kind of you. I will give your snippet away to one of
our
C gurus. I'll see, what he can make out of this.
Thank you again
Frank
Confidentiality Notes
This email and any files transmitted is intended only for the person or
entity to which it is addressed and may contain confidential and/or
privileged material. If you are not the authorised recipient, any use,
disclosure, copying, distribution or any action taken or omitted to be
taken in reliance on it, is prohibited. If you received this in error,
please contact the sender and delete the material from any computer.
E-mail messages are not necessarily secure. Scheidt Bachmann does not
accept responsibility for any changes made to this message after it was
sent.
Thank you for your cooperation!
Diese E-Mail und alle angehängten Dateien ist ausschließlich für die
Person / Personen bestimmt, an die diese adressiert ist und könnte
vertrauliche und / oder rechtlich geschützte Informationen enthalten.
Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum
Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede
Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das
Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte
Information untersagt. Sollten Sie diese E-Mail irrtümlich erhalten
haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail
von jedem Computer.
E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt
Bachmann übernimmt keine Verantwortung für Veränderungen dieser
Mitteilung, die nach dem Senden vorgenommen wurden.
Herzlichen Dank für Ihre Unterstützung!
-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Foelz.Frank
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Upgrade 8.1.7.3.0 to 8.1.7.4.1 on W2K
I have a few databases in Home0 and a few in Home1 and need to move one from Home0 to Home1 So if I understand correct I can do: Oradim -delete -sid SID Remove the entries that look like ORA_SID_AUTOSTART ORA_SID_SHUTDOWN ORA_SID_SHUTDOWNTYPE ORA_SID_SHUTDOWN_TIMEOUT From Home0(if not done by oradim) Put the initSID.ora file in the new home location Create a new service NEW_HOME\bin\oradim.exe -new -sid SID -startmode m NEW_HOME\bin\oradim.exe -edit -sid SID -startmode a NEW_HOME\bin\orapwd.exe file=d:\oracle\ora90\database\PWDSID.ora password=password (Or can I just copy the password file?) Change the listener.ora Start the database Run catalog/catproc/catexp etc Is this Correct??? TIA Jack -Original Message- Sent: Tuesday, July 01, 2003 11:07 PM To: Multiple recipients of list ORACLE-L Assuming that you are changing the second Oracle Home, ie. Home1. Change the following entries in the registry: HKLM\Software\Oracle\ALL_HOMES\ID1\PATH HKLM\Software\Oracle\HOME1\ORACLE_HOME Depending on how you've located init and dump files, etc., you may see other entries in HKLM\Software\Oracle\HOME1 that need to be changed as well. You then need to remove and re-create the service. oradim -delete -sid SID oradim -new -sid SID -pfile path to init.ora type oradmin on a command line to get all options. Then check HKLM\System\CurrentControlSet\Services\OracleServiceSID to ensure that the correct binary is in use. Edit your tnsnames.ora/names registry or whatever you use for name resolution. That should about do it. Jared Jack van Zanen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/01/2003 08:55 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Upgrade 8.1.7.3.0 to 8.1.7.4.1 on W2K Hi All, We have a test machine with both the 81730 and 81741 software in their own oracle homes. We now want to move a database from the 81730 home to 81741 I know I have to run the catalog.sql, catproc.sql from the new home etc... But how do I tell the registry that the database moved homes?? TiA Jacob A. van Zanen Oracle DBA Quant Systems Europe b.v. Tel : +31 (0) 251 - 268 268 Mobile: +31 (0) 6 51308813 Fax: +31 (0) 251 - 268 269 E-mail: [EMAIL PROTECTED] Visit our web site at http://www.quantsystems.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: create imp and exp Oracle as single-task binaries?
I once experimented with it (8.1.7.smth on linux), but started having crashes. Don't remember what exactly crashed, but it didn't work. I believe last version where it was supported, was 8.0? Anyway, even if it would run correctly, I wouldn't use it anywhere else than during one-time migrations or maybe loading staging databases. Of course ST binaries behaviour should be thouroughly tested before. Also, if your system is IO bound, you won't get any benefit from STL, because the same amount of data has to be read during exp anyway... I'd set direct parameter to true and recordlength maximum for increasing exp speed. Buffer parameter doesn't have any effect when doing direct mode export. For imp, there's lot's of opportunities for speeding up it's speed. For huge tables/schemas I would: 1) Import the table definitions without any constraints or indexes. 2) Disable triggers if any. 3) Import data using large buffer parameter. (Import is always done in conventional mode). For extremely huge tables CTAS over dblinks could be used instead. 4) Enable triggers 5) Get index commands using indexfile from structure export (use indexes=y constraints=y indexfile=xxx to get bot stand-alone index definitions and constraint-index defs) 5.1) Modify index creation commands to use parallel clauses and nologging 5.2) Run index creation commands 6) Run structure import again with indexes=n rows=n constraints=y to enable constraints I hope I didn't miss anything... There's actually more stuff which you could use such are unsupported _disable_logging and _wait_for_sync parameters which you could use during migrations or test environments... So, there's plenty of other stuff which to try out first before single task linking. Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:49 AM Hi: I saw the following message while searching web for some tips to improve exp speed. Has anyone tried this un-supported way and found improved performance? Had any problems? I don't have a machine that I can try this. Guang - Oracle Export/Import Scripts: How to create imp and exp Oracle as single-task binaries Posted by: Administrator If someone is interested in implementing the create a new Oracle import binary as a single-task, see the steps below. The import and export programs (imp/exp) run in two task mode to protect the SGA from potential corruption by user programs. By relinking these two programs in single task mode you can gain much improvement in speed. In many cases up to 30%. To read the complete article, click on the Read more ... link. Submitted by our member: Chris Oracle themselves use this method although it is NOT supported by Oracle. Running in single-task is much faster but it requires more memory since the Oracle executable's text is no longer shared between the front-end and background processes. If you need to transfer large amounts of data between databases, relink the executale for greater efficiency. To relink the RDBMS kernel and create the two new imp/exp programs, use the following: $ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk singletask $ make -f ins_rdbms.mk expst $ make -f ins_rdbms.mk impst $ make -f ins_rdbms.mk sqlldrst $ mv expst $ORACLE_HOME/bin/ $ mv impst $ORACLE_HOME/bin/ $ mv sqlldrst $ORACLE_HOME/bin/ Now use expst and impst instead of imp or exp when doing export import of data. Let me know if you have any problems, or improve the above procedure somehow. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Filesystem for Linux production database server?
Linux file systems usually do not support direct I/O (bypassing the buffer cache), which means that you're going to have double caching with almost This is no longer the case. Look at the O_DIRECT open option, which can be used with oracle. Make sure that your distribution has support for it. everything except raw devices. You can have up to 256 raw devices on a Linux box and if your database is small enough (32-bit system, 2G limit applies), that will definitely be the fastest option. My recommendation is to go with IBM JFS because it is a battle tested, mature, well performing file system. I would advise against XFS because nobody uses it. Quite a few people use XFS. Its made it both into the -ac branch and into the 2.5 kernel. However, *I* would strongly recommend ext3 as many people run oracle on it and that OS/filesystem combination has been beaten to hell a lot more than linux/JFS. Also, when placed into data=writeback,noatime mode[1] performs *much* better than the default data=ordered. I recall a rerun of that benchmark in which ext3/writeback was pretty close to ext2's performance save that it has much faster recovery on boot. Second on the most used list is reiserfs, however ext3/writeback's direct/block io should be somewhat faster than it. Being that i don't have my own large-scale personal testing in a publishable form, i'll point you to someone else's work (he, unfortunately, doesn't appear to have had a raid array to beat up with via an SMP server) http://oregonstate.edu/~kveton/fs/page2.php I'll note that on my desktop running 2.5.70 the performance for bonnie resulted in the following order (all noatime) FASTEST:ext3/writeback ; XFS ; JFS ;SLOWEST but with only a single platter and one cpu, not much can be said :) Before you get concerned about what writeback means, read the man page for the filesystem you'll find that it is basically what most other filesystems (jfs, xfs, reiserfs, vxfs, etc) use for their default/performance options. however,do make sure that you're patched up to date if you go near data=journal. [1] The noatime mount parameter should be used for any filesystem backing oracle (ext[23],xfs,jfs,reiserfs, etc as there is no need to keep updating last access times, so you can save a whole pile of ~1k writes. One other thing: if you are using fibre attached storage and are *not* using async io, then put some usefully large value into /proc/sys/vm/max-readahead, remember it is power of 2 minus one, so youd want to look at values like 255, 511, and 1023. As for availability, you'll have to go with some RAID controller and standby database or RAC. In case of RAC, your choice of file systems is clear (OCFS). Whatever you do, do not configure your RAID as RAID-5 but RAID 1+0 (BARF). what he said, save that i like raid10. which means that On 2003.06.29 11:44, zhu chao wrote: Hi, friends that run oracle on linux: We are running some database on linux, some with UPS protection and some not, all using ext2. Currently we have a DW server running oracle 817/Redhat 7.2/Ext2. When server crash because of power supply, fsck took rather long time! And we are planning migrate to redhat as2.1/9.2, and I am thinking of what kind of filesystem shall I choose for the new server. The database is about 150G.I had planned to use ext3 for it is journal filesystem, But according to http://www.linuxjournal.com/article.php?sid=5841, ext3 is bad for performance, and some friend said he hit filesystem crash with ext3. So,what is your experience using filesystem for oracle in linux? Performance and avaliability is of most important. Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- .-... . -.-. .-. . --- . ... ... .- --. . Craig I. Hagan hagan(at)cih.com Never let your sense of morals prevent you from doing what is right. - Mayor Hardin from Isaac Asimov's Foundation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig I. Hagan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: Should percent increase higher than 0 in 817?
I acutally work with oracle ever since the version 4.1 (IBM PC/XT, 512KB RAM) but a) I've never worked with 4.0 b) I know very well what PCTINCREASE is. Conclusion: Tanel, it wasn't me! I swear! Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
error reading file
Automate an update
Hi all, I need help in order to create a following "mechanism". I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, JoshuaGå före i kön och få din sajt värderad på nolltid med Yahoo! Express
RE: Oracle DBA 9i Books ever needed.
you mean we don't tune Oracle using spells, incantations and prayers? Dang, I knew I was doing something wrong! --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Not a problem, Cary :) I was pleasently surprised to see Chris Lawson's new book at Borders. Did a fair amount of reading (3 chapters), while waiting for Harry Potter's book to go on sale by 12:01 AM on June 21! (also, wondered what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Chris has indeed done a very nice job. I should have bought the book that night. It's now on order. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: I'll second the recommendation of Christopher Lawson's new book. Very nice. grinBy not mentioning Gaja and Kirti's work explicitly in the first sentence should not be considered a withholding of endorsement. My opinion of their work is--hey!--right on the cover./grin Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, July 01, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Rajuveera Are you seeking a book on DBA (administration) or performance tuning or real time database design? The more specific the question, the more likely the answer will match your needs. If you have some books, don't be shy about mentioning them so you don't just get recommendations that include books you already have. Since most of us collect books, if you say what you need that isn't included in your existing books, we can recommend books that cover different material. For performance tuning, a great book to start with is: Oracle 101 Performance Tuning by Jaga Krishna Vaidyanatha, Kirtikumar Deshpande, and John Kostelac. I have also been impressed with: The Art and Science of Oracle Performance Tuning by Christopher Lawson. This is very new, just published. Both of these books emphasize the fact that successful performance tuning begins with a correct philosophy. With a mistaken philosophy you can spend a lot of time and may even think you are being successful. There are two aspects to performance tuning, tuning the system and tuning your SQL. Which is more important will vary by your situation and as a competent DBA you need to understand both. For example, some packaged applications do not allow you to tune the SQL at all. But often tuning SQL statements can yield great gains in performance. As to the other topics you mention, perhaps it would be best if you reply with more specifics as to your situation. Sitting here in the U.S., it is rather difficult for me to tell what books are available to you in Hyderabad. Can you buy books from www.amazon.com? Getting a book that applies to both 8i and 9i is easy, in my experience. When a new Oracle version arrives, publishers often brush up an existing book with some new information so they can place the new version number on the book. It takes awhile for authors to absorb the impact that the changes can have on the daily work of a DBA and produce a book that incorporates these changes. I'm just trying to accurately represent the situation, not being critical. Fortunately when Oracle creates a new version, 99% of the features already there still work. Just familiarize yourself with the main feature differences between versions and you'll be fine with any book. I will be on vacation for several days, so if you are sleeping when this reply is posted, I will be unable to reply. However, there are others on this list that can provide far more competent assistance than I, even including some book authors. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:40 AM To: Multiple recipients of list ORACLE-L Dear Friends, I am willing to buy Oracle DBA 9i books. ,I need your suggestion on this. I got information friends from this list that the book (Practical Oracle 8i - Jonathan Lewis.) is practically good. Even I am also looking for the same kind of book, But I want it for both 8i and 9i. It should be more practical oriented , performance tuning and real time database design and problems. The book should be more practical oriented . What abt performance tuning 101 techniques , Oracle 9i DBA handbook. What are the best books for Oracle DBA (Intermediate level ) available in INDIA , HYDERABAD. Any URL / best sites also will be helpful. Thanks Rajuveera __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the
RE: perl DBI/DBD: can I pass in an array as parameter?
not a guru Chris? me thinks you sell yourself short. i like both solutions
- very nice and TMTOWTDI!
and Tim's coming soon to a module near you: execute_array() was nice too ;)
hopefully that answers the original posters question.
-Original Message-
Sent: Tuesday, July 01, 2003 6:05 PM
To: Multiple recipients of list ORACLE-L
Responses to 2 emails below:
Alex wrote:
not sure if this is what you want. one sql call
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?
and tab1.X = ?
and tab1.X = ?
;
@my_array = (1,2,3);
sth-execute(@my_array);
Errm, no rows will be returned. Think about this one a little more.
Steve Ollig wrote:
ok - that makes more sense. sorry for misinterpreting the
question. i've
never done it, but my first instinct would be to explore
using an in clause
in the query -
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (1, 2, 3)
can you simply pass an array to the prepared statement that
way? i'd try it
but don't have a sandbox with the DBI/DBD modules handy.
perhaps one of the great Perl gurus of the list will offer
some insight...
I'm not a Perl guru, but I can think of 2 solutions:
#build an array with the keys you want to look for:
my @my_array = (1, 2, 3);
# then add that many ?s to the query
#the 'in' solution:
my $query = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (' . join(',',('?') x @my_array) . ')';
# or the 'union' solution
my $subquery = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?';
my $query = join(' union ', ($subquery) x @my_array);
# pick only one of the above!
# and then
my $sth = $db-prepare($query);
$sth-execute(@my_array);
# then get the data back your favorite way: fetchall_arrayref, fetch_array,
etc
But is it really worth the trouble? As long as you are using bind variables,
the overhead of multiple executes should not be very high.
warning: these are typed from memory - I may have typos in the perl code.
But the concept should work.
-Chris
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be
privileged. It is intended for the addressee(s) only. Access to this e-mail
by anyone else is unauthorized. If you are not an addressee, any disclosure
or copying of the contents or any action taken (or not taken) in reliance on
it is unauthorized and may be unlawful. If you are not an addressee, please
inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: STEVE OLLIG
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Automate an update
Hi, If you are looking to populate this table with the y/n value based on the time, you could just create a job (oracle/cron) to update the table with the appropriate value when the time changes over. That would be the simplest way, but maybee not the cleanest. HTH, Joe -- Joe Frohne Rawson Oaks Consulting, Remote Oracle Administration http://www.rawsonoaks.com [EMAIL PROTECTED] or [EMAIL PROTECTED] Hi all, I need help in order to create a following mechanism. I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Frohne INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Do Not Call
Title: Message snip http://uptime.netcraft.com/up/graph/?host=www.donotcall.gov Well, if it were unix then it couldn't be SQLserver. It is Windows, therefore I assume its SQLserver, but it could be Oracle. But who runs Oracle on Windows anyway *hahahaha* ... We do ... groan-- Steve Wolfe [EMAIL PROTECTED] 07/01/03 02:29PM Paradox for DOS -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Subject: Do Not Call There have been over 10,000,000 entries made in the National Do Not Call Registry since Friday June 27. Does anyone know the database engine in which this is stored? Curious in Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: nt script
Thanks to all who replied. Especialy thanks to Seefelt. This solution
works. except errorlevel 1 is not a match.
Thanks again!!!
Joan
Seefelt, Beth wrote:
That actually makes it alot easier
FOR /F tokens=1-5 delims=/, %%i in ('date/t') DO SET MMDD=%%j%%k
if NOT EXIST psprcsrv_psnt_%MMDD%.log goto no_file
FIND ORA-03313 psprcsrv_psnt_%MMDD%.log
if errorlevel 1 ( echo 'there is a match'
d:\start_proc.bat )
goto :EOF
:no_file
echo Can't find todays log!
exit /b 2
-Original Message-
Sent: Tuesday, July 01, 2003 3:51 PM
To: Multiple recipients of list ORACLE-L
Bob,
I tested it out, it works on one hard code file. If I set thisfile it
works. But in reality, I need to find today's log, the log file name
convention is always goes psprcsrv_psnt_0701.log with the date suffix to
the end. I can't hard code the file name in the bat file. Is there
anyway how to get around with this?
Thanks,
Joan
Bob Metelsky wrote:
Don't run it line by line
Save it to a file called myfind.bat
Then call from a command prompt
C:\ myfind.bat
Or click the batch file, be sure to leave the pause so you can see an
error if any. Once its working, you can then implemet blat as someone
suggested
Here is a version using variables
##
[myfind.bat]
:: Find example by BMetelsky on Friday-April-11-2003-9:22:53 AM
:: This script searches thefile for a string and if it finds it, sends
an email message to dbamail
:: 0 =is found
echo off
set themess=A Database SERVICE on %computername% is NOT running on
%date% at %time%
set [EMAIL PROTECTED]
set blatfile=D:\dbmon\blattext.txt
set thefile=D:\dbmon\runit.log
set subject=A DB SERVICE IS NOT RUNNING
set [EMAIL PROTECTED]
echo starting %blatfile%
find ERROR: %thefile% NUL
IF ERRORLEVEL 1 GOTO no
IF NOT ERRORLEVEL 1 GOTO yes
:no
goto end
:yes
echo A db service is down on %computername%%blatfile%
blat D:\dbmon\blattext.txt -subject %subject% -to %dbamail% -i
%from% -body %themess%
goto end
:end
@exit
rem echo %errorlevel%
##
Hi Bob, thanks for the reply. I added your code and tested
still no good. I tested the case without a match. Still go to run.
Joan
D:\oracle\adminFORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log -d+0 -cCMD
/c echo @FILE PSPRCSRV_PSNT_0630.log PSPRCSRV_PSNT_0701.log
D:\oracle\adminecho on
D:\oracle\adminFORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log -d+0 -cCMD
/c type d:\pslog [EMAIL PROTECTED]|findstr ORA-03113
D:\oracle\admin\test.log
D:\oracle\adminrem FORFILES
-pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log -d+0 -cCMD
/c type d:\p [EMAIL PROTECTED]|findstr ORA-03113 NUL
D:\oracle\adminIF ERRORLEVEL 1 GOTO no
D:\oracle\adminIF NOT ERRORLEVEL 1 GOTO RUN_PROC
D:\oracle\adminrem d:\psfm\start_proc_sched.bat
D:\oracle\adminecho run
run
D:\oracle\adminpause
Press any key to continue . . .
D:\oracle\admingoto end
D:\oracle\adminecho finished
finished
D:\oracle\adminrem if errorlevel 1 @d:\psfm\start_proc_sched.bat
Bob Metelsky wrote:
echo off
@cls
find ORA-124 C:\yourlog.txt NUL
IF ERRORLEVEL 1 GOTO no
IF NOT ERRORLEVEL 1 GOTO yes
:no
ECHO NOT FOUND
goto end
:yes
ECHO String IS found
pause
goto end
:end
exit
Hi listers,
I am working on a nt script. I download FORFILE exe, it
works fine.
My intention is finding the ORA-03113 string in the log.
If there is
a match, then reboot the server, else do nothing. I don't have
problem with the findstr part. However, the errorlevel
always return
0, no matter it find the error or not. So the if statement is
not
working. Can somebody give me some light on NT syntax? On
unix, it
is so easy to script the condition. Please help,
Thanks,
Joan
FORFILES -pd:\pslogs_psfm\fmdev8\_psprcsrvlog\ -s -m*.log
-d+0 -cCMD /c type
d:[EMAIL PROTECTED]|findstr ORA-03113 if
errorlevel 1 goto run_proc echo 'there is a match'
if errorlevel 0 goto end
echo 'there is no match'
:run_proc
d:\start_proc.bat
goto end
:end
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and web
hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of
Re: Automate an update
Joshua, To update existing rows you could do something like: update x set window_open = 'Y' where to_char (time_field, 'hh24:mi:ss') between '08:00:00' and '16:00:00'; Similar for window_open = 'N'. Create a trigger to deal with future inserts. Gudmundur Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CASE in PL/SQL
Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Redo Copy Latch contention ??
Hello ALL, Do you guys think we have redo copy latch contention ?? Also what are your suggestions on tuning Redo Copy Latch ?? SUBSTR(LN.NAME,1,20)GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES -- -- -- redo allocation 943350646 8862115 0 0 redo copy 22097 497 907958724 1592481 14:54:54 SQL select (497/22097)*100 from dual; (497/22097)*100 ~ (misses/gets)*100 --- 2.2491741 --- Oracle suggests it should be under 1% Madhu Reddy X13944 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle DBA 9i Books ever needed.
Title: RE: Oracle DBA 9i Books ever needed. The Oracle school of Witchcraft and Wizardry April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need. ~ Jerry Gillies ~ -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle DBA 9i Books ever needed. you mean we don't tune Oracle using spells, incantations and prayers? Dang, I knew I was doing something wrong! --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Not a problem, Cary :) I was pleasently surprised to see Chris Lawson's new book at Borders. Did a fair amount of reading (3 chapters), while waiting for Harry Potter's book to go on sale by 12:01 AM on June 21! (also, wondered what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Chris has indeed done a very nice job. I should have bought the book that night. It's now on order. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: I'll second the recommendation of Christopher Lawson's new book. Very nice. grinBy not mentioning Gaja and Kirti's work explicitly in the first sentence should not be considered a withholding of endorsement. My opinion of their work is--hey!--right on the cover./grin Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, July 01, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Rajuveera Are you seeking a book on DBA (administration) or performance tuning or real time database design? The more specific the question, the more likely the answer will match your needs. If you have some books, don't be shy about mentioning them so you don't just get recommendations that include books you already have. Since most of us collect books, if you say what you need that isn't included in your existing books, we can recommend books that cover different material. For performance tuning, a great book to start with is: Oracle 101 Performance Tuning by Jaga Krishna Vaidyanatha, Kirtikumar Deshpande, and John Kostelac. I have also been impressed with: The Art and Science of Oracle Performance Tuning by Christopher Lawson. This is very new, just published. Both of these books emphasize the fact that successful performance tuning begins with a correct philosophy. With a mistaken philosophy you can spend a lot of time and may even think you are being successful. There are two aspects to performance tuning, tuning the system and tuning your SQL. Which is more important will vary by your situation and as a competent DBA you need to understand both. For example, some packaged applications do not allow you to tune the SQL at all. But often tuning SQL statements can yield great gains in performance. As to the other topics you mention, perhaps it would be best if you reply with more specifics as to your situation. Sitting here in the U.S., it is rather difficult for me to tell what books are available to you in Hyderabad. Can you buy books from www.amazon.com? Getting a book that applies to both 8i and 9i is easy, in my experience. When a new Oracle version arrives, publishers often brush up an existing book with some new information so they can place the new version number on the book. It takes awhile for authors to absorb the impact that the changes can have on the daily work of a DBA and produce a book that incorporates these changes. I'm just trying to accurately represent the situation, not being critical. Fortunately when Oracle creates a new version, 99% of the features already there still work. Just familiarize yourself with the main feature differences between versions and you'll be fine with any book. I will be on vacation for several days, so if you are sleeping when this reply is posted, I will be unable to reply. However, there are others on this list that can provide far more competent assistance than I, even including some book authors. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:40 AM To: Multiple recipients of list ORACLE-L Dear Friends, I am willing to buy Oracle DBA 9i books. ,I need your suggestion on this. I got information friends from this list that the book (Practical Oracle 8i - Jonathan Lewis.) is practically good. Even I am also looking for the same kind of book, But I want it for both 8i and 9i. It should be more practical
Re: fine grained access
What our security officer wants is that he can see who changed something in those tables Sounds more like auditing than FGAC. Gudmundur Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automate an update
This may be complex but I think can work , create a payload in advanced queue with delay=8hrs ( or whatever ). run a job(immediately..no future timing)which listens to this advaced queue . As soon as job gets the event in queue (after 8hrs) it will update the column based on logic ( if time = ...blah blah ) and at the end it will insert another item in queue with delay=8hrs . So in 24hrs job willget 3 payloads and check the timing at that time .. . This job will be running in an infinite loop . You can create some other procedure to stop it , by sending some particuler item in queue . As usual there might be more methods you will be getting in few minutes :) -Ak - Original Message - From: Joshua Becker To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 02, 2003 2:40 AM Subject: Automate an update Hi all, I need help in order to create a following "mechanism". I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express
Re: CASE in PL/SQL
You don't need CASE. try : to_char( date , 'Q') George Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: George Oneata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[Q] by accident run pupbld.sql by sys??
When I create database under 9iR2. supposely I should run following two SQL files by system, but I run it by sys: $ORACLE_HOME/sqlplus/admin/pupbld.sql $ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql my questions are: 1. do I need remove those objects from sys which create by those two sql files? 2. if I need how to do? 3. if NOT, can I just run those two SQL files under system? Thanks. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: fine grained access
Title: RE: fine grained access auditing triggers will do a fine job than FGA. Simpler ... easier .. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 9:21 AM To: Multiple recipients of list ORACLE-L Subject: fine grained access Hi list We have a dozen authorisation tables who are visibible for end-users and changeable for application owners. What our security officer wants is that he can see who changed something in those tables (including timestamp and update statement) Is FGA the solution for this ? Anyone with a few tips/hints how to do this with FGA ? thanks vr.gr. Geo Kor Sr. System Engineer IDM Db RDW Voertuiginformatie en -toelating Ict Bedrijf Holland * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: perl DBI/DBD: can I pass in an array as parameter?
As long as you are using bind variables,
the overhead of multiple executes should
not be very high.
It's all a matter of degree. On several trace files I've analyzed lately,
the whole response time problem was caused by thousands of 'SQL*Net message
from client' calls. They had nice little latencies (less than 0.010
seconds), but there are thousands of them. ...And one thousand 0.010-second
latencies adds up to 10 seconds.
The only way to get rid of this kind of response time problem is to get rid
of the thousands of 'SQL*Net message from client' calls. And because these
waits exist between adjacent database calls, the way to get rid of them is
to get rid of lots of parse, execute, and fetch calls. It's a good goal.
idea warning=I haven't confirmed the quality of this with anyone who's
decent at writing optimized SQLOne technique that hasn't been mentioned
yet: I think one way to reduce the number of executes without introducing
dynamic SQL (which can't be shared effectively among users) is to use an
inline view in the IN clause:
SELECT tab1.col1, tab2.col2
FROM tab1, tab2
WHERE tab1.ID1 = tab2.ID2
AND tab1.X IN (
SELECT codes FROM table123
)
/warning
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Sarnowski, Chris
Sent: Tuesday, July 01, 2003 6:05 PM
To: Multiple recipients of list ORACLE-L
Responses to 2 emails below:
Alex wrote:
not sure if this is what you want. one sql call
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?
and tab1.X = ?
and tab1.X = ?
;
@my_array = (1,2,3);
sth-execute(@my_array);
Errm, no rows will be returned. Think about this one a little more.
Steve Ollig wrote:
ok - that makes more sense. sorry for misinterpreting the
question. i've
never done it, but my first instinct would be to explore
using an in clause
in the query -
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (1, 2, 3)
can you simply pass an array to the prepared statement that
way? i'd try it
but don't have a sandbox with the DBI/DBD modules handy.
perhaps one of the great Perl gurus of the list will offer
some insight...
I'm not a Perl guru, but I can think of 2 solutions:
#build an array with the keys you want to look for:
my @my_array = (1, 2, 3);
# then add that many ?s to the query
#the 'in' solution:
my $query = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (' . join(',',('?') x @my_array) . ')';
# or the 'union' solution
my $subquery = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?';
my $query = join(' union ', ($subquery) x @my_array);
# pick only one of the above!
# and then
my $sth = $db-prepare($query);
$sth-execute(@my_array);
# then get the data back your favorite way: fetchall_arrayref, fetch_array,
etc
But is it really worth the trouble? As long as you are using bind variables,
the overhead of multiple executes should not be very high.
warning: these are typed from memory - I may have typos in the perl code.
But the concept should work.
-Chris
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be
privileged. It is intended for the addressee(s) only. Access to this e-mail
by anyone else is unauthorized. If you are not an addressee, any disclosure
or copying of the contents or any action taken (or not taken) in reliance on
it is unauthorized and may be unlawful. If you are not an addressee, please
inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary Millsap
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
OPatch -- Can not set up OUI inventory session ???
Hi, Anyone has experience with OPatch tool? While applying a patch using OPatch, I have error saying Can not set up OUI inventory session. Any idea? Thanks. lostdog:oradv2 5% opatch apply PERL5LIB=/oracle/DV2/920_64/Apache/perl/lib/5.00503; export PERL5LIB /oracle/DV2/920_64/Apache/perl/bin/perl /oracle/DV2/920_64/OPatch/opatch.pl apply OPatch Version 1.0.0.0.39 Perl Version 5.00503 Performing pre-patch installation checks. general_options is set to 0 Using oraInst.loc to look up oui libs... Parsing /oracle/ContentsXML/comps.xml Found oracle.swd.oui version 2.2.0.12.0 on /oracle/DV2/920_64/oracle.swd.oui Found JRE version 1.3.1.0.0a on /oracle/DV2/920_64/oracle.swd.jre/bin/java Oracle Home = /oracle/DV2/920_64 inventory_location = /oracle liboraInstaller_lib= /oracle/DV2/920_64/oracle.swd.oui/bin/solaris/libor aInstaller.so path_to_java = /oracle/DV2/920_64/oracle.swd.jre/bin/java path_to_oI_loc = /var/opt/oracle/oraInst.loc oui_component_loc = /oracle/DV2/920_64/oracle.swd.oui required_jar_file under oui = lib/OraInstaller.jar Checking if this is a RAC system... /oracle/DV2/920_64/OPatch/opatch.pl version: 1.0.0.0.39 Copyright (c) 2001,2002,2003 Oracle Corporation. All Rights Reserved. Can not set up OUI inventory session OPatch stops because of Inventory problem. lostdog:oradv2 6% exit Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Should percent increase higher than 0 in 817?
generally, but not always if the first thing someone tells you about themselves or the first justification for doing something is their years of experience, that person is probably a novice. alot of people assume that just because they have been doing something for a while they have been doing it well. They dont spend time reading documentation, they dont try out things that they have not always done, etc... I was just reading the first chapter of Kytes new book(there is a link on his page) and he has a series of examples of DBA pitfalls that include people like this. now when you get to th technical reason for the approach its usually weak, outdated, and founded on myths. If you even get to that level. alot of times they get insulted because they got questioned. Now this isnt always the case, but most of the time it is. and its tough to argue with them since they have more 'experience'. sounds like your Sr. DBA falls into that category. From: Gogala, Mladen [EMAIL PROTECTED] Date: 2003/07/02 Wed AM 11:49:14 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Should percent increase higher than 0 in 817? I acutally work with oracle ever since the version 4.1 (IBM PC/XT, 512KB RAM) but a) I've never worked with 4.0 b) I know very well what PCTINCREASE is. Conclusion: Tanel, it wasn't me! I swear! Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Microsoft VS Oracle (again)
Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Bug in Execute Immediate clause???
Hi Listers,
The below procedure gets created successfully in TEST
Schema. But when I execute the
procedure by starting a fresh session connecting as TEST schema I get the below
error and when I execute the procedure for the second time it executes
successfully. I have granted the dba privileges and explicit granted select on TEMP1 to TEST
Schema. Inspite
of that I am getting the below errors.
I tried this 8.1.7 and 9.2.1.0.
Is it a bug in the code or the database?
CREATE OR REPLACE procedure test authid
current_user is
c number;
n number;
a varchar2(1000);
Begin
Execute Immediate 'Alter Session Set Current_Schema
= SCOTT';
Dbms_output.put_line(sys_context('userenv', 'Current_schema'));
Select count(*) into c >From temp1;
Dbms_output.put_line(c);
End;
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at TEST.TEST, line 9
ORA-06512: at line 1
Can anybody help me out? Any help in this regard is very much
appreciated.
Thanks and Regards,
Ranganath
RE: Oracle DBA 9i Books ever needed.
The divination class does use a book called The Dream Oracle. :) -Original Message- Sent: Tuesday, July 01, 2003 7:21 PM To: Multiple recipients of list ORACLE-L Not a problem, Cary :) I was pleasently surprised to see Chris Lawson's new book at Borders. Did a fair amount of reading (3 chapters), while waiting for Harry Potter's book to go on sale by 12:01 AM on June 21! (also, wondered what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Chris has indeed done a very nice job. I should have bought the book that night. It's now on order. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: I'll second the recommendation of Christopher Lawson's new book. Very nice. grinBy not mentioning Gaja and Kirti's work explicitly in the first sentence should not be considered a withholding of endorsement. My opinion of their work is--hey!--right on the cover./grin Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Tuesday, July 01, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Rajuveera Are you seeking a book on DBA (administration) or performance tuning or real time database design? The more specific the question, the more likely the answer will match your needs. If you have some books, don't be shy about mentioning them so you don't just get recommendations that include books you already have. Since most of us collect books, if you say what you need that isn't included in your existing books, we can recommend books that cover different material. For performance tuning, a great book to start with is: Oracle 101 Performance Tuning by Jaga Krishna Vaidyanatha, Kirtikumar Deshpande, and John Kostelac. I have also been impressed with: The Art and Science of Oracle Performance Tuning by Christopher Lawson. This is very new, just published. Both of these books emphasize the fact that successful performance tuning begins with a correct philosophy. With a mistaken philosophy you can spend a lot of time and may even think you are being successful. There are two aspects to performance tuning, tuning the system and tuning your SQL. Which is more important will vary by your situation and as a competent DBA you need to understand both. For example, some packaged applications do not allow you to tune the SQL at all. But often tuning SQL statements can yield great gains in performance. As to the other topics you mention, perhaps it would be best if you reply with more specifics as to your situation. Sitting here in the U.S., it is rather difficult for me to tell what books are available to you in Hyderabad. Can you buy books from www.amazon.com? Getting a book that applies to both 8i and 9i is easy, in my experience. When a new Oracle version arrives, publishers often brush up an existing book with some new information so they can place the new version number on the book. It takes awhile for authors to absorb the impact that the changes can have on the daily work of a DBA and produce a book that incorporates these changes. I'm just trying to accurately represent the situation, not being critical. Fortunately when Oracle creates a new version, 99% of the features already there still work. Just familiarize yourself with the main feature differences between versions and you'll be fine with any book. I will be on vacation for several days, so if you are sleeping when this reply is posted, I will be unable to reply. However, there are others on this list that can provide far more competent assistance than I, even including some book authors. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:40 AM To: Multiple recipients of list ORACLE-L Dear Friends, I am willing to buy Oracle DBA 9i books. ,I need your suggestion on this. I got information friends from this list that the book (Practical Oracle 8i - Jonathan Lewis.) is practically good. Even I am also looking for the same kind of book, But I want it for both 8i and 9i. It should be more practical oriented , performance tuning and real time database design and problems. The book should be more practical oriented . What abt performance tuning 101 techniques , Oracle 9i DBA handbook. What are the best books for Oracle DBA (Intermediate level ) available in INDIA , HYDERABAD. Any URL / best sites also will be helpful. Thanks Rajuveera __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services--
Re: Microsoft VS Oracle (again)
How do you go about finding MS SQL Server on MS Advanced Server? We have quite few Win2k/NT servers around here: no mention of MS SQL in the register, and it's not on the program menu. Jared On Tuesday 01 July 2003 13:10, Goulet, Dick wrote: Well, I'll be a little more forgiving than Raj has been. Basically MicroSoft is just the pot calling the kettle black. On the issue of price, well yes MicroSoft does have an edge, but that's just how they've eliminated all of their other competition over the years. The product does not cost less than Oracle or DB2, it's just that MicroSoft can leverage the tremendous amounts of cash they have to offset the giveaway they're into. I believe that was one of the points of the Antitrust suit they were embroiled in. On RAC they really have a long way to go. If you want to do a federated database setup like MicroSoft states, just buy standard edition Oracle licenses toss in a pile of database links. Works the same way you don't have to pay extra for it. The whole idea of RAC is that when one node dies, for whatever reason, the data that node was hosting is not offline till you get it repaired. And actually you really do not need to do anything to your application to take advantage of RAC, except adjust your error handling to understand that a transaction needed to be resubmitted. Overall I think MicroSoft's Achilles heel is Windows. Their a one OS horse. Oracle is a multi OS horse that runs the same whatever the platform. And BTW, Oracle runs RAC on Red Hat without any additional software, never mind that it runs on Linux in the first place. Similar things can also be said for DB2, Sybase, and Informix. When, if ever, Microsoft has a version of Sql*Server that runs as multiple independent processes on Linux then I'll give them a second look. As long as their a single multi-threaded Windows only process they can stay in Redmond. Also, yes they are the fastest growing database on the market, take a look at that copy of Windows 2000 Advanced Server you've got. There's a copy of Sql*Server 2000 in there, and you can't uninstall it. Billy G is still up to his old tricks. The only solution to MicroSoft is forced divestiture. Too bad that judge could not stand up to King George. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Aargh ... you must be very brave telling a Oracle cult to move to SQL Server ... How do we know you are not Billy G using an alias ... 8) Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automate an update
Do window_open's values depend on the time the record is inserted, or the time it is retrieved? If the former, you could do it in a trigger. If the latter, you could make window_open a calculated column in a view. What are you using for a user interface--oracle forms, sql*plus, java, vb...? HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 02, 2003 2:41 AM To: Multiple recipients of list ORACLE-L Hi all, I need help in order to create a following mechanism. I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle DBA 9i Books ever needed.
what would happen if Hogwarts taught Oracle Tuning using wands spells ;) Isn't that how Oracle Education does it? Alomahorabuffercachehitratio! Gudmundur Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Bug in Execute Immediate clause???
Are we forgetting that your current schema is SCOTT
??
comment that line, and then try running the procedure
...
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
are strictly personal. QOTD: Any clod
can have facts, having an opinion is an art !
-Original Message-From: Ranganath K
[mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003
11:31 AMTo: Multiple recipients of list ORACLE-LSubject:
Bug in Execute Immediate clause???
Hi Listers,
The below procedure gets created
successfully in TEST Schema. But
when I execute the procedure by starting a fresh session connecting as TEST
schema I get the below error and when I execute the procedure for the second
time it executes successfully. I
have granted the dba privileges and explicit granted
select on TEMP1 to TEST Schema.
Inspite of that I am getting the below
errors. I tried this 8.1.7 and
9.2.1.0. Is it a bug in the code
or the database?
CREATE OR REPLACE procedure test
authid current_user
is
c
number;
n
number;
a
varchar2(1000);
Begin
Execute Immediate 'Alter Session
Set Current_Schema =
SCOTT';
Dbms_output.put_line(sys_context('userenv', 'Current_schema'));
Select count(*) into c From temp1;
Dbms_output.put_line(c);
End;
ERROR at line
1:
ORA-00942: table or view does not
exist
ORA-06512: at "TEST.TEST", line
9
ORA-06512: at line
1
Can anybody help me out? Any help in this regard is very much
appreciated.
Thanks and
Regards,
Ranganath
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
Re: fine grained access
Fine grained access control doesn't tell you who did what, it restricts users from doing certain things. If you want to just capture the changes, not the actual update statement, you can do this via triggers which insert into a copy of the table, with the additional columns of timestamp and user --- [EMAIL PROTECTED] wrote: Hi list We have a dozen authorisation tables who are visibible for end-users and changeable for application owners. What our security officer wants is that he can see who changed something in those tables (including timestamp and update statement) Is FGA the solution for this ? Anyone with a few tips/hints how to do this with FGA ? thanks vr.gr. Geo Kor Sr. System Engineer IDM Db RDW Voertuiginformatie en -toelating Ict Bedrijf Holland * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Online tech books
Monday, June 30, 2003, 10:39:40 AM, you wrote: BM I just returned frm vacation, but I wanted to respond to Jonathan BM Gennick regarding my collection of books.( I managed to delete that BM message) But, Ive taken some pictures of my collection.. Ive actually BM purchaced all the books you see! BM http://162.42.213.232/books/index.html Wow! Bob, I think you have me beat, and I'm no slouch when it comes to ordering books. Hey, you know what else, as I look very closely at your bookcases, I think you use the same cheap, partical-board-covered-with-paper, bendable-shelf brand that I usegrin. I better get on the stick and order more booksgrin. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OPatch -- Can not set up OUI inventory session ???
Is it possible that other oracle installations have been done with /oracle as inventory location? Is there another inventory location specifically for your 920_64 installation? We have had similar issues with Opatch and as a consequence, we are now putting inventory location inside ORACLE_HOME. In your case, that would be /oracle/DV2/920_64/oraInventory. You then have to point the oraInst.loc to that location. In this way, provided that the oraInst.loc file is correct, your inventories will not collide or get clobbered. HTH, Gerardo -Original Message- Sent: Wednesday, July 02, 2003 8:26 AM To: Multiple recipients of list ORACLE-L Hi, Anyone has experience with OPatch tool? While applying a patch using OPatch, I have error saying Can not set up OUI inventory session. Any idea? Thanks. lostdog:oradv2 5% opatch apply PERL5LIB=/oracle/DV2/920_64/Apache/perl/lib/5.00503; export PERL5LIB /oracle/DV2/920_64/Apache/perl/bin/perl /oracle/DV2/920_64/OPatch/opatch.pl apply OPatch Version 1.0.0.0.39 Perl Version 5.00503 Performing pre-patch installation checks. general_options is set to 0 Using oraInst.loc to look up oui libs... Parsing /oracle/ContentsXML/comps.xml Found oracle.swd.oui version 2.2.0.12.0 on /oracle/DV2/920_64/oracle.swd.oui Found JRE version 1.3.1.0.0a on /oracle/DV2/920_64/oracle.swd.jre/bin/java Oracle Home = /oracle/DV2/920_64 inventory_location = /oracle liboraInstaller_lib= /oracle/DV2/920_64/oracle.swd.oui/bin/solaris/libor aInstaller.so path_to_java = /oracle/DV2/920_64/oracle.swd.jre/bin/java path_to_oI_loc = /var/opt/oracle/oraInst.loc oui_component_loc = /oracle/DV2/920_64/oracle.swd.oui required_jar_file under oui = lib/OraInstaller.jar Checking if this is a RAC system... /oracle/DV2/920_64/OPatch/opatch.pl version: 1.0.0.0.39 Copyright (c) 2001,2002,2003 Oracle Corporation. All Rights Reserved. Can not set up OUI inventory session OPatch stops because of Inventory problem. lostdog:oradv2 6% exit Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: fine grained access
FGA/VPD/RLS(*) is not a candidate solution to your problem. FGA via RLS is more for access control (who has permissions to see the records) rather than keeping track of who has exercised their access rights. Oracle's auditing may not provide the level of detail that you want, so your best bet is to write your own insert and update triggers, and possibly delete triggers that inserts into some other table too if you want to know who deleted what record when. (*) VPD=Virtual Private Database; RLS=Row Level Security. Basically the same stuff based on which Oracle blurb you happen to have read. -Original Message- Sent: Wednesday, July 02, 2003 9:21 AM To: Multiple recipients of list ORACLE-L Hi list We have a dozen authorisation tables who are visibible for end-users and changeable for application owners. What our security officer wants is that he can see who changed something in those tables (including timestamp and update statement) Is FGA the solution for this ? Anyone with a few tips/hints how to do this with FGA ? thanks vr.gr. Geo Kor Sr. System Engineer IDM Db RDW Voertuiginformatie en -toelating Ict Bedrijf Holland * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: error reading file
yeah, I see that error. really sucks. -Original Message-From: Shishir Kumar Mishra [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003 6:16 AMTo: Multiple recipients of list ORACLE-LSubject: error reading file
RE: CASE in PL/SQL
If your Jul is 1st quarter, then your offset is should be 6 months instead of the 9 months in your email; or think of it another way, if you Jan is the beginning of the 3rd quarter, it is the beginning of the 2nd half of the year, and half a year is 6 months. With this in mind, you really don't need any decodes at all for just finding out your financial quarter. The following query suffices: select to_char(add_months(sysdate, 6), 'Q') from user_users; My best interpretation of your second query with the if-then is that if a date is in the second half of the calendar, you want to push it to the following year (or perhaps if the calendar date is from second half of last year, you want it reported as being in this year, which is kind of like saying fiscal year ending ) So, here's your solution (not exactly fast, but it's implemented competely with numeric functions provided by Oracle): Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q')) To figure out how much correction you need to each year based on the quarter the year appears in, add the following to the year: sign((sign(3 - Q) + 1) * sign(3 - Q)) -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automate an update
Joshua, How about this: CREATE OR REPLACE TRIGGER SCOTT.WINDOW_OPEN_TR BEFORE INSERT ON SCOTT.ORDER_TYPE FOR EACH ROW DECLARE l_compare_time NUMBER; BEGIN SELECT to_char(sysdate,'HH24') into l_compare_time FROM dual; IF l_compare_time between '8' and '16' THEN :NEW.window_open := 'Y'; ELSE :NEW.window_open := 'N'; END IF; END; John Green SSi Technologies, North America -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joshua Becker Sent: Wednesday, July 02, 2003 4:41 AM To: Multiple recipients of list ORACLE-L Subject: Automate an update Hi all, I need help in order to create a following mechanism. I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express
RE: Should percent increase higher than 0 in 817?
After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Microsoft VS Oracle (again)
there are a couple of finer points that are left out... There are really two versions of TAF that they are talking about here... 1) Session Failover -- it's easy to do, just rebuild the TNSNAMES.ORA file on the client machine, and create a backup connection. If the connection fails to connect to the primary, it will retry it on the secondary, after XX number of seconds (and a couple other options as well.) The client has no idea that it even reconnected. This works with about 99% of applications written with OCI8. However, since all it does is reconnect the user, any in process transactions are lost, and the user does not know it until they try and commit, and then they select that data back, and only half of it is there. It's a risky solution, but works GREAT for demos. 2) Session Failover and reprocessing of in process transactions - This method actually replays any in process activities on the secondary node, and then allows the user to continue on as if nothing happened. This is one way not to have perceived data corruption. But it does require extensive modification to the OCI connection layer so that the Client product is 'TAF aware' And it means the client software must record all the uncommitted activity that a session does, so that when oracle fails it to the other machine, it knows to replay that activity before giving any response back to the user. This works today in SQL*Plus without any modification (try it it's pretty cool) but will require HUGE amounts of code changes to any other app to get it to work. (i.e. try it with Oracle forms, or People Soft clients -- no chance it will work.) so, the Microsoft is right and wrong at the same time... odd how they do that so well. Nick -Original Message- Sent: Wednesday, July 02, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nick Wagner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automate an update
why not create a view like this ...
SELECT 'Y' AS FLAG
FROMDUAL
WHERE TO_CHAR(SYSDATE,'HH24MI') BETWEEN '0800' AND '1600'
UNION ALL
SELECT 'N' AS FLAG
FROMDUAL
WHERE TO_CHAR(SYSDATE,'HH24MI') NOT BETWEEN '0800' AND '1600';
that would have the flag on and off without an update ... and you can other
tables with
this view to implement the actual logic ...
HTH
-Original Message-
Sent: Wednesday, July 02, 2003 5:41 AM
To: Multiple recipients of list ORACLE-L
Hi all,
I need help in order to create a following mechanism.
I have a table where is a column called window_open and it has two values
'Y' and 'N'
Now I need to automate the update a single row based on following rules:
If time is between 08:00-16:00 the value on that window_open column should
be 'Y' during other period the value should be 'N'. How can I do this and
automate the task...
Thanks in advance,
Joshua
Gå före i kön och få din sajt värderad på nolltid med Yahoo!
http://se.docs.yahoo.com/info/express/help/index.html Express
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: error reading file
I see the problem. Looks like your file is empty. ;o) Dave -Original Message-From: Shishir Kumar Mishra [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003 5:16 AMTo: Multiple recipients of list ORACLE-LSubject: error reading file
RE: Automate an update
Title: RE: Automate an update select case when to_number(to_char(sysdate,'HH24')) between 8 and 16 then 'Window Open' else 'Window Closed' end from your table / Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, July 02, 2003 2:41 AM To: Multiple recipients of list ORACLE-L Hi all, I need help in order to create a following mechanism. I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Should percent increase higher than 0 in 817?
That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Do Not Call
Yeah, it's an asp.net app (you can tell from the .aspx file extension on the URLs). But the db could be anything... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 9:16 AM To: Multiple recipients of list ORACLE-L snip http://uptime.netcraft.com/up/graph/?host=www.donotcall.gov Well, if it were unix then it couldn't be SQLserver. It is Windows, therefore I assume its SQLserver, but it could be Oracle. But who runs Oracle on Windows anyway *hahahaha* ... We do ... groan -- Steve Wolfe [EMAIL PROTECTED] 07/01/03 02:29PM Paradox for DOS -Original Message- Sent: Tuesday, July 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L There have been over 10,000,000 entries made in the National Do Not Call Registry since Friday June 27. Does anyone know the database engine in which this is stored? Curious in Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
LISTENER ON LINUX
Hi, When I am trying to start listener on Linux box ,getting error TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use I checked no process fro listener and no port used by another process. Let me know how to fix that pl thx -Seema _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: Online tech books
Bless you Bob ! I will use your pictures to settle the argument of too many books with my wife! I told her I reduce my number of books as long as she reduces the number of shoes. She got a bit offensive. Luis -Original Message- Sent: Wednesday, July 02, 2003 10:11 AM To: Multiple recipients of list ORACLE-L Monday, June 30, 2003, 10:39:40 AM, you wrote: BM I just returned frm vacation, but I wanted to respond to Jonathan BM Gennick regarding my collection of books.( I managed to delete that BM message) But, Ive taken some pictures of my collection.. Ive actually BM purchaced all the books you see! BM http://162.42.213.232/books/index.html Wow! Bob, I think you have me beat, and I'm no slouch when it comes to ordering books. Hey, you know what else, as I look very closely at your bookcases, I think you use the same cheap, partical-board-covered-with-paper, bendable-shelf brand that I usegrin. I better get on the stick and order more booksgrin. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Filesystem for Linux production database server?
Reiserfs' biggest strength is in its ability to deal with directories with huge numbers of files in a very speedy fashion. Obviously, for oracle this is less relevant. As far as the max-readahead option, I don't understand the specific relevance to fibre-attached storage. I would imagine, though admittedly I no longer have large storage arrays to play with, that setting readahead to be higher could damage performance against arrays with intelligent caching algorithms. Having the OS handle read-ahead rather than the array will likely fool the array into thinking that the i/o patterns are more sequential than they are. This will cause them to pre-allocate cache regions and pre-fetch more tracks off disk, which could adversely impact performance. Totally separate from that, Redhat strongly advises _against_ tuning that parameter. I happen to be doing I/O testing right now anyway, so maybe I'll gen up some workloads on different filesystems and go nuts. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Craig I. Hagan Sent: Wednesday, July 02, 2003 4:11 AM To: Multiple recipients of list ORACLE-L Subject: Re: Filesystem for Linux production database server? Linux file systems usually do not support direct I/O (bypassing the buffer cache), which means that you're going to have double caching with almost This is no longer the case. Look at the O_DIRECT open option, which can be used with oracle. Make sure that your distribution has support for it. snip One other thing: if you are using fibre attached storage and are *not* using async io, then put some usefully large value into /proc/sys/vm/max-readahead, remember it is power of 2 minus one, so youd want to look at values like 255, 511, and 1023. As for availability, you'll have to go with some RAID controller and standby database or RAC. In case of RAC, your choice of file systems is clear (OCFS). Whatever you do, do not configure your RAID as RAID-5 but RAID 1+0 (BARF). what he said, save that i like raid10. HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Should percent increase higher than 0 in 817?
Kirti, I will kinda agree with your Very Senior DBA. Make TEMP an LMT with uniform extents, of type temp and with a tempfile your most likely not to have a problem there that will have any lasting effect. It's one of those things that you have to accept end user complaints on to determine if there has been a problem, otherwise the problem clears as fast as it happens. Now RollBack can get top be a problem if when you run out of space due to some LONG running transaction that should have been killed 2 days ago. Consequently I watch rollback. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, July 02, 2003 2:16 PM To: Multiple recipients of list ORACLE-L After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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[2]: Online tech books
Title: RE: Re[2]: Online tech books Good Lord, you can NEVER have too many books! April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need. ~ Jerry Gillies ~ -Original Message- From: Luis deUrioste [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 1:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re[2]: Online tech books Bless you Bob ! I will use your pictures to settle the argument of too many books with my wife! I told her I reduce my number of books as long as she reduces the number of shoes. She got a bit offensive. Luis -Original Message- Sent: Wednesday, July 02, 2003 10:11 AM To: Multiple recipients of list ORACLE-L Monday, June 30, 2003, 10:39:40 AM, you wrote: BM I just returned frm vacation, but I wanted to respond to Jonathan BM Gennick regarding my collection of books.( I managed to delete that BM message) But, Ive taken some pictures of my collection.. Ive actually BM purchaced all the books you see! BM http://162.42.213.232/books/index.html Wow! Bob, I think you have me beat, and I'm no slouch when it comes to ordering books. Hey, you know what else, as I look very closely at your bookcases, I think you use the same cheap, partical-board-covered-with-paper, bendable-shelf brand that I usegrin. I better get on the stick and order more booksgrin. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
Logical standby?
OK, I'm going to aim a little lower: is _anyone_ using Logical Standby? Any tips, gotchas, implementation accounts to share? TIA = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
service name, sid ..
I am reading oracle network admin guide and getting confused abt service name, instance name , db name , sid .. why service name is not same as db name. Earlier service name and sid used to be same thing .. isn't it ( ? ) . Can some one clarify with some examples . TIA -ak
RE: Microsoft VS Oracle (again)
-Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: July 2, 2003 11:31 AM To: Multiple recipients of list ORACLE-L Subject: Re: Microsoft VS Oracle (again) How do you go about finding MS SQL Server on MS Advanced Server? We have quite few Win2k/NT servers around here: no mention of MS SQL in the register, and it's not on the program menu. Jared It comes with Small Business Server. That's another name for M$ Exchange, M$ SQL, modem and fax sharing software running atop of Win2K server all bundled together and wrapped in gazillion wizards. All geared to fit needs of small business owners who have better things to do in their lives (than worry their (pretty?) heads with sordid details of what's been showed down their eager throats and why)... Branimir -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Branimir Petrovic INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Redo Copy Latch contention ??
According that suggestion you do seem to have redo copy latch contention.
As far as getting that ratio close to suggested value, you may set some special
init.ora
parameters. There is plenty of notes on Metalink for that.
But, you should first determine if this is causing any performance issue. Have you
explored all
other avenues to address those issues. If not, I would not worry about this
contention.
- Kirti
--- Reddy, Madhusudana [EMAIL PROTECTED] wrote:
Hello ALL,
Do you guys think we have redo copy latch contention ?? Also what are your
suggestions on tuning
Redo Copy Latch ??
SUBSTR(LN.NAME,1,20)GETS
MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
--
-- --
redo allocation
943350646 8862115 0 0
redo copy
22097 497 907958724 1592481
14:54:54 SQL select (497/22097)*100 from dual;
(497/22097)*100 ~ (misses/gets)*100
---
2.2491741 --- Oracle suggests it
should be under 1%
Madhu Reddy
X13944
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
www.fatcity.com
Guys... I need to find some past threads about security schemas from develop to production schemas... How can I find them ? Do I need to register at www.fatcity.com? May be some of you can help me with ideas about the topics that I should take into account when we develop our security schemas... (our developers are in the process of deploying an integrated medical/hospital application). for ex: managing roles and privileges, synonyms from the source schema to an empty schema, auditing operations, etc... etc... Do any of you have something similar or can give me ideas? TIA JL __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: LISTENER ON LINUX
Sounds like a duplicated IP or duplicated Service name. Luis -Original Message- Sent: Wednesday, July 02, 2003 12:36 PM To: Multiple recipients of list ORACLE-L Hi, When I am trying to start listener on Linux box ,getting error TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use I checked no process fro listener and no port used by another process. Let me know how to fix that pl thx -Seema _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: service name, sid ..
AK, Let me put it this way, from our own configurations: Specs.world is an alias(servicename) for database(instance name/db name) BART3 which resides on host BART in SID 03. Does that help?? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: service name, sid .. I am reading oracle network admin guide and getting confused abt service name, instance name , db name , sid .. why service name is not same as db name. Earlier service name and sid used to be same thing .. isn't it ( ? ) . Can some one clarify with some examples . TIA -ak
RE: LISTENER ON LINUX
What is the port? How did you check that it wasn't used? Which distro, which version of the database? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 02, 2003 2:36 PM To: Multiple recipients of list ORACLE-L Hi, When I am trying to start listener on Linux box ,getting error TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use I checked no process fro listener and no port used by another process. Let me know how to fix that pl thx -Seema _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Logical standby?
one gotcha -- logical standby is based on logminer techniques. so anything that logminer can't handle (and there is a bunch, well documented), logical standby can't handle --- Paul Baumgartel [EMAIL PROTECTED] wrote: OK, I'm going to aim a little lower: is _anyone_ using Logical Standby? Any tips, gotchas, implementation accounts to share? TIA = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Microsoft VS Oracle (again)
Rich, So, what's the case for code changes? TAF (Transparent Application Failover) will provide both SESSION failover as well as SELECT failover. In the former case, the session aborts on the now-failed server and starts from the beginning on the new node, while the latter enables user with open cursors to continue fetching on them after failure by re-executing the cursors on the new node. The former does not require code changes, while the latter does, and requires code changes done on a limited number of executable environments (JDBC Thin and OCI come to mind) that support 'TAF Callback'. TAF *with RAC* will provide the environment for a clustered environment where the user can failover from one node to another node accessing the same data (as compared to TAF in a replicated environment). Hope this answers your question. Murali Vallath [are you listening in, Murali?] may be able to add some details [Hint!] John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Microsoft VS Oracle (again) Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Microsoft VS Oracle (again)
Interesting. For some reason, the term transparent failover sticks in my head. Then again, I was remembering incorrectly. The Oracle Workload Generator demo was for load-balanced queries between the two nodes of the RAC. The failover was a SQL statement run from SQL*Plus, which probably comes TAF aware or can be made to with a simple relinking. Thanks for the clarification, Nick! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Nick Wagner [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:41 PM To: Multiple recipients of list ORACLE-L Subject: RE: Microsoft VS Oracle (again) there are a couple of finer points that are left out... There are really two versions of TAF that they are talking about here... 1) Session Failover -- it's easy to do, just rebuild the TNSNAMES.ORA file on the client machine, and create a backup connection. If the connection fails to connect to the primary, it will retry it on the secondary, after XX number of seconds (and a couple other options as well.) The client has no idea that it even reconnected. This works with about 99% of applications written with OCI8. However, since all it does is reconnect the user, any in process transactions are lost, and the user does not know it until they try and commit, and then they select that data back, and only half of it is there. It's a risky solution, but works GREAT for demos. 2) Session Failover and reprocessing of in process transactions - This method actually replays any in process activities on the secondary node, and then allows the user to continue on as if nothing happened. This is one way not to have perceived data corruption. But it does require extensive modification to the OCI connection layer so that the Client product is 'TAF aware' And it means the client software must record all the uncommitted activity that a session does, so that when oracle fails it to the other machine, it knows to replay that activity before giving any response back to the user. This works today in SQL*Plus without any modification (try it it's pretty cool) but will require HUGE amounts of code changes to any other app to get it to work. (i.e. try it with Oracle forms, or People Soft clients -- no chance it will work.) so, the Microsoft is right and wrong at the same time... odd how they do that so well. Nick -Original Message- Sent: Wednesday, July 02, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What are the restrictions in naming an Oracle SID/Database?
Jared wrote Which reminds me, I somehow missed that when cloning a SAP db last weekend. Guess I'll go fix it now. Surely one of those things is more than enough :( Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DataGuard Logical Standby Benchmark
I don't have any great answers other than there is good docs on MetaLink. However, one word of warning. We have been attempting to install DataGuard here and have encountered bugs. We have open bugs for release 9.2.0.1, 9.2.0.2 and 9.2.0.3. The bugs are supposed to be fixed in 9.2.0.4, but then again, they were supposed to have been fixed in 9.2.0.3 too. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -Original Message- Sent: Tuesday, July 01, 2003 12:19 AM To: Multiple recipients of list ORACLE-L We are Looking to do an In-house small Benchmark on DataGuard in it's various modes Logical Standby Database Oracle 9.2 Solaris 8 Any experiences of people , approach methodology , dos don'ts , Good Docs , Links ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Raid 0+1 vs. mirrored pairs
Title: Raid 0+1 vs. mirrored pairs Hi Everyone, I guess I am stuck in the old myth which says one giant raid array for everything is bad. We have been told Windows 2000 server is what we will now run Oracle on. Setting aside the debate putting of Oracle on a Windows box, I am currently in discussions about how said server will be spec'ed out. So far we have agreed on everything except the disc drives. Our 2000 admin says taking six 36g drives and making a raid 0+1 out of them (108 raid 0, mirrored) is the fastest and absolute best way for this server to be setup. I like the idea of having sets of mirrored pairs. That way I can separate tables, indexes, redo logs, rollbacks, etc. Is my admin right? Are raid 0+1 setups the best of the best? Better than sets of mirrored pairs? Thank you in advance. Chris
RE: Re[2]: Online tech books
Must say that I approve of your set of books :-)) Robert Freeman Author of at least two books on your bookshelves! -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/2/2003 1:26 PM Bless you Bob ! I will use your pictures to settle the argument of too many books with my wife! I told her I reduce my number of books as long as she reduces the number of shoes. She got a bit offensive. Luis -Original Message- Sent: Wednesday, July 02, 2003 10:11 AM To: Multiple recipients of list ORACLE-L Monday, June 30, 2003, 10:39:40 AM, you wrote: BM I just returned frm vacation, but I wanted to respond to Jonathan BM Gennick regarding my collection of books.( I managed to delete that BM message) But, Ive taken some pictures of my collection.. Ive actually BM purchaced all the books you see! BM http://162.42.213.232/books/index.html Wow! Bob, I think you have me beat, and I'm no slouch when it comes to ordering books. Hey, you know what else, as I look very closely at your bookcases, I think you use the same cheap, partical-board-covered-with-paper, bendable-shelf brand that I usegrin. I better get on the stick and order more booksgrin. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Should percent increase higher than 0 in 817?
What are the things that we should be monitoring in ROLLBACK or TEMP tablespace? So far I don't have any script to monitor ROLLBACK or TEMP (havn't had any problem though). I too thought Oracle would take care of rollback and temp ts space management. Right or wrong? Guang -Original Message- Kirtikumar Deshpande Sent: Wednesday, July 02, 2003 2:16 PM To: Multiple recipients of list ORACLE-L After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CASE in PL/SQL
Hi Rudy, Thanks for your suggestion. But I got a better suggestion from Metalink. Here what I was suggested: SQL SELECT (MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1) qtr 2 , DECODE(SIGN(TO_NUMBER(TO_CHAR(main_rec.termination_date,'Q')) - 3) 3 , -1, TO_NUMBER(TO_CHAR(main_rec.termination_date,'')) 4 , TO_NUMBER(TO_CHAR(main_rec.termination_date,'')) + 1) yr 5 ... 6 Thanks for your help. Surendra -Original Message- Sent: Wednesday, July 02, 2003 1:51 PM To: Multiple recipients of list ORACLE-L If your Jul is 1st quarter, then your offset is should be 6 months instead of the 9 months in your email; or think of it another way, if you Jan is the beginning of the 3rd quarter, it is the beginning of the 2nd half of the year, and half a year is 6 months. With this in mind, you really don't need any decodes at all for just finding out your financial quarter. The following query suffices: select to_char(add_months(sysdate, 6), 'Q') from user_users; My best interpretation of your second query with the if-then is that if a date is in the second half of the calendar, you want to push it to the following year (or perhaps if the calendar date is from second half of last year, you want it reported as being in this year, which is kind of like saying fiscal year ending ) So, here's your solution (not exactly fast, but it's implemented competely with numeric functions provided by Oracle): Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q')) To figure out how much correction you need to each year based on the quarter the year appears in, add the following to the year: sign((sign(3 - Q) + 1) * sign(3 - Q)) -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send
RE: service name, sid ..
Example using Transparent Application Failover (TAF) If I didn't forget something, here's what it looks like: XXTP_QUOTE.WORLD = (DESCRIPTION= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_QUOTE2.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=180) (DELAY=1) ) ) ) XXTP_QUOTE2.WORLD = (DESCRIPTION= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1527)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_QUOTE.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=50) (DELAY=1) ) ) ) XXTP_SHOP.WORLD = (DESCRIPTION= (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1526)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1526)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_SHOP2.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=180) (DELAY=1) ) ) ) XXTP_SHOP2.WORLD = (DESCRIPTION= (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1526)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1526)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_SHOP.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=50) (DELAY=1) ) ) ) XXTP_UPDATE.WORLD = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (SERVER=DEDICATED) (FAILOVER_MODE= (TYPE=NONE) ) ) ) In the init.ora for one database ... db_domain = world instance_name = XXTP1 service_names = XXTP In the init.ora for the other database db_domain = world instance_name = XXTP2 service_names = XXTP -Original Message- why service name is not same as db name . Earlier service name and sid used to be same thing .. isn't it ( ? ) . Can some one clarify with some examples . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: fine grained access
Hi Arup, LogMiner is fine for certain tasks but not for auditing everything, it has some deficiencies such as it cannot be used in an MTS environment as it uses PGA memory, it doesn't fully support chained and migrated rows (fixed in 9i), doesn't support selects (as they are not recorded in the redo prior to 9i), doesn't fully support objects of analysis of IOT's or clustered tables. But I do agree with you that the best solution is to use regular audit or normal user triggers. If the poster wants to use Fine Grained audit then there are a few links to some good documents on my site http://www.petefinnigan.com/orasec.htm that cover FGA. kind regards Pete However, FGA is bit of an overkill in your case. It's typically the only solution for auditing the seelct statements. For changes (insert/update/delete), you could employ the regular auditing (AUDIT). that will tell you who changed something, but not what. To see the what, you could use log miner to unearth those statements with the data. -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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[2]: Online tech books
Title: RE: Re[2]: Online tech books Well Robert, you're the author of two books on my shelf as well. Oracle 9i:New Features and Oracle9i:RMAN backup Recovery. -Original Message- From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 2:41 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re[2]: Online tech books Must say that I approve of your set of books :-)) Robert Freeman Author of at least two books on your bookshelves! -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/2/2003 1:26 PM Bless you Bob ! I will use your pictures to settle the argument of too many books with my wife! I told her I reduce my number of books as long as she reduces the number of shoes. She got a bit offensive. Luis -Original Message- Sent: Wednesday, July 02, 2003 10:11 AM To: Multiple recipients of list ORACLE-L Monday, June 30, 2003, 10:39:40 AM, you wrote: BM I just returned frm vacation, but I wanted to respond to Jonathan BM Gennick regarding my collection of books.( I managed to delete that BM message) But, Ive taken some pictures of my collection.. Ive actually BM purchaced all the books you see! BM http://162.42.213.232/books/index.html Wow! Bob, I think you have me beat, and I'm no slouch when it comes to ordering books. Hey, you know what else, as I look very closely at your bookcases, I think you use the same cheap, partical-board-covered-with-paper, bendable-shelf brand that I usegrin. I better get on the stick and order more booksgrin. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Luis deUrioste INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CASE in PL/SQL
Hi George, With this solution you can only find Quarter number. but I also need Year of that quarter number. when you say to_char(date,'Q'), it might go next year or stay in current fiscal year depending on number of months we add to the date. Thanks, Surendra -Original Message- Sent: Wednesday, July 02, 2003 12:56 PM To: Multiple recipients of list ORACLE-L You don't need CASE. try : to_char( date , 'Q') George Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: George Oneata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Redo Copy Latch contention ??
Thanks Kirti,
We have HP Openview implemented on our database and hence got some alert on redo copy
latch. When I have queried the database I found the contention on this latch. Yes we
have other performance issues ( HIGH CPU utilization , because of lotta bad code ).
and We are checking every possible contention on the database.
Question : How can I determine if this redo copy latch is causing the performance
issues , guess that is my main question before altering some hidden parameter in
init.ora.
Madhu Reddy
X13944
-Original Message-
Sent: Wednesday, July 02, 2003 1:56 PM
To: Multiple recipients of list ORACLE-L
According that suggestion you do seem to have redo copy latch contention.
As far as getting that ratio close to suggested value, you may set some special
init.ora
parameters. There is plenty of notes on Metalink for that.
But, you should first determine if this is causing any performance issue. Have you
explored all
other avenues to address those issues. If not, I would not worry about this
contention.
- Kirti
--- Reddy, Madhusudana [EMAIL PROTECTED] wrote:
Hello ALL,
Do you guys think we have redo copy latch contention ?? Also what are your
suggestions on tuning
Redo Copy Latch ??
SUBSTR(LN.NAME,1,20)GETS
MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
--
-- --
redo allocation
943350646 8862115 0 0
redo copy
22097 497 907958724 1592481
14:54:54 SQL select (497/22097)*100 from dual;
(497/22097)*100 ~ (misses/gets)*100
---
2.2491741 --- Oracle suggests it
should be under 1%
Madhu Reddy
X13944
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Logical standby?
Paul Baumgartel wrote: OK, I'm going to aim a little lower: is _anyone_ using Logical Standby? Any tips, gotchas, implementation accounts to share? TIA = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] Paul, I am not using it but sometimes testing it. One gotcha : RENAME messes up everything (doesn't travel). Monitoring what happens (or doesn't) is extremely difficult. And if you don't automate the 22 step setup ... Also : don't use the doc, totally bug-ridden, but the Metalink papers. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrade 8.1.7.3.0 to 8.1.7.4.1 on W2K
A quick scan of it looks ok. Not sure about copying the password file. Too easy to create it to bother I think. BTW, export the Oracle registry key to a file first. It comes in useful on occasion. :) Jared Jack van Zanen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/02/2003 01:10 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Upgrade 8.1.7.3.0 to 8.1.7.4.1 on W2K I have a few databases in Home0 and a few in Home1 and need to move one from Home0 to Home1 So if I understand correct I can do: Oradim -delete -sid SID Remove the entries that look like ORA_SID_AUTOSTART ORA_SID_SHUTDOWN ORA_SID_SHUTDOWNTYPE ORA_SID_SHUTDOWN_TIMEOUT From Home0(if not done by oradim) Put the initSID.ora file in the new home location Create a new service NEW_HOME\bin\oradim.exe -new -sid SID -startmode m NEW_HOME\bin\oradim.exe -edit -sid SID -startmode a NEW_HOME\bin\orapwd.exe file=d:\oracle\ora90\database\PWDSID.ora password=password (Or can I just copy the password file?) Change the listener.ora Start the database Run catalog/catproc/catexp etc Is this Correct??? TIA Jack -Original Message- Sent: Tuesday, July 01, 2003 11:07 PM To: Multiple recipients of list ORACLE-L Assuming that you are changing the second Oracle Home, ie. Home1. Change the following entries in the registry: HKLM\Software\Oracle\ALL_HOMES\ID1\PATH HKLM\Software\Oracle\HOME1\ORACLE_HOME Depending on how you've located init and dump files, etc., you may see other entries in HKLM\Software\Oracle\HOME1 that need to be changed as well. You then need to remove and re-create the service. oradim -delete -sid SID oradim -new -sid SID -pfile path to init.ora type oradmin on a command line to get all options. Then check HKLM\System\CurrentControlSet\Services\OracleServiceSID to ensure that the correct binary is in use. Edit your tnsnames.ora/names registry or whatever you use for name resolution. That should about do it. Jared Jack van Zanen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/01/2003 08:55 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Upgrade 8.1.7.3.0 to 8.1.7.4.1 on W2K Hi All, We have a test machine with both the 81730 and 81741 software in their own oracle homes. We now want to move a database from the 81730 home to 81741 I know I have to run the catalog.sql, catproc.sql from the new home etc... But how do I tell the registry that the database moved homes?? TiA Jacob A. van Zanen Oracle DBA Quant Systems Europe b.v. Tel : +31 (0) 251 - 268 268 Mobile: +31 (0) 6 51308813 Fax: +31 (0) 251 - 268 269 E-mail: [EMAIL PROTECTED] Visit our web site at http://www.quantsystems.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re[2]: Online tech books
Like that tag line April... Robert G. Freeman Consultant - TUSC www.tusc.com Silence is consent... -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/2/2003 1:49 PM Good Lord, you can NEVER have too many books! April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need. ~ Jerry Gillies ~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: service name, sid ..
I forgot to say: the first listing is the tnsnames.ora on the client(s). -Original Message- From: Stephen Lee Sent: Wednesday, July 02, 2003 3:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: service name, sid .. Example using Transparent Application Failover (TAF) If I didn't forget something, here's what it looks like: XXTP_QUOTE.WORLD = (DESCRIPTION= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_QUOTE2.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=180) (DELAY=1) ) ) ) XXTP_QUOTE2.WORLD = (DESCRIPTION= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1527)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_QUOTE.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=50) (DELAY=1) ) ) ) XXTP_SHOP.WORLD = (DESCRIPTION= (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1526)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1526)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_SHOP2.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=180) (DELAY=1) ) ) ) XXTP_SHOP2.WORLD = (DESCRIPTION= (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1526)) (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.47)(Port=1526)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (FAILOVER_MODE= (BACKUP=XXTP_SHOP.WORLD) (TYPE=SESSION) (METHOD=BASIC) (RETRIES=50) (DELAY=1) ) ) ) XXTP_UPDATE.WORLD = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(Host=123.123.22.48)(Port=1527)) (CONNECT_DATA= (SERVICE_NAME=XXTP.WORLD) (SERVER=DEDICATED) (FAILOVER_MODE= (TYPE=NONE) ) ) ) In the init.ora for one database ... db_domain = world instance_name = XXTP1 service_names = XXTP In the init.ora for the other database db_domain = world instance_name = XXTP2 service_names = XXTP -Original Message- why service name is not same as db name . Earlier service name and sid used to be same thing .. isn't it ( ? ) . Can some one clarify with some examples . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What are the restrictions in naming an Oracle SID/Database?
At the moment I have 5, more to come. What fun. Niall Litchfield [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/02/2003 12:50 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: What are the restrictions in naming an Oracle SID/Database? Jared wrote Which reminds me, I somehow missed that when cloning a SAP db last weekend. Guess I'll go fix it now. Surely one of those things is more than enough :( Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: www.fatcity.com
you can find them at fatcity.com Click on sign up, sign in, should be easy from there. Jose Luis Delgado [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/02/2003 11:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:www.fatcity.com Guys... I need to find some past threads about security schemas from develop to production schemas... How can I find them ? Do I need to register at www.fatcity.com? May be some of you can help me with ideas about the topics that I should take into account when we develop our security schemas... (our developers are in the process of deploying an integrated medical/hospital application). for ex: managing roles and privileges, synonyms from the source schema to an empty schema, auditing operations, etc... etc... Do any of you have something similar or can give me ideas? TIA JL __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ODS and data modeling
Hi all, I'm doing the data model for an Operational Data Store. The ODS will serve to consolidate data from many operational systems and mainly from a new ERP, then most of the data will go in an existing data warehouse. I've worked with datawarehouses before but never withODS. I've check about Bill Inmon and at IBM red book site on the web. Any other good site on ODS ? Also, what are your arguments when choosing between1 table handling all codes or having a table for each code. TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED]
How do I find out the SQL statements for a session
Hi, From V$SESSION, I can find out all the sessions for a user. How do I find out the current SQL and previous SQL for that session? Thanks, Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Should percent increase higher than 0 in 817?
I totally agree with Dick...The person who si saying not to bother these two ,must be a sleeping DBA waiting for trouble to come and then jump and this is the right strategy in US market. I lost my job because I kept my production databases so smooth and trouble free (with proactive monitoring)that gave impression to my management that I am totally free all day. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 02 Jul 2003 10:36:16 -0800 Kirti, I will kinda agree with your Very Senior DBA. Make TEMP an LMT with uniform extents, of type temp and with a tempfile your most likely not to have a problem there that will have any lasting effect. It's one of those things that you have to accept end user complaints on to determine if there has been a problem, otherwise the problem clears as fast as it happens. Now RollBack can get top be a problem if when you run out of space due to some LONG running transaction that should have been killed 2 days ago. Consequently I watch rollback. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, July 02, 2003 2:16 PM To: Multiple recipients of list ORACLE-L After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: CASE in PL/SQL
Title: RE: CASE in PL/SQL just use the sql to assign value to your variable ... if you really want my advise, make this a function or a procedure so you can call it from where ever you want. As function can be completely written in pl/sql you should be okay ... -- this is a procedure ... create or replace PROCEDURE dbp_calc_fin_qtr (pi_date in date, po_qtr number, po_year out number) is nCurrQtr pls_integer := to_number(to_char(pi_date,'Q')); nFinQtr pls_integer := 0; nFinYear pls_integer := to_number(to_char(pi_date,'')); begin if nCurrQtr in (1,2) then nFinQtr := nCurrQtr + 2; else nFinQtr := nCurrQtr - 2; nFinYear := nFinYear + 1; end if; -- end dbp_calc_fin_qtr; / -- this functions returns following string ... -- QQ where QQ is financial qtr and is financial year create or replace FUNCTION dbp_calc_fin_qtryr (pi_date in date, po_qtr number, po_year out number) return varchar2 is nCurrQtr pls_integer := to_number(to_char(pi_date,'Q')); nFinQtr pls_integer := 0; nFinYear pls_integer := to_number(to_char(pi_date,'')); begin if nCurrQtr in (1,2) then nFinQtr := nCurrQtr + 2; else nFinQtr := nCurrQtr - 2; nFinYear := nFinYear + 1; end if; -- return (to_char(nFinQtr,'09') || to_char(nFinYear)); -- end dbp_calc_fin_qtryr; / Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 4:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: CASE in PL/SQL Hi George, With this solution you can only find Quarter number. but I also need Year of that quarter number. when you say to_char(date,'Q'), it might go next year or stay in current fiscal year depending on number of months we add to the date. Thanks, Surendra -Original Message- Sent: Wednesday, July 02, 2003 12:56 PM To: Multiple recipients of list ORACLE-L You don't need CASE. try : to_char( date , 'Q') George Hello ALL, I am trying to find quarter number from a given date . Here is the description Our Financial year runs from July thru June. So, Given any date between these dates I need to find 3 quarters(9 months) from it. July -Sep - 1st Quarter Oct -Dec - 2nd Quarter Jan -Mar - 3rd Quarter Apr -Jun - 4th Quarter I got this done using the following Select select decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') Decode function is used to change calender quarter to our Quarter. But I am unable to find the Year for that quarter. I was using Case statement to solve my problem,as below Select case when decode(to_char(add_months(sysdate,9),'Q'), '1','3', '2','4', '3','1', '4','2', '') 3 then to_number(to_char(main_rec.termination_date,''''))+1 else to_number(to_char(main_rec.termination_date,'''')) P.S The reason for 3 condition check in CASE Statement is, if a sysdate+9months falls in next Financial year , I need to change Year accordingly. But,this works only in SQL, in Procedures, i cannot do this using CASE Statement Can anybody give some ideas on how to approach this?. I have to use this in a cursor (not in the body of my procedure, so condition checking like If then else after fetching year is not possible) Any help would be greatly appreciated. Thanks, Surendra Tirumala Database Administrator Cabinet for Workforce Development Commonwealth of Kentucky -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: George Oneata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:
RE: Microsoft VS Oracle (again)
Title: RE: Microsoft VS Oracle (again) We handle our fail over in the code and trust me users don't know. Nor it affects any scores that we put on the TV ... works just fine, but code design plays important role. BTW talking of TAF, has anyone experimented with 'warming the lib cache' ?? Any experiences? I am referring to dbms_libcache in 9ir2. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: John Kanagaraj [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 2:31 PM To: Multiple recipients of list ORACLE-L Subject: RE: Microsoft VS Oracle (again) Rich, So, what's the case for code changes? TAF (Transparent Application Failover) will provide both SESSION failover as well as SELECT failover. In the former case, the session aborts on the now-failed server and starts from the beginning on the new node, while the latter enables user with open cursors to continue fetching on them after failure by re-executing the cursors on the new node. The former does not require code changes, while the latter does, and requires code changes done on a limited number of executable environments (JDBC Thin and OCI come to mind) that support 'TAF Callback'. TAF *with RAC* will provide the environment for a clustered environment where the user can failover from one node to another node accessing the same data (as compared to TAF in a replicated environment). Hope this answers your question. Murali Vallath [are you listening in, Murali?] may be able to add some details [Hint!] John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Microsoft VS Oracle (again) Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
retrieving BLOB
Hi, I have a file called file1.doc stored in a BLOB column that I would like to retrieve and save it to the filesystem. Can someone post a sample PLSQL code or tell me where I can get the information. Thanks! elain _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Should percent increase higher than 0 in 817?
Rafiq, If we had a slot for you I'd probably recommend you submitting a resume. As it is we're full up on DBA's. I have not had a major, or minor problem for that matter, in years. In this company keeping things running smoothly is a recipe for success. And proactive monitoring is the key. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, July 02, 2003 5:01 PM To: Multiple recipients of list ORACLE-L I totally agree with Dick...The person who si saying not to bother these two ,must be a sleeping DBA waiting for trouble to come and then jump and this is the right strategy in US market. I lost my job because I kept my production databases so smooth and trouble free (with proactive monitoring)that gave impression to my management that I am totally free all day. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 02 Jul 2003 10:36:16 -0800 Kirti, I will kinda agree with your Very Senior DBA. Make TEMP an LMT with uniform extents, of type temp and with a tempfile your most likely not to have a problem there that will have any lasting effect. It's one of those things that you have to accept end user complaints on to determine if there has been a problem, otherwise the problem clears as fast as it happens. Now RollBack can get top be a problem if when you run out of space due to some LONG running transaction that should have been killed 2 days ago. Consequently I watch rollback. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, July 02, 2003 2:16 PM To: Multiple recipients of list ORACLE-L After we rolled out our own scripts to monitor TS usage (de-Installing BMC Patrol) following is a line from a Very Senior DBA's email sent to us (not-so-senior-DBA-team-members) yesterday: I don't think the script should monitor ROLLBACK or TEMP tablespace for space issues - these normally resolve themselves (I will surely get in trouble when my co-workers see this post, but what the heck.. We will get outsourced soon anyway);) BTW, the VSDBA supports 8.1.7.4 databases. - Kirti --- Goulet, Dick [EMAIL PROTECTED] wrote: That's OK, I know a couple who never heard of optimal and/or organization index either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 7:14 PM To: Multiple recipients of list ORACLE-L I saw a Sr. DBA few years ago, who claimed he had worked with Oracle since V4.0, and he didn't have a clue what PCTINCREASE is... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:51 PM What about ones that you don't know? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 4:11 PM To: Multiple recipients of list ORACLE-L PCTINCREASE is a bad good thing. Every DBA that I know of hunts down objects with a pctincrease other than 0 and resets them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ:
RE: CASE in PL/SQL
I still feel that
to_char(add_months(sysdate, 6), 'Q')
is a simpler solution than
MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1
when determining the fiscal quarter. Also, the latter solution above is
off-by-one regarding the results per your original email wherein the fiscal
year starts with Jul through Sep as the first quarter. To wit:
SQL set serveroutput on
SQL exec dbms_output.enable;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL
SQL declare
2 d date;
3 d2 date;
4 q1 number;
5 q2 number;
6 n number;
7 begin
8 d := to_date('1-Jan-2001', 'dd-Mon-');
9 for n in 0 .. 11
10 loop
11d2 := add_months(d, n);
12q1 := mod(to_number(to_char(add_months(d2, 9),'Q'))+1,4) + 1;
13q2 := to_char(add_months(d2, 6), 'Q');
14dbms_output.put_line(
15 to_char(d2, 'dd-Mon-') || ': ' ||
16 to_char(q1) || ' ' ||
17 to_char(q2));
18 end loop;
19 end;
20 /
01-Jan-2001: 2 3
01-Feb-2001: 2 3
01-Mar-2001: 2 3
01-Apr-2001: 3 4
01-May-2001: 3 4
01-Jun-2001: 3 4
01-Jul-2001: 4 1
01-Aug-2001: 4 1
01-Sep-2001: 4 1
01-Oct-2001: 1 2
01-Nov-2001: 1 2
01-Dec-2001: 1 2
PL/SQL procedure successfully completed.
Don't get locked into the whole adding 9 months bit, which as I've pointed
out in my earlier email is not a correct offset. Having now added the
incorrect 9 months, it looks like you're trying to make corrections to the
skewed results by adding 1 then modding by 4, and finally adding another one
because you will get a zero out of the mod operation when in the fourth
quarter. Your fiscal year happens to lag the calendar year by 6 months, so
just add 6 months to the date for the to_char() operation.
Regards.
...Rudy
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 4:06 PM
To: Multiple recipients of list ORACLE-L
Hi Rudy,
Thanks for your suggestion. But I got a better suggestion from Metalink.
Here what I was suggested:
SQL SELECT (MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1) qtr
2 , DECODE(SIGN(TO_NUMBER(TO_CHAR(main_rec.termination_date,'Q')) - 3)
3 , -1, TO_NUMBER(TO_CHAR(main_rec.termination_date,''))
4 , TO_NUMBER(TO_CHAR(main_rec.termination_date,'')) + 1) yr
5 ...
6
Thanks for your help.
Surendra
-Original Message-
Sent: Wednesday, July 02, 2003 1:51 PM
To: Multiple recipients of list ORACLE-L
If your Jul is 1st quarter, then your offset is should be 6 months instead
of the 9 months in your email; or think of it another way, if you Jan is the
beginning of the 3rd quarter, it is the beginning of the 2nd half of the
year, and half a year is 6 months.
With this in mind, you really don't need any decodes at all for just finding
out your financial quarter. The following query suffices:
select to_char(add_months(sysdate,
6),
'Q') from user_users;
My best interpretation of your second query with the if-then is that if a
date is in the second half of the calendar, you want to push it to the
following year (or perhaps if the calendar date is from second half of last
year, you want it reported as being in this year, which is kind of like
saying fiscal year ending )
So, here's your solution (not exactly fast, but it's implemented competely
with numeric functions provided by Oracle):
Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q'))
To figure out how much correction you need to each year based on the quarter
the year appears in, add the following to the year:
sign((sign(3 - Q) + 1) * sign(3 - Q))
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L
Hello ALL,
I am trying to find quarter number from a given date . Here is the
description
Our Financial year runs from July thru June. So, Given any date between
these dates I need to find 3 quarters(9 months) from it.
July -Sep - 1st Quarter
Oct -Dec - 2nd Quarter
Jan -Mar - 3rd Quarter
Apr -Jun - 4th Quarter
I got this done using the following Select
select decode(to_char(add_months(sysdate,9),'Q'),
'1','3',
'2','4',
'3','1',
'4','2',
'')
Decode function is used to change calender quarter to our Quarter.
But I am unable to find the Year for that quarter.
I was using Case statement to solve my problem,as below
Select case when decode(to_char(add_months(sysdate,9),'Q'),
'1','3',
'2','4',
'3','1',
'4','2',
'') 3
then
Re: How do I find out the SQL statements for a session
Roger Xu wrote: Hi, From V$SESSION, I can find out all the sessions for a user. How do I find out the current SQL and previous SQL for that session? Thanks, Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 SQL statements are identified by an address (location of the cursor in SGA) and a hash value (to make sure that the address has not been reused by another cursor). 999 first characters can be found in V$SQL or V$SQLAREA. For longer statements nose around V$SQLTEXT or V$SQLTEXT_WITH_NEWLINES. I still have to see the 'previous SQL' coordinates point to something else than the 'current SQL'. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Raid 0+1 vs. mirrored pairs
RAID 0+1 and RAID 1+0 provide similar performance but RAID 1+0 has the advantage of offering more redundancy/availability. If you take your sys admin's advice and go with 2 x 3 disks in a 0+1 config and you lose one disk then you lose the other two spindles in that stripe as well. That's half of your I/O capacity gone. With RAID 1+0 the other two spindles will still be available when one disk fails in a stripe. I think it is useful to be able to split your data between different physical disks. Apart from the fact that redo and archive logs should never reside on the same volume then it can make a big difference being able to keep your data tablespaces from your indexes from your rollback from your redo. And now I have stated the bleeding obvious. BAARF. Gudmundur -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: retrieving BLOB
Well, heres a script that will dump out contents of blob that I used to test
things out. The script takes 3 arguments:
1) Name of table
2) Name of CLOB/BLOB field
3) ROWID of record with the LOB that you want, OR an asterisk * for all
records
Saving it into the file system is left as an exercise for the reader using
UTL_FILE (mostly because I don't actually have anything that deals with
UTL_FILE.)
YMMV
...Rudy
---Begin script---
exec dbms_output.disable;
set serveroutput off
set serveroutput on
exec dbms_output.enable(200);
set verify off
define BLOB_TABLE=1
define BLOB_FIELD=2
define BLOB_ROWID=3
declare
dType USER_TAB_COLUMNS.DATA_TYPE%type;
maxLen number;
readLennumber;
position number;
lastPosition number := 0;
offset number;
vBuffervarchar2(32767);
rBufferraw(32767);
needFinalCount number := 0;
begin
select DATA_TYPE
into dType
from USER_TAB_COLUMNS
where COLUMN_NAME = upper('BLOB_FIELD') and
TABLE_NAME = upper('BLOB_TABLE');
for tCursor in (
select t.*, ROWID ROW_ID
from BLOB_TABLE t
where 'BLOB_ROWID' = '*' or
ROWID = 'BLOB_ROWID')
loop
maxLen := 32767;
offset := 1;
dbms_output.put_line(' ');
dbms_output.put_line('RowID=' || tCursor.ROW_ID ||
' getLength()=' ||
dbms_lob.getlength(tCursor.BLOB_FIELD) || ':');
if (tCursor.BLOB_FIELD is not null and
nvl(dbms_lob.getlength(tCursor.BLOB_FIELD), 0) 0) then
begin
if (dType = 'CLOB') then
dbms_lob.read(tCursor.BLOB_FIELD, maxLen, offset, vBuffer);
dbms_output.put_line(substr(vBuffer, 1, 255));
readLen := maxLen;
elsif (dType = 'BLOB') then
dbms_lob.read(tCursor.BLOB_FIELD, maxLen, offset, rBuffer);
dbms_output.put_line(
substr(
utl_raw.cast_to_varchar2(
utl_raw.translate(rBuffer,
utl_raw.cast_to_raw(chr(0)),
utl_raw.cast_to_raw('?'))),
1, 255));
vBuffer := utl_raw.cast_to_varchar2(rBuffer);
readLen := utl_raw.length(rBuffer);
end if;
for position in 1..readLen
loop
dbms_output.put(
substr('000' ||
ascii(substr(vBuffer, position, 1)), -3, 3) || '
');
if (mod(position, 20) = 0) then
dbms_output.put_line(' : ' || position);
needFinalCount := 0;
else
needFinalCount := 1;
end if;
lastPosition := position;
end loop;
if (needFinalCount 0) then
dbms_output.put(' : ' || lastPosition);
end if;
dbms_output.put_line(' ');
exception
when others then
dbms_output.put_line(' ?Exception?');
end;
end if;
end loop;
end;
/
undefine 1
undefine 2
undefine 3
---End script---
-Original Message-
Sent: Wednesday, July 02, 2003 4:56 PM
To: Multiple recipients of list ORACLE-L
Hi,
I have a file called file1.doc stored in a BLOB column that I would like to
retrieve and save it to the filesystem. Can someone post a sample PLSQL code
or tell me where I can get the information.
Thanks!
elain
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Redo Copy Latch contention ??
Question : How can I determine if this redo copy latch is causing the performance issues , guess that is my main question before altering some hidden parameter in init.ora. Do a 10046 trace and see if you have any waits on this latch. I'll run the risk of being sued for copyright infringement and say that you can't extrapolate detail from an aggregate and aggregates are what you get from the V$ views. Redo copy latch contention might not be a problem for 99% of your users but it might be a huge problem for the other 1% of your users. However, you have no way of figuring out which users are suffering from this by looking at V$ views. And you can't even tell if redo latch contention is a problem even if your ratio is screwed. I'm willing to bet you my autographed Steve Adams' book that you will not fix your redo latch contention by tweaking an init.ora parameter (unless it's set _redo_latch_ratio=0). Gudmundur -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 do I find out the SQL statements for a session
1. query on v$session to get the SID select sid from v$session where username='user_name'; 2. Then pass sid to the follwing query select sql_text from v$sqlarea a, v$session b where a.hash_value=b.sql_hash_value and a.address=b.sql_address and b.sid=essiedi / 3. Also query v$open_cursor to check all the SQL statements executed by that user session. Madhu Reddy X13944 -Original Message- Sent: Wednesday, July 02, 2003 3:56 PM To: Multiple recipients of list ORACLE-L Hi, From V$SESSION, I can find out all the sessions for a user. How do I find out the current SQL and previous SQL for that session? Thanks, Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
