Shrinking Tablespaces...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Shrinking Tablespaces...

  1. #1
    Join Date
    Jan 2002
    Posts
    78

    Thumbs up

    Hi,

    I have created a table which we need to test for performance impace in a tablespace with 2 GB. We have some more objects which are already existed in this tablespace. Our table have been occupied nearly 1.7 GB.

    Now after our all experiments I droped that Table. and i got the 1.7 GB freeSpace in that tablespace. But I could not able to shrink that tablespace till to my requirement.

    When i am trying to shrinking it .. it is giving oracle error that I cannt give the min space which i occupied. but actually now it is just 200 MB is occupied. I want to give 500 MB for this and use the remaining 1.5 GB for another purpose.

    Please help me to come out from this problem.

    Thankx and Regards,
    Sekhar

  2. #2
    Join Date
    Dec 2001
    Posts
    221
    what command u executed for shrinking.

    use

    alter database datafile 'path\datafile' resize 500m
    Santosh Jadhav
    8i OCP DBA

  3. #3
    Join Date
    Jan 2002
    Posts
    78
    I am using DBA Studio...

    Originally posted by sjadhavdba
    what command u executed for shrinking.

    use

    alter database datafile 'path\datafile' resize 500m

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    then resize it in the studio.../
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Jan 2002
    Posts
    78
    Its not allowing me to resize .. Its giving Problem that the size which i gave is not free to give .. but the space is available now which we used before.

  6. #6
    Join Date
    Mar 2002
    Posts
    4
    Hi, you can't free space of a datafile (with 'alter database datafile '..' resize') which was already allocated to an object (like a table) - even if this object doesn't exist anymore (highwatermark principle). The only possibility for you to make the tablespace/the datafile smaler is to export all the objects inside this tablespace, drop & recreate the tablespace (with a smaler size) and import the objects again.

    Greetings,

    Kirsten

  7. #7
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    Yours datas don´t continuos, shrinking don´t resolve.
    You need export data, drop tablespace, create new tablespace with new size and import.
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  8. #8
    Join Date
    Mar 2002
    Posts
    56
    I had the same problem when archiving a partitioned table. If you still have objects in that tablespaces is not necessary to drop them, you can move them in a different tablespace, then resize the file then move them back. After I truncated the table, even though the size was zero, the segment was still there somewere and that's why I got the same error message like you. Same for the indexes.
    I send you the code, maybe it helps

    "alter table sfia.vehicle_history truncate partition " + Partition_Name(i) + " drop storage"
    "alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_data"
    "alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_data"
    "alter database datafile 'e:\sfiadbs\dbs\sfia_history_data_" & Trim(Str(i)) & ".ora' resize 10k"
    "alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))
    "alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))


  9. #9
    Join Date
    Jan 2002
    Posts
    78
    Yes, I did this export , import things earlier . . But through DB studio, it is allowing me to shrink the Datafile Sizes if it is never used with any object.

  10. #10
    Join Date
    Jan 2002
    Posts
    78
    This looking helpful for me.. I do try it..

    Thankx

    Originally posted by gab
    I had the same problem when archiving a partitioned table. If you still have objects in that tablespaces is not necessary to drop them, you can move them in a different tablespace, then resize the file then move them back. After I truncated the table, even though the size was zero, the segment was still there somewere and that's why I got the same error message like you. Same for the indexes.
    I send you the code, maybe it helps

    "alter table sfia.vehicle_history truncate partition " + Partition_Name(i) + " drop storage"
    "alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_data"
    "alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_data"
    "alter database datafile 'e:\sfiadbs\dbs\sfia_history_data_" & Trim(Str(i)) & ".ora' resize 10k"
    "alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))
    "alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))


Posting Permissions

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



Click Here to Expand Forum to Full Width