Re: Re: WHERE 1 = 1 (any info on this)
Hi! I think where num_field + 0 = 1 suppresses index, not the other way. Tanel. im assuming this is an old 'trick' from RBO days. Alot of these are still floating around. Wasnt there one when you wanted to force a full table scan you would go where num_field = 1 + 0; the + 0 forced the full table scan? I was on a project earlier this year and one guy told people to use it. Actually, that one is still valid. Along with using any function around a predicate column. In general, an expression instead of the column name in a predicate will force ignoring of any conventional indexes on that column. Of course, function-based indexes were made just for that. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: using temp tables for staging databases?
No, blocks won't fill up to PCTFREE in case a block is already above PCTUSED and the next insert would drop amount of free space less than PCTFREE. Blocks are just unlinked from freelist in this case. That means if you normally have 10byte inserts and occasionally have 4000 byte inserts in your table, then you might be wasting space due to premature unlink of blocks in freelist. So, as Arup already said, PCTFREE and PCTUSED definitely affect block space utilization, even in insert-only environment. Tanel. - Original Message - From: Binley Lim To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 6:39 AM Subject: Re: using temp tables for staging databases? Yes, I understand BBW, and how PCTFREE affects block density, and hence BBW. My point, which you appear to have missed, is PCTUSED does not affect block densitywhich is determined by PCTFREE. Lets take a simple case of 10% PCTFREE, and 40% PCTUSED. Blocks will fill up to 90% _no matter_ what you make the PCTUSED figure to be. Only if you havelots of updates/deletes that PCTUSED comes into play but then you have a 90% packed block to begin with, so its effect is marginal.PCTFREE alone will reduce block density if that is the aim. In your paper, you changed several parameters at the same time - including freelists which does not affect block density. From a statistical/testing point of view you cannot really draw any conclusions. Lower block density mayreduce BBWs, but you still have to process the same number of rows. And you have to process a higher number of blocks which incurs an additional cost. Question becomes - has the problem been simply shifted somewhere else? - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 4:19 AM Subject: Re: using temp tables for staging databases? Binley,The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs.When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention.How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways:(1) reducing the block size(2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring.I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl.Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same
Re: index full scan over an index fast full scan in an analytic f
Hi! You can't have ascending nor descending indesc fast full scans. This (asc/desc) only works with range or regular full scans. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 25, 2003 4:59 AM But the fact is, the access path is still a valid path if the user needs the data returned ordered on the indexed columns. Index_asc or Index_desc should do the job without extra cost. Waleed -Original Message- Sent: Friday, October 24, 2003 9:39 PM To: Multiple recipients of list ORACLE-L function? Tanel Tanel Poder wrote: As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) Full scan will search from index root block using branch blocks to first leaf block. And since all leaf blocks have pointers to next and previous leaf block in index, sequentially reading only leaf blocks is sufficient for returning all values in index, in order (keys are ordered inside leaf blocks as well). FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to record at SqlServer using Oracle
Hi friends How can i do for write data in SqlServer database using Oracle. Need I run the process in sqlserver or in Oracle ? What is necessary for it ? database link ? Regards Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eriovaldo Andrietta INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dba interview questions
Nope - you will get the diagram and basic description in Tom's book (and of course the Concepts manual) but that's not the idea of using this for an interview. As they (hopefully) draw the SGA you can drift into musings like: So - if all these changes are in memory - how do we ensure against not losing data if we have a power failure That (hopefully) leads us onto redo logs, which leads to more musings: But if we've only get three - what happens when all three are full , or if they're good - there is no such thing as full for a file - how does oracle 'know' that the redo is full etc etc etc... If its a production role - you simply head down the path that most appropriate for that. If its development, you start heading down other paths (say) when they talk about library cache ... eg parsing, blah blah blah... And perhaps the best thing is, you'll easily get to detect the honesty rating. Sometimes the best answer is I have no f...ing idea. That's fine with me, and so much better than a load of BS. The saddest thing I did last week was conduct 10 or so interviews (for developers, not DBA's). Not a single one could answer the question: What is a bind variable and explain its significance to Oracle :-( A couple gave me the time honoured answer of: Hmmm, yes, I used them in my last project but cannot quite remember the specificsspare me please Cheers Connor --- [EMAIL PROTECTED] wrote: that question is diagrammed and answered in tom kytes new book. :) im waiting to get asked it. there is a new ault book out on interview questions. I dont think they are very tough. I think situational questions are better. Have a development DB set up with things for the applicant to do. I find that most employers ask the same easy questions. Particularly developer questions From: Kirtikumar Deshpande [EMAIL PROTECTED] Date: 2003/10/24 Fri AM 09:49:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: dba interview questions The problem with such lists is that the prospects also have those... A few years ago Mike Ault published one. We used it to screen candidates via phone interviews. Two out of four I talked to gave me perfect answers: word to word from Mike's list. Use Conner's approach: Give the candidate a white/black board, and ask him/her to draw the SGA with all it interal structures, all background processes, and explain how all this works together. - Kirti --- system manager [EMAIL PROTECTED] wrote: Dear List,Can anyone send me a list of dba interview questions? Thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Replication
I'm sorry - been very busy these last several days. Yes, a somewhat flippant answer, but I built replication for our place (asynchronous, master-master, multiple remote instances with full constraint support) years ago, and its still running very reliably. Must be cheaper than Oracle's product... ! If you really want more details, then contact me off-line. peter edinburgh -Original Message- Sent: Wednesday, October 22, 2003 12:39 PM To: Multiple recipients of list ORACLE-L Peter, your reply was empty, could pls. send it again, maybe you have valuable information... rgds gb Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Gunnar=20Berglund?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Replication : ORA-04068: existing state of packages has been discarded
Hi List,
Coudl someone please help me out to resolve this issue?
1 begin
2* dbms_repcat.do_deferred_repcat_admin('scott_mg',FALSE);
end;
/
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of package SYS.DBMS_REPCAT_RPC has been changed
ORA-06512: at SYS.DBMS_REPCAT_MAS, line 812
ORA-06512: at SYS.DBMS_REPCAT, line 532
ORA-06512: at line 2
-tamizh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: How to record at SqlServer using Oracle
You can do it in whichever db you prefer. Oracle has a product called (If I remember it correctly) transparent gateways which will give you a dblink to a sqlserver db. MS supplies the ability to link to oledb data sources - they call it linked servers, both are in the respective documentation. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Eriovaldo Andrietta Sent: 25 October 2003 10:54 To: Multiple recipients of list ORACLE-L Subject: How to record at SqlServer using Oracle Hi friends How can i do for write data in SqlServer database using Oracle. Need I run the process in sqlserver or in Oracle ? What is necessary for it ? database link ? Regards Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eriovaldo Andrietta INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during
Whether or not you can do it across a dblink depends on the following, in no particular order: * the number of transactions occurring during the refresh period, and the amount of data resulting from them. * the usable bandwidth of the network connection * whether or not it is acceptable to have triggers on the production tables of the source system * the period, or frequency of the refresh, which, along with the network bandwidth and amount of data generated during the refresh period will determine if a fast refresh will work in your environment. That is, apart from the other technical requirements imposed on a fast refresh as outlined in TFM. * probably some other stuff I'm not thinking of at the moment, but I believe these are the primary technical considerations. The refresh period can be determined by simply polling your users on what an acceptable lag is. We have a 'reporting' database here that is based on materialized views from 2 different production systems. There are currently 53 MV's, all of which are fast refresh. 40 or so are refreshed every 2 minutes, and the rest are refreshed every 5 minutes. It all works very well, and keeps the Crystal Reports users out of the production systems. :) HTH Jared On Fri, 2003-10-24 at 20:49, Ryan wrote: no we dont need query rewrite. we load data every night across a database link. we drop and recreate all the tables from scratch. I thought about using materialized views. i thought they might load faster because of fast refresh. not sure if we can do that across a db link. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:14 PM refresh - for No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The
Re: Replication : ORA-04068: existing state of packages has been
Have you recently made any changes to your database?
Upgrades, etc?
This is the message you will receive when the state
of a package is invalid. It either needs recompiled,
as something it is dependent on has changed, and/or
it is broken for the same reason.
One possible explanation for this is an incomplete
upgrade.
You can recompile all stored code in the database
by logging in as SYS and running $ORACLE_HOME/rdbms/admin/utlrp.sql.
There's another script for this as well, but I can't recall
it at the moment. The above script is generally used as
a final step in an upgrade.
Jared
On Sat, 2003-10-25 at 10:14, [EMAIL PROTECTED] wrote:
Hi List,
Coudl someone please help me out to resolve this issue?
1 begin
2* dbms_repcat.do_deferred_repcat_admin('scott_mg',FALSE);
end;
/
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of package SYS.DBMS_REPCAT_RPC has been changed
ORA-06512: at SYS.DBMS_REPCAT_MAS, line 812
ORA-06512: at SYS.DBMS_REPCAT, line 532
ORA-06512: at line 2
-tamizh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Replication : ORA-04068: existing state of packages has been
Thanks Jared.
Recently we did run catrepr.sql and catrep.sql to recreate replication catalog.
I was getting the same error even after recompiled all the invalid objects(few
packages were invalid). Then restared my instance and now it is woking fine.
Thanks again
-tamizh
- Original Message -
Date: Saturday, October 25, 2003 2:04 pm
Have you recently made any changes to your database?
Upgrades, etc?
This is the message you will receive when the state
of a package is invalid. It either needs recompiled,
as something it is dependent on has changed, and/or
it is broken for the same reason.
One possible explanation for this is an incomplete
upgrade.
You can recompile all stored code in the database
by logging in as SYS and running $ORACLE_HOME/rdbms/admin/utlrp.sql.
There's another script for this as well, but I can't recall
it at the moment. The above script is generally used as
a final step in an upgrade.
Jared
On Sat, 2003-10-25 at 10:14, [EMAIL PROTECTED] wrote:
Hi List,
Coudl someone please help me out to resolve this issue?
1 begin
2* dbms_repcat.do_deferred_repcat_admin('scott_mg',FALSE);
end;
/
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of package SYS.DBMS_REPCAT_RPC has been
changed ORA-06512: at SYS.DBMS_REPCAT_MAS, line 812
ORA-06512: at SYS.DBMS_REPCAT, line 532
ORA-06512: at line 2
-tamizh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services -
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
---
--
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: What happened to Howard Rogers ?
Howard's response is below. Hopefully this makes things clearer for those who are still interested. I worked for Oracle for 4 years, producing my own stuff in my own time, to elaborate on questions that perplexed me, and about which I would often get asked by Oracle course students -because the official material either didn't cover it, covered it badly, or just plain mis-informed. Oracle claimed copyright on the lot, so I had to remove the material (Lydian Third is a site which copied the lot first, and despite repeated requests still hasn't removed it). In June this year, I asked for permission to have a website again, offering to have all material and content vetted by anyone Oracle cared to choose for the job, before it went up. They refused. I also asked for permission to stay at home when I wasn't training, so that I could do research on Oracle matters. They refused that too. In August, I therefore resigned. I finished work *for* Oracle on October 6th. I had two weeks of leisure, and now I contract back to Oracle, teaching much as before. Only this time, I get to write my own material, and when I'm not training, I can stay at home and do real research. I was never sacked by Oracle. Regards HJR -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: index full scan over an index fast full scan in an analytic f
Correct, I did not read the post carefully, thought he was saying Full scan does not return rows in order, so was stating the fact that rows come sorted using Index Full Scan. My fault! Waleed -Original Message- Sent: Saturday, October 25, 2003 5:34 AM To: Multiple recipients of list ORACLE-L Hi! You can't have ascending nor descending indesc fast full scans. This (asc/desc) only works with range or regular full scans. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 25, 2003 4:59 AM But the fact is, the access path is still a valid path if the user needs the data returned ordered on the indexed columns. Index_asc or Index_desc should do the job without extra cost. Waleed -Original Message- Sent: Friday, October 24, 2003 9:39 PM To: Multiple recipients of list ORACLE-L function? Tanel Tanel Poder wrote: As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) Full scan will search from index root block using branch blocks to first leaf block. And since all leaf blocks have pointers to next and previous leaf block in index, sequentially reading only leaf blocks is sufficient for returning all values in index, in order (keys are ordered inside leaf blocks as well). FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Replication : ORA-04068: existing state of packages has been
There's another script for this as well, but I can't recall it at the moment. The above script is generally used as a final step in an upgrade. Jared Hi Jared, Did you mean: utlirp.sql - UTiLity script to Invalidate Recompile Pl/sql modules Which does the extra invalidate first that utlrp.sql doesn't do. utlirp.sql uses utlip.sql first to invalidate all PL/SQL and then uses utlrp.sql to recompile all the invalid packages. kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What happened to Howard Rogers ?
is that copyright thing something unique to Australia? I dont think they can claim that in the US unless you sign some documents first. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 25, 2003 4:39 PM Howard's response is below. Hopefully this makes things clearer for those who are still interested. I worked for Oracle for 4 years, producing my own stuff in my own time, to elaborate on questions that perplexed me, and about which I would often get asked by Oracle course students -because the official material either didn't cover it, covered it badly, or just plain mis-informed. Oracle claimed copyright on the lot, so I had to remove the material (Lydian Third is a site which copied the lot first, and despite repeated requests still hasn't removed it). In June this year, I asked for permission to have a website again, offering to have all material and content vetted by anyone Oracle cared to choose for the job, before it went up. They refused. I also asked for permission to stay at home when I wasn't training, so that I could do research on Oracle matters. They refused that too. In August, I therefore resigned. I finished work *for* Oracle on October 6th. I had two weeks of leisure, and now I contract back to Oracle, teaching much as before. Only this time, I get to write my own material, and when I'm not training, I can stay at home and do real research. I was never sacked by Oracle. Regards HJR -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Replication : ORA-04068: existing state of packages has been
Thanks Pete, that was the one. Jared On Sat, 2003-10-25 at 15:09, Pete Finnigan wrote: There's another script for this as well, but I can't recall it at the moment. The above script is generally used as a final step in an upgrade. Jared Hi Jared, Did you mean: utlirp.sql - UTiLity script to Invalidate Recompile Pl/sql modules Which does the extra invalidate first that utlrp.sql doesn't do. utlirp.sql uses utlip.sql first to invalidate all PL/SQL and then uses utlrp.sql to recompile all the invalid packages. kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What happened to Howard Rogers ?
I wouldn't be too sure about that. At least here in Canada everything job related you produce while employed belongs to the employer. The in my own time part opens a grey area, but if it is very job related and contains or is related to information obtained on the job I don't think you've got a leg to stand on. As a consultant I had a lawyer draw up an amendment that specifically retains my rights on anything I produce that is not specifically tied to the client's competitiveness - most of my work is in administering and tuning Oracle, Peoplesoft, or related middleware and is therefore generic. Otherwise everything I create under the contract is automatically property of the client. At 04:14 PM 10/25/2003, you wrote: is that copyright thing something unique to Australia? I dont think they can claim that in the US unless you sign some documents first. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 25, 2003 4:39 PM Howard's response is below. Hopefully this makes things clearer for those who are still interested. I worked for Oracle for 4 years, producing my own stuff in my own time, to elaborate on questions that perplexed me, and about which I would often get asked by Oracle course students -because the official material either didn't cover it, covered it badly, or just plain mis-informed. Oracle claimed copyright on the lot, so I had to remove the material (Lydian Third is a site which copied the lot first, and despite repeated requests still hasn't removed it). In June this year, I asked for permission to have a website again, offering to have all material and content vetted by anyone Oracle cared to choose for the job, before it went up. They refused. I also asked for permission to stay at home when I wasn't training, so that I could do research on Oracle matters. They refused that too. In August, I therefore resigned. I finished work *for* Oracle on October 6th. I had two weeks of leisure, and now I contract back to Oracle, teaching much as before. Only this time, I get to write my own material, and when I'm not training, I can stay at home and do real research. I was never sacked by Oracle. Regards HJR Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: UTL_RAW and slowness
Raj,
When I was writing the PL/SQL implementation of Blowfish, I also wrote a
version as a Java Stored Procedure so I could compare the performance of
the two implementations. For CPU intensive work (like encryption), the
Java Stored Procedure performed orders of magnitude better than the PL/SQL
version. I was using 8.1.7 at the time.
I am wondering why you need to flush the TCP connection after 128 bytes?
Have you profiled your code using DBMS_PROFILER to see where the time it
being spent?
Cheers,
Craig.
At 04:09 PM 23/10/2003 -0800, you wrote:
Raj,
I'll try to be tactful here, but that isn't encryption. It only looks like
encryption, and is in fact very easy to break.
Check out the article at
http://www.cybcon.com/~jkstill/util/encryption/encryption.html
There are PL/SQL implementations of RC4 and Blowfish there. RC4 is a
stream cipher
and Blowfish is a block cipher. Either would suit your purpose.
Blowfish is there courtesy of Craig Munday.
RC4 is there courtesy of a leak at RSA, but it's in the public domain now,
so that
doesn't matter anymore.
Also check Pete Finnigans site: http://www.petefinnigan.com/
HTH
Jared
Jamadagni, Rajendra [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/23/2003 02:54 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:UTL_RAW and slowness
Hi all,
I am experimenting with a block of pl/sql code (wrote myself) that uses
utl_raw to perform xor operation. (Note to Jared and Mladan, I can't use
Perl on production boxes, so don't even go there).
I take a message, encrypt 1 character send it out on a wire using utl_tcp.
After every 128 bytes, I flush the tcp connection. While this works fine
and within our acceptable range for smallish messages (up to 20-25K in
size), it nearly kills itself when working with larger messages (80k+).
Initially I could encrypt 128 characters (including conversion from/to
utl_raw) in about 50ms. Bt this increase progressively. When I reach about
98000 to 99000 range, it takes about 16 seconds to encrypt 128 characters.
I have logged tar with Oracle, but does anyone know if one could do a
block encryption (can't use standard algorithms, this is custom) like
encrypt 128 characters at a time instead of 1 ...
The skeleton code looks like this ...
msglen := LENGTH (msg_text);
nCharsSent := 0;
p('Encrypting data...');
FOR i IN 1 .. msglen
LOOP
ntcpchar := ASCII (SUBSTR (msg_text, i, 1));
r_chr:= utl_raw.cast_to_raw(CHR(ntcpchar));
nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'');
tcpmsglen := UTL_TCP.write_text (gv_tcp_conn, CHR(nenctcpchar), NULL);
nCharsSent := nCharssent + 1;
IF MOD(ncharssent,128) = 0 THEN
p('Before Flush ...');
UTL_TCP.FLUSH (gv_tcp_conn);
p('Connection Flushed at ' || ncharssent);
END IF;
--
END LOOP; -- FOR i IN 1 .. msglen
where p is a procedure that dumps supplied text to a trace file with a
timestamp that is up to 1 ms resolution. BTW this is a 9202 box. Also when
it starts getting slow, using nmon I can see that this process is
hogging a CUP at 99-100%. Of course this is a dev box, but my SA will not
like this on a production box.
Any ideas?
Thanks in advance
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
**
This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged,
attorney work product or exempt from disclosure under applicable law. If
you have received this message in error, or are not the named
recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
**5
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Craig Munday
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: state objects
Hi K Gopalakrishnan , Wishing you the same Thanks I understood what is state objects,but another question arisses,when this state objects having process status then what this PGA really do and why that process status recorded again in PGA? Please clarify - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 8:59 PM Wishing you all a very Happy Diwali.. The festival of lights ! State object is a structure inside the shared pool which keeps the details about every components (for example, process or session or enqueues) and their status (or state). Basically it is an operating system resource related to the oracle instance (or an OS resource held by oracle instance) which has multiple states (free/init and dead I think). The background process PMON is responsible for freeing those resources to the OS should any of the state object dies because of the process failure. Typically the SYSTEMSTATE dump will have the details about the state objects. Part of this info is visible in the V$sysstat. You will see the state object as SO in the system state dump. Along with you get the owner (holding that SO) and the state of the state object (is that too confusing?) which tells whether that SO is in the freelist or initialized or dead. For better understanding of the various state objects I would recommend to take a systemstate/process state dump and have a look at the trace files. Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:29 PM Does anybody have a definition for the Oracle shared pool component State Object? An Object that holds the state of something? Using Google, I get some hints, but just wondered if someone has something definite. Sultan - your question got my curiosity aroused. I'm sorry I lost your reply that confirmed you were indeed looking at the SGA. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 8:59 AM To: '[EMAIL PROTECTED]' Sultan - Are you referring to the Oracle shared pool components that are identified as state objects? A quick Google also revealed that Java has state objects. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 3:14 AM To: Multiple recipients of list ORACLE-L Can someone please explain what is 'state objects' in oracle and what is that real work? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sultan Syed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
