Why is the SYSTEM tablespace datafile growing? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Why is the SYSTEM tablespace datafile growing?

  1. #11
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    You can only shrink it to the top of high water mark...

    try these scripts to see hor far you can go to resize your datafiles....
    Code:
    set verify off
    appdev@NICK817>column file_name format a50 word_wrapped
    appdev@NICK817>column smallest format 999,990 appdev@NICK817>heading "Smallest|Size|Poss."
    appdev@NICK817>column currsize format 999,990 heading "Current|Size"
    appdev@NICK817>column savings  format 999,990 heading "Poss.|Savings"
    appdev@NICK817>break on report
    appdev@NICK817>compute sum of savings on report
    
    column value new_val blksize
    
    appdev@NICK817> select file_name,
      2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
      3         ceil( blocks*&&blksize/1024/1024) currsize,
      4         ceil( blocks*&&blksize/1024/1024) -
      5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
      6  from dba_data_files a,
      7       ( select file_id, max(block_id+blocks-1) hwm
      8           from dba_extents
      9          group by file_id ) b
     10  where a.file_id = b.file_id(+)
     11  /
    
                                                       Smallest
                                                           Size  Current    Poss.
    FILE_NAME                                             Poss.     Size  Savings
    -------------------------------------------------- -------- -------- --------
    E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF                   55      264      209
    H:\ORACLE\ORADATA\NICK817\RBS01.DBF                      30      200      170
    F:\ORACLE\ORADATA\NICK817\TEMP01.DBF                      1       72       71
    E:\ORACLE\ORADATA\NICK817\INDX01.DBF                      1       58       57
    F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF                     1       12       11
    G:\ORACLE\ORADATA\NICK817\USERS01.DBF                     1      108      107
                                                                         --------
    sum                                                                       625
    
    6 rows selected.
    Then run this to resize them..
    Code:
    appdev@NICK817> column cmd format a75 word_wrapped
    appdev@NICK817> 
    appdev@NICK817> select 'alter database datafile '''||file_name||''' resize ' ||
      2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
      3  from dba_data_files a,
      4       ( select file_id, max(block_id+blocks-1) hwm
      5           from dba_extents
      6          group by file_id ) b
      7  where a.file_id = b.file_id(+)
      8    and ceil( blocks*&&blksize/1024/1024) -
      9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
     10  /
    
    CMD
    ---------------------------------------------------------------------------
    alter database datafile 'E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF' resize
    55m;
    
    alter database datafile 'H:\ORACLE\ORADATA\NICK817\RBS01.DBF' resize 30m;
    alter database datafile 'F:\ORACLE\ORADATA\NICK817\TEMP01.DBF' resize 1m;
    alter database datafile 'E:\ORACLE\ORADATA\NICK817\INDX01.DBF' resize 1m;
    alter database datafile 'F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF' resize 1m;
    alter database datafile 'G:\ORACLE\ORADATA\NICK817\USERS01.DBF' resize 1m;
    
    6 rows selected.
    HTH
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  2. #12
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Thanks,

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