Altering and Dropping Tablespaces

No post anterior vimos como criar um tablespace, agora vamos ver como alterar e até mesmo dropar um tablespace.

Vamos criar um tablespace com uma tabela nele:

SQL> CREATE TABLESPACE teste_tbs;

Tablespace created.

SQL> CREATE TABLE teste(a CHAR(2000)) TABLESPACE teste_tbs;

Table created.

SQL>

O tablespace teste_tbs foi criado utilizando todos as características DEFAULT juntamente com OMF. Logo após foi criada um tabela utilizando esse tablespace. Vamos agora modificar um pouco o tablespace, como o seu nome, colocar offline, online e etc.

SQL> ALTER TABLESPACE teste_tbs OFFLINE;

Tablespace altered.

SQL> DELETE teste;
DELETE teste
       *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_tb_gknq1p1q_.dbf'


SQL> drop table teste;

Table dropped.

SQL> ALTER TABLESPACE teste_tbs ONLINE;

Tablespace altered.

SQL> CREATE TABLE teste(a CHAR(2000)) TABLESPACE teste_tbs;

Table created.

SQL> ALTER TABLESPACE teste_tbs READ ONLY;

Tablespace altered.

SQL> INSERT INTO teste VALUES(1);
INSERT INTO teste VALUES(1)
            *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_tb_gknq1p1q_.dbf'


SQL> DROP TABLE teste;

Table dropped.

SQL> ALTER TABLESPACE teste_tbs RENAME TO terciocosta_tbs;

Tablespace altered.

SQL>

Primeira ação foi deixar o tablespace offline, logo após foi feito uma tentativa de executar um DELETE na tabela, e assim é exibido um erro, informando que o datafile não pode ser lido, mas logo depois conseguimos executar um DROP. O mesmo acontece com o o tablespace em modo READY ONLY, ou seja, pode ser feito o SELECT, mas nenhuma modificação. Então, por que podemos executar um DROP nos objetos? Por que o drop nada mais é que deletar a linha que descreve esse objeto no dicionário de dados, deixando ele inacessível e assim o seu espaço poderá depois ser reutilizado. O tablepsace do dicionário de dados fica no SYSTEM, e esse tablespace esta online e READ WRITE.

Por último foi feito um rename do tablepsace. Para renomear o datafile o tablespace não pode estar online, pois temos que modificar o nome do arquivo no sistema operacional. Vamos ver como seria, e logo após, uma saída de contorno, para ter o mesmo resultado, mas de maneira tudo online, sem downtime no tablespace e/ou datafile:

SQL> ALTER TABLESPACE terciocosta_tbs READ WRITE;

Tablespace altered.

SQL> CREATE TABLE teste(a CHAR(2000)) TABLESPACE terciocosta_tbs;

Table created.

SQL> ALTER TABLESPACE terciocosta_tbs OFFLINE;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oca ~]$ mv '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_tb_gknq1p1q_.dbf' '/u01/app/oracle/oradata/ORCL/datafile/terciocosta_tbs01.dbf';
[oracle@oca ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 1 16:34:47 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_tb_gknq1p1q_.dbf' TO '/u01/app/oracle/oradata/ORCL/datafile/terciocosta_tbs01.dbf';

Database altered.

SQL> ALTER TABLESPACE terciocosta_tbs ONLINE;

Tablespace altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/terciocosta_tbs01.dbf' TO '/u01/app/oracle/oradata/ORCL/datafile/terciocosta_tbs';

Database altered.

SQL>

Primeiro o tablespace foi colocado OFFLINE para a manutenção. Saímos para o Sistema operacional para modificar o nome do arquivo. Logamos de novo no banco e renomeamos o datafile, de acordo como ficou no S.O.. Logo após é possível colocar o tablespace online de novo. Após isso foi visto uma saída, para não ter que modificar a nível de S.O. e nem deixar o tablespace offline, que foi mover o datafile direto do Oracle.

Com respeito a deixar offline o tablespace, temos algumas opções. Veja a sintaxe:

ALTER TABLESPACE nome OFFLINE [NORMAL | IMMEDIATE | TEMPORARY]

O DEFAULT é o nomal, em que irá forçar um checkpoint em todos os datafiles que compõe o tablespace. Ou seja, todos os dirty buffers serão escritos antes de fechar os datafiles.

A opção IMMEDIATE seria forçar o tablespace e datafiles a ficar offline, sem gravar nenhum dirty buffers. Dessa maneira os datafiles estão corrompidos, faltando dados que foram comitados e terão que ser recuperados depois, antes de colocar o tablespace online de novo. Isso só pode ser feito se o banco estiver em archive mode.

E por último, a opção temporary, que é um meio termo dos dois anteriores. Todos os datafiles que estiverem ok irá ter o checkpoint e ficarão offline, mas aqueles que estiverem com problema, sendo impossível fazer o checkpoint ficarão offline imediatamente, para serem recuperados depois.

E como podemos modificar o tamanho do tablespace? Isso é feito por alterar o tamanho atual do datafile ou por adicionar novos datafiles.

Digamos que tenhamos um datafile que possui apenas 100MB e que queremos alterar ele para 32GB. E que logo depois queremos adicionar mais um datafile.

SQL> ALTER DATABASE  DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/terciocosta_tbs' RESIZE 32767M;

Database altered.

SQL> ALTER TABLESPACE terciocosta_tbs ADD DATAFILE;

Tablespace altered.

SQL>

Aumentamos o tamanho de um datafile, e também adicionamos outro datafile. Claro que para não ficar logo com esse tamanho todo, é somente deixar o datafile com auto extense.

E agora para finalizar, vejamos como DROPAR um tablespace

SQL> DROP TABLESPACE terciocosta_tbs;
DROP TABLESPACE terciocosta_tbs
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> DROP TABLESPACE terciocosta_tbs INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>

Foi impossível executar um DROP no tablespace por ele não estava vazio. Então seria necessário o INCLUDING CONTENTS para o Oracle executar um DROP nos objetos primeiros para depois executar o DROP no datafile. No exemplo também informei AND datafiles. Assim os datafiles também serão excluídos a nível de S.O..

Anúncios

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: , , , , , , , , ,
Publicado em Administração, Certificação Oracle

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Sou um
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 142.605 Visualizações
%d blogueiros gostam disto: