reclaiming wasted space in Oracle

[Robert Grzesiak]

Welcome,

I would like to reclaim wasted space in tablespaces. I have Oracle 11g Standard Edition, RAC, ASM. I know only this method:

- check what blocks tbs space and move objects to any temporary tbs:
select * from dba_extents de, dba_objects do where de.tablespace_name = 'USERS'
and de.SEGMENT_NAME = do.object_name order by de.block_id desc

alter table tabName lob(lobKolName) store as (tablespace tmp_clear);
alter table tabName move tablespace tmp_clear;
alter index indName rebuild tablespace tmp_clear;

next:
ALTER DATABASE DATAFILE file_name || ''' resize 1G;' from dba_data_files where tablespace_name = 'USERS' -- autoextend on maxsize 10G

and move back to USERS tbs...

Is there any better process to do this ? I'm thinking about:
ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name SHRINK SPACE CASCADE;
but it is only on the table level, but what about tablespaces and datafiles ?

Sorry for my pure english...

Regards
Robert

[ddf]

Running 'alter table ... shrink space cascade;' can take quite a while for exceptionally large tables, and will lock the table until the action is completed. You will still need to manually adjust the size of the datafiles for the dessired tablespace; what the shrink space cascade gives you is an 'in-place' shrink of the table size, reorganizing the data and eliminating any embedded free space that may exist. As mentioned before shrink space may take longer than your current method; both lock the table/indexes until the task is finished so the table will be unavailable to users in either situation.


David Fitzjarrell

[joel garry]

On Tuesday, May 13, 2014 6:15:25 AM UTC-7, Robert Grzesiak wrote:

Don't forget to purge the recyclebin. http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

jg
--
@home.com is bogus.
http://it.tmcnet.com/news/2014/05/13/7825635.htm

[Mark D Powell]

Rather than move the tables to another tablespace you can just move the tables and rebuild the indexes in the existing locally managed tablespace. The existing extent allocation logic looks for free extents at the logical front of the file so the tablespace is usually compacted after moves.

HTH -- Mark D Powell --

[Robert Grzesiak]

so... can I use the same tabelspace in the following ? (where tabName is actually stored in tbs USERS):
alter table tabName lob(lobKolName) store as (tablespace USERS);
alter table tabName move tablespace USERS;
alter index indName rebuild tablespace USERS;

regards.

[Mark D Powell]

If you issue "alter table owner.t_1 move;" then Oracle by default performs the move the table's assigned tablespace. The same is true on an index rebuild.

[Mark D Powell]

PS - This does mean the tablespace must have enough free space to hold up to two full copies of the object while the move/rebuild runs.

[Mladen Gogala]

On Wed, 14 May 2014 14:46:20 -0700, Mark D Powell wrote:


> If you issue "alter table owner.t_1 move;" then Oracle by default
> performs the move the table's assigned tablespace. The same is true on
> an index rebuild.
>
> HTH -- Mark D Powell --

Also, there is a command "ALTER TABLE SHRINK SPACE" which can shrink
unused space in the table. That command should not be confused with the
TRUNCATE TABLE command, which also shrinks space in the table. There are
subtle but significant differences between the two commands.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[Robert Grzesiak]

ok, thanks :)

[Robert Klemme]

On 13.05.2014 15:15, Robert Grzesiak wrote:

> I would like to reclaim wasted space in tablespaces.

Since nobody else seems to have asked: why? I am asking because there
are scenarios where it does not make sense because, for example, the
table grows and shrinks over time. If you reclaim space when it is
shrunk you may actually do unnecessary work.

Kind regards

robert