Skip to Content
avatar image
Former Member

Unable to release space from table

Hi all,

We are unable to release space from a table called TST03 even after deletion of records.

Followings are the information.

Database : 9.2

Table Name : TST03

Tablespace : LOCALY MANAGED.

Previously there were lots of rows.

At Present only 9 No of Rows.

Space allocated : 41 GB

PCT_INCREASE : Null

One of the column is of LONG RAW type.

Since the table is in LMTS, we were expecting that the space allocated will be released automaticaly after deletion of records.

Now, what are the option left with us to release 41GB of space ?

A. Is there any effect of "Drop storage" option of 'Truncate table' command ?

B. If yes , can i copy all the 9 rows to a new table, then use "Truncate table TST03 drop storage", check if space is released and then copy back all the 9 rows to this table.

C. Do you have any other easy solution apart from export/import ?

D. Checked all the relevant Note(646681,48400,10551), could not find an easy solution. I want to avoid offline export/import option.

Thanks .

Naba J Neog

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • May 02, 2007 at 09:15 PM

    > Hi all,

    Hi !

    > We are unable to release space from a table called

    > TST03 even after deletion of records.

    >

    > Followings are the information.

    > Database : 9.2

    > Table Name : TST03

    > Tablespace : LOCALY MANAGED.

    > Previously there were lots of rows.

    > At Present only 9 No of Rows.

    > Space allocated : 41 GB

    > PCT_INCREASE : Null

    > One of the column is of LONG RAW type.

    >

    > Since the table is in LMTS, we were expecting that

    > the space allocated will be released automaticaly

    > after deletion of records.

    Sorry, but that is not what LMTS is for. Wrong assumption.

    MaxDB e.g. returns space immediately - Oracle does not.

    > Now, what are the option left with us to release 41GB

    > of space ?

    > A. Is there any effect of "Drop storage" option of

    > 'Truncate table' command ?

    Yes, the effect is, that after the truncate the table is empty and only one extent is allocated - the rest is returned to freespace. That's the DEFAULT behaviour of TRUNCATE TABLE.

    > B. If yes , can i copy all the 9 rows to a new table,

    > then use "Truncate table TST03 drop storage", check

    > if space is released and then copy back all the 9

    > rows to this table.

    Nope - you would have to copy the LONG RAW columns as well and this cannot be done easily from sqlplus.

    > C. Do you have any other easy solution apart from

    > export/import ?

    Nope again - you'd have to use this offline reorganisation as long as you're not on Oracle 10g and the long raw fields got converted into LOBs. With 10g you might also use the SHRINK table command. But with Oracle 9i - sorry: exp and imp (will be pretty fast for 9 rows...)

    > D. Checked all the relevant Note(646681,48400,10551),

    > could not find an easy solution. I want to avoid

    > offline export/import option.

    Sorry - no way to avoid it and still get the free space back.

    Anyhow you might want to take actions to prevent this situtation from reoccuring.

    These kinds of questions are covered in the notes

    #48400http://service.sap.com/sap/support/notes/48400">#48400>

    #66290http://service.sap.com/sap/support/notes/66290">#66290>

    >

    > Thanks .

    > Naba J Neog

    You're welcome.

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      1. Did you switch TemSe to OS file System ? Yes

      2 What Database and Its Version. Oracle 9.2.0.6 & 10.2.0.2

      3. Type of Tablespace (LMTS or Dictionery Managed ) where TST03 exist. LMTS

  • May 01, 2007 at 12:30 PM

    problem with HWM (High water mark)

    I usually put table that keep on growing/shrinking in their own tablespaces. TST03 is a good candidate for this. move it to PSAPTST03D and its index in PSAPTST03I.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 01, 2007 at 03:17 PM

    1) Every few months when ousr TST03 table got tobe 85 GBs, we used to stop SAP and then truncate the table TST03. I do not know if you want to do this?

    2) Then we switched to TemSe that stores the files in the O/S and not the database. You may want to look into this.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 04, 2007 at 03:08 PM

    Replies from various persons(Mr. Bredman and Willian) are very helpfull and informative.

    Their reply Will help lot to decide course of action to solve the problem.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi All,

      By the way, reclaim space from specific single table...

      1.how to perform REORG Table with offline option truncate in db6/db2 udb 9.7?

      2.how to check spaces of a table has reclaimed enough spaces in db6 database if a reorg table offline ran to completed status?

      Regards

      Susi