[Doug]

Pessoal, bom dia.

Tenho uma tablespace chamada MSAFD. A essa tablespace foi add uma nova, a MSAFD_01. Hj na tablespace adicionada MSAFD_01.DBF não existe nenhum registro. Gostaria de dropar a mesma, mas não consigo.

Estou realizando o comando abaixo:

DROP TABLESPACE MSAFD_01 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Qual seria o comando correto, para dropar o datafile adicionado ?

PS – base de testes…

abs a todos

[burga]

Qual mensagem que aparece quando você executa o comando?

Restrictions on Dropping Tablespaces

*

You cannot drop a tablespace that contains a domain index or any objects created by a domain index.
*

You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.
*

You cannot drop a tablespace that has been designated as the default tablespace for the database. You must first reassign another tablespace as the default tablespace and then drop the old default tablespace.
*

You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. You must first remove the tablespace from the database default temporary tablespace group and then drop it.
*

You cannot drop a tablespace, even with the INCLUDING CONTENTS and CASCADE CONSTRAINTS clauses, if doing so would disable a primary key or unique constraint in another tablespace. For example, if the tablespace being dropped contains a primary key index, but the primary key column itself is in a different tablespace, then you cannot drop the tablespace until you have manually disabled the primary key constraint in the other tablespace.

http://download.oracle.com/docs/cd/B120 … s_9004.htm

[Doug]

ERRO na linha 1:
ORA-00959: tablespace ‘MSAFD_01’ não existe

[Doug]

SQL> ALTER TABLESPACE MSAFD
2 ADD DATAFILE ‘C:oracleproduct10.2.0oradataORCLMSAFD_01.DBF’
3 SIZE 100M
4 AUTOEXTEND ON
5 NEXT 512M
6 MAXSIZE 8192M;

Tablespace alterado.

SQL> DROP TABLESPACE MSAFD_01 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE MSAFD_01 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERRO na linha 1:
ORA-00959: tablespace ‘MSAFD_01’ não existe

SQL>

abs e brigaduuuu….

[burga]

ah, tablespace e datafile são duas coisas diferentes, o que você quer é apagar um datafile…

No 10gR2 ou mais recente:
alter tablespace MSAFD drop datafile ‘C:oracleproduct10.2.0oradataORCLMSAFD_01.DBF’

Versões anteriores:
http://download.oracle.com/docs/cd/B105 … .htm#16235

[Doug]

Verdade Burga, me desculpe, me confundi com os nomes, mas muito obrigado, deu certo.

abs

[Autor]

Posts