oracel 表空间建立以及建立用户sql语句

【实例简介】表空间建立及检查表空间使用情况代码

【实例截图】

【核心代码】

--创建表空间
create tablespace assp  
logging  
datafile 'F:\oracle\product\10.2.0\oradata\orcl\yb.dbf' 
size 50m  
autoextend on  
next 50m maxsize 2048m  
extent management local;
--增大表空间
alter database datafile 'F:\oracle\product\10.2.0\oradata\orcl\grp.dbf' autoextend on next 1000m maxsize 5000m

-- Create the user 
create user YBDC identified by 123456
  default tablespace ASSP
  temporary tablespace TEMP
  profile DEFAULT
  password expire;
-- Grant/Revoke role privileges 
grant connect to YBDC with admin option;
grant dba to YBDC with admin option;
grant exp_full_database to YBDC with admin option;
grant imp_full_database to YBDC with admin option;
-- Grant/Revoke system privileges 
grant alter profile to YBDC;
grant alter session to YBDC;
grant alter tablespace to YBDC;
grant unlimited tablespace to YBDC with admin option;


--检查表空间使用情况
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;