关于oracle 用户去DBA权限的管理.docxVIP

为了规范数据库用户的操作，特制订如下规范：一、每个系统建立自己的数据库文件和索引文件，每个文件不允许超过2G，不支持自动扩展，系统根据自己的情况申请相应大小的数据文件；创建文件系统的命令为：（1）创建表空间Createtablespacecoredb_dataloggingdatafile/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_data01.dbfsize 2G autoextend off（2）创建索引空间Createtablespacecoredb_idxloggingdatafile/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx01.dbfsize1G autoextend off（3）增扩表空间altertablespacecoredb_idxadddatafile/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx02.dbf size 2Gautoextend off（4）查看表空间selectdbf.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) totalblocksfromdba_data_files tgroup by t.tablespace_name) dbf,(selecttt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocksfromdba_free_spacettgroup by tt.tablespace_name) dfswhere trim(dbf.tablespace_name) = trim(dfs.tablespace_name)二、创建用户的命令使用ORACLE用户执行如下命令（无DBA权限）：sqlplus /as sysdba !drop user usercascade;create user useridentified by user default tablespacecoredb_data temporary tablespace temp;grant connect to user;grant resource to user;grantcreate session to user;grantcreate view to user;alter user user quota unlimited on coredb_idx;alter user user quota unlimited on coredb_data;!三、检查用户是否有DBA权限select * from sys.dba_role_privs where granted_role=DBA;GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ----------------------SYSTEM DBA YES YESGLSDBDBA YES YESSHENG DBA YES YESSYS DBA YES YESCOREDB DBA YES YES四、去除DBA权限1）Sqlplus“/as sysdba”2）revoke dba from user;3）exit;五、用户的权限（1）查看用户权限；（2）增加用户权限；当系统报错：ORA-01031: 权限不足grant create any synonym to dhrep;//增加同义词权限（3）解除用户权限；五、异常情况的处理（1）ora-01045:usersystemlackscreatese