oracle 表空间 数据库用户管理

----------------创建临时表空间
create temporary tablespace sms_temp 
tempfile 'E:\SMS_DIRECT_DATA\SMS_DIRECT_temp.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m 
extent management local; 

-----创建表空间
create tablespace sms_data 
logging 
datafile 'E:\SMS_DIRECT_DATA\SMS_DIRECT_DATA.dbf' 
size 32m 
autoextend on 
next 32m maxsize 10000M 
extent management local; 

-----创建用户sms
create user sms  identified by 123456 
default tablespace sms_data 
temporary tablespace sms_temp; 

-----用户授权
grant   connect ,resource,dba  to   sms  ;
grant create any table,create any procedure to sms;


---导出数据
exp sms/123456@ORCL   file=d:\sms.dmp owner=(sms);

----导入数据

imp sms/123456@ORCL   file=d:\sms.dmp  full=y  ignore=y;

-----导入数据到不同用户

imp sms/123456@127.0.0.1:1521/ORCL   file=d:\sms.dmp    FROMUSER="sms" touser="crm";

----赋予表权限
grant select, insert, update, delete, references, alter, index on SYS_USER_TABLE  to PUBLIC;

----查询表空间 大小

select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name ;

-----查询物理块大小

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例" 
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);


--删除用户,会删除此用户名下的所有表和视图
drop user sms cascade 
--删除表空间 包括里面的物理内容
drop   tablespace   sms_data   including   contents   and   datafiles