Home » RDBMS Server » Server Administration » not able to drop table space (merged)
not able to drop table space (merged) [message #399295] Tue, 21 April 2009 08:34 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi all,

below is the needed info.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop tablespace PART2 including contents;
drop tablespace PART2 including contents
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace


SQL> ed
Wrote file afiedt.buf

1 select table_name,partition_name,tablespace_name,'TABLE' as object_type from dba_tab_partitions
2 where tablespace_name = '&tablespace'
3 union all
4 select index_name,partition_name,tablespace_name,'INDEX' from dba_ind_partitions
5 where tablespace_name = '&tablespace'
6* order by 1
SQL> /
Enter value for tablespace: PART2
old 2: where tablespace_name = '&tablespace'
new 2: where tablespace_name = 'PART2'
Enter value for tablespace: PART2
old 5: where tablespace_name = '&tablespace'
new 5: where tablespace_name = 'PART2'

TABLE_NAME
----------------------------------------------------------------------------------------------------
PARTITION_NAME TABLESPACE_NAME OBJEC
------------------------------ ------------------------------ -----
INTERVAL_PART
FF PART2 TABLE

SQL> select * from INTERVAL_PART;
select * from INTERVAL_PART
*
ERROR at line 1:
ORA-00942: table or view does not exist

how can i drop the table space now ?..
Re: not able to drop table space (merged) [message #399300 is a reply to message #399295] Tue, 21 April 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The fact that you cannot select without giving schema name does not mean the table does not exist.

ORA-14404: partitioned table contains partitions in a different tablespace
 *Cause: An attempt was made to drop a tablespace which contains tables
         whose partitions are not completely contained in this tablespace
 *Action: find tables with partitions which span the tablespace being
          dropped and some other tablespace(s). Drop these tables or move
          partitions to a different tablespace

Regards
Michel
Re: not able to drop table space (merged) [message #399302 is a reply to message #399295] Tue, 21 April 2009 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

drop object before tablespace.
Re: not able to drop table space (merged) [message #399303 is a reply to message #399302] Tue, 21 April 2009 08:52 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thnks Michel,

It's done . I was trying to delete a table of SYSTEM schema from SYS.
Previous Topic: database failure
Next Topic: standby issue
Goto Forum:
  


Current Time: Sat Dec 03 08:04:31 CST 2016

Total time taken to generate the page: 0.04682 seconds