You are here:

Oracle/Bulk collect

Advertisement


Question
Hi,
I am working on archiving the data from the table which is around 100 million records, I am doing with the following script:
it's take long time and generate more redo log.

spool c:\sample.txt
DECLARE
      stmt VARCHAR2(2000);
BEGIN
  stmt:= 'CREATE TABLE sample_'||createDt||'  as select * from sample';
  execute Immediate stmt;
  
  stmt:= 'create table sample_Tmp  as select * from sample d where g_date=sysdate-45';
  execute Immediate stmt;

  sql_stmt:= 'Truncate table PREDICTED_LOAD1';
       Execute Immediate sql_stmt;
      
  
  insert into sample as select * from sample_tmp;
       COMMIT;
  Execute immediate ' Drop table PREDICTED_LOAD1_Temp';

  end;
/
Spool off;


Is there any short and easy way to do this or can u suggest how to use the bulk collect and forall in this script.
and this table have lot of dendencies,constraints, trigger and indxes, we can think for rename the table.

Thanks in advance.
Regards,
Irshad

Answer
Hi Irshad,

You can check the following links for your problem -

http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
http://dba-blog.blogspot.com/2005/08/using-of-bulk-collect-and-forall-for.html
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.htm
http://www.dba-oracle.com/oracle_news/2004_1_31_plsqlL_bulk_binds_FORALL.htm


Hope these help.

Regards

Suchitra

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suchitra Joshi

Expertise

I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience

15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 About.com. All rights reserved.