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

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;

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;

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

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