[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12236
  • Last Modified:

Oracle 10G how to checking data file space

Hi, I use this scripts to display the data file usage, will someone verify is this suffcient to check on data file properties and space usage?

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 355
SET FEEDBACK OFF

SELECT df.AUTOEXTENSIBLE "AutoExtent",Substr(df.tablespace_name,1,20) "Tablespace Name",
       Substr(df.file_name,1,50) "File Name",
       Round(df.bytes/1024/1024,2) "Size (M)",
       Round(df.maxbytes/1024/1024,2) "Size (MaxMBytes)",
       Round(e.used_bytes/1024/1024,2) "Used (M)",
       Round(f.free_bytes/1024/1024,2) "Free (M)",
       Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM   DBA_DATA_FILES DF,
       (SELECT file_id,
               Sum(Decode(bytes,NULL,0,bytes)) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  df.tablespace_name='SHIPPING_INDX' and e.file_id (+) = df.file_id
AND    df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18


The results shown below: as u can see in autoextensible column there is YES and NO? is that mean "YES" data file is auto growth and  I'm trying also to add more space but it return me with with nothing then I try with another data files but it return me error, what is wrong here?


Aut Tablespace Name      File Name                                            Size (M) Size (MaxMBytes)   Used (M)   Free (M) % Used
--- -------------------- -------------------------------------------------- ---------- ---------------- ---------- ---------- -----------
NO  SHIPPING_INDX        /spw/u03/oradata/aaasda/SHIPPING_INDX.ora               15800                0      15799        .94  XXXXXXXXXX
NO  SHIPPING_INDX        /spw/u03/oradata/aaasda/SHIPPING_INDX1.ora              16000                0      15999        .94  XXXXXXXXXX
NO  SHIPPING_INDX        /spw/u03/oradata/aaasda/SHIPPING_INDX3.dbf               8000                0       4299       3637  XXXXX-----
YES SHIPPING_INDX        /spw/u08/oradata/aaasda/SHIPPING_INDX5.dbf              23000            32767      15446       3968  XXXXXXX---
NO  SHIPPING_INDX        /spw/u13/oradata/shpr/SHIPPING_INDX3.dbf                31000                0      30373     616.88  XXXXXXXXXX
NO  SHIPPING_INDX        /spw/u13/oradata/shpr/SHIPPING_INDX4.dbf                30000                0   28999.88       1000  XXXXXXXXXX
NO  SHIPPING_INDX        /spw/u17/oradata/shpr/SHIPPING_INDX2.ora                20000                0   19942.31         16  XXXXXXXXXX
SQL> SQL>
SQL> SQL> SQL> alter database datafile '/spw/u17/oradata/shpr/SHIPPING_INDX2.ora' resize 23000M;

SQL> alter database datafile '/spw/u13/oradata/shpr/SHIPPING_INDX4.dbf' resize 33000M;
alter database datafile '/spw/u13/oradata/shpr/SHIPPING_INDX4.dbf' resize 33000M
*
ERROR at line 1:
ORA-01144: File size (4224000 blocks) exceeds maximum of 4194303 blocks

0
motioneye
Asked:
motioneye
  • 3
1 Solution
 
MikeOM_DBACommented:

Check out the MAXSIZE option for those files and change it if necesary:

SELECT File_Name, TableSpace_Name, MAXBYTES, MAXBLOCKS FROM DBA_DATA_FILES;
0
 
MikeOM_DBACommented:

PS: You are trying to increse the file to 33000M that has a maxsize of 30000M.
0
 
motioneyeAuthor Commented:
Hi MikeOM_DBA
The 30000MB is actually the current size and I trying to add another 3gb than it prompt me an error. Here is the results how it should look like.

Aut Tablespace Name      File Name                                                            Size (M)          Size (MaxMBytes)   Used (M)
--- -------------------- -------------------------------------------------- ---------- ---------------- ---------- ---------- -----------
NO  SHIPPING_INDX        /spw/u13/oradata/shpr/SHIPPING_INDX4.dbf                30000                0                28999.88  
0
 
MikeOM_DBACommented:

Execute this SQL to find out the MAXSIZE:

SELECT File_Name, TableSpace_Name, MAXBYTES, MAXBLOCKS
  FROM DBA_DATA_FILES
 WHERE File_Name = "/spw/u13/oradata/shpr/SHIPPING_INDX4.dbf";
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now