用户管理

创建用户

 

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