How to view the size of the Oracle database table space (idle, used), whether to increase the tablespace's data file

To see the size of the Oracle database table space, you need to increase the table space data files, in database Management , the lack of disk space is a problem that DBAs encounter, the problem is more common.

--1, viewing the percentage of table space already used

SQL code

2. 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) *100,2) "percent_used" from

4. (Select tablespace_name,sum(bytes) bytes from dba_data_files group by Tablespace_name) A,

6. (Select tablespace_name,sum(bytes) bytes,Max(bytes) largest from Dba_free_space Group by Tablespace_name) b

8. where A.tablespace_name=b.tablespace_name

10. Order    by   ((a.bytes-b.bytes)/a.bytes) desc

"Sum MB" means that all data files in the tablespace are in total size of disk space in the operating system

For example: Test table space has 2 data files, Datafile1 is 300mb,datafile2 to 400MB, then the test table space "Sum MB" is 700MB
"UserD MB" indicates how much table space has been used
"Free MB" indicates how much of the table space is left
"Percent_user" indicates the percentage that has been used

--2, such as viewing from 1 to Mlog_norm_space table space has used a percentage of more than 90% , you can see the total number of table space

According to the file, each data file is automatically expanded to the maximum value that can be automatically expanded.

SQL code

1. Select file_name,tablespace_name,bytes/1024/1024 "bytes mb", maxbytes/1024/1024 "MaxBytes MB" from Dba_data_files

2. where tablespace_name=' mlog_norm_space ';

--2.1, see if the XXX table space is automatically extended

SQL code

1. Select file_id,file_name,tablespace_name,autoextensible,increment_by from Dba_data_files Order by file_id desc;

--3, such as the Mlog_norm_space table space is currently 19GB, but the maximum of each data file can only be 20GB, the data file is almost full, you can increase the table space data files
Use the OS unix, Linux df-g command (view the amount of disk space you can use)
Gets the statement that created the tablespace:

SQL code

1. Select dbms_metadata.get_ddl (' tablespace ',' Mlog_norm_space ') from dual;

--4 confirm enough disk space to add a data file

SQL code

1. Alter tablespace mlog_norm_space

2. add datafile '/oracle/oms/oradata/mlog/mlog_norm_data001.dbf '

3. size 10M autoextend on MaxSize 20G

--5 verifying data files that have been added

SQL code

1. Select file_name,file_id,tablespace_name from Dba_data_files

2. where tablespace_name=' mlog_norm_space '

--6 If you delete a tablespace data file, as follows:

SQL code

1. Alter tablespace mlog_norm_space

2. drop datafile '/oracle/oms/oradata/mlog/mlog_norm_data001.dbf '

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1679749

How to view the size of the Oracle database table space (idle, used), whether to increase the tablespace's data file