The ALTER TENANT statement can be used to:

  • Modify the definition of a tenant
  • Change the NO SPACE parameter specified for one or more existing multi-tenant tables
  • Add an existing tenant to an existing multi-tenant group that is defined for a multi-tenant table

The table name and group name specified in the FOR TABLE JOIN clause should not be repeated. You can add the tenant to only one group for a given table. However, you can add it to multiple groups on different tables.

The current tenant partition for the table specified in the FOR TABLE clause should be allocated NO SPACE. When the tenant for the specified table joins a group, its existing table, index, and LOB partitions for that table is removed. No new partition is created for the tenant-table combination.

Syntax

ALTER TENANT tenant_name
 [FOR TABLE [owner_name] table_name_1 
   [{[USING TABLE AREA table_area_name] 
   [ USING INDEX AREA index_area_name]           
   [ USING LOB AREA lob_are_name] 
    | JOIN GROUP group_name }]...;

Parameters

tenant_name

Specifies the name of the tenant you want to modify.

FOR TABLE

Indicates the default area, which can be overridden by a specific area for a table, or for its index, or for its LOB areas. You can specify an area of storage for data of tables and indexes.

TABLE AREA area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE TABLE statement execution.The TABLE AREA specification is optional.

INDEX AREA index_area_name

Modifies the name of the default index storage area for a tenant.

If the storage area for an index is omitted in the syntax, then the default index area of the tenant is allocated to the specified index.

LOB AREA lob_area_name

Modifies the name of the default LOB storage area for a tenant.

If the storage area for a LOB is omitted in the syntax, then the default LOB area of the tenant will be allocated to the specified LOB.

table_name_1

Specifies the name of the table. The referenced table name must be a valid table in the multi-tenant database.

owner_name

Specifies the name of the schema that qualifies the table.

JOIN GROUP

Adds the tenant to the specified group.

group_name

Specifies the name of the group that you want the tenant to join.

Example

The following example modifies the table partition of the tenant to the allocated space and adds the tenant to a group:


ALTER TENANT Mega_Media_Networks
    FOR TABLE pub.finance_accts USING TABLE DEFAULT AREA
    FOR TABLE pub.new_customers USING TABLE AREA "MMN Data area"
                                USING INDEX AREA "MMN_Index_Area"
    FOR TABLE pub.farm_location
    FOR TABLE pub.archive_10yr JOIN GROUP Joint_Archives
;