This discussion is archived
7 Replies Latest reply: Oct 5, 2012 1:18 PM by Mark Malakanov (user11181920) RSS

datafile size Alter

Madhu.149 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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: Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    I realigned to the best possible
    use
    for such monospaced texts

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points