5

I use SQL*Plus for school and I use the username Scott. I cannot create views because it says:

ORA-01031: insufficient privileges;

I've searched and searched, but nothing seems to get it right. Any help?

David Lacombe
  • 61
  • 1
  • 1
  • 6

4 Answers4

17

As the error states - your privileges are insufficient to create view - you will have to ask database administrator to grant you this privilege. If you can log in as database administrator you will have to execute statement(I can't guarantee correctness, no oracle database at hand)

GRANT CREATE ANY VIEW TO Scott;

or

GRANT CREATE VIEW TO Scott;
mkubacki
  • 585
  • 2
  • 21
  • Well I am using it on a home computer, so does this mean I can log in as the database administrator? If so, how? – David Lacombe Dec 15 '13 at 15:14
  • While installing Oracle database there should be a moment when you had to choose your admin password or try to log in with defalut user SYSTEM and password MANAGER. – mkubacki Dec 17 '13 at 14:06
  • You can try this answer for more information on how to log into Oracle db, how to grant privileges and some more: http://stackoverflow.com/questions/18403125/how-to-create-a-new-schema-new-user-in-oracle-11g – vitfo Aug 12 '14 at 11:38
1

You need to GRANT the CREATE VIEW privilege to the USER which is creating the view.

For example, I create a new user to let it create a session, a table and a view:

SQL> create user test identified by test;

User created.

SQL> grant create session, create table, create view to test;

Grant succeeded.

SQL> conn test/test@pdborcl;
Connected.
SQL> Create Table advanced
  2   (Id  varchar(15),
  3   Name varchar(20),
  4   Dept  varchar(15),
  5   Cgpa  float,
  6   Birth_date date,
  7   Mob_no  int,
  8   Dist varchar(20),
  9   Salary  number(8));

Table created.

SQL> Create View advanced_data as
  2  (
  3  select name,dept,dist,salary from advanced
  4  );

View created.

If I revoke the privilege, you will recieve ORA-01031: insufficient privileges:

SQL> revoke create view from test;

Revoke succeeded.

SQL> conn test/test@pdborcl;
Connected.
SQL> Create or replace View advanced_data as
  2  (
  3  select name,dept,dist,salary from advanced
  4  );
Create or replace View advanced_data as
                       *
ERROR at line 1:
ORA-01031: insufficient privileges
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

step 1-conn ss/ss as sysdba;
step 2- GRANT CREATE ANY VIEW TO Scott;
step 3- conn scott/tiger
step 4-create or replace view v as select *from emp;

Yeldar Kurmangaliyev
  • 33,467
  • 12
  • 59
  • 101
naseem ahmad
  • 1
-1

To log-in as DBA (database administrator) you can use:

sqlplus / as sysdba

or

sqlplus sys as sysdba
brasofilo
  • 25,496
  • 15
  • 91
  • 179
Jan Vitásek
  • 33
  • 3