【oracle】表空间相关查询

环境：oracle 11g

1、表空间路径

select * from dba_data_files;

2、查询用户所在表空间

select username,DEFAULT_tablespace,temporary_tablespace from dba_users;

3、表空间使用情况

select * from (
/**  Data Tablespace **/
select 'Data Tablespace' tablespace_type, a.tablespace_name, round(c.bytes/1024/1024,1) as "Free Size M", a.bytes/1024/1024 as "Size M",
round((a.bytes - c.bytes)/1024/1024,1) as "Used Size M", round(b.bytes/1024/1024 ,1) as "Max Size M",
cast(((a.bytes - c.bytes)/a.bytes )*100 as int) as "Used%", cast(((a.bytes - c.bytes)/b.bytes )*100 as int ) as "Max Used%"
from
(select tablespace_name, sum(bytes) as bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) as bytes from (
select tablespace_name, sum(bytes) as bytes from dba_data_files where autoextensible=upper('no') 
group by tablespace_name
union all
select tablespace_name, sum(maxbytes) as bytes from dba_data_files where autoextensible=upper('yes')
group by tablespace_name) group by tablespace_name) b,
(select tablespace_name, sum(bytes) as bytes from dba_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
union all
/**  Temp Tablespace **/
select 'Temp Tablespace' tablespace_type, a.tablespace_name, round(c.bytes/1024/1024,1) as "Free Size M", round(a.bytes/1024/1024,1) as "Size M",
round((a.bytes - c.bytes)/1024/1024,1) as "Used Size M", round(b.bytes/1024/1024 ,1) as "Max Size M",
cast(((a.bytes - c.bytes)/a.bytes )*100 as int) as "Used%", cast(((a.bytes - c.bytes)/b.bytes )*100 as int) as "Max Used%"
from
(select tablespace_name, sum(bytes) as bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) as bytes from (
select tablespace_name, sum(bytes) as bytes from dba_temp_files where autoextensible=upper('no') 
group by tablespace_name
union all
select tablespace_name, sum(maxbytes) as bytes from dba_temp_files where autoextensible=upper('yes')
group by tablespace_name) group by tablespace_name) b,
(select tablespace_name, sum(free_space) as bytes from dba_temp_free_space group by tablespace_name) c
where a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
) order by 1,7 desc;

4、查看表空间是否开启了自动扩展的功能

数据文件最大限制是32G

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 
FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 
ORDER BY TABLESPACE_NAME,FILE_NAME; 

5、增加数据文件并允许自动增长

ALTER TABLESPACE  ABC      
 ADD DATAFILE '+ORADATA/orcl/datafile/ABC05.ora'SIZE 500M AUTOEXTEND ON      
 NEXT 200M MAXSIZE UNLIMITED;  

6、扩大表空间的四种方法：

（1）增加数据文件

ALTER TABLESPACE ***_TRD ADD DATAFILE 
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD_2.DBF' SIZE 1024M;

（2）增加数据文件并允许自动增长，限额

ALTER TABLESPACE ***_TRD ADD DATAFILE 
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD_2.DBF' SIZE 
1024M AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

（3）允许已存在的数据文件，自动增长 ，限额

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD.DBF'
AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

（4）手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD.DBF' 
RESIZE 10240M;

或者

ALTER DATABASE DATAFILE '/home/oracle/app/oradata/orcl/abc3.dbf'
AUTOEXTEND ON NEXT 25M MAXSIZE 31G;

7、查看表空间占空间前10的大表

SELECT * FROM (
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024/1024 GB 
FROM DBA_SEGMENTS 
WHERE TABLESPACE_NAME = 'ABC' 
GROUP BY SEGMENT_NAME ORDER BY 2 DESC
) WHERE ROWNUM < 10;

8、分析指定表大小

analyze table SYS.AUD$ compute statistics;

select num_rows * avg_row_len 
from all_tables   
where table_name = 'AUD$';

9、修改用户-表空间配额

全局：grant unlimited tablespace to test01;
针对某个表空间：
alter user test01 quota unlimited on test01;
Alter user test01 quota 10m on test01;
回收：revoke unlimited tablespace from test01;

10、临时表空间

查询临时表空间文件

select * from dba_temp_files;

新增/增加临时表空间

create temporary tablespace OA_TEMP01   
tempfile '/appdata/vdb_dir/oracle/oradata/orcl/TEMP01.dbf'
size 2G autoextend on;

创建用户表空间

CREATE TABLESPACE OA 
DATAFILE '/home/oracle/app/oradata/orcl/oa.dbf' 
SIZE 200M 
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

修改用户对应的临时表空间

alter user OA temporary tablespace OA_TEMP01;