Hi,

My O/S is Sun Solaris ver 9 and database is Oracle 9i rel 2.

I am facing one problem. If I query DBA_subscriptions then I get one row

HANDLE 2
SET_NAME SYNC_SET
USERNAME CDC_SUB
CREATED 7/23/2004 16:49
STATUS A
EARLIEST_SCN 2648307
LATEST_SCN 2648676
DESCRIPTION Change data for emp
LAST_PURGED 7/23/2004 17:39
LAST_EXTENDED 7/23/2004 17:39

Now I am trying to drop above subscription. So I am issuing the following command

SQL> EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW (SUBSCRIPTION_HANDLE=>2,SOURCE_SCHEMA=>'hr',SOURCE_TABLE=>'employees');
BEGIN DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW (SUBSCRIPTION_HANDLE=>2,SOURCE_SCHEMA=>'hr',SOURCE_TABLE=>'employees'); END;

*
ERROR at line 1:
ORA-31432: invalid source table
ORA-06512: at "SYS.DBMS_CDC_SUBSCRIBE", line 0
ORA-06512: at line 1

If I try to purge the window then I get following errors

SQL> EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW( SUBSCRIPTION_HANDLE=>2);
BEGIN DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW( SUBSCRIPTION_HANDLE=>2); END;

*
ERROR at line 1:
ORA-31430: change view exists
ORA-06512: at "SYS.DBMS_CDC_SUBSCRIBE", line 0
ORA-06512: at line 1

After this If I query DBA_SUBSCRIBED_TABLES or DBA_SUBSCRIBED_COLUMNS, then I cann't find out any row. How can I get the name
of the change view which exists ? How to drop this active subscription ?

If I query v$version then I get following output

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

Ultimately I had to remove base change table using following command

SQL> execute dbms_cdc_publish.DROP_CHANGE_TABLE(OWNER=>'CDC',CHANGE_TABLE_NAME=>'EMP_NEW_CDC',FORCE_FLAG=>'Y');
PL/SQL procedure successfully completed.

Now What to do ? How to remove the above info from the database ? I need help urgently.

Thanks
Deba