-- NOTE: do NOT clear table UIDGEN.  Doing so could produce duplicate
-- IDs which are visible to external program.  Also do not clear
-- FIPERSYS with schema version info.

-- Note we use TRUNCATE instead of DELETE so table space is released.

truncate table ACCESSCONTROLLIST;
truncate table ACSPKIDESCRIPTOR;
truncate table AFFINITYGROUPBEAN;
truncate table APPROXDATA;
truncate table DATA_TYPES;
truncate table DBVERSIONCONTROLLER;
truncate table DEFAULTACL;
truncate table FILESERVERDETAIL;
truncate table FILESTORE;
truncate table GROUPMASTER;
truncate table GROUPUSERMAP;
truncate table HIERARCHYMASTER;
truncate table JOBBEAN;
truncate table JOBLOGDATABEAN;
truncate table JOBMODEL;
truncate table JOBMONITOR;
truncate table JOBOWNER;
truncate table MASTERMODEL;
truncate table METAINFO;
truncate table OBJECTMASTER;
truncate table OBJECTNAMEVALUE;
truncate table OBJECTREFERENCELIST;
truncate table OBJECTVERSIONMASTER;
truncate table PARTNERPROFILEBEAN;
truncate table RESULTREQUESTREGISTRY;
truncate table STATIONBEAN;
truncate table SYNCPOINT;
truncate table TCBASICSEARCHPARMS;
truncate table TCMODEL;
truncate table TCPREFERENCES;
truncate table VERSIONCONTROLIMPLEMENTATION;
truncate table VERSIONCONTROLMASTER;
truncate table WORKCONTEXTBEAN;
truncate table WORKFLOWBEAN;
truncate table WORKITEMBEAN;
truncate table IMPORTDETAILS;
truncate table WORKITEMSUMMARY;
truncate table FIPERLOCK;
truncate table JOBVIEWDATA;
truncate table AFFINITIES;
truncate table BulletinBoard;
truncate table PARAMHISTORYBEAN;

-- Remove all job data (vertical schema) tables

begin
  for trec in (select table_name from user_tables where table_name LIKE 'VS%') loop
    execute immediate 'drop table '||trec.table_name||' cascade constraint';
  end loop;
end;
/

-- For Oracle 10 and above, dropped tables take space until the
-- recycle bin is purged.  This command will fail on Oracle 9.

purge recyclebin;

quit;

