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 [WITH PROGRAM]
- authorization-identifier
- Specifies the authorization identifier of the user who owns the schema.
- WITH PROGRAM
- When deleting a schema, specify this operand to disable another user's valid SQL object for a function, procedure, or trigger that uses a base table, view table, index, abstract data type, routine, trigger, or sequence generator in a specified schema.
- If WITH PROGRAM is omitted and another user has a valid SQL object for a function, procedure, or trigger that uses a base table, view table, index, abstract data type, procedure, trigger, or sequence generator in a schema, that schema cannot be deleted.
Common rules
- All base tables, view tables (including public views), indexes, comments, access privileges, routines (including public procedures and public functions for which an authorization identifier is defined with DROP SCHEMA), triggers, abstract data types, index types, and sequence generators in a schema of a specified authorization identifier are deleted.
- Even when WITH PROGRAM is specified, if another user has a valid SQL object for a function, procedure, or trigger that uses an abstract data type, function, procedure, trigger, or sequence generator in a specified schema, that schema cannot be deleted.
- The schema cannot be deleted if another user has a view table or public view of that uses the function of a specified schema.
- The schema cannot be deleted if there is a table or index of another user that uses the abstract data type in the schema.
- 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.
- 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 Version 9 Staticizer Option.
- If a specified schema contains a falsification-prevented table and the falsification-prevented table contains rows, that schema cannot be deleted.
- 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
- The DROP SCHEMA statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- If a valid SQL object of a function, procedure, trigger, or sequence generator is disabled by specifying WITH PROGRAM, the information about the disabled function, procedure, trigger, or sequence generator in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
- To execute a valid SQL object of a function, procedure, trigger, or sequence generator that has been disabled by specifying WITH PROGRAM, you must execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER, to recreate the valid SQL object of the function, procedure, trigger, or sequence generator.
Example
Delete the schema owned by the user whose authorization identifier is USER1:
DROP SCHEMA
AUTHORIZATION USER1