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

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;

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;

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;

Суть - создаём временное (не 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;