Background:
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;