|
Amped Labs is happy to announce we are providing an opensource report for gathering tablespace data on your Oracle database. Run the report from sqlplus as a user with DBA permissions. No waranties expressed or implied, use at your own risk.
REM Copyright 2008 Amped Labs LLC REM Licensed under the Apache License, Version 2.0 (the "License"); REM you may not use this file except in compliance with the License. REM You may obtain a copy of the License at REM http://www.apache.org/licenses/LICENSE-2.0 REM Unless required by applicable law or agreed to in writing, software REM distributed under the License is distributed on an "AS IS" BASIS, REM WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. REM See the License for the specific language governing permissions and REM limitations under the License. SET HEAD ON SET VERIFY OFF spool AmpedTablespaceReport.txt col tspace form a25 Heading "Tablespace" col tot_ts_size form 99999999999999 Heading "Size (Mb)" col free_ts_size form 99999999999999 Heading "Free (Mb)" col ts_pct form 9999 Heading "% Free" col ts_pct1 form 9999 Heading "% Used" column FILE_NAME format a80 set pagesize 300 set linesize 300 break on report compute sum of free_ts_size on report compute sum of tot_ts_size on report SELECT /* + RULE */ df.tablespace_name tspace, df.file_name, df.bytes/(1024*1024) tot_ts_size, sum(fs.bytes)/(1024*1024) free_ts_size, nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct, round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1 FROM dba_free_space fs , (select file_name, tablespace_name, sum(bytes) bytes from dba_data_files group by file_name, tablespace_name ) df WHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.file_name, df.tablespace_name, df.bytes union all SELECT /* + RULE */ 'Temp Space', df.tablespace_name tspace, fs.bytes/(1024*1024) tot_ts_size, sum(df.bytes_free)/(1024*1024) free_ts_size, nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct, round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1 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 1,2,4 desc / spool off
|