query rewrite doesn't work if based on a regular view
Dear All, query rewrite doesn't work on materialized view if based on a regular view which contains joins and coorelated subquery. Got the the following message: QSM-01063: query has a dictionary table or view QSM-01019: no suitable materialized view found to rewrite this query. I create a MV based on definition of the view. Anybody has clue on this or Oracle has restrictions on this kind of MV? TIA Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How does Oracle determine the materialized view eligible for text
Hi, Anybody knows how Oracle determine the materialized view eligible for textmatch or general rewrite? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Materialized view and index
Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
get sid (session id) and serial#?
DABs, Is there any way in my connection to get the sid and serial# for my own connection? Suppose I connect to Oracle db by sqlplus scott/[EMAIL PROTECTED] In this connection, SQL What shoud I input to get this sid and serial#? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: get sid (session id) and serial#?
Thanks all for the input. the script is select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1) But the user need select access to v$mystat Chuan -Original Message- Sent: Friday, 29 August 2003 15:19 To: Multiple recipients of list ORACLE-L Hi Chuan, can v$session help you this view has information like machine, osuser, username, sid, program, and others SQL desc v$session SQL select columns,... from v$session where username = 'SCOTT' and machine = 'YOUR_HOSTNAME' if you do telnet you will get 2 rows (if scoot is only use by you) is not Sinardy -Original Message- Sent: 29 August 2003 12:34 To: Multiple recipients of list ORACLE-L DABs, Is there any way in my connection to get the sid and serial# for my own connection? Suppose I connect to Oracle db by sqlplus scott/[EMAIL PROTECTED] In this connection, SQL What shoud I input to get this sid and serial#? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is there any data dictionary to check out the creation statem
Thanks, Chao Zhu. Since the query column in user_mviews is long data type, when I select query from user_mview, I can only see part of select statement. Even set linesize doesn't help. One more, what I want is to compare two select statements in querie column. But with query long data type, I could not do this. I just wondering why Oracle set this column as varchar2. Chuan -Original Message- Sent: Friday, 25 July 2003 18:19 To: Multiple recipients of list ORACLE-L statements SQL select query from user_mviews where mview_name='MV_END_ART'; QUERY SELECT * FROM ARTICLESCATALOG WHERE END_DATE_DTM SYSDATE AND END_DATE_DTM SYS Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 25, 2003 2:24 PM Hi, All, Is there any data dictionary in Oracle to check out creation statements of materialized view? Something like the user_source view about object creation. I have checked user_mviews, user_mview_joins, etc, but I haven't got one. TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Is there any data dictionary to check out the creation statements
Hi, All, Is there any data dictionary in Oracle to check out creation statements of materialized view? Something like the user_source view about object creation. I have checked user_mviews, user_mview_joins, etc, but I haven't got one. TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Unique constraint violation question.
Hi, All, When an unique constraint is violated, is there any way to know which record cause this problem? TIA, Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Example to populate the dimensional tables
Dear all, Are there articles or good examples to show how to populate the dimension and fact table in Dimensional(star) data model? Especially the time dimension. TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to run sql*plus and its command in Windows as batch job?
Hi, All, In unix, we can put following commands in a file and run that file. For example: $ORACLE_HOME/bin/sqlplus /nolog EOF connect test/test alter sesion set sql_trace=true select count(*) from product_temp p, invoice_temp i where p.invpsid=i.invoiceid; disconnect exit EOF What's the corresponding format on windows? Your input is precious, TIA Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
system/internal tables for query tree
DBAs, Does anyone by all means know the system/internal tables which store the information about query tree? Query tree is an internal representation of an SQL statement where the single parts built in(Join, projection..) are stored separately. Many thanks in advance. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
system/internal tables for QEP (Query Evaluation Plan).
DBAs, Does anyone by all means know the system/internal tables which store the information about QEP (query evaluation plan)? QEP here means the projection,restriction and join on tables after Oracle parses the SQL statement. What I want to do is to get this QEP and do some modification on it. Many thanks in advance. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
export in full mode but exclude particular user?
Dear DBA gurus, Has anyone got such experience when export database in full mode but exclude a particular user? TIA, Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
The life time of the data in v$sqlarea
Dear All, I wonder how long the life time of data in v$sqlarea. Is it a cumulative data collecting in v$sqlarea since last database startup?or do the data in v$sqlarea only reflect some recently data in Least Recetly List? Why do I ask this is after I used alter system flush shared_pool, I could not see some SQL statements in v$sqlarea where they were there before. TIA Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Reduce parse call for stored procedure?
Hi, DBA Guru, I have a stored procedure of a package which is called with execute immediate in a loop with runtime input parameters. I found that no. of parse calls(451983) is equal to no. of executions (451982). Is there any way such as set cursor_sharing=force or keep this stored procedure into shared pool to reduce the parse call down to one or some no.? TIA, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Any way to see the currently running SQL in one session.
Dear ALL, Is there any way to see the currently running SQL in one session? I used v$open_cursor, but there might be many SQL statements, I don't know which one is currently running. And more, if I execute a stored procedure, which SQL statements could be seen in v$open_cursor? Any clues would be much appreciated. TIA Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
How to find out those parameters set in session level.
Hi, All, Is there any way to find out the parameters setting in session level? These parameters are not the initialization parameters. For example, if alter session set use_stored_outlines=true; how could I find out the use_stored_outlines value later on? TIA Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: Cannot see parallel hint in outline?
Further to this question, I found another interesting thing. The results between ol$hints of outln and user_outlines are inconsistent after I swap the outline names(bad sql and tuned name). User_outlines's name didn't reflect the swapping. Is there something wrong? Thanks Chuan -Original Message- Sent: Wednesday, 11 September 2002 3:53 PM To: Multiple recipients of list ORACLE-L Hi, All, I created a plan as follows: create or replace outline hsubstr_vchfilename on select /*+ full(test) parallel(test, 10) */ max(num_sequencel) + 1 from test where substr(filename,1,3)='AAA'; And I got the following outlines: OL_NAME HINT#CATEGORY HINT_TYPE HINT_TEXT STAGE NODE# TABLE_NAME TABLE_TIN TABLE_POS HSUBSTR_VCHFILENAME 1 DEFAULT 0NO_EXPAND 3 10 0 HSUBSTR_VCHFILENAME 2 DEFAULT 0ORDERED 3 10 0 HSUBSTR_VCHFILENAME 3 DEFAULT 0NO_FACT(test) 31 test 1 0 HSUBSTR_VCHFILENAME 4 DEFAULT 0FULL(test) 3 1 test 1 1 HSUBSTR_VCHFILENAME 5 DEFAULT 0NOREWRITE 2 10 0 HSUBSTR_VCHFILENAME 6 DEFAULT 0NOREWRITE 1 10 0 Why couldn't I see hint_text for parallel hints? The actually execution plan output like follows: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2 8) 10 SORT (AGGREGATE) 21 PARTITION RANGE (ALL) 32 TABLE ACCESS (FULL) OF 'test' (Cost=62067 Card=1 07970 Bytes=3023160) Actually I want to see the following execution plan: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2 8) 10 SORT (AGGREGATE) 21 SORT* (AGGREGATE):Q115000 32 PARTITION RANGE* (ALL) :Q115000 43 TABLE ACCESS* (FULL) OF 'TBL_RAWAMA' (Cost=62067 Car :Q115000 d=5397992 Bytes=151143776) 2 PARALLEL_TO_SERIALSELECT /*+ PIV_SSF */ SYS_OP_MSR(MAX(A1.C0)) FROM (SELECT /*+ NO_EXPAND ROWID(A2 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT What am I missing? or Oracle just do this way? BTW, I set up: alter session set query_rewrite_enabled=true; alter session set use_stored_outlines=true; alter session set cursor_sharing=force; Any experience or idea? TIA Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: Aout plan stabilty matching.
Yes, But how could I ensure offensive statements in outline exactly matching the incoming statements? Thanks CHuan -Original Message- Sent: Tuesday, 10 September 2002 6:58 PM To: Multiple recipients of list ORACLE-L Chuan, If I remember right, you're supposed to turn the outlines on and then run your sikvel or procedure to catch the offensive statement(s). Regards, Ed Hi, All, From Oracle Doc: if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch. How could I ensure the incoming SQL text exactly match the SQL text in an outline? If I fish out an offensive SQL from library cache by some scripts in SQL*Plus, is this offensive SQL text identical to the incoming SQL text? Supposed this SQL text is extracted from stored procedure. Appreciated your experience. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Cannot see parallel hint in outline?
Hi, All, I created a plan as follows: create or replace outline hsubstr_vchfilename on select /*+ full(test) parallel(test, 10) */ max(num_sequencel) + 1 from test where substr(filename,1,3)='AAA'; And I got the following outlines: OL_NAME HINT#CATEGORY HINT_TYPE HINT_TEXT STAGE NODE# TABLE_NAME TABLE_TIN TABLE_POS HSUBSTR_VCHFILENAME 1 DEFAULT 0NO_EXPAND 3 10 0 HSUBSTR_VCHFILENAME 2 DEFAULT 0ORDERED 3 10 0 HSUBSTR_VCHFILENAME 3 DEFAULT 0NO_FACT(test) 31 test 1 0 HSUBSTR_VCHFILENAME 4 DEFAULT 0FULL(test) 3 1 test 1 1 HSUBSTR_VCHFILENAME 5 DEFAULT 0NOREWRITE 2 10 0 HSUBSTR_VCHFILENAME 6 DEFAULT 0NOREWRITE 1 10 0 Why couldn't I see hint_text for parallel hints? The actually execution plan output like follows: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2 8) 10 SORT (AGGREGATE) 21 PARTITION RANGE (ALL) 32 TABLE ACCESS (FULL) OF 'test' (Cost=62067 Card=1 07970 Bytes=3023160) Actually I want to see the following execution plan: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2 8) 10 SORT (AGGREGATE) 21 SORT* (AGGREGATE):Q115000 32 PARTITION RANGE* (ALL) :Q115000 43 TABLE ACCESS* (FULL) OF 'TBL_RAWAMA' (Cost=62067 Car :Q115000 d=5397992 Bytes=151143776) 2 PARALLEL_TO_SERIALSELECT /*+ PIV_SSF */ SYS_OP_MSR(MAX(A1.C0)) FROM (SELECT /*+ NO_EXPAND ROWID(A2 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT What am I missing? or Oracle just do this way? BTW, I set up: alter session set query_rewrite_enabled=true; alter session set use_stored_outlines=true; alter session set cursor_sharing=force; Any experience or idea? TIA Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Aout plan stabilty matching.
Hi, All, From Oracle Doc: if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch. How could I ensure the incoming SQL text exactly match the SQL text in an outline? If I fish out an offensive SQL from library cache by some scripts in SQL*Plus, is this offensive SQL text identical to the incoming SQL text? Supposed this SQL text is extracted from stored procedure. Appreciated your experience. Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Find the dependent objects?
Hi, All, I'll drop a table in production DB. I wnder whether there is a way to find the dependent object on this table beforehand. Thanks in advance. Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: Find the dependent objects? -- Attachment History Removed
Thanks Jack very much. It works very well.
CHuan
-Original Message-
Sent: Wednesday, 4 September 2002 6:28 PM
To: Multiple recipients of list ORACLE-L
break on Type skip 1 nodup
set verify off
set pages 100
col Owner for a30
col Object name for a32
Col Type for a12
select
type Type
, owner Owner
, name Object name
from
dba_dependencies
where
referenced_owner||'.'||referenced_name=upper('1')
order by
Type
, Owner
/
clear breaks
clear columns
call this script from sqlplus like @script owner.table
Chuan Zhang
Chuan.Zhang@transTo: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
act.com.au cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: Find the dependent objects?
[EMAIL PROTECTED]
04-09-2002 09:33
Please respond to
ORACLE-L
Hi, All,
I'll drop a table in production DB. I wnder whether there is a way to
find the dependent object on this table beforehand.
Thanks in advance.
Chuan
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
Young, niet toegestaan. Ernst Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst Young. Ernst Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Trace file with tkprof
Hi DBAs, Is there any way to get the same execution statistics between the finished sql statement and interrupted sql statement? Supposed table A have ten million rows. If select A.a, A.b from A where ..., in sqlplus session, actually the returned could be millions. I could not wait for all the selected rows coming out. I have to interrupte it in the process. Could I still get the same execution statistics in trace file? The same happened to set autotrace on in sqlplus session. I could only see the execution plan at the end of execution. Thanks in advance, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: Index hints?
Thanks all the responses. It's the syntax error like Naveen said. Oracle didn't detect the error, it just simply ignore the hints if the syntax is wrong. Thanks again, Chuan -Original Message- Sent: Friday, 30 August 2002 5:33 PM To: Multiple recipients of list ORACLE-L If you specify a hint Oracle is forced to use the index. Your hint's syntax is wrong. USE - /*+ INDEX(test index_a) */ Don't use the comma *** between the table name and the index name Naveen -Original Message- Sent: Friday, August 30, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Hi, All, On a million row table, test, there is primary key on column a. when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to Oracle, I should get the following explain plan. TABLE ACCESS BY INDEX ROWID test 1 INDEX UNIQUE SCAN PK_test 1 But I acctucally got : TABLE ACCESS FULL test 1 The database optimizer mode is choose, and I gathered the statistics on table and index using dbms_utitlity and analyze. In the session level, I also set optimizer_mode=first_rows to push optimizer to choose the index. According to Oracle Doc use hints to force the optimizer to use the optimal execution plan., to my understanding, the explain plan under such a situation, should be the first one. Does Oracle follow the hint? or to waht extents, it follows? Appreciated if someone guides me to the right direction. Chuan, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Index hints?
Hi, All, On a million row table, test, there is primary key on column a. when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to Oracle, I should get the following explain plan. TABLE ACCESS BY INDEX ROWID test 1 INDEX UNIQUE SCAN PK_test 1 But I acctucally got : TABLE ACCESS FULL test 1 The database optimizer mode is choose, and I gathered the statistics on table and index using dbms_utitlity and analyze. In the session level, I also set optimizer_mode=first_rows to push optimizer to choose the index. According to Oracle Doc use hints to force the optimizer to use the optimal execution plan., to my understanding, the explain plan under such a situation, should be the first one. Does Oracle follow the hint? or to waht extents, it follows? Appreciated if someone guides me to the right direction. Chuan, Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: dbms_utility and dbms_stat difference
Hi, Madhavan, Thanks very much for your invaluable input. Chuan -Original Message- Sent: Saturday, 24 August 2002 5:46 AM To: Multiple recipients of list ORACLE-L Hi Chuan, Just wonder whether analyze table name estimate statistics generates the stats at partition level. But for sure, it does not generate the stats at table level. Please correct me if wrong. Yes it does here is a sample (sorry for the long post) SQL analyze table atest delete statistics 2 / Table analyzed. SQL select table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tables 3 where table_name = 'ATEST' 4 / TABLE_NAME NUM_ROWS TO_CHAR(LAST_ANALY -- -- -- ATEST SQL select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS -- -- -- TO_CHAR(LAST_ANALY -- ATEST P1 ATEST P2 SQL analyze table atest estimate statistics sample 10 percent 2 / Table analyzed. SQL select table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tables 3 where table_name = 'ATEST' 4 / TABLE_NAME NUM_ROWS TO_CHAR(LAST_ANALY -- -- -- ATEST 199 23-AUG-02-11-33-21 SQL select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS -- -- -- TO_CHAR(LAST_ANALY -- ATEST P1 99 23-AUG-02-11-33-21 ATEST P2 100 23-AUG-02-11-33-21 SQL analyze table atest partition (p1) estimate statistics sample 10 percent 2 / Table analyzed. SQL exec dbms_lock.sleep(10) PL/SQL procedure successfully completed. SQL / Table analyzed. SQL select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS -- -- -- TO_CHAR(LAST_ANALY -- ATEST P1 99 23-AUG-02-11-33-31 ATEST P2 100 23-AUG-02-11-33-21 As Connor said, dbms_stats runs a lot heavier than analyze, I think, it's because it gathers the stats at table and partition level and related indexes. analyze also generates statistics for at table, partition and indexes. If you specify analyze table name compute statistics (generates for all associated objects) If you use for table , for all indexes, then it does only table level and index level. In brief, I prefer to choose analyze .. estimate statistics for non-partition table and dbms_stats for partitioned table. dbms_stats in my opinion executes much better and the parallelism is a big help. Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm -- Does exactly what it says on the tin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
dbms_utility, dbms_stats difference
Hi, ALL, What is the difference between dbms_utility and dbms_stats in terms of statistics gathering? Any clues would be much appreciated. Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
dbms_utility and dbms_stat difference
Hi, ALL, Sorry if this one is posted more than once. What is the difference between dbms_utility and dbms_stats in terms of statistics gathering? Any clues would be much appreciated. Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Materialized view selection.
Hi, All, Just wonder what's tool or methodology you are using to select materialized view in Oracle data warehouse environment? Many thanks in advance, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: What's STATSPACK
Thanks Chaos for your invaluable information. Chuan -Original Message- Sent: Thursday, 8 August 2002 7:34 PM To: Multiple recipients of list ORACLE-L Chuan Zhang£¬ hi, go to $ORACLE_HOME/rdbms/admin, ls sp*, you will find the sqls that needed by statspack. and vi spdoc.txt, you will know what statspack is. There is some paper on statspack performance tuning on otn. and donald burleson also wrote a book on statspack: oracle statspack high performance tuning, your can get it and take a look. Good luck. If you are in china, there is chinese translation already. 2002-08-07 23:43:00 You wrote: Hi, I saw lots of discussion related to STATSPACK. What's STATSPACK? Is it Oracle build-in function or third party product? Where can I download those scripts? Thanks in advance, Chuan Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chaos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Unix solaris forum.
Hi, DBAs, Could anyone recommend a good unix solaris discussion/news group for me? Thanks, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Oracle 9i upgrade exam.
Dear all, I want to prepare my self for oracle 9i upgrade exam . Much appreciated If anyone can tell me any sites or books for the preparation of this exam, Regards Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
data dictionary View about jobs
Hi, All, Is there any Oracle data dict view to see whether the dbms jobs is on the job queue or not. Why I ask this is that I use dbms_job.remove to remove one job, later on, I check it using user_jobs, it's still there. Thus issue dbms_job.remove again, this time, this execution seems hung there. I log into sys and issue dbms_job.remove, I got the following error ORA-23421: job number 41 is not a job in the job queue ORA-06512: at SYS.DBMS_SYS_ERROR, line 86 ORA-06512: at SYS.DBMS_IJOB, line 525 ORA-06512: at SYS.DBMS_JOB, line 166 ORA-06512: at line 2 Could anyone tell me why this happens? or is there any dict view to check the job queue? Thanks in advance, Chuan Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: stopiing write to listener.log
SHibu, In lsnrctl, set log_status=off. Chuan Oracle DBA Transact Communication, Ltd. -Original Message- Sent: Thursday, 7 February 2002 9:38 PM To: Multiple recipients of list ORACLE-L Hi How Can i stop Oracle from writing to the file listener.log?? regards, shibu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What tools for Oracle Data Warehouse ETL
Hi, DBA gurus, I am going to build our data warehousing project. In theory, I am confident but in reality, At this moment, I only have Oracle Data Warehousing Builder 3i in my hand. For those have built DW in Oracle, could you share your experience on the tools to build the data warehousing especially ETL process? Many thanks in advance, Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What tools for Oracle Data Warehouse ETL
Thanks Jared for your invaluable information. One more question, To my very little knowledge about these tools(DataStage, Informatica), do these tools generate Oracle executable scripts for ETL process? To my knowledge, OWBuilder(Oracle Warehouse Builder) is a good one for me at this moment in the sense of its 100% compatibile with Oracle. But I cannot get evaluation on to what extents to use it to create ETL. Maybe somewhere outside, such as Quest product, got such a kind of tool. -Original Message- Sent: Friday, 1 February 2002 11:00 AM To: Multiple recipients of list ORACLE-L In the second half of 2000, I participated in an extensive evaluation of ETL tools for a large data warehouse. There were two main players at that time that we considered: DataStage, owned by Informix, and now part of Ascential software http://www.ascentialsoftware.com/products/datastage/ Informatica was the other. http://www.informatica.com/ We looked at some other tools, notably IBM's offerings and one other that I can't recall. They didn't make it to the hands on evaluation stage for a number of reasons. Informatica and DataStage both appeared to be excellent tools. Informatica clearly had a better interface, while DataStage had a top down methodology that we preferred. The strength of these tools IMO is that they allow a team to effectively organize and manage the ETL process and all of the code that goes with it. You can do the same thing without these tools, but it will more difficult to manage. Tom Cox may have something to add to this, as he was leading that evaluation. Tom? Jared Chuan Zhang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/31/02 02:45 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:What tools for Oracle Data Warehouse ETL Hi, DBA gurus, I am going to build our data warehousing project. In theory, I am confident but in reality, At this moment, I only have Oracle Data Warehousing Builder 3i in my hand. For those have built DW in Oracle, could you share your experience on the tools to build the data warehousing especially ETL process? Many thanks in advance, Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Performance monitoring tools.
DBA gurus, I am just curious about what the performance tuning and monitoring tools you are using besides OEM. Your sharing is highly appreciated. Chuan Zhang Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Speed up Truncate tables
Hi All, Is there any way to speed up the truncating a big table with 12 million rows? Basically, I implemented truncating that big table on Production, but it affected the performance much, so I had to stop it in the middle of way. All the rows were truncated but the HWM was not shrunk at all. I want to do it again to get the space back. Is there any way to speed up this process? Platform: Oracle EE8.0.6 and Solaris 2.7 Thanks a lot in advance. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora-1031 when connect internal or / as sysdba locally
Dear all, I have this problem. When connect internal or connect /as sysdba in svrmgrl, it gives me insufficient priviledge. I connect it locally instead of remotly. I have not set up any password file or os authentication. It works well last week and suddenly happens today when I want to shutdown the db. The unix account I log into is in "dba" group which was created when Oracle software was installed. I already asked everybody related. Nobody touched any system stuff. Has anyone experienced this before? It's on Sun Solaris 2.7 and oracle EE8.0.6. Thanks for your advice. Chuan
Questions about Oracle World Wide Support
Dear DBA gurus, Could I ask you about the following questions: What are the benifits of having a support contract?what are the issues withnot having a support contract or what the risks do I have to take without the Support?Whatis it covering? I mean, Does it cover issues involved in our application systems? Your views and advice are vital for me. Thanks Chuan
find free space under HWM for a table
Hi, DBA gurus, Recently, I will archiving some big tables. How to find free space under HWM for a table? Thanks, Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Unkept package
Hi, DBAs, I got this problem.Some of the kept packages including sys.standard in the shared pool are unkept. This makes the shared pool fragmented. I have no idea what the cause is. Do you happend to know the reason for this? If I re-kept these packages in off-working time withoutbounce the DB, should the shared pool fragmentation be resolved? Your advice would be highly appreciated. Thanks Chuan
Re: Which SQL is executing
Hi, Sam, Thanks for your information. To my understanding,the script you gave is for all the queries. There is still no way to figure out which query is running. Chuan, - Original Message - From: Sam Roberts To: Multiple recipients of list ORACLE-L Sent: Monday, June 04, 2001 5:00 PM Subject: Re: Which SQL is executing SELECT T.SQL_TEXT FROM V$SQLTEXT T,V$SESSION S WHERE S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE; Sam - Original Message - From: Chuan Zhang To: Multiple recipients of list ORACLE-L Sent: Monday, June 04, 2001 9:30 AM Subject: Which SQL is executing Hi All, From v$open_cursor, I know every SQL opened and parsed in one session. Is there any way to know which SQL is running. Or put another way, canall the SQLs in one session be sorted in timing order dynamically? Any clue would be much appreciated. Chuan
Which SQL is executing
Hi All, From v$open_cursor, I know every SQL opened and parsed in one session. Is there any way to know which SQL is running. Or put another way, canall the SQLs in one session be sorted in timing order dynamically? Any clue would be much appreciated. Chuan
Confused with v$session and v$process
Hi, DBAs, I think that I might ask a simple question but I have been confused sometimes for a long time. Could someone help me to clarify these? First, is " v$session.paddr=v$process.addr" the only way to join these two tables" Second,I am confused about the Username, Osuser in v$session with the Username in v$process. "select a.program, b.program,a.username,b.username,b.osuser from v$process a, v$session b where a.addr=b.paddr and sid in (18,33,68,115,144,150)" One of the results is as follows: a.program :oracle@vantive (TNS V1-V3) b.username: iwserver@vantive (TNS V1-V3) b.username: oracle a.username:SWBAPPS a.username: vantive Third: I am confused with Spid,pid in v$process and sid in v$session. Is pid (oracle process id)oracle server process id? what is it used for? Thanks very much for your help. Chuan
What does data block in report.txt stand for?
Hi, All, Could someone help me to figure outthe meaning of "data block" contention in "Buffer busy wait statistics" section in utlbstat/utlestat report.txt or "data block" class in v$waitstat? Thanks very much in advance. Chuan
Re: how to compile the object type in oracle 8i
Hi, Mala, I had exactly this problem before in Oracle 8.0.6. I corrected it simply issue "alter type PQ$_DEQUEUE_HISTORY compile". I don't know what caused this problem happended. Pls let me know your implementation result. Chuan, - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 08, 2001 2:55 AM Hi gurus I found that one of objects has status INVALID. The following are the output. OWNER OBJECT_NAME OBJECT_TYP STATUS -- -- --- SYSPQ$_DEQUEUE_HISTORY_T TYPE INVALID How to correct this object. Please advice me. Thanks. -Mala _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mala singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Build a duplicate DB using hot backups
Thanks very much for your help. Yes, I finally solve this by creating the new control file with "resetlogs" and using recover using backup controlfile until cancel. Chuan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 02, 2001 3:25 PM are you using recover using backup controlfile until cancel? make sure that the alter database open statement has the clause "resetlogs" Check the trace file that's generated when to backup the controlfile to trace. You need to edit that script to suit your needs. HTH Gerardo -Original Message- Sent: Thursday, March 01, 2001 5:01 PM To: Multiple recipients of list ORACLE-L Hi, DBAs, I want to build a duplicate DB of the production DB using the hot backups without RMAN. Redo online log files are not included in the hot backups according to Oracle recommendation. The control file is modified to suit for the new host configuration. When I log into svrmgrl and run this new creating control file script, it complains that the redo log files are not found. Should I backup the online redo logfiles? How could I deal with the online redo log files for my purpose? Many thanks in advance. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Molina, Gerardo INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Build a duplicate DB using hot backups
Hi, DBAs, I want to build a duplicate DB of the production DB using the hot backups without RMAN. Redo online log files are not included in the hot backups according to Oracle recommendation. The control file is modified tosuit for the new host configuration. When I log into svrmgrl and run this new creating control file script, it complains that the redo log files are not found. Should I backup the online redo logfiles? How could I deal with the online redo log files for my purpose? Many thanks in advance. Chuan
Revoke system privilege from user
Hi, All, I have granted DBA role to an user. But I don't want him holding the "Drop Any Table" system privilege. I did as follows: 1. Connect as sysdba. 2. revoke drop any table from ABC-user; And I got the error:" ORA-01952: system privileges not granted to 'ABC-user'". Could anyone help me out? Thanks, Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
