Документ создан: 16.02.2010

Табличные пространства

Создание аналогичных табличных пространств

Вывод строк создания табличных пространств, аналогичных существующим, для определённых схем

select 'create tablespace '
    || tablespace_name
    || ' datafile '''
    || regexp_replace(file_name,'E:\\ORADATA\\ONE\\','/opt/oracle/oradata/TWO/')
    || ''' size '||bytes||decode(AUTOEXTENSIBLE,'YES',' autoextend on next '||INCREMENT_BY,'')
    || ' maxsize '||decode(maxbytes,34359721984,'unlimited',maxbytes)||';'
from dba_data_files
where tablespace_name in (
   select default_tablespace ts from dba_users where username in ('SCOTT','SCOTT1')
   union
   select tablespace_name ts from dba_ts_quotas where username in ('SCOTT','SCOTT1')
   )
order by 1;

Размер табличных пространств указанных схем

select tablespace_name
     , sum(bytes)/1024/1024 mbytes 
from dba_data_files 
where tablespace_name in (select tablespace_name 
                          from dba_ts_quotas 
                          where lower(username) in ('scott','scott1')) 
group by tablespace_name;

Размер занимаемый объектами указанных схем

select owner
     , sum(bytes)/1024/1024 mbytes 
from dba_extents 
where lower(owner) in ('scott','scott1')
group by owner;

Занятое и свободное пространство табличных пространств

select a.TABLESPACE_NAME tablespace_name
     , b.BYTES total_bytes
     , a.BYTES free_bytes
     , round(a.BYTES*100/b.BYTES,2) percent_free
     , round((b.BYTES-a.BYTES)*100/b.BYTES,2) percent_used
from (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space group by TABLESPACE_NAME) a,  
     (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by a.TABLESPACE_NAME;

или

select de.tablespace_name
     , round(df.bytes/1024/1024,2) "total (mbytes)"
     , round(de.bytes/1024/1024,2) "used (mbytes)"
from (select tablespace_name, sum(bytes) bytes from dba_extents group by tablespace_name ) de
   , (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) df
where df.tablespace_name=de.tablespace_name
order by de.tablespace_name;

Изменение temporary tablespace

  1. Смотим где находится и как называется старое временное табличное пространство:
    select file_name,tablespace_name from dba_temp_files;
  2. Создаём новое временное табличное пространство:
    create temporary tablespace TEMP01 tempfile '/opt/oracle/oradata/db02/temp.dbf' size 100M autoextend on next 500M;
  3. Переназначаем всем, кто использует временное табличное по-умолчанию, временное табличное пространство:
    alter database default temporary tablespace TEMP01;
  4. Убеждаемся, что всем пользователям выставлено новое временное табличное пространство:
    select username,temporary_tablespace from dba_users;
  5. Удаляем старое временное табличное пространство:
    drop tablespace TEMP including contents and datafiles;

Уменьшение размера temporary tablespace

  1. Получаем размещение temporary files:
    select file_name from dba_temp_files;
  2. Добавляем новый temporary file рядом с существующим. Для этого путь берём из результата запроса, описанного в предыдущем пункте:
    alter tablespace temp add tempfile '/path/to/datafiles/of/instance/temp02.dbf' size 100M autoextend on next 1M maxsize 4G;

    Здесь: temp - имя temporary tablespace, которое мы модифицируем.

  3. Выходим из сессии на случай, если, вдруг, наша сессия использует temporary tablespace, и заходим снова.
  4. Удаляем tempfile из СУБД и из операционной системы:
    alter database tempfile '/path/to/datafiles/of/instance/temp01.dbf' drop including datafiles;

После таких нехитрых манипуляций, размер temporary tablespace уменьшится до размера заданного в п.2. Если удаляемый temporary файл кем-то занят, он будет удалён из СУБД и ОС по освобождении.

Старт базы данных при отсутствующем или разрушенном файле данных

Случается и такое, что файл данных по каким-то причинам отсутствует или разрушен, а база нужна. В этом случае нам поможет набор следующих действий

startup mount;
alter database datafile '/path/to/oradata/corrupted_datafile.dbf' offline drop;
alter database open;
drop tablespace ts_bonded_with_corrupted_datafile;

Вариант tablespace shrink

Суть - создаём временное (не temporary) табличное пространство, переносим туда таблицы и индексы, уменьшаем размеры файлов данных и возвращаем назад таблицы с индексами.
Не подойдёт при отсутствии достаточного свободного дискового пространства.

Основная идея и некоторые запросы позаимствованы у SUN-TEXNIKA.

  1. Создаём табличное пространство "test".
  2. Переносим все таблицы из одного табличного пространства в другое
    set serveroutput on
    begin
      for i in (select distinct owner,segment_name from dba_extents where segment_type='TABLE' and tablespace_name='TS1')
      loop
        execute immediate 'alter table '||i.owner||'.'||i.segment_name||' move tablespace test';
      end loop;
    end;
    /
  3. Переносим все таблицы с LOBами из одного табличного пространства в другое
    set serveroutput on
    begin
      for i in (select distinct owner,table_name,column_name,segment_name from dba_lobs where tablespace_name='TS1')
      loop
        execute immediate 'alter table '||i.owner||'.'||i.table_name||' move tablespace test '||'LOB ('||i.column_name||') store as '||i.segment_name||' (tablespace test)'
      end loop;
    end;
    /
  4. Восстанавливаем 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;
    /
  5. Переносим все индексы из одного табличного пространства в другое
    set serveroutput on
    begin
      for i in (select distinct owner,segment_name from dba_extents where segment_type='INDEX' and tablespace_name='TS1')
      loop
        execute immediate 'alter index '||i.owner||'.'||i.segment_name||' rebuild tablespace test';
      end loop;
    end;
    /
  6. Определяем до какого размера можно уменьшить 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='TS1'
      and dba_data_files.file_id = b.file_id(+);

    Поле "smallest" покажет до какого размера можно уменьшить файл данных, а поле "savings" покажет выигрыш в размере после уменьшения.

  7. Уменьшаем datafile
    alter database datafile '/path/datafile' resize <newsize>M;
  8. Возвращаем все таблицы обратно
    set serveroutput on
    begin
      for i in (select distinct owner,segment_name from dba_extents where segment_type='TABLE' and tablespace_name='TEST')
      loop
        execute immediate 'alter table '||i.owner||'.'||i.segment_name||' move tablespace TS1';
      end loop;
    end;
    /
  9. Возвращаем все таблицы с LOBами обратно
    set serveroutput on
    begin
      for i in (select distinct owner,table_name,column_name,segment_name from dba_lobs where tablespace_name='TEST')
      loop
        execute immediate 'alter table '||i.owner||'.'||i.table_name||' move tablespace TS1'||'LOB ('||i.column_name||') store as '||i.segment_name||' (tablespace TS1)'
      end loop;
    end;
    /
  10. Восстанавливаем 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;
    /
  11. Возвращаем все индексы обратно
    set serveroutput on
    begin
      for i in (select distinct owner,segment_name from dba_extents where segment_type='INDEX' and tablespace_name='TEST')
      loop
        execute immediate 'alter index '||i.owner||'.'||i.segment_name||' rebuild tablespace TS1';
      end loop;
    end;
    /
  12. Убеждаемся, что табличное пространство больше не используется
    select count(*) from dba_extents where tablespace_name='TEST';
  13. Удаляем ненужное нам более табличное пространство вместе с файлом данных
    drop tablespace TEST including contents and datafiles;
 
Recent changes RSS feed Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki Donate