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;