Oracle Tutorial: ORA-01653 error Solution

1. ---- Query table space usage ---
3. SELECT UPPER(F. TABLESPACE_NAME)"Tablespace name",
4. D. TOT_GROOTTE_MB"Tablespace size (MB )",
5. D. TOT_GROOTTE_MB-F. TOTAL_BYTES"Used space (M )",
6. TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100,2 ),'1970. 99')"Usage ratio",
7. F. TOTAL_BYTES"Free Space (M )",
8. F. MAX_BYTES"Maximum block (M )"
9. FROM(SELECTTABLESPACE_NAME,
10. ROUND (SUM(BYTES) // (1024*1024), 2) TOTAL_BYTES,
11. ROUND (MAX(BYTES)/(1024*1024), 2) MAX_BYTES
12. FROMSYS. DBA_FREE_SPACE
13. GROUP BYTABLESPACE_NAME) F,
14. (SELECTDD. TABLESPACE_NAME,
15. ROUND (SUM(DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB
16. FROMSYS. DBA_DATA_FILES DD
17. GROUP BYDD. TABLESPACE_NAME) D
18. WHERED. TABLESPACE_NAME = F. TABLESPACE_NAME
19. ORDER BY4DESC;

 

1. ---- Query table space usage ---
3. SELECT UPPER(F. TABLESPACE_NAME)"Tablespace name",
4. D. TOT_GROOTTE_MB"Tablespace size (MB )",
5. D. TOT_GROOTTE_MB-F. TOTAL_BYTES"Used space (M )",
6. TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100,2 ),'1970. 99')"Usage ratio",
7. F. TOTAL_BYTES"Free Space (M )",
8. F. MAX_BYTES"Maximum block (M )"
9. FROM(SELECTTABLESPACE_NAME,
10. ROUND (SUM(BYTES) // (1024*1024), 2) TOTAL_BYTES,
11. ROUND (MAX(BYTES)/(1024*1024), 2) MAX_BYTES
12. FROMSYS. DBA_FREE_SPACE
13. GROUP BYTABLESPACE_NAME) F,
14. (SELECTDD. TABLESPACE_NAME,
15. ROUND (SUM(DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB
16. FROMSYS. DBA_DATA_FILES DD
17. GROUP BYDD. TABLESPACE_NAME) D
18. WHERED. TABLESPACE_NAME = F. TABLESPACE_NAME
19. ORDER BY4DESC;

SQL code

1. -- Check whether the tablespace can be automatically expanded.
2. SELECTT. TABLESPACE_NAME, D. FILE_NAME,
3. D. AUTOEXTENSIBLE, D. BYTES, D. MAXBYTES, D. STATUS
4. FROMDBA_TABLESPACES T, DBA_DATA_FILES D
5. WHERET. TABLESPACE_NAME = D. TABLESPACE_NAME
6.  ORDER BYTABLESPACE_NAME, FILE_NAME;

 

1. -- Check whether the tablespace can be automatically expanded.
2. SELECTT. TABLESPACE_NAME, D. FILE_NAME,
3. D. AUTOEXTENSIBLE, D. BYTES, D. MAXBYTES, D. STATUS
4. FROMDBA_TABLESPACES T, DBA_DATA_FILES D
5. WHERET. TABLESPACE_NAME = D. TABLESPACE_NAME
6.  ORDER BYTABLESPACE_NAME, FILE_NAME;

SQL code

1. -- Modify the extension mode of tablespace files:
2. ALTER DATABASE
3. DATAFILE'/U01/Oracle/oradata/orcl/ccen01.dbf'AUTOEXTEND
4. ON NEXT50 M MAXSIZE UNLIMITED

 

1. -- Modify the extension mode of tablespace files:
2. ALTER DATABASE
3. DATAFILE'/U01/oracle/oradata/orcl/ccen01.dbf'AUTOEXTEND
4. ON NEXT50 M MAXSIZE UNLIMITED

The query solution on the internet is as follows:
1. Enable the tablespace Extension function;

2. Increase the tablespace space;

3. Add data files to the tablespace;