The DROP CATALOG statement drops the specified catalog from the database.
If you do not specify either RESTRICT or CASCADE, all the database objects in the catalog must have been dropped prior to using this statement.
The RESTRICT keyword means that the catalog is dropped only if all database objects in the catalog have been dropped before.
The CASCADE keyword means that if the catalog contains database objects (such as tables), those will automatically be dropped. If you use the CASCADE keyword and the objects in other catalogs reference an object in the catalog being dropped, the references will automatically be resolved by dropping those referencing objects or updating them to eliminate the reference.
Only the creator of the database or users having SYS_ADMIN_ROLE have privileges to create or drop a catalog. Even the creator of a catalog cannot drop that catalog without SYS_ADMIN_ROLE privileges.