The reference table for the mentioned objects is called VANTAGE_DYN_TAB, which holds information on dynamic entities like CB*, F*, BS*, etc, created by Marketing Director. Note, however, it only contains a reference to these tables, but not the table data itself.
For housekeeping of the CMD schema, kindly go through the attached document on Purge Scripts. Do let us know if you have any doubts/queries regarding the same, we will be happy to assist.
Temporary tables beginning with letter like like 'E_%', 'F%', 'T<digit>%' and 'ZZZ%' are created during Campaign run and are dropped at the end for a successful execution. However, they are left behind if the Campaign aborts. If you have any of the these tables present in the database, they can be safely dropped.
'CB%' temporary tables are generated during output processing if the delivery channel is 'Combined' type. It has a reference entry in vantage_dyn_tab table. It is a combined segment table created using the CS* tables (of the combined segments) of the campaign. We have tried dropping CB% tables of aborted/failed campaigns and didn’t face any issue when rescheduled. However, as a precaution, it is suggested to drop only the ‘CB%’ tables of campaigns that are completed or cancelled.
The OT_TEMP* tables mentioned doesn't seem to be standard tables generated by CMD so these are either output tables that have been deliberately named like this or are may be tables created outside of CMD. If they are output tables you will need to check if they are no longer needed.
The Dynamic tables are only created when the particular object is executed, for example, segments, derived values, campagins, etc and are recreated during any future execution. Please refer the attached document('Dynamic Table Names' topic of Chapter 8) about standard dynamic tables list, which can be purged using the purge scripts. Other temporary tables need to be manually dropped.
Please note as a safety measure, it is always suggested to back up the respective tables or entire schema before making any changes to the CMD Schema.
We apologize for the delayed response, and regret the inconvenience caused.
OBJ_TYPE_ID field or Object Type Identifier indicates the type of object to which the dynamic customer table belongs. These are predefined values and the information for this, is present in OBJ_TYPE table. Any dynamic table related to Campaign Segment (CS), for example, will have OBJ_TYPE_ID=27, and so on.
Could you please run the below SQL and send us the output?
select * from obj_type where regexp_like(obj_abbrev, '^F');