This forum is currently locked. You can't register or post questions at this time. (read more)

Delete empty rows from a table

All posts relating to Oracle database administration.

Moderator: Tim...

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Delete empty rows from a table

Postby jnrpeardba » Fri Aug 10, 2012 8:39 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete empty rows from a table

Postby Tim... » Fri Aug 10, 2012 9:27 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Delete empty rows from a table

Postby jnrpeardba » Fri Aug 10, 2012 10:48 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete empty rows from a table

Postby Tim... » Fri Aug 10, 2012 12:04 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 6 guests

cron