oracle dba_hist tablepsace,oracle表空间大小、使用情况查询

----查看表空间大小-----

select tablespace_name, sum(bytes),file_name from dba_data_files group by tablespace_name,file_name;

-----查看表空间free space---------

select tablespace_name,

count(*) as extends,

round(sum(bytes)/1024/1024,2) as MB,

sum(blocks) as blocks

from dba_free_space

group by tablespace_name;

--------查看表空间使用情况-------------

select upper(f.tablespace_name) "表空间名",

d.tot_grootte_mb "表空间小(M)",

d.tot_grootte_mb - f.total_bytes "已使用空间(M)",

to_char(round((d.tot_grootte_mb - f.total_bytes)/d.tot_grootte_mb*100,2),'990.99') || '%' "使用比",

f.total_bytes "空闲空间(M)",

f.max_bytes "最快(M)"

from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total_bytes,

round(max(bytes)/(1024*1024),2) max_bytes

from sys.dba_free_space

group by tablespace_name) f,

(select dd.tablespace_name, round(sum(dd.bytes)/(1024*1024),2) tot_grootte_mb

from sys.dba_data_files dd

group by dd.tablespace_name) d

where d.tablespace_name=f.tablespace_name

order by 1

-----删除表空间下所有表----

select 'Drop table ' ||table_name|| ';'

from user_tables

where tablespace_name='YBTTBSPACE'

-----表空间相关的三个系统表---

dba_free_tablepsace

all_tablespace

---------重建新的临时表空间并进行切换----------

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10M;

Tablespace created.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 20M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

扩展表空间 首先查看表空间的名字和所属文件

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 game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;