Re: reclaiming wasted space in Oracle

On Wednesday, May 14, 2014 5:46:20 PM UTC-4, Mark D Powell wrote:
> On Wednesday, May 14, 2014 6:38:47 AM UTC-4, Robert Grzesiak wrote:
>
> > W dniu środa, 14 maja 2014 01:56:31 UTC+2 użytkownik Mark D Powell napisał:
>
> >
>
> > > On Tuesday, May 13, 2014 9:15:25 AM UTC-4, 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
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > > 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 --
>
> >
>
> >
>
> >
>
> > 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.
>
>
>
> 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 --

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. Received on Wed May 14 2014 - 23:47:55 CEST