DROP DOMAIN
Purpose
Use this statement to drop a domain, thereby disassociating the domain from all its dependent objects.
Prerequisites
The domain must be in your own schema or you must have the DROP ANY DOMAIN system privilege.
Syntax
drop_domain::=
Semantics
IF EXISTS
Specify IF EXISTS to drop a domain that exists.
You cannot specify IF NOT EXISTS with DROP DOMAIN. This results in the following error: ORA-11544:Incorrect IF EXISTS clause for ALTER/DROP statement.
You can drop a domain by specifying its domain name and disassociate it from all its dependent columns. In the following example email is the name of a domain name:
DROP DOMAIN email;
If a domain is associated with columns, DROP DOMAIN domain_name fails with ORA-11502:The domain <domain_name> to be dropped has dependent objects.This includes any tables in the recyclebin.
You can additionally drop a domain by specifying two optional keywords: either FORCE or FORCE PRESERVE which have different meanings:
FORCE
DROP DOMAIN domain_name FORCE disassociates the domain from all its dependent columns. This includes dropping all constraints on columns that were inherited from the domain. Defaults inherited from the domain are also dropped unless these defaults were set specifically on columns.
If you must drop a domain and have dependent tables in the recyclebin, you must use the FORCE option.
FORCE PRESERVE
Use DROP DOMAIN domain_name FORCE PRESERVE if you want to preserve domain defaults and domain constraints on columns inherited from the domain. You must first specify FORCE, in order to specify the PRESERVE option. Use this option if you want to temporarily drop the domain and recreate it later with new data and the former dependent columns. In this case you want to ensure that the table data continues to be consistent with the domain definition with all the constraints and defaults on columns inherited from the domain preserved. If you drop a domain with FORCE PRESERVE and later recreate the domain and reassociate the column with it, you can end up with a second constraint. In this case, use ALTER TABLE DROP CONSTRAINT to drop the second constraint.
If there are no tables or materialized views with columns of the given domain, then DROP DOMAIN will invalidate any SQL dependent statements and remove the domain object from the catalog. In this case you can specify FORCE and FORCE PRESERVE without affecting the domain or the domain's dependent objects.
If there are tables or materialized views with columns of the given domain, DROP DOMAIN without the FORCE option will fail without affecting the domain or domain's dependent objects.
If there are tables or materialized views with columns of the given domain, DROP DOMAIN FORCE will do the following:
-
Remove the default expression from any dependent column, if the column has a default expression that was only set as a domain default. If the default expression was added on the column and set as domain default, then default on the column is preserved.
-
Remove the domain annotations from all dependent columns
-
Preserve collation on any domain dependent columns
-
Invalidate all SQL dependent statements in the cursor cache.
-
Materialized views (MVs) that reference domain functions like
DOMAIN_DISPLAY,DOMAIN_ORDER,DOMAIN_NAMEwill be invalidated so they can be fully refreshed. MVs that reference columns of a given domain will not be invalidated . -
Remove the domain successfully.
If there are flexible domains referencing the domain, then DROP DOMAIN without the FORCE option will raise an error, while DROP DOMAIN FORCE will drop all flexible dependent domains in FORCE mode also.
Examples
The following example drops the domain day_of_week. If there are any columns associated with the domain the statement will raise ORA-11502 and the domain will still be present:
DROP DOMAIN day_of_week;
The following statement drops the domain day_of_week. If there are any columns associated with it, the columns will inherit any defaults and constraints from the domain:
DROP DOMAIN day_of_week FORCE PRESERVE;