Oracle View table space size and its extensions

In the Oracle database, all the data from the logical structure is stored in the table space, of course, the table space is also a section, area, block and other logical structure. From the physical structure is placed in the data file. A table space can consist of multiple data files. Several table spaces created by default in the system: System,Sysaux,users,UNDOTBS1,EXAMPLE,temp, and thetable space that the user has built themselves, It can be divided into three categories

Permanent table space holds persistent data, such as tables, indexes, and so on.

Temporary table spaces cannot hold persistent objects, which are used to hold database sorting and to create temporary data when grouped.

Undo table Space holds the mirror image before the data is modified.

We can view the basic information through the following system views:

--Contains descriptive information for all table spaces in the database

SELECT * from Dba_tablespaces

--Contains the information describing the current user's tablespace

SELECT * from User_tablespaces

--Contains the tablespace name and number information obtained from the control file

SELECT * from V$tablespace;

View data files

--Contains descriptive information about the data file and the table space to which it belongs

SELECT * from Dba_data_files

--Contains descriptive information about the temporary data file and the table space to which it belongs

SELECT * from Dba_temp_files

--Contains basic information about the data file obtained from the control file, including the table space name, number, etc. to which it belongs

SELECT * from V$datafile

--Contains basic information for all temporary data files

SELECT * from V$tempfile

You can use the following script to view database table space usage:

SQL1:

SELECT DBF. tablespace_name,       DBF. Totalspace "Total (M)",       DBF. Totalblocks as total block number,       DBF. Totalspace-dfs. FREESPACE "Usage (M)",       DBF. Totalblocks-dfs. Freeblocks as uses blocks,             dfs. FREESPACE "Total remaining (M)",       dfs. Freeblocks "Number of remaining blocks",        (DFS. Freespace/dbf. Totalspace) * 100 "idle scale"   from (select t.tablespace_name,               sum (T . BYTES)/1024/1024 totalspace,               sum (t.blocks) totalblocks  &NB Sp       from Dba_data_files t         group by T.tablespace_name) dbf,        (SELECT TT. tablespace_name,               sum (TT. BYTES)/1024/1024 freespace,               sum (TT. BLOCKS) freeblocks          from Dba_free_space tt         group by TT. Tablespace_name) Dfs where TRIM (DBF. Tablespace_name) = TRIM (DFS. Tablespace_name);  SQL2:SELECT A.tablespace_name, a.bytes/1024/1024 total, b.bytes/1024/1024 used, c.bytes/1024/1024 FR EE, (B.bytes *)/a.bytes "% used", (c.bytes * +)/a.bytes "% free" from SYS. Sm$ts_avail A, SYS. Sm$ts_used B, SYS. Sm$ts_free C WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name = C.tablespace_name; SQL3:Select Total.name "tablespace name", Free_space, (total_space-free_space) used_space, Total_space FRO M (select Tablespace_name, sum (bytes/1024/1024) free_space from Sys.dba_free_space GROUP by Tablespac E_name) Free, (select B.name, sum (bytes/1024/1024) Total_space from Sys.v_$datafile A, Sys.v_$tablespac E B where a.ts# = b.ts# GROUP by b.name) Total where free.tablespace_name = Total.name;

If you find that the table space is almost full, you need to increase the table space. To increase the table space, follow these steps:

1. This tablespace is a large file table space

Oracle introduced the concept of large file table space, the normal table space data files can have many, each size of 32G, and large file table space data file only one, so if the table space is a large file table space, you cannot increase the data file, can only increase the file size.

Select Bigfile from dba_tablespaces where tablespace_name= ' table space name '----------------- return Yes is a large file table space, return no, is the normal table space (small file).
In general, big Data File table space is automatically expanded, do not need to increase the size manually,
The select * from Dba_data_files------------has a autoextensible field that indicates whether it is automatically extended, and Maxblocks indicates how large each auto-expansion

If not, increase the size of the data file to increase the tablespace
First look at the name of the tablespace and where it is located:

Select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_spacefrom Dba_data_filesorder by Tablespace_name increase the required tablespace size: ALTER DATABASE datafile ' table space Location ' Resize new dimensions such as ALTER DATABASE DataFile ' \oracle\oradata\ Test.dbf ' Resize 4000m2. This tablespace is a normal file tablespace this type of table space is increased by 2 methods, one is to add data files to the tablespace, and the other is to increase the size of the existing data file in the tablespace. Two methods, one is to add data files to the tablespace:
The code is as follows: Alter TABLESPACE users add datafile '/opt/oracle/oradata/esop/test02.dbf ' size 200M; Another way is to increase the size of the table space's original data file:
The code is as follows: Alter DATABASE datafile '/OPT/ORACLE/ORADATA/ESOP/TEST01.DBF ' resize 200M; In general, table space growth is set to automatic and can be set with parameters

Increase the table space by increasing the number of ways:

Alter tablespace table space name add datafile table space Size table space autoextend on next grow size maxsize space maximum (if you do not limit the maximum space value for unlimited) /c0>

How to modify table spaces is increased:

ALTER DATABASE datafile tablespace file path autoextend (Auto-expand) on NEXT table space increases size after full

After the addition of the above script can then be queried to find the size of the table space changes!

Oracle View table space size and its extensions