Delete empty rows from a table

[jnrpeardba]

Hi Tim,

Can you tell me how I would remove empty rows from a table, so I can shrink back the table to the optimum size

I cannot use delete ... from table where row ..... - because the table has 1/2 million rows

Is there a simple way to do this?

I have a DB with 3 tables, the largest being a repository of word documents. Oracle say's there's 40,000 documents, and on average they are 60KB long. By my reckoning that should be 2.2GB in total.

However, Oracle reckons it's at its 11GB limit, and running a query (pulled from the internet) sums the table to be 9922 MB (based on various dba_ tables).
Is there some DB shrinkage I can do here, as it seems Oracle objects are consuming more space than they need? I have tried running:

alter table <table_name> enable row movement;
alter table <table_name> shrink space;

...but it doesn't seem to have helped.

Your advice would be appreciated

regards

jnrpeardba

[Tim...]

Hi.

"empy rows"? Do you mean empty space in the table/tablespace?

The shrink space should reduce the size of the segment. It will not free up any unused space in the tablespace.

If you were hoping to reduce the size of the datafiles/tablespace, you need to read this.

http://www.oracle-base.com/articles/mis ... -space.php

If not, then I really don't understand your question.

Cheers

Tim...

[jnrpeardba]

Hi Thanks Tim,

I will try to be more clearer

I think it's the table I'm trying to free up space in.

I ran the following query:

Code: Select all

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 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('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

And it shows my 2 tables:

Code: Select all

OWNER      TABLE_NAME                       MEG
---------- -------------------------------- ---
OHCLINICAL ORIGINAL_DOCUMENTS               9923
DOCSUSER                                     

OHCLINICAL CONVERTED_DOCUMENTS              619
DOCSUSER       

The Original_Documents table consumes 9.7GB, almost the full 11GB of Oracle 11g XE. However, after deleting half of that table using:

Code: Select all

delete from Original_Documents where rownum < (select count / 2 from original_documents)

It still shows as the same size. I assume that's the table that's consuming the space, not the tablespace?

I tried running

Code: Select all

alter table OHCLINICALDOCSUSER.ORIGINAL_DOCUMENTS shrink space;

but that's had no affect.

Hope this has made it more clearer and apologies for my lack of understanding

Jnrpeardba

[Tim...]

Hi.

OK. When you delete data, you do not release any of the space. Oracle leaves all the extents in the segment alone, ready for reuse. This is why a delete does not free up your space.

The shrink should recover the extra space in the table, but not the other dependent objects. To do that you need to do this.

Code: Select all

ALTER TABLE table-name SHRINK SPACE CASCADE;

Note. The tablespace holding the table is not affected by this operation. It will have no impact on the tablespace and datafile sizes. Just the objects they contain.

Cheers

Tim...