Oracle表空间操作 #

-- 查询表空间
SELECT a.tablespace_name 表空间名,total 表空间大小,free 表空间剩余大小 ,(total-free) 表空间使用大小, round((total-free)/total,4)*100 使用率 FROM (SELECT tablespace_name,sum(bytes) free FROM DBA_FREE_SPACE group by tablespace_name )a ,
(SELECT tablespace_name,sum(bytes) total FROM DBA_DATA_FILES group by tablespace_name) b
where a.tablespace_name=b.tablespace_name

-- 查询表空间数据文件
SELECT * FROM dba_data_files where tablespace_name = '表空间名称';

-- 表空间扩容（增加数据文件）
alter tablespace pdzy 
add datafile '/data/oracle/datsle/pdzy27.dbf' size 20G autoextend on next 300m;

-- 表空间数据文件位置
SELECT * FROM dba_data_files 
where tablespace_name in (
	SELECT a.tablespace_name 表空间名 
	FROM (
     SELECT tablespace_name,sum(bytes) free 
		 FROM DBA_FREE_SPACE 
		 group by tablespace_name 
	) a ,(
	   SELECT tablespace_name,sum(bytes) total 
		 FROM DBA_DATA_FILES 
		 group by tablespace_name
	) b
  where a.tablespace_name=b.tablespace_name
)

-- 创建临时表空间   
create temporary tablespace lfjc_temp   
tempfile 'G:\myoracle\oracle\oradata\orcl\lfjc_temp.dbf'  
size 50m   
autoextend on   
next 50m maxsize 20480m   
extent management local;   

-- 创建数据表空间   
create tablespace test_data   
logging   
datafile 'G:\myoracle\oracle\oradata\orcl\HYDA.DBF'  
size 50m   
autoextend on   
next 50m    maxsize unlimited   --每次扩展10M，无限制扩展
extent management local;   

-- 创建用户并指定表空间   
create user LFJC identified by LFJC    
default tablespace HYDA   
temporary tablespace lfjc_temp;   

-- 给用户授予权限   
grant dba to lfjc;