Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Exclamation Unanswered: Reorganizing our tablespace errors.

    Hello !

    We ran a tablespace reorganization recently.

    After doing this job, We noticed that a whole bunch of synonym's & triggers were now missing from our database.

    We can not seem to be able to figure out why this happened, The script that was run is shown below. And for every one trigger or synonym that was deleted there is a referance of the same trigger / synonym being recreated.

    However none of the triggers or synonym's were recreated and our database failed.

    The only other thing to note, is that one of our remote dba's ran the daily backup job at the same time as the reorg job!

    I do not know if this could have caused the errors, But it is our only working theory.

    Start of main script
    Executing script in direction: Proceed and Clean Up
    Executing as user SYS

    -- Script Generation for PLAN$_89
    -- Plan was last modified: 07-Mar-2003
    -- Target destination db : raxprod
    -- Generation started at: 07-Mar-2003
    -- Generation finished at: 07-Mar-2003
    DROP SYNONYM "PUBLIC"."HIST_DELIVERYPUB"
    ALTER TABLE "RAX2000"."HIST_DELIVERYPUB" MOVE TABLESPACE "REORG_TBSP"
    DROP SYNONYM "PUBLIC"."HIST_DELIVERYROUTE"
    ALTER TABLE "RAX2000"."HIST_DELIVERYROUTE" MOVE TABLESPACE "REORG_TBSP"
    DROP TRIGGER "RAX2000"."HIST_DUTYASSIGNMENT_PREINSERT"
    DROP SYNONYM "PUBLIC"."HIST_DUTYASSIGNMENT"
    ALTER TABLE "RAX2000"."HIST_DUTYASSIGNMENT" MOVE TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_HIST_MARKET" REBUILD TABLESPACE "REORG_TBSP"
    DROP SYNONYM "PUBLIC"."HIST_MARKET"
    ALTER TABLE "RAX2000"."HIST_MARKET" MOVE TABLESPACE "REORG_TBSP"
    DROP TRIGGER "RAX2000"."HIST_PUBISSUE_PREINSERT"
    DROP SYNONYM "PUBLIC"."HIST_PUBISSUE"
    ALTER TABLE "RAX2000"."HIST_PUBISSUE" MOVE TABLESPACE "REORG_TBSP"
    DROP TRIGGER "RAX2000"."HIST_PUBLICATION_PREINSERT"
    DROP SYNONYM "PUBLIC"."HIST_PUBLICATION"
    ALTER TABLE "RAX2000"."HIST_PUBLICATION" MOVE TABLESPACE "REORG_TBSP"
    DROP TRIGGER "RAX2000"."HIST_PUBSERVICE_PREINSERT"
    DROP SYNONYM "PUBLIC"."HIST_PUBLICATIONSERVICE"
    ALTER TABLE "RAX2000"."HIST_PUBLICATIONSERVICE" MOVE TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_HIST_REGION" REBUILD TABLESPACE "REORG_TBSP"
    DROP SYNONYM "PUBLIC"."HIST_REGION"
    ALTER TABLE "RAX2000"."HIST_REGION" MOVE TABLESPACE "REORG_TBSP"
    DROP SYNONYM "PUBLIC"."HIST_VENDER"
    ALTER TABLE "RAX2000"."HIST_VENDER" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLESPACE "RAX_HIST" COALESCE
    ALTER TABLE "RAX2000"."HIST_VENDER" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_VENDER" FOR "RAX2000"."HIST_VENDER"
    ALTER TABLE "RAX2000"."HIST_REGION" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_REGION" FOR "RAX2000"."HIST_REGION"
    ALTER TABLE "RAX2000"."HIST_PUBLICATIONSERVICE" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_PUBLICATIONSERVICE" FOR "RAX2000"."HIST_PUBLICATIONSERVICE"
    CREATE TRIGGER "RAX2000"."HIST_PUBSERVICE_PREINSERT" BEFORE
    UPDATE ON "RAX2000"."HIST_PUBLICATIONSERVICE" FOR EACH ROW DECLARE
    BEGIN
    :NEW.WHOLASTUPDATED := SUBSTR(USER,1,8); :NEW.DATELASTUPDATED := SYSDATE;
    END;
    ALTER TABLE "RAX2000"."HIST_PUBLICATION" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_PUBLICATION" FOR "RAX2000"."HIST_PUBLICATION"
    CREATE TRIGGER "RAX2000"."HIST_PUBLICATION_PREINSERT" BEFORE
    UPDATE ON "RAX2000"."HIST_PUBLICATION" FOR EACH ROW DECLARE
    BEGIN
    :NEW.WHOLASTUPDATED := SUBSTR(USER,1,8); :NEW.DATELASTUPDATED := SYSDATE;
    END;
    ALTER TABLE "RAX2000"."HIST_PUBISSUE" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_PUBISSUE" FOR "RAX2000"."HIST_PUBISSUE"
    CREATE TRIGGER "RAX2000"."HIST_PUBISSUE_PREINSERT" BEFORE
    UPDATE ON "RAX2000"."HIST_PUBISSUE" FOR EACH ROW DECLARE
    BEGIN
    :NEW.WHOLASTUPDATED := SUBSTR(USER,1,8); :NEW.DATELASTUPDATED := SYSDATE;


    END;
    ALTER TABLE "RAX2000"."HIST_MARKET" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_MARKET" FOR "RAX2000"."HIST_MARKET"
    ALTER TABLE "RAX2000"."HIST_DUTYASSIGNMENT" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_DUTYASSIGNMENT" FOR "RAX2000"."HIST_DUTYASSIGNMENT"
    CREATE TRIGGER "RAX2000"."HIST_DUTYASSIGNMENT_PREINSERT" BEFORE
    UPDATE ON "RAX2000"."HIST_DUTYASSIGNMENT" FOR EACH ROW DECLARE
    BEGIN
    :NEW.WHOLASTUPDATED := SUBSTR(USER,1,8); :NEW.DATELASTUPDATED := SYSDATE;
    END;
    ALTER TABLE "RAX2000"."HIST_DELIVERYROUTE" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_DELIVERYROUTE" FOR "RAX2000"."HIST_DELIVERYROUTE"
    ALTER TABLE "RAX2000"."HIST_DELIVERYPUB" MOVE TABLESPACE "RAX_HIST"
    CREATE PUBLIC SYNONYM "HIST_DELIVERYPUB" FOR "RAX2000"."HIST_DELIVERYPUB"
    ALTER INDEX "RAX2000"."AGENTSTATUS_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CITY_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CLIENTTYPE_PK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CONSTRUCTIONRACKS_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CONSTRUCTIONTYPES_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CONTRACTORTYPE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CONTRACTORVEND_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."CURRENTISSUE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DELIVERYPUBS_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DELIVERYPUB_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DELIVERYROUTE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DELIVHISTISSUE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DELIVHISTPOCKET_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DUTYASSIGNMARKET_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DUTYASSIGNMENT_TEMPL_IDX_001" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DUTYASSIGN_DATE_IDX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DUTYTYPE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."DUTY_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."EMPJOBTITLE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."EMPLOYEEDUTY_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."EMPMKT_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."ERRORMARKET_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."ERRORUSER_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCATIONMARKTET_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCATIONSEQ_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCATION_IDX_002" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCRACK_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCSTATUS_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."LOCTYPE_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."MARKETISSUE_IDX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."MARKET_MARKETCODE_IDX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."MARKET_MARKETNAME_IDX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."MKTEMP_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."MKTREGION_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."NUMBERINGSTORECHAIN_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."OVERPRINTCATEGORY_FK" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."OVERPRINTDAEEXECUTED_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."OVERPRINTMARKET_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."OVERPRINT_DA_MK_LOC" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."OVERPRINT_PUBID_IDX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."O_ISSUEID_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."O_PREVISSUEID_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PIISSUEEND_IX" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_SCHEDSERVAPPOINTMENT" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_SCHEDSERVDETAIL" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_SCHEDSERVITEM" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_SCHEDSERVMARKET" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_SERVER" REBUILD TABLESPACE "REORG_TBSP"
    ALTER INDEX "RAX2000"."PK_STANDARDISSUE" REBUILD
    DROP TABLE "RAX2000"."PLAN_TABLE$$OCMrecovery"
    ALTER TABLE "RAX2000"."POCKET" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLE "RAX2000"."POCKETASSIGNMENT" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLE "RAX2000"."PUBCATEGORY" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLE "RAX2000"."PUBISSUE" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLE "RAX2000"."PUBLICATION" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLE "RAX2000"."PUBLICATIONREPORTING" MOVE TABLESPACE "REORG_TBSP"
    ALTER TABLESPACE "RAX_INDX" COALESCE
    ALTER TABLE "RAX2000"."PUBLICATIONREPORTING" MOVE
    " REBUILD TABLESPACE "RAX_INDX"
    ALTER TABLE "RAX2000"."RATECARD" MOVE TABLESPACE "RAX_DATA"
    CREATE PUBLIC SYNONYM "RATECARD" FOR "RAX2000"."RATECARD"
    ALTER INDEX "RAX2000"."PK_RATECARDID" REBUILD TABLESPACE "RAX_INDX"
    ALTER TABLE "RAX2000"."RACKTYPE" MOVE TABLESPACE "RAX_DATA"
    CREATE PUBLIC SYNONYM "RACKTYPE" FOR "RAX2000"."RACKTYPE"
    ALTER INDEX "RAX2000"."RACKCAT_FK" REBUILD TABLESPACE "RAX_INDX"

    CREATE TRIGGER "RAX2000"."RACK_BEF_INS" BEFORE

    Starting cleanup of recovery tables...
    DROP TABLE "RAX2000"."PLAN_TABLE$$OCMrecovery$$OCMre"
    Completed cleanup of recovery tables.
    Script execution complete.

  2. #2
    Join Date
    Feb 2003
    Posts
    45

    Don't Understand

    Why if you were doing a Tablespace REORG did you want to drop and recreate Synonyms & Triggers?

    The Synonyms would have been OK as long as the objects in the Tablespace were still there and under the same Schema Owner.

    The triggers would just have needed to be re-compiled.

    Sounds like your 'remote dba' ran the script too early and screwed things up before your REORG.

    I'd suggest going to the ALL important backup that one takes prior to doing anything like this OR failing that you'll need to recreate these missing Synonyms and Triggers.

    Good Luck anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •