Blog do Marcio Portes

Recebi alguns feedbacks sobre o post anterior, portanto vamos estudar um pouco mais o drop datafile.

Visitei hoje seu blog e li o artigo sobre como dropar um datafile. No exemplo você dropa um datafile de uma tablespace que possui dois datafile.
O que acontece com o conteudo daquele datafile? Porque ate onde eu sei não é possivel colocarmos uma determinada tabela por exemplo em um datafile específico, somente especificamos em qual tablespace queremos alocar a tabela e o proprio banco distribui pelos datafiles.

Os dados do datafile dropado são jogados para o outro datafile? Ou todos os objetos que estavam contidos inteiramente ou parcialmente no datafile também são dropados?

Não, os dados não são jogados em outro datafile, neste caso não é permitido o drop datafile e não só nesse caso. Veja:

ops$marcio:LX10G> create tablespace tbs_deldf datafile
  2    '/oracle/tmp/df1.dbf' size 10m,
  3    '/oracle/tmp/df2.dbf' size 10m
  4  /

Tablespace created.

ops$marcio:LX10G>
ops$marcio:LX10G> col file_name format a50
ops$marcio:LX10G> select file_name
  2    from dba_data_files
  3   where tablespace_name = 'TBS_DELDF'
  4  /

FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
/oracle/tmp/df2.dbf

2 rows selected.

ops$marcio:LX10G>
ops$marcio:LX10G> drop table t;

Table dropped.

ops$marcio:LX10G> create table t tablespace tbs_deldf as select * from all_objects;

Table created.

ops$marcio:LX10G> insert /*+ append */ into t select * from t;

51191 rows created.

ops$marcio:LX10G> commit;

Commit complete.

ops$marcio:LX10G>
ops$marcio:LX10G> select count(*) from t;

     COUNT(*)
-------------
       102382

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> select file_name, round( bytes/1024/1024 ) mbytes,
  2         round( ratio_to_report(bytes) over () * 100, 2 ) pct
  3    from (
  4  select sum(a.bytes) bytes, b.file_name
  5    from dba_extents a,
  6         dba_data_files b
  7   where a.owner = USER
  8     and a.segment_name = 'T'
  9     and a.segment_type = 'TABLE'
 10     and a.file_id = b.file_id
 11   group by b.file_name
 12         )
 13   order by file_name
 14  /

FILE_NAME                                                 MBYTES           PCT
-------------------------------------------------- ------------- -------------
/oracle/tmp/df1.dbf                                            6            50
/oracle/tmp/df2.dbf                                            6            50

2 rows selected.

Fizemos uma carga e o oracle distribuiu os dados uniformemente através dos datafiles, portanto, 50% para cada. Nesse caso, o comando já não é mais permitido, porque os datafiles não estão vazios.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

Outra restrição para o comando, não é permitido dropar o primeiro datafile da tablespace (no momento da criação).

ops$marcio:LX10G> drop table t;

Table dropped.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TBS_DELDF

Drop é suficiente para limpar um datafile? Não! Não podemos esquecer que um drop na versão 10g, na verdade marca a tabela como dropada, mas o datafile ainda não está vazio enquanto não purgamos os dados. Nesse caso, o comando também falha.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


ops$marcio:LX10G> purge recyclebin;

Recyclebin purged.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';

Tablespace altered.

Outro caso é quando a tablespace possui apenas um datafile.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TBS_DELDF has only one file

É importante frizar também que o comando drop datafile não substitui o alter database ... offline drop, eles convivem e cada qual tem sua aplicação.

Labels: New Feature