1. View all table space sizes
sql> Select Tablespace_name,sum (bytes)/1024/1024 from Dba_data_files Group by Tablespace_name;
2. The size of the table space that has been idle
sql> Select Tablespace_name,sum (bytes)/1024/1024 from Dba_free_space Group by Tablespace_name;
3. So the use of space can be calculated like this
Select A.tablespace_name,total,free,total-free used from
(select Tablespace_name,sum (bytes)/1024/1024 Total from Dba_data_files
Group by Tablespace_name) A,
(select Tablespace_name,sum (bytes)/1024/1024 free from Dba_free_space
Group BY Tablespace_name) b
where A.tablespace_name=b.tablespace_name;
4. View data files for all tablespaces:
Select Tablespace_name,file_name from Dba_data_files;
5. Four ways to increase the size of the table space
MEATHOD1: Adding data files to table spaces
ALTER tablespace USERS ADD datafile ' +hisdata/his/datafile/users01. DBF ' SIZE 10240M;
MEATHOD2: Add data file and allow data file 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 data files that already exist 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;
This article from "Lone Wolf" blog, reproduced please contact the author!
Oracle Error ora-01658 workaround-Capacity table space