Shrink Data files in Oracle

Материал из IN-TON
Перейти к: навигация, поиск

Сперва надо пожать таблицы

  1.  
  2. declare
  3. cursor tbn is select * from dba_tables where TABLESPACE_NAME = 'USERS';
  4. tabname tbn%ROWTYPE;
  5. begin
  6. open tbn;
  7. loop
  8. FETCH tbn INTO tabname;
  9. EXIT WHEN tbn%NOTFOUND;
  10. execute immediate 'ALTER TABLE ' || tabname.OWNER || '.' || tabname.TABLE_NAME || ' ENABLE ROW MOVEMENT';
  11. execute immediate 'ALTER TABLE ' || tabname.OWNER || '.' || tabname.TABLE_NAME || ' SHRINK SPACE COMPACT';
  12. execute immediate 'ALTER TABLE ' || tabname.OWNER || '.' || tabname.TABLE_NAME || ' SHRINK SPACE';
  13.  
  14. end loop;
  15. close tbn;
  16. exception
  17. when OTHERS THEN
  18. dbms_output.put_line(tabname.OWNER || '.' || tabname.TABLE_NAME);
  19. end;
  20.  

Теперь пожать тейблспейс и физические файлы

  1.  
  2. select
  3. 'ALTER DATABASE DATAFILE ''' ||
  4. file_name ||
  5. ''' RESIZE ' ||
  6. ceil ( (nvl (hwm, 1) * dbats.block_size) / 1024 / 1024) ||
  7. 'M;' shrink_datafiles
  8. from
  9. dba_data_files dbadf,
  10. (
  11. select
  12. file_id, max (block_id + blocks - 1) hwm
  13. from
  14. dba_extents
  15. group by
  16. file_id
  17. )
  18. dbafs
  19. where dbadf.file_id = dbafs.file_id(+)
  20. and ceil (blocks * dbats.block_size / 1024 / 1024) - ceil ( (nvl (hwm, 1) * dbats.block_size) / 1024 / 1024) > 0;
  21.  

Результат - набор команд для ужатия БД.