1 2 Previous Next 15 Replies Latest reply: Apr 13, 2011 2:53 AM by 807932 RSS

    resizing the tablespace

    807932
      Hi all,
      I have ran out of space in the asm so couldn't add the datafiles to a tablesapce so I tried to resize the tablespace which has got unused space so that I can use the gained space to add datafile when I tried to do that I am getting the following error. What can I do

      select FILE_NAME,TABLESPACE_NAME,sum(MAXBYTES)/1024/1024/1024,sum(USER_BYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='DATA11' group by TABLESPACE_NAME,FILE_NAME;

      FILE_NAME TABLESPACE_NAME SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024 SUM(BYTES)/1024/1024/1024
      -------------------------------------------------- -------------------- ---------------------------- ------------------------------ -------------------------
      +DATA_1/affperf/datafile/data11.290.639902725      DATA11                                 31.9999847                     2.01165771                2.01171875
      +DATA_1/affperf/datafile/data11.291.639902049      DATA11                                 31.9999847                     1.91497803                1.91503906
      +DATA_1/affperf/datafile/data11.296.639896325      DATA11                                 31.9999847                     4.73626709                4.73632813
      +DATA_1/affperf/datafile/data11.298.639895327      DATA11                                 31.9999847                     1.99212646                 1.9921875
      +DATA_1/affperf/datafile/data11.300.639894203      DATA11                                 31.9999847                     2.07611084                2.07617188
      +DATA_1/affperf/datafile/data11.305.639892373      DATA11                                 31.9999847                     1.93255615                1.93261719
      +DATA_1/affperf/datafile/data11.319.639873689      DATA11                                 31.9999847                     2.04779053                2.04785156
      +DATA_1/affperf/datafile/data11.339.639863013      DATA11                                 31.9999847                     5.23431396                  5.234375



      Actually the alloted size for +DATA_1/affperf/datafile/data11.290.639902725 file is 31 g but used is very less as you can see, but when I tried to make it to 20 g by using the following command i am getting the error.

      SQL> ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g;
      ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g
      *
      ERROR at line 1:
      ORA-01237: cannot extend datafile 233
      ORA-01110: data file 233: '+DATA_1/affperf/datafile/data11.291.639902049'
      ORA-17505: ksfdrsz:1 Failed to resize file to size 2621440 blocks
      ORA-15041: diskgroup space exhausted

      What is the problem, Please help.
        • 1. Re: resizing the tablespace
          Hemant K Chitale
          +DATA_1/affperf/datafile/data11.291.639902049 DATA11 31.9999847 1.91497803 1.91503906
          The datafile is only 1.9GB currently. Why not try resizing it to 2GB

          Similarly :
          +DATA_1/affperf/datafile/data11.290.639902725 DATA11 31.9999847 2.01165771 2.01171875
          this datafile is only 2.012GB in size.

          What other files do you have in the DiskGroup ? What is the total size of the files ? How large is the DiskGroup ?


          Hemant K Chitale
          • 2. Re: resizing the tablespace
            807932
            Hemant K Chitale wrote:
            +DATA_1/affperf/datafile/data11.291.639902049 DATA11 31.9999847 1.91497803 1.91503906
            The datafile is only 1.9GB currently. Why not try resizing it to 2GB

            Similarly :
            +DATA_1/affperf/datafile/data11.290.639902725 DATA11 31.9999847 2.01165771 2.01171875
            this datafile is only 2.012GB in size.

            What other files do you have in the DiskGroup ? What is the total size of the files ? How large is the DiskGroup ?


            Hemant K Chitale
            actually i am trying to shrink data11 tablespace


            TABLESPACE_NAME SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024 SUM(BYTES)/1024/1024/1024
            ------------------------------ ---------------------------- ------------------------------ -------------------------

            DATA11 255.999878 21.9458008 21.9462891

            This is the status of the tablespace, See out of 255gb alloted for it only 22gb is used. So I thought of shrinking it and use the gained space through shrink to add datafile to another tablespace. on trying this i got the mentioned error. also now i checked the asm space it shows that 99gb is free. Is re balancing problem causing this ORA-15041.
            • 3. Re: resizing the tablespace
              Hemant K Chitale
              But your command was attempting to resize the datafile upwards (i.e. increase it) ! Your SQL command wasn't shrinking the datafile but increasing the size.

              Do you need to reduce OR to increase the sizes ?


              Hemant K Chitale
              • 4. Re: resizing the tablespace
                807932
                Hemant K Chitale wrote:
                But your command was attempting to resize the datafile upwards (i.e. increase it) ! Your SQL command wasn't shrinking the datafile but increasing the size.

                Do you need to reduce OR to increase the sizes ?


                Hemant K Chitale
                FILE_NAME SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024 SUM(BYTES)/1024/1024/1024
                -------------------------------------------------- -------------------- ---------------------------- ------------------------------ -------------------------
                +DATA_1/affperf/datafile/data11.290.639902725                                         31.9999847                     2.01165771                2.01171875

                The above is the datafile which i am trying to shrink. Here the size of the datafile is 31.9999847, by using the below command i am trying to shrink it to 20gb.
                ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.290.639902725' RESIZE 20g;

                Am I correct. Please correct me if i am wrong.
                • 5. Re: resizing the tablespace
                  Hemant K Chitale
                  MAXBYTES is the maximum size of the file.
                  BYTES is the current size of the file -- which is 2GB.

                  You should be running this query :
                  select tablespace_name, file_name, maxbytes/1024/1024/1024 Max_Size_GB,  bytes/1024/1024/1024 Curr_Size_GB
                  from dba_data_files
                  -- optionally -- where tablespace_name = 'DATA11'
                  In your queries (which are inconsistent) you mix the concept of tablespace (which is a logical grouping of one or more datafiles) and datafile (which is a physical allocation of space).

                  Hemant K Chitale
                  • 6. Re: resizing the tablespace
                    807932
                    TABLESPACE_NAME FILE_NAME MAX_SIZE_GB CURR_SIZE_GB
                    ------------------------------ -------------------------------------------------- ----------- ------------
                    DATA11 +DATA_1/affperf/datafile/data11.339.639863013       31.9999847     5.234375
                    DATA11 +DATA_1/affperf/datafile/data11.319.639873689       31.9999847   2.04785156
                    DATA11 +DATA_1/affperf/datafile/data11.305.639892373       31.9999847   1.93261719
                    DATA11 +DATA_1/affperf/datafile/data11.300.639894203       31.9999847   2.07617188
                    DATA11 +DATA_1/affperf/datafile/data11.298.639895327       31.9999847    1.9921875
                    DATA11 +DATA_1/affperf/datafile/data11.296.639896325       31.9999847   4.73632813
                    DATA11 +DATA_1/affperf/datafile/data11.291.639902049       31.9999847   1.91503906
                    DATA11 +DATA_1/affperf/datafile/data11.290.639902725       31.9999847   2.01171875


                    this is the output
                    • 7. Re: resizing the tablespace
                      Hemant K Chitale
                      You can now see that the current sizes of the datafiles add up to approx 20GB for the tablespace.
                      Each file, in itself, is small (the largest being 5.23GB)


                      Hemant K Chitale
                      • 8. Re: resizing the tablespace
                        807932
                        hmmm. i am confused here. the thing is that there is enough space in the asm but still i get error when i try to add datafile in the tablespace data10.
                        This is the error

                        SQL> alter tablespace data10 add datafile '+DATA_1' size 500m;
                        alter tablespace data10 add datafile '+DATA_1' size 500m
                        *
                        ERROR at line 1:
                        ORA-01119: error in creating database file '+DATA_1'
                        ORA-17502: ksfdcre:4 Failed to create file +DATA_1
                        ORA-15041: diskgroup space exhausted

                        First i thought that the asm has ran out of space so thought of shrinking data11 tablespace and using the obtained space thro the shrink to add datafile to data10. but later i foundout that there is space in asm after examining the asm. then y i am unable to add the datafile?

                        Edited by: KRIS on Apr 12, 2011 1:59 PM
                        • 9. Re: resizing the tablespace
                          Hemant K Chitale
                          What I am saying is that your initial :
                          ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g;
                          was not shrinking but increasing the size of that datafile in DATA11.

                          I was not commenting on your ASM DiskGroup free space. Yes, you have run out of space in your ASM Disk Group. You could see if any of the datafiles in the disk grou p can be shrunk.


                          Assuming that the tablespace has been created with an 8KB block size, this query will help identify the HighWaterMark of each datafile in the tablespace. The datafiles cannot be shrunk below the HighWaterMark :
                          select e.file_id file_id, f.file_name file_name, f.bytes/1024/1024 sz,
                                 f.maxbytes/1024/1024 msz,
                                 max(e.block_id+e.blocks)*8192/1024/1024 hwm
                          from dba_data_files f, dba_extents e
                          where e.tablespace_name = '&tbs_name'
                          and f.file_id=e.file_id
                          group by e.file_id, f.file_name, f.bytes, f.maxbytes
                          order by f.file_name
                          /
                          Note : If the tablespace block size (which you can obtain from DBA_TABLESPACES) is 4K or 16K instead of 8K, change the 8192 multiplier accordingly.


                          Hemant K Chitale
                          • 10. Re: resizing the tablespace
                            807932
                            FILE_ID FILE_NAME SZ MSZ HWM
                            ---------- -------------------------------------------------- ---------- ---------- ----------
                            235 +DATA_1/affperf/datafile/data11.290.639902725            2060 32767.9844 2059.07031
                            233 +DATA_1/affperf/datafile/data11.291.639902049            1961 32767.9844 1960.07031
                            231 +DATA_1/affperf/datafile/data11.296.639896325            4850 32767.9844 4735.07031
                            238 +DATA_1/affperf/datafile/data11.298.639895327            2040 32767.9844 2039.07031
                            232 +DATA_1/affperf/datafile/data11.300.639894203            2126 32767.9844 2125.07031
                            234 +DATA_1/affperf/datafile/data11.305.639892373            1979 32767.9844 1978.07031
                            237 +DATA_1/affperf/datafile/data11.319.639873689            2097 32767.9844 2096.07031
                            236 +DATA_1/affperf/datafile/data11.339.639863013            5360 32767.9844 5182.07031
                            i get this.
                            • 11. Re: resizing the tablespace
                              Hemant K Chitale
                              So, all of these datafiles HWMs are close to the actual sizes. You will not be able to shrink any of these files (unless you drop/reorganise/rebuild tables and indexes).


                              You need more space allocated to the DiskGroup.
                              OR explore shrinking other tablespaces.
                              OR explore using another DiskGroup.


                              Hemant K Chitale
                              • 12. Re: resizing the tablespace
                                807932
                                Ok lets forget about the resizing problem. is there any comments from you reg why I couldn't add datafile to a tablespace despite the face that i have nearly 100gb free space in the asm. this is the error i get when i try to add the datafile


                                SQL> alter tablespace data10 add datafile '+DATA_1' size 500m;
                                alter tablespace data10 add datafile '+DATA_1' size 500m
                                *
                                ERROR at line 1:
                                ORA-01119: error in creating database file '+DATA_1'
                                ORA-17502: ksfdcre:4 Failed to create file +DATA_1
                                ORA-15041: diskgroup space exhausted
                                • 13. Re: resizing the tablespace
                                  Hemant K Chitale
                                  You say that you have 100GB free space in the disk group.

                                  The error message says otherwise :
                                  ORA-15041: diskgroup "string" space exhausted
                                  Cause: The diskgroup ran out of space.
                                  Action: Add more disks to the diskgroup, or delete some existing files.

                                  Have you queried V$ASM_DISKGROUP and V$ASM_FILE ?
                                  (query from the ASM instance, not from the database instance)


                                  Hemant K Chitale
                                  • 14. Re: resizing the tablespace
                                    807932
                                    i queried the asm instance only and this is the result


                                    SQL> select NAME,sum(TOTAL_MB)/1024,sum(FREE_MB)/1024 from v$asm_diskgroup group by NAME;

                                    NAME SUM(TOTAL_MB)/1024 SUM(FREE_MB)/1024
                                    ------------------------------ ------------------ -----------------
                                    ARCH_1 200 191.683594
                                    DATA_1 4510 99.2109375
                                    1 2 Previous Next