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 privilegeUSAGE
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
.
- System privilege
- Additional prerequisites for virtual schemas:
CHANGE OWNER
: System privilegeALTER ANY VIRTUAL SCHEMA
.SET
: System privilegeALTER ANY VIRTUAL SCHEMA
, object privilegeALTER
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 privilegeALTER ANY VIRTUAL SCHEMA
orALTER ANY VIRTUAL SCHEMA REFRESH
, object privilegeALTER
orREFRESH
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.
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 valueNULL
indicates that there is no limit. - 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.
Note: To avoid typos while specifying long numbers, we suggest you to use a numerical expression. For example, 128*1024*1024 for 128MB.