Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.