Dropping Tables for Deleted Record Definitions
While application designer makes it easy to delete record definitions, it doesn't drop the table or view in the database after the record definition has been deleted. So you can be left with orphaned tables or views that have no application designer record definition.
select
RDEL.RECNAME,
RDEL.VERSION,
nvl(AT.TABLE_NAME, AV.VIEW_NAME) as TABLE_VIEW_NAME
from
PSRECDEL RDEL left outer join ALL_TABLES AT
on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
left outer join ALL_VIEWS AV
on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')
where
nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;
The PSRECDEL table stores record definitions that have been deleted through application designer.
If you want, you can use the following query to automatically generate drop table and drop view statements for any orphaned tables or views that are found:
select
(case when AT.TABLE_NAME is null
then 'drop view ' || AV.VIEW_NAME || ';'
else 'drop table ' || AT.TABLE_NAME || ';'
end) as DROP_STATEMENT
from
PSRECDEL RDEL left outer join ALL_TABLES AT
on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
left outer join ALL_VIEWS AV
on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')
where
nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;
No Comments