•  
 
Содержание

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

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

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

select * from dba_data_files;
select 'create tablespace '|| tablespace_name ||
       ' datafile '''
       || regexp_replace(
            file_name
            ,'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\DB01\\'
            ,'D:\\ORACLE\\PRODUCT\\10G\\ORADATA\\DB02\\'
          )
       ||''' size '|| bytes/1024/1024
       ||'M autoextend on next '
       || increment_by
       ||';' command
from dba_data_files
where tablespace_name in (
        select tablespace_name from dba_ts_quotas where username in ('SCOTT','SCOTT1')
        union
        select default_tablespace from dba_users where username in ('SCOTT','SCOTT1')
      )
order by command;

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

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;
 
Назад
Наверх 
Rambler's Top100 бесплатная музыка mp3
Recent changes RSS feed Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki Donate