Copying table in pl/sql 1000 rows at once.
624226Jul 20 2010 — edited Jul 23 2010Hi everybody,
I have an PL/SQL procedure that does the following.
execute immediate 'create table ' || p_descination_table || ' as select * from ' || p_source_table || '; '
And this copies a table from one schema to another (the procedure is in a user that has create table anywhere privilege).
The problem is if the table is very big I want to copy 1000 rows at a time and do a commit between batches.
So I can do
execute immediate 'create table ' || p_descination_table || ' as select * from ' || p_source_table || ' where 1=0; '
To create the structure but how do I copy the rows 1000 at a time.
Lastly does anyone have code that generates the create index commands so I can also create the indexes on the target table.
Ben