OraFAQ Forum: Server Administration » not able to drop table space (merged)

Home » RDBMS Server » Server Administration » not able to drop table space (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

not able to drop table space (merged) [message #399295]

Tue, 21 April 2009 08:34

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 ?..

Report message to a moderator

Re: not able to drop table space (merged) [message #399300 is a reply to message #399295]

Tue, 21 April 2009 08:45

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: not able to drop table space (merged) [message #399302 is a reply to message #399295]

Tue, 21 April 2009 08:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: not able to drop table space (merged) [message #399303 is a reply to message #399302]

Tue, 21 April 2009 08:52

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.

Report message to a moderator

Previous Topic:	database failure
Next Topic:	standby issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 23:35:36 CDT 2024