CARIS HPD : Database Administration : Upgrading a Schema : Before Upgrading an HPD Schema : Mandatory Elements : Tablespace Requirements – Database
 
Tablespace Requirements – Database

Before the upgrade, make sure that there is extra tablespace available or use the auto-extend parameter to ensure that the upgrade will have as much space as is needed. The extra space can be reclaimed after the upgrade is complete.

You should also increase the size of the TEMP tablespace or check its auto-extent parameters. The TEMP tablespace is used during the schema migration.

Example:

In this example, the HPD schema is named HPDOWNER and is hosted on an Oracle Instance called HPDSIM12.

1. Connect as the System or DBA user.

Find the amount of space currently used by the HPD schema.

2. Enter the following:

SELECT tablespace_name, sum(bytes)/1024/1024/1024 Total_GB from dba_segments where owner='HPDOWNER' group by tablespace_name;

The results are displayed. Two tablespaces are assigned to HPD.

TABLESPACE_NAME TOTAL_GB

--------------------- ----------

HPDOWNER 133.298767

INDEX01 3.95800781

Normally, you add space to the largest tablespace, which is the default tablespace for the HPD schema owner.

3. Enter the following command to confirm this action:

select default_tablespace from dba_users where username='HPDOWNER';

This result is displayed.

DEFAULT_TABLESPACE

------------------------------

HPDOWNER

Because you want to allocate more space to the data file for this tablespace, you must know the data file name.

4. Enter the following command:

select file_name from dba_data_files where tablespace_name='HPDOWNER';

The result is displayed.

FILE_NAME

-----------------------------------------------------------

E:\ORACLE\ORADATA\HPDSIM12\HPDOWNER.DBF

Now you can make the data file larger. If the HPDOWNER schema is 133 GB and you want to increase the size by 75%, multiply 133 by 1.75; your total is 232.75. Round the total up to 233 GB.

5. Enter this command:

ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\HPDSIM12\HPDOWNER.DBF' RESIZE 233G;

If you want to create new indexes in a location other than the same tablespace that tables are created in, you can run the setIndexTablespace.sql command. The script defines the tablespace in which new indexes are created. It is located in C:\ProgramData\CARIS\HPD\<version>\Server\
admintools.

6. [Optional] Enter the setIndexTablespace.sql command:

sqlplus hpdowner@hpdsim12 C:\ProgramData\CARIS\HPD\<version>\Server\upgrade\setIndexTablespace.sql

You can now upgrade the schema.