I have an urgent question, which could put a nail in the coffin for using Oracle Streams at my client.
I have the following scenario.
DB1 with Schema ABC with Tablespace TS1 which contains a table TAB1 that I would like to replicate using downstream capture
My Downstream Target Environment looks like
DB2 with Schema XYZ with Tablespace TS2 I would like to replicate table TAB1 from DB1 into this Schema and Tablespace
I am using dbms_streams_adm.maintain_tables and this procedure completes successfully but the table is not created in the XYZ schema. I have found 2 references on the internet stating that the Target DB (DB2) must have the same tablespace(s) as the source DB, and that datapump will create the schema in the target DB when it performs the import and import the table into the schema and tablespace. Nowhere in the Oracle user/admin guides for Streams does it state this as a requirement for table level replication.
I have tested the call to dbms_streams_adm.maintain_tables on 2 DBs that are a mirror of each other (i.e. same user and same tablespaces) and the table I choose to replicate is replicated successfully.
My question is can I replicate a table without creating the tablespace in my Target DB? I so can I achieve this using the dbms_streams_adm.maintain_tables procedure? If not how?
We are looking to replicate data from several databases with tables in a number of tablespaces, surely we don't have to create all the tablespaces in the downstream database? Our preferred approach is to replicate the tables from the source systems into 1 schema and tablespace.