Re: ANALYZED Questions?

From: Dan Whatley <dan.whatley_at_motorola.com>
Date: Mon, 17 Dec 2001 09:53:59 -0800
Message-ID: <F001.003DE176.20011217093031@fatcity.com>

Hi Seema,

Those tables are used by the replication services to populate changes to other snapshot/master databases. When you create a primary key snapshot log on a master table, execute an "alter snapshot log add primary key" in addition to MLOG$_<tablename>, an temporary table by the name "RUPD$_<tablename> will also be created.

To remove them, you would run this PL/SQL script, and as an rule of thumb, before you try this, be sure to try this out on an test database "BEFORE" running on your production database to verify the end results.

dan whatley
Sr. Oracle DBA
Global Information Systems
Motorola, Inc.
7700 W. Parmer Ln.
Austin, Tx 78729

dan.whatley_at_motorola.com

Remove Temporary Updatable Snapshot Logs

Determine if you have temporary updatable snapshot logs by issuing the following SQL statement:

SELECT owner, table_name FROM dba_tables

    WHERE temporary='Y' AND
    table_name LIKE 'RUPD$%';

If any rows are returned, temporary updatable snapshot logs exist in your database. Run the following PL/SQL block to remove them:

DECLARE

  sql_cur  BINARY_INTEGER; 
  dummy    BINARY_INTEGER; 
  new_flag BINARY_INTEGER; 
 

  CURSOR mv_logs IS
    SELECT '"'||mowner||'"."'||temp_log||'"' temp_log,

           flag, mowner, master 
      FROM mlog$ m 
     WHERE temp_log IS NOT NULL 

    FOR UPDATE;
BEGIN
  sql_cur := dbms_sql.open_cursor;
  FOR alog IN mv_logs LOOP
    new_flag := alog.flag;
    IF dbms_ijob.bit(new_flag, 64) THEN ---KKZLOGTUPS       new_flag := new_flag - 64;
    END IF;       BEGIN
      dbms_sql.parse(sql_cur, 'DROP TABLE ' || alog.temp_log, dbms_sql.v7);

      dummy := dbms_sql.execute(sql_cur);  

      UPDATE mlog$ m 
         SET flag = new_flag, temp_log = NULL 
       WHERE m.mowner = alog.mowner AND m.master = alog.master; 
    EXCEPTION WHEN others THEN 
      NULL; --- Ignore the error 

    END;
  END LOOP;
  dbms_sql.close_cursor(sql_cur);
  COMMIT;
EXCEPTION WHEN others THEN
  IF dbms_sql.is_open(sql_cur) THEN
    dbms_sql.close_cursor(sql_cur);
  END IF;
  RAISE;
END;
/

Seema Singh wrote:
>
> Hi
> I execute DBMS_UTLITY ANALYSE SCHEMA for one schema .I Found some of tables
> like
> RUPD$_GT_PRODUCTS
> RUPD$_GT_TEL_NUMBER_AFF_MA
> RUPD$_GT_USER_EMAIL_ADDRES
> RUPD$_GT_USER_PROVIDER_THR
> are no analysed.
> Any idea What kind of these tables are?
> Why they are not analysed?
> Thanks
> -Seema
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Seema Singh
> INET: oracledbam_at_hotmail.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: Dan Whatley
  INET: dan.whatley_at_motorola.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).

Received on Mon Dec 17 2001 - 11:53:59 CST