Вывод строк создания табличных пространств, аналогичных существующим, для определённых схем
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;
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;
select file_name from dba_temp_files;
alter tablespace temp add tempfile '/path/to/datafiles/of/instance/temp02.dbf' size 100M autoextend on next 1M maxsize 4G;
Здесь: temp - имя temporary tablespace, которое мы модифицируем.
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.
test
".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; /
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; /
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; /
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; /
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
" покажет выигрыш в размере после уменьшения.
alter database datafile '/path/datafile' resize <newsize>M;
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; /
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; /
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; /
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; /
select count(*) from dba_extents where tablespace_name='TEST';
drop tablespace TEST including contents and datafiles;