Если нужно изменить размер файла TABLESPACE и он является у нас BIGFILE, то простая конструкция не работает:
alter database datafile '/path/datafile' resize M;
- При подключении в базе, ещё раз смотрим, что подключились правильно
set pagesize 1000
set linesize 1000
select host_name from v$instance;
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;
- Определяем до какого размера можно уменьшить datafile
select dba_data_files.file_name,
dba_data_files.file_id,
dba_data_files.tablespace_name,
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
ceil(blocks * db_block_size / 1024 / 1024) currsize,
ceil(blocks * db_block_size / 1024 / 1024) -
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from dba_data_files,
(select file_id,
max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b,
(select value db_block_size from v$parameter where name = 'db_block_size') c
where dba_data_files.tablespace_name='MY_TABLESPACE'
and dba_data_files.file_id = b.file_id(+);
Поле «smallest» покажет до какого размера можно уменьшить файл данных, а поле «savings» покажет выигрыш в размере после уменьшения.
- Создаём табличное пространство «MY_TABLESPACE_TMP»
CREATE TABLESPACE MY_TABLESPACE_TMP datafile '/oracle_data_file_location/MY_TABLESPACE_TMP.dbf' size 1024M;
- Переносим все таблицы из одного табличного пространства в другое
set serveroutput on
begin
for i in (select distinct owner,segment_name from dba_extents where segment_type='TABLE' and tablespace_name='MY_TABLESPACE')
loop
execute immediate 'alter table '||i.owner||'.'||i.segment_name||' move tablespace MY_TABLESPACE_TMP';
end loop;
end;
/
- Переносим все таблицы с LOBами из одного табличного пространства в другое
set serveroutput on
begin
for i in (select distinct owner,table_name,column_name,segment_name from dba_lobs where tablespace_name='MY_TABLESPACE')
loop
execute immediate 'alter table '||i.owner||'.'||i.table_name||' move tablespace MY_TABLESPACE_TMP '||'LOB ('||i.column_name||') store as '||i.segment_name||' (tablespace MY_TABLESPACE_TMP)';
end loop;
end;
/
- Восстанавливаем UNUSABLE индексы
set serveroutput on
begin
for i in (select owner,index_name from dba_indexes where status = ‘UNUSABLE’)
loop
execute immediate ‘alter index ‘||i.owner||’.’||i.index_name||’ rebuild’;
end loop;
end;
/
- Переносим все индексы из одного табличного пространства в другое
set serveroutput on
begin
for i in (select distinct owner,segment_name from dba_extents where segment_type='INDEX' and tablespace_name='MY_TABLESPACE')
loop
execute immediate 'alter index '||i.owner||'.'||i.segment_name||' rebuild tablespace MY_TABLESPACE_TMP';
end loop;
end;
/
- Проверяем, что всё перенесено
select count(*) from dba_extents where tablespace_name='MY_TABLESPACE';
select distinct owner,segment_name, segment_type from dba_extents where tablespace_name='MY_TABLESPACE';
select tablespace_name, table_name from dba_tables WHERE tablespace_name LIKE 'MY_TABLESPACE';
SELECT INDEX_NAME,OWNER, TABLE_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLESPACE_NAME= 'MY_TABLESPACE';
- Уменьшаем datafile путём пересоздания
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
----------------
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
Можем получить эту ошибку, если MY_TABLESPACE стоит по умолчанию.
- Если нужно, то ставим MY_TABLESPACE_TMP по умолчанию
alter database default tablespace MY_TABLESPACE_TMP;
CREATE TABLESPACE MY_TABLESPACE datafile '/oracle_data_file_location/MY_TABLESPACE.dbf' size 1024M;
- Возвращаем все таблицы обратно
set serveroutput on
begin
for i in (select distinct owner,segment_name from dba_extents where segment_type='TABLE' and tablespace_name='MY_TABLESPACE_TMP')
loop
execute immediate 'alter table '||i.owner||'.'||i.segment_name||' move tablespace MY_TABLESPACE';
end loop;
end;
/
- Возвращаем все таблицы с LOBами обратно
set serveroutput on
begin
for i in (select distinct owner,table_name,column_name,segment_name from dba_lobs where tablespace_name='MY_TABLESPACE_TMP')
loop
execute immediate 'alter table '||i.owner||'.'||i.table_name||' move tablespace MY_TABLESPACE '||'LOB ('||i.column_name||') store as '||i.segment_name||' (tablespace MY_TABLESPACE)';
end loop;
end;
/
- Восстанавливаем UNUSABLE индексы
set serveroutput on
begin
for i in (select owner,index_name from dba_indexes where status = 'UNUSABLE')
loop
execute immediate 'alter index '||i.owner||'.'||i.index_name||' rebuild';
end loop;
end;
/
- Возвращаем все индексы обратно
set serveroutput on
begin
for i in (select distinct owner,segment_name from dba_extents where segment_type='INDEX' and tablespace_name='MY_TABLESPACE_TMP')
loop
execute immediate 'alter index '||i.owner||'.'||i.segment_name||' rebuild tablespace MY_TABLESPACE';
end loop;
end;
/
- Убеждаемся, что табличное пространство больше не используется
select count(*) from dba_extents where tablespace_name='MY_TABLESPACE_TMP';
select distinct owner,segment_name, segment_type from dba_extents where tablespace_name='MY_TABLESPACE_TMP';
select tablespace_name, table_name from dba_tables WHERE tablespace_name LIKE 'MY_TABLESPACE_TMP';
- Если нужно, то ставим MY_TABLESPACE по умолчанию
alter database default tablespace MY_TABLESPACE;
- Удаляем ненужное нам более табличное пространство вместе с файлом данных
drop tablespace MY_TABLESPACE_TMP including contents and datafiles;
