7 Replies Latest reply: Oct 5, 2012 3:18 PM by Mark Malakanov (user11181920) RSS

    datafile size Alter

    Madhu.149
      Hello All,


      Datafile status before resizing
      FILE_NAME                           TABLESPACE_NAME    STATUS     ONLINE_STATUS     AUTOEXTENSIBLE           TOTAL (MB)    USED (MB)      BALANCE (MB)     USED_%
      D:\ORACLE\ORADATA\USER1_512M_01.DBF     USER1_512M      AVAILABLE     ONLINE             YES              7,680           3,584            4,096           47%
      N:\ORACLE\ORADATA\USER2_512M_02.DBF     USER2_512M      AVAILABLE     ONLINE              NO              512                0              512           0%
      I resized the datafile to 4G
      But the used space reduced from 3584MB to 2560MB. How is this possible?
      ALTER DATABASE DATAFILE 'N:\ORACLE\ORADATA\USER2_512M_02.DBF' AUTOEXTEND ON
      ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\USER1_512M_01.DBF' AUTOEXTEND OFF
      ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\USER1_512M_01.DBF' RESIZE 4G;
      
      
      FILE_NAME                          TABLESPACE_NAME   STATUS     ONLINE_STATUS     AUTOEXTENSIBLE     TOTAL (MB)  USED (MB)    BALANCE (MB)  USED_%
      D:\ORACLE\ORADATA\USER1_512M_01.DBF     USER1_512M  AVAILABLE     ONLINE             NO               3,584             2,560          1,024         71%
      N:\ORACLE\ORADATA\USER2_512M_02.DBF     USER2_512M  AVAILABLE     ONLINE             YES              1,024              0            1,024         0%
      Edited by: Madhu.149 on Oct 5, 2012 5:31 PM
        • 1. Re: datafile size Alter
          John Stegeman
          Looks like you've misaligned the columns in your query. I still see 3584MB in the output, so you did something funny either in the query or in aligning the columns when pasting.
          • 2. Re: datafile size Alter
            Madhu.149
            I have realigned to the best possible.
            I am sure that the Used Space is reduced.

            What may be the reason?


            Oracle 11g 11.2.0.1 standard edition
            • 3. Re: datafile size Alter
              John Stegeman
              Sorry - something just doesn't make sense.

              4G isn't 3584M

              Are you using two different databases or something?
              • 4. Re: datafile size Alter
                Madhu.149
                No, I am not using two different database.

                Also I have used 4G only while resizing the datafile.

                At the time I resized to 4G and turned autoextend off, it was displaying Used space as 3584MB
                But today when I checked. It it is reduced.

                And for another datafile of same tablespace, I made auto extend on. But the Total Size was 512M and used was 0M
                But today when I checked Total became 1024M and Used remained 0M


                Also, the front end application works fine.
                I am just shock with the used space.

                Every three days, datafile undergo shrinking by oracle shrink command.
                Will this impact on the used space?

                Edited by: Madhu.149 on Oct 5, 2012 5:55 PM
                • 5. Re: datafile size Alter
                  John Stegeman
                  Your first post doesn't support what you say.

                  You say you resized the file to 4GB, but your query shows the file size as 3584 MB.

                  So, the only thing I can say is that you've confused yourself somewhere, as I have done to myself many times before :)
                  • 6. Re: datafile size Alter
                    abdul:
                    hi ,
                    demonstrate the output:
                    SET ECHO        OFF
                    SET FEEDBACK    6
                    SET HEADING     ON
                    SET LINESIZE    180
                    SET PAGESIZE    50000
                    SET TERMOUT     ON
                    SET TIMING      OFF
                    SET TRIMOUT     ON
                    SET TRIMSPOOL   ON
                    SET VERIFY      OFF
                    
                    CLEAR COLUMNS
                    CLEAR BREAKS
                    CLEAR COMPUTES
                    
                    COLUMN owner           FORMAT a20                  HEADING "Owner"
                    COLUMN tablespace_name FORMAT a30                  HEADING "Tablespace Name"
                    COLUMN segment_type    FORMAT a18                  HEADING "Segment Type"
                    COLUMN bytes           FORMAT 9,999,999,999,999    HEADING "Size (in Bytes)"
                    COLUMN seg_count       FORMAT 9,999,999,999        HEADING "Segment Count"
                    
                    BREAK ON report ON owner SKIP 2
                    
                    COMPUTE sum LABEL ""                OF seg_count bytes ON owner
                    COMPUTE sum LABEL "Grand Total: "   OF seg_count bytes ON report
                    
                    SELECT
                        owner
                      , tablespace_name
                      , segment_type
                      , sum(bytes)  bytes
                      , count(*)    seg_count
                    FROM
                        dba_segments
                    GROUP BY
                        owner
                      , tablespace_name
                      , segment_type
                    ORDER BY
                        owner
                      , tablespace_name
                      , segment_type
                    /
                    B>O>L
                    • 7. Re: datafile size Alter
                      Mark Malakanov (user11181920)
                      I realigned to the best possible
                      use
                      for such monospaced texts