OraFAQ Forum: Server Administration » How to get the tables in Oracle 10g

Home » RDBMS Server » Server Administration » How to get the tables in Oracle 10g (RHEL4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get the tables in Oracle 10g [message #486625]

Fri, 17 December 2010 08:10

tejokrishna
Messages: 15
Registered: December 2010
Location: Hyderabad

Junior Member

hi,
I have installed the Oracle 10g software and created Database my own in RHEL 4. i got the emp tables when i run @?/rdbms/admin/utlsampl.sql with main user system. After i created another user test and i tried to get the tables. but im getting error like table not exist.
What is the prob.Im not understanding pls give me support.

Tejo Krishna.Y

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486627 is a reply to message #486625]

Fri, 17 December 2010 08:21

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>What is the prob.Im not understanding
Either do we.
Next time please post what you did. Your session. Just copy and paste.
Explaining what you did and errors you got help us little.
In this case, it depends on how you were trying to "get" the tables.
If tables are owned by user A, then User needs to grant permissions
to make that table visible to user B (unless user B has DBA privs).

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486641 is a reply to message #486627]

Fri, 17 December 2010 12:00

tejokrishna
Messages: 15
Registered: December 2010
Location: Hyderabad

Junior Member

SQL> !
[oracle@localhost dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 17 23:14:35 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "SYS"
SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> select *from emp;
select *from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> !
[oracle@localhost dbs]$ sqlplus system/manager

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 17 23:16:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> select *from emp;
select *from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

this is the prob, when i grant the priviliges to test user. its successful. but not getting the tables as test user,
but im getting tables with scott user only; i want tables with test user also. how to get the tables as test.

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486642 is a reply to message #486641]

Fri, 17 December 2010 12:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Quoting myself
Quote:

If tables are owned by user A, then User needs to grant permissions
to make that table visible to user B (unless user B has DBA privs).

You have not granted any privilege that would grant access to emp table.
First hit on google for me
http://techonthenet.com/oracle/grant_revoke.php

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486643 is a reply to message #486641]

Fri, 17 December 2010 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>but im getting tables with scott user only;
expected & desire behavior

>i want tables with test user also. how to get the tables as test.
Please clarify EXACTLY what you desire.
Do you want TEST schema to have its own tables; like EMP?
Do you want TEST schema to access SCOTT.EMP table?

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486644 is a reply to message #486641]

Fri, 17 December 2010 12:08

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Because scott owns the table and not test.
Grant test the privilege to select scott tables and create synonyms on scott tables if you don't want to prefix by scott the table in your statement.

You lack the basics on Oracle and SQL. I advise you to read:
Database Concepts
Database SQL Reference

Regards
Michel

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486659 is a reply to message #486643]

Sat, 18 December 2010 01:57

tejokrishna
Messages: 15
Registered: December 2010
Location: Hyderabad

Junior Member

[oracle@localhost ~]$ sqlplus system/manager;

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 18 13:10:31 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> grant all on emp to test;
grant all on emp to test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn scott/tiger;
Connected.
SQL> grant select, update, insert on emp to test;

Grant succeeded.

SQL> grant all on emp to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> select *from emp;
select *from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist.

I want to use tables which are owned by scott as test user, im getting error as above. pls tell me how to do that.

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486661 is a reply to message #486659]

Sat, 18 December 2010 02:04

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

create synonyms, answers have already been provided, you are unable to understand them, read the links I provided, you MUST read them (at least the first chapter of the first one), your lack of the basics is too big to get any advantage of what you are trying to do (whatever it is).

Regards
Michel

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486662 is a reply to message #486659]

Sat, 18 December 2010 02:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> grant all on emp to test;
grant all on emp to test
*
ERROR at line 1:
ORA-00942: table or view does not exist

user system does not own emp table.
Above script might work if login as scott user .

Logged in as sys or system or other dba user,
you are supposed to use

sql> grant all on scott.emp to test;

Again, all these are in documentation suggested by folks here.

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486674 is a reply to message #486662]

Sat, 18 December 2010 06:45

tejokrishna
Messages: 15
Registered: December 2010
Location: Hyderabad

Junior Member

im getting tables, when i tried with scott.tablename

tell me how to get the tables own by test user.

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486676 is a reply to message #486674]

Sat, 18 December 2010 07:02

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Logged in as test, you need access the emp table the same way as system/sys schema did.
The table is owned by scott.

select * from scott.emp;

If you want to get tables owned by test user,
you need to do nothing (logged in as test).

[Updated on: Sat, 18 December 2010 07:03]

Report message to a moderator

Re: How to get the tables in Oracle 10g [message #486681 is a reply to message #486674]

Sat, 18 December 2010 08:05

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

tejokrishna wrote on Sat, 18 December 2010 13:45

im getting tables, when i tried with scott.tablename

tell me how to get the tables own by test user.

STOP wasting others time:

Michel Cadot wrote on Fri, 17 December 2010 19:08

You lack the basics on Oracle and SQL. I advise you to read:
Database Concepts
Database SQL Reference

Regards
Michel

Report message to a moderator

Previous Topic:	ASM File size
Next Topic:	Variable file size for archive log files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 11:49:31 CDT 2024