0

I'm trying to grant all to scott on emp from SYS, but oracle throwing ORA-00942: table or view does not exist error message. I'm not even getting emp table details in sys as dba.

How can I grant all permission to scott so that I can create views or user in scott. When I tried to create a view or user in scott, system is throwing insufficient privileges error message.

Thanks for your help.

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
user3454315
  • 11
  • 1
  • 2

2 Answers2

1

You're having (at least) three different problems:

  • missing system privileges to create views etc.
  • (assuming you want to use the example tables provided by the database installation) the table is called EMPLOYEES, not EMP
  • the EMPLOYEES table is in the HR schema, not in the SYS schema
  • missing privileges on HR.EMPLOYEES

To fix these:

-- connect as SYS
GRANT CREATE VIEW TO scott;

-- connect as HR
CREATE SELECT ON employees TO scott;

-- connect as SCOTT
CREATE VIEW v_scott AS SELECT * FROM hr.employees;

UPDATE

If you're unsure about the correct table name, you can use this query to get a list of all tables in the database whose names start with EMP (to run this, you'll have to use a privileged user account, e.g. SYS):

select owner, table_name from dba_tables where table_name like 'EMP%'
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • The OP says `emp` not `employees`. Although your analysis of the tasks is spot on. – APC Mar 24 '14 at 07:13
  • I know. But I'm guessing he wants to access the example tables provided with the database installation and just doesn't know the correct table name. I've added a comment to my answer mentioning that I assume this is the case. – Frank Schmitt Mar 24 '14 at 07:15
  • Thank you. I was able to grant "create view" permission to scott. Yes I'm using sample DB, here a new issue raised, the table name is emp, I tried with employees, oracle throwing ORA-00942: table or view does not exist error. Based on this, I'd like to check all available tables in the database, how can I do that and can I view all tables under scott. Also, I don't have an user with HR. Once again thank you for taking time in helping me with this. – user3454315 Mar 24 '14 at 07:26
  • I've added a query to obtain the tables whose names start with `EMP`. Good luck. – Frank Schmitt Mar 24 '14 at 07:31
  • Thank you Frank, it returned the EMP table. I've been trying to get syntax to view all tables under sys, with your query select owner, table_name from dba_tables, I got it. Thanks again for taking time and I appreciate your patience. – user3454315 Mar 24 '14 at 07:40
1

The EMP table is a venerable object, used in many old tutorials and blog posts. However, it is not built by default any more.

If you want EMP in a schema (and traditionally SCOTT is its owner) you need to run the build script. Where you'll find it depends on the precise version of the database, but it might be under the sqlplus/demo sub-directory. On my Linux 11gR2 build it's at

%ORACLE_HOME/rdbms/admin/utlsampl.sql

OraFAQ has lots more about the SCOTT schema. Check it out.

On the other hand, if you want to use the modern demo schema (HR, SALES, etc) which are referenced in the current documentation, you will find all the scripts under:

%ORACLE_HOME/demo/schema

The documentation has installation instructions. Find out more.

APC
  • 144,005
  • 19
  • 170
  • 281