[Oracle] Table space situation view, occupancy, capacity expansion, usage, space maintenance, and other operations

--Querying 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 "Max Block (M)"
From (SELECT Tablespace_name,
Round (Sum (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
Round (Max (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
Round (Sum (DD). BYTES)/(1024x768 * 1024x768), 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 1;
--View Table space object occupancy
Select Segment_name "segment Name",
Segment_type "segment Type",
Tablespace_name "Table Space",
Sum (bytes)/1024/1024 "space occupied (MB)"
From User_extents
Group by Segment_type, Segment_name, Tablespace_name
Order by Sum (bytes)/1024/1024 desc;

--Query The free space of the tablespace
Select Tablespace_name, COUNT (*) as Extends,round (sum (bytes)/1024/1024, 2) as mb,sum (blocks) as blocks from dba_free_s Pace GROUP BY Tablespace_name;

--Query the total capacity of the table space
Select Tablespace_name, sum (bytes)/1024/1024 as MB from Dba_data_files Group by Tablespace_name;
--Querying table space utilization
SELECT Total.tablespace_name,
Round (total. MB, 2) as TOTAL_MB,
Round (total. Mb-free. MB, 2) as USED_MB,
Round (1-free. Mb/total. MB) * 100, 2)
|| '% ' as used_pct
From (SELECT Tablespace_name,
Sum (bytes)/1024/1024 as MB
From Dba_free_space
GROUP by Tablespace_name) free,
(SELECT Tablespace_name,
Sum (bytes)/1024/1024 as MB
From Dba_data_files
GROUP by Tablespace_name) Total
WHERE free.tablespace_name = total.tablespace_name;
--View Table space usage
Select SUM (bytes/1024/1024) sizemb from Dba_free_space z where z.tablespace_name= ' DATA6 ';
--View Index Table space consumption
Select S.index_name,
S.table_name,
S.tablespace_name,
S.initial_extent,
S.next_extent
From User_indexes S
where s.index_name = ' pk_log_inter_called_detail ';
--View tablespace files
Select file_name,tablespace_name,bytes/1024/1024 from Dba_data_files where tablespace_name= ' USERS ';
--View Table space remaining
Select SUM (bytes/1024/1024) sizemb from Dba_free_space z where z.tablespace_name= ' USERS ';
--Modify the tablespace file size
ALTER DATABASE datafile '/HOME/ORACLE/ORADATA/EASYWEB/DATA3_01.DBF ' resize 10000M MAXSIZE UNLIMITED;
ALTER DATABASE datafile '/HOME/ORACLE/ORADATA/EASYWEB/DATA6_01.DBF ' autoextend on NEXT 200M MAXSIZE UNLIMITED;
--View all tablespace files
SELECT file_name,tablespace_name,autoextensible from Dba_data_files;
--Set table space to increase automatically
ALTER DATABASE datafile '/data/oradata/easyweb/users01.dbf ' autoextend on; --Turn on auto-grow
ALTER DATABASE datafile '/data/oradata/easyweb/users01.dbf ' autoextend on NEXT 200M; --200m per auto growth
ALTER DATABASE datafile '/home/oracle/oradata/easyweb/users01.dbf ' autoextend on NEXT 200M MAXSIZE 10000M; --Each auto-growth 200m, the data table maximum not more than 1G
--maxsize UNLIMITED;

--a table space of up to 32g, more than 32g need to increase, for example:
Alter tablespace ZSK ADD datafile '/mc_data/oradata/zsk/zsk_01.dbf ' SIZE 30G;

[Oracle] Table space situation view, occupancy, capacity expansion, usage, space maintenance, and other operations