分类目录归档：数据库

回收dba中alter system处理方法

发表于 2011 年 12 月 10 日由惜分飞

今天在pub上看到一个问题，一个朋友想回收dba的alter system权限，直接回收这个系统权限从dba的做法是不推荐使用，因为修改了系统默认的dba角色所具有的系统权限，可能会导致未知的后果。好的做法是创建新的角色，使其有dba中除alter system之外的所有权限。
1、数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2、创建角色并授权

SQL> create role xifenfei;

角色已创建。

SQL> declare
  2  cursor cur is select privilege from role_sys_privs where role='DBA'
  3  AND PRIVILEGE NOT IN ('ALTER SYSTEM','ADMINISTER RESOURCE MANAGER');
  4  BEGIN
  5    FOR C in cur loop
  6    EXECUTE IMMEDIATE  'grant '||c.privilege||' to xifenfei';
  7    END loop;
  8  END;
  9  /

PL/SQL 过程已成功完成。

SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  2  WHERE ROLE ='DBA' AND PRIVILEGE NOT IN(
  3  SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  4  WHERE ROLE ='XIFENFEI'
  5  );

PRIVILEGE
----------------------------------------
ALTER SYSTEM
ADMINISTER RESOURCE MANAGER

说明：授予创建角色出ALTER SYSTEM和ADMINISTER RESOURCE MANAGER系统权限之外的所有权限

3、创建用户并授权角色

SQL> create user chf_xff identified by xifenfei;

用户已创建。

SQL> grant xifenfei to chf_xff;

授权成功。

4、单独授予ADMINISTER RESOURCE MANAGER权限

SQL> exec dbms_resource_manager_privs.grant_system_privilege(
   2 grantee_name => 'CHF_XFF',admin_option => false);

PL/SQL 过程已成功完成。

SQL> CONN chf_xff/xifenfei
已连接。

SQL> SELECT * FROM SESSION_PRIVS WHERE
  2  PRIVILEGE ='ADMINISTER RESOURCE MANAGER';

PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER

说明:
1)通过授权xifenfei角色和ADMINISTER RESOURCE MANAGER权限，完成回收dba中的alter system权限要求。
2)如果只有个别用户有这样的需求，那么可以直接生成批量授权语句实现，而不用建立类此xifenfei这样的角色。

5、为何单独授予ADMINISTER RESOURCE MANAGER权限

SQL> CONN / AS SYSDBA
已连接。
SQL> GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF;
GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效

说明：ADMINISTER RESOURCE MANAGER这个系统权限在10g及其以后版本中，就不能直接使用GRANT/REVOKE直接授权/回收权限,而必面使用dbms_resource_manager_privs.grant_system_privilege和revoke_system_privilege过程进行处理。

发表在 Oracle | 标签为 ORA-00990 | 评论关闭

创建视图提示ORA-01031

发表于 2011 年 12 月 9 日由惜分飞

1、重现问题

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看相关权限情况

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

发表在 ORA-xxxxx | 标签为 ORA-01031 | 评论关闭

删除Oracle数据文件/临时文件

发表于 2011 年 12 月 8 日由惜分飞

有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件

一、删除数据文件

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name 
   2 from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF
        11 /opt/oracle/oradata/test/xifenfei03.dbf            XFF

11 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 00:05 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei03.dbf

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

OWNER                          SEGMENT_NAME            FILE_ID     BLOCKS
------------------------------ -------------------- ---------- ----------
CHF                            XFF_TEST                     11          8
CHF                            XFF_TEST                     11        128
CHF                            XFF_TEST                     11        128
CHF                            T_XFF                        11        128
CHF                            T_XFF                        11        128

SQL> alter table chf.xff_test move tablespace users;

Table altered.

SQL> alter table chf.t_xff move tablespace users;

Table altered.

SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

no rows selected

SQL> alter tablespace xff drop datafile 11;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name 
  2  from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF

10 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:52 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:52 /opt/oracle/oradata/test/xifenfei01.dbf

二、删除临时表空间文件

SQL> alter  tablespace temp add tempfile '/opt/oracle/oradata/test/xff_temp02.dbf' size 10m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         2 /opt/oracle/oradata/test/xff_temp02.dbf            TEMP
         1 /opt/oracle/oradata/test/temp01.dbf                TEMP

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 13:00 /opt/oracle/oradata/test/xff_temp02.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

SQL> alter tablespace temp drop tempfile 2;

Tablespace altered.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

三、注意事项
1、Oracle Database 10g Release 2及其以上版本
2、删除数据文件从数据库和系统
3、数据文件必须为空
4、数据文件不是对应表空间的最后一个
5、不能删除只读表空间中数据文件
6、不能删除system中数据文件
7、不能删除本地管理的offline表空间数据文件

发表在 Oracle | 标签为 ORA-03262 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

回收dba中alter system处理方法

创建视图提示ORA-01031

删除Oracle数据文件/临时文件

文章分类

最近发表

分类目录归档：数据库

回收dba中alter system处理方法

创建视图提示ORA-01031

删除Oracle数据文件/临时文件

标签云

文章分类

最近发表