Re: reclaiming wasted space in Oracle

On Tuesday, May 13, 2014 7:15:25 AM UTC-6, Robert Grzesiak wrote:
> 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

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 Received on Tue May 13 2014 - 15:46:28 CEST