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;