Question
to clone a work table in oracle database
I would like to create a copy of existing work object table along with the data and give a new name in oracle database . Would you please guide the steps to be followed in SQL developer tool?
Accepted Solution
Hello branl,
Thank you for your reply. I got a couple questions on this: you have mentioned that Open the table and change table name, index name(s) etc. Does open mean open table as create scripts? Would you please elaborate what else be included in the etc? I need to make sure the cloned table works appropriately after I map it to the existing work group class.
I also saw table rename comand in SQL developer? If I directly rename the table, and map it to the work class, does it perfectly work or is there any other things I need to consider?
By Open the table I just mean open it in SQL developer. But I wasn't trying to imply that you change the table name itself. But instead copy the SQL from the SQL tab and change the table/index names in the SQL text. Then re-apply that through a SQL worksheet in order to create the new table.
You could rename the table, if you don't need the original name. If you do this you would have to ensure that the table mapping in the Pega table mapping is also pointing to the correct table name (as you had mentioned).
But there are other factors which may come into play. For example, if you are referring to this table's original name in custom triggers or stored procedures then these too would need to be amended.
This is possibly not as clear cut as you were hoping.
There is an option to do this in SQL Developer;
Open the table, then under the Actions drop-down there should be an option named Copy...
This should hopefully include an option to copy the data. But I believe this is all that you will get (data and table structure), so if you have any contraints/indexes then these will not be included.
If you need to ensure that the table has the appropriate indexes as well, I'd probably be inclined to open the table then copy the data from the SQL tab.
Paste this into a SQL worksheet and then change the table name, index name(s) etc... so that they are unique. And run that SQL to create the table.
Then you could populate it with: -
insert into NEWTABLE (select * from OLDTABLE);
commit;
But you may need to consider how many records this is going to insert in one go, as this may put some strain on the database.