ALTER SCHEMA

Purpose

This statement alters a schema. You can alter a schema in the following ways:

  • With the CHANGE OWNER option, you can assign a schema to another user or role. The schema and all its objects will then belong to the specified user or role.
  • You can set a schema quota with the RAW_SIZE_LIMIT option.
  • For virtual schema, you can change its properties or refresh its metadata.

Prerequisites

  • You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

  • Additional prerequisites for physical schemas:
    • System privilege ALTER ANY SCHEMA.
  • Additional prerequisites for virtual schemas:
    • CHANGE OWNER: System privilege ALTER ANY VIRTUAL SCHEMA.
    • SET: System privilege ALTER ANY VIRTUAL SCHEMA, object privilege ALTER on the schema, or the ownership of the schema by the current user or one of its user roles. Additionally, access rights are necessary on the corresponding adapt script.
    • REFRESH: System privilege ALTER ANY VIRTUAL SCHEMA or ALTER ANY VIRTUAL SCHEMA REFRESH, object privilege ALTER or REFRESH on the schema, or ownership of the schema by the current user or one of its roles. Additionally, access rights are necessary on the corresponding adapter script.

Syntax

alter_schema::=

Alter Schema

alter_virtual_schema::=

Alter Virtual Schema

Alter Virtual Schema

Usage Notes

  • This command is not a part of the SQL standard.
  • The option RAW_SIZE_LIMIT imposes a limitation on the size of a schema, i.e., the sum of the raw data sizes in bytes of all the objects within the schema. This is similar to quotas within a file system. You can find the raw object sizes in the system table EXA_ALL_OBJECT_SIZES. The value NULL indicates that there is no limit.

    • Note: To avoid typos while specifying long numbers, we suggest you to use a numerical expression. For example, 128*1024*1024 for 128MB.

  • For details about the virtual schema concept, refer to Virtual Schemas section.
  • The REFRESH option triggers an update of the metadata of a virtual schema, that is, the underlying objects of the external data source. This update is only done explicitly by this statement, as doing this implicitly while querying the virtual schemas could lead to transaction conflicts for read-only transactions.

Examples

ALTER SCHEMA s1 CHANGE OWNER user1;
ALTER SCHEMA s1 CHANGE OWNER role1;
ALTER SCHEMA s1 SET RAW_SIZE_LIMIT = 128*1024*1024;
ALTER VIRTUAL SCHEMA s2
  SET CONNECTION_STRING = 'jdbc:hive2://localhost:10000/default';
ALTER VIRTUAL SCHEMA s2 REFRESH;