RE: How to find the last execution time of a Procedure.
Thanks John and everyone for their suggestions.
Best Regards,
Prasad
John Kanagaraj
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
ds.com cc:
Sent by: Subject: RE: How to find the last
execution time of a Procedure.
[EMAIL PROTECTED]
.com
01/23/2004 06:59
PM
Please respond to
ORACLE-L
Raj,
I am no X$ expert either, but X$KGLOB is exposed to us lowly DBAs as
V$DB_OBJECT_CACHE and KGLHDEXC is actually the EXECUTIONS column.
Prasada, you can check V$DB_OBJECT_CACHE for TYPE in ('PACKAGE','PACKAGE
BODY') and KEPT = 'NO' and keep pinning these using DBMS_POOL.KEEP via a
scheduled job. After a while, all those used packages will not only become
KEPT (and provide some side benefit of reducing reloads), you will not have
to store them back into the database... The KEPT = NO will avoid having to
revisit/manipulate those objects that were previously pinned. Of course,
this assumes that there is adeqauet Shared pool space and the Db is not
restarted in-between :)
YMMV!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and
do
not reflect those of my employer or customers **
-Original Message-
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to find the last execution time of a Procedure.
But you better check with experts as my knowledge of x$ is
feather-weight ... also there is a column on x$kglob called
kglhdexc ... to me it seems the execution count (I feel like
Mr. Monk already). so if execution count is 0 then you
can say that it actually got executed.
But if this doesn't work, in the next CTOUG meeting, I'll try
to hide away from you.
YMMV
Raj
---
-
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 1:24 PM
To: Multiple recipients of list ORACLE-L
Thanks for input Raj.
I was also thinking on the same lines (Querying v$views
periodically and
store it in some metadata table) if there is no easier way to
figure out
from DBA_ views.
As far as changing the production code, as you know, It has
to go thru the
dev/test databases first and then go thru the release process
to implement
into the production. It is painful process.
I will use x$kglob instead of changing production code and all
that release
stuff. Thanks for your help, Raj.
Best Regards,
Prasad
860 843 8377
***
***
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
RE: How to find the last execution time of a Procedure.
Thanks for your input, Naveen. But, It is hard to do that since everything is under Production Support Team control. Best Regards, Prasad Naveen, Nahata (IE10) To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: eywell.com Subject: RE: How to find the last execution time of a Procedure. Sent by: [EMAIL PROTECTED] com 01/23/2004 02:24 AM Please respond to ORACLE-L Not sure if that is what you want... and not sure if this will really work, just a quick thought... Invalidate the procedure, so next time it will be used, it will be recompiled, and then you can see at LAST_DDL_TIME in ALL_OBJECTS to find out when it was used first after invalidating. Regards Naveen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, January 23, 2004 1:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: How to find the last execution time of a Procedure. Thanks Raj. I have confidence on you and hope we will meet in next CTOUG meeting. Best Regards, Prasad 860 843 8377 Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: How to find the last execution time of a Procedure. Sent by: [EMAIL PROTECTED] com 01/22/2004 01:59 PM Please respond to ORACLE-L But you better check with experts as my knowledge of x$ is feather-weight .. also there is a column on x$kglob called kglhdexc ... to me it seems the execution count (I feel like Mr. Monk already). so if execution count is 0 then you can say that it actually got executed. But if this doesn't work, in the next CTOUG meeting, I'll try to hide away from you. YMMV Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 1:24 PM To: Multiple recipients of list ORACLE-L Thanks for input Raj. I was also thinking on the same lines (Querying v$views periodically and store it in some metadata table) if there is no easier way to figure out from DBA_ views. As far as changing the production code, as you know, It has to go thru the dev/test databases first and then go thru the release process to implement into the production. It is painful process. I will use x$kglob instead of changing production code and all that release stuff. Thanks for your help, Raj. Best Regards, Prasad 860 843 8377 **
How to find the last execution time of a Procedure.
Hi All, Is there anyway to find out from data dictionary views when was a database procedure/function last executed. Would like know the solution for 8i and 9i databases. We have some older code in the databases and do not know if any application is using it or not. I appreciate your help. Thanks Best Regards, Prasad * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * -- 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 find the last execution time of a Procedure.
Thanks for input Raj. I was also thinking on the same lines (Querying v$views periodically and store it in some metadata table) if there is no easier way to figure out from DBA_ views. As far as changing the production code, as you know, It has to go thru the dev/test databases first and then go thru the release process to implement into the production. It is painful process. I will use x$kglob instead of changing production code and all that release stuff. Thanks for your help, Raj. Best Regards, Prasad 860 843 8377 Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: How to find the last execution time of a Procedure. Sent by: [EMAIL PROTECTED] com 01/22/2004 11:39 AM Please respond to ORACLE-L not easily, but you could probably scan through x$kglob (frequently) and see if the name exists ... if it is, it means it was loaded for execution. Another solution might be to modify old code and have them add a row in a separate table using autonomous transaction to indicate they got executed. Then wait for couple of months and it will give you some good idea. Of course people who know more than I do will have better solutions. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:19 AM To: Multiple recipients of list ORACLE-L Hi All, Is there anyway to find out from data dictionary views when was a database procedure/function last executed. Would like know the solution for 8i and 9i databases. We have some older code in the databases and do not know if any application is using it or not. I appreciate your help. Thanks Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing 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). * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify
RE: How to find the last execution time of a Procedure.
Thanks Raj. I have confidence on you and hope we will meet in next CTOUG meeting. Best Regards, Prasad 860 843 8377 Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: How to find the last execution time of a Procedure. Sent by: [EMAIL PROTECTED] com 01/22/2004 01:59 PM Please respond to ORACLE-L But you better check with experts as my knowledge of x$ is feather-weight .. also there is a column on x$kglob called kglhdexc ... to me it seems the execution count (I feel like Mr. Monk already). so if execution count is 0 then you can say that it actually got executed. But if this doesn't work, in the next CTOUG meeting, I'll try to hide away from you. YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 1:24 PM To: Multiple recipients of list ORACLE-L Thanks for input Raj. I was also thinking on the same lines (Querying v$views periodically and store it in some metadata table) if there is no easier way to figure out from DBA_ views. As far as changing the production code, as you know, It has to go thru the dev/test databases first and then go thru the release process to implement into the production. It is painful process. I will use x$kglob instead of changing production code and all that release stuff. Thanks for your help, Raj. Best Regards, Prasad 860 843 8377 ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing 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). * PRIVILEGED AND CONFIDENTIAL: This communication, including
Re: Hiding passwords
There is a good discussion in asktom website on this topic. Here is the link : http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:142212348066 Hth. Best Regards, Prasad O'Neill, Sean [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] non.ie cc: Sent by: Subject: Hiding passwords [EMAIL PROTECTED] .com 09/23/2003 10:24 AM Please respond to ORACLE-L So the story goes like this. We're a NT/W2K shop. We have various scripts that run DB related jobs but these are in plain text and we'd like to hide these passwords in some way to allow scripts to run but the passwords not be visible to potential prying eyes. Has anyone cracked this one yet. I've had a trawl around MetaLink but found nothing of substance. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing 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).
Congratulations Arup (DBA of the Year)
Just came to know from CTOUG news that Oracle has chosen Arup Nanda(our List member) as DBA of the Year. Congratulations Arup. thought I will share this news with the group. CTOUG Board Member, Arup Nanda, Chosen as DBA of the Year Oracle has chosen our very own CTOUG board member, Arup Nanda, as the DBA of the Year, out of some quarter million DBAs worldwide. There will be a felicitation ceremony at OracleWorld and the award recipients will be featured in Nov-Dec issue of Oracle Magazine. Thought you would like to know, as a part of the CTOUG community. In his interview, he has mentioned CTOUG, so watch for it. Arup is President of Proligence (http://www.proligence.com/). Thanks. Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Change Data Capture
I am testing the Change Data Capture and for the most part I am able to get it working. I have the following issues/questions. 1. I published 3 columns out of 9 column table and subscribed those 3 columns. When I make a change to the columns that were not published/subscribed, I see change records in the Change table and getting the change record in the subscriber view. Is that the way it suppose to work. I thought I should not see the changes to those records because I have not subscribed to those columns. Is there anyway we could set it up so that we only see the change records for the columns we subscribed are modified. 2. Is it possible to create a change on the Target(destination) database? I am aware that there is a performance overhead on the source system application because it has to insert the records across the databases. But, We would like to know whether it is possible? 3. If we want to fetch all the records in the change table (not just the changed records), How do I set it up so that it can read from earliest SCN in the change table. Another question related to this is, How do I read the change table from certain date timestamp? There is a packaged procedure called dbms_cdc_utility.set_window_start and I think this one set the window so that it can read the data from earliest SCN. I tried this but no luck. Unfortunately, I could not find any documentation on this package. 4. Documentation says that old data in the change table get purged automatically as long as there are no active subscriptions. Looks like it is not happening and I also tried manually using the DBMS_CDC_PUBLISH.PURGE and the old data did not get purged. 5. Change table captures only the changed records. Is there anyway I can see all the source table records in the change table initially. It helps for the initial load to go after the change table rather than the source table. Database : 9.2.0.1 OS : Solaris Thanks for your help. Best Regards, Prasad 860 843 8377 -- 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).
DBMS_STATS and CBO
We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- 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: After Logon Trigger and Import
Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and Oracle 8.1.7.4 Thanks. Best Regards, Prasad 860 843 8377 Prasada R Gunda To: [EMAIL PROTECTED] 08/05/2003 12:21 cc: PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- 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: DBMS_STATS and CBO
Thanks Govind for your reply and suggestion.
Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
Best Regards,
Prasad
860 843 8377
Govind.Arumugam@
alltel.com To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
.com
08/09/2003 12:14
AM
Please respond to
ORACLE-L
Prasad,
We ran into the same problem when we did FOR ALL INDEXED COLUMNS using
DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then
CBO started to use the indexes.
execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
Hope this helps.
Govind
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 9:24 PM
To: Multiple recipients of list ORACLE-L
We have a table (monthly fact table) which has 24 partitions and
partitioned by month. There is data in only 3 partitions. All the indexes
are locally partitioned.
In dev db, I analyzed the table and indexes with analyze table..compute
statistics. When I query the no. of rows group by month, it returns the
query in couple of seconds and does the index scan(bitmap) on month column.
I created this table in production db and this time I used the dbms_stats
to create the statistics (compute both on table and indexes). When I run
this query, it does full table scan. The only way I could make it to use
index scan by specifying hints.
All the parameters(init.ora) are exactly the same on both databases and it
is 8.1.7.4. In prod db, I tried various combinations of
optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
There was no use.
Then, I did analyze table .. compute statistics on prod table but it was
still doing the FTS. I did not delete the stats created by dbms_stats
before using analyze table stmnt.
Finally, I deleted the stats generated by dbms_stats/analyze table before
generating stats again with Analyze table table compute statistics. It is
doing the index scan now.
I have no clue 1. why it does not use index when I generated the stats
w/dbms_stats. 2. why I had to explicitly delete the stats before generating
the stats again to make use of the index.
Thanks for your help in advance.
Thanks.
Best Regards,
Prasad
860 843 8377
--
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: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services--
After Logon Trigger and Import
Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- 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: IMP-00020: long column too large for column buffer size (22)
I thought I would share this information with the List. When we ftp'ed the file from NT to UNIX in Binary mode, we were able to import the tables we needed with 'tables' option in import. It wasn't working with 'full=y' option. Developer is verifying the data to make sure it looks good. Thanks. Best Regards, Prasad 860 843 8377 [EMAIL PROTECTED] ordlife.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: IMP-00020: long column too large for column buffer size (22) om 08/07/2003 06:14 PM Please respond to ORACLE-L Thanks for all those who responded my mail. When there was buffer parameter or record length parameter, It did not work. Instead of importing the full dmp file, If I try to import one table, it is working okay. I still have to try couple of alternatives and let the List Group know the outcome. Best Regards, Prasad 860 843 8377 Odland, Brad [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] orld.comcc: Sent by: Subject: RE: IMP-00020: long column too large for column buffer size (22) [EMAIL PROTECTED] .com 08/07/2003 04:09 PM Please respond to ORACLE-L What about that buffer setting in imp...?? DOn't you have to set that higher than default when importing data that has binary fields? The exp file might be okay. THis is the BUFFER input parameter to imp. Brad O -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Hi All, One of our developer ftp'ed the export (dmp) file from UNIX to NT in ASCII mode. He didn't know that it supposed to be done in BINARY mode. Now, he ftp'ed the dmp file back to UNIX in ASCII mode and tried doing the import. Looks like dmp file has been corrupted and Import is giving the following error. IMP-00020: long column too large for column buffer size (22) IMP-00028: partial import of previous table rolled back: 16380 rows rolled back/ Is there any way to fix the dmp file. Any ideas? I searched the metalink and looks like there is no way to correct it. It is on HP-UX and oracle 8.1.7.4 I appreciate your help. Thanks. Best Regards, Prasad 860 843 8377 -- 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: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: streams comments
Hi, I also started looking into Oracle streams for change data capture framework. My research is still in the beginning phase. I found that there is nice article (3 part series) on Change data capture using Oracle streams. Here is the link : http://www.dbasupport.com/oracle//ora9i/datacapture.shtml Also, there is a discussion forum on Oracle streams in Oracle Technet. Hope this helps. Best Regards, Prasad 860 843 8377 [EMAIL PROTECTED] dclinic.org To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: streams comments 08/13/2003 01:04 PM Please respond to ORACLE-L Hello, env: Oracle 9.2.0.3 on Solaris. I am looking for any comments, good or bad, regarding Oracle Streams. We are considering using this to transfer data between some 9.2 databases on multiple Sun machines, and hope that someone would share their experience with us. Our rationale for using this as opposed to other replication methods is: 1) streams uses log-based capture 2) captures DDL and DML 3) appears to be Oracle's future direction I have scanned the Oracle Streams documentation. A google search and a Technet search turned up a few (mainly non-technical) papers. I could not find any books on this subject. I have just vague questions at this point: 1) Are the Oracle docs a good source for setting this up? Do they omit any important information? 2) Are there any additional sources of helpful information? 3) Did you encounter any unexpected problems when configuring this tool? 4) Can you provider any measure (subjective is fine) of performance? 5) How painful is this to maintain? Thanks for any responses. -- 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS and CBO
Thanks Govind and Tanel for your replies.
Since I was testing both ways (analyze table and dbms_stats) before, I
cleared out the stats using analyze table..delete statistics and
dbms_stats.delete_table_stats.
Then, I generated stats using dbms_stats.gather_table_stats and it is
working fine now.
Thanks for your help.
Best Regards,
Prasad
860 843 8377
Govind.Arumugam@
alltel.com To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
.com
08/09/2003 01:19
PM
Please respond to
ORACLE-L
Prasad,
Make sure that low_value and high_value columns do not have null values ie.
generate statistics on all columns. Analyze table generates the correct
values for these columns whereas FOR ALL INDEXED columns in DBMS_STATS do
not. Once these values are available through FOR ALL COLUMNS SIZE 1 ( ie
do not generate histograms ), we seen the queries to be using the indexes
without any hints. We ran into this problem when we went to 9i for the
first time.
select
column_name,
NUM_DISTINCT ,
decode(LOW_VALUE ,null,null,'Full') Low ,
decode(HIGH_VALUE ,null,null,'Full') High ,
DENSITY ,
NUM_NULLS ,
NUM_BUCKETS ,
LAST_ANALYZED ,
SAMPLE_SIZE
from dba_tab_columns
where owner = 'your_owner'
and table_name = 'your_table';
Govind
-Original Message-
Sent: Saturday, August 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Hi!
Maybe you used analyze command without column analyzing clause, but used
dbms_stats package with column analyze clause (for all columns parameter).
Or it just could be because dbms_stats calculates some stats somewhat
differently (supposedly better), than old analyze command (average column
length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in
histogram. (In hist_head$ instead of histgrm$).
As an alternative to Govind's suggestion, you could increase SIZE parameter
(maximum is 254) to give CBO better understanding of data distribution.
Note that you should be careful with the METHOD_OPT parameter in
gather_schema_statistics procedure in version 9i, because if you supply
invalid parameter there, then the procedure just does nothing and returns
without error! You should verify from last_analyzed column to see whether a
segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k,
it
might be fixed on newer patch levels).
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, August 09, 2003 7:29 AM
Thanks Govind for your reply and suggestion.
Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
Best Regards,
Prasad
860 843 8377
Govind.Arumugam@
alltel.com To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS
and CBO
.com
08/09/2003 12:14
AM
Please
Re: After Logon Trigger and Import
Thanks very much Arup for your suggestion. Actually, Tables and indexes are already exist in the schema. I just have to copy the data from one environment to other environment. So I thought I will make the indexes unusable, load the data and rebuild the indexes with nologging, parallel and compute statistics. Best Regards, Prasad 860 843 8377 Arup Nanda [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Subject: Re: After Logon Trigger and Import [EMAIL PROTECTED] .com 08/05/2003 08:29 PM Please respond to ORACLE-L Prasad, You can use SKIP_UNUSABLE_INDEXES=Y as an import parameter. But then again, why do that? Why not just impirt with INDEXES=N and then rebuild the indexes in parallel and with NOLOGGING? HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 1:19 PM Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and Oracle 8.1.7.4 Thanks. Best Regards, Prasad 860 843 8377 Prasada R Gunda To: [EMAIL PROTECTED] 08/05/2003 12:21 cc: PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
RE: IMP-00020: long column too large for column buffer size (22)
Thanks for all those who responded my mail. When there was buffer parameter or record length parameter, It did not work. Instead of importing the full dmp file, If I try to import one table, it is working okay. I still have to try couple of alternatives and let the List Group know the outcome. Best Regards, Prasad 860 843 8377 Odland, Brad [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] orld.comcc: Sent by: Subject: RE: IMP-00020: long column too large for column buffer size (22) [EMAIL PROTECTED] .com 08/07/2003 04:09 PM Please respond to ORACLE-L What about that buffer setting in imp...?? DOn't you have to set that higher than default when importing data that has binary fields? The exp file might be okay. THis is the BUFFER input parameter to imp. Brad O -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Hi All, One of our developer ftp'ed the export (dmp) file from UNIX to NT in ASCII mode. He didn't know that it supposed to be done in BINARY mode. Now, he ftp'ed the dmp file back to UNIX in ASCII mode and tried doing the import. Looks like dmp file has been corrupted and Import is giving the following error. IMP-00020: long column too large for column buffer size (22) IMP-00028: partial import of previous table rolled back: 16380 rows rolled back/ Is there any way to fix the dmp file. Any ideas? I searched the metalink and looks like there is no way to correct it. It is on HP-UX and oracle 8.1.7.4 I appreciate your help. Thanks. Best Regards, Prasad 860 843 8377 -- 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: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing 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
IMP-00020: long column too large for column buffer size (22)
Hi All, One of our developer ftp'ed the export (dmp) file from UNIX to NT in ASCII mode. He didn't know that it supposed to be done in BINARY mode. Now, he ftp'ed the dmp file back to UNIX in ASCII mode and tried doing the import. Looks like dmp file has been corrupted and Import is giving the following error. IMP-00020: long column too large for column buffer size (22) IMP-00028: partial import of previous table rolled back: 16380 rows rolled back/ Is there any way to fix the dmp file. Any ideas? I searched the metalink and looks like there is no way to correct it. It is on HP-UX and oracle 8.1.7.4 I appreciate your help. Thanks. Best Regards, Prasad 860 843 8377 -- 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: Union quries: INTERSECT, MINUS, etc
Jonathan, We use MINUS technique heavily in our DW environment to get the source system changes since we last extracted. We do 2-way minus (src to ods and ods to src). Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
