Вывод строк создания табличных пространств, аналогичных существующим, для определённых схем
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;
select file_name,tablespace_name from dba_temp_files;
create temporary tablespace TEMP01 tempfile '/opt/oracle/oradata/db02/temp.dbf' size 100M autoextend on next 500M;
alter database default temporary tablespace TEMP01;
select username,temporary_tablespace from dba_users;
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;