Move a table/joins from one schema to another

[RADSr]

I have a situation where I had to create a key column as a formula column - however, I can't join to a table in another schema with a formula column, so I'm thinking of moving the target table into the same schema as the original table. 

1) Is there a way to do that easily?  Or is it recreate the table and all the joins manually?

2) I *could* create an MV of the original table so the formula columns aren't formula columns anymore, but then I'd potentially be in the same situation w/ having to recreate a ton of joins to the new MV.  Is there any way to do a re-naming shuffle (  table1 becomes table1_old, MV1 becomes table1 ) without the underlying joins being affected?  

3) I could - I think - export the schema w/ the new MV and edit the joins w/ a search and replace pointing the joins at the new MV from #2.

4) But if I do that w/ the renaming shuffle will the business schema need to be repointed?  I'm thinking not, but anyone w/ experience doing something like this please chime in! 

[john_cross]

Below is an approach I took when dealing with something similar. It's basically your 2nd approach. 

1) Delete the table. When prompted, "Are you sure you want to remove this table?", ensure to unselect "Remove all joins related to this table." This will keep the joins, but not the table. The delete verbiage might be slightly different based on what version you are using. 

2) Recreate your original table and name it something new, I use TableName_stg.

3) Create MV to select * from the original table.

All your original joins and business schemas should still work.

[RADSr]

Thanks!

I've added the steps "backup, backup, backup"  😉    but that's the path I've started upon.   Wish me luck!

[RADSr]

@john_cross  - did you encounter anything like a cache of metadata?   I'm getting this:

- INC_04030340: Materializing table failed because Table metadata update required; Columns with mismatched labels: [ bunch of table.column references] which are the exact same column names in the table which was deleted.  

But of course the table was deleted, and then created again from scratch so there shouldn't be any labels to mismatch.   Ugh.   

[john_cross]

I did not. I was in 5.1.4 when I tried this last. The error says mismatched labels, do the labels have the same naming convention? Can you check out the XML backup you took and see if the labels are matching?