ORA-01653 solutions that cannot be extended in table space--Increase the size of the table space or add data files to the table space

The current system data volume is getting bigger and larger, yesterday also ran the normal database, suddenly cannot use. After locating the discovery is "ORA-01653" namely the table space is full, here simply records the processing method, the long-term view Oracle is unable to withstand, needs to cut to the Hadoop hbase to store the data as soon as possible

Refer to the following SQL

----query table space usage---   
  
select UPPER (f.tablespace_name) Table space 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 * 100,2), ' 990.99 ') "use ratio",   
f.total_bytes "free Space (M)",   
f.max_bytes "maximum block (m)" from  
(SELECT tablespace_name,   
ROUND ( SUM (BYTES)/(1024 * 1024), 2) total_bytes,   
ROUND (MAX (BYTES)/(1024 * 1024), 2) max_bytes from   
SYS. Dba_free_space   
GROUP by Tablespace_name) F,   
(SELECT DD. Tablespace_name,   
ROUND (SUM (DD). BYTES)/(1024 * 1024), 2) tot_grootte_mb from   
SYS. Dba_data_files DD   
GROUP by DD. Tablespace_name) D   
WHERE d.tablespace_name = f.tablespace_name ORDER by   
4 DESC;  

--To see if the table space has the ability to automatically expand   
SELECT t.tablespace_name,d.file_name,   
d.autoextensible,d.bytes,d.maxbytes,d.status From   
dba_tablespaces t,dba_data_files D   
WHERE t.tablespace_name =d.tablespace_name ORDER by   
 Tablespace_name,file_name;  

--Modify table Space file expansion mode:   
   alter DATABASE  
    datafile '/u01/oracle/oradata/orcl/ccen01.dbf ' autoextend on   
    NEXT 50M MAXSIZE Unlimited  

Four ways to increase the size of the table space
MEATHOD1: Adding data files to the table space

ALTER tablespace app_data ADD datafile
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03. DBF ' SIZE 50M;

MEATHOD2: Add data files and allow data files to grow automatically

ALTER tablespace app_data ADD datafile
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04. DBF ' SIZE 50M
autoextend on NEXT 5M MAXSIZE 100M;

MEATHOD3: Allow existing data files to grow automatically

ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03. DBF '
autoextend on NEXT 5M MAXSIZE 100M;

MEATHOD4: Manually changing the size of existing data files

ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02. DBF '
RESIZE 100M;

Reference:

Http://www.linuxidc.com/Linux/2011-08/39834.htm

Http://www.2cto.com/database/201109/103101.html