Database version:
system@TESTDB> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Method 1:
system@TESTDB> @tbs_usage_metrics_10g.sql
system@TESTDB> select * from dba_tablespace_usage_metrics
2 order by used_percent desc;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
PAC_TLGP02M 2726400 2816000 96.8181818
PAC_TLGP52M 4894720 5120256 95.5952202
PAC_TBS11L 88857600 95207040 93.3309133
PAC_CHARGE_HIST_TAXES51 40320000 43499776 92.6901325
PAC_MEMO01M 33925120 37633152 90.1469003
( .... truncated ....)
Method 2
system@TESTDB>
system@TESTDB> @l2/freespace
system@TESTDB> clear columns
system@TESTDB> clear computes
system@TESTDB> clear breaks
system@TESTDB>
system@TESTDB> set verify off
system@TESTDB> set linesize 180
system@TESTDB> set pagesize 40
system@TESTDB>
system@TESTDB> compute sum of fsize on dummy
system@TESTDB> compute sum of totalfree on dummy
system@TESTDB> break on dummy
system@TESTDB>
system@TESTDB> column dummy noprint
system@TESTDB> column ts format a18 heading "Tablespace"
system@TESTDB> column fn format a65 heading "File Name"
system@TESTDB> column fid format 9999 heading "File|ID "
system@TESTDB> column fsize format 99,999,999 heading "File |Size (MB)"
system@TESTDB> column maxfree format 999,999.9 heading "Largest |Free |Frag (MB)"
system@TESTDB> column totalfree format 99,999,999 heading "Total |Free (MB)"
system@TESTDB> column pctused format 999.99 heading "% Used"
system@TESTDB>
system@TESTDB> select null dummy,
2 a.tablespace_name ts,
3 a.file_name fn,
4 a.file_id fid,
5 nvl(a.bytes/1024/1024,0) fsize,
6 nvl(max(b.bytes/1024/1024),0) maxfree,
7 nvl(sum(b.bytes/1024/1024),0) totalfree,
8 (1 - nvl(sum(b.bytes),0)/a.bytes)*100 pctused
9 from dba_data_files a, dba_free_space b
10 where a.tablespace_name = upper('&tablespace')
11 and a.file_id = b.file_id (+)
12 group by a.tablespace_name, a.file_name, a.file_id, a.bytes
13 order by substr(a.file_name, instr(a.file_name,'/',-1) + 1, 40)
14 /
Enter value for tablespace: PAC_TLGP02M
Largest
File File Free Total
Tablespace File Name ID Size (MB) Frag (MB) Free (MB) % Used
------------------ ----------------------------------------------------------------- ----- ----------- ---------- ----------- -------
PAC_TLGP02M /ora_44a_02/ORACLE/TESTDB/dbf/tlgp02mTESTDB_01.dbf 179 22,000 690.0 8,680 60.55
----------- -----------
22,000 8,680
The following query list all tablespace usage by method 2
system@TESTDB> set echo on
system@TESTDB> @tf2
system@TESTDB> col tsname format a24 justify c heading 'Tablespace'
system@TESTDB> col nfrags format 9,999,990 justify c heading 'Free|Frags'
system@TESTDB> col mxfrag format 999,990.999 justify c heading 'Largest|Free Frag'
system@TESTDB> col totsiz format 9,999,990.999 justify c heading 'Total|Mbytes'
system@TESTDB> col avasiz format 9,999,990.999 justify c heading 'Available|Mbytes'
system@TESTDB> col pctusd format 990.99 justify c heading 'Percent|Used'
system@TESTDB>
system@TESTDB> set pagesize 200
system@TESTDB> set linesize 120
system@TESTDB>
system@TESTDB> select
2 total.tablespace_name tsname,
3 count(free.bytes) nfrags,
4 round(nvl(max(free.bytes)/1048576,0),2) mxfrag,
5 total.bytes/1048576 totsiz,
6 round(nvl(sum(free.bytes)/1048576,0),2) avasiz,
7 round((1-nvl(sum(free.bytes),0)/total.bytes)*100,2) pctusd
8 from
9 (select tablespace_name, sum(bytes) bytes
10 from dba_data_files
11 group by tablespace_name) total,
12 dba_free_space free
13 where
14 --total.tablespace_name in ('TBS01S','TBS01M','TBS01L','TBS51S','TBS51M','TBS51L')
15 --and
16 total.tablespace_name = free.tablespace_name(+)
17 group by
18 total.tablespace_name,
19 total.bytes
20 order by 6 desc, 1
21 /
Free Largest Total Available Percent
Tablespace Frags Free Frag Mbytes Mbytes Used
------------------------ ---------- ------------ -------------- -------------- -------
PAC_TBS11L 32 21,200.000 743,805.000 49,400.000 93.36
PAC_CHARGE_HIST_TAXES51 5 11,200.000 339,842.000 24,500.000 92.79
PAC_MEMO01M 4 24,160.000 294,009.000 28,920.000 90.16
PAC_TBS61L 5 11,400.000 360,014.000 36,000.000 90.00
PAC_CHARGE_TAXES51 2 2,210.000 40,768.000 4,150.000 89.82
PAC_TBS61M 31 10,570.000 175,771.000 18,030.000 89.74
( ... truncated ... )
PAC_TLGP52M 1,348 1,760.000 40,002.000 15,230.000 61.93
PAC_TLGP02M 800 690.000 22,000.000 8,680.000 60.55
( ... truncated .... )
No comments:
Post a Comment