Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Tablespace problem when running procedures

    When I drop tables in a plsql procedure, the tablespace is not increasing and I am running out of tablespace. I have no control over the tablespace limit allocated to me. Is there a way to force the tablespace to be updated while the procedure is still running ???

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    in my opinion this 'update' is done immediately since it is DDL and it has a commit in it.
    You might try to use it in an autonomous transaction, but I don't think i will solve the problem

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    I have double-checked and although the tables are definitely dropped, the tablespace remains unchanged until the plsql procedure has ended at which point it reflects the space freed up by dropping the tables.

    Help !

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    hmmm strange behaviour, but that can be resolved i think.
    Try the following.
    Create a new procedure to drop the tables. In the declare add 'pragma autonomous transaction;'
    Next program the code to drop the tables and include a commit (I know that is not necessary for ythe dropping, but it is for Oracle itself).

    In the original procedure, take out the code for dropping the tables and add a call to the new procedure.

    Then try again. Since the pragma should treat the procedure as uif it was a separate connection which is created when the procedure starts and ends when the procedure ends, your problem should be solved.

    Hope that helps.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    Thanks for your suggestions. I tried using an autonomous transaction but with exactly the same results. The tablespace did not change when I ran the new DropTable procedure. It only reflected the released space after the calling procedure terminated. Here is the new DropTable procedure which I was calling from my main procedure:

    Create or replace procedure DropTable
    (v_table IN VARCHAR2)
    is
    pragma autonomous_transaction;
    BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE '||v_table;
    COMMIT;
    end DropTable;

    Any more ideas please????

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    have you tried to run the two procedures separately ?
    First run the drop procedure, check the tablespace and then the other procedure. It's just to make sure that the ending of the procedure causes this behaviour.
    Let me know if that works.
    Edwin van Hattem
    OCP DBA / System analyst

  7. #7
    Join Date
    Oct 2003
    Posts
    8
    I'm not quite sure I understand what you mean but I have had a breakpoint on the DropTable statement and also on the very last line of the calling procedure and checked the tablespace before and after the DropTable and before and after the last line of the calling procedure and the tablespace only changes after the last line of the calling procedure has run and it has terminated.

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i meant really separate:
    sql> execute proc_drop_tables;

    plsql procedure completed succesfully
    sql> execute other_procedure;

    ....

    Just to see this works.
    Edwin van Hattem
    OCP DBA / System analyst

  9. #9
    Join Date
    Oct 2003
    Posts
    8
    I see what you mean. Unfortunately, the calling procedure creates the tables which then need to be dropped so I can't run the DropTable procedure as a separate entity. Sorry.

  10. #10
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    you can't create two simple procs to check it ? Just to make sure the idea works ?
    Have u tried to make calling procedure that first calls the drop-procedure and then the create procedure ?
    Edwin van Hattem
    OCP DBA / System analyst

  11. #11
    Join Date
    Oct 2003
    Posts
    8
    Hi

    As you suggested, I have created a simple calling procedure that first calls the drop_tables procedure and then calls the other procedure that recreates them. The tablespace used figure stayed the same after the first procedure ran eventhough the tables were dropped and increased after the second procedure ran. When the calling procedure terminated, the tablespace used figure dropped back down to what it was originally.

    I then ran the drop_tables procedure on its own and the tablespace used figure decreased. I then ran the other procedure on its own and the tablespace used figure increased.

    I think this proves that the tablespace being released by the drop tables is not freed up even when using the autonomous_transaction feature.

    Any other ideas ???

  12. #12
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    thanks for your answer. Although i do not face this problem myself, it made me so curious that I will ask Oracle's opinion about it (doing it tomorrow). I let u know what they say.
    Edwin van Hattem
    OCP DBA / System analyst

  13. #13
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    Got an answer from Oracle :

    Answer from Oracle
    This is intended behavior and basically 2 things can be done:
    1. As you already did, perform the drop and create steps in seperate procedures
    2. Increase the size of the tablespace in which the object resides.

    When objects such as a table are dropped the segments belonging to the object are initially marked at temporary. These temporary segments are then dropped at the end of each top-level call to the RDBMS. Until the segments are actually dropped, the space they occupy is not available for reuse. If there is not sufficient space in the tablespace (minus the space currently in use by temporary segments), an error occurs.

    In this case, each PL/SQL anonymous block represents a single top-level call. If the drop and create requests are made in two separate blocks, then the segments are dropped after the drop and before the create. If they are made within a single block, the segments are not dropped until the end of the top-level call.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  14. #14
    Join Date
    Oct 2003
    Posts
    8
    Thanks for all your assistance. I have managed to alleviate the problem by replacing some of the tables with views.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •