Home » SQL & PL/SQL » SQL & PL/SQL » disk space get reduced due to procedure (oracle 10g)
disk space get reduced due to procedure [message #406417] Wed, 03 June 2009 23:35 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i am using a stored procedure whcih includes cursor for loops in it.when i compile and execute it my 'D' drive with space 5GB came to 24 MB.can u tell me why it happend like this and where i have to found those files to delete.

Re: disk space get reduced due to procedure [message #406419 is a reply to message #406417] Wed, 03 June 2009 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is YOUR code and as you don't share it with us we can't know what you do.

Regards
Michel
Re: disk space get reduced due to procedure [message #406424 is a reply to message #406419] Wed, 03 June 2009 23:53 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
my procedure is like this


create or replace PROCEDURE ezemrxmigrate AS
  a    NUMBER := 1;
  
  CURSOR c1 IS 
    SELECT screen_immun_id 
    FROM   emrscreenimmunlkup 
    WHERE  GROUP_ID=0 
    ORDER BY screen_immun_id
    FOR UPDATE OF screen_immun_id;
  
  CURSOR c11 (t1 NUMBER) IS
    SELECT screening_reference_id 
    FROM   emrpatientscreeningaudit 
    WHERE  screening_reference_id=t1
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c12 (t2 NUMBER) IS 
    SELECT screening_reference_id 
    FROM   emrpatientscreeningdetails 
    WHERE  screening_reference_id=t2
    FOR UPDATE OF screening_reference_id;
  
  CURSOR c13 (t3 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationaudit 
    WHERE  immunization_reference_id=t3
    FOR UPDATE OF immunization_reference_id;
  
  CURSOR c14 (t4 NUMBER) IS 
    SELECT immunization_reference_id 
    FROM   emrpatientimmunizationdetails 
    WHERE  immunization_reference_id=t4
    FOR UPDATE OF immunization_reference_id;
  
  BEGIN
  FOR i IN c1 LOOP
    UPDATE emrscreenimmunlkup 
    SET    screen_immun_id=a
    WHERE CURRENT OF c1;

    FOR j IN c11 (i.screen_immun_id) LOOP
      UPDATE emrpatientscreeningaudit 
      SET    screening_reference_id=a
      WHERE CURRENT OF c11;
    END LOOP;
 
    FOR j IN c12(i.screen_immun_id) LOOP
       UPDATE emrpatientscreeningdetails 
       SET    screening_reference_id=a
       WHERE CURRENT OF c12;
    END LOOP;
    
    FOR j IN c13(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationaudit 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c13;
    END LOOP;
    
    FOR j IN c14(i.screen_immun_id) LOOP
       UPDATE emrpatientimmunizationdetails 
       SET    immunization_reference_id=a
       WHERE CURRENT OF c14;
    END LOOP;
    a := a+1;
  END LOOP;
 END;
 



this is thw way i proceeded
Re: disk space get reduced due to procedure [message #406429 is a reply to message #406424] Thu, 04 June 2009 00:17 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i have noticed in my instance orcl undo tablespace dbf fole size is now more than 5GB.
how can i reduce this to normal way
Re: disk space get reduced due to procedure [message #406438 is a reply to message #406429] Thu, 04 June 2009 01:01 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It IS normal way, there is nothing you can do to reduce the size.
Get rid of your cursors and do it with UPDATE statements.

Regards
Michel
Previous Topic: Select for update
Next Topic: Scheduled job is not running automatically (merged)
Goto Forum:
  


Current Time: Fri Dec 02 12:37:44 CST 2016

Total time taken to generate the page: 0.10914 seconds