DROP SCHEMA (Delete schema)

Function

DROP SCHEMA deletes a schema defined in a schema definition.

Privileges

Owner of the schema

The owner of a schema can delete that schema.

Users with the DBA privilege

A user with the DBA privilege can delete schemas owned by other users.

Format

DROP SCHEMA authorization-identifier [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the user who owns the schema.
WITH PROGRAM
When deleting a schema, this option is specified to nullify any of the following: base tables in the specified schema, view tables, foreign tables, indexes, foreign indexes, abstract data types, routines, and SQL objects of other uses for which functions, procedures, and triggers that use a trigger are in effect.
If WITH PROGRAM is omitted, and if there are base tables in the schema, view tables, foreign tables, indexes, foreign indexes, abstract data types, procedures, and SQL objects of other uses for which functions, procedures, and triggers that use a trigger are in effect, the schema cannot be deleted.

Common rules

  1. The following items are deleted: all base tables in the schema of a specified authorization identifier, view tables (including public views), foreign tables, indexes, foreign indexes, comments, access privileges, routines, triggers, abstract data types, and index types.
  2. Even if WITH PROGRAM is specified, the schema cannot be deleted if there are abstract data types in the specified schema, and SQL object of other users for which functions, procedures, and triggers are in effect that use functions, procedures, or triggers.
  3. If there are other users' view tables or public views that use a function in a specified schema, the schema cannot be deleted.
  4. The schema cannot be deleted if there is a table or index of another user that uses the abstract data type in the schema.
  5. In the following cases, DROP SCHEMA cannot be executed from within a Java procedure:
    • The SQL object being executed will be invalidated or deleted.
    • The Java procedure being executed will be deleted.
  6. If tables in the specified schema, indexes, and columns of the abstract data type containing LOB columns and LOB attributes that are defined for the table are stored in an RDAREA to which the inner replica facility is applied, the schema can be deleted, provided that certain conditions are met. For details about execution conditions for DROP SCHEMA under a condition in which the inner replica facility is used, see the manual HiRDB Staticizer Option Version 7.
  7. If a specified schema contains a falsification-prevented table and the falsification-prevented table contains rows, that schema cannot be deleted.
  8. Users with the DBA privilege can delete schemas belonging to an auditor. However, if there is an audit trail table in the schema belonging to an auditor, the schema owned by the auditor cannot be deleted.

Notes

  1. The DROP SCHEMA statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any information associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
  3. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.

Example

Delete the schema owned by the user whose authorization identifier is USER1:

DROP SCHEMA
   AUTHORIZATION USER1