RE: freebie. Summarize Oracle Listener logs
Sorry - meant to send just to Steve
Please ignore
-Original Message-
Hallas, John, Tech Dev
Sent: 05 January 2004 11:19
To: Multiple recipients of list ORACLE-L
Steve,
I have an awk script which does something similar - see code and example below
However I can put your file on my site where I have already stored your Perl script
for getting the DDL out of an export file ( www.hcresources.co.uk) if you wish.
Cheers
John
Service :Host :User :tcp:ip address
===
WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734
echo "Service :Host :User :tcp:ip address"
echo "==="
grep CONNECT b2tperf.log |\
awk -F= '{print $3 ":1:" $6 ":2:" $7 ":3:" $9 ":4:" $10 ":5:" $11}' |\
sed 's/).*:1:/:/' | \
sed 's/).*:2:/:/' | \
sed 's/).*:3:/:/' | \
sed 's/).*:4:/:/' | \
sed 's/).*:5:/:/' | \
sed 's/).*$//' | \
awk -F: '{printf("%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n", \
$1,$2,$3,$4,$5)}' | sort > /tmp/j.lis
grep -v PROGRAM /tmp/j.lis > /tmp/j1.lis
cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\
END{for (i = 1; i <= count; i++)print lines[i],"\t"data[lines[i]]}'\
|sort -n +4
rm /tmp/j.lis
rm /tmp/j1.lis
-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L
I wanted to summarize our listener logs to see who's connecting with what tools. I
checked google and didn't see anything, so I decided to write one. It may be useful to
somebody else.
It summarizes the lines of a listener log. the output is similar to
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
PA1DBO TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE 600
PW2DBO TSOORA56 DLLHOST.EXE 362
ITMDBO TSOORA52 SQLPLUS.EXE 166
SYSTEM TSOORA56 MAPS_BIZ 88
PW3DBO TSOORA57 TD2000.EXE 72
ASPNET TSOORA56 ASPNET_WP.EXE 57
ORACLE __JDBC__41
ARCDBO SATSAP09 SQLPLUS.EXE12
2KNUPPS SKNUPP-1 TOAD.EXE7
PERRYSM SAIT02547SQLPLUSW.EXE6
2RILEYJ GLEFIO01210 TOAD.EXE4
2MASHUA GLEFIO01262 TOAD.EXE3
PA1DBO TSOORA49 DMFILESCAN.EXE 3
PA1DBO TSOORA49 DMCLEAN.EXE 3
LDQDBO TSOORA24 JREW.EXE2
2SHURTN GLEFIO01261 TOAD.EXE1
PW3DBO TSOORA57 DBDBOIN.EXE 1
PA1DBO TSOORA49 SQLPLUSW.EXE1
ODDDBO TSOORD08 EXP.EXE 1
2KNUPPS SKNUPP-1 SQLPLUSW.EXE1
PW2DBO TSOORA56 SQLPLUSW.EXE1
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made
- only that it was attempted. seperating the failed connections may be a future
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in
yet.
It will process 100 meg of logs in few minutes.
It was written for windows only. sorry, I work in a MicroSlop env...
I have a cmd file that I use to run it so it can process our current logs or older
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
otherwise use [EMAIL PROTECTED] and I'll get to it when I get home.
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and
RE: freebie. Summarize Oracle Listener logs
Steve,
I have an awk script which does something similar - see code and example below
However I can put your file on my site where I have already stored your Perl script
for getting the DDL out of an export file ( www.hcresources.co.uk) if you wish.
Cheers
John
Service :Host :User :tcp:ip address
===
WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734
echo "Service :Host :User :tcp:ip address"
echo "==="
grep CONNECT b2tperf.log |\
awk -F= '{print $3 ":1:" $6 ":2:" $7 ":3:" $9 ":4:" $10 ":5:" $11}' |\
sed 's/).*:1:/:/' | \
sed 's/).*:2:/:/' | \
sed 's/).*:3:/:/' | \
sed 's/).*:4:/:/' | \
sed 's/).*:5:/:/' | \
sed 's/).*$//' | \
awk -F: '{printf("%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n", \
$1,$2,$3,$4,$5)}' | sort > /tmp/j.lis
grep -v PROGRAM /tmp/j.lis > /tmp/j1.lis
cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\
END{for (i = 1; i <= count; i++)print lines[i],"\t"data[lines[i]]}'\
|sort -n +4
rm /tmp/j.lis
rm /tmp/j1.lis
-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L
I wanted to summarize our listener logs to see who's connecting with what tools. I
checked google and didn't see anything, so I decided to write one. It may be useful to
somebody else.
It summarizes the lines of a listener log. the output is similar to
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
PA1DBO TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE 600
PW2DBO TSOORA56 DLLHOST.EXE 362
ITMDBO TSOORA52 SQLPLUS.EXE 166
SYSTEM TSOORA56 MAPS_BIZ 88
PW3DBO TSOORA57 TD2000.EXE 72
ASPNET TSOORA56 ASPNET_WP.EXE 57
ORACLE __JDBC__41
ARCDBO SATSAP09 SQLPLUS.EXE12
2KNUPPS SKNUPP-1 TOAD.EXE7
PERRYSM SAIT02547SQLPLUSW.EXE6
2RILEYJ GLEFIO01210 TOAD.EXE4
2MASHUA GLEFIO01262 TOAD.EXE3
PA1DBO TSOORA49 DMFILESCAN.EXE 3
PA1DBO TSOORA49 DMCLEAN.EXE 3
LDQDBO TSOORA24 JREW.EXE2
2SHURTN GLEFIO01261 TOAD.EXE1
PW3DBO TSOORA57 DBDBOIN.EXE 1
PA1DBO TSOORA49 SQLPLUSW.EXE1
ODDDBO TSOORD08 EXP.EXE 1
2KNUPPS SKNUPP-1 SQLPLUSW.EXE1
PW2DBO TSOORA56 SQLPLUSW.EXE1
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made
- only that it was attempted. seperating the failed connections may be a future
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in
yet.
It will process 100 meg of logs in few minutes.
It was written for windows only. sorry, I work in a MicroSlop env...
I have a cmd file that I use to run it so it can process our current logs or older
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
otherwise use [EMAIL PROTECTED] and I'll get to it when I get home.
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTE
RE: Code Conversion from MSSQL into Oracle
Steve Perry from this list produced a perl script to read a import file (rows=n) and produce formatted DDL. Whilst it is not perfect (as Steve agrees) it does give a good basis of where to start with processing and transforming a text input stream into a text output stream whilst making a small no of changes) - or ETL as it is called these days, using PERL as the mechanism. I host the zip file on my site at http://www.hcresources.co.uk/perlscript.shtml I hope someone finds it useful. If they do thank Steve. HTH John -Original Message- Sent: 11 December 2003 12:55 To: Multiple recipients of list ORACLE-L 10 years ago or so, I wrote a 105 line script for the UNIX "sed" (a.k.a. "stream editor") command to convert Teradata "BTEQ" scripts into Oracle SQL*Plus. Painful, yet thrilling, and it took only about a day of concentration with the O'Reilly "Awk and Sed" book at hand. I'll bet you can write something (whether in "sed" or "awk" or Perl or Java) faster than it takes you find a TransactSQL-to-PL/SQL converter...? And if you don't know Perl (probably the best choice) yet, this exercise could be the opportunity to put a huge new skill into the old skillset... Just a thought... on 12/11/03 4:49 AM, VIVEK_SHARMA at [EMAIL PROTECTED] wrote: Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle? SAMPLE :- DECLARE @entity_id char(32), @branch_id char(9) DECLARE cur_temp_GEMT CURSOR FOR select branch_id,entity_id from GEMT where other_party_name='' and entity_type='D' OPEN cur_temp_GEMT FETCH NEXT FROM cur_temp_GEMT INTO @branch_id,@entity_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id FETCH NEXT FROM cur_temp_GEMT INTO @branch_id, @entity_id END CLOSE cur_temp_GEMT DEALLOCATE cur_temp_GEMT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: shareplex: datatype unsupported
Please bear in mind that there is one thing in a datatype being supported and another in all functions and features of Shareplex being usable when that datatype is involved. I am thinking about datatype long specifically. We have been replicating a 8i database (tru64) to a 9i one (sun)using Shareplex for months and we have tested the reverse replication and that works equally well. That is not to say that we have not had problems though !! John -Original Message- elain he Sent: 05 November 2003 12:04 To: Multiple recipients of list ORACLE-L Hi, We are evaluating using either Oracle logical standby or Quest Shareplex replication for reporting purposes. It appears that there are quite a few datatypes not supported by Logical standby. Anyone knows what datatypes are not supported by shareplex replication? Tried looking up at quest website but could not find any documentation. Quest claimed that shareplex can replicate database of different versions, for eg from 9i to 8i as long as the 9i new features are not being utilized. Anyone has any experience with that? Thanks. elain _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: A quick note
Hey good to see you back again Lee. The big change here is that all Oracle knowledge has been cyber-engineered into an automoton called Tanel Poder. Any query to the list is routed by a big fast connection into a huge server with hundreds of CPU's and unlimited memory An almost instantaneous reply is automatically generated , often complete with complex examples and code cuts to demonstrate the point. The best part is that is that the machine is programmed to exibit almost humanistic type responses (occasionally) John -Original Message- Robertson Lee - lerobe Sent: 08 October 2003 11:34 To: Multiple recipients of list ORACLE-L Hi everyone, I'm back in the land of the living after spending a year on a DB2 EEE project. I've just started working on a 9i RAC solution so the questions will soon be flooding in. I will be lucky if I can even remember to spell spqlusl , I mean sqlplus :-) Cheers Lee ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to check 32 or 64 bit?
Perhaps it is even simpler to describe V$session The first column SADDR is raw(8) on 64 bit and is raw(4) on 32 bit databases John -Original Message- Sent: 23 September 2003 22:25 To: Multiple recipients of list ORACLE-L Oracle DBA wrote: > Also how to check whether my DB is 32 or 64 bit SQL> SELECT dbms_utility.port_string FROM dual; PORT_STRING SVR4-be-64bit-8.1.0 SQL> SELECT paddr FROM v$session WHERE rownum < 2; PADDR 00038A57CA28 SQL> connect ... Connected. SQL> / PORT_STRING SVR4-be-8.1.0 SQL> SELECT paddr FROM v$session WHERE rownum < 2; PADDR 902B51D8 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: translate, replace...?
Yes, you are correct Igor. It was taken from an example I had which did 3 replaces. I did manage to substitute the 13 and 10 correctly though select replace(replace(a,chr(13),'R'),chr(10),'') from table John -Original Message- Sent: 17 September 2003 17:20 To: Multiple recipients of list ORACLE-L Looks like you've got "one too many" REPLACEs. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, September 17, 2003 11:00 AM To: Multiple recipients of list ORACLE-L IA, The following code should be close to what you want select replace(replace(replace(a,chr(13),'R'),chr(10),'')) from table John -Original Message- Sent: 16 September 2003 16:15 To: Multiple recipients of list ORACLE-L Hi, I have a column with carriage returns (chr(13) ) and line feeds (chr(10)). I want to select this column replacing the chr(13) with 'R' and chr(10) with ' ' . Whats the best way to do this? Regards IA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: translate, replace...?
IA, The following code should be close to what you want select replace(replace(replace(a,chr(13),'R'),chr(10),'')) from table John -Original Message- Sent: 16 September 2003 16:15 To: Multiple recipients of list ORACLE-L Hi, I have a column with carriage returns (chr(13) ) and line feeds (chr(10)). I want to select this column replacing the chr(13) with 'R' and chr(10) with ' ' . Whats the best way to do this? Regards IA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: get sid (session id) and serial#?
osting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (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: Sinardy Xing > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > Important: This transmission is intended only for the use of the addressee > > and may contain confidential or legally privileged information. If you > > are > > not the intended recipient, you are notified that any use or dissemination > > of this communication is strictly prohibited. If you receive this > > transmission in error please notify the author immediately by telephone > > and > > delete all copies of this transmission together with any attachments. > > > > -- > > 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: Corniche Park > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Hallas, John, Tech Dev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 can I measure my DB performance.
Get the purge package written by Tim Gorman available from http://www.evdbt.com/tools.htm (no 31 on the list) Install it as user perfstat and then create the folloiwng 2 dbms_jobs as user perfstat to take snapshots every hour and purge snapshot data older than 7 days Works on 8.1.7 & 9 (but not on 8.1.6 because the perfstat tables have changed since then) declare l_job number; begin dbms_job.submit (l_job,'statspack.snap;',trunc(sysdate)+13/24,'sysdate+1/24'); --every hour on the hour end; / declare m_job number; begin dbms_job.submit (m_job,'sppurpkg.purge(7);', trunc(sysdate)+12/24,'sysdate+1'); end; / John -Original Message- Sent: 04 September 2003 21:05 To: Multiple recipients of list ORACLE-L I too have mine setup to take a snapshot every 15 minutes. Can you teach me how to automate purges using sppurge.sql ? Thanks . - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 06:04 > Start by installing and configuring statspack. > > I have mine setup to take a snapshot every 15 minutes. > > It collects a lot of data, I am now in the midst of automating a purge > process. :) > > Then implement YAPPPACK from http://www.miracleas.dk/en/tech.html > and use the data to create response time charts. > > At least, that's one way to do it. > > Jared > > > > > > > Jake Johnson <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 08/28/2003 04:59 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:How can I measure my DB performance. > > > Is there a standard query I can run to compare the performance of a db. > (Kind of like bogomips for unix) > > Thanks, > Jake Johnson > [EMAIL PROTECTED] > > __ > Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on > Rims, Tires, and Wheel Packages. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jake Johnson > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 on Win2000
PC Anywhere or direct from the console -Original Message- Sent: 03 September 2003 18:44 To: Multiple recipients of list ORACLE-L First time I have got to maintain Oracle databases on Win 2000 server. What tools do you guys use to get to the server to admin oracle databases ? Thanks, Bala. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sqlload
Tom, I am afraid you are wrong there, a parameter file can be used to run a sqlloader script The syntax that Fawzia uses is correct although as per imp/exp it would be better to put all the parameters in the parfile file I spent years not knowing that and in frustration posted to the list and was immediately shown how to do it Interesting to see if this reply makes it to the list as the last one I posted about 2 hours ago never got there. John -Original Message- Sent: 02 September 2003 19:05 To: Multiple recipients of list ORACLE-L Fawzia, Try the following (change parfile= to control=) sqlldr control=pants.ctl log=me.log parfile is for imp/exp. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 02, 2003 1:20 PM To: Multiple recipients of list ORACLE-L PLEASE Can someone help??I have a sqlloader control file below and I am getting the following error when I run it and I just cant see what is wrong!!! Rgds Fawzia [EMAIL PROTECTED]> sqlldr parfile=pants.ctl log=me.log LRM-00110: syntax error at 'LOAD' LRM-00113: error when processing file 'pants.ctl' Control file: LOAD DATA INFILE 'gazza2.csv' APPEND INTO TABLE olsr10_tlmif2.gazetteer1 fields terminated by ',' trailing nullcols (postcode_prefix_spaced char, filler1 char, postcode_prefix char, filler2 char, postcode_range char, filler3 char, town char, filler4 char, county char, valid_date char) ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Permissions error
Well spotted Ron. I had not realised it was a VMS box Either way the principle is correct - set up the environment first John -Original Message- Sent: 03 September 2003 13:25 To: Multiple recipients of list ORACLE-L John, If the OS is Dec Alpha OpenVMS. In the user environment execute the $ORACLE_HOME[.util]orauser.com to properly set the Oracle logicals for the user. A SHOW LOGICAL will display what you have set now. Ron >>> [EMAIL PROTECTED] 09/03/03 04:34AM >>> The different account wil lnot have it's Oracle environment set up properly. Check out the .profile on the account that works and the one that does not. One will set up an Oracle_home and Oracle_sid variable, the other will not. If it is not in the .profile then look at any other script that is called before you run sqlplus John -Original Message- Sent: 03 September 2003 01:19 To: Multiple recipients of list ORACLE-L Hi All I get this error when I try to log into sqlplus from my DEC ALPHA machine through a telnet session. Enter user-name: testid Enter password: ERROR: ORA-01034: ORACLE not available ORA-07320: smsget: shmat error when trying to attach sga. DEC OSF/1 (AXP) Error: 13: Permission denied Did some permissions get corrupted. I am able to connect using sqlplus from my oracle account. This happens when I try to connect from a different account. Thanks David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Permissions error
The different account wil lnot have it's Oracle environment set up properly. Check out the .profile on the account that works and the one that does not. One will set up an Oracle_home and Oracle_sid variable, the other will not. If it is not in the .profile then look at any other script that is called before you run sqlplus John -Original Message- Sent: 03 September 2003 01:19 To: Multiple recipients of list ORACLE-L Hi All I get this error when I try to log into sqlplus from my DEC ALPHA machine through a telnet session. Enter user-name: testid Enter password: ERROR: ORA-01034: ORACLE not available ORA-07320: smsget: shmat error when trying to attach sga. DEC OSF/1 (AXP) Error: 13: Permission denied Did some permissions get corrupted. I am able to connect using sqlplus from my oracle account. This happens when I try to connect from a different account. Thanks David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Some posts not showing up
I sent 2 posts last week within the space of 5 minutes and neither of them showed up. Posts made both before and since have shown up so it is not a matter of becoming unsubscribed in my case. John -Original Message- Sent: 02 September 2003 23:15 To: Multiple recipients of list ORACLE-L There is no moderator per se. The only one is me, and I only do that when absolutely necessary. I see this one made it to the list, you must have resubscribed. All: If email sent to you bounces too many times, you will be automatically dropped from the subscriber list, and you will have to re-subscribe. If you're not getting emails from the list, try resubscribing. Jared "Bob Metelsky" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/02/2003 06:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Some posts not showing up I've noticed a few of my recent posts have not shown up. Is "someone" reading and discerning whether the post is suitable? I've newer seen this before but some of my posts make it and some do not. I don't see how my content would be objectionable Thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: get sid (session id) and serial#?
ia-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Congratulations Arup (DBA of the Year)
Not being a party pooper but what does DBA of the Year actually mean. What criteria was used. How can Oracle know about a quarter of a million DBA's unless it monitors tars raised. John -Original Message- [mailto:[EMAIL PROTECTED] Sent: 28 August 2003 15:54 To: Multiple recipients of list ORACLE-L Just came to know from CTOUG news that Oracle has chosen Arup Nanda(our List member) as "DBA of the Year". Congratulations Arup. thought I will share this news with the group. CTOUG Board Member, Arup Nanda, Chosen as "DBA of the Year" Oracle has chosen our very own CTOUG board member, Arup Nanda, as the "DBA of the Year", out of some quarter million DBAs worldwide. There will be a felicitation ceremony at OracleWorld and the award recipients will be featured in Nov-Dec issue of Oracle Magazine. Thought you would like to know, as a part of the CTOUG community. In his interview, he has mentioned CTOUG, so watch for it. Arup is President of Proligence (http://www.proligence.com/). Thanks. Best Regards, Prasad -- 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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: number of active members in this list
Active and subscribers are 2 different things I bet there are not more than 100 -150 people who post regularly, say once a month or more but I am sure there are a lot more lurkers John -Original Message- Sent: 24 August 2003 07:40 To: Multiple recipients of list ORACLE-L Hi! Just wondering. what is the number of active subscribers to this list? An approximation is enough :) Tanel. -- 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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: speeding up conventional path sqlldr
rk Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SharePlex
Ha, been there done that Not an uncommon event as I understand it. John -Original Message- Sent: 09 August 2003 23:59 To: Multiple recipients of list ORACLE-L I'll add one to the list: nnn) How much downtime and DBA time is required when the Shareplex replication queues get corrupted and you have to rebuild your entire replicated database? (I only add that as I've got to do it tomorrow morning). T¬ Sent: 09 August 2003 00:04 To: Multiple recipients of list ORACLE-L I know a little of their product... but I think you should be well aware of the limitations before attempting to implement it. Here are a couple of questions to ask the technical folks over there... 1) What happens when someone applies an Oracle Financials patch to the system. What is the procedure? 2) Do you have anyone else running Financials 11i that you could talk to as a reference? 3) Do they support IOT's? (Which Financials has a lot of) 4) How much downtime is normally associated with something like adding a new table or dropping one from replication? Nick -Original Message- Sent: Friday, August 08, 2003 3:44 PM To: Multiple recipients of list ORACLE-L We are not running 9i but 8.1.7.4, sorry for not including that earlier. We are rolling out to our international offices and we basically have offices in every time zone. I'm looking at SharePlex for HA, reporting use, and potentially migrating from HP to Linux. They will be on Linux next month. Allan -Original Message- Sent: Friday, August 08, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Nelson, SharePlex does the same basic thing that Oracle does for the logical standby, as a matter of fact if your running 9i why not use that instead of Quests's pricey tool? I don't believe there is any additional cost to using logical standby over the second server license that your going to have to pay anyway. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, August 08, 2003 5:14 PM To: Multiple recipients of list ORACLE-L Hello, Quest is trying to sell us a product named SharePlex. It sounds very attractive, but then sales people are supposed to be good at that. We are a mid sized company, about 2.2 billion per year, running Financials 11.5.7. We are interested in this for HA and for reporting instance use. We use Cognos as our query tool and the owners of this product tells me that we can't tune the SQL it emits. It makes pretty poor choices, which is not surprising for a gooey, sticky tool designed for end users. It is sort of pretty and if you can drool you too can generate cross products. Anyway , I'd like to get them off the production box. Does SharePlex allow you to stay close to the production instance in time? Does the store and forward work well? Do you love it? Hate it? Anything you'd like to say about this product I'd like to hear Thanks in advance Allan L. Nelson Oracle DBA M-I L.L.C. (832) 295-2238 office (832) 351-4180 fax [EMAIL PROTECTED] < <mailto:[EMAIL PROTECTED]> mailto:[EMAIL PROTECTED]> __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SharePlex
Is your first sentence a bit of an understatement Nick ? If so I suppose you can call your points 'leading' questions like lawyers use to trap a defendant We use Shareplex and I have seen some of your posts on the subject (and still have them in my saved box) John -Original Message- Sent: 09 August 2003 00:04 To: Multiple recipients of list ORACLE-L I know a little of their product... but I think you should be well aware of the limitations before attempting to implement it. Here are a couple of questions to ask the technical folks over there... 1) What happens when someone applies an Oracle Financials patch to the system. What is the procedure? 2) Do you have anyone else running Financials 11i that you could talk to as a reference? 3) Do they support IOT's? (Which Financials has a lot of) 4) How much downtime is normally associated with something like adding a new table or dropping one from replication? Nick -Original Message- Sent: Friday, August 08, 2003 3:44 PM To: Multiple recipients of list ORACLE-L We are not running 9i but 8.1.7.4, sorry for not including that earlier. We are rolling out to our international offices and we basically have offices in every time zone. I'm looking at SharePlex for HA, reporting use, and potentially migrating from HP to Linux. They will be on Linux next month. Allan -Original Message- Sent: Friday, August 08, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Nelson, SharePlex does the same basic thing that Oracle does for the logical standby, as a matter of fact if your running 9i why not use that instead of Quests's pricey tool? I don't believe there is any additional cost to using logical standby over the second server license that your going to have to pay anyway. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, August 08, 2003 5:14 PM To: Multiple recipients of list ORACLE-L Hello, Quest is trying to sell us a product named SharePlex. It sounds very attractive, but then sales people are supposed to be good at that. We are a mid sized company, about 2.2 billion per year, running Financials 11.5.7. We are interested in this for HA and for reporting instance use. We use Cognos as our query tool and the owners of this product tells me that we can't tune the SQL it emits. It makes pretty poor choices, which is not surprising for a gooey, sticky tool designed for end users. It is sort of pretty and if you can drool you too can generate cross products. Anyway , I'd like to get them off the production box. Does SharePlex allow you to stay close to the production instance in time? Does the store and forward work well? Do you love it? Hate it? Anything you'd like to say about this product I'd like to hear Thanks in advance Allan L. Nelson Oracle DBA M-I L.L.C. (832) 295-2238 office (832) 351-4180 fax [EMAIL PROTECTED] < <mailto:[EMAIL PROTECTED]> mailto:[EMAIL PROTECTED]> __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: sar
Normally sudo is used to grant limited permissions. The SA would allow you to use sar and for you to access it you would type sar and be prompted for a password and you would enter the appropriate password. John -Original Message- Sent: 31 July 2003 16:45 To: Multiple recipients of list ORACLE-L Just got this email from my SysAdmin when I asked for access to sar. Anyone know what he is talking about? We are on AIX 4 and 5. "I cannot give you direct access to the sar command. Because of the parameters the command allows, it would be equivalent to giving full root access. If you could give me some details on the kind of information you would like to be able to collect, maybe we could set up some kind of command to obtain it." Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8.1.7.4 interim patch management strategy
Always a difficult activity although the opatch utility available with 9i could prove a real benefit Most sites I have worked on don't have a policy as such but they only apply patches as and when necessary. The other difficulty is that out of say the 180 patches you mention, many may apply to a specific executable and therefore over write each other. The other problem is where many databases share a single OH. Applying a patch to one database impacts them all and requires a lot more testing, outage and overall management. The only clean way is to have a new OH for each patch install, this is not very practicable No easy answer I am afraid but I would only be applying patches when absolutely necessary John -Original Message- Sent: 29 July 2003 23:55 To: Multiple recipients of list ORACLE-L Hello All, There are 188 interim patches after 8.1.7.4 patchset. I know that 8.1.7.4 is the last patchset. 1. What methods/strategy do you'll have for installing patches on a 8.1.7.4 64 bit hp-ux 11.11 database? 2. Is there opatch for 8.1.7.4? AFAIK opatch is only for 9i. 3. Should one install all the applicable patches (around 180 individual patches)? pessimistic (wait for the bug to hit and db to crash) optimistic (install applicable patches) 4. How to manage the interdependencies between these 180 individual patches? 5. Can we create a hp unix patch depot like "thing" for all these oracle patches? pls dont advise putting an enhancement request and upgrading to 9i/10i :-) Thanks, Mandar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mandar A. Ghosalkar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Extproc setup Q?
Jack, This is a post from a few weeks ago that gives a setup TNSNAMES: extproc_connection_data.world = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc_agent) <--- Key1 ) (CONNECT_DATA = (SID = 11) <--- Key2 (server=dedicated)) ) Listener: EXTPROC_LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc_agent) < Key1 ) ) SID_LIST_EXTPROC_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = 11) < Key2 (ORACLE_HOME = /ora1/81764) (PROGRAM = extproc) ) ) BTW: Oracle's recommendation is to use a seperate listener for extproc calls. -Original Message- Sent: 24 July 2003 15:10 To: Multiple recipients of list ORACLE-L <http://www.quantsystems.nl/> Tried that still the same error message. Any other hints & tips for me to try? Just curious though ,why is it DLLS if I'm working on LINUX Jack -Original Message- Sent: Thursday, July 24, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Please add the line in red to your listener.ora file. Extproc has been made secure in Oracle 9i. The following line has to be added to execute your own external procedures. LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = plsextproc) (ORACLE_HOME = /oracle/app/product/9.2.0) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=ANY") ) (SID_DESC = (GLOBAL_DBNAME = Ora92) (ORACLE_HOME = /oracle/app/product/9.2.0) (SID_NAME = ora92) ) ) Regards Munish Bajaj -Original Message- Sent: Thursday, July 24, 2003 16:24 To: Multiple recipients of list ORACLE-L Hi All, I am trying to setup EXTPROC but keep getting ORA-28575 (Check your tnsnames.ora & listener.ora) Attached are my files that look OK to me Anybody?? [EMAIL PROTECTED] admin]$ cat tnsnames.ora # TNSNAMES.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/tnsnames.ora ORA92 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Ora92) ) ) extproc_connection_data = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) (CONNECT_DATA = (SID = plsextproc)) ) ) [EMAIL PROTECTED] admin]$ cat listener.ora # LISTENER.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/listener.ora LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = plsextproc) (ORACLE_HOME = /oracle/app/product/9.2.0) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = Ora92) (ORACLE_HOME = /oracle/app/product/9.2.0) (SID_NAME = ora92) ) ) TIA Jack van Zanen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DB_BLOCK_LRU_LATCHES , multiple buffer pools , oracle9i
Not sure what exactly you are asking here but one way of sizing the multiple buffer pools is to look at V$DB_CACHE_ADVICE view. The following is from the 9i performance tuning manual V$DB_CACHE_ADVICE can be used to size all pools configured on an instance. Make the initial cache size estimate, run the representative workload, then simply query the V$DB_CACHE_ADVICE view for the pool you want to use. For example, to query data from the KEEP pool: SELECT size_for_estimate, buffers_for_estimate , estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'KEEP' AND block_size= (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON'; John -Original Message- [mailto:[EMAIL PROTECTED] Sent: 23 July 2003 13:17 To: Multiple recipients of list ORACLE-L when configuring multiple buffer pools in oracle9i , how are lru_latches set. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: faq broke? -- Other options
Good point Rich. I am sure I saw a post recently from Cary Millsap which shows how you could flush the buffers out with an alter system command (9i only) - to save restarting the database when running performance trials I have searched for it everywhere to no avail So if anybody recalls what it was please let me know (I assume it is undocumented as I cannot find anything in the manual) Thnaks John -Original Message- Sent: 21 July 2003 15:45 To: Multiple recipients of list ORACLE-L Hey Bruce, Any possibility of making the bodies of the FatCity archived messages searchable, instead of just the subject? That's the only reason why I even bother with the orafaq search. Thanks, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Bruce A. Bergman [mailto:[EMAIL PROTECTED] > Sent: Saturday, July 19, 2003 2:49 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: faq broke? -- Other options > > > Don't think that the only searchable archives are on orafaq. > Fat City has the definitive archives that date back to the > start of the list here. You should consider using the > archives here as well. > > You can find them here: http://www.listguru.com > > thanks, > bruce -- 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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: rman legato 32 vs 64 bit conflict.
Joe. We had that problem on several servers We installed the 64 bit libs as libnwora.64 and proceeded to relink using those. We had 32 bit installed first (Oracle 8.1.7x) and we needed 64 bit for the 9i databases The symbolic link required is below mv $ORACLE_HOME/lib64/libnwora.so $ORACLE_HOME/lib64/libnwora.so.save 2>>/dev/null ln -s /usr/lib/libnwora.64.so $ORACLE_HOME/lib64/libobk.so John -Original Message- Sent: 07 July 2003 22:34 To: Multiple recipients of list ORACLE-L Anyone there in oracle land running both 32 bit and 64 bit oracle and using rman w/legato. I assume i need 64 bit libs for 64 bit oracle and 32 bit libs for 32 bit oracle of the legato software?, the unix admin says legato wont install the 64 bit since it says its already installed. Any ideas would be appreciated. thanks, joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: any script to parse alert log ?!
This was posted by David Hill on 21/3/3. Hope it helps John Here a fast little script I wrote that can be ran every minute Emails only when it finds a new message And pages if it finds a new message between 6pm and 5am FPATH=/prod/dba/scr autoload f_xmail logfile=/prod/dba/logs/`cat /prod/dba/sys_data/newpath`/misc_logs/chk_alerts.log echo "$(date +%y-%m-%d_%H:%M) BOK - $$" >> $logfile for i in DB1 DB2; do typeset -l sub=$(echo $i |cut -c1) alert_log="/prod/dump/$sub/alert_$i.log" if [ -f $alert_log ]; then #if you don't find an alert log its ok just exit line_file="/prod/dump/$sub/ora_line" if [ ! -f $line_file ]; then echo 0 > $line_file fi last_line=$(cat $line_file) new_line=$(cat $alert_log |wc -l) if [ $new_line -lt $last_line ]; then#then the alter log must have been deleted and recreated last_line=0 fi if [ $(tail +$((last_line + 1)) $alert_log |grep ORA- |wc -l) -gt 0 ]; then echo "$(date +%y-%m-%d_%H:%M) NEW ORA Messages Found" >> $logfile (echo "From: chk_alerts.ksh" echo "Subject: ORA messages found for $i \n" cat $alert_log ) |mail [EMAIL PROTECTED] #mail the entire log when new messages are found if [[ $(date +%H%M) -lt 0600 || $(date +%H%M) -gt 1800 || $(date +%u) -gt 5 ]]; then #nobody is around page also echo "$(date +%y-%m-%d_%H:%M) Nobody is at work so paging also" >> $logfile f_xmail pager ORA messages found for $i fi fi echo $new_line > $line_file else echo "$(date +%y-%m-%d_%H:%M) NO alert_log found to scan" >> $logfile fi done -Original Message- Sent: 07 July 2003 10:39 To: Multiple recipients of list ORACLE-L Guys, I need a parse alert log and mail me if there is any error. Hope u guys would have some script for the same. i have 8i and 9i instances spread across windows 2000 network. even if u guys have script for unix,just pass it on to me. let me modify and try it. Thanks and Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Migration to 9.2.0.3.0 (64 Bit) on Solaris 8 issues
0.03100.0 00 INDEX 9,4310.4 9,4310.4 00 SQL AREA 1,137,4675.8 21,417,4020.3 5,4630 TABLE/PROCEDURE 353,6980.2717,1640.7 3,5050 TRIGGER 610.0 616.6 40 - 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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Urgent: Need Oradim.exe for version 8.1.6
; >Privileged/Confidential information may be contained in > this message. > If you are not the addressee indicated in this message >(or responsible for delivery of the message to such person), > you may not copy or deliver this message to anyone. > In such case, you should destroy this message and kindly > notify the sender >by reply e-mail or by telephone on (61 3) 9612-6999. >Please advise immediately if you or your employer does not > consent to > Internet e-mail for messages of this kind. > Opinions, conclusions and other information in this message > that do not relate to the official business of > Transurban City Link Ltd > shall be understood as neither given nor endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>> > >>>>>>>>>>>>>>>>>> > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Richard > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rule Based Optimizer
-- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(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: K Gopalakrishnan > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com > >San Diego, California-- Mailing list and web hosting services > >- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > > > > > > > > > > > > > _ Yep. Here's the official statement from Oracle about RBO in 10i: "The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has continued to be available for backwards compatibility. Oracle Oracle10i will support only one optimizer, and all applications running on that release should use that optimizer. " What's behind this wording? I don't really know, but I can guess. There's a myth that the RBO hasn't been enhanced since the introduction of CBO in 7.0. That, I learned one day in Steve Adams' car, is not true. It has been enhanced to know about new segment types, etc. A fun (well, fun...) exception to this is partitions. If you're using partitions you're using CBO. So I read it as: "Yeah, we haven't really enhanced the RBO since 7.0 except to allow it to recognize IOT's and other new segment types. In 10i we won't even do that." Mogens Freeman Robert - IL wrote: RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- 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
RE: dbms_stats via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my
worked example I had CASCADE>=TRUE whereas it should have been
CASCADE=>TRUE. A subtle difference but one that caused me (and fellow listers) no end
of hassle.
So all the answers that I said were incorrect were not, they had all replicated my
original typo.
But one thing I proved was that you can mix and match positional and naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=>TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =>'
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent => 10,
block_sample => FALSE, CASCADE >= TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
"positional" method with "naming": either you use formal parameters, or
not.
It seems like you need "naming".
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent
=>10,cascade=>true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE>=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE>=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other
RE: Deleting Statspack tables.
Tim Gorman has produced an excellent package sppurpkg.sql which certainly does the business for me It can be found at http://www.evdbt.com/sppurpkg.sql John -Original Message- Sent: 11 June 2003 00:35 To: Multiple recipients of list ORACLE-L Version : 8.1.7.3. I am not sure how it is working for you just by deleting from stats$snapshot and we do not have 8.1.6 here. Thanks -Original Message- Sent: Tuesday, June 10, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Reddy - You didn't mention your Oracle version. I am on 8.1.6 and I don't think those scripts are available there (sorry, busy day, no time to research). So I just delete from stats$snapshot. It seems to remove data from the associated tables just fine. First I select the snap_id and snap_time from stats$snapshot to determine which snapshots to remove. It runs slowly (because of all the child tables?), so I just remove a hundred or so at a time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH50 STATS$SYSSTAT 51 TABLE Size in MB -- -- STATS$PARAMETER56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =>'
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent => 10,
block_sample => FALSE, CASCADE >= TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
"positional" method with "naming": either you use formal parameters, or
not.
It seems like you need "naming".
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent
=>10,cascade=>true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE>=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE>=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
dbms_stats via dbms_job - syntax question
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent=>10,cascade=>true);
but trying to get that into an dbms_job is destroying my brain.The syntax I am trying
is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE>=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the line but 2 of
them around the schema name as that is a varchar2. Ideally I think I want 2 single
quotes around the cascade but I cannot get it to work properly. The above example
submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE>=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his web site
but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: 9.2.0.3 online index builds resolved (we hope)
Stephen, (BWhen you say there IS an problem with index rebuilds what do you mean? (BHave Oracle acknowledged it as a bug? (B (BYour work around basically slows the volume of data being passed through the buffer (Bcache, presumably allowing other users to get on with their work. (BI assume that this causes the index rebuild to take longer than it did with with a (Bhigher MBRC on 8.1.7.4 - is that true ? (B (BJust trying to determine the extent of this issue (B (BJohn (B (B-Original Message- (BSent: 03 June 2003 18:05 (BTo: Multiple recipients of list ORACLE-L (B (B (B (BWell folks, there IS an issue with online index builds in 9.2.0.3. Not only (Bhave we experienced "normal" SQL getting blocked, but we have seen index (Bbuilds hang indefinitely -- while holding the blocking lock (of course!). (BThe work around is (we hope): (B (Balter session set db_file_multiblock_read_count=1; (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Stephen Lee (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Hallas, John, Tech Dev (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
RE: Function Based Index - Not Used ???
Naveen , these parameters look to be already set
Doesn't FBI come in with 8.1.7 and Prem is on 8.16 and a comapatible of 8.10 is in
place
Failing that, as my second guess, 2 privileges need to be assigned to the user
(QUERY_REWRITE and CREATE INDEX). The 2nd one is obviously in place to allow the index
to be created but I don't know about the first
The final point is whether the name column is not null , again I assume not but it is
a possibility
John
-Original Message-
Sent: 29 May 2003 08:50
To: Multiple recipients of list ORACLE-L
Don't remember but you need a few parameters set appropriately for the FB
indexes to work.
I guess one of the parameters is QUERY_REWRITE_ENABLED which should be set to
TRUE and you also need to set QUERY_REWRITE_INTEGRITY to some appropriate
value.
Regards
Naveen
> -Original Message-
> From: Prem Khanna J [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 29, 2003 12:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Function Based Index - Not Used ???
>
>
> Guys,
>
> create table Tab1 ( name varchar2(100),age int,state
> varchar2(100),country varchar2(100));
>
> insert into tab1 values ('SCOTT',25,'TN','India');
>
> I have 20,00,000 records like above.
>
> create index idx1 on tab1 (upper(name));
>
> analyze table tab1 compute statistics;
> analyze index idx1 compute statistics;
>
> select age from tab1 where upper(name)='SCOTT'; ---> this
> will return around 50 records.
>
> -
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2458
> Card=262146 Bytes=2097168)
> 10 TABLE ACCESS (FULL) OF 'TAB1' (Cost=2458
> Card=262146 Bytes=2097168)
>
> --- INIT.ORA --
> optimizer_mode=choose
> compatible=8.1.0
> query_rewrite_enabled=true
> query_rewrite_integrity=trusted
> ---
> It's Oracle 8.1.6/Win2K.
>
> When i add a HINT, IDX1 is used.Why is it so ?
> Why this SELECT does not use index IDX1 w/o a hint ?
>
> TIA.
> Jp.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or dissemination
of this message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper, untimely
or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: alter table add
Have you updated your cursor to add the new column? John -Original Message- Sent: 03 April 2003 21:54 To: Multiple recipients of list ORACLE-L I added a column using alter table add ... i can see it from sql . can select it from sql . But if I try to use it in a pl/sql block or procedure .It can't find that column . is it a bug ? 8.1.6. hp-ux -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: certifications was Re: Remote DBA
Say what you think Joe, When I have been looking for contacts several agencies have asked me to take those tests as a means of ensuring that I know what I am supposed to know. They act as a pre-interview filter If it sorts the wheat from the chaff then I don't think they perform a bad service in that respect. However as far as being touted as a professional qualification as Ramesh is using them then I think I would forget about it. I think anybody who contributes to this list claiming to be this, that or the other is setting himself up for a fall. Dennis has got the idea by having 40% OCP in his sig (however that has been there so long now that the exams probably need re-taking now ) John -Original Message- Sent: 03 April 2003 12:24 To: Multiple recipients of list ORACLE-L Ramesh, since in your sig line you say you're brainbench and brainbuzz certified, do you really think those tests have any meaning, i took them and thought they were a joke. joe Ramesh Papnoi wrote: >It can be Unix as well as Windows. As I have to support various DB's > >Best Regards >Ramesh D. Papnoi http://www22.brinkster.com/rpapnoi >(Brainbench & Brainbuzz certified Oracle 8/8i DBA & Developer) > >-Original Message- >Adar >Sent: Thursday, April 03, 2003 2:39 PM >To: Multiple recipients of list ORACLE-L > > >You did not mentioned what OS you are working on. >If the remote database is on your network, even via wan, you can activate >all the tools from your desktop. >We use RAS to connect to remote servers without network connections and then >you have SLOW network connection and your tools will work. >If the need arise you can use Netop or other PcAnywhere to take control of >the server and do your work from there. > >We are on windows servers and clients. > >Yechiel Adar >Mehish >- Original Message - >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Sent: Wednesday, April 02, 2003 10:34 PM > > > > >>Dear All >>Can anyone of u throw light on how remote dbas work. I would appreaciate >> >> >if > > >>any whitepaper / document on this topic is sent to me directly at >>[EMAIL PROTECTED] or [EMAIL PROTECTED] >> >>TIA >> >>Best Regards >>Ramesh D. Papnoi >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>-- >>Author: Ramesh Papnoi >> INET: [EMAIL PROTECTED] >> >>Fat City Network Services-- 858-538-5051 http://www.fatcity.com >>San Diego, California-- Mailing list and web hosting services >>- >>To REMOVE yourself from this mailing list, send an E-Mail message >>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>the message BODY, include a line containing: UNSUB ORACLE-L >>(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: Yechiel Adar > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > > > -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the "CACHE" -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: Anyone whom could explain this?
Is this procedure being run with a trigger Roland? If so then the :new refers to the value of each row that is being inserted from the table To explain better if an update triggger is on table roland with columns (road,town,country) and one row is in the table with values Main Rd, Leeds, England and the row is being updated to be New_Main Rd, Leeds, England then the following values will be in place old:road = Main Rd new:road = New_Main Rd old:town = Leeds new:town=Leeds old:country = England new:country=England. That is about 100% of my PL/SQL knowledge so I hope I am correct John -Original Message- Sent: 03 April 2003 11:09 To: Multiple recipients of list ORACLE-L Hallo, anone whom could explain what new. means in this pl/sql procedure. for instance: new.kundeoms := new.kundeoms + (in_diff); new.mva := new.mva + (in_diff_mva); How do I get hold of it so i can see which sql it runsor is new.kundeoms just a value from a table? PROCEDURE add_kundeoms(in_diff in number, in_diff_mva in number, in_diff_brtkr in number, in_flagg in varchar2) IS lDetaljFunnet boolean; BEGIN IF in_flagg = '0' THEN new.kundeoms := new.kundeoms + (in_diff); new.mva := new.mva + (in_diff_mva); new.kostverdi := new.kostverdi + (in_diff - in_diff_mva - in_diff_brtkr); ELSIF in_flagg = '1' THEN new.utg_pant := new.utg_pant + (in_diff); ELSIF in_flagg = '9' THEN new.shop_in_shop := new.shop_in_shop + (in_diff); ELSIF in_flagg = '5' THEN new.rest_oms := new.rest_oms + (in_diff); END IF; BEGIN db_dagoms_detalj.find(new.dagoms_id, in_flagg); lDetaljFunnet := TRUE; EXCEPTION WHEN OTHERS THEN lDetaljFunnet := FALSE; END; IF lDetaljFunnet THEN db_dagoms_detalj.edit; db_dagoms_detalj.add_kundeoms(in_diff, in_diff_mva, in_diff_brtkr); db_dagoms_detalj.modify; db_dagoms_detalj.close; END IF; END; END; Thanks in advance Roland -- 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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Job Task Analysis
ables 4. Understand and implement different Index options available in Oracle including B*Tree, Bitmap, reverse key, and function based 5. Implement integrity constraints 6. Understand the implementation of Workspace Management for table Versioning G.TUNING AND TROUBLESHOOTING 1. Use data dictionary and Dynamic Performance views to Monitor the Database and the Instance 2. Collect and analyze relevant database performance information 3. Identify and implement appropriate solutions for database performance problems 4. Diagnose and resolve locking conflicts 5. Diagnose Oracle Net problems 6. Perform capacity planning 7. Control system resource contention using the Oracle Resource Manager 8. Implement profiles to limit resource consumption. 9. Use vendor support services when necessary 10. Communicate with users about problem resolution and proper system usage H. SCALABILITY 1. Demonstrate an appreciation of Real Application Clusters Architecture Scaleup and Speedup capabilities 2. Demonstrate an Appreciation of Shared Server Architecture 3.Demonstrate an appreciation of parallel execution (Parallel Query,DML, DDL, and Recovery) 4. Understand Scaleup using Oracle Net Connection Pooling, Connecton Multiplexing I. HIGH AVAILABILITY 1. Demonstrate an appreciation of Real Application Clusters Architecture High availability capabilities Including RAC Guard 2. Understand Connect time Failover and Load Balancing 3. Understand Transparent Application Failover 4. Understand Data Guard Architecture and Physical and Logical Standby Databases 5. Understand Replication using Advanced Replication and Oracle Streams J. BUSINESS INTELLIGENCE 1. Understand and Use Oracles Extranction, Transformation, and Load facilities including External Tables, and Incremental Change Data Capture 2. Understand the use of Star Schemas, Bitmap Indexes and Bitmap Join Indexes 3. Understand Summary Management using Materialised Views, and Oracle Dimensions for Realtional OLAP processing 4. Understand Oracles implementation of Analytic Workspaces for Multidimensional OLAP processing Thanks in advance for your help, Scott __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Scott INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy
So what are you doing this afternoon after you have mastered the SQL Server gui this morning? -Original Message- Sent: 15 March 2003 09:44 To: Multiple recipients of list ORACLE-L I put them on http://www.vanzanen.com/rman.zip They are oracle 8.0 (if memory serves me right) so they won't work with 9i. I'll see if I can find the time to do the same for 9i one of these days (have to learn SQL Server first) Jack -Original Message- Sent: vrijdag 14 maart 2003 20:24 To: Multiple recipients of list ORACLE-L I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup & Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup & Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Backup Strategy
I have found Joe Testa's site has a good set of RMAN scripts (I think they came from Jack van Zanen off this list), quite simple but they give the syntax for most of the commands you will want The link was http://www.oracle-dba.com but that is no longer working Where have you put them Joe?? John -Original Message- Sent: 14 March 2003 16:34 To: Multiple recipients of list ORACLE-L Jay If you want a good book to get up to speed on RMAN, buy Oracle9i RMAN Backup & Recovery by Robert Freeman and Matthew Hart If you want to compare the steps for various recovery scenarios between RMAN and user-managed recovery, get Oracle Backup & Recovery 101 by Smith and Haisley. It has you create a small test database and then run various backup and recovery steps for various types of failures and recoveries. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Dear All, Iam entitled the responsibility to come out with a plan for Backup (using RMAN) for our forthcoming data centre operations. Could someone help me on this? I would also like to know the steps for Recovery in the case of a Redo Log member failure, using RMAN and the traditional Recovery commands from SQLPLUS. TIA . Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OCP
Well said Kirti - Darell, you wil fail every OCP exam you take with 50%. You need a minimum of about 65% and up to 75% on others (don't know why they vary so much) John -Original Message- Sent: 12 March 2003 13:59 To: Multiple recipients of list ORACLE-L >>> You only need to get 50% of the questions right on the upgrade tests. Why not set the goal to it get 100% right? ;) - Kirti -Original Message- Sent: Wednesday, March 12, 2003 7:29 AM To: Multiple recipients of list ORACLE-L I didnt realize that you might be able to do the 8i one without handing $2k to oracle for their class and taking the upgrade exams is a better path. anyone know if this is possible? You only need to get 50% of the questions right on the upgrade tests. > > From: "Darrell Landrum" <[EMAIL PROTECTED]> > Date: 2003/03/12 Wed AM 07:48:43 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: OCP > > Jay, > > One thing to consider is that just starting the 9i track and if you've not taken any > OCP exams prior to September of 2002, there is a requirement to attend one 9i > training class in addition to the exams. However (you'll probably need to call > Oracle to get a solid answer to this), if you can still take the 8i track with no > classroom training and then take the 9i upgrade exam (if they allow this with no > classroom training), this would be your least expensive route since the classes are > quite expensive. The down side is a total of six exams instead of just four. > If your company is willing to send you to one of the 9i classes, I would take > advantage of that and just go for the 9i track. > > Good luck! > Darrell > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: "Is Anybody Out There" - Pink Floyd
Paula, I am currently having problems where we have 32 bit Oracle (8.1.7.4) backing up to networker successfully via RMAN. However 64 bit Oracle (9.2) will not work against the same networker installation ORA-27211: Failed to load Media Management Library See note 209590.1 on Metalink for more information. It looks as if the libnwora.so needs to be linked to a libobk.so link. I am still investigating this but I will let you know John PS RMAN - 'Another brick in the wall ?' -Original Message- Sent: 12 March 2003 21:50 To: Multiple recipients of list ORACLE-L Guys, Does anyone know if I can use a 32-bit RMAN catalog and database running networker 32 bit to backup a 64 bit database link to networker 64 bit libraries on a remote host? I am in the process of migrating to both 64 bit and 9i. Help? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Make first character Versal
Title: RE: Make first character Versal The following sql gives the first character of a column. I am sorry but I don't know what you mean by VERSAL select substr(version,1,1) from database_control John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 27 March 2002 11:59 To: Multiple recipients of list ORACLE-L Subject: Make first character Versal Hallo, I know this question sound a bit simple, but can anyone give me a select statement which make the first character of the word in a field Would appreciate very much. I have checked the manual but cant get it right. I am starting with this sql statement: select (substr (namn,0,1)) from test to pick outthe first character and I want that first character to be a VERSAL. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Archiving Data Strategies.
Title: RE: Archiving Data Strategies. As you say Ian partitioning is a obvious answer as I imagine the billing data will be quite easy to range partition using dates. However why go to 9i, 8i has many partitioning options and it may be an easier upgrade as well as a leap that management might find easier to agree to. Unfortunately removing data when there is a lot of RI involved can be quite complex and needs good knowledge of the schema and data dependencies. This knowledge is not always available and sometimes the cost in terms of man-hours is not one that management is prepared to pay. HTH John -Original Message- From: Biddell, Ian [mailto:[EMAIL PROTECTED]] Sent: 27 March 2002 09:53 To: Multiple recipients of list ORACLE-L Subject: Archiving Data Strategies. Hi All, I previously posted this question to the Lazydba List and got a couple of replies, but thought I would also send it to this list as well to see if I can just get a couple more (so excuses to those people that have already seen it) I am currently discussing with a customer their requirements for archiving data as their system is 4 years old and billing data is piling up which obviously is affecting performance. I am pushing for an Oracle upgrade, they are currently on 7.3.4 and I am trying to get them to go to 9i. The main reason for this is so they can use partitioning. My question to the List is to try and find out other people's experiences in archiving complex and integral data and whether most have gone the partitioning path or some other path (ie. Something like separate tables and data migration). So I would appreciate anyones comments, the path they chose, database size etc. With Thanks Ian Biddell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Biddell, Ian INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Convertion from Figures to Words
Title: RE: Convertion from Figures to Words
I can get the following to work with a date format but I cannot see how to do it with just plain numbers.
If nobody else comes up with an answer then I suppose you could use DECODE or populate a reference table with each number and it's alphabetic translation
HTH
John
1 SELECT TO_CHAR(TO_DATE('27-OCT-01','DD-MM-'),'DDSP-MMSP-YYSP')
2* from dual
SQL> /
TO_CHAR(TO_DATE('27-OCT-01','DD-M
-
TWENTY-SEVEN-TEN-ONE
-Original Message-
From: Ahmed Gholam Hussain [mailto:[EMAIL PROTECTED]]
Sent: 27 March 2002 09:48
To: Multiple recipients of list ORACLE-L
Subject: Convertion from Figures to Words
Dear Listers ,
I need to convert Figures to Words ...EX : 123 to One Hundred Twenty
Three
Currently I am using a writen function to do that ..But it has limitations
...
Any suggestions regarding that ?
Thanks a lot in advance
Regards
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ahmed Gholam Hussain
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: Concatenating formated number and date doesn't work
Title: RE: Concatenating formated number and date doesn't work Well spotted Catherine. I got it working by wrapping an LTRIM round the TO_CHAR(12345,'099') line John -Original Message- From: CHAN Chor Ling Catherine (CSC) [mailto:[EMAIL PROTECTED]] Sent: 26 March 2002 08:18 To: Multiple recipients of list ORACLE-L Subject: RE: Concatenating formated number and date doesn't work Hi Aleem, I think it's because of the space in front. Try SELECT ';'||TO_CHAR(12345,'099')||';' from dual and you will notice that there is a space in front. 1* SELECT ';'||TO_CHAR(12345,'099')||';' from dual SQL> / ';'||TO_CH -- ; 0012345; The problem does not lies in TO_CHAR( SysDate, 'MMDD' ). Hope it helps. Regds, Catherine -Original Message- Sent: Tuesday, March 26, 2002 3:33 PM To: Multiple recipients of list ORACLE-L Hi, In a database procedure that accepts employee_ID as numeric parameter I have the following code that doesn't work together, it however works separatly, I wonder why? PROCEDURE abc ( emp_ID IN NUMBER := 12345 ) AS Emp_Char_ID VARCHAR2(7); Bar_Char_Date VARCHAR2(8); Emp_Bar_Code VARCHAR2(15); BEGIN Emp_Char_ID := TO_CHAR( emp_id, '099' ) Bar_Char_Date := TO_CHAR( SysDate, 'MMDD' ); Emp_bar_code := Emp_Char_ID || Bar_Char_Date; /* The concatenation line compiles fine, but at execution time gives error: ORA-06502: PL/SQL: numeric or value error */ UPDATE Employee SET Bar_Code = Emp_Bar_Code WHERE Employee_ID = emp_id; END; The same procedure works fine after the following modifications Removed format mask on emp_id (First line of the BEGIN section) Modified the update statement and used LPAD( Emp_Bar_Code, 15, '0') The question is why the formatted number conversion gives error in concatenation with date converted to character. TIA! Aleem -Original Message- Sent: Tuesday, March 26, 2002 9:48 AM To: Multiple recipients of list ORACLE-L I suspect that you need a new version of Veritas Volume manager, I would check around the Veritas website for info. or there is a driver you need to load in the boot of the installation process. If you are still mystified try asking your question over on the sunmangers list. www.sunmanagers.org you almost certainly will find an answer there Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical Consultant WWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom "The People, The Experience, The Vision" = "CHAN Chor Ling Catherine (CSC)" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 26/03/2002 11:33 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Fax to: Subject: OT : Upgrade Unix OS with Veritas Volumn Manager Hi Unix Gurus, My UNIX administrator has to upgrade the OS from 5.6 to 5.8. We are using Veritas Volumn Manager. The upgrade fails because we are not familiar with Veritas Volumn Manager. Has anyone upgraded SUN-Solaris Unix OS from 5.6 to 5.8 with Veritas Volumn Manager ? Please help. Thanks. Regds, New Bee -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing List
RE: Cronjob misbehaving
Title: RE: Cronjob misbehaving
It looks as if your ORACLE_HOME is not being set up.
Try either or both of the following
1) Amend your cron job to look like the following (I assume you are running this as user oracle)
0 5 * * * su - oracle 'usr/scripts/recompile.sh' > /ops/scripts/recompile.log 2>&1
Otherwise set the environment up in the script
#!/bin/ksh
export ORACLE_SID=
export ORACLE_BASE=
export ORACLE_TERM=vt100
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin
Then the script
HTH
John
-Original Message-
From: Simon Waibale [mailto:[EMAIL PROTECTED]]
Sent: 22 March 2002 08:18
To: Multiple recipients of list ORACLE-L
Subject: Cronjob misbehaving
Hi all,
Thanx for all the good work U R doing for Oracle.
I have a misbehaving cronjob
-Cron Entry
0 5 * * * /usr/scripts/recompile.sh > /ops/scripts/recompile.log 2>&1
-recompile.sh
#!/bin/ksh
## Program name : recompile.sh
## Purpose : Recompile Invalid Database Objects for specified Shema in
FLAG
## Author : C.S Waibale Simon
## Date written : 2002-03-19
#for i in FLAGPASS CALLSPASS FLAGPASS CH1PASS CH2PASS CH3PASS ; do
#j:=1
#$i=${i:-$(crypt flag < passwds |awk '{print $j}')
#done
SQLPLUS=/ops/product/817/bin/sqlplus
#Do not export the password variables !
FLAGPASS=${FLAGPASS:-$(crypt flag < /usr/scripts/passwds |awk '{print $1}')}
CALLS_PASS=${CALLS_PASS:-$(crypt flag < /usr/scripts/passwds |awk '{print
$2}')}
WHPASS=${WHPASS:-$(crypt flag < /usr/scripts/passwds |awk '{print $3}')}
echo "Recompiling Invalid Database Objects in the FLAG instance"
#This can be sustitued with generic code
$SQLPLUS flag_calls/$[EMAIL PROTECTED] @/ops/rom/recompile.sql
$SQLPLUS flag/$[EMAIL PROTECTED] @/ops/rom/recompile.sql
$SQLPLUS flag_wh/$[EMAIL PROTECTED] @/ops/rom/recompile.sql
-recompile.log
Recompiling Invalid Database Objects in the FLAG instance
Message file sp1.msb not found
Error 6 initializing SQL*Plus
Message file sp1.msb not found
Error 6 initializing SQL*Plus
Message file sp1.msb not found
Error 6 initializing SQL*Plus
What could be broken ??
The script runs correctly from comand prompt.
---
+---+
C.S Waibale Simon
MTN-Uganda, 8th Floor UDB Building
Cell: +256 77-212655,http://mtn.co.ug
+---+
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Simon Waibale
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: cannot create bitmap index "feature not enabled"
Title: RE: cannot create bitmap index "feature not enabled" Forget my previous reply regarding query_rewrite privileges - I misread it as a FBI question not a BMI one - enough of these TLA BFN! John -Original Message- From: Joe Raube [mailto:[EMAIL PROTECTED]] Sent: 20 March 2002 15:04 To: Multiple recipients of list ORACLE-L Subject: Re: cannot create bitmap index "feature not enabled" It's only available in Enterprise Edition, see docs at http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76962/ch4.htm#52939 -Joe --- [EMAIL PROTECTED] wrote: > Hi All, > > Oracle 8.1.6 SE > > I tried to create a bitmap index and but get feature not enabled. > This > cannot be only available in EE. Is there some script I need to run > to > enable creation of bitmap indexes. I have searched MetaLink but see > no > restrictions. > > Thanks > Rick > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: cannot create bitmap index "feature not enabled"
Title: RE: cannot create bitmap index "feature not enabled" Try granting the query_rewrite and query_rewrite_enabled (check spelling first) privileges to the user -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 20 March 2002 14:28 To: Multiple recipients of list ORACLE-L Subject: cannot create bitmap index "feature not enabled" Hi All, Oracle 8.1.6 SE I tried to create a bitmap index and but get feature not enabled. This cannot be only available in EE. Is there some script I need to run to enable creation of bitmap indexes. I have searched MetaLink but see no restrictions. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: More info on sql query running slow
Title: RE: More info on sql query running slow The query in itself seems quite well optimised in my opinion, although 30 seconds does seem quite long relative to what is possible. What I do not understand is why the query uses the PCON_CMPY_FK index on the PHY_CONTRACTS table when that field (company_id) is not the obvious one to be queried. I would have expected to see it using the PK by searching on PHY_CONTRACT_ID. Does a index on all 3 columns, in the same order as the query help matters? John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 20 March 2002 13:58 To: Multiple recipients of list ORACLE-L Subject: More info on sql query running slow Hi All, Oracle 8.1.6 I have 2 tables which have been analyzed. The query takes about 30+ seconds to run. If I run it many times it is faster as data gets loaded into buffer cache. I want to optimize when that is not the case. All the fields in the query have a separate index created EXCEPT for ACCRUED_AND_PAID.hold_payment_flag. Phy_Contracts has 10,466 rows. Accrued_and_Paid has 820,919 rows. Here is output from explain plan SELECT STATEMENT Cost = 1382 SORT AGGREGATE NESTED LOOPS TABLE ACCESS BY INDEX ROWID PHY_CONTRACTS INDEX RANGE SCAN PCON_CMPY_FK TABLE ACCESS BY INDEX ROWID ACCRUED_AND_PAID INDEX RANGE SCAN ADPD_PCON_FK Any ideas what I can do to speed this query. SELECT COUNT(a.phy_contract_id) FROM accrued_and_paid a, phy_contracts b WHERE a.hold_payment_flag = 'Y' AND b.phy_contract_id = a.phy_contract_id AND b.company_id = 16 AND b.contract_type = 'IC'; List of single column index on PHY_CONTRACTS INDEX_NAME COLUMN_NAME -- - PCON_CONTRACT_TYPE_IDX CONTRACT_TYPE PCON_CMPY_FK COMPANY_ID PCON_PK PHY_CONTRACT_ID List of single column index on ACCRUED_AND_PAID INDEX_NAME COLUMN_NAME -- -- ADPD_PCON_FK PHY_CONTRACT_ID Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Oracle Certification Upgrade 8 to 8i
Title: RE: Oracle Certification Upgrade 8 to 8i Manoj I am in the same situation and looking at the IZO-020 exam as well. It is 8 that is due to retire , IZO-020 is an 8i exam. You have plenty of time to take the exam. John -Original Message- From: Manoj Jain [mailto:[EMAIL PROTECTED]] Sent: 20 March 2002 08:58 To: Multiple recipients of list ORACLE-L Subject: Oracle Certification Upgrade 8 to 8i Dear all, I have Oracle8 DBA certification. To upgrade there is following exam. 1Z0-020 ORACLE 8I: NEW FEATURES FOR ADMINISTRATORS Oracle announced : Oracle8 OCP Track Retirement As of March 31, 2002 Do anyone of you know if they will also retire 1Z0-020 ORACLE 8I: NEW FEATURES FOR ADMINISTRATORS on March-31-2002? If that is the case I will have to complete this exam by Mar-31-2002. Please let me know. Also please advise me on good study material and useful resources/links for this Upgrade Exam. Thanks Manoj Oracle7.3, Oracle8 OCP DBA Chauncey Certified DBA __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manoj Jain INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Identification of tables NOT being used in the System.
Title: RE: Identification of tables NOT being used in the System.
There was a note previously on a similar theme which I have posted below.
The dba_tab_modifications does not show when a table has been selected from which could be a problem for reference data type table which can easily be overlooked.
+ Previous notes
Tony is thinking along the same lines I was. Go ahead and capture the
outlines and then querying the DD where the indexes aren't found in the
OL$HINTS.HINT_TEXT column could tell you what indices haven't been used.
Something like:
select i.owner, i.table_name, i.index_name
from dba_indexes i, outln.OL$HINTS h
where index_name not like ('%'||hint_text||'%')
and owner not in ('SYS','SYSTEM','PORTAL30','DES6I')
Just add the schema's to exclude. Only as good as the code coverage from
your collection timeframe but still seems like a pretty solid approach.
Anyone tried this? I've kicked it around but have never actually tried it in
anything other than a test environment.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
-Original Message-
Sent: Monday, February 04, 2002 4:31 PM
To: Multiple recipients of list ORACLE-L
Another option you have if your Oracle version is high enough is to use
Stored Outlines. Enable automatic generation of stored outlines for a full
processing cycle as defined by the application (full month, Qtr, etc.) Then
extract all of the indexes used during that cycle from OL$HINTS.HINT_TEXT
(i.e.. WHERE HINT_TEXT LIKE 'INDEX%'). If all of your application code has
been traversed in the cycle then this list will be pretty darn close to real
usage. Even if you can't wait until year-end processing, you can eliminate
the bulk of code to be mined for embedded SQL and focus on those once-a-year
programs.
HTH
Tony Aponte
-Original Message-
Sent: Monday, February 04, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Hi
I there any view which can tell us which indexes are not in use?
Thx
-Seema
-Original Message-
From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
Sent: 15 March 2002 21:49
To: Multiple recipients of list ORACLE-L
Subject: RE: Identification of tables NOT being used in the System.
ALTER TABLE tablename MONITORING;
Every three hours or so, the SYS.DBA_TAB_MODIFICATIONS view gets updated
with the tables UPDATEs, DELETEs, and INSERTs, as well as wether or not the
table has been TRUNCATEd since the last time it was DBMS_STATS'd. The view
also gets updated on a SHUTDOWN, except for SHUTDOWN ABORT. I believe that
if there's no activity on the table you set for MONITORING, that there will
not be a row for it in this view.
And if you use CBO, you'll want to save the rows from DBA_TAB_MODIFICATIONS
to your own table before using DBMS_STATS. It will zero the counters in the
view for the tables it's run against.
Also, there's very little overhead, at least according to Oracle.
HTH! You might want to look this up in Metalink or the Oracle docs, too.
Enjoy! :)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
Sent: Friday, March 15, 2002 3:08 PM
To: Multiple recipients of list ORACLE-L
In our production database environment, I have a list of about 1000 tables
,for which we want to find if these tables are being used by anyone. How it
can be done. One of the ideas is that we start database auditing on these
tables for a considerable period of time say one month. Then for those
tables for which there is nothing in database audit, we assume that tables
are not being used. For this option I would like to know if we put auditing
on these 1000 tables, how much extra burden it is add onto the system (CPU,
Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00.
If there are some other alternatives, please let me know.
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have receiv
RE: Fav. Urban Legend...
Title: RE: Fav. Urban Legend... Patrice, Given the current state of the job market I am not sure if this is myth or fact John -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 12:18 To: Multiple recipients of list ORACLE-L Subject: RE: Fav. Urban Legend... That as Oracle software becomes larger, fewer DBAs are required. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 13, 2002 12:28 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fav. Urban Legend... oh man, mine has to be what is probably Jeremiah's as well: the myth that Oracle doesn't write to the database files when you are in hot backup mode --- "Freeman, Robert " <[EMAIL PROTECTED]> wrote: > I'm putting the final touches on my IOUG-A presentation (I got an > extension > for those who > realize that I'm late on it!)... I'm doing Oracle Urban Legends. I've > got > several in my presentation but I thought I'd ask here, before I put > the > presentation to bed, what your favorite (or the one you find the most > irritating) Oracle Urban legend was > > RF > > Robert G. Freeman - Oracle8i OCP > Oracle DBA Technical Lead > CSX Midtier Database Administration > > The Cigarette Smoking Man: Anyone who can appease a man's conscience > can > take his freedom away from him. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Freeman, Robert > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Anyone use V$SESSION_LONGOPS ???
Title: RE: Anyone use V$SESSION_LONGOPS ??? There have been a few posts today which are repeat posts from a few days ago -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 12:53 To: Multiple recipients of list ORACLE-L Subject: RE: Anyone use V$SESSION_LONGOPS ??? Is it just me, or am I having a slight case of de ja vu here ;P -Original Message- Lewis Sent: 13 March 2002 11:34 To: Multiple recipients of list ORACLE-L No version numbers, and no O/S details. Cary Millsap mentioned to me a little while ago that on one of his linux platforms the values you got from timed_statistics seemed to be a very good random number generator ;) Possibly this is just a 32-bit/64-bit misalignment in code - I've seem similar silly numbers appearing for that reason. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 07 March 2002 21:36 |Well here's what's curious... |Notice the changing SID-serial# with the same sql address and hash. |Notice how elapsed seconds gyrates. Elapsed seconds goes from zero to |447,507,719 yet there were only a few seconds between the queries. How can a | |session with 447,507,719 seconds of elapsed time suddenly appear??? Why are |there sessions going back to November when the computer hasn't even been up |that long? None of the sessions in V$SESSION_LONGOPS are in V$SESSION. The |sql |address and hash is not extant in V$SQL, V$SQLAREA, etc. Is this view |supposed to behave this way? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Time for a reorg?
Title: RE: Time for a reorg? Having a sulk Mark? :-) There may be a number of factors involved here 1) Database well designed and managed and reorganisation does not become a problem 2) Regular use of alter table move and rebuild index reduces/removes the need to reorg 3) LMT and well sized tablespaces reduces/removes the need to reorg 4) As systems get bigger it is harder to find the outage therefore tend not to bother 5) Overtime is reduced at a lot of sites therefore less inclination to work out of hours for free 6) Disk is faster, more cpu etc so negative impact on performance is not as noticeable I think all of the above have some truth in them I think it hard to define hard and fast guidelines because of the individuality of each site and the requirements of each database, combined with the availability and cost of resource to implement reorgs. Don't you sell any of these reorg tools?. If so how do they calculate whether they are needed or not? HTH John -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 11:39 To: Multiple recipients of list ORACLE-L Subject: RE: Time for a reorg? Wow - does nobody reorg databases anymore? ;P Thanks for all your help guys.. I'll take that as a cue to put some time aside..;0P -Original Message- Sent: 12 March 2002 11:43 To: Multiple recipients of list ORACLE-L Does anyone have any "set limits" to know when it would be time for object reorgs? Firstly, I *know* that the reorg issue is viciously guarded on two sides - those that say reorgs should be done, and those that argue that they are unnecessary in todays environments. I agree with both sides ;P And don't intend this to break out the debate for the nth time.. What I am really after is guidelines from people to their own views of when a reorg is deemed necessary. Things such as percentage of rows that are chained/migrated, or number of extents, or number of extents as a percentage of maxextents or whatever.. Anybody come up with a really cool script that evaluates this - instead of me re-inventing the wheel? I could do this - I just don't really have the time at the moment - so your input is greatly appreciated! Thanks in advance Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: RE: Time for a reorg?
Title: RE: RE: Time for a reorg? Thanks Dick, I think what you are saying ties in exactly with my post on the matter John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 13:53 To: Multiple recipients of list ORACLE-L Subject: Re:RE: Time for a reorg? Yes, I think databases still get reorg'd today. It is just that our definition of a reorg and a database I think are changing. In the past if you said you were going to reorg a database that a full export, possibly a rebuild and an import. Way too time consuming for today's 24x7 requirements. Today I think we reorg at the object or schema level, not the entire instance. BTW, I think our definition of a database is also changing from the entire instance to a single schema. Dick Goulet Reply Separator Author: "Mark Leith" <[EMAIL PROTECTED]> Date: 3/13/2002 3:38 AM Wow - does nobody reorg databases anymore? ;P Thanks for all your help guys.. I'll take that as a cue to put some time aside..;0P -Original Message- Sent: 12 March 2002 11:43 To: Multiple recipients of list ORACLE-L Does anyone have any "set limits" to know when it would be time for object reorgs? Firstly, I *know* that the reorg issue is viciously guarded on two sides - those that say reorgs should be done, and those that argue that they are unnecessary in todays environments. I agree with both sides ;P And don't intend this to break out the debate for the nth time.. What I am really after is guidelines from people to their own views of when a reorg is deemed necessary. Things such as percentage of rows that are chained/migrated, or number of extents, or number of extents as a percentage of maxextents or whatever.. Anybody come up with a really cool script that evaluates this - instead of me re-inventing the wheel? I could do this - I just don't really have the time at the moment - so your input is greatly appreciated! Thanks in advance Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Poll & Questions
Title: RE: Poll & Questions I agree the work involved in creating a representative subset of data , complete with full RI in place can be quite significant. I know there are tools in place that do some of the work (Checkmate by BitybyBit and Quest have one - data factory I think) but these also require significant input and knowledge of the schemas. More importantly is the performance testing aspect. I have never seen a production system that has been implemented without some unknown/unexpected bottleneck because code has not been tested with realistic volumes. I know there are options available to asisst these days (outlines and stats to name 2) but a full size dev database is not the overhead it may seem. Disk is cheap , time is not. John -Original Message- From: Gene Sais [mailto:[EMAIL PROTECTED]] Sent: 13 March 2002 13:13 To: Multiple recipients of list ORACLE-L Subject: Re: Poll & Questions Tracy - 75 gb is nothing. Lets see: dba hourly rate to create test from data subset of production vs. cost of 75gb disks? Hmm, seems like a no brainer. >>> [EMAIL PROTECTED] 03/12/02 05:43PM >>> We currently have a production, system and development database here. The system and development databases are purged periodically and reloaded with lookup data. The developers are then responsible for entering transactional data in both regions. I am looking to follow the same practice for development, however I would like to clone my production database directly to the system test database. The production database is ~75G. Management does not want to commit $ to a full sized system database. Costs outweigh the benefits. I would like to sway them. HOW? Please give me your costs/benefits of doing this. In addition, what is the norm (if there can be one) in other shops. Does utopia exist? ps. One of the biggest reasons for this database would be for benchmarking, timings, stress-testing. I realize I can copy the production stats, but that won't give me a good execution time. Do others load a subset of data (say 25%) and then extrapolate to a total time? Is that even necessarily accurate to do? I have my doubts. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Free scripts
Title: RE: Free scripts I use Tim Onion's site which has a comprehensive listing of basic DBA scripts and there is also a zip file to download the lot in one go. www.timonions.com John -Original Message- From: Nguyen, David M [mailto:[EMAIL PROTECTED]] Sent: 12 March 2002 22:54 To: Multiple recipients of list ORACLE-L Subject: Free scripts I try to find some scripts (PL/SQL package) to monitor my oracle database such as tablespace, rollback segment, etc. Does someone know a website that has this? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: problem in creating control file?
Are you sure the sid is set to the new sid (TEST2) and also add a SET command in the first line CREATE CONTROLFILE SET DATABASE "TEST2" RESETLOGS ARCHIVELOG HTH John -Original Message-From: Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]Sent: 12 March 2002 11:48To: Multiple recipients of list ORACLE-LSubject: problem in creating control file? I try to clone my database . I have the datafiles on D:\yedek123 and want to create the control files . What I do is : set oracle_sid=new_sid svrmgrl internal/ startup nomount pfile=new_pfile CREATE CONTROLFILE DATABASE "TEST2" RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 254 MAXINSTANCES 1 MAXLOGHISTORY 1815LOGFILE GROUP 1 'D:\yedek123\REDO01.LOG' SIZE 1M, GROUP 2 'D:\yedek123\REDO02.LOG' SIZE 1M, GROUP 3 'D:\yedek123\REDO03.LOG' SIZE 1MDATAFILE 'D:\yedek123\SYSTEM01.DBF', 'D:\yedek123\RBS01.DBF', 'D:\yedek123\USERS01.DBF', 'D:\yedek123\TEMP01.DBF', 'D:\yedek123\TOOLS01.DBF', 'D:\yedek123\INDX01.DBF', 'D:\yedek123\DR01.DBF'CHARACTER SET WE8ISO8859P9; But When I run the create control file script , I am encountering the error below. CREATE CONTROLFILE DATABASE "TEST2" RESETLOGS ARCHIVELOG*ORA-01503: CREATE CONTROLFILE failedORA-01161: database name TESTDB in file header does not match given name of TEST2ORA-01110: data file 1: 'D:\yedek123\SYSTEM01.DBF' TESTDB is the original database . TEST2 is new DB. What is the problem ? Thank you. Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA. = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: local partition index question
Title: RE: local partition index question This subject nicely raises an question(s) I have. We have some tables complete with PK and FK's referencing them. We have now added a create_timestamp column to the tables and wish to partition the tables using the range (create_timestamp). No problems. However we canot create a PK index because, as you state Cherie, the PK does not contain the column that we have partitioned on. What workarounds are there. I can create a unique index on the PK columns (name,id for example)but it also affects the FK that other tables use to reference this table. How do other people sites work around this John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 11 March 2002 15:38 To: Multiple recipients of list ORACLE-L Subject: Re: local partition index question Yes, that is true. I've seen that to be the case in our warehouse database. Cherie Machler Oracle DBA Gelco Information Network "oracle dba" mail.com> cc: Sent by: Subject: local partition index question [EMAIL PROTECTED] om 03/11/02 08:58 AM Please respond to ORACLE-L Hi all, I am new to partition and I was told the following statement and I couldn't verify it in the doc. Could someone please tell me if it's true or not? "The partition column must be included in the primary key for the resulting unique index to be locally partitioned." Thanks _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Number / 0
Title: RE: Number / 0 Sinardy You are trying to create a function based index. To do so you need the query rewrite privilege The index will still fail with a divide by 0 error (don't know why you are dividing by zero) AS USER create index wrong_logic on john(income/0) * ERROR at line 1: ORA-01031: insufficient privileges AS SYS SQL> connect internal Connected. SQL> grant query rewrite to user; AS USER SQL> create index wrong_logic on john(income/0); create index wrong_logic on john(income/0) * ERROR at line 1: ORA-01476: divisor is equal to zero 1* create index wrong_logic on john(income/4) SQL> / Index created. HTH John -Original Message- From: Sinard Xing [mailto:[EMAIL PROTECTED]] Sent: 05 March 2002 08:38 To: Multiple recipients of list ORACLE-L Subject: Number / 0 Hi guys, This is my test: SQL> desc emp Name Null? Type - EMPNO NOT NULL CHAR(3) NAME VARCHAR2(10) INCOME NUMBER(5) SQL> create index no_logic on emp(income); Index created. SQL> drop index no_logic; Index dropped. SQL> create index wrong_logic on emp(income/0); create index wrong_logic on emp(income/0) * ERROR at line 1: ORA-01031: insufficient privileges I don't understand why Oracle throw such exception (reply). Do you have any idea what is Oracle doing when Oracle find out number / 0 Sinardy = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
How's the job market - UK - (slightly OT)
Title: How's the job market - UK - (slightly OT) Here in the UK it has been pretty bad but from all accounts I understand there to be a bit of light at the end of the tunnel As I have just been given notice I would appreciate anyone on the list in the UK or Europe who knows of DBA vacancies to get in touch Thanks John [EMAIL PROTECTED] Oracle DBA MMO2 * [EMAIL PROTECTED] ( 0113 388 6062 Desk ) 07713 066194 BT Mobile = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: vxfs File System full - it's not, and it's not Oracle (I don'
Title: RE: vxfs File System full - it's not, and it's not Oracle (I don't think) David, Why does the SA say it is an Oracle issue? The error message shown refers to a LV that is full (allegedly) that happens to contain some files owned by the oracle user. Sorry if I am being a bit dense but I am doing my best :-) John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 28 February 2002 16:39 To: Multiple recipients of list ORACLE-L Subject: vxfs File System full - it's not, and it's not Oracle (I don't think) My sysadmin is complaining about an Oracle(?) error on our HP9000, HP_UX v11.0, running three Oracle 8.1.7.0 instances. His error message is as follows: vxfs: mesg 001: vx_nospace - /dev/vgt001/lvol1 file system full (1 block extent) This file system has my /u001/app/oracle.. OFA compliant stuff on it. A df -k shows me the following: qe2l1:oracle /u001/app/oracle/admin/sasidist/bdump>>df -k . /u001 (/dev/vgt001/lvol1 ) : 7096543 total allocated Kb 1729572 free allocated Kb 5366971 used allocated Kb 75 % allocation used Nothing in any of the alert logs, the databases are up and functioning with several hundred users logged in and working away. The sysadmin has told damagement that it's Oracle's fault the backups aren't working (I don't trust the tape setup, and let them think they're backing up the DBs, but backups are run to disk and they get the compressed TAR'd result on tape - I hope). I have my doubts, but am thinking the error might have something to do with OmniBack, absent Oracle. Metalink doesn't have anything on this, and there are some questions I've found on Google, but no answers yet. Any input (well...almost any input) would be appreciated David A. Barbour Oracle DBA, OCP AISD 512-414-1002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Function based indexes
Sergey, I did a quick test which indicates that no special path is mentioned when using a FBI, instead the execution plan will show that an index is being used, which it would not do if a FBI had not been set up. The example below shows what I mean HTH John SQL> create index john_idx1 on john(spid_type); SQL> select spid_type from john where upper(substr(spid_type,2,1)) = 'Y' SQL> / SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'JOHN' SQL> drop index john_idx1; Index dropped. SQL> create index john_idx1 on john(upper(substr(spid_type,2,1))); Index created. SQL> analyze table john compute statistics; Table analyzed. SQL> select spid_type from john where upper(substr(spid_type,2,1)) = 'Y'; SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony 8 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=96 Bytes= 1344) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'JOHN' (Cost=7 Card=96 By *FBI in use tes=1344) 2 1 INDEX (RANGE SCAN) OF 'JOHN_IDX1' (NON-UNIQUE) (Cost=1 C *FBI in use ard=96) -Original Message-From: Babich , Sergey [mailto:[EMAIL PROTECTED]]Sent: 26 February 2002 20:51To: Multiple recipients of list ORACLE-LSubject: Function based indexes Hi, everyone, This may seem very simple to you, but what's the best way to see if a fresh FBI (sorry!) is used during the execution? Are they reported in the same manner to the SQL trace as other ones? Regards, Sergey = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Pinning Packages in the Shared Pool??
Title: RE: Pinning Packages in the Shared Pool?? John, I understand this still to be true or at least I have never heard anything to oppose it. Looking at the latest book I have purchased (101 Performance Tuning) the authors do talk about the principle and state "many database administrators recommend pinning the key system packages as soon as the instance starts" which whilst not written in the style of the 10 commandments does imply that they see no reason not to. There is also a warning to balance what you load against the likelyhood of ageing other objects quicker as there is less free memory to use. So all in all I would continue to pin large, frequently used objects subject to sufficient space in the shared_pool. Someone else recently asked if that is the case why don't Oracle automatically pin some of these packages. I think that is a fair question but I suppose the argument against is that Oracle have no idea how much memory you have available on the server and how much you allocate to Oracle and then how much you allocate to the shared pool and so it is safer to leave it to the DBA who knows the system to manage it. HTH John -Original Message- From: orantdba [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 20:34 To: Multiple recipients of list ORACLE-L Subject: Pinning Packages in the Shared Pool?? Hi all, There used to be a piece of wisdom that indicated that you should pin Large, frequently used objects in the shared pool at startup of the database where large was defined as > 5000 bytes. Is this still true There was also a list of "Oracle Packages" that were recommended to be pinned. That included diutil, dbms_sql, dbms_utility, and standard. Is this still good advice? Thanks, John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: ORA-27072
Have a word with your sysadmin. See if I can find out what/who is using that file Are all datafiles/control files and redo log etc owned by the same user (Oracle) and with the same permissions Seems odd that it appears to be random across a number of files HTH John -Original Message-From: Shibu [mailto:[EMAIL PROTECTED]]Sent: 23 February 2002 06:18To: Multiple recipients of list ORACLE-LSubject: ORA-27072 Hi all.. My database is going down frequently showing this error in alertfile . This is happening to different datafiles and controlfiles. What are the possibilities that cause this error ? How can i avoid this error ? oracle 8.1.7 in win2k Errors in file C:\oracle\admin\acusis\bdump\acusisCKPT.TRC:ORA-00206: error in writing (block 3, # blocks 1) of controlfileORA-00202: controlfile: 'D:\ORADATA\CONTROL01.CTL'ORA-27072: skgfdisp: I/O errorOSD-04008: WriteFile() failure, unable to write to fileO/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file. regards, shibu = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: SQL Loader:How to load data with carriage return embedded
Arul,
Interesting problem. We had a similar situation and used
AWK instead after a bit of head-scratching.
The
following code worked for us
BEGIN{
FS="`"
first=0
}
{
if
(substr($1,1,3)=="KB0"){
-- to identify each new record which starts with those
letters
if
(first==1)
printf("\n")
printf("%s", $0)
}
else
printf("%s", $0)
if
(first==0)
first=1
}
END{
printf("\n")
}
I am still interested in a sqlloader resolution to this
problem if anyone else has one
John
-Original Message-From: Arul kumar
[mailto:[EMAIL PROTECTED]]Sent: 23 February 2002
07:33To: Multiple recipients of list ORACLE-LSubject:
SQL Loader:How to load data with carriage return
embeddedHi DBAs,
I have a flat file with Varchar2 data spread across lines with carriage
return..
I have tried using the following but in vain.
= LOAD DATA INFILE
'test.dat' "str X'0c0a'" INTO TABLE arul.test1 FIELDS
TERMINATED BY "~" optionally enclosed by '"' trailing nullcols (
no , descr )
=
where descr - Varchar2(4000) field..
There is no error message(!)??
Note - With the above spec. able to load only the first record with
carriage returns embedded. Rest of the records are skipped as bad records(!) .
Can anyone suggest the ways of loading it thru SQL Loader?
Thank you.
regards, Arul.
*DisclaimerThis
message (including any attachments) contains confidential information
intended for a specific individual and purpose, and is protected by law.
If you are not the intended recipient, you should delete this message
and are hereby notified that any disclosure, copying, or distribution of
thismessage, or the taking of any action based on it, is strictly
prohibited.*Visit
us at http://www.mahindrabt.com
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: 8i and Veritas NetBackup
Title: RE: 8i and Veritas NetBackup
The folloiwng is a good post from Samir which I kept. It seems to cover your environment well. Hope it helps John
Hi,
In order for RMAN to work with a third party backup product the third party
vendor writes an interface to an Oracle supplied API and supplies this in
the form of a library.
Oracle supplies a default copy of the library as $ORACLE_HOME/lib/libobk.so
which has the RMAN default functionality of writing to disk only.
Installation of an RMAN media management product logically replaces this
library. How this logical replacement is done depends on several Oracle
documents and one netbackup document, most of which contradict each other.
As long as it is clear how the library is logically presented to Oracle it
doesn't matter how it is done.
The library that Oracle currently uses can be established by executing:
ldd $ORACLE_HOME/bin/oracle
This will list the libraries linked to the Oracle executable, specifically
libobk.so.
The Veritas supplied file is called libobk.so.1. Oracle thinks it is called
libobk.so, which it looks for in LD_LIBRARY_PATH. Therefore a soft link is
needed from libobk.so somewhere in LD_LIBRARY_PATH to the Netbackup supplied
libobk.so.1. This can be done with:
LD_LIBRARY_PATH=$ORACLE_HOME/lib:
and then either:
ln -s /opt/openv/netbackup/bin/libobk.so.1 $ORACLE_HOME/lib/libobk.so
or:
cp /opt/openv/netbackup/bin/libobk.so.1 $ORACLE_HOME/lib/libobk.so.1
ln -s $ORACLE_HOME/lib/libobk.so.1 $ORACLE_HOME/lib/libobk.so
Other combinations of these links are obviously possible, but the important
thing is to be consistent. With several ORACLE_HOME's the former is
preferable, the latter becomes more relevant if a version dependency had
been discovered between RMAN and Netbackup such that each ORACLE_HOME needed
a different copy of the library.
Additionally, Oracle 8.0.5 statically links the library into the Oracle
executable, so once the executable is relinked it doesn't matter what
happens to the original library file. At 8.1.6 however the library is
THEORETICALLY dynamically linked and so always depends on finding a copy on
LD_LIBRARY_PATH. A bug in 8.1.6 (1252142) means this dynamic linking does
not work and the library still has to be statically linked to the Oracle
executable. The bug is fixed at 8.1.7.
Hope this helps.
Hi Listers,
I have a question regarding using 8i RMAN with Netbackup 3.4.
I remember on 8i one no longer needs to relink the oracle executable
when intergrate with a 3rd party backup software. All I have to
do it change the symbolic link $ORACLE_HOME/lib/libobk.so to point
to the media management library.
Now from the Netbackup 3.4 for Oracle manual, it says on Solaris
for 8.1.6, you need to relink the oracle executable by doing:
make -f ins_rdbms.mk ioracle LLIBOBK=-lobk
So the question is should I relink or not?
Thanks for your feedback.
Rich
Nice answer Samir. As an additional note... after linking I've used the
sbttest utility from Oracle to verify a working installation. (We're using
Netbackup with Linux and have encountered problems which Veritas is
working on.)
The sbttest program is nice but for complete backup installation testing I
believe in performing test recoveries. I've created small test databases
on production servers and have scripted several recovery scenarios. For our
current situation it turns out that we can get a successful backup but
restores are a problem. Since backups aren't any good if you can't restore
the backup is suspect. We can restore files from tape to disk but finishing
the recovery requires getting out of RMAN and recovering from the SQL
prompt. We can also restore from RMAN as long as we don't have more than 2
commands in the run { commands... } syntax. Of course this could get quite
tedious in a multiple datafile restore scenario so we're insisting that
Veritas fix the communication problems between RMAN, Netbackup and our tape
robot.
So I guess the object lesson from all this is that, regardless of "apparent"
linking success, it pays to be anal and thorough when it comes to validating
your backup implementation and the best way to do this is to perform some
actual recoveries.
Hi Listers,
I have a question regarding using 8i RMAN with Netbackup 3.4.
I remember on 8i one no longer needs to relink the oracle executable
when intergrate with a 3rd party backup software. All I have to
do it change the symbolic link $ORACLE_HOME/lib/libobk.so to point
to the media management library.
Now from the Netbackup 3.4 for Oracle manual, it says on Solaris
for 8.1.6, you need to relink the oracle executable by doing:
make -f ins_rdbms.mk ioracle LLIBOBK=-lobk
So the question is should I relink or not?
Thanks for your feedback.
Rich
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 22 February 2002 14:38
To: Multiple recipients of list ORACLE-L
Subject: 8i and Veritas NetBackup
Is anyone using this combinat
RE: Woopeee and TWO ORA-600's!
Title: RE: Woopeee and TWO ORA-600's!
Mark,
Here are a couple of notes I saved on the subject
John
rmjvm.sql doesnot remove all the components properly. If your resources sizes are ok, then try following.
If for any reason the installation fails then $ORACLE_HOME/javavm/install/rmjvm.sql has to be run. Unfortunately it does not remove all the components. Following workaround has to be used to get over the bugs.
1. Restart the database.
2. Start a different session other than the one that started the database.
3. Run initjvmaux.sql
4. Run rmjvm.sql
5. Run following SQL queries, LOG on as SYS.
--The minimum action to remove JIS trigger is running the following sql.
drop trigger JIS$ROLE_TRIGGER$;
delete from ducs$ where owner='SYS' and pack='JIS$INTERCEPTOR$' and proc='USER_DROPPED';
delete from aurora$startup$classes$ where classname='oracle.aurora.mts.http.admin.RegisterService';
delete from aurora$dyn$reg;
.
Are you sure its hanging. Documents have said it can take up to an hour and its that statement
that tends to take the longest. I have seen quite the variation in time depending on the system
I am on.
-Original Message-
From: Saravana Kumar [mailto:[EMAIL PROTECTED]]
Sent: Sunday, November 11, 2001 11:25 PM
To: Multiple recipients of list ORACLE-L
Subject: initjvm.sql
Hi friends,
I have problem during execution of initjvm.sql
The step's which i have followed.
1)Increased system tablespace 200 mb
2)increased the shared pool size 50mb
3)increased the java pool size to 50mb
4)ran rmjvm.sql
5)initjvm.sql
While runnig initjvm.sql the script hangs while i get the
statement create or replace java system;
-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED]]
Sent: 22 February 2002 11:08
To: Multiple recipients of list ORACLE-L
Subject: RE: Woopeee and TWO ORA-600's!
Ahh well!
I have another question following this:
I am also getting the following error on startup:
Fri Feb 22 09:33:04 2002
Errors in file C:\oracle\admin\ORCL\udump\ORA03028.TRC:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared
pool","unknown object","joxs heap init","ioc_allocate_pal")
Fri Feb 22 09:33:10 2002
Errors in file C:\oracle\admin\ORCL\udump\ORA03028.TRC:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared
pool","unknown object","joxs heap init","ioc_allocate_pal")
ORA-06512: at "SYS.DBMS_JAVA", line 216
ORA-06512: at line 2
Completed: alter database open
This is because I set the java_pool to 0 in my init file (we don't use
anything java related against our databases). Does anybody know how to
disable or de-install java from the database? I've taken a look at the
jvmrm.sql in RDBMS/Admin:
-- Remove some portion of the Java related data dictionary objects
-- This script must be run as a subscript of a script which sets the
-- variable jvmrmaction.
-- Possible values are
-- FULL_REMOVAL: remove all java related objects
But can't find anything of a script that will call this? Anyone have any
ideas?
Cheers
Mark (Why do I bother?) Leith
-Original Message-
Sent: 22 February 2002 10:18
To: Multiple recipients of list ORACLE-L
Mark
The ora600 lookup tool on Metalink states that there is no description for
this code
John
-Original Message-
Sent: 22 February 2002 09:34
To: Multiple recipients of list ORACLE-L
Hi Ladies and Gents,
I installed 9.0.1 on to my Win2K machine yesterday, and whilst installing an
un-named monitoring tool against it this morning, the instance crashed with
a bunch of ORA-600's in the alert log. Below are the two that appeared,
though the first turned up about 10 times concurrently:
Fri Feb 22 09:22:28 2002
Errors in file C:\oracle\admin\ORCL\udump\ORA01668.TRC:
ORA-00600: internal error code, arguments: [26599], [1], [190], [], [], [],
[], []
Fri Feb 22 09:22:34 2002
Errors in file C:\oracle\admin\ORCL\udump\ORA01668.TRC:
ORA-00600: internal error code, arguments: [150], [], [], [], [], [], [], []
Anyone ever seen these before? I'm not going to call support as this is only
a sandbox database, so please don't tell me to call them or log a tar.. I
have enough to do today already! :(
Any feedback on these is, however, much appreciated!
Thanks
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput & performance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
RE: Woopeee and TWO ORA-600's!
Title: RE: Woopeee and TWO ORA-600's! Mark The ora600 lookup tool on Metalink states that there is no description for this code John -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: 22 February 2002 09:34 To: Multiple recipients of list ORACLE-L Subject: Woopeee and TWO ORA-600's! Hi Ladies and Gents, I installed 9.0.1 on to my Win2K machine yesterday, and whilst installing an un-named monitoring tool against it this morning, the instance crashed with a bunch of ORA-600's in the alert log. Below are the two that appeared, though the first turned up about 10 times concurrently: Fri Feb 22 09:22:28 2002 Errors in file C:\oracle\admin\ORCL\udump\ORA01668.TRC: ORA-00600: internal error code, arguments: [26599], [1], [190], [], [], [], [], [] Fri Feb 22 09:22:34 2002 Errors in file C:\oracle\admin\ORCL\udump\ORA01668.TRC: ORA-00600: internal error code, arguments: [150], [], [], [], [], [], [], [] Anyone ever seen these before? I'm not going to call support as this is only a sandbox database, so please don't tell me to call them or log a tar.. I have enough to do today already! :( Any feedback on these is, however, much appreciated! Thanks Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: OPS DBA work (was dumb question)
Title: Message Bjorn, I don't have any issues with what you say - in fact it really agrees mostly with what I stated. You have added 2 important factors though, better application knowledge and use of raw file systems. I use Compaq Tru64 so that does not require raw files systems but other o/s certainly do. I think you were a bit unfair to suggest that I meant you only needed to check a few init.ora parameters out ( 'it is far more than knowing the GC_ parameters' ). I am fully aware of the need to look at freelists and freelist groups - I encompassed that in my statement 'Management and tuning of internode communication. Specifically reducing the level of pinging - use of GC% init.ora variables' Anyway I don't think we are that far away from each other Regards John -Original Message-From: Bjørn Engsig [mailto:[EMAIL PROTECTED]]Sent: 05 February 20021 2:25To: Multiple recipients of list ORACLE-LSubject: Re: OPS DBA work (was dumb question) With the caveat, that I am a consultant and not actually a DBA, I would argue very strongly, that the OPS DBA needs quite some extra understanding, knowledge and experience compared to one managing a single instance Oracle. In particular:- Performance problems, primarily due to poor application design/development, that are seen in single instance are likely to be one to two orders of magnitude worse in OPS. Hence, the DBA needs a much better application understanding.- There are Oracle features (e.g. free list groups) that must be used with OPS and which rarely are needed single instance.- Recovery scenarios are more complex- You must use raw devices (except on platforms with inhertance from Digital Corp), which can add complexity- A frequent requirement of OPS systems is better uptimes than for single instance, which is a very non-trivial task. The whole stack is far more complex, and even though the possibility to have two or more independent nodes sound really great in theory, the practical assurance, that they are in fact completely independent is difficult. And if they aren't independent, they are likely to have worse uptimes than the single instance! - And I probably forgot something, so it is far more than knowing the GC_ parameters, which, BTW, by itself isn't that simple!- Also, BTW, note that except for a few things, RAC doesn't make your life easier than OPS!Thanks, Bjørn.Shreeni wrote: Hi John, Thx for the input. I really appreciate it. I was just kind of stumped when I was asked not once but several times and places, to point out the diff between an OPS DBA and a "regular" DBA that I am. Thanks again Shreeni Shreenivasa Raoe-Z ing Technologies, Inc..41-43 Beekman Street, 3rd FloorNew York, NY 10038.Tel: (212)233-9861 xt.241Fax: (212)233-9862Cell:(917)861-4966lsama@e-zingtech.com *Your IT Solutions Provider*** *** http://www.e-zingtech.com ***Under Bill s.1618 Title III passed by the 105th U.S. Congress this mail cannot be considered spam as long as we include contact information and a remove link for removal from our mailing list. To be removed from our mailing list reply with remove in the subject heading and your email address in the body. Include complete address and/or domain to be removed. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hallas JohnSent: Monday, February 04, 2002 4:05 AMTo: Multiple recipients of list ORACLE-LSubject: OPS DBA work (was dumb question) Shreeni, The mangement of a OPS system does not require any extra skills or facilities. Areas that are different or need more attention from a standalone instance include the following : Management and tuning of internode communication. Specifically reducing the level of pinging - use of GC% init.ora variables Requirement for different start up scripts (exclusive and shared modes) Some additional work when duplicating databases using RMAN Perhaps more involvement with application and sys admin teams to determine load balancing factors I am sure there are others (probably ones I should be doing that I am not) The simplest thing to remember about OPS is that there is only 1 set of datafiles and therefore tables, despite the number of instances that may be using those datafiles. This point is occasionally made to those whob elieve that we have a fully resilient set up. HTH John -Original Message-From: Shreeni [mailto:[EMAIL PROTECTED]]Sent:
RE: How to find out what caused job to fail?
Title: How to find out what caused job to fail? How bizarre. Roland asked a question about where does he find alert log & trace files (answer = select name,value from v$parameter where name like '%dest%') or check your init.ora file at least 10 minutes before this message came to me. I have noticed before that I can post a message that I see on the list in about 20 minutes , at other times it can be well over an hour. It can be quite frustrating because you see a message and post a reply and then see 5 other responses at least 30 minutes before your's appears John -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: 04 February 2002 09:05To: Multiple recipients of list ORACLE-LSubject: RE: How to find out what caused job to fail? Hi, you can find the reason in alert log or trace files. -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Monday, February 04, 2002 4:40 PMTo: Multiple recipients of list ORACLE-LSubject: How to find out what caused job to fail? Hi! Is there a way to find out, why a database job failed? in dba_jobs (or user_jobs), I can only see that it failed, but no indication of the reason why it failed. Any ideas? This is 8.1.7 on Solaris. Thanks, Helmut = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
OPS DBA work (was dumb question)
Title: Message Shreeni, The mangement of a OPS system does not require any extra skills or facilities. Areas that are different or need more attention from a standalone instance include the following : Management and tuning of internode communication. Specifically reducing the level of pinging - use of GC% init.ora variables Requirement for different start up scripts (exclusive and shared modes) Some additional work when duplicating databases using RMAN Perhaps more involvement with application and sys admin teams to determine load balancing factors I am sure there are others (probably ones I should be doing that I am not) The simplest thing to remember about OPS is that there is only 1 set of datafiles and therefore tables, despite the number of instances that may be using those datafiles. This point is occasionally made to those whob elieve that we have a fully resilient set up. HTH John -Original Message-From: Shreeni [mailto:[EMAIL PROTECTED]]Sent: 04 February 2002 00:40To: Multiple recipients of list ORACLE-LSubject: Dumb question Hi List, To ask a dumb question, is there any special way to run exp/imp on Oracle Parallel server on Solaris ?? Is parallel server DBA different than a "regular" DBA ?? :) TIA Shreeni Shreenivasa Raoe-Zing Technologies, Inc..41-43 Beekman Street, 3rd FloorNew York, NY 10038.Tel: (212)233-9861 xt.241Fax: (212)233-9862Cell:(917)861-4966lsama@e-zingtech.com *Your IT Solutions Provider*** *** http://www.e-zingtech.com ***Under Bill s.1618 Title III passed by the 105th U.S. Congress this mail cannot be considered spam as long as we include contact information and a remove link for removal from our mailing list. To be removed from our mailing list reply with remove in the subject heading and your email address in the body. Include complete address and/or domain to be removed. = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Rman again. Long screen output removed/SOLVED
Title: RE: Rman again. Long screen output removed/SOLVED
Jack,
We are on 8.1.7.1 on Compaq Tru64 (5.1) and we are sure that we have done both a duplicate database and a restore using PITR
(format to_date('18-SEP-2001 01:00:00','dd-mon- HH24:MI:SS')";
I am not really in a position to retest at the moment but we are reasonably certain. What bug no is it and on which platform
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 30 January 2002 10:10
To: Multiple recipients of list ORACLE-L
Subject: RE: Rman again. Long screen output removed/SOLVED
Hi All,
It was an Oraclebug.
The only , very workable, workaround Oracle could come up with is to set
NLS_DATE_FORMAT at Unix level and use :
set time until 'string'; (STRING BEING IN THE FORMAT OF NLS-DATE_FORMAT AT
UNIX LEVEL)
9.0.2 is supposedly fixed.
Jack
Hallas John <[EMAIL PROTECTED]>@fatcity.com on 30-01-2002 10:25:18
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
Subject: RE: Rman again. Long screen output removed
Jack,
Your date format is
set until time 'to_date
(''29012002094700'',''ddmmhh24miss'')';
whereas one that works for me and matches the documentation is
set until time "to_date('18-SEP-2001 01:00:00','dd-mon-
HH24:MI:SS')";
Note the difference in quotation marks which is what the RMAN error message
refers to
Try my format and see what happens
HTH
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 29 January 2002 14:25
To: Multiple recipients of list ORACLE-L
Subject: RE: Rman again. Long screen output included
Hi,
That only works if the format mask is set correctly, ergo not very
flexible.
Jack
××ר ××××× <[EMAIL PROTECTED]>@fatcity.com on 29-01-2002 14:15:22
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
Subject: RE: Rman again. Long screen output included
Hello Jack
The RMAN command recover database;
is changed to be recover database until time '1999-03-05:11:33:00';
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
==
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this com
RE: RTFM questions (formally RE: PL/SQL)
Title: RE: RTFM questions (formally RE: PL/SQL) Kevin, I am afraid your post shows a distinct lack of ambition. :-) No flames please John -Original Message- From: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: 30 January 2002 08:50 To: Multiple recipients of list ORACLE-L Subject: RE: RTFM questions (formally RE: PL/SQL) Don't stop posting. I'm not a DBA either. Don't have any intention of being one. I'm an Oracle duhveloper (as people sometimes point out..LOL). I read this list to get a better understanding of how databases 'hang together'. I never profess to being an expert in DBA related stuff...I'm far, far from that. -Original Message- Sent: 29 January 2002 22:29 To: Multiple recipients of list ORACLE-L Thank you all for your kind replies, and I now definitely know the correct answer to this. Please let me reiterate that I am *not* yet an Oracle dba. I've only been to one class so far, for crying out loud! Obviously it would be better for me to just not post for a while, in any event. No need to keep on responding to this thread. Cheers, JoJo -Original Message- Walt Sent: Tuesday, January 29, 2002 1:46 PM To: Multiple recipients of list ORACLE-L I strongly suggest you not experiment with this on your production database. --Walt -Original Message- Sent: Tuesday, January 29, 2002 1:16 PM To: Multiple recipients of list ORACLE-L I'm a complete newbie, so I may be off-base here, but I believe that truncating a table does not delete any data from the table. It moves the cursor up, thereby closing up the "empty space" where data has previously been deleted. --JoJo -Original Message- Sent: Tuesday, January 29, 2002 10:46 AM To: Multiple recipients of list ORACLE-L To our resident Oracle Expert who just 6 months (July 26 2001) ago posted the following e-mail If I remember rightly, deleting rows from the table does NOT free up tablespace. In order to do that you have to trunctate the table (although this of course deletes all data from the table)...I can't for the life of me remember how you adjust the space the table is actually using after doing a delete...(to everyone else) would an analyze work? Kev. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: JoJo Al-Zawawi INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
Alter system check datafiles
Title: Alter system check datafiles Paul Sherman wrote 'I tried an 'alter system check datafiles' I had never come across that command before and looked at the documentation which states that 'in a distributed database system, such as an Oracle Parallel Server environment, updates an instance's SGA from the database control file to reflect information on all online datafiles'. Global actions against all nodes , local against the current instance only. I did check out the OPS install/config guide but did not found anything more in there. I was failing to understand in which circumstances the command would be required, knowing that we are running a number of OPS set ups and I had never used it. Looking further on Metalink I found the following note (Note:1071756.6) When running "ALTER SYSTEM CHECK DATAFILES" it delivers a "STATEMENT PROCESSED" but there is no recorded activity. There is no alert file even though the book says there is. You can do an ALTER SYSTEM CHECK DATAFILES if the database is only mounted. But the command does not return an error if a datafile is missing. Solution Description: = The ALTER SYSTEM CHECK DATAFILES command was designed to solve a particular problem of file accessibility. This most often has to do with OPS, and disks that are shared among several machines. There are cases where a disk may be accessible on one machine, but not another, because of configuration or OS problems. Imagine that you have a datafile that is marked OFFLINE. If the disk where that datafile lives is accessible to a particular machine in an OPS installation, then you can bring it ONLINE. If, however, another machine can't get to that disk at the time you bring it ONLINE, then that instance won't be able to verify the datafile. Hence in that instance, the controlfile will have the datafile listed as ONLINE, but the datafile still will not be properly accessible and entered in the SGA. Explanation: The ALTER SYSTEM CHECK DATAFILES command was designed to allow you to correct the problem from the OS perspective and make the disk accessible. This would then trigger the instance into recognizing this fact, verifying the datafile, and thereby making it available to Oracle in that instance. So the command does not quite do what might be implied by the current documentation. It does not verify access to all online datafiles,rather, it only looks at those that are now online, but which were not previously verified. Once a datafile has been verified, it is considered verified for all time, until you OFFLINE the file or until the database is dismounted in that instance. I hope someone founds that useful, at least I have learnt a bit more by checking it out. Thanks Paul for mentioning it, if only in passing. John Oracle DBA MMO2 * [EMAIL PROTECTED] ( 0113 388 6062 Desk ) 07713 066194 BT Mobile = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Web site of messages
Title: RE: Web site of messages I also found this site by searching Google for Oracle-L http://faqchest.dynhost.com/prgm/oracle-l/ -Original Message- From: Marin Dimitrov [mailto:[EMAIL PROTECTED]] Sent: 30 January 2002 08:00 To: Multiple recipients of list ORACLE-L Subject: Re: Web site of messages - Original Message - > Hi, > > Is there a website, which keeps all the messages posted to this forum, which > can be accessed without subscribing to the forum? > try http://www.fatcity.com/ListGuru/my.php and http://www.mail-archive.com/oracle-l%40fatcity.com/ hth, Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. " -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Rman again. Long screen output removed
Title: RE: Rman again. Long screen output removed
Jack,
Your date format is
set until time 'to_date(''29012002094700'',''ddmmhh24miss'')';
whereas one that works for me and matches the documentation is
set until time "to_date('18-SEP-2001 01:00:00','dd-mon- HH24:MI:SS')";
Note the difference in quotation marks which is what the RMAN error message refers to
Try my format and see what happens
HTH
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 29 January 2002 14:25
To: Multiple recipients of list ORACLE-L
Subject: RE: Rman again. Long screen output included
Hi,
That only works if the format mask is set correctly, ergo not very
flexible.
Jack
אדר יחיאל <[EMAIL PROTECTED]>@fatcity.com on 29-01-2002 14:15:22
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
Subject: RE: Rman again. Long screen output included
Hello Jack
The RMAN command recover database;
is changed to be recover database until time '1999-03-05:11:33:00';
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: auditing tables
Title: RE: auditing tables Auditing is a definite overhead but the degree varies depending on what auditing is set up and how many transactions you have. Auditing can tell you who did what to a table (IUD etc). What it will not do is store before and after values. If that is what you want then triggers are required. Obviously if a new record is going to be written to an audit table every time a record in the master table is changed then that can cause significant database activity which will have an overhead on performance and possibly space as well depending on the volumes. Another workround could be to use Logminer to check what a user has done and when. This is perhaps more suitable for a one off security check that a method of tracing every change by any user at any time. Realy it is a matter of negotiating requirements v performance v implementation time/costs and identifying which solution (or mixture of the above) provides the necessary facilities HTH John -Original Message- From: Foelz.Frank [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 14:11 To: Multiple recipients of list ORACLE-L Subject: auditing tables Hi all, does anyone have experience in using Oracle's possibilities of auditing a database ?? I am interested in performance questions i.e. is it a hughe loss of performance when auditing tables Inserts/Updates/Deletes. Should I use triggers instead ? any hints (comments, websites, etc...) are welcome. > Frank < -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Unix script
Title: RE: Unix script Basis of a script added into yours below. Note I am only checking to see if there is a PMON proces up - maybe best to make a sqlplus connection and read v$instance or something and exit if it fails. Really very poor but will give you something to work on. What I don't see from your sample script is the end marker for the << ! command I normally use << EOF and then at the end of the sql statements I have a line with EOF in. I do not see your ! marker To get a log file call the shell script and > to log_file or set that up in your cron routine HTH John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 14:31 To: Multiple recipients of list ORACLE-L Subject: Unix script Hallo, I have this unix script. I would like to include code which checks if the oracle database is up and running so the procedure konto.fillbotables.anrop doesnt start at all.. I also want an errorlog file to be created. Please tell me how to write the code and where to include it in this shell script. #!/bin/sh . /usr/bin/orasetup.sh . /d31/appl/konto/bat/movefiles.sh numberOfLines=`ps -ef|grep pmon wc -l` grep for sid or ora_pmon_sid or whatever if [ $numberOfLines -lt 2 ] # ie database not running then cat dummy_file |mailx -s "No database running loader not started" [EMAIL PROTECTED] else fi sqlldr userid=konto/icakort control=/d31/appl/konto/bat/ehbgrupp.ctl log=/d31/appl/konto/log/ehbgrupp.log sqlplus -s konto/icakort << ! set heading off set verify off set feedback off set termout off set pages 0 begin konto.fillbilbotables.anrop; END; / EXIT fi sqlldr userid=konto/icakort control=/d31/appl/konto/bat/ehbgrupp.ctl log=/d31/appl/konto/log/ehbgrupp.log sqlplus -s konto/icakort << ! set heading off set verify off set feedback off set termout off set pages 0 begin konto.fillbilbotables.anrop; END; / EXIT Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: RMAN and ORA 8.1.7 on NT/W2K
Title: RE: RMAN and ORA 8.1.7 on NT/W2K Allen, I am sure you can continue in the same manner as before if you wish. I am no NT expert but we are using hot backups on a variety of Unix and NT servers using 8.1.7. We also have some RMAN backups as well. Carry on with the hot backups and then perhaps use RMAN on a test database until you are familiar with it John -Original Message- From: Allen R. Lucas [mailto:[EMAIL PROTECTED]] Sent: 23 January 2002 15:11 To: Multiple recipients of list ORACLE-L Subject: RMAN and ORA 8.1.7 on NT/W2K I am looking at upgrading our ORA 8.0.5 databases to ORA 8.1.7. In preparation, I am reading the 8i administrators guide for NT. Chapter 11, Backing up and Recovering Database Files, states that one can use Oracle Enterprise Manager DBA Management Pack, or Line Command RMAN, or OCopy. Currently, we backup using a homemade batch file which varies the tablespace offline, does a host copy, then varies the tablespace back online. We also copy the controlfiles, password files, etc... The process is continually tested and works. We hotbackup daily and cold backup once a week. Anyway the question is whether I will be able to continue backing up a 8.1.7 DB the same way, or am I finally going to be forced into RMAN? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Allen R. Lucas INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: COPY vs. OCOPY on NT
Title: RE: COPY vs. OCOPY on NT
Try http://www.speakeasy.org/~jwilton/oracle/hot-backup.html for a view on this
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 28 January 2002 09:20
To: Multiple recipients of list ORACLE-L
Subject: RE: COPY vs. OCOPY on NT
I seem to miss something here.
We are talking about backups that run after: alter tablespace XXX begin
backup.
There are no writes to the tablespace in this situation.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
> -Original Message-
> From: Thomas B. Cox [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, January 28, 2002 6:35 AM
> To: Multiple recipients of list ORACLE-L
> Subject: COPY vs. OCOPY on NT
>
>
> Your point is (I believe) that the Oracle data files are
> opened writeable by Oracle at instance startup time. When
> you use the NT command "COPY" it will prevent additional
> writers, but not those writers that are already holding
> write locks.
>
> My comments and questions are:
>
> 1. This used to fail with an error in previous versions
> of Oracle and NT that I've worked with.
>
> 2. I'd be curious if multiple DB writers could cause
> a problem with your setup.
>
> 3. If a COPY fails/dies holding its lock, can I re-start
> either the COPY of Oracle without rebooting or manually
> clearing the lock?
>
> 4. You have demonstrated that the statement "COPY will
> always fail" is false. But we're not yet at "COPY will
> always work" -- and that could be a problem.
>
> Cheers.
> -Tom
>
>
> --- Igor Neyman <[EMAIL PROTECTED]> wrote:
> > Now, from my other message to the list (don't know, if Eric forwarded
> > it to
> > you), it answers your questions about my experience recovering from
> > online
> > backup and why I don't like MetaLink Note explanation in regard to
> > advantages of "Ocopy" versus "Copy":
> >
> >
> > Couple years ago, when we were preparing first release of our
> > product, I
> > read of course about "NT Copy" versus "Oracle Ocopy".
> > Still I decided to test it, because not always I trust what I read,
> > and I
> > like to get proof myself.
> > Testing of online ("hot") backup/recovery scenario showed, that using
> > "NT
> > Copy" command in backup scripts is perfectly fine, when creating
> > backup set
> > of files on the disk. And there is no problem restoring from this
> > backup.
> > Now this "disk backup" set of files could be saved on tape, using
> > NTBACKUP
> > (that's the one, that really can not copy file, if it's opened by
> > some other
> > program. But that's not the case with prepared in advance "disk
> > backup").
> > "NT Copy" has no problems copying files opened already by Oracle, and
> > backup
> > is consistent, as long of course as I am using "alter tablespace
> >
> > begin backup" before copying relevant files and "alter tablespace
> > end
> > backup" after finishing files copy.
> > So, those scripts (using "NT Copy") were put into production, and now
> > have
> > been used for more than two years on more than hundred
> > installations/sites
> > (the number keeps growing).
> > From time to time, our field engineers are bringing back to me sets
> > of
> > online (can not use "cold" backup - our systems should run 24*7, I'm
> > not
> > saying they are, but we are trying to minimize downtime) backed up
> > files (db
> > files and archived RedoLog files), and I recover them with no problem
> > (we
> > need this, to test how the upgrade to next release of our product
> > will run
> > against "real" customers data).
> >
> > Now, about MetaLink Note:139327.1
> > It says:
> >
> > Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and
> > FILE_SHARE_WRITE flags. This allows writing to continue while we
> > take the
> > backup. Inconsistencies in the backup are repaired by applying
> > archived
> > redo during recovery. The 'copy' command from NT doesn't use these
> > flags
> > since it wants to prevent writes to the file while the copy is
> > taking
> > place.
> >
> >
> > I don't think, it's very accurate, and here is why:
> > When during online backup I run "NT copy" against db file, the file
> > is
> > already opened by Oracle (at moment, when I "open" the database).
> > So, even if "NT copy" opens file without FILE_SHARE_READ and
> > FILE_SHARE_WRITE flags, all it means is that "Subsequent open
> > operations on
> > the object will fail" (quote from NT docs). I want you to notice, it
> > says
> > "Subsequent open operations" not "Subsequent write/read operations".
> > So,
> > all it does is prohibiting some other program/process from "opening"
> > the
> > file. But Oracle, as I mentioned, has this file already opened, and
> > it is
> > perfectly capable of reading/writing this file.
> > Of course, the image of the saved file will be "fuzzy", and that's
> > why when
> > recovering from online backup we are applying archived RedoLog files
> > (which
> > getting written much more intensely during onlin
RE: Query
Title: RE: Query Short for machine John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 12:50 To: Multiple recipients of list ORACLE-L Subject: RE: Query What is: m/c ??? Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Ghadge,Sameer [SMTP:[EMAIL PROTECTED]] > Sent: Mon, January 28, 2002 1:55 PM > To: Multiple recipients of list ORACLE-L > Subject: Query > > Hi all, > Can we have the multiple databases on the same m/c running at the same > time. > > thx > Sameer > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ghadge,Sameer > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > This e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: More Rman q's
Title: RE: More Rman q's
Jack,
TEST3 instance should be exactly as you stated, an Oracle instance with no datafiles as yet.
What I think I was meaning regarding the names of the objects is that the format is
Rman catalogue = catalogue
Source database = target
new database = auxilliary.
To my mind that is wrong and the new database should be target. It is of no importance but I do think it can be confusing.
What is even odder is requirement to connect to the target database in the first place. A live database has been copied to disk/tape. We want to make a new copy on a development server. But we still have to connect to the original live database. I once had a reason from Oracle for this in a tar dialogue however it did not sound very convincing to me at the time.
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 25 January 2002 10:10
To: Multiple recipients of list ORACLE-L
Subject: RE: More Rman q's
Hi,
Am I misreading all this, but should the auxiliary database be in existence
before I can Clone to it?
I created the init.ora and all directories and started TEST3 in nomount. I
created the password file and can connect to it with a connect string no
problem.
Another point is. Did you mean that the auxilliary database is the database
that needs to be cloned (source)?
Jack
"Jay Hostetter" <[EMAIL PROTECTED]> on 23-01-2002 15:41:35
To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
cc:
John and Jack,
I guess I didn't read Jack's info closely enough, since he states that
his auxiliary database is already started nomount. I wonder if the three
separate connect statements are the issue? When I duplicate a database, I
use the following commands from the OS:
>rman target internal/xxx@original catalog rman/xxx@rman1 auxiliary /
rman>@prod_to_test
rman>exit
My ORACLE_HOME and SID are set for the auxiliary database. I have started
the auxiliary database NOMOUNT, which basically just starts the background
processes. Password files are required for the remote login (this is on
a different server). I don't use db_file_name_convert or
log_file_name_convert, because I group the data files differently on this
server (I don't have the exact same number of file systems as production).
After I have RMAN started, I do the following:
run {
#set until time "to_date('0108200220','mmddhh24miss')";
set until scn 316498395;
#set until logseq 1389 thread 1;
allocate auxiliary channel ch1 type disk;
set newname for datafile 1 to '/u03/oradata/BSCST/system01.dbf';
set newname for datafile 2 to '/u03/oradata/BSCST/rbs01.dbf';
...
...yadda yadda yadda...
...
set newname for datafile 49 to '/u04/oradata/BSCST/bill_image_data06.dbf';
set newname for datafile 50 to '/u04/oradata/BSCST/process_data03.dbf';
duplicate target database to BSCST
logfile
group 1 ('/u03/oradata/BSCST/redo1a.log','/u04/oradata/BSCST/redo1b.log')
size 2m,
group 2 ('/u03/oradata/BSCST/redo2a.log','/u04/oradata/BSCST/redo2b.log')
size 2m,
group 3 ('/u03/oradata/BSCST/redo3a.log','/u04/oradata/BSCST/redo3b.log')
size 2m;
}
Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA
>>> [EMAIL PROTECTED] 01/23/02 03:20AM >>>
Jay,Jack
I don't understand here how RMAN connects to auxilliary when Jack seems to
be saying that the database does not exist it all.
When I tested a duplicate database session I made the following notes which
indicated I need a remote_login_password file setting up. I assume you
didn't do this then Jack as the database was not created at this point in
time
Please correct me if I have misunderstood
John
My notes follow
+++
To allow this connection a remote_login_password file needs to be in place.
This can be created running the orapwd command
$ORACLE_HOME/bin/orapwd file$ORACLE_HOME/dbs/ orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (using the initSID_excl.ora file in
the
pfile directory).
Ensure that this works by performing a sqlplus internal @tnsnames_alias
with
the correct password and ensuring that a connection has been made.
This needs to be set up on the target environment as well as on the
auxillary server. Ensure that a connection can be made both locally and
remotely
sqlplus internal/password@tnsnames_alias
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-m
RE: Re: How can I tell if a procedure/package is running?
Title: RE: Re: How can I tell if a procedure/package is running? Sinardy, This only tells you if an object exists not if it is running - or even if it is valid unless status is elected from dba_objects as well. There was a thread around 30/10/01 with the appropriate title 'How can I tell if a procedure/package is running?' - I am not sure if it is the same thread that we are on now. The following quote came from a reply by Steve Adams You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See "executing_packages.sql" at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. HTH John -Original Message- From: Sinard Xing [mailto:[EMAIL PROTECTED]] Sent: 22 January 2002 08:40 To: Multiple recipients of list ORACLE-L Subject: RE: Re: How can I tell if a procedure/package is running? Hi, You can do Select object_name, object_type, status from dba_objects where object_type like '%PACK%' or object_type like '%PROCE%' order by 2,1; Sinardy -Original Message- [EMAIL PROTECTED] Sent: 22 January 2002 15:10 To: Multiple recipients of list ORACLE-L Can anyone give me a good example on how this works? Thanks in advance Roland Connor McDonald <[EMAIL PROTECTED]>@fatcity.com den 2001-10-25 01:45 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: I would say that the best way is in its coding typically by adding calls to dbms_application_info - which is great way of tying SQL to its owning PL/SQL as well. You might be lucky to catch it in sql_address in v$session (depending on what its actually doing at the time). Similarly, you could possibly glean some info from V$SQL via USERS_EXECUTING You could always try modify the proc which would probably hang on library cache pin or similar - hardly a recommended way of course :-) hth connor --- Doug C <[EMAIL PROTECTED]> wrote: > How can I tell if a stored procedure or package is > in the middle of execution? > (for lack of doing what it does).. I've heard of > parse locks, is that a way? > > Thanks, > D > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Doug C > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Nokia Game is on again. Go to http://uk.yahoo.com/nokiagame/ and join the new all media adventure before November 3rd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet a
RE: Sqlloader
Title: RE: Sqlloader See the following note sent on 4/12/01 - it should assist John Try the following example: TMD=`date '+%Y/%m/%d'` TMS=`date '+%Y%m%d%H%M%S'` sqlplus -s internal < set verif off pages 0 echo off feed off term off lines 200 spool $SHELLSRCDIR/dbcontents.$TMS.log select 'alter database rename file '''||name||''' to '''||name||''';' from v\$datafile; column member format a50 select 'alter database rename file '''||member||''' to '''||member||''';' from v\$logfile; spool off whisky Regards Peter Lomax (Oracle DBA) Expertise Oracle SG/DSI/SIMBAD/AT&P OrangeFrance Bureau: *: - [EMAIL PROTECTED] (: (+33) (0)1 55 22 59 13 fax: (+33) (0)1 55 22 39 69 +++ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 22 January 2002 08:45 To: Multiple recipients of list ORACLE-L Subject: Sqlloader Hallo all you gurus, Anyone who can help me with this: I have this unix script: In the script I wamt to create a logfile for every day that I run this script so that thel logfile will look like: 2002-02-22laddabsg.log and when i run the script tomorrow I want it to be: 2002-02-23laddabsg.log anyone who can help me to complete this unix script This line executes the sqlloader command and it is this logfile I want to have the runningdate in too. sqlldr userid=konto/icakort control=/d31/appl/konto/bat/laddabsg.ctl log=/d31/appl/konto/log/laddabsg.log (See attached file: loadfast.sh) Thanks in advance Roland = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
pinning objects
Title: pinning objects
I wrote a database tuning document a while ago and the notes below are from the section on pinning. I do recall that the final comment re the difference betwen pinning and keeping was from a Steve Adams response to a question on this list.
The library cache forms part of the shared pool area. An important part of managing the library cache efficiently is to ensure that any frequently used packages are kept in the shared pool and not aged out. Oracle provides the dbms_shared_pool package to keep (or pin) packages, procedures, triggers and cursors. The object is never flushed out of memory until either an instance shutdown or it is explicitly unpinned. Objects that are accessed frequently are the best candidates for pinning. Pin packages as user SYS.
It is recommended that the following packages are pinned in memory at instance startup.
SYS.STANDARD
SYS.DBMS_STANDARD
SYS.DBMS_DESCRIBE
SYS.DBMS_UTILITY
SYS.DBMS_LOCK
SYS.DBMS_PIPE
SYS.DBMS_OUTPUT
The syntax to pin and then unpin a package is
EXECUTE DBMS_SHARED_POOL.KEEP ('sys.dbms_output');
EXECUTE DBMS_SHARED_POOL.UNKEEP ('sys.dbms_output');
1) Note that when the shared pool is flushed (ALTER SYSTEM FLUSH SHARED_POOL) kept packages are NOT flushed out.
2) Note that pinning and keeping are technically not the same. In the library cache a pin is a lock held by a particular session on one or more heaps of an object. With regard to the shared pool, a pin is a bitmap in the header of a recreatable chunk indicating whether it is eligible to be aged out or flushed from the shared pool. Library cache object heaps that are marked for keeping may not be pinned in either sense.
A script to identify suitable objects is :-
Col owner format a10
Col name format a25
Set lines 200
Rem
rem Look for high number of loads
rem
select owner,name,loads,executions,kept
from v$db_object_cache
where type like 'PACK%'
and loads > 1
order by loads
/
HTH
John
-Original Message-
From: John Dunn [mailto:[EMAIL PROTECTED]]
Sent: 11 January 2002 09:40
To: Multiple recipients of list ORACLE-L
Subject: pinning objects
I am investigating pinning some of the developers packages into memory.
Could you please clarify how this is done.
Also how can I identify which packages would beefit from pinning?
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: pinning objects
Title: RE: pinning objects
I wrote a database tuning document a while ago and the notes below are from the section on pinning. I do recall that the final comment re the difference betwen pinning and keeping was from a Steve Adams response to a question on this list.
The library cache forms part of the shared pool area. An important part of managing the library cache efficiently is to ensure that any frequently used packages are kept in the shared pool and not aged out. Oracle provides the dbms_shared_pool package to keep (or pin) packages, procedures, triggers and cursors. The object is never flushed out of memory until either an instance shutdown or it is explicitly unpinned. Objects that are accessed frequently are the best candidates for pinning. Pin packages as user SYS.
It is recommended that the following packages are pinned in memory at instance startup.
SYS.STANDARD
SYS.DBMS_STANDARD
SYS.DBMS_DESCRIBE
SYS.DBMS_UTILITY
SYS.DBMS_LOCK
SYS.DBMS_PIPE
SYS.DBMS_OUTPUT
The syntax to pin and then unpin a package is
EXECUTE DBMS_SHARED_POOL.KEEP ('sys.dbms_output');
EXECUTE DBMS_SHARED_POOL.UNKEEP ('sys.dbms_output');
1) Note that when the shared pool is flushed (ALTER SYSTEM FLUSH SHARED_POOL) kept packages are NOT flushed out.
2) Note that pinning and keeping are technically not the same. In the library cache a pin is a lock held by a particular session on one or more heaps of an object. With regard to the shared pool, a pin is a bitmap in the header of a recreatable chunk indicating whether it is eligible to be aged out or flushed from the shared pool. Library cache object heaps that are marked for keeping may not be pinned in either sense.
A script to identify suitable objects is :-
Col owner format a10
Col name format a25
Set lines 200
Rem
rem Look for high number of loads
rem
select owner,name,loads,executions,kept
from v$db_object_cache
where type like 'PACK%'
and loads > 1
order by loads
/
HTH
John
-Original Message-
From: John Dunn [mailto:[EMAIL PROTECTED]]
Sent: 11 January 2002 09:40
To: Multiple recipients of list ORACLE-L
Subject: pinning objects
I am investigating pinning some of the developers packages into memory.
Could you please clarify how this is done.
Also how can I identify which packages would beefit from pinning?
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email
(to the numbers or address above) immediately.
=
RE: login/exit problems
Title: RE: login/exit problems There was a problem with Oracle Trace being set in some V7 instances. A couple of files grew quite large. STOP PRESS just checked on Metalink and found note 45482.1 This suggest checking the $ORACLE_HOME/otrace/admin directory for a file process.dat and see if it growing (recently written to). -Original Message- From: afa2 [mailto:[EMAIL PROTECTED]] Sent: 08 January 2002 19:20 To: Multiple recipients of list ORACLE-L Subject: login/exit problems We have an Oracle 7.3.4 database that has been stable for a number of years but has started having problems during logon and exit with both sqlplus and remote logins. It hangs for up to a minute before connecting to Oracle server after you get $ sqlplus ***/*** SQL*Plus: Release 3.3.4.0.1 - Production on Tue Jan 8 19:06:28 2002 Copyright (c) Oracle Corporation 1979, 1996. All rights reserved. and similarly following an exit from Oracle server after disconnecting Disconnected from Oracle7 Server Release 7.3.4.0.1 - Production With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.4.0.0 - Production before returning to OS command line prompt lsnrctl, tnsping, etc suggest port is okay. Stopping and restarting database make no difference. Any advice or help will be much appreciated. Tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: afa2 INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Becoming a DBA questions
Title: RE: Becoming a DBA questions Mark, Why do you sound so suprised Mark? £120 / hour - wouldn't get out of bed for that. As Lee says he is either very lucky, specialised or both. Wherever the job is in the UK I will undercut by £10/hour :-) John -Original Message- From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 16:06 To: Multiple recipients of list ORACLE-L Subject: RE: Becoming a DBA questions Mark, Is this in the UK ?? Those sorts of rates were being bandied around a while ago but even some of the better ones these days (and they are few and far between) are round about the 70 per hour tops. Up here in the N.E that drops to something around 50. Mind you they are about as rare as rocking horse dung !!! Lee -Original Message- Sent: 07 January 2002 15:57 To: Multiple recipients of list ORACLE-L We actually heard in the office today that one of our contacts is currently forking ?120 PER HOUR for an Oracle contractor that has experience on UNIX / AIX based systems. This guy is working on a 40 hour per week basis - and the cash is paid directly to him! He is supposedly a very competent DBA. With this in mind: Per week = ?4,800 Per Month = ?19,200 Per Year = ?249,600 () I know these numbers may not be "true" as this guys is a contractor, and may not have 52 weeks of the year booked up - but.. For you guys an' gals in the US that equates to: Per Hour = $172 Per Week = $6,900 Per Month = $27,600 Per Year = $358,775 Now THAT'S what I call making REAL money! -Original Message- [EMAIL PROTECTED] Sent: 07 January 2002 03:30 To: Multiple recipients of list ORACLE-L Yeh, I thought wow as well My question is this is it really possible to earn that sort of money as a DBA in the US?, and no I aint looking for a job over there. Just curious if this is total bull We hear rumours routinely about salary levels in the US and they tend to contradict themselves sometimes figures such as this and then sometimes a lot, lot less. But then the same applies to the market here. There is probably a couple of key DBA's in Sydney and Melbourne that are 6 figures and then there is a lot of DBA's over here that earn more like midddle to high 5 figure incomes. Sydney figures always are or should be higher Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical Consultant WWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom "The People, The Experience, The Vision" = "Sinard Xing" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/01/2002 11:40 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Fax to: Subject: RE: Becoming a DBA questions 200K US$ ??? Wow -Original Message- [EMAIL PROTECTED] Sent: 05 January 2002 02:16 To: Multiple recipients of list ORACLE-L Lemme get this right. This guy is a *new* DBA. He's making 150k and he's not even a senior DBA, where he can make 200k? That's all from me. I'm gonna go sulk now. Jared DENNIS WILLIAMS list ORACLE-L <[EMAIL PROTECTED]> TOUCH.COM> cc: Sent by: Subject: RE: Becoming a DBA questions [EMAIL PROTECTED] m 01/04/02 09:05 AM Please respond to ORACLE-L The following eweek article might be of interest. If the link gets mangled, the article is at http://www.eweek.com Following the Data to a DBA Job by Jeff Moad. http://www.eweek.com/article/0,3658,s%253D703%2526a%253D20563,00.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services --
RE: Criteria for handoff from development
Title: RE: Criteria for handoff from development Taking a slight diversion away from data modelling issues a set of Production type issues need to be addressed prior to going live These could include Signed off user acceptance testing, Backup strategy, devised, tested and signed-off Expected performance targets and response times (part of an SLA really) Signed off volumetric testing. Some of the major issues when going live appear when real volumes of data have been entered and a query that ran well against 500 rows in developmenbt does not do quite as well against 500,000 in live. Network impact assesment Data archiving and houskeeping routines agreed and tested I am sure there are many others items that listers can supply John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 13:50 To: Multiple recipients of list ORACLE-L Subject: RE: Criteria for handoff from development I agree with the column naming comments. I would find it hard to over-emphasize the need for a column naming convention that allows you to know what table a column belongs to, whether it's a primary or foreign key, and ,if it's a foreign key, then what the name of the base table and column are. I also agree with the comments on choosing a primary key. If the database is never going to by synched with an outside database then the best choice for a primary key is a sequence generated number. Personally, I wouldn't worry too much about normalization problems if they're deliberately done. It's easy to gong developers over normalization, but sometimes a denormalized table is necessary for performance. In fact, if the data model is perfect 3rd normal form then you'll probably have to do some denormalization. All that being said, it's silly to have the developers present you with a data model. You should have been involved in developing the data model from the very beginning. But life, or management, is always presenting us with new "opportunities." Good luck. "Mercadante, Thomas F" To: Multiple recipients of list ORACLE-L @labor.state. cc: ny.us> Subject: RE: Criteria for handoff from Sent by: root development 01/04/2002 03:50 PM Please respond to ORACLE-L Dennis, First of all, I would tell your manager that 90% of tuning is in writing good queries no matter what the data model looks like. Unfortunately, you receiving a data model and expecting to perform miracles is pretty naive of the organization. This is a classic example of how NOT to do things. Saying that, I would look closely at the model and check for the following: Look closely for normalization problems. If you see repeating fields in a table, reject it and tell them to change it. Look for column-naming standards. If they do not have them, make some up and enforce them. Some common naming standards would use a suffix to indicate the type of data the column is holding. Things like _DATE _NBR _FNAME _LNAME _ID and _CODE would indicate date, number, standard length first and last name, Id type columns indicating it is a primary key (possibly) an integer value, and a Code column indicating that this is a foreign key to another table. This is s important for report-writing people on the back-end of the project. They can implicitly see that the column has a certain value by the name. Ask how they determined primary key values for all tables. Specifically, how do they KNOW that the values will be unique. Question everything you see. This is probably the biggest area of concern that I would have. Non-db designers will always make a mistake here. I developed a db once that used the soc-sec as the pk. WRONG! The db was at a college. Want to know how many parents use thei
RE: Problem Granting Roles
Title: RE: Problem Granting Roles What exactly is the error message - if it is table or view does not exist (ORA-904) then I suspect that it is synonyms that are not working/created. get the user to select from the full table name sys.x and see what happens. -Original Message- From: Ken Janusz [mailto:[EMAIL PROTECTED]] Sent: 02 January 2002 12:30 To: Multiple recipients of list ORACLE-L Subject: Problem Granting Roles I granted myself roles via SYS. I have another user on our server (W2000 / 8.1.7) and I have gone through the same process of granting the new user roles via SYS. However, when she connects to the DB she gets the message that the objects don't exist. The owner of the DB is me. What am I doing wrong? Should I grant her roles from my user name or from SYS? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: XP Professional & 9i
Title: RE: XP Professional & 9i I think it is our problem actually because the majority of questions you ask could be answered quite easily by looking at the manual or using Metalink. Is your company just a one or 2 man band or is it a reasonably sized company. If so I would have thought that having Oracle support was a necessity rather than a nice to have. I once turned down a contract at a company because they did not have 24 hour support and I was expected to do out of hours support for which any call was made would be charged for. I and I am sure many others have got into a situation where we are on our own/tired/stuck/confused whatever and a call to support has been very helpful, not just for the answer but more as someone to talk to, ensure that you are not about to do anyuhing stupid (too late for me :-)). Knowadays there is a lot more on-line resource available and this list is getting better all the time but I personally think Metalink is invaluable both for resolving immediate problems and digging deeper into areas of interest. John -Original Message- From: Ken Janusz [mailto:[EMAIL PROTECTED]] Sent: 29 December 2001 17:55 To: Multiple recipients of list ORACLE-L Subject: RE: XP Professional & 9i Jared: We are not on MetaStink because my company won't pay for it. It's their problem not mine. Ken -Original Message- Sent: Saturday, December 29, 2001 11:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: XP Professional & 9i That brings rise to the question "Why aren't you a member of MetaStink?" Have you purchased Oracle Support? If so, you likely have access to MetaLink. If not, you need to. You'll never be able to successfully implement an Oracle based application on 9i without support. You at least need to have access to patches. Jared On Saturday 29 December 2001 06:25, Ken Janusz wrote: > We are not a member of metastink. I looked at OTN an it's only certified > up to W2000. So, I was wondering if anyone knew if it is cert. for XP > Prof. or when it will be? > > Thanks, > Ken > > -Original Message- > Sent: Saturday, December 29, 2001 7:50 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: XP Professional & 9i > > Ken, go to metalstink and look for the certification matrix. > > joe > > Ken Janusz wrote: > > Will 9i run on XP Professional client? And, no I am not interested in > > putting Linux on my home PC at this time. > > > > Thanks, > > Ken Janusz, CPIM > > Database Conversion Lead > > Sufficient Systems, Inc. > > Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: Exp / Imp Utility Questions
Title: RE: Exp / Imp Utility Questions 1) You cannot have read the docs to well not to know the answer to Q1 Hint - look at the ignore parameter to imp 2) It all depends if the triggers have been run once to change the data that is now in the conversion db. You may have a trigger that adds 1 to a field. If the data has been populated into a table with the trigger enabled it will have value of field + 1. If you then import the data into your test db with a trigger enabled it will action the trigger and the field will then have the value of field +1 +1. Remember import is only a fancy way of typing insert into for each row of data 3) As you say, it is a test database, experiment with a couple of tables and find out the answers for yourself. That is the best way of learning John -Original Message- From: Ken Janusz [mailto:[EMAIL PROTECTED]] Sent: 28 December 2001 12:45 To: Multiple recipients of list ORACLE-L Subject: Exp / Imp Utility Questions I'm doing a DB conversion to 8.1.7 on W2000. I have converted the old tables / data to the new application on my conversion server. Now I have to load it onto a test server at a different site. I will be using tables= parameter to a select group of tables / data. I have not found the answers to my questions in the documentation. 1. Can I use Exp / Imp to just move the data? Or, does this utility move the data and the table structures? 2. The conversion DB that I have only has the tables, PK's, FK's, and indexes and no triggers, functions, cursors, etc. (they are created by another script). There is no application code attached to this DB. The DB I will be loading to is fully functional - all the PK's, FK's, triggers, functions, et al. Will loading the data from my conversion DB cause problems with the test DB? 3. I know I will have to disable the FK's on the test DB. But, what about the triggers on the applicable tables? Should they be disabled? Anything else I need to know before I get rolling on this? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: online backup help
Title: RE: online backup help Jain, This is not really an Oracle issue, rather a sys admin one. Hot backup is Oracle method of ensuring that a datafile can be copied whilst still in use. So just issue the command to put it into backup mode, backup the file by whatever means and then take the datafile out of backup mode. You will have to experiment as to whether you can secure files to a remote tape device. If you have a locally mounted tape drive you should not have a problem at all. If you have not it may be best to copy the datafile(s) (when in backup mode) to a disk drive and then copy them to tape later. John -Original Message- From: Anand Jain [mailto:[EMAIL PROTECTED]] Sent: 24 December 2001 08:35 To: Multiple recipients of list ORACLE-L Subject: online backup help Dear All, We are in the planning/testing stage right now. I wanted to know if it is possible to backup the Oracle database online, i.e. at the time Oracle database is running. This is also called hot backup. If yes, do I have to install the backup tape device to the same server where the Oracle is installed? Or can I install tape drive to any other server and from there also can I take online/hot backup of the oracle database? Regards Jain Anand -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anand Jain INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
RE: unused blocks BELOW HWM - Thanks
Title: RE: unused blocks BELOW HWM - Thanks I agree with your reasoning Dennis. I have long felt that one of the problems with the list is that the original raiser of the question should have some sort of responsibility for summarising the various responses and posting a short summarised reply showing the solutions(s) that worked for him/her This type of question where various solutions were proposed is the ideal candidate for this type of summarised response. I used to be on a Unix list and that strategy was used quite well there. John -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 19:10 To: Multiple recipients of list ORACLE-L Subject: RE: unused blocks BELOW HWM - Thanks Naaah, I'm just the proverbial lazy DBA. Gene received many replies, including "it can't be done". Since he happened to point out that two of them worked and produced the same results (an excellent sanity check), I thought it would benefit everyone by telling us which ones worked. I realize that we could have each tried each of the strategies and discovered the two for ourselves, but my principle is if there is an easier way, why not? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 11:35 AM To: Multiple recipients of list ORACLE-L Uh, amen. Isn't that the purpose of the list? Or is there a *third* list, one "on topic", a second "off topic", and a third where all the answers really are? -Original Message- Sent: Tuesday, December 18, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Gene - Now that you've gotten your answer, would you mind to post both of the methods that you found to work so that the rest of us could learn? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 18, 2001 7:05 AM To: Multiple recipients of list ORACLE-L Hi. This is just to thank all who replied to my post. Based on what I read, I have got two different ways of calculating that number and they seem to produce the same result. thank you all (you know who you are) = __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other informa
RE: Deinstalling JServer
Title: RE: Deinstalling JServer Cherie - see the post below from sunilshivappa - 12/11/01 John +++ rmjvm.sql doesnot remove all the components properly. If your resources sizes are ok, then try following. If for any reason the installation fails then $ORACLE_HOME/javavm/install/rmjvm.sql has to be run. Unfortunately it does not remove all the components. Following workaround has to be used to get over the bugs. 1. Restart the database. 2. Start a different session other than the one that started the database. 3. Run initjvmaux.sql 4. Run rmjvm.sql 5. Run following SQL queries, LOG on as SYS. --The minimum action to remove JIS trigger is running the following sql. drop trigger JIS$ROLE_TRIGGER$; delete from ducs$ where owner='SYS' and pack='JIS$INTERCEPTOR$' and proc='USER_DROPPED'; delete from aurora$startup$classes$ where classname='oracle.aurora.mts.http.admin.RegisterService'; delete from aurora$dyn$reg; Regards, sunil s. +++ -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: 17 December 2001 15:13 To: Multiple recipients of list ORACLE-L Subject: RE: Deinstalling JServer Cherie, I'm not entirely sure, as I've never done this before - but there is a package in ORACLE_HOME/RDBMS/ADMIN called utljavarm.sql, which seems to do the kind of thing you want. Maybe there is another that fits your needs better, as this one has a warning note about only using it to upgrade/downgrade to 8.1.5.. Rem Rem $Header: utljavarm.sql 12-jan-99.17:10:39 rshaikh Exp $ Rem Rem utljavarm.sql Rem Rem Copyright (c) Oracle Corporation 1999. All Rights Reserved. Rem Rem NAME Rem utljavarm.sql - Remove all java objects Rem Rem DESCRIPTION Rem This removes all the java objects from the data dictionary. Rem Rem NOTES Rem WARNING: This script is highly destructive. It should Rem only be run if you upgrading to or downgrading Rem from 8.1.5. Once this script is run all of your Rem java objects will be gone unless you have a backup!!! Rem Rem This script requires a significant amount of rollback Rem to execute. Rem Rem MODIFIED (MM/DD/YY) Rem rshaikh 01/12/99 - Created (for mjungerm) Rem Hopefully the list can point to one that fits your needs more precisely. Regards Mark -Original Message- [EMAIL PROTECTED] Sent: 17 December 2001 14:20 To: Multiple recipients of list ORACLE-L Does anyone know how to deinstall JServer? I accidentally installed it and I don't see anywhere in the documentation where it talks about how to remove the objects from the database. This is for Sun Solaris 2.6 Oracle version 8.1.7.2. Please reply directly to [EMAIL PROTECTED] Thanks, Cherie Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
