The Upgrade Process › Oracle Database Only: PRJ_BLB_SLICES Table

Oracle Database Only: PRJ_BLB_SLICES Table

The ID, UNIT, LAST_UPDATED_DATE and LAST_UPDATED_BY columns in the PRJ_BLB_SLICES table have been dropped for the 14.1 release. If you are using Oracle, the table is replicated before the columns are dropped. The replication requires you to verify that the tablespace that PRJ_BLB_SLICES occupies is large enough to accommodate the temporary size increase.

Upgrade Action:

To determine if the tablespace can deal with this condition, have your database administrator (as sysdba) run the following query. Change the schema owner (bold) to the owner of the CA Clarity PPM schema:

select SLC.owner, SLC.table_name, SLC.TABLESPACE_NAME, SLC.MB MB_NEEDED,  TBSPC.MB_FREE MB_FREE, SIZING.MAX_MB, SIZING.MB_USED,
EXT.AUTOEXTENSIBLE
FROM
(select owner, table_name, NVL(round((num_rows*avg_row_len)/(1024*1024)),0) MB, TABLESPACE_NAME
from all_tables 
where owner = 'CLARITY'
and table_name = 'PRJ_BLB_SLICES') SLC
INNER JOIN (select df.tablespace_name,
    (df.totalspace - tu.totalusedspace) "MB_FREE"
    from
    (select tablespace_name, 
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files
    group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
    from dba_segments
    group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name) TBSPC ON (SLC.TABLESPACE_NAME = TBSPC.TABLESPACE_NAME)
INNER JOIN (select distinct(autoextensible), tablespace_name from dba_data_files) EXT on (slc.tablespace_name = ext.tablespace_name)
INNER JOIN (select tablespace_name
       , count(*) as no_of_data_files
       , sum(MAXBYTES)/(1024*1024)*count(*) as MAX_MB
       , sum(user_bytes)/(1024*1024) MB_USED
       , round((sum(user_bytes)/(1024*1024))/(sum(MAXBYTES)/(1024*1024))*100,2) PERCENT_USED
from dba_data_files
group by tablespace_name) SIZING ON (SLC.TABLESPACE_NAME = SIZING.TABLESPACE_NAME)

The following example shows the kind of results that the query returns:

If AUTOEXTENSIBLE is YES:

The tablespace for PRJ_BLB_SLICES can grow automatically up to the MAX_MB value. Ensure that the MAX_MB value is higher than the MB_NEEDED + MB_USED.

If AUTOEXTENSIBLE is NO:

Ensure that the MB_NEEDED value is less than MB_FREE. If the MB_NEEDED value is not lower, the database administrator can allocate or extend extra data files to the tablespace under the TABLESPACE_NAME column.

We recommend increasing the tablespace at least 20 percent more than is needed. The increase helps to ensure that the tablespace can accommodate standard data growth and the temporary replication.