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 previously mentioned 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, storage 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 row permission or a column mask, 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 an event monitor or usage list the privilege held by the authorization ID of the statement must include SQLADM or DBADM 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.
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-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--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-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-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-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 (SQLSTATE 428EK) that exists at the current server (SQLSTATE 42704).
If the alias is referenced in the definition of a row permission or a column mask, the alias cannot be dropped (SQLSTATE 42893).
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.
IBMCATGROUP, IBMDEFAULTGROUP, and IBMTEMPGROUP 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.
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.
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.
If the module is referenced in the definition of a row permission or a column mask, the module cannot be dropped (SQLSTATE 42893).
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).
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.
Identifies the storage group that is to be dropped; storagegroup-name must identify a storage group that exists at the current server (SQLSTATE 42704). This is a one-part name.
The current default storage group cannot be dropped (SQLSTATE 42893). A new default can be designated using the ALTER STOGROUP statement.
The DROP STOGROUP statement cannot be executed while a database partition server is being added (SQLSTATE 55071).
Identifies the base table, created temporary table, or declared temporary table that is to be dropped. The table-name must identify a table that is described in the catalog or, if it is a declared temporary table, the table-name must be qualified by the schema name SESSION and exist in the application (SQLSTATE 42704). The subtables of a typed table are dependent on their supertables. All subtables must be dropped before a supertable can be dropped (SQLSTATE 42893). The table-name must not identify a catalog table (SQLSTATE 42832), or a history table associated with a system-period temporal table (SQLSTATE 42893). The specified table is deleted from the database.
All indexes, primary keys, foreign keys, row permissions (including the default row permission), column masks, check constraints, materialized query tables, and staging tables that are defined on the table are dropped. All views and triggers that reference the table are made inoperative, including both the table referenced in the ON clause of the CREATE TRIGGER statement and all tables referenced within the triggered SQL statements. All packages which depend on any object dropped or marked inoperative will be invalidated. This includes packages dependent on any supertables above the subtable in the hierarchy. Any referenced 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.
If table-name is a system-period temporal table, any associated history table and any indexes defined on the history table are also dropped. To drop a system-period temporal table, the privilege set must also contain the authorization required to drop the history table (SQLSTATE 42501).
A history table associated with a system-period temporal table cannot be explicitly dropped using the DROP statement (SQLSTATE 42893). A history table is implicitly dropped when the associated system-period temporal table is dropped.
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.
When a table is dropped, all row permissions, including the default row permission, and column masks that are created for the table are also dropped.
If the table is referenced in the definition of a row permission or a column mask, the table cannot be dropped (SQLSTATE 42893).
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 built-in 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.
If the type is referenced in the definition of a row permission or a column mask, the type cannot be dropped (SQLSTATE 42893).
No additional objects are dropped.
The authorization-name must be listed in the catalog (SQLSTATE 42704). The server-name must identify a data source that is described in the catalog (SQLSTATE 42704). The user mapping is deleted.
No additional objects are dropped.
If the variable is referenced in the definition of a row permission or a column mask, the variable cannot be dropped (SQLSTATE 42893).
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.
If the view is referenced in the definition of a row permission or a column mask, the view cannot be dropped (SQLSTATE 42893).
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).
Statement | Object Type | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C |
F |
F |
G |
I |
I |
M |
M |
N |
D |
P |
P |
S |
S |
T |
T |
T |
T |
T |
T |
U |
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 | - | - | R | - | - | - | A | - | - | - | - | - | - | A | - | - | - | - | A | - | - | - | X34 |
ALTER TABLE DROP COLUMN | C | C | - | C | C | - | R | - | - | - | - | R | - | - | - | - | - | 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 | - | - | R | - | - | - | A3 | R | - | - | C3 | - | - | X3 | - | - | - | - | X3 | - | - | - | - |
DROP BUFFERPOOL | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | R | - | - | - | - | - | - | - | - | - | - | - |
DROP DATABASE PARTITION GROUP | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | C | - | - | - | - | - | - | - | - | - | - | - |
DROP FUNCTION | R | R7 | R | R | - | R | R | R7 | - | - | X | R | - | - | R | - | - | R | - | - | - | - | R | - | - | - | - |
DROP FUNCTION MAPPING | - | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP INDEX | R | - | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - | - | C37 | - | R17 | - | - | - | - |
DROP INDEX EXTENSION | - | R | - | R | R | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP MASK | - | - | - | - | - | - | - | - | - | - | A39 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
DROP METHOD | R | R7 | R | R | - | R | - | R | - | - | X, A30 |
- | - | - | R | - | - | R | - | - | - | - | R | - | - | - | - |
DROP NICKNAME | - | R | - | R | C | - | - | R | - | - | A | - | - | - | C11 | - | - | - | - | - | - | - | X16 | - | - | - | - |
DROP PERMISSION | - | - | - | - | - | - | - | - | - | - | A40 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
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 STOGROUP | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | R | - | - | - | - | - | - | - | - | - | - | - |
DROP TABLE32 | C | R | - | R | C | - | R | - | - | - | A9 | R | - | - | R, C11 |
- | - | X16 | - | - | C37 | - | 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 | R | - | - | A | R | - | - | - | - | - | R | - | - | - | - | R | - | - | - | - |
DROP VIEW | - | R | - | R | - | - | R | - | - | - | A2 | R | - | - | - | - | - | 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 |
- | - | - | - | R38 | C, R25 |
- | - | A1 | R38 | - | - | 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.
A history table is dependent on the system-period temporal table with which it is associated. Cascade semantics apply to the history table when the system-period temporary table on which depends is 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 these describe 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).
CREATE PERMISSION RP1 ON T1 FOR ROWS
WHERE C1>(SELECT MAX(C1) FROM T2)
ENFORCED FOR ALL ACCESS
ENABLE;
CREATE PERMISSION RP2 ON T2 FOR ROWS
WHERE C1>(SELECT MAX(C1) FROM T1)
ENFORCED FOR ALL ACCESS
ENABLE
The DROP TABLE T1 and DROP TABLE T2 statements fail because RP1 depends on T2 and RP2 depends
on T1. The user with the SECADM authority should
drop one of the row permissions first then issue the DROP
TABLE statement. 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). 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.SQL100506102825100
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
DROP PERMISSION P1
DROP MASK M1
DROP STOGROUP TEST_SG
DROP USAGE LIST MON_PAYROLL