Re: tirggers

If you fts the tables, won't Oracle just place them into the Keep pool because it will think that it needs them? ----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Sunday, April 08, 2001 8:55 AM

> Alex,
>
> Further testing has proved that only the blocks needed to satisfy the
query
> are loaded, and not the entire table.
>
> I can post the details if you wish, but there's your answer:)
>
> Mark
>
> -----Original Message-----
> Alex
> Sent: Friday, April 06, 2001 06:27
> To: Multiple recipients of list ORACLE-L
>
>
> Looks like oracle reads into keep buffer pool only blocks that it needed
but
> then keep it there. Would be intersting to know if let say table has more
> then 1 block and access is using indexes - so oracle needs to read only 1
> block - will be all table loaded into keep buffer pool or only block
needed
> to satisfy query.
>
> Alex Hillman
>
> -----Original Message-----
> From: Mark Leith [SMTP:mark_at_cool-tools.co.uk]
> Sent: Friday, April 06, 2001 11:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: tirggers
>
> I thought that as well Tim, but wasn't sure whether Oracle loads the
> table
> at startup even if this is specified in the storage clause. The
> following
> test seems to show that it doesn't though:
>
> SQL> create table DUMMY_TABLE (id number(3), dummy varchar2(5))
> 2 storage(BUFFER_POOL KEEP);
>
> Table created.
>
> SQL> select DATA_OBJECT_ID, OBJECT_TYPE
> 2 from USER_OBJECTS
> 3 where OBJECT_NAME = 'DUMMY_TABLE';
>
> DATA_OBJECT_ID OBJECT_TYPE
> -------------- ------------------
> 26408 TABLE
>
> SQL> select count(*) buffers
> 2 from V$BH
> 3 where OBJD = 26408;
>
> BUFFERS
> ----------
> 1
>
> SQL> connect internal/password
> Connected.
> SQL> shutdown immediate;
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SQL> startup
> ORACLE instance started.
>
> Total System Global Area 73701404 bytes
> Fixed Size 75804 bytes
> Variable Size 56770560 bytes
> Database Buffers 16777216 bytes
> Redo Buffers 77824 bytes
> Database mounted.
> Database opened.
> SQL> connect mark/password
>
> SQL> select count(*) buffers
> 2 from V$BH
> 3 where OBJD = 26408;
>
> BUFFERS
> ----------
> 0
>
> SQL> select * from DUMMY_TABLE;
>
> no rows selected
>
> SQL> select count(*) buffers
> 2 from V$BH
> 3 where OBJD = 26408;
>
> BUFFERS
> ----------
> 1
>
> Not sure on the trigger though, PL/SQL is not one of my strong
> points :)
>
> Mark
>
> -----Original Message-----
> Sawmiller
> Sent: Friday, April 06, 2001 02:07
> To: Multiple recipients of list ORACLE-L
>
>
> Why not just specify BUFFER POOL KEEP in an alter table statement?
>
> >>> Alex.Hillman_at_usmint.treas.gov 04/05/01 05:56PM >>>
> What is the problem to write something like
> Select * from <table_name> for all tables that you need or if there
> are too
> many such tables - create a new table with names of the tables and
> use
> dynamic SQL .
>
> Alex Hillman
>
> -----Original Message-----
> From: Kevin Kostyszyn [SMTP:kevin_at_dulcian.com]
> Sent: Thursday, April 05, 2001 5:28 PM
> To: Multiple recipients of list ORACLE-L
> Subject: tirggers
>
> Hi Intelligent DBA's
> I was wondering if anyone knows how to create a
> trigger that
> would fire off
> at database startup time and run a script to do full table
> scans on
> several
> tables to get them into the buffer cache keep pool? Right
> now I do
> it
> manually and would like to automate the task.
>
> Sincerely,
> Kevin Kostyszyn
> DBA
> Dulcian, Inc
> www.dulcian.com
> kevin_at_dulcian.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kevin Kostyszyn
> INET: kevin_at_dulcian.com
>
> 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: 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.com
> --
> Author: Hillman, Alex
> INET: Alex.Hillman_at_usmint.treas.gov
>
> 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: 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.com
> --
> Author: Tim Sawmiller
> INET: sawmillert_at_state.mi.us
>
> 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: 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.com
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> 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: 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.com
> --
> Author: Hillman, Alex
> INET: Alex.Hillman_at_usmint.treas.gov
>
> 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: 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.com
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> 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: 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.com
-- 
Author: Kevin Kostyszyn
  INET: kevin_at_dulcian.com

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: 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).