Oracle表空间

--查看表空间大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 
from dba_tablespaces t, dba_data_files d 
where t.tablespace_name = d.tablespace_name 
group by t.tablespace_name;

--查看版本

select * from v$instance

--创建表空间
create tablespace VoiceTraceDB
logging
datafile 'E:\orcl10Data\VoiceTraceDB.dbf' 
size 32m 
autoextend on 
next 32m maxsize 256m
extent management local;


--无大小限制
create tablespace UIDB
logging
datafile 'E:\ORADB\UIDB.dbf' 
size 32m 
autoextend on 
next 32m MAXSIZE UNLIMITED 
extent management local;


--创建用户并指定表空间
Create user icd identified by orcl
default tablespace UIDB
temporary tablespace temp;


--授权
grant connect,resource,dba to icd;


--drop tablespace CCMS_HA


--drop user pbuser cascade


--表空间利用率
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
  round((f.free/a.total)*100) "% Free"
  from
  (select tablespace_name, sum(bytes/(1024*1024)) total
       from dba_data_files group by tablespace_name) a,
  (select tablespace_name, round(sum(bytes/(1024*1024))) used
       from dba_extents group by tablespace_name) u,
  (select tablespace_name, round(sum(bytes/(1024*1024))) free
       from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;


--修改表空间大小
--实际上是修改数据文件大小
alter database datafile 'E:\ORADB\rdata51' autoextend on next 50m MAXSIZE unlimited;