Change Tablespaces

Use this API to alter the tablespace of Table instances and indexes in a single container—Domain, Application Area, or Work Area—identified recursively. This may be helpful if you want to archive data or if the current tablespace runs out of room. You can associate Table instances and indexes with the same tablespace or two different ones.

The API generates a script to change the tablespace attribute of all table instances and indexes in the container you specify. It also analyzes the effect of the change and generates a log file that may include errors or warnings.

To Use this API:

1. Take a backup of the database.
2. Run the API with a database account associated with an application user.
3. Check the generated log file for errors and warnings before running the generated script.
4. Run the script with a database account with DBA privileges.

   Note:

   If a Work Area is installed after its Table instances and indexes have been changed to a different tablespace, some of the new tablespace associations are lost:

   • Table instances remain in the new tablespace.
   • Temporary tables revert to the default tablespace.
   • Nonunique and bitmap indexes are associated with the new tablespace of the Table instances, even if they had been in a different new tablespace.
   • All other indexes revert to the default tablespace.

   You can run the script again to properly reassociate all objects with the preferred tablespaces.

Name

CDR_PUB_REORG_TABLES.genTablesTablespaceAlterScript

Signature

procedure genTablesTablespaceAlterScript(
		p_api_version      IN NUMBER ,
		p_init_msg_list    IN VARCHAR2 DEFAULT CDR_PUB_DEF_CONSTANTS.G_FALSE,
		p_validation_level IN NUMBER DEFAULT 
                   CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
		pi_companyId        IN cdr_namings.company_id%TYPE,
		pi_objId            IN cdr_namings.obj_id%TYPE,
		pi_targetTabTablespace IN VARCHAR2,
		pi_targetIdxTablespace IN VARCHAR2,
		po_cReturnClob OUT NOCOPY clob,
		po_cReturnLogClob OUT NOCOPY clob,
		x_return_status OUT NOCOPY VARCHAR2,
		x_msg_count OUT NOCOPY NUMBER,
		x_msg_data OUT NOCOPY VARCHAR2);

Parameters

This API has standard parameters (see the Oracle Health Sciences Life Sciences Data Hub API Guide) and the following parameters:

Note:

Unlike other LSH public APIs, this API has the standard P_COMMIT parameter permanently set to FALSE.

• PI_COMPANYID (Mandatory). Enter the company ID of the container object—Domain, Application Area, or Work Area—that contains the table instances whose tablespace you want to change. To get your company ID, use CDR_PUB_DEF_FACTORY_UTILS.GetCompanyId.
• PI_OBJID (Mandatory). Enter the object ID of the container object—Domain, Application Area, or Work Area—that contains the table instances whose tablespace you want to change.
• PI_TARGETTABTABLESPACE (Mandatory). Enter the name of the new tablespace for tables.
• PI_TARGETIDXTABLESPACE (Mandatory). Enter the name of the new tablespace for indexes.
• PO_CRETURNCLOB The API returns a generated script CLOB output.
• PO_CRETURNLOGCLOB The API returns a generated log CLOB output.