search cancel

Which tables in the Harvest database consume the most space?

book

Article ID: 203247

calendar_today

Updated On:

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

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