Export and import is a option for DBA but for QA people , these access cannot be given so end up in creating a script like this
TERRIBLE IDEA! Don't do that!
You should ask management to approve having the DBAs use export and import.
The process you suggest of using DELETE and INSERT has a MAJOR flaws that can very quickly cause some MAJOR problems.
That flaw is: there will be NO guarantee that the tables, indexes and other constraints on those 'test2' schema objects will be identical to the original objects in the 'test1' schema.
The chances of both sets of objects being identical for any length of time is SLIM and NONE. If even a singlel object is different it could potentially invalidate your entire set of tests and wind up just wasting everyone's time.
1. a table column in test1 might have been changed from VARCHAR2(30) to VARCHAR2(40) and have data too big to fit in the old 'test2' table. That will break your DELETE/INSERT process.
2. one or more test1 tables may have had new columns added or had columns dropped. That will break your DELETE/INSERT process.
3. one or more test1 tables may have had column constraints or defaults changed. That can break your DELETE/INSERT process.
It makes NO sense to waste your time writing a complex set of scripts/code to avoid the one constraint problem you mentioned only to have all of that time wasted if a developer makes even ONE simple, and needed, change to a single table.
There is NO WAY you can write a bullet-proof set of scripts/code to try to detect all of those possible issues.
Worse - when your DELETE/INSERT process fails in the middle it will very very time consuming to figure out how to fix the problem and then manually finish the process of loading the remaining tables.
The process will break when it encounters the first problem. If you then just fix that problem and restart from the beginning the process could then break again on the next problem that you didn't know existed yet.
Export/Import avoids ALL of those issues and also avoids ALL of that nonsense of writing a set of scripts/code that can't guarantee to do the job anyway.
The only 'script' you should write is one that will DROP all of the objects in 'test2' so that the import is guaranteed to create the proper versions of them.when the import is done.
Please - ask management to rethink that plan - it is DOOMED to fail.
I'd go one step further than rp, and drop/recreate the schema. Why? I've seen odd things happen with triggers when some old session remained connected while impdp replaces a table. Never quite was able to figure out exactly what was happening, though, even recompiling with no errors and flushing shared pool wouldn't fix it. Some poor QA guy would be driven nuts. I could scarcely believe it myself (10.2.0.4 with vendor-written trigger that emulates what SQL Server computed columns do).