I have been struggling with how to locate objects that come into the model as 'tables' from Reverse Engineering that you will never find in the RDBMS table catalog. I suggested a filter be provided in the RE template on the Idea Wall - but didn't get much support for it, so here is a way to at least find them and deal with them as you deem appropriate.
I have found 2 different sources for them - synonyms used in Views and temp tables used in Functions. If you RE with View and Function turned on - you may see these objects in your model. There may be other ways to have them created in the RE model that I haven't tripped over yet.
The first type is pretty easy. They come in with a Table name and no other information, no columns, just empty boxes - like ghosts. Use Bulk editor, select Table, select characteristic 'Database Column Order', select all tables, then sort on the column. All the 'virtual tables' that have no columns will sort to the top or bottom making it easy to find them and either keep them out of subject areas of 'real' tables - or delete them like I am doing, as I can't find a real need for them being there.
The second type uses the same technique. But these 'virtual tables' may have columns so you can't find them the same way. They seem to consistently come into the model with the 'Generate' flag off. Use the Bulk editor, select Table, select characteristic 'Do Not Generate', select all tables, then sort on the column. All the tables without the generate flag will sort together. You would think that the View synonym based 'virtual tables' would all be 'Do Not Generate' so one characteristic would work for both - but I haven't found that to be the case. These objects are a bit more difficult to delete. ERwin will not let you delete them until the Function they came in with is also deleted. And I haven't found an easy way to tie the 'virtual table' to the functions that depend on them yet - but once you do you can delete both objects.
My goal is to have a library of Production database models that closely represent the same information found in DBA catalog viewing tools that I can publish out to the portal. These 'virtual table' objects cause confusion and distrust of my models for the team I am working with.
Please add your thoughts. :-)
Please sign in to leave a comment.