欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

用户管理

程序员文章站 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

 

 

相关标签: 脚本