Insufficient use of Oracle tablespace and Solutions

Insufficient use of Oracle tablespace and solutions 1. how to query table space: www.2cto.com select B. file_id, B. tablespace_name, B. file_name, B. bytes, (B. bytes-sum (nvl (. bytes, 0), sum (nvl (. bytes, 0), sum (nvl (. bytes, 0)/(B. bytes) * 100 from dba_free_space a, dba_data_files B where. file_id = B. file_id group by B. tablespace_name, B. file_name, B. file_id, B. bytes order by B. tablespace_name; or select fs. tablespace_name, max (round (1-freespace/totalspace) * 100, 2) ratio, totalspace from (select tablespace_name, round (sum (bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name, round (sum (bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df. tablespace_name = fs. tablespace_name (+) and fs. tablespace_name not in ('undotbs2', 'undotbs1', 'sysaux ') group by fs. tablespa Ce_name, totalspace order by 2 desc; www.2cto.com 2. add tablespace: view the data file storage path: select file_name from dba_data_files; solution 1: increase data files-increase the size of a data file in the corresponding tablespace to *** M alter database datafile' full path data file name 'resize *** M; solution 2: add the data file alter tablespace name add datafile 'full path data file name 'size *** M; -- add a new data file, the full path data file name is the full path File Name of the new data file. The size is *** M, and the value is set. PS: Oracle tablespaces generally keep the free percentage above 10%. The current ratio in MDMC is 25%. If it is lower, the datafile or resieze datafile will be added. Generally, the data file size should not exceed 2 GB.