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