Thereapy for Zelenograd

-- Clearing UPDATELIST table
--1 930 637
begin
  for rec in (
    select T.ROWID TR, row_number() over (order by T.RN) RRN
    from UPDATELIST T
    where to_number(to_char(T.MODIFDATE, 'yyyy')) < 2015
    order by RN)
  loop
    delete UPDATELIST where ROWID = rec.TR;
    if mod(rec.RRN, 100) = 0 then
      commit;
    end if;
  end loop;
  commit;
end;
/

create tablespace PARUS_IND2 datafile 
  'D:\BASES\PZAO1\PARUS_IND2.DAT'
  size 10M autoextend on next 1M 
  maxsize unlimited logging online;


select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;'
from DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PARUS_IND'
  and SEGMENT_TYPE    = 'INDEX'
  and OWNER           = 'PARUS'

drop tablespace PARUS_IND including contents and datafiles;

How to calculate size of tablespaces

select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, BLOCKS
from 
(
  select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS
  from DBA_DATA_FILES
  union all
  select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS
  from DBA_TEMP_FILES
  order by TABLESPACE_NAME, FILE_NAME
);

Recreation indexes' tablespace

create tablespace PARUS_IND2 datafile 
  'D:\BASES\PZAO1\PARUS_IND2.DAT'
  size 10M autoextend on next 1M 
  maxsize unlimited logging online;


select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;'
from DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PARUS_IND'
  and SEGMENT_TYPE    = 'INDEX'
  and OWNER           = 'PARUS'

drop tablespace PARUS_IND including contents and datafiles;