中烟141

mkdir /oracle/BIP/sapdata2/sr3_110
mkdir /oracle/BIP/sapdata2/sr3_111
mkdir /oracle/BIP/sapdata2/sr3_112

ALTER TABLESPACE PSAPSR3 ADD DATAFILE '/oracle/BIP/sapdata2/sr3_110/sr110.data110' SIZE 20G;
ALTER TABLESPACE PSAPSR3 ADD DATAFILE '/oracle/BIP/sapdata2/sr3_111/sr111.data111' SIZE 20G;
ALTER TABLESPACE PSAPSR3 ADD DATAFILE '/oracle/BIP/sapdata2/sr3_112/sr112.data112' SIZE 20G;

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Check free/used space per tablespace

Displays Space Usage for Each Datafile

SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN "Tablespace Name" FORMAT A20 COLUMN "File Name" FORMAT A80

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",

• Substr(df.file_name,1,80) "File Name", Round(df.bytes/1024/1024,0) "Size (M)", decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)", decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)", decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"

FROM DBA_DATA_FILES DF,

• (SELECT file_id,
  • sum(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 e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) ORDER BY df.tablespace_name,

• df.file_name

/ }}}