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;