Some Oracle operations on table spaces

To see if the XXX table space is auto-extended:

Select file_name,autoextensible,increment_by from Dba_data_files;

Select file_id,file_name,tablespace_name,autoextensible,increment_by from Dba_data_files order by file_id Desc;

To open the table space Auto-expansion feature:

ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/TESTDB/TEST01.DBF ' autoextend on;

To view the data file size:

Select tablespace_name,file_name,bytes/1024/1024 M from Dba_data_files order by Tablespace_name;

Total capacity of the query table space:

Select Tablespace_name, sum (bytes)/1024/1024 as MB

From Dba_data_files

Group BY Tablespace_name;

Query table Space usage:

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;

To extend a table space:

#首先查看表空间的名字和所属文件及空间

Select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_space from Dba_data_files ORDER by Tablespace_name;

#三种扩展方法

1.alter tablespace test Add datafile '/u01/test02.dbf ' size 10M (auto plus one datafile)

2.alter database datafile '/u01/test.dbf ' resize 20M;

3.alter database datafile '/u01/test.dbf ' autoextend on next 10M maxsize 1G;

To view tablespace information after setting:

Select A.tablespace_name,a.bytes total,b.bytes used,c.bytes free, (b.bytes*100)/a.bytes "% used", (c.bytes*100)/ 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;

This article is from a "a little" blog, make sure to keep this source http://pengai.blog.51cto.com/6326789/1976437

Some Oracle operations on table spaces