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

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,

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

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

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,

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

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

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,

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

How to run sql*plus and its command in Windows as batch job?

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

system/internal tables for query tree

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

system/internal tables for QEP (Query Evaluation Plan).

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

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

The life time of the data in v$sqlarea

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

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

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

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,

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

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

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

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

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

RE: Find the dependent objects? -- Attachment History Removed

Chuan Zhang Chuan.Zhang@transTo: Multiple

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

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

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 :

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

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

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

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

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

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

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

data dictionary View about jobs

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

RE: stopiing write to listener.log

-- 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

What tools for Oracle Data Warehouse ETL

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

RE: What tools for Oracle Data Warehouse ETL

, 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

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

Speed up Truncate tables

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

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

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

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

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

Re: Which SQL is executing

, 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

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

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

ubscribing). -- 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 / Mai

Re: Build a duplicate DB using hot backups

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

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

Revoke system privilege from user

quot;. 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 ac