Просмотр исходного текста страницы Тотальная чистка скорой помощи (Оракл)

Чистка Oracle 10g (с ограничение в 4 гб). Подстанции СМП разрослись до такой степени, что чистка логов и awr помогала максимум на месяц.  Нашел способ осовободить несколько ГБ. 
Т.к. на ЦДС хранится полная история всех вызовов,то на ПС оставляем вызовы только за последние два года. Все отсальное удаляем, двигаем пользовательские таблицы , освобождаем пространсво. Дополнительно двигаем лобы.


* 1. Отключаем листенер (чтобы не было репликации и чтобы пользователи не подключались
* 2. Подключаемся к БД без указания алиса (в навигаторе или PL/SQL очищаем имя БД, в SQLPLUS conn sys/pass as sysdba ) 
* 3. выполняем скрипты
* 4. позже выложу


УДАЛЕНИЕ ЛИНКА НА ЦДС (не забыть в конце добавить линк)      
                                                                 
<source>
DROP PUBLIC DATABASE LINK stlink00;  
</source>

предварительная чистка логов

<source>
truncate table   mis_log.user_info_log;
truncate table  mis_log.tuser_msg_log;

ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i SHRINK SPACE;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r SHRINK SPACE;
ALTER TABLE mis_log.tuser_msg_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tuser_msg_log SHRINK SPACE;
ALTER TABLE mis_log.emergency_call_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source SHRINK SPACE;
ALTER TABLE mis_log.user_info_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.user_info_log SHRINK SPACE;

ALTER INDEX mis_log.pk_tuser_action_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.dr$idx_emergency_call_log_ft$x REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_id REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_user_info_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tmsg_source REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tuser_msg_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.ix_user_info_log$key_tbl_name REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_log_side REBUILD  NOLOGGING;

ALTER TABLESPACE log_data COALESCE;
ALTER TABLESPACE log_idx COALESCE;
</source>

Очистка LOG_DATA

<source>
truncate table sys.aud$;
truncate table mis.station_link_log;

purge tablespace LOG_DATA user mis;

purge tablespace USR_INDX user mis;
purge tablespace USR_DATA user mis;
purge tablespace UNDO user mis;
purge tablespace USERS user mis;
ALTER TABLE mis.team_state SHRINK SPACE;

truncate TABLE mis_log.user_info_log;
truncate TABLE mis_log.tuser_msg_log;

purge tablespace LOG_DATA user sys;

purge tablespace USR_INDX user sys;
purge tablespace USR_DATA user sys;
purge tablespace UNDO user sys;
purge tablespace USERS user sys;

purge tablespace SYSAUX user sys;
purge tablespace SYSAUX user system;
</source>


Запоминаем количество записей для отправки на ЦДС
(в идеале должно быть 0)
на работающей БД до 100 записей  

<source>
select count(1) from mis.buffer_transfer 
</source>

 
Отключаем триггеры репликации (не забываем вконце включить и проверить на валидность)

<source>
ALTER TRIGGER MIS.People_Addr$REPLICATION DISABLE; 
ALTER TRIGGER MIS.PEOPLE$REPLICATION DISABLE;
ALTER TRIGGER MIS.TEAM_EMP_SCHEDULE$REPLICATION DISABLE;
ALTER TRIGGER MIS.Call_Addr$REPLICATION DISABLE;
ALTER TRIGGER MIS.policlinic_call$REPLICATION DISABLE;
ALTER TRIGGER MIS.emergency_call$REPLICATION DISABLE;
ALTER TRIGGER MIS.team_emp_schedule$REPLICATION DISABLE;
ALTER TRIGGER MIS.TEAM_SHEDULE$REPLICATION DISABLE;
ALTER TRIGGER MIS.Station_Call$REPLICATION DISABLE;
ALTER TRIGGER MIS.team_state$REPLICATION DISABLE;
ALTER TRIGGER MIS.call_inform$REPLICATION DISABLE; 
ALTER TRIGGER MIS.call_treatment$REPLICATION DISABLE; 
ALTER TRIGGER MIS.MEDICATION$REPLICATION DISABLE; 

ALTER TRIGGER MIS.call_diag$REPLICATION DISABLE;  
ALTER TRIGGER MIS.EMERGENCY_CALL$AU_MESSAGE  DISABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL$BI DISABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL_LOG$AIUD DISABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL$REPLICATION DISABLE; 
ALTER TRIGGER MIS.TEAM_CALL$REPLICATION DISABLE; 
ALTER TRIGGER MIS.CALL_SURVEY$REPLICATION DISABLE; 
ALTER TRIGGER MIS.Additional_Call$REPLICATION DISABLE; 

</source>
 
Чистим пакеты (только на ПС, т.к. общая выгрузка идет с ЦДС. Не очищать, если выгрузка идет с ПС) (на некоторы БД может отсутсвовать mis.PAKAGE_DATA)

<source>
truncate table mis.PAKAGE_DATA;
-- Чистим сообщения
truncate table mis.message;
--Чистим полисы (таблица может отсутсвовать)
truncate table mis.people_insur;
</source>

<source>
на всякий случай еще раз проверяем/запоминаем количество записей в буфере
<source>
select count(1) from mis.buffer_transfer 
</source>

Временная таблица (в конце удалить)

<source>
-- Create table
create table MIS.call_tmp
(
  call_id        NUMBER ,
  people_id NUMBER,
  people_er_id NUMBER
  
)
tablespace USR_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ); 
-- Create/Recreate indexes  
create index MIS.IX_CALL_TMP$PEOPLE_ID on MIS.call_tmp (PEOPLE_ID)
  tablespace USR_INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ); 



create index MIS.IX_CALL_TMP$PEOPLE_er_ID on MIS.call_tmp (PEOPLE_er_ID)
  tablespace USR_INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ); 
  
  
create index MIS.IX_CALL_TMP$call_ID on MIS.call_tmp (call_ID)
  tablespace USR_INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ); 

</source>

НАЧАЛО ЧИСТКИ
ВНИМАНИЕ (ДОЛГИЙ ЗАПРОС)
Выполнятся может несколько чаосв
(работа через таблицу mis.call_tmp 
сокращает время работы в десятки раз)
(при желании можно еще оптимизировать)
 
добавлеям все вызовы и людей которые попадают за период
<source>
insert into mis.call_tmp(call_id, people_id, people_er_id)
(select ec.id,  pe.people_id , pe.id
from 
  mis.emergency_call ec
  left join mis.PEOPLE_ER pe on ec.people_id  = pe.id 
  where
    ec.regist_date < to_date('01.01.2016','dd.mm.yyyy')) ;
</source>

убираме из удаленных  людей б которые будут в в будущем
<source>
update mis.call_tmp set PEOPLE_ER_id = null , PEOPLE_id = null where PEOPLE_id in 
(select pe.people_id from mis.emergency_call ec, mis.PEOPLE_ER pe where pe.id = ec.people_id and ec.regist_date >= to_date('01.01.2016','dd.mm.yyyy'))   ;
</source>

убираем пустые записи
<source>
delete from mis.call_tmp  where call_id is null and  PEOPLE_id is null or  PEOPLE_er_id is null ;
</source>


УДАЛЕНИЕ ПОЛЬЗОВАТЕЛЬСКИХ ДАННЫХ ПО ВЫЗОВАМ
<source>
begin
  
    DBMS_OUTPUT.enable;
    DBMS_OUTPUT.PUT_LINE ('-3. Medication');  
    DBMS_OUTPUT.disable;
    
    delete from   mis.Medication where team_call_id in 
         (select sc.id 
           from mis.team_call sc, mis.call_tmp ec
           where sc.call_id = ec.call_id     ) ;     
  commit; 
    DBMS_OUTPUT.enable;
    DBMS_OUTPUT.PUT_LINE ('-2. PEOPLE_ADDR');  
    DBMS_OUTPUT.disable;
    
    delete from mis.People_Addr where 
    people_id      in (select pe.people_id from mis.call_tmp pe /* where pe.id < 50000 */);
  commit;  
  /*
    delete from mis.People_Addr where 
    people_id      in (select pe.people_id from mis.call_tmp pe where pe.id > 50000 and pe.id < 100000);    
  commit;  
    delete from mis.People_Addr where 
    people_id      in (select pe.people_id from mis.call_tmp pe where pe.id > 100000 and pe.id < 150000);    
  commit;  
    delete from mis.People_Addr where 
    people_id     in (select pe.people_id from mis.call_tmp pe where pe.id > 150000 and pe.id < 200000);            
  commit;  
    delete from mis.People_Addr where 
    people_id     in (select pe.people_id from mis.call_tmp pe where pe.id > 200000 and pe.id < 250000);                
  commit;  
    delete from mis.People_Addr where 
    people_id     in (select pe.people_id from mis.call_tmp pe where pe.id > 250000 and pe.id < 300000);                
  commit;  
    delete from mis.People_Addr where 
    people_id     in (select pe.people_id from mis.call_tmp pe where pe.id > 50000 and pe.id < 300000);                
  commit;  
  */
      
    DBMS_OUTPUT.enable;
    DBMS_OUTPUT.PUT_LINE ('-1. PEOPLE');  
    DBMS_OUTPUT.disable;
    
    delete from mis.PEOPLE where id in (select pe.people_id from mis.call_tmp pe);
    
  commit;  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('0. PEOPLE_ER'); 
  DBMS_OUTPUT.disable; 
  delete from mis.PEOPLE_ER where 
    id   in (select pe.people_er_id from mis.call_tmp pe);
  commit;
   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('1. ADDITIONAL_CALL 2');
  DBMS_OUTPUT.disable;
  delete from mis.ADDITIONAL_CALL a where a.call_id  in (select pe.call_id from mis.call_tmp pe);
  commit;  
   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('2. policlinic_call 2');
  DBMS_OUTPUT.disable;
  delete from mis.policlinic_call p where p.call_id  in (select pe.call_id from mis.call_tmp pe);
  commit;
    
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('3. team_emp_schedule 2');
  DBMS_OUTPUT.disable;
  delete from mis.team_emp_schedule t where t.call_id  in (select pe.call_id from mis.call_tmp pe);
  commit;
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('4. call_survey');
  DBMS_OUTPUT.disable;


  delete from mis.call_survey cs where cs.team_call_id  in 
    (select tc.id 
     from mis.team_call tc, mis.call_tmp ec
     where tc.call_id = ec.call_id       ) ;  
  commit;  
   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('5. team_state 2');
  DBMS_OUTPUT.disable;
  delete from mis.team_state ts where ts.team_call_id  in 
    (select tc.id 
     from mis.team_call tc, mis.call_tmp ec
     where tc.call_id = ec.call_id     ) ;  
  commit;  
  
  
  DBMS_OUTPUT.enable; 
  DBMS_OUTPUT.PUT_LINE ('6. team_call 1'); 
  DBMS_OUTPUT.disable;
  update mis.team_call set station_call_id = null
  where call_id in (select pe.call_id from mis.call_tmp pe);
  commit;  
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('7. team_call 2');   
  DBMS_OUTPUT.disable; 
  delete from mis.team_call tc where tc.call_id in (select pe.call_id from mis.call_tmp pe);
  commit;  
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('8. team_call 3'); 
  DBMS_OUTPUT.disable; 
  delete from mis.team_call tc where tc.station_call_id in 
     (select sc.id 
     from mis.station_call sc, mis.call_tmp ec
     where sc.call_id = ec.call_id     ) ;       
  commit; 
  
  DBMS_OUTPUT.enable;   
  DBMS_OUTPUT.PUT_LINE ('9. team_schedule ');  
  DBMS_OUTPUT.disable; 

  delete from mis.team_schedule tt where tt.day_date  < to_date('02.01.2016','dd.mm.yyyy'); 
  
  commit;    
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('10. station_call'); 
  DBMS_OUTPUT.disable; 
  delete from mis.station_call sc where sc.call_id in (select pe.call_id from mis.call_tmp pe);
  commit;   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('11. call_addr');   
  DBMS_OUTPUT.disable;     
  delete from mis.call_addr ca where ca.call_id in (select pe.call_id from mis.call_tmp pe);
  commit;     
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('12. call_inform');    
  DBMS_OUTPUT.disable; 
  delete from mis.call_inform where call_id in (select pe.call_id from mis.call_tmp pe); 
  commit;   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('13. call_diag');  
  DBMS_OUTPUT.disable; 
  delete from mis.call_diag where call_id in (select pe.call_id from mis.call_tmp pe);     
  commit;   
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('14. call_treatment');  
  DBMS_OUTPUT.disable;
  delete from mis.call_treatment where call_id in (select pe.call_id from mis.call_tmp pe);   
  commit;  
  
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('15. emergency_call 1');  
  DBMS_OUTPUT.disable;
  delete from mis.emergency_call ec where ec.id in (select pe.call_id from mis.call_tmp pe);
  commit;
  
 
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.PUT_LINE ('22. конец');  
  DBMS_OUTPUT.disable; 
end;  
</source>
 
ВО ВКЛАДКЕ OUTPUT ДОЛЖНО БЫТЬ от - 2 до 22 
КОНЕЦ ЧИСТКИ 

Удаляем временную таблицу
<source>
truncate TABLE  mis.call_tmp ;
drop TABLE  mis.call_tmp ;
</source>


проверяем количество записей в буфере
должно совпасть с тем количеством которе было в начале

<source>
select * from mis.buffer_transfer 
</source>

  чистка логов

<source>
truncate table mis_log.user_info_log;
truncate table  mis_log.tuser_msg_log;

ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i SHRINK SPACE;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r SHRINK SPACE;
ALTER TABLE mis_log.tuser_msg_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tuser_msg_log SHRINK SPACE;
ALTER TABLE mis_log.emergency_call_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source SHRINK SPACE;
ALTER TABLE mis_log.user_info_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.user_info_log SHRINK SPACE;

ALTER INDEX mis_log.pk_tuser_action_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.dr$idx_emergency_call_log_ft$x REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_id REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_user_info_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tmsg_source REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tuser_msg_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.ix_user_info_log$key_tbl_name REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_log_side REBUILD  NOLOGGING;

ALTER TABLESPACE log_data COALESCE;
ALTER TABLESPACE log_idx COALESCE;
</source>
 
Перемещение таблиц
 
<source>
truncate table  mis.JOB_RESTART_LOG;
ALTER TABLE  mis.medication  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.EMP  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.GROUPS  move tablespace USR_DATA;
ALTER TABLE  mis.GROUP_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.MIS_USER  move tablespace USR_DATA;
ALTER TABLE  mis.MIS_USER_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.MIS_USER_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.POST_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.POST_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_SUBST  move tablespace USR_DATA;
ALTER TABLE  mis.PEOPLE  move tablespace USR_DATA;
ALTER TABLE  mis.DICTIONARY  move tablespace USR_DATA;
ALTER TABLE  mis.DOAMESSAGE  move tablespace USR_DATA;
ALTER TABLE  mis.AUDIT_ACTIONS  move tablespace USR_DATA;
ALTER TABLE  mis.DICTIONARY_COLUMN  move tablespace USR_DATA;
ALTER TABLE  mis.MISFILE  move tablespace USR_DATA;
ALTER TABLE  mis.RIGHT_MISFILE  move tablespace USR_DATA;
ALTER TABLE  mis.KLADR  move tablespace USR_DATA;
ALTER TABLE  mis.DICTIONARY_RIGHT  move tablespace USR_DATA;
ALTER TABLE  mis.MDOC  move tablespace USR_DATA;
ALTER TABLE  mis.MDOC_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT_MDOC_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT_COMP  move tablespace USR_DATA;
ALTER TABLE  mis.POST_COMP  move tablespace USR_DATA;
ALTER TABLE  mis.COMPANY  move tablespace USR_DATA;
ALTER TABLE  mis.MDICT  move tablespace USR_DATA;
ALTER TABLE  mis.MDICT_CONF  move tablespace USR_DATA;
ALTER TABLE  mis.SYS_PROTOCOL  move tablespace USR_DATA;
ALTER TABLE  mis.USER_PROTOCOL  move tablespace USR_DATA;
ALTER TABLE  mis.SERVPLACE  move tablespace USR_DATA;
ALTER TABLE  mis.AMBTICK_STATUS  move tablespace USR_DATA;
ALTER TABLE  mis.IDOC  move tablespace USR_DATA;
ALTER TABLE  mis.AMBTICKET  move tablespace USR_DATA;
ALTER TABLE  mis.VISIT  move tablespace USR_DATA;
ALTER TABLE  mis.TICK_RES  move tablespace USR_DATA;
ALTER TABLE  mis.LOG_ERRORS_TAB  move tablespace USR_DATA;
ALTER TABLE  mis.ADJ  move tablespace USR_DATA;
ALTER TABLE  mis.NUM_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.NAME  move tablespace USR_DATA;
ALTER TABLE  mis.PATRONYMIC  move tablespace USR_DATA;
ALTER TABLE  mis.REPORT  move tablespace USR_DATA;
ALTER TABLE  mis.PDOC  move tablespace USR_DATA;
ALTER TABLE  mis.DEPT_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.GROUP_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.POST_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.CABINET  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_CABINET  move tablespace USR_DATA;
ALTER TABLE  mis.MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.PRESC  move tablespace USR_DATA;
ALTER TABLE  mis.PRESC_STATE  move tablespace USR_DATA;
ALTER TABLE  mis.SHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.SHED_MAC  move tablespace USR_DATA;
ALTER TABLE  mis.MAC_CAB  move tablespace USR_DATA;
ALTER TABLE  mis.RESULT  move tablespace USR_DATA;
ALTER TABLE  mis.MFOLDER  move tablespace USR_DATA;
ALTER TABLE  mis.TST_SHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.MPARAM  move tablespace USR_DATA;
ALTER TABLE  mis.CONST  move tablespace USR_DATA;
ALTER TABLE  mis.SOCIALRANK  move tablespace USR_DATA;
ALTER TABLE  mis.LIVEPLACE  move tablespace USR_DATA;
ALTER TABLE  mis.REGPLACE  move tablespace USR_DATA;
ALTER TABLE  mis.INVALIDKIND  move tablespace USR_DATA;
ALTER TABLE  mis.INVALIDGROUP  move tablespace USR_DATA;
ALTER TABLE  mis.FAVORKIND  move tablespace USR_DATA;
ALTER TABLE  mis.DISPGROUP  move tablespace USR_DATA;
ALTER TABLE  mis.COMP_COMPTYPE  move tablespace USR_DATA;
ALTER TABLE  mis.COMPANYTYPE  move tablespace USR_DATA;
ALTER TABLE  mis.SEC_JOB  move tablespace USR_DATA;

ALTER TABLE  mis.VISIT_ADDR  move tablespace USR_DATA;
ALTER TABLE  mis.COMPANY_ADDR  move tablespace USR_DATA;
ALTER TABLE  mis.ADDR_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_DATA  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_MARKER  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_PARAM  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_PRINT  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_PROGRAMMER  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_SIGNATURE  move tablespace USR_DATA;
ALTER TABLE  mis.PAY_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.IDOC_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.VISIT_A  move tablespace USR_DATA;
ALTER TABLE  mis.IDOC_A  move tablespace USR_DATA;
ALTER TABLE  mis.PLGCOMMAND  move tablespace USR_DATA;
ALTER TABLE  mis.PROTOCOL_TEMPLATE_DEF  move tablespace USR_DATA;
ALTER TABLE  mis.RIGHT_PLGCOMMAND  move tablespace USR_DATA;
ALTER TABLE  mis.MDICT_PARAM  move tablespace USR_DATA;
ALTER TABLE  mis.PRESC_TIME  move tablespace USR_DATA;
ALTER TABLE  mis.TYPE_NAME  move tablespace USR_DATA;
ALTER TABLE  mis.PINFO  move tablespace USR_DATA;
ALTER TABLE  mis.PARAM_NAME  move tablespace USR_DATA;
ALTER TABLE  mis.PRESC_PARVAL  move tablespace USR_DATA;
ALTER TABLE  mis.MAC_PAR  move tablespace USR_DATA;
ALTER TABLE  mis.ADDR  move tablespace USR_DATA;
ALTER TABLE  mis.SICKLIST  move tablespace USR_DATA;
ALTER TABLE  mis.SICKLIST_PERIOD  move tablespace USR_DATA;
ALTER TABLE  mis.SICKLIST_REASON  move tablespace USR_DATA;
ALTER TABLE  mis.PEOPLE_ADDR  move tablespace USR_DATA;
ALTER TABLE  mis.SICKLIST_REGIMEN  move tablespace USR_DATA;
ALTER TABLE  mis.ADDR_A  move tablespace USR_DATA;
ALTER TABLE  mis.DISTRICT  move tablespace USR_DATA;
ALTER TABLE  mis.DISTRICT_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.SC_VISIT_KIND  move tablespace USR_DATA;
ALTER TABLE  mis.HOLIDAY  move tablespace USR_DATA;
ALTER TABLE  mis.WORKDAY  move tablespace USR_DATA;
ALTER TABLE  mis.DISTRICT_DIAPAZON  move tablespace USR_DATA;
ALTER TABLE  mis.REPORT_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.DISTRICT_HOUSE  move tablespace USR_DATA;
ALTER TABLE  mis.MR_ACTION  move tablespace USR_DATA;
ALTER TABLE  mis.MR_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.MR_SECTION  move tablespace USR_DATA;
ALTER TABLE  mis.DISTRICT_EMP  move tablespace USR_DATA;
ALTER TABLE  mis.SCHE_TEMPLATE  move tablespace USR_DATA;
ALTER TABLE  mis.SCHE_TEMPLATE_DATA  move tablespace USR_DATA;
ALTER TABLE  mis.TOMP_RAZDEL  move tablespace USR_DATA;
ALTER TABLE  mis.TOMP  move tablespace USR_DATA;
ALTER TABLE  mis.IPROGRAM  move tablespace USR_DATA;
ALTER TABLE  mis.ICONTRACT  move tablespace USR_DATA;
ALTER TABLE  mis.ICONTRACT_PAYER  move tablespace USR_DATA;
ALTER TABLE  mis.IDOC_CONTRACT  move tablespace USR_DATA;
ALTER TABLE  mis.INVOICE  move tablespace USR_DATA;
ALTER TABLE  mis.MSERVICE  move tablespace USR_DATA;
ALTER TABLE  mis.MSERVICE_PRICE  move tablespace USR_DATA;
ALTER TABLE  mis.INVOICE_MSERVICE  move tablespace USR_DATA;
ALTER TABLE  mis.INVOICE_PAYMENT  move tablespace USR_DATA;
ALTER TABLE  mis.MSERVICE_MACTION  move tablespace USR_DATA;
ALTER TABLE  mis.TOMP_PRICE  move tablespace USR_DATA;
ALTER TABLE  mis.INVOICE_TOMP  move tablespace USR_DATA;
ALTER TABLE  mis.MR_SECTION_RW_DATA  move tablespace USR_DATA;
ALTER TABLE  mis.MR_SECTION_CHANGE  move tablespace USR_DATA;
ALTER TABLE  mis.SIGN_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.MR_PRESC_COUNT  move tablespace USR_DATA;
ALTER TABLE  mis.LIST_SQL_PARAM  move tablespace USR_DATA;
ALTER TABLE  mis.GROUP_LIST  move tablespace USR_DATA;
ALTER TABLE  mis.LGCOMMAND  move tablespace USR_DATA;
ALTER TABLE  mis.LGROUP  move tablespace USR_DATA;
ALTER TABLE  mis.LGROUP_COMMAND  move tablespace USR_DATA;
ALTER TABLE  mis.LCOLUMN  move tablespace USR_DATA;
ALTER TABLE  mis.LIST_COLUMN  move tablespace USR_DATA;
ALTER TABLE  mis.LFILTER_ITEMS  move tablespace USR_DATA;
ALTER TABLE  mis.TITLE_PAGE_TEMPLATE  move tablespace USR_DATA;
ALTER TABLE  mis.ASSENT  move tablespace USR_DATA;
ALTER TABLE  mis.MASK_PEOPLE  move tablespace USR_DATA;
ALTER TABLE  mis.DISEASE_CHARACTER  move tablespace USR_DATA;
ALTER TABLE  mis.AUDIT_PRIVILEGES  move tablespace USR_DATA;
ALTER TABLE  mis.PRESC_FUNC  move tablespace USR_DATA;
ALTER TABLE  mis.MALLOW  move tablespace USR_DATA;
ALTER TABLE  mis.DIAG_KIND  move tablespace USR_DATA;
ALTER TABLE  mis.DISP_STEP  move tablespace USR_DATA;
ALTER TABLE  mis.DISP_STAGE  move tablespace USR_DATA;
ALTER TABLE  mis.DIAG_DETECT  move tablespace USR_DATA;
ALTER TABLE  mis.DIAG_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.DIAG_TEMPLATE  move tablespace USR_DATA;
ALTER TABLE  mis.MACTION_PF  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_REASON  move tablespace USR_DATA;
ALTER TABLE  mis.PASS_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.PEOPLE_ER  move tablespace USR_DATA;
ALTER TABLE  mis.PLACE_FROM  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_CALL_DELIVER  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_EMP  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_CALL_UPLOAD  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.STATION  move tablespace USR_DATA;
ALTER TABLE  mis.UPLOAD_DBF  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_PASS_STATE  move tablespace USR_DATA;
truncate TABLE mis_log.TUSER_ACTION_LOG ;
ALTER TABLE  mis_log.TUSER_ACTION_LOG  move tablespace USR_DATA;
ALTER TABLE  mis.EMERGENCY_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_SCHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_STATE  move tablespace USR_DATA;
ALTER TABLE  mis.STATION_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.BUFFER_TRANSFER  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_EMP_SCHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_START_PARAM  move tablespace USR_DATA;
ALTER TABLE  mis.MR_SECTION_DATA  move tablespace USR_DATA;
ALTER TABLE  mis.VISIT_SIGN  move tablespace USR_DATA;
ALTER TABLE  mis.MESSAGE  move tablespace USR_DATA;
ALTER TABLE  mis.PROTOCOL_SIGN  move tablespace USR_DATA;
ALTER TABLE  mis.LIST  move tablespace USR_DATA;
ALTER TABLE  mis.BUFFER_TRANSFER_LOG  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_TREATMENT  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_INFORM  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_DIAG  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_SURVEY  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_ADDR  move tablespace USR_DATA;
ALTER TABLE  mis.MEDICATION  move tablespace USR_DATA;
ALTER TABLE  mis.ICD10_DESC  move tablespace USR_DATA;
ALTER TABLE  mis.JOB_RESTART_LOG  move tablespace USR_DATA;
ALTER TABLE  mis.BUFFER_TRANSFER_ERRLOG  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_EMP_SCHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.POLICLINIC  move tablespace USR_DATA;
ALTER TABLE  mis.POLICLINIC_CALL  move tablespace USR_DATA;


truncate TABLE  mis.TOAD_PLAN_TABLE  ;

ALTER TABLE  mis.TEST  move tablespace USR_DATA;
ALTER TABLE  mis.TEST_LOG  move tablespace USR_DATA;
ALTER TABLE  mis.RESULT_TEAM_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.DIAG  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_SOUND  move tablespace USR_DATA;
ALTER TABLE  mis.TEAM_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.ADDITIONAL_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.STATION_LINK_LOG  move tablespace USR_DATA;
ALTER TABLE  mis.OKATO  move tablespace USR_DATA;
ALTER TABLE  mis.REASON_HELP  move tablespace USR_DATA;
ALTER TABLE  mis.ICD10  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_CANCEL_REASON  move tablespace USR_DATA;
ALTER TABLE  mis.STATION_LINK  move tablespace USR_DATA;
ALTER TABLE  mis.REFUSE_REASON  move tablespace USR_DATA;
ALTER TABLE  mis.TREATMENT  move tablespace USR_DATA;
ALTER TABLE  mis.INFORM_PLACE  move tablespace USR_DATA;
ALTER TABLE  mis.KLADR_BACKUP  move tablespace USR_DATA;
ALTER TABLE  mis.LOG_DROP_ME  move tablespace USR_DATA;
ALTER TABLE  mis.INTRODUCTION  move tablespace USR_DATA;
ALTER TABLE  mis.ADDR_TEST  move tablespace USR_DATA;
ALTER TABLE  mis.ACCIDENT  move tablespace USR_DATA;
ALTER TABLE  mis.MESSAGE_TYPE  move tablespace USR_DATA;
ALTER TABLE  mis.RER_NAZ_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.REP_NAZ_GROUP_ICD  move tablespace USR_DATA;
ALTER TABLE  mis.CALL_REP_NAZ_GROUP  move tablespace USR_DATA;
ALTER TABLE  mis.EMP_SCHEDULE  move tablespace USR_DATA;
ALTER TABLE  mis.STATION_DEPT  move tablespace USR_DATA;
ALTER TABLE  mis.SPRECORD_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.COMPANY_CALL  move tablespace USR_DATA;
ALTER TABLE  mis.COMPANY_INSUR  move tablespace USR_DATA;
ALTER TABLE  mis.PAKAGE_DATA  move tablespace USR_DATA;
ALTER TABLE  mis.PAKAGE_LIST  move tablespace USR_DATA;
ALTER TABLE  mis.EMERGENCY_CALL_INSUR  move tablespace USR_DATA;
ALTER TABLE  mis.ERROR_CLOB  move tablespace USR_DATA;
ALTER TABLE  mis.STATION_EC_TMP  move tablespace USR_DATA;
ALTER TABLE  mis.EINSUR_01  move tablespace USR_DATA;
ALTER TABLE  mis.PEOPLE_INSUR  move tablespace USR_DATA;
ALTER TABLE  mis.SAVE_TEAM_STATE_20140409  move tablespace USR_DATA;
ALTER TABLE  mis.SAVE_TEAM_SCHEDULE_20140409  move tablespace USR_DATA;
ALTER TABLE  mis.MISFILE  move tablespace USR_DATA;
ALTER TABLE  mis.PROTOCOL_TEMPLATE  move tablespace USR_DATA;
ALTER TABLE  mis.PROTOCOL  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_SQL  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_TABLE  move tablespace USR_DATA;
ALTER TABLE  mis.MDS_FORM  move tablespace USR_DATA;
</source>


Пересобираем ИНДЕКСЫ
ошибки можно игнорироватью

<source>
ALTER INDEX mis.IX_EMERGENCY_CALL$ACCIDENT_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$ADDR_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$CALL_TYPE_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$PEOPLE_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$PLACEFROM_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$REASON_ID REBUILD;
ALTER INDEX mis.IX_EMERGENCY_CALL$REGIST_DATE REBUILD;
ALTER INDEX mis.IX_ER_CALL$SREFUSE_REASON REBUILD;
ALTER INDEX mis.PK_EMERGENCY_CALL REBUILD;

ALTER INDEX mis.IX_EMERGENCY_CALL$ACCIDENT_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$ADDR_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$CALL_TYPE_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$PEOPLE_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$PLACEFROM_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$REASON_ID shrink space;
ALTER INDEX mis.IX_EMERGENCY_CALL$REGIST_DATE shrink space;
ALTER INDEX mis.IX_ER_CALL$SREFUSE_REASON shrink space;
ALTER INDEX mis.PK_EMERGENCY_CALL shrink space;


ALTER INDEX mis.IX_CALL_TREATMENT$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_TREATMENT$CALL_ID  shrink space; 
ALTER INDEX mis.IX_CALL_TREATMENT$TREATMENT_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_TREATMENT$TREATMENT_ID  shrink space; 
ALTER INDEX mis.PK_CALL_TREATMENT  REBUILD; 
ALTER INDEX mis.PK_CALL_TREATMENT  shrink space; 



ALTER INDEX mis.IX_CALL_INFORM$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_INFORM$CALL_ID  shrink space; 
ALTER INDEX mis.IX_CALL_INFORM$INFORM_PLACE_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_INFORM$INFORM_PLACE_ID  shrink space; 
ALTER INDEX mis.PK_CALL_INFORM  REBUILD; 
ALTER INDEX mis.PK_CALL_INFORM  shrink space; 


ALTER INDEX mis.IX_CALL_ADDR$ADDR_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_ADDR$ADDR_ID  shrink space; 

ALTER INDEX mis.PK_CALL_ADDR  REBUILD; 
ALTER INDEX mis.PK_CALL_ADDR  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$CALL_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$CANCEL_REASON_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$CANCEL_REASON_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$CLOSE_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$CLOSE_EMP_ID  shrink space; 


ALTER INDEX mis.IX_TEAM_CALL$GOSP_COMPANY_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$GOSP_COMPANY_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$PASS_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$PASS_EMP_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$PASS_TYPE_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$PASS_TYPE_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$RESULT_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$RESULT_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$SHD_ID$TEAM_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$SHD_ID$TEAM_ID  shrink space; 


ALTER INDEX mis.IX_TEAM_CALL$STATION_CALL_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$STATION_CALL_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_CALL$TEAM_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_CALL$TEAM_ID  shrink space; 

ALTER INDEX mis.IX_TEMA_CALL$PASS_DATE  REBUILD; 
ALTER INDEX mis.IX_TEMA_CALL$PASS_DATE  shrink space; 

ALTER INDEX mis.PK_TEAM_CALL  REBUILD; 
ALTER INDEX mis.PK_TEAM_CALL  shrink space; 

ALTER INDEX mis.IX_TEAMS_CHEDULE$DAY_DATE$  REBUILD; 
ALTER INDEX mis.IX_TEAMS_CHEDULE$DAY_DATE$  shrink space; 


ALTER INDEX mis.IX_TEAM_SCHEDULE$SHIFT_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_SCHEDULE$SHIFT_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_SHEDULE$TEAM_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_SHEDULE$TEAM_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_SHEDULE$TYPE_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_SHEDULE$TYPE_ID  shrink space; 

ALTER INDEX mis.PK_TEAM_SCHEDULE  REBUILD; 
ALTER INDEX mis.PK_TEAM_SCHEDULE  shrink space; 

ALTER INDEX mis.IX_STATION_CALL$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$CALL_ID  shrink space; 


ALTER INDEX mis.IX_STATION_CALL$CANCEL_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$CANCEL_EMP_ID  shrink space; 

ALTER INDEX mis.IX_STATION_CALL$GET_DATE  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$GET_DATE  shrink space; 

ALTER INDEX mis.IX_STATION_CALL$PASS_DATE  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$PASS_DATE  shrink space; 

ALTER INDEX mis.IX_STATION_CALL$PASS_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$PASS_EMP_ID  shrink space; 

ALTER INDEX mis.IX_STATION_CALL$STATION_ID  REBUILD; 
ALTER INDEX mis.IX_STATION_CALL$STATION_ID shrink space; 


ALTER INDEX mis.PK_STATION_CALL  REBUILD; 
ALTER INDEX mis.PK_STATION_CALL  shrink space; 

ALTER INDEX mis.IX_TEAM_STATE$DATE_BEG  REBUILD; 
ALTER INDEX mis.IX_TEAM_STATE$DATE_BEG  shrink space; 

ALTER INDEX mis.IX_TEAM_STATE$STATE_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_STATE$STATE_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_STATE$TEAM_CALL_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_STATE$TEAM_CALL_ID  shrink space; 

ALTER INDEX mis.IX_TEAM_STATE$TEAM_ID  REBUILD; 
ALTER INDEX mis.IX_TEAM_STATE$TEAM_ID  shrink space; 

ALTER INDEX mis.PK_TEAM_STATE  REBUILD; 
ALTER INDEX mis.PK_TEAM_STATE shrink space; 

ALTER INDEX mis.IX_CALL_SURVEY$MDICT_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_SURVEY$MDICT_ID  shrink space; 

ALTER INDEX mis.IX_CALL_SURVEY$TEAM_CALL_ID  REBUILD; 
ALTER INDEX mis.IX_CALL_SURVEY$TEAM_CALL_ID  shrink space; 

ALTER INDEX mis.PK_CALL_SURVEY  REBUILD; 
ALTER INDEX mis.PK_CALL_SURVEY shrink space; 

ALTER INDEX mis.IX_POLICLINIC_CALL$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_POLICLINIC_CALL$CALL_ID  shrink space; 

ALTER INDEX mis.IX_POLICLINIC_CALL$PASS_DATE  REBUILD; 
ALTER INDEX mis.IX_POLICLINIC_CALL$PASS_DATE  shrink space; 

ALTER INDEX mis.IX_POLICLINIC_CALL$PASS_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_POLICLINIC_CALL$PASS_EMP_ID  shrink space; 

ALTER INDEX mis.IX_POLICLINIC_CALL$P_ID  REBUILD; 
ALTER INDEX mis.IX_POLICLINIC_CALL$P_ID  shrink space; 

ALTER INDEX mis.IX_POLICLINIC_CALL$ST_CALL_ID  REBUILD; 
ALTER INDEX mis.IX_POLICLINIC_CALL$ST_CALL_ID  shrink space; 

ALTER INDEX mis.PK_POLICLINIC_CALL  REBUILD; 
ALTER INDEX mis.PK_POLICLINIC_CALL  shrink space; 

ALTER INDEX mis.IX_MESSAGE$CALL_ID  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$CALL_ID  shrink space; 

ALTER INDEX mis.IX_MESSAGE$CONFIRM_EMP_ID  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$CONFIRM_EMP_ID  shrink space; 

ALTER INDEX mis.IX_MESSAGE$CREATE_DATE  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$CREATE_DATE  shrink space; 

ALTER INDEX mis.IX_MESSAGE$STATE_ID  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$STATE_ID  shrink space; 

ALTER INDEX mis.IX_MESSAGE$STATION_ID  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$STATION_ID  shrink space; 

ALTER INDEX mis.IX_MESSAGE$TYPE_ID  REBUILD; 
ALTER INDEX mis.IX_MESSAGE$TYPE_ID  shrink space; 
ALTER INDEX mis.PK_MESSAGE  REBUILD; 
ALTER INDEX mis.PK_MESSAGE  shrink space; 
 ----------

ALTER INDEX mis.IX_PEOPLE$SURNAME REBUILD;
ALTER INDEX mis.IX_PEOPLE$SEX REBUILD;
ALTER INDEX mis.IX_PEOPLE$POST_COMP_ID REBUILD;
ALTER INDEX mis.IX_PEOPLE$COMPANY_ID  REBUILD;
ALTER INDEX mis.IX_PEOPLE$DEPT_COMP_ID REBUILD;
ALTER INDEX mis.IX_PEOPLE$NAME REBUILD;
ALTER INDEX mis.IX_PEOPLE$PATRON REBUILD;
ALTER INDEX mis.IX_PEOPLE$SYS_NC00011$ REBUILD;
ALTER INDEX mis.PK_PEOPLE REBUILD;


ALTER INDEX mis.IX_PEOPLE$SURNAME shrink space ;
ALTER INDEX mis.IX_PEOPLE$SEX shrink space;
ALTER INDEX mis.IX_PEOPLE$POST_COMP_ID shrink space;
ALTER INDEX mis.IX_PEOPLE$COMPANY_ID  shrink space;
ALTER INDEX mis.IX_PEOPLE$DEPT_COMP_ID shrink space;
ALTER INDEX mis.IX_PEOPLE$NAME shrink space;
ALTER INDEX mis.IX_PEOPLE$PATRON shrink space;
ALTER INDEX mis.IX_PEOPLE$SYS_NC00011$ shrink space;
ALTER INDEX mis.PK_PEOPLE shrink space;

ALTER INDEX mis.IX_PEOPLE_ER$NAME  REBUILD;
ALTER INDEX mis.IX_PEOPLE_ER$NAME shrink space ;

ALTER INDEX  mis.IX_PEOPLE_ER$PEOPLE_ID REBUILD;
ALTER INDEX mis.IX_PEOPLE_ER$PEOPLE_ID  shrink space ;

ALTER INDEX mis.IX_PEOPLE_ER$PATRON   REBUILD;
ALTER INDEX mis.IX_PEOPLE_ER$PATRON   shrink space ;

ALTER INDEX mis.IX_PEOPLE_ER$SURNAME  REBUILD;
ALTER INDEX  mis.IX_PEOPLE_ER$SURNAME shrink space ;

ALTER INDEX  mis.PK_PEOPLE_ER REBUILD;
ALTER INDEX  mis.PK_PEOPLE_ER shrink space ; 

ALTER INDEX  mis.IX_PEOPLE_ADDR$ADDR_ID REBUILD;
ALTER INDEX  mis.IX_PEOPLE_ADDR$ADDR_ID shrink space ; 

ALTER INDEX  mis.IX_PEOPLE_ADDR$PEOPLE_ID REBUILD;
ALTER INDEX  mis.IX_PEOPLE_ADDR$PEOPLE_ID shrink space ; 

ALTER INDEX  mis.PK_PEOPLE_ADDR REBUILD;
ALTER INDEX  mis.PK_PEOPLE_ADDR shrink space ; 
</source>

 Можно дополнительно почистить статиску и awr
 Внимание лучше использовать удаление снапшотов через системную функцию
<source>
-- Эти скрипты использовал, т.к. не смог почистить снапшоты через системную функцию
truncate table WRH$_SQL_BIND_METADATA
truncate table WRH$_SQL_BIND_METADATA
truncate table WRH$_SQL_PLAN
truncate table WRH$_ACTIVE_SESSION_HISTORY
truncate table WRI$_OPTSTAT_HISTGRM_HISTORY
</source>

Уще Индексы

<source>
alter index DBMS_UPG_CAT_C0$_IDX2  rebuild ;
alter index DBMS_UPG_CAT_CS$_IDX2  rebuild ;
alter index DBMS_UPG_CAT_CT$_IDX2  rebuild ;
alter index DBMS_UPG_CHANGE$_IDX1  rebuild ;
alter index WRH$_DB_CACHE_ADVICE_BL_PK  rebuild ;
alter index WRH$_JAVA_POOL_ADVICE_PK  rebuild ;
alter index WRH$_PGA_TARGET_ADVICE_PK  rebuild ;
alter index WRH$_SGA_TARGET_ADVICE_PK  rebuild ;
alter index WRH$_SHARED_POOL_ADVICE_PK  rebuild ;
alter index WRH$_STREAMS_POOL_ADVICE_PK  rebuild ;
alter index WRI$_ADV_ACTIONS_PK  rebuild ;
alter index WRI$_ADV_DEFINITIONS_PK  rebuild ;
alter index WRI$_ADV_DEF_PARAMETERS_PK  rebuild ;
alter index WRI$_ADV_DIRECTIVES  rebuild ;
alter index WRI$_ADV_FINDINGS_PK  rebuild ;
alter index WRI$_ADV_JOURNAL  rebuild ;
alter index WRI$_ADV_MESSAGE_GROUPS_PK  rebuild ;
alter index WRI$_ADV_MSG_GRPS_IDX_01  rebuild ;
alter index WRI$_ADV_OBJECTS_PK  rebuild ;
alter index WRI$_ADV_PARAMETERS_PK  rebuild ;
alter index WRI$_ADV_RATIONALE_PK  rebuild ;
alter index WRI$_ADV_REC_ACTIONS_PK  rebuild ;
alter index WRI$_ADV_REC_PK  rebuild ;
alter index WRI$_ADV_SQLA_FREG_IDX_01  rebuild ;
alter index WRI$_ADV_SQLA_MAP_01  rebuild ;
alter index WRI$_ADV_SQLA_MAP_02  rebuild ;
alter index WRI$_ADV_SQLA_STMTS_IDX_01  rebuild ;
alter index WRI$_ADV_SQLA_STMTS_IDX_02  rebuild ;
alter index WRI$_ADV_SQLA_TMP_PK  rebuild ;
alter index WRI$_ADV_SQLT_BINDS_PK  rebuild ;
alter index WRI$_ADV_SQLT_PLANS_PK  rebuild ;
alter index WRI$_ADV_SQLT_RTN_PLAN_PK  rebuild ;
alter index WRI$_ADV_SQLT_STATISTICS_PK  rebuild ;
alter index WRI$_ADV_SQLW_CV_PK  rebuild ;
alter index WRI$_ADV_SQLW_STMTS_PK  rebuild ;
alter index WRI$_ADV_SQLW_SUM_PK  rebuild ;
alter index WRI$_ADV_SQLW_TABLES_IDX_01  rebuild ;
alter index WRI$_ADV_SQLW_TV_PK  rebuild ;
alter index WRI$_ADV_TASKS_IDX_01  rebuild ;
alter index WRI$_ADV_TASKS_IDX_02  rebuild ;
alter index WRI$_ADV_TASKS_IDX_03  rebuild ;
alter index WRI$_ADV_TASKS_PK  rebuild ;
alter index WRI$_SEGADV_CNTRLTAB_PK  rebuild ;
alter table DBMS_ALERT_INFO  move tablespace SYSTEM;
alter table DBMS_LOCK_ALLOCATED  move tablespace SYSTEM;
alter table DBMS_UPG_ACTION_QUEUE  move tablespace SYSTEM;
alter table DBMS_UPG_CHANGE$  move tablespace SYSTEM;
alter table DBMS_UPG_CON_MAPPING  move tablespace SYSTEM;
alter table DBMS_UPG_DEBUG  move tablespace SYSTEM;
alter table DBMS_UPG_LOG$  move tablespace SYSTEM;
alter table DBMS_UPG_OBJAUTH_C0$  move tablespace SYSTEM;
alter table DBMS_UPG_OBJAUTH_CS$  move tablespace SYSTEM;
alter table DBMS_UPG_OBJAUTH_CT$  move tablespace SYSTEM;
alter table DBMS_UPG_OBJECT$  move tablespace SYSTEM;
alter table DBMS_UPG_RLS_C0$  move tablespace SYSTEM;
alter table DBMS_UPG_RLS_CS$  move tablespace SYSTEM;
alter table DBMS_UPG_RLS_CT$  move tablespace SYSTEM;
alter table DBMS_UPG_STATUS$  move tablespace SYSTEM;
alter table DBMS_UPG_SYSAUTH_C0$  move tablespace SYSTEM;
alter table DBMS_UPG_SYSAUTH_CS$  move tablespace SYSTEM;
alter table DBMS_UPG_SYSAUTH_CT$  move tablespace SYSTEM;
alter table WRH$_DB_CACHE_ADVICE_BL  move tablespace SYSAUX;
alter table WRH$_JAVA_POOL_ADVICE  move tablespace SYSAUX;
alter table WRH$_MTTR_TARGET_ADVICE  move tablespace SYSAUX;
alter table WRH$_PGA_TARGET_ADVICE  move tablespace SYSAUX;
alter table WRH$_SGA_TARGET_ADVICE  move tablespace SYSAUX;
alter table WRH$_SHARED_POOL_ADVICE  move tablespace SYSAUX;
alter table WRH$_STREAMS_POOL_ADVICE  move tablespace SYSAUX;
alter table WRI$_ADV_ACTIONS  move tablespace SYSAUX;
alter table WRI$_ADV_DEFINITIONS  move tablespace SYSAUX;
alter table WRI$_ADV_DEF_PARAMETERS  move tablespace SYSAUX;
alter table WRI$_ADV_DIRECTIVES  move tablespace SYSAUX;
alter table WRI$_ADV_FINDINGS  move tablespace SYSAUX;
alter table WRI$_ADV_JOURNAL  move tablespace SYSAUX;
alter table WRI$_ADV_MESSAGE_GROUPS  move tablespace SYSAUX;
alter table WRI$_ADV_OBJECTS  move tablespace SYSAUX;
alter table WRI$_ADV_PARAMETERS  move tablespace SYSAUX;
alter table WRI$_ADV_RATIONALE  move tablespace SYSAUX;
alter table WRI$_ADV_RECOMMENDATIONS  move tablespace SYSAUX;
alter table WRI$_ADV_REC_ACTIONS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLA_FAKE_REG  move tablespace SYSAUX;
alter table WRI$_ADV_SQLA_MAP  move tablespace SYSAUX;
alter table WRI$_ADV_SQLA_STMTS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLA_TMP  move tablespace SYSAUX;
alter table WRI$_ADV_SQLT_BINDS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLT_PLANS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLT_RTN_PLAN  move tablespace SYSAUX;
alter table WRI$_ADV_SQLT_STATISTICS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLW_COLVOL  move tablespace SYSAUX;
alter table WRI$_ADV_SQLW_STMTS  move tablespace SYSAUX;
alter table WRI$_ADV_SQLW_SUM  move tablespace SYSAUX;
alter table WRI$_ADV_SQLW_TABLES  move tablespace SYSAUX;
alter table WRI$_ADV_SQLW_TABVOL  move tablespace SYSAUX;
alter table WRI$_ADV_TASKS  move tablespace SYSAUX;
alter table WRI$_ADV_USAGE  move tablespace SYSAUX;
alter table WRI$_SEGADV_CNTRLTAB  move tablespace SYSAUX;
alter table WRI$_SEGADV_OBJLIST  move tablespace SYSAUX;


alter index SYS_C001304  rebuild ;
alter index SYS_C001305  rebuild ;
</source>


Поиск инвалидных индексов и построение скриптов для ребилда
<source>
select ' ALTER INDEX '||owner||'.'|| index_name||' REBUILD;' from all_indexes where status <> 'VALID' 
</source>
 
ДВИГАЕМ ЛОБЫ

<source>
ALTER TABLE mis.MDS_FORM MODIFY LOB(VIEW_SOURCE) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_FORM MODIFY LOB(PRINT_TEMPL) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_FORM MODIFY LOB(SQLEND) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_FORM MODIFY LOB(SQLBEGIN) (SHRINK SPACE CASCADE);
ALTER TABLE mis.ICD10_DESC MODIFY LOB(DES) (SHRINK SPACE CASCADE);
ALTER TABLE mis.PROTOCOL_TEMPLATE MODIFY LOB(TEXT) (SHRINK SPACE CASCADE);
ALTER TABLE mis.PROTOCOL MODIFY LOB(SIG) (SHRINK SPACE CASCADE);
ALTER TABLE mis.PROTOCOL MODIFY LOB(TEXT) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_SQL MODIFY LOB(SQL) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_TABLE MODIFY LOB(SQLEND) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_TABLE MODIFY LOB(SQLBEGIN) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MDS_START_PARAM MODIFY LOB(PARAM_SQL) (SHRINK SPACE CASCADE);
ALTER TABLE mis.MR_SECTION_DATA MODIFY LOB(TEXT) (SHRINK SPACE CASCADE);
ALTER TABLE mis.VISIT_SIGN MODIFY LOB(SIGN) (SHRINK SPACE CASCADE);
ALTER TABLE mis.PROTOCOL_SIGN MODIFY LOB(SIGN) (SHRINK SPACE CASCADE);
ALTER TABLE mis.LIST MODIFY LOB(FILTER) (SHRINK SPACE CASCADE);
ALTER TABLE mis.LIST MODIFY LOB(VIEW_) (SHRINK SPACE CASCADE);
ALTER TABLE mis.DIAG MODIFY LOB(BASE) (SHRINK SPACE CASCADE);
ALTER TABLE mis.CALL_SOUND MODIFY LOB(BLOB_SOUND) (SHRINK SPACE CASCADE);
ALTER TABLE mis.TOAD_PLAN_TABLE MODIFY LOB(OTHER_XML) (SHRINK SPACE CASCADE); -- здесь возможно ошибка
</source>
 Теперь можно проверить свободное место (позже будут скрипты)

Здесь сжатие БД (и без сжатия все должно быть хорошо)
Возможны два варианта сжатия:
* 1. С помощью операции «Compaсt Storage» из домашней странички БД.
* 2. С помощью скрипта запускаемого из командной строки sqlplus с правами DBA (смотри статью)

СЖАТИЕ БД

Получаем размер блока
<source>
select value from v$parameter where name = 'db_block_size'
</source>

Вставляем размер блока и выполняем
<source>
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
</source>

 
Формируем скрипты по сжатию datafile (Скопипастили результат запроса и выполнили его отделено)

<source>
select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
</source>


 ВКЛЮЧЕНИЕ РЕПЛИКАЦИИ 
 
<source>
ALTER TRIGGER MIS.PEOPLE$REPLICATION ENABLE;
ALTER TRIGGER MIS.TEAM_EMP_SCHEDULE$REPLICATION ENABLE;
ALTER TRIGGER MIS.Call_Addr$REPLICATION ENABLE;
ALTER TRIGGER MIS.policlinic_call$REPLICATION ENABLE;
ALTER TRIGGER MIS.emergency_call$REPLICATION ENABLE;
ALTER TRIGGER MIS.team_emp_schedule$REPLICATION ENABLE;
ALTER TRIGGER MIS.TEAM_SHEDULE$REPLICATION ENABLE;
ALTER TRIGGER MIS.Station_Call$REPLICATION ENABLE;
ALTER TRIGGER MIS.team_state$REPLICATION ENABLE;
ALTER TRIGGER MIS.call_inform$REPLICATION ENABLE; 
ALTER TRIGGER MIS.call_treatment$REPLICATION ENABLE; 
ALTER TRIGGER MIS.call_diag$REPLICATION ENABLE;  
ALTER TRIGGER MIS.EMERGENCY_CALL$AU_MESSAGE  ENABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL$BI ENABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL_LOG$AIUD ENABLE; 
ALTER TRIGGER MIS.EMERGENCY_CALL$REPLICATION ENABLE; 
ALTER TRIGGER MIS.TEAM_CALL$REPLICATION ENABLE; 
ALTER TRIGGER MIS.CALL_SURVEY$REPLICATION ENABLE; 
ALTER TRIGGER MIS.Additional_Call$REPLICATION ENABLE; 
ALTER TRIGGER MIS.People_Addr$REPLICATION ENABLE; 
</source>

Проверяем объекты на валидность (через девелопер проще всего).

СОЗДАЕМ ЛИНК НА ЦДС
<source>
create public database link STLINK00  connect to SYSTEM identified by pass  using 'st00';
</source>