Home » Server Options » Streams & AQ » Not able to drop the tablespace logmnr_ts (oracle9i)
Not able to drop the tablespace logmnr_ts [message #326509] Wed, 11 June 2008 16:32 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
My oracle version is

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


I am trying to drop the tablespace logmnr_ts.

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


SQL> connect strmadmin/strmadminpw@oraprod
Connected.
SQL> select tablespace_name,table_name from user_tables;

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
                               AQ$_STREAMS_QUEUE_TABLE_H
                               AQ$_STREAMS_QUEUE_TABLE_I
                               AQ$_STREAMS_QUEUE_TABLE_NR
LOGMNR_TS                      AQ$_STREAMS_QUEUE_TABLE_S
                               AQ$_STREAMS_QUEUE_TABLE_T
LOGMNR_TS                      STREAMS_QUEUE_TABLE
USERS                          SYS_IOT_OVER_31020

7 rows selected.

SQL> alter table sys_iot_over_31020 move tablespace logmnr_ts;
alter table sys_iot_over_31020 move tablespace logmnr_ts
            *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

SQL>


I googled this error message. Still i am not able to move the SYS_IOT_OVER_31020 object into LOGMNR_TS tablespace..

Any help appreicated...
Re: Not able to drop the tablespace logmnr_ts [message #326517 is a reply to message #326509] Wed, 11 June 2008 18:23 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
My problem resolved. Please ignore my request.
Re: Not able to drop the tablespace logmnr_ts [message #326538 is a reply to message #326517] Wed, 11 June 2008 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about posting what you did to solve your problem?
Don't you think it may help others?

Regards
Michel
Re: Not able to drop the tablespace logmnr_ts [message #326719 is a reply to message #326538] Thu, 12 June 2008 07:04 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member

It looks like, there were some of the objects part of system tablespace. I dropped those objects in system tablespace first. Then i am able to drop logmnr_ts tablespace. I think, the objects i dropped in system tablespace are related to IOT tables in logmnr_ts tablespace. Thanks
Previous Topic: Need direction to learn streams & AQ
Next Topic: 9.2.0.4 to 10.2.0.1
Goto Forum:
  


Current Time: Fri Mar 29 00:06:44 CDT 2024