5 Replies Latest reply: Mar 15, 2013 1:55 AM by Pavan DBA RSS

    Can't drop tablespace ORA-01561

    996996
      Hi, i want to drop 2 tablespaces, but i can't =S:

      drop tablespace PSAPSTABD_OLD including contents and datafiles;
      drop tablespace PSAPSTABI_OLD including contents and datafiles;

      gives me these errors:

      Erro ao iniciar na linha 11 no comando
      drop tablespace PSAPSTABD_OLD including contents and datafiles
      Relatório de erro:
      Erro de SQL: ORA-01561: falha ao remover todos os objetos do tablespace especificado
      01561. 00000 - "failed to remove all objects in the tablespace specified"
      *Cause:    Failed to remove all objects when dropping a tablespace
      *Action:   Retry the drop tablespace until all objects are dropped
      Erro ao iniciar na linha 12 no comando
      drop tablespace PSAPSTABI_OLD including contents and datafiles
      Relatório de erro:
      Erro de SQL: ORA-01561: falha ao remover todos os objetos do tablespace especificado
      01561. 00000 - "failed to remove all objects in the tablespace specified"
      *Cause:    Failed to remove all objects when dropping a tablespace
      *Action:   Retry the drop tablespace until all objects are dropped

      So i did the follwing select:

      select owner, segment_name, segment_type, tablespace_name from dba_segments where tablespace_name in ('PSAPSTABD_OLD','PSAPSTABI_OLD');

      and got:

      SYS     BBBB     TABLE     PSAPSTABD_OLD
      SYS     BBBB~0     INDEX     PSAPSTABI_OLD
      SYS     BBBB~1     INDEX     PSAPSTABI_OLD
      SYS     BBBB~2     INDEX     PSAPSTABI_OLD
      SYS     BBBB~3     INDEX     PSAPSTABI_OLD
      SYS     BBBB~4     INDEX     PSAPSTABI_OLD

      but there is no index, table or even object with these names. if i try to drop then, oracle says they do not exist.


      any help?