Oracle Database error ora-01653 table space extension failure solution

1) ora-01653 error:

You can see that inserts with two tables are affected, both in the Users table space. Use the following SQL to view Tablespace usage:

SELECTA.tablespace_name "tablespace name", A.bytes/ 1024x768 / 1024x768"Tablespace size (M)", (a.bytes-B.bytes)/ 1024x768 / 1024x768"used Space (M)", B.bytes/ 1024x768 / 1024x768"Free Space (M)",round(((A.bytes-B.bytes)/A.bytes)*  -,2) "Use ratio" from(SELECTTablespace_name,sum(bytes) bytes fromDba_data_filesGROUP  byTablespace_name) A, (SELECTTablespace_name,sum(bytes) Bytes,Max(bytes) Largest fromDba_free_spaceGROUP  bytablespace_name) bWHEREA.tablespace_name=B.tablespace_nameORDER  by((a.bytes-B.bytes)/A.bytes)DESC;

You can see that the users table space is already using 99.99%! Immediately 100%, no more places to store data! Formal Environment Ah! It's a critical situation!

2) Workaround

-- Get data file dbf path Select file_id,file_nameround(bytes/(1024x768),0  fromorder by Tablespace_name;

The following two scenarios all need to get the actual physical size under the/home directory, after I log on to the Linux database server, use the DU-SH command to see the/home directory size of 250GB.

The first of these solutions:

You can see that the users table space has a total of 3 dbf (at this point I have added 04.dbf), copy one of the DBF path/home/oracle/oradata/users01.dbf, overwriting the following datafile parameter:

--A new DBF file is added, specifying that the DBF file size is around 32GB, and that it automatically expands 1GB each time, and there is no maximum limit Alter Add ' /home/oracle/oradata/users04.dbf '  on Next  --segment Space Management auto Extent management Local; It is said that the previous sentence can be connected to execute (remove the previous semicolon), did not execute, do not know the effect of execution , cautious during the period, use caution. 

Second Solution:

-- Resize (enlarge) The original DBF data file Alter Database  ' /home/oracle/oradata/users01.dbf ' resize  -- never tried, didn't know the effect

I use the first solution, the second one has not tried, do not know how the effect.

3) Post-resolution effect

The first solution I used was to increase the 04.DBF and use SQL again to view the Tablespace usage:

SELECTA.tablespace_name "tablespace name", A.bytes/ 1024x768 / 1024x768"Tablespace size (M)", (a.bytes-B.bytes)/ 1024x768 / 1024x768"used Space (M)", B.bytes/ 1024x768 / 1024x768"Free Space (M)",round(((A.bytes-B.bytes)/A.bytes)*  -,2) "Use ratio" from(SELECTTablespace_name,sum(bytes) bytes fromDba_data_filesGROUP  byTablespace_name) A, (SELECTTablespace_name,sum(bytes) Bytes,Max(bytes) Largest fromDba_free_spaceGROUP  bytablespace_name) bWHEREA.tablespace_name=B.tablespace_nameORDER  by((a.bytes-B.bytes)/A.bytes)DESC;

The use ratio has been reduced from 99.99% to 69.42%, indicating that the effect is obvious and the scheme is available.

To check home/home, use the Du-sh command to view the/home directory size of 282GB. Originally adding a DBF file will make the/home directory actually increase the size of 32GB.

4) Precautions and all SQL used

In the first solution above, the 32760m is approximately equal to 32GB, calculated based on the maximum block, and the block calculates the SQL:

SELECT UPPER(F.tablespace_name) Tablespace name, D.TOT_GROOTTE_MB table space Size (M), D.TOT_GROOTTE_MB-f.total_bytes "used Space (M)", To_char (ROUND((D.TOT_GROOTTE_MB-F.total_bytes)/D.tot_grootte_mb*  -,2),'990.99')|| '%'"Use ratio", f.total_bytes "free Space (M)", F.max_bytes "Max Block (m)" from(SELECTTablespace_name,ROUND(SUM(BYTES)/(1024x768 * 1024x768),2) Total_bytes,ROUND(MAX(BYTES)/(1024x768 * 1024x768),2) Max_bytes fromSYS. Dba_free_spaceGROUP  bytablespace_name) F, (SELECTDD. Tablespace_name,ROUND(SUM(DD. BYTES)/(1024x768 * 1024x768),2) TOT_GROOTTE_MB fromSYS. Dba_data_files DDGROUP  byDD. Tablespace_name) DWHERED.tablespace_name=F.tablespace_nameORDER  by 1;

Other SQL statements are fully affixed as follows:

-- -Querying data files and data file sizes Select file_id file_name,round(bytes/(1024x768), 0 from  order by Tablespace_name; Select  from Dba_users;

--Get data file pathSelectTablespace_name,file_id,file_name,round(bytes/(1024x768*1024x768),0) Total_space fromDba_data_filesOrder  byTablespace_name;--Let the data file expand automaticallyAlterTablespace usersAddDataFile'/home/oracle/oradata/users04.dbf'Size 32760m Autoextend on Next1024m MaxSize Unlimited;--Segment Space Management auto Extent management Local; It is said that the previous sentence can be connected with the execution (the last semicolon is removed), did not execute, do not know the effect of implementation, cautious period, use caution. --Resize the data fileAlter DatabaseDataFile'/home/oracle/oradata/users01.dbf'Resize 61440M;--I haven't tried, I don't know the effect .

Oracle Database error ora-01653 table space extension failure solution