ALTER SCHEMA - Amazon Redshift
Required privilegesSyntaxParametersExamples

ALTER SCHEMA

Changes the definition of an existing schema. Use this command to rename a schema or change the owner of a schema. For example, rename an existing schema to preserve a backup copy of that schema when you plan to create a new version of that schema. For more information about schemas, see CREATE SCHEMA.

To view the configured schema quotas, see SVV_SCHEMA_QUOTA_STATE.

To view the records where schema quotas were exceeded, see STL_SCHEMA_QUOTA_VIOLATIONS.

Required privileges

Following are required privileges for ALTER SCHEMA:

  • Superuser

  • User with the ALTER SCHEMA privilege

  • Schema owner

When you change a schema name, note that objects using the old name, such as stored procedures or materialized views, must be updated to use the new name.

Syntax

ALTER SCHEMA schema_name
{
RENAME TO new_name |
OWNER TO new_owner |
QUOTA { quota [MB | GB | TB] | UNLIMITED }
}

Parameters

schema_name

The name of the database schema to be altered.

RENAME TO

A clause that renames the schema.

new_name

The new name of the schema. For more information about valid names, see Names and identifiers.

OWNER TO

A clause that changes the owner of the schema.

new_owner

The new owner of the schema.

QUOTA

The maximum amount of disk space that the specified schema can use. This space is the collective size of all tables under the specified schema. Amazon Redshift converts the selected value to megabytes. Gigabytes is the default unit of measurement when you don't specify a value.

For more information about configuring schema quotas, see CREATE SCHEMA.

Examples

The following example renames the SALES schema to US_SALES.

alter schema sales
rename to us_sales;

The following example gives ownership of the US_SALES schema to the user DWUSER.

alter schema us_sales
owner to dwuser;

The following example changes the quota to 300 GB and removes the quota.

alter schema us_sales QUOTA 300 GB;
alter schema us_sales QUOTA UNLIMITED;