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.

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.