Oracle Common Operations

1. View Tablespace usage information

Select A.tablespace_name "Table space name",

Total "Table space size",
Free "Table space remaining size",
(total-free) "Table space usage size",
Total /(1024 * 1024 * 1024) "Table space Size (G)",
Free /(1024 * 1024 * 1024) "Table space remaining size (G)",
(Total-free)/(1024 * 1024 * 1024) "Table space use Size (G)",
round ((total-free)/Total, 4) * 100 "Utilization%"
From (SELECT tablespace_name, SUM (bytes) free
From dba_free_space
GROUP by Tablespace_name) A,
(SELECT tablespace_name, SUM (bytes) Total
From dba_data_files
GROUP by Tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

2. See if the tablespace has the ability to scale automatically

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;

3. Extending Table Space Operations

(1) Add data file to table space

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

(2) 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;

(3) Allow automatic growth of existing data files

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

(4) Manually change the size of the existing data file

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

Oracle Common Operations