View the size and space usage of an Oracle database(2012-06-19 14:44:30)
reproduced
| Tags: gossip |
Category: Oracle |
1. View the usage status of table spaceSelect Upper (F.tablespace_name) tablespace name,  D.TOT_GROOTTE_MB "tablespace size (M)", D.tot_grootte_mb-f.total_bytes "used Space (M)", round ((d.tot_grootte_mb-f.total_bytes)/d.Tot_ GROOTTE_MB * 100, 2) "use ratio", f.total_bytes "free Space (M)", f.max_bytes "Max Block (M) " from (SELECT tablespace_name, round (SUM (bytes)/(1024 * 1024x768), 2) total_bytes, round (MAX (bytes)/(1024x768 * 1024x768), 2) max_by tes from sys.dba_free_space group by Tablespace_name) F , (SELECT dd.tablespace_name, round (SUM (d d.bytes)/(1024x768 * 1024x768), 2) tot_grootte_mb from Sys.dba_data_files dd &nbs P group by DD.Tablespace_name) D where d.tablespace_name = F.tablespace_name order by 4 desc 2. View segments that cannot be expandedSelect Segment_name, Segment_type, owner, A.tablespace_name "Tablespacename", INITIAL_EXTENT/10 "Inital_extent (k)", next_extent/1024 "Next_extent (k)", Pct_increase, b.bytes/1024 "tablespace m Ax free space (k) ', b.sum_bytes/1024 ' total free space (k) ' from Dba_segments A, (SELECT Tablespace_nam E, MAX (bytes) bytes, SUM (bytes) sum_bytes from Dba_free_space GROUP by Tablespace_name) b WHERE a.tablesp Ace_name = B.tablespace_name and next_extent > B.bytes ORDER by 4, 3, 1
View the size and space usage of an Oracle database
