Which tables in the Harvest database consume the most space?

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

In Harvest we have an Oracle 12 database where the following table is the largest and we want to know if it is possible to shrink it, the table is:

TABLESPACE	HARVESTBLOB
OWNER	OBJECT_NAME	OBJECT_TYPE	SIZE
HARVEST	SYS_LOB0001486336C00012$$	LOBSEGMENT	433.32 GB
HARVEST	HARVERSIONDATA	TABLE	1.39 GB
HARVEST	SYS_IL0001486336C00012$$	LOBINDEX	784 MB
HARVEST	SYS_LOB0001486355C00008$$	LOBSEGMENT	72 MB
HARVEST	SYS_LOB0001486339C00004$$	LOBSEGMENT	5 MB

Environment

Release : 13.0 and up

Component : CA Harvest Software Change Manager

Resolution

I expect most of the space in your database is taken up by the data in the HARVERSIONDATA table. When you check in a file, the contents of that file are stored in this table. Here's a query that will best tell you which tables in Harvest's database take up the most space:

SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS MB,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
/* — Tables */
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
/* — Indexes */
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
/* — LOB Segments */
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
/* — LOB Indexes */
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('harvest') /* <--- Schema owner name */
)
/* WHERE total_table_MB > 10 */ /* If you want to only see tables that are > 10MB, uncomment this where clause */
ORDER BY total_table_MB DESC, MB DESC