用户管理
程序员文章站
2022-03-19 22:02:11
...
创建用户
create user xxxx identified by xxxx;
分配权限
grant resource,connect,dba,create table,create tablespace to xxxx;
查看用户密码:
select username,password from dba_users;
修改用户密码:
alter user xxxxx identified by values '2D594E86F93B17A1';
删除用户
drop user USERNAME cascade;
注:DB建好后,会有tablespace,tablespace里面会有datafile,假如你某个tablespace里面有10个datafile,每个datafile 2G,那么你的tablespace就是20G,不管这20G里面有没有资料,这20G都是会在系统里面霸占20G的空间的。
datafile里面的资料清除了30G,但是空间还是有的,你可以手动去把datafile缩小,这样才能减少磁盘空间
计算datafile可以resize收缩的空间脚本
--计算datafile可以resize收缩的空间.
col name for a40
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5
--如果只是想对某个表个间的datafile resize,可采用:
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents where file_id in
(select b.file# From v$tablespace a ,v$datafile b
where a.ts#=b.ts# and a.name='MP2000')
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5
--计划tempfile可以resize的空间.on apply that have only one tempfile
select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(select value From v$parameter where name = 'db_block_size') bk) b
上一篇: beanshell学习笔记(三)——作用域的修饰Scope Modifiers
下一篇: sql2