Re: Fwd: Looking for help.

There is a difference between a statement that is invalid (i.e. won't execute) and one whose execution plan has been invalidated in the shared pool. When the statement is 'invalidated' all it means is that if a process wants to reexecute the statement, it must be reparsed. At this time, the statement may become invalid (column referenced has been dropped). Usually it means that a 'hard parse' occurs and the dictionary info is reloaded and the execution plan is regenerated (may be different than the last).

Daniel

Ron Rogers wrote:

> Daniel,
> I understand what is you are saying and what you tested but I don't
> the why or what it means. Does it mean that the sql command is not
> going to work? Does it means that you have to issue it again to get it
> to work?
>
> Ref:
> Doc ID: Note:123214.1
> invalid
> Type: PROBLEM
> Status: PUBLISHED
> ....
> Seems that truncate command invalidates object definition and existence
> in library cache.
>
> Invalidation can also be seen on temporary tables!
>
> ..
>
> Jonathan,
> The memory problem is described in Doc id:1157495.8 Support
> Description of Bug 1157495
>
> Ron
>
> >>> Daniel.Fink_at_Sun.COM 11/12/2003 1:14:25 PM >>>
> Ron,
>
> I don't know about the TRUNCATE option w/ sql*loader, but the regular
> DDL
> TRUNCATE invalidates sql that references the table.
>
> Example:
>
> 1 select sql_text, invalidations
> 2 from v$sql
> 3* where sql_text = 'select * from emp'
> SQL> /
>
> SQL_TEXT INVALIDATIONS
> -------------------------------------------------- -------------
> select * from emp 0
>
> SQL> truncate table emp;
>
> Table truncated.
>
> SQL> select sql_text, invalidations
> 2 from v$sql
> 3 where sql_text = 'select * from emp'
> 4 /
>
> SQL_TEXT INVALIDATIONS
> -------------------------------------------------- -------------
> select * from emp 1
>
> Ron Rogers wrote:
>
> > Daniel,
> > How does using the TRUNCATE command is a sqlldr invalidate
> anything?
> > The sqlldr truncate command reuses the storage that the table
> originally
> > used and does not change the HW mark. If there are indexes on the
> tables
> > then they are placed in the "DIRECT PATH" state during the load and
> > updated with the now block info.
> > Please explain whet you mean by "invalidate".
> > Ron
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Fink
> INET: Daniel.Fink_at_Sun.COM
>
> 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: ListGuru_at_fatcity.com (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: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> 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: ListGuru_at_fatcity.com (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: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

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: ListGuru_at_fatcity.com (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).