Amped Labs LLC
Home
Links
Advanced Search
Contact Us
Mobile News
MobileTracker
Cell phone news and reviews
Login Form
Username

Password

Remember me
Password Reminder
No account yet? Create one
Latest News
 
Oracle Tablespace and Datafiles Report PDF Print E-mail
Thursday, 28 August 2008

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

 
 
Stats