OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P7: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market. | Subqueries 231 If the scalar subquery returns more than one row the query will fail. If the scalar subquery returns no rows the value is NULL. Finding Total Space and Free Space Using Dictionary Views The following dictionary views are best friends of a DBA. They show the most critical aspect of the database from the user perspective the space allocated and free. If the DBA is not monitoring the growth and free space available in the database it is likely that they might get calls from the user community that they ran out of space in the tablespace. Let s build a query using four dictionary views you may need the SELECT_CATALOG_ROLE privilege to query these views . DBA_TABLESPACES Shows the tablespace name type and so on. DBA_DATA_FILES Shows the data files associated with a permanent or undo tablespace and the size of the data file. The total size of all data files associated with a tablespace gives the total size of the tablespace. DBA_TEMP_FILES Shows the temporary files associated with a temporary tablespace and their size. DBA_FREE_SPACE Shows the unallocated space free space in each tablespace. The query to get the tablespace names and type of tablespace would be as follows column tablespace_name format a18 SELECT tablespace_name contents FROM dba_tablespaces TABLESPACE_NAME CONTENTS SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT To find the total space allocated to each tablespace you need to query DBA_DATA_FILES and DBA_TEMP_FILES. Since you are using a group function SUM along with a nonaggregated column tablespace_name the GROUP BY clause is a must. Notice the use of an arithmetic operation on the aggregated result to display the bytes in megabytes. SELECT tablespace_name SUM bytes 1048576 MBytes FROM dba_data_files 232 Chapter 4 Using Joins and Subqueries GROUP BY tablespace_name TABLESPACE_NAME MBYTES N B SYSAUX USERS SYSTEM EXAMPLE 730 710 100 SELECT tablespace_name SUM bytes 1048576 .
