View temporary tablespace usage and how to extend tablespace temp
SELECT D. TABLESPACENAMEMagneSpace "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 passing through create table in ORACLE. When the as select statement creates a new table, the amount of data in the new table is relatively large, such as 1 billion, and SQL*Plus will probably prompt "ORA-01653:..." Error message. This error message implies that the tablespace is not large enough and that a data file needs to be added to the tablespace.
Analysis:
1. Query tablespace remaining byte size
SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 plus 1024 AS "FREE SPACE (M)"
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME ='& tablespace_name'
GROUP BY TABLESPACE_NAME
Note: if it is a temporary tablespace, please query 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'
two。 If you don't know the table space data file directory planning, you can query all the data files in the table space first.
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 tablespace, please query 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. Add data files for insufficient tablespaces
ALTER TABLESPACE & tablespace_name ADD DATAFILE'& datafile_name' SIZE 2G
Note: if you want to expand the temporary tablespace, use the following statement
ALTER TABLESPACE & tablespace_name ADD TEMPFILE'& datafile_name' SIZE 2G
