Oracle queries table space usage

Select a. tablespace_name, a. bytes bytes_used, B. largest, round (a. bytes-B. bytes)/a. bytes) * 100,2) percent_used
From (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name order by (a. bytes-B. bytes)/a. bytes) desc

 

 

Select a. tablespace_name, a. bytes/1024/1024 "Sum MB", (a. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB ",
Round (a. bytes-b.bytes)/a. bytes) *) "percent_used"
From
(Select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-b.bytes)/a. bytes) desc

Query the total capacity, used, remaining, and used percentage of all tablespaces!

Select a. tablespace_name, a. bytes/1024/1024 "Sum MB", (a. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB ",
Round (a. bytes-b.bytes)/a. bytes) *) "percent_used"
From
(Select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-b.bytes)/a. bytes) desc

Generally, you can put the preceding complex query statement into a file and call it when necessary, or create an attempt to query it when necessary.
1. Write the file: # vi/home/mzl/percent_used_tablespace. SQL
Content:
Select a. tablespace_name, a. bytes/1024/1024 "Sum MB", (a. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB ",
Round (a. bytes-b.bytes)/a. bytes) *) "percent_used"
From
(Select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-b.bytes)/a. bytes) desc

2 import:
SQL> @/home/mzl/percent_used_tablespace. SQL
SQL> l
1 select a. tablespace_name, a. bytes "Sum", a. bytes-b.bytes "used", B. bytes "free ",
2 round (a. bytes-b.bytes)/a. bytes) *, 2) "percent_used"
3 from
4 (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
5 (select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
6 where a. tablespace_name = B. tablespace_name
7 * order by (a. bytes-b.bytes)/a. bytes) desc
SQL>/

Or create a view:
SQL> create view percent
SQL>
SQL> select a. tablespace_name, a. bytes/1024/1024 "Sum MB", (a. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB ",
SQL> round (a. bytes-b.bytes)/a. bytes) *, 2) "percent_used"
SQL> from
SQL> (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
SQL> (select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
SQL> where a. tablespace_name = B. tablespace_name
SQL> order by (a. bytes-b.bytes)/a. bytes) desc;

SQL> select * from percent;

Check whether the data files in the tablespace are automatically expanded:
SQL> l
1 * select file_name, tablespace_name, autoextensible from dba_data_files
SQL>/

FILE_NAME TABLESPACE_NAME AUT
------------------------------------------------------------------------------
/U01/app/oracle/oradata/orcl/risenet. dbf RISENET
/U01/app/oracle/oradata/orcl/perfstat. dbf PERFSTAT NO
/U01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
/U01/disk1/users01.dbf USERS YES
/U01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
/U01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/U01/disk2/system01.dbf SYSTEM YES
/U01/app/oracle/oradata/orcl/undotbs02.dbf UNDOTBS2 NO
/U01/disk1/pioneer_data.dbf PIONEER_DATA YES
/U01/disk2/pioneer_indx.dbf PIONEER_INDX NO
/U01/disk3/pioneer_undo.dbf PIONEER_UNDO NO

FILE_NAME TABLESPACE_NAME AUT
------------------------------------------------------------------------------
/U01/app/oracle/oradata/orcl/paul01.dbf PAUL NO
/U01/disk1/wenchuan. dbf WENCHUAN NO

13 rows selected.

For example, if the table space PIONEER_INDX has already used 83.33%, the data file cannot be automatically extended and can be changed to auto-extended to avoid data files being fully written.
SQL> alter database
2 datafile '/u01/disk2/pioneer_indx.dbf' autoextend on;

Database altered.

SQL> select file_name, tablespace_name, autoextensible from dba_data_files
2 where tablespace_name = 'Pioneer _ INDX ';

FILE_NAME TABLESPACE_NAME AUT
------------------------------------------------------------------------------
/U01/disk2/pioneer_indx.dbf PIONEER_INDX YES

Or add an auto-extended data file to the tablespace. If there are multiple hard disks, you can add multiple data files (in this way, the concurrency of multiple database systems is better)
SQL> alter tablespace pioneer_indx
2 add datafile size 30 M;

Tablespace altered.

SQL> select file_name, tablespace_name, bytes/1024/1024 "MB" from dba_data_files
2 where tablespace_name = 'Pioneer _ INDX ';

FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------
MB
----------
/U01/disk2/pioneer_indx.dbf PIONEER_INDX
6

/U01/disk5/ORCL/datafile/o1_mf_pioneer _ 45dpy PIONEER_INDX
Fty _. dbf
30