Select D.tablespace_name,space "Sum_space (M)", BLOCKS Sum_blocks,
Used_space "Used_space (M)", ROUND (NVL (used_space,0)/space*100,2) "Used_rate (%)",
NVL (free_space,0) "Free_space (M)"
From
(SELECT tablespace_name,round SUM (BYTES)/(1024*1024), 2) space,sum (BLOCKS) BLOCKS
From Dba_temp_files
GROUP by Tablespace_name) D,
(SELECT tablespace_name,round (SUM (bytes_used)/(1024*1024), 2) Used_space,
ROUND (SUM (bytes_free)/(1024*1024), 2) free_space
From V$temp_space_header
GROUP by Tablespace_name) F
WHERE D.tablespace_name = f.tablespace_name (+)
When a new table is created from the CREATE table in Oracle, the new table has a larger amount of data, such as 1 billion, when Sql*plus is likely to prompt "ORA-01653: ..." error message. This error message implies that the table space is not large enough and requires additional data files for the tablespace.
Analysis:
1. Query table space remaining byte size
SELECT Tablespace_name, SUM (BYTES)/1024/1024 as "free Space (M)"
From Dba_free_space
WHERE tablespace_name = ' &tablespace_name '
GROUP by Tablespace_name;
Note: If it is a temporary table space, please inquire dba_temp_free_space
SELECT Tablespace_name, free_space/1024/1024 as "free Space (M)"
From Dba_temp_free_space
WHERE tablespace_name = ' &tablespace_name ';
2. If you do not know the table space data file directory planning, you can first query the table space all data files
SELECT Tablespace_name, file_id, file_name, bytes/1024/1024 as "BYTES (M)"
From Dba_data_files
WHERE tablespace_name = ' &tablespace_name ';
Note: If it is a temporary table space, please inquire dba_temp_files
SELECT Tablespace_name, file_id, file_name, bytes/1024/1024 as "space (M)"
From Dba_temp_files
WHERE tablespace_name = ' &tablespace_name ';
3. Additional data files for space-deficient tablespaces
ALTER tablespace &tablespace_name ADD datafile ' &datafile_name ' SIZE 2G;
Note: If you want to enlarge the temp table space, use the following statement
ALTER tablespace &tablespace_name ADD tempfile ' &datafile_name ' SIZE 2G;