Oracle database, Summary of common SQL statements

--View tablespace names, path, size, and usage of data files
Select B.tablespace_name "tablespace name",
B.file_name "Data file path",
b.bytes/1024/1024 "Size (M)",
SUBSTR ((B.bytes-sum (NVL (a.bytes,0))/(b.bytes) *100,1,5) "Utilization"
From Dba_free_space A,dba_data_files b
where a.file_id (+) =b.file_id
GROUP BY B.tablespace_name,b.file_name,b.bytes
Order by 1, 2;

--Increase table space size

--First you need to query the table space name and the path to the data file, and then execute the following command

Alter Tablespace "USERS" Add datafile ' D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS02. DBF ' size 10240M;

Alter temporary tablespace "TEMP" add tempfile ' D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02. DBF ' size 1024M;

Oracle database, Summary of common SQL statements