The DROP statement deletes an object. Any objects that are directly or indirectly dependent on that object are either deleted or made inoperative. Whenever an object is deleted, its description is deleted from the catalog, and any packages that reference the object are invalidated.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
When dropping a table or view hierarchy, the privileges held by the authorization ID of the statement must include one of the above privileges for each of the tables or views in the hierarchy.
When dropping an audit policy, the privileges held by the authorization ID of the statement must include SECADM authority.
When dropping a buffer pool, database partition group or table space, the privileges held by the authorization ID of the statement must include SYSADM or SYSCTRL authority.
When dropping a data type mapping, function mapping, server definition, or wrapper, the privileges held by the authorization ID of the statement must include DBADM authority.
When dropping an event monitor the privilege held by the authorization ID of the statement must include SQLADM or DBADM authority.
When dropping a role the privileges held by the authorization ID of the statement must include SECADM authority.
When dropping a schema, the privileges held by the authorization ID of the statement must include DBADM authority, or be the schema owner, as recorded in the OWNER column of the SYSCAT.SCHEMATA catalog view.
When dropping a security label, a security label component, or a security policy, the privileges held by the authorization ID of the statement must include SECADM authority.
When dropping a service class, work action set, work class set, workload, threshold, or histogram template, the privileges held by the authorization ID of the statement must include WLMADM or DBADM authority.
When dropping a transform, the privileges held by the authorization ID of the statement must include DBADM authority, or must be the owner of type-name.
When dropping a trusted context, the privileges held by the authorization ID of the statement must include SECADM authority.
When dropping a user mapping, the privileges held by the authorization ID of the statement must include DBADM authority, if this authorization ID is different from the federated database authorization name within the mapping. Otherwise, if the authorization ID and the authorization name match, no authorities or privileges are required.
When dropping a public user mapping, the privileges held by the authorization ID of the statement must include DBADM authority.
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ | .-RESTRICT-. | +-| function-designator |--+----------+-------------------------+ +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ | .-RESTRICT-. | +-| method-designator |--+----------+---------------------------+ +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | | .-RESTRICT-. | +-| procedure-designator |--+----------+------------------------+ +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ | .-RESTRICT-. | +-SEQUENCE--sequence-name--+----------+-------------------------+ +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | +-USER---------------+ | | '-PUBLIC-------------' | | .-RESTRICT-. | +-VARIABLE--variable-name--+----------+-------------------------+ +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ | .-RESTRICT-. | +-WORKLOAD--workload-name--+----------+-------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------' alias-designator .-FOR TABLE----. |--+--------+--ALIAS--alias-name--+--------------+--------------| '-PUBLIC-' +-FOR MODULE---+ '-FOR SEQUENCE-' function-designator |--+-FUNCTION--function-name--+-------------------------+-+-----| | '-(--+---------------+--)-' | | | .-,---------. | | | | V | | | | '---data-type-+-' | '-SPECIFIC FUNCTION--specific-name---------------------' method-designator |--+-METHOD--method-name--+-------------------------+--FOR--type-name-+--| | '-(--+---------------+--)-' | | | .-,---------. | | | | V | | | | '---data-type-+-' | '-SPECIFIC METHOD--specific-name-----------------------------------' procedure-designator |--+-PROCEDURE--procedure-name--+-------------------------+-+---| | '-(--+---------------+--)-' | | | .-,---------. | | | | V | | | | '---data-type-+-' | '-SPECIFIC PROCEDURE--specific-name----------------------' service-class-designator |--SERVICE CLASS--service-class-name--+--------------------------------+--| '-UNDER--service-superclass-name-'
All views and triggers that reference the alias are made inoperative. This includes alias references in both the ON clause of the CREATE TRIGGER statement and within the triggered SQL statements. Any materialized query table or staging table that references the alias is dropped.
If PUBLIC is specified, the alias-name must identify a public alias that exists at the current server (SQLSTATE 42704).
Dropping a database partition group drops all table spaces defined in the database partition group. All existing database objects with dependencies on the tables in the table spaces (such as packages, referential constraints, and so on) are dropped or invalidated (as appropriate), and dependent views and triggers are made inoperative.
System-defined database partition groups cannot be dropped (SQLSTATE 42832).
If a DROP DATABASE PARTITION GROUP statement is issued against a database partition group that is currently undergoing a data redistribution, the drop database partition group operation fails, and an error is returned (SQLSTATE 55038). However, a partially redistributed database partition group can be dropped. A database partition group can become partially redistributed if a REDISTRIBUTE DATABASE PARTITION GROUP command does not execute to completion. This can happen if it is interrupted by either an error or a FORCE APPLICATION ALL command. (For a partially redistributed database partition group, the REDISTRIBUTE_PMAP_ID in the SYSCAT.DBPARTITIONGROUPS catalog is not -1.)
If the identified event monitor is active, an error is returned (SQLSTATE 55034); otherwise, the event monitor is deleted. Note that if an event monitor has been previously activated using the SET EVENT MONITOR STATE statement, and the database has been deactivated and subsequently reactivated, use the SET EVENT MONITOR STATE statement to deactivate the event monitor before issuing the DROP statement.
If there are event files in the target path of a WRITE TO FILE event monitor that is being dropped, the event files are not deleted. However, if a new event monitor that specifies the same target path is created, the event files are deleted.
When dropping WRITE TO TABLE event monitors, table information is removed from the SYSCAT.EVENTTABLES catalog view, but the tables themselves are not dropped.
There are several different ways available to identify the function instance:
If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.
It is not necessary to specify the length, precision or scale for the parameterized data types. Instead, an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match.
FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).
If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement.
A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.
Other objects can be dependent upon a function. All such dependencies must be removed before the function can be dropped, with the exception of packages which are marked inoperative. An attempt to drop a function with such dependencies will result in an error (SQLSTATE 42893). See Rules for a list of these dependencies.
If the function can be dropped, it is dropped.
Any package dependent on the specific function being dropped is marked as inoperative. Such a package is not implicitly rebound. It must either be rebound by use of the BIND or REBIND command, or it must be re-prepared by use of the PREP command.
Default function mappings cannot be dropped, but can be disabled by using the CREATE FUNCTION MAPPING statement. Dropping a user-defined function mapping that was created to override a default function mapping reinstates the default function mapping.
Packages having a dependency on a dropped function mapping are invalidated.
Packages having a dependency on a dropped index or index specification are invalidated.
DROP METHOD deletes the body of a method, but the method specification (signature) remains as a part of the definition of the subject type. After dropping the body of a method, the method specification can be removed from the subject type definition by ALTER TYPE DROP METHOD.
There are several ways available to identify the method body to be dropped:
If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path.
It is not necessary to specify the length, precision or scale for the parameterized data types. Instead, an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match.
FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).
However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE TYPE statement.
A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.
If no method with the specified signature exists for the named data type, an error is returned (SQLSTATE 42883).
Other objects can be dependent upon a method. All such dependencies must be removed before the method can be dropped, with the exception of packages which will be marked inoperative if the drop is successful. An attempt to drop a method with such dependencies will result in an error (SQLSTATE 42893).
If the method can be dropped, it will be dropped.
Any package dependent on the specific method being dropped is marked as inoperative. Such a package is not implicitly re-bound. Either it must be re-bound by use of the BIND or REBIND command, or it must be re-prepared by use of the PREP command.
If the specific method being dropped overrides another method, all packages dependent on the overridden method - and on methods that override this method in supertypes of the specific method being dropped - are invalidated.
All information about the columns and indexes associated with the nickname is deleted from the catalog. Any materialized query tables that are dependent on the nickname are dropped. Any index specifications that are dependent on the nickname are dropped. Any views that are dependent on the nickname are marked inoperative. Any packages that are dependent on the dropped index specifications or inoperative views are invalidated. The data source table that the nickname references is not affected.
If an SQL function or method is dependent on a nickname, that nickname cannot be dropped (SQLSTATE 42893).
There are several different ways available to identify the procedure instance:
If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.
It is not necessary to specify the length, precision or scale for the parameterized data types. Instead, an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match.
FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).
However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement or, for federated procedures, it must exactly match what is stored in the local catalog for the corresponding parameter.
A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.
It is not possible to drop a procedure that is in the SYSIBM, SYSFUN, or the SYSPROC schema (SQLSTATE 42832).
The specified role is deleted from the catalog.
All nicknames for tables and views residing at the data source are dropped. Any index specifications dependent on these nicknames are dropped. Any user-defined function mappings, user-defined type mappings, and user mappings that are dependent on the dropped server definition are also dropped. All packages dependent on the dropped server definition, function mappings, nicknames, and index specifications are invalidated. All federated procedures that are dependent on the server definition are also dropped.
All indexes, primary keys, foreign keys, check constraints, materialized query tables, and staging tables referencing the table are dropped. All views and triggers that reference the table are made inoperative. (This includes both the table referenced in the ON clause of the CREATE TRIGGER statement, and all tables referenced within the triggered SQL statements.) All packages depending on any object dropped or marked inoperative will be invalidated. This includes packages dependent on any supertables above the subtable in the hierarchy. Any reference columns for which the dropped table is defined as the scope of the reference become unscoped.
Packages are not dependent on declared temporary tables, and therefore are not invalidated when such a table is dropped. Packages are, however, dependent on created temporary tables, and are invalidated when such a table is dropped.
In a federated system, a remote table that was created using transparent DDL can be dropped. Dropping a remote table also drops the nickname associated with that table, and invalidates any packages that are dependent on that nickname.
When a subtable is dropped from a table hierarchy, the columns associated with the subtable are no longer accessible although they continue to be considered with respect to limits on the number of columns and size of the row. Dropping a subtable has the effect of deleting all the rows of the subtable from the supertables. This may result in activation of triggers or referential integrity constraints defined on the supertables.
When a created temporary table or declared temporary table is dropped, and its creation preceded the active unit of work or savepoint, then the table will be functionally dropped and the application will not be able to access the table. However, the table will still reserve some space in its table space and will prevent that USER TEMPORARY table space from being dropped or the database partition group of the USER TEMPORARY table space from being redistributed until the unit of work is committed or savepoint is ended. Dropping a created temporary table or declared temporary table causes the data in the table to be destroyed, regardless of whether DROP is committed or rolled back.
A table cannot be dropped if it has the RESTRICT ON DROP attribute.
A newly detached table is initially inaccessible. This prevents the table from being read, modified, or dropped until the SET INTEGRITY statement can be run to incrementally refresh MQTs or to complete any processing for foreign key constraints. After the SET INTEGRITY statement executes against all dependent tables, the table is fully accessible, its detached attribute is reset, and it can be dropped.
All indexes, materialized query tables, staging tables, primary keys, foreign keys, and check constraints referencing the dropped tables are dropped. All views and triggers that reference the dropped tables are made inoperative. All packages depending on any object dropped or marked inoperative will be invalidated. Any reference columns for which one of the dropped tables is defined as the scope of the reference become unscoped.
Unlike dropping a single subtable, dropping the table hierarchy does not result in the activation of delete triggers of any tables in the hierarchy nor does it log the deleted rows.
The table spaces will not be dropped (SQLSTATE 55024) if there is any table that stores at least one of its parts in a table space being dropped, and has one or more of its parts in another table space that is not being dropped (these tables would need to be dropped first), or if any table that resides in the table space has the RESTRICT ON DROP attribute.
Objects whose names are prefixed with 'SYS' are system-defined objects and, with the exception of the SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces, cannot be dropped (SQLSTATE 42832).
A SYSTEM TEMPORARY table space cannot be dropped (SQLSTATE 55026) if it is the only temporary table space that exists in the database. A USER TEMPORARY table space cannot be dropped if there is an instance of a created temporary table or a declared temporary table created in it (SQLSTATE 55039). Even if a created temporary table has been dropped, the USER TEMPORARY table space will still be considered to be in used until all instances of the created temporary table are dropped. Instances of a created temporary table are dropped when the session terminates or when the created temporary table is referenced in the session. Even if a declared temporary table has been dropped, the USER TEMPORARY table space will still be considered to be in use until the unit of work containing the DROP TABLE statement has been committed.
Dropping a table space drops all objects that are defined in the table space. All existing database objects with dependencies on the table space, such as packages, referential constraints, and so on, are dropped or invalidated (as appropriate), and dependent views and triggers are made inoperative.
Containers that were created by a user are not deleted. Any directories in the path of the container name that were created by the database manager during CREATE TABLESPACE execution are deleted. All containers that are below the database directory are deleted. When the DROP TABLESPACE statement is committed, the DMS file containers or SMS containers for the specified table space are deleted, if possible. If the containers cannot be deleted (because they are being kept open by another agent, for example), the files are truncated to zero length. After all connections are terminated, or the DEACTIVATE DATABASE command is issued, these zero-length files are deleted.
Dropping triggers causes certain packages to be marked invalid.
If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.
If there are not transforms defined for type-name, an error is returned (SQLSTATE 42740).
DROP TRANSFORM is the inverse of CREATE TRANSFORM. It causes the transform functions associated with certain groups, for a given data type, to become undefined. The functions formerly associated with these groups still exist and can still be called explicitly, but they no longer have the transform property, and are no longer invoked implicitly for exchanging values with the host language environment.
The transform group is not dropped if there is a user-defined function (or method) written in a language other than SQL that has a dependency on one of the group's transform functions defined for the user-defined type type-name (SQLSTATE 42893). Such a function has a dependency on the transform function associated with the referenced transform group defined for type type-name. Packages that depend on a transform function associated with the named transform group are marked inoperative.
Dropping triggers causes certain packages to be marked invalid.
If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.
If RESTRICT is not specified, the behavior is the same as RESTRICT, except for functions and methods that use the type. The restrict rule is enforced by default for the same dependencies as in version 9.5 if the auto_reval database configuration parameter is set to disabled.
DROP SPECIFIC FUNCTION SF
It is possible that this statement also would cascade to drop dependent functions. If all of these functions are also in the list to be dropped because of a dependency on the user-defined type, the drop of the user-defined type will succeed (otherwise it fails with SQLSTATE 42893).
DROP SPECIFIC METHOD SM
ALTER TYPE T1 DROP SPECIFIC METHOD SM
The existence of objects that are dependent on these methods may
cause the DROP TYPE operation to fail.All packages that are dependent on methods defined in supertypes of the type being dropped, and that are eligible for overriding, are invalidated.
No additional objects are dropped.
No additional objects are dropped.
The specified view is deleted. The definition of any view or trigger that is directly or indirectly dependent on that view is marked inoperative. Any materialized query table or staging table that is dependent on any view that is marked inoperative is dropped. Any packages dependent on a view that is dropped or marked inoperative will be invalidated. This includes packages dependent on any superviews above the subview in the hierarchy. Any reference columns for which the dropped view is defined as the scope of the reference become unscoped.
The definition of any view or trigger that is directly or indirectly dependent on any of the dropped views is marked inoperative. Any packages dependent on any view or trigger that is dropped or marked inoperative will be invalidated. Any reference columns for which a dropped view or view marked inoperative is defined as the scope of the reference become unscoped.
All server definitions, user-defined function mappings, and user-defined data type mappings that are dependent on the wrapper are dropped. All user-defined function mappings, nicknames, user-defined data type mappings, and user mappings that are dependent on the dropped server definitions are also dropped. Any index specifications dependent on the dropped nicknames are dropped, and any views dependent on these nicknames are marked inoperative. All packages dependent on the dropped objects and inoperative views are invalidated. All federated procedures that are dependent on the dropped server definitions are also dropped.
Check constraints that reference the XSR object are dropped. All triggers and views referencing the XSR object are marked inoperative. Packages having a dependency on a dropped XSR object are invalidated.
In a partitioned database environment, you can issue this statement against an XSR object by connecting to any partition.
A package used by a function or a method, or by a procedure that is called directly or indirectly from a function or method, will only be automatically revalidated if the routine is defined as MODIFIES SQL DATA. If the routine is not MODIFIES SQL DATA, an error is returned (SQLSTATE 56098).
Object Type | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Statement | C |
F |
F |
G |
I |
I |
M |
N |
D |
P |
S |
S |
T |
T |
T |
T |
T |
T |
U |
V |
W |
W |
W |
X |
ALTER FUNCTION | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER METHOD | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER NICKNAME, altering the local name or the local type | R33 | R | - | - | - | - | R | - | - | A | - | - | R | - | - | - | - | - | - | R | - | - | - | - |
ALTER NICKNAME, altering a column option or a nickname option | - | - | - | - | - | - | - | - | - | A | - | - | R | - | - | - | - | - | - | - | - | - | - | - |
ALTER NICKNAME, adding, altering, or dropping a constraint | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER PROCEDURE | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER SERVER | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER TABLE ALTER COLUMN | - | A | - | A | - | - | - | - | - | A | - | - | - | - | - | A | - | - | - | A | - | - | - | X34 |
ALTER TABLE DROP COLUMN | C | C | - | C | C | - | - | - | - | - | - | - | - | - | - | C | - | - | - | C | - | - | - | X34 |
ALTER TABLE DROP CONSTRAINT | C | - | - | - | - | - | - | - | - | A1 | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER TABLE DROP PARTITIONING KEY | - | - | - | - | - | - | - | - | R20 | A1 | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER TYPE ADD ATTRIBUTE | - | - | - | - | - | R | - | - | - | A23 | - | - | R24 | - | - | - | - | - | - | R14 | - | - | - | - |
ALTER TYPE ALTER METHOD | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER TYPE DROP ATTRIBUTE | - | - | - | - | - | R | - | - | - | A23 | - | - | R24 | - | - | - | - | - | - | R14 | - | - | - | - |
ALTER TYPE ADD METHOD | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
ALTER TYPE DROP METHOD | - | - | - | - | - | - | R27 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
CREATE METHOD | - | - | - | - | - | - | - | - | - | A28 | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
CREATE TYPE | - | - | - | - | - | - | - | - | - | A29 | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP ALIAS | - | R | - | R | - | - | - | - | - | A3 | - | - | C3 | - | - | X3 | - | - | - | X3 | - | - | - | - |
DROP BUFFERPOOL | - | - | - | - | - | - | - | - | - | - | - | - | - | R | - | - | - | - | - | - | - | - | - | - |
DROP DATABASE PARTITION GROUP | - | - | - | - | - | - | - | - | - | - | - | - | - | C | - | - | - | - | - | - | - | - | - | - |
DROP FUNCTION | R | R7 | R | R | - | R | R7 | - | - | X | - | - | R | - | - | R | - | - | - | R | - | - | - | - |
DROP FUNCTION MAPPING | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP INDEX | R | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | R17 | - | - | - | - |
DROP INDEX EXTENSION | - | R | - | R | R | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP METHOD | R | R7 | R | R | - | R | R | - | - | X, A30 |
- | - | R | - | - | R | - | - | - | R | - | - | - | - |
DROP NICKNAME | - | R | - | R | C | - | R | - | - | A | - | - | C11 | - | - | - | - | - | - | X16 | - | - | - | - |
DROP PROCEDURE | - | R7 | - | R | - | - | R7 | - | - | A | - | - | - | - | - | R | - | - | - | - | - | - | - | - |
DROP SEQUENCE | - | R | - | - | - | - | R | - | - | A | - | - | - | - | - | R | - | - | - | - | - | - | - | - |
DROP SERVER | - | C21 | C19 | - | - | - | - | C | - | A | - | - | - | - | - | - | - | C19 | C | - | - | - | - | - |
DROP SERVICE CLASS | - | - | - | - | - | - | - | - | - | - | - | R35 | - | - | R35 | - | - | - | - | - | R35 | - | R35 | - |
DROP TABLE32 | C | R | - | R | C | - | - | - | - | A9 | - | - | R, C11 |
- | - | X16 | - | - | - | X16 | - | - | - | X34 |
DROP TABLE HIERARCHY | C | R | - | R | C | - | - | - | - | A9 | - | - | R, C11 |
- | - | X16 | - | - | - | X16 | - | - | - | - |
DROP TABLESPACE | - | - | - | - | C6 | - | - | - | - | - | - | - | C, R6 |
- | - | - | - | - | - | - | - | - | - | - |
DROP TRANSFORM | - | R | - | - | - | - | - | - | - | X | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP TRIGGER | - | - | - | - | - | - | - | - | - | A1 | - | - | - | - | - | X26 | - | - | - | - | - | - | - | - |
DROP TYPE | R13 | R5 | - | R | - | R | - | - | - | A12 | - | - | R18 | - | - | R13 | R4 | - | - | R14 | - | - | - | - |
DROP VARIABLE | - | - | R | R | - | - | R | - | - | A | - | - | - | - | - | R | - | - | - | R | - | - | - | - |
DROP VIEW | - | R | - | R | - | - | - | - | - | A2 | - | - | - | - | - | X16 | - | - | - | X15 | - | - | - | - |
DROP VIEW HIERARCHY | - | R | - | R | - | - | - | - | - | A2 | - | - | - | - | - | X16 | - | - | - | X16 | - | - | - | - |
DROP WORK CLASS SET | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | R36 | - | - |
DROP WRAPPER | - | - | C | - | - | - | - | - | - | - | C | - | - | - | - | - | - | C | - | - | - | - | - | - |
DROP XSROBJECT | C | - | - | - | - | - | - | - | - | A | - | - | - | - | - | X | - | - | - | X | - | - | - | - |
REVOKE a privilege10 | - | C, R25 |
- | - | - | - | C, R25 |
- | - | A1 | - | - | C, X8 |
- | - | X | - | - | - | X8 | - | - | - | - |
If a package has a statement acting on a typed view, creating or dropping any view in the same view hierarchy will invalidate the package.
Aliases themselves are not dependent on anything. It is possible for an alias to be defined on an object that does not exist.
If a package has a statement acting on a typed table, creating or dropping any table in the same table hierarchy will invalidate the package.
If a package, trigger or view includes the use of OUTER(Z) in the FROM clause, there is a dependency on the SELECT privilege on every subtable or subview of Z. Similarly, if a package, trigger, or view includes the use of DEREF(Y) where Y is a reference type with a target table or view Z, there is a dependency on the SELECT privilege on every subtable or subview of Z.
Cascade semantics apply to dependent materialized query tables.
A subtable is dependent on its supertables up to the root table. A supertable cannot be dropped until all of its subtables are dropped.
All packages that are dependent on methods defined in supertypes of the type being dropped, and that are eligible for overriding, are invalidated.
Mutator method: DROP METHOD A (TA) FOR T
Observer method: DROP METHOD A () FOR T
ALTER TYPE T
DROP METHOD A(TA)
DROP METHOD A()
Statement | Impacted Dependent Objects |
---|---|
ALTER NICKNAME (altering the local name or the local type) | Anchor Type, Function, Method, Procedure, User Defined Type, Variable, View |
ALTER TABLE ALTER COLUMN | Anchor Type, Function, Method, Procedure, Trigger4, User Defined Type, Variable, View, XSROBJECT |
ALTER TABLE DROP COLUMN2 | Anchor Type, Function, Method, Index, Procedure, Trigger4, User Defined Type, Variable, View, XSROBJECT |
ALTER TABLE RENAME COLUMN1, 3 | Anchor Type, Function, Method, Index, Procedure, Trigger4, User Defined Type, Variable, View, XSROBJECT |
ALTER TYPE ADD ATTRIBUTE | View |
ALTER TYPE DROP ATTRIBUTE | View |
DROP ALIAS | Anchor Type, Function, Method, Procedure, Trigger, User Defined Type, Variable, View |
DROP FUNCTION (ALTER MODULE DROP FUNCTION) | Function, Function Mapping, Index Extension, Method, Procedure, Trigger, Variable, View |
DROP METHOD | Function, Function Mapping, Index Extension, Method, Procedure, Trigger, Variable, View |
DROP NICKNAME | Anchor Type, Function, Method, Procedure, Trigger, User Defined Type, Variable, View |
DROP PROCEDURE (ALTER MODULE DROP PROCEDURE) | Function, Method, Procedure, Trigger |
DROP SEQUENCE | Function, Method, Procedure, Trigger, Variable, View |
DROP TABLE | Anchor Type, Function, Method, Procedure, Trigger4, User Defined Type, Variable, View, XSROBJECT |
DROP TABLE HIERARCHY | Function, Method, Procedure, Trigger, Variable, View |
DROP TRIGGER | Trigger |
DROP TYPE (ALTER MODULE DROP TYPE) | Anchor Type, Cursor Type, Function, Method, Procedure, Index Extension, Trigger, User Defined Type, Variable, View |
DROP VARIABLE (ALTER MODULE DROP VARIABLE) | Anchor Type, Function, Function Mapping, Method, Procedure, Trigger, User Defined Type, Variable, View |
DROP VIEW | Anchor Type, Function, Method, Procedure, Trigger4, User Defined Type, Variable, View |
DROP VIEW HIERARCHY | Function, Procedure, Trigger, Variable, View |
DROP XSROBJECT | Trigger, View |
RENAME TABLE | Anchor Type, Function, Method, Procedure, Trigger4, User Defined Type, Variable, View, XSROBJECT |
REVOKE a privilege | Function, Method, Procedure, Trigger, Variable, View |
CREATE OR REPLACE ALIAS1 | Function, Trigger, Procedure, Variable, View |
CREATE OR REPLACE VIEW1 | Anchor Type, Function, Method, Procedure, Trigger4, User Defined Type, Variable, View |
CREATE OR REPLACE FUNCTION1 | Function, Function Mapping, Index Extension, method, Procedure, Variable, View |
CREATE OR REPLACE PROCEDURE1 | Function, Method, Procedure, Trigger |
CREATE OR REPLACE NICKNAME1 | Function, method, Procedure, Variable, View |
CREATE OR REPLACE SEQUENCE1 | Function, Method, Procedure, Trigger, Variable, View |
CREATE OR REPLACE VARIABLE1 | Function, Method, Procedure, Trigger, User Defined Type, Variable, View |
CREATE OR REPLACE TRIGGER1 | Trigger |
The DROP DATABASE PARTITION GROUP statement might fail (SQLSTATE 55071) if an add database partition server request is either pending or in progress. This statement might also fail (SQLSTATE 55077) if a new database partition server is added online to the instance and not all applications are aware of the new database partition server.
Since the above describes the only circumstances under which dependencies are recorded due to implicit invocation of transforms, no objects other than functions, methods, or packages can have a dependency on implicitly invoked transform functions. On the other hand, explicit calls to transform functions (in views and triggers, for example) do result in the usual dependencies of these other types of objects on transform functions. As a result, a DROP TRANSFORM statement may also fail due to these "explicit" type dependencies of objects on the transform(s) being dropped (SQLSTATE 42893).
DROP TABLE TDEPT
DROP VIEW VDEPT
DROP ALIAS A1
The alias HEDGES.A1 is removed from the catalogs.
DROP ALIAS T1
This statement fails (SQLSTATE 42809).
Example 5:
DROP TABLESPACE ACCOUNTING
DROP TABLESPACE PLANS
DROP DATABASE PARTITION GROUP BUSINESS_OPS
DROP FUNCTION CENTRE (INT,FLOAT)
DROP SPECIFIC FUNCTION PELLOW.FOCUS92
DROP FUNCTION CHEM.ATOMIC_WEIGHT
DROP TRIGGER SALARY_BONUS
DROP TYPE SHOESIZE
DROP EVENT MONITOR SMITHPAY
DROP TABLE PART
DROP SCHEMA INVENTRY RESTRICT
DROP SPECIFIC PROCEDURE EIGLER.DESTROY
DROP PROCEDURE BIOLOGY.OSMOSIS
DROP USER MAPPING FOR SHAWN SERVER ORACLE1
DROP INDEX INDEXSPEC
DROP TRANSFORM MYSTRUCT1 FOR POLYGON
DROP METHOD BONUS (SALARY DECIMAL(10,2)) FOR PERSONNEL.EMP
DROP SEQUENCE ORG_SEQ
DROP TABLE EMPLOYEE
DROP FUNCTION MAPPING BONUS_CALC
DROP SECURITY LABEL COMPONENT LEVEL
DROP SECURITY LABEL DATA_ACCESS.EMPLOYEESECLABEL
DROP SECURITY POLICY DATA_ACCESS
DROP SECURITY LABEL COMPONENT GROUPS
DROP XSROBJECT HR.EMPLOYEE
DROP SERVICE CLASS DOGSALES UNDER PETSALES
DROP SERVICE CLASS PETSALES