View two common SQL statements used by the tablespace and two SQL statements used by the tablespace
1. view the tablespace size:
SELECT tablespace_name, SUM (bytes)/1024/1024 total FROM DBA_FREE_SPACE group by tablespace_name order by 2 DESC;
SQL> SELECT tablespace_name, SUM (bytes)/1024/1024 | 'mb' total FROM DBA_FREE_SPACE GROUP BY tablespace_name ORDER BY 2 DESC;
Note the sorting of the preceding two sqls. Obviously, the first SQL is the result we need and is sorted in descending order by the tablespace size. The reason why the second SQL statement is disordered is that | 'mb' is used to connect the string, this field is searched as a string type,
Sort by ASCII characters..
2. view the tablespace usage:
SQL> BREAK ON REPORTSQL> COMPUT SUM OF tbsp_size ON REPORTSQL> compute SUM OF used ON REPORTSQL> compute SUM OF free ON REPORT
SQL> COL tbspname FORMAT a20 HEADING 'tablespace name' SQL> COL tbsp_size FORMAT 999,999 HEADING 'size | (MB) 'SQL> COL used FORMAT 999,999 HEADING 'used | (MB) 'SQL> COL free FORMAT 999,999 heading' Free | (MB)' SQL> COL pct_used FORMAT 999,999 HEADING '% used'
SQL> SELECT df. tablespace_name tbspname, sum (df. bytes)/1024/1024 tbsp_size, nvl (sum (e. used_bytes)/1024/1024, 0) used, nvl (sum (f. free_bytes)/1024/1024, 0) free, nvl (sum (e. used_bytes) * 100)/sum (df. bytes), 0) pct_usedFROM DBA_DATA_FILES df, (SELECT file_id, SUM (nvl (bytes, 0) used_bytesFROM dba_extentsGROUP BY file_id) e, (select max (bytes) free_bytes, file_idFROM dba_free_spaceGROUP BY file_id) fWHERE e. file_id (+) = df. file_idAND df. file_id = f. file_id (+) group by df. tablespace_nameORDER BY 5 DESC;
View definition:
