RE: tirggers

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Fri, 06 Apr 2001 07:54:12 -0700
Message-ID: <F001.002E40A1.20010406075358@fatcity.com>

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

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

SQL> select * from DUMMY_TABLE;

no rows selected

SQL> select count(*) buffers
2 from V$BH
3 where OBJD = 26408;

BUFFERS

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

Received on Fri Apr 06 2001 - 09:54:12 CDT