ALTER SCHEMA

Use the ALTER SCHEMA command to change the properties of a schema such as its path, name, or authorization user. You cannot alter a schema if it is being accessed in another session. You cannot alter a schema to move it from one database to another.

Syntax

Syntax of the ALTER SCHEMA command:

ALTER SCHEMA [<database_name>.]<schema_name> 
  { AUTHORIZATION TO <username> | 
    RENAME TO [<database_name>.]<new_name> |
    SET PATH '<schema_path>' |
    DATA_VERSION_RETENTION_TIME <number-of-days> NOCASCADE}

Inputs

The ALTER SCHEMA command takes the following inputs:

Table 1. ALTER SCHEMA inputs
Input	Description
<database_name>	The name of the database where the schema is defined. The default is the current database.
<schema_name>	The name of the schema.
<new_name>	The changed schema name. Note that you cannot move the schema to a different database.
AUTHORIZATION TO <user_name>	The authorization user, or owner, of the schema. The name must already exist in the system.
RENAME TO <new_name>	The new name of the schema.
SET PATH <schema_path>	The list of schema names that the system searches through to resolve unqualified routine names, such as the names of functions, stored procedures, and user-defined objects such as functions, aggregates, and libraries. A schema name that is not fully qualified (that is, that does not include a database prefix) is in the current database. If you alter the path, you must specify a completely new path; it is not possible to append a schema to or delete a schema from the path.
DATA_VERSION_RETENTION_TIME <number-of-days> NOCASCADE	Updates the value of DATA_VERSION_RETENTION_TIME for the given schema. The new value has no impact on existing tables in the database. But, the new value determines the default value that is inherited by a subsequent CREATE TABLE command in the schema that at does not explicitly specify this property. The maximum allowed value is 92 days, which is the maximum number of days in a calendar quarter.

Outputs

The ALTER SCHEMA command produces the following outputs:

Table 2. ALTER SCHEMA outputs
Output	Description
`ALTER SCHEMA`	The command was successful.
`ERROR: ALTER SCHEMA: schema "schemaname" is being accessed by other users`	The schema is being used by other users.

Privileges

You must be the admin user, the schema owner, the owner of the database where the schema resides, or your account must have Alter privilege for theschema or for the Schema object class. If you are changing the authorization user, you must have List access to that user. The Netezza Performance Server system must be configured to support multiple schemas. For more information on enabling multiple schema support, see the IBM® Netezza® System Administrator’s Guide.