在oracle中创建视图问题

Question

SQL> create view empv20 as select empno,ename,job,hiredate from emp where deptno=20;
create view empv20 as select empno,ename,job,hiredate from emp where deptno=20
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用

SQL> create view empv21 as select empno,ename,job,hiredate from emp where deptno=20;
create view empv21 as select empno,ename,job,hiredate from emp where deptno=20
*
第 1 行出现错误:
ORA-01031: 权限不足

Answer 1

权限不足呀：

要赋予建立视图的用户相应的权限：
grant select any table to 用户名;
grant select any dictionary to 用户名;

Answer 2

那个 ORA-00955 应该是 VIEW 名字已经有了。

SQL> CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL;

View created.

SQL> CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL;
CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

那个 ORA-01031: 权限不足
可能是 没有创建 VIEW 的权限。
或者是 没有 SELECT emp 表的权限

==================================================

对于那个 ORA-00955: 名称已由现有对象使用

使用 CREATE OR REPLACE 关键字。

SQL> CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL;

View created.

SQL> CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL;
CREATE VIEW test_dian AS SELECT 1 AS TMP FROM DUAL
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> CREATE OR REPLACE VIEW test_dian AS SELECT 2 AS TMP FROM DUAL;

View created.

SQL>