Skip to Main Content
  • Questions
  • Need to if any provision for parallelly copying data from main table to different schema table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, shital.

Asked: November 29, 2022 - 3:46 am UTC

Last updated: December 01, 2022 - 3:59 am UTC

Version: 19C

Viewed 100+ times

You Asked

Hello Team,

I would like to know is there any provision for parallelly copying data from main table (one schema) to different schema table without hampering current traffic?

We need such provision/approach which should be faster way of doing this.

Please suggest or guide us how we can do this?

Please note: we want to create new schema which contains same table like in main schema. As soon as SQL operation happens on main schema table same should get copied to different new schema as well in faster way. We need to use this new schema for only reporting purpose so we need to develop this approach.


please guide us on this as soon as possible.

Thanks & Regards,
Shital


and Connor said...

I would like to know is there any provision for parallelly copying data from main table (one schema) to different schema table...

Yes

...without hampering current traffic?

Maybe, maybe not.

If my machine/storage can handle 60 concurrent busy sessions, then running a parallel operation that needs 16 workers is not drama.
Conversely, if my machine/storage can handle 12 concurrent busy sessions, then running a parallel operation that needs 16 workers is going to fry it.

But copying data in parallel is as simple as:

insert /*+ enable_parallel_dml append */ into target
select /*+ parallel */ ... from source;


Full discussion on the feature here https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/using-parallel.html#GUID-3E2AE088-2505-465E-A8B2-AC38813EA355

You can also use Resource Manager to place a limit on how much impact a user/session/etc can have if you want to have "safety limits". You'd need to liaise with your DBAs on that

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.