Home » SQL & PL/SQL » SQL & PL/SQL » Access different schema from another schema.
icon5.gif  Access different schema from another schema. [message #204376] Mon, 20 November 2006 07:02 Go to next message
talk2dipankar
Messages: 3
Registered: October 2006
Location: Kolkata,India
Junior Member
i enter to the oracle giving valid user id password and host string...

but the actual tables are situated in different schima...

i give the example...

i enter to the oracle by user name dipankar, password bhowmick and host string fire.lkj...

it successfully connected...

but the actual tables are situated in different schema...

suppose the separate schema is duck...

i can access those tables using this query

select * from duck.table_name...

it show full details of tables...

it is ok

but i want to access the system tables like cols,tab.. which show all table information under schema duck...

but it does not show that...

it show schema details under dipankar schema but we don' access duck schema.

select * from tab; work but
select * from duck.tab; does not work...

Please reply me as early as possible...
Re: Access different schema from another schema. [message #204378 is a reply to message #204376] Mon, 20 November 2006 07:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you need to query dba* and all* tables.
Filter with owner='YOUROWNER'.
>>select * from duck.tab; does not work...
Will not work because there is no such table/view.
'tab' is a view available only for that schema owner.
Look into dba_tables/all_tables
Re: Access different schema from another schema. [message #204379 is a reply to message #204376] Mon, 20 November 2006 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use the proper views like ALL_TABLES (tab), ALL_COLUMNS (cols) instead.
Re: Access different schema from another schema. [message #204380 is a reply to message #204376] Mon, 20 November 2006 07:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

select * from duck.tab; does not work...

what error you got or if you have privs on data dictionary tables. you can also see other schema tables. like

select * from dba_tables where owner = 'DUCK';

hope this helps
Mohammad Taj.
Re: Access different schema from another schema. [message #204412 is a reply to message #204376] Mon, 20 November 2006 09:55 Go to previous messageGo to next message
vshari
Messages: 9
Registered: October 2005
Location: India/UK
Junior Member
Hi,

To get information on tables that you have access to you can use

user_tables, user_objects.....(all user_ tables they contains information pertaining to your schema.)
all_tables, all_objects......(all the all_ tables contain the information regarding the objects of your schema and the objects that you have access to like rights granted to you by other users.)
To get all the information regarding all the schemas can be had by querying the dba_ tables which you can do only if you have privileges to access them.

You cannot do: select * from xyz.tab because the tab is a public synonym created by schema system you cannot qualify it using a schema name as xyz in this case does not own tab synonym.


Please correct me if i am wrong.

Thanks.
Re: Access different schema from another schema. [message #204422 is a reply to message #204376] Mon, 20 November 2006 10:29 Go to previous message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Yes Vshari i right ,if you want to access the table of Duck schema. the Duck should give you grant permission to ypur schema, only if you have grant permission over the tables or objects ,you can access the information.
Previous Topic: DB2 to Oracle migration
Next Topic: Query to extract information based on random search separated by commas
Goto Forum:
  


Current Time: Sat Dec 03 16:00:22 CST 2016

Total time taken to generate the page: 0.14004 seconds