0

I make a privileges to user on one schema at Oracle, when accessing oracle database using SSIS I saw all tables and schema. When I use SQL Plus show me only one schema.

What is the problem here?

Mat
  • 202,337
  • 40
  • 393
  • 406
Hossam Ahmed
  • 1

1 Answers1

1

What query are you running to see tables in SQL*Plus? If you are querying USER_TABLES, you will only see the tables that the current user owns. If you are querying ALL_TABLES, you will see all the tables that you have permission to query regardless of the owner. If you are querying DBA_TABLES, you will see all the tables in the database (though you need additional privileges to query the DBA% objects.

There is another question on how to get a list of all the tables in a database that goes into more detail about this.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I am using USER_TABLES, Sorry I do not understand the last question, any way when I access to oracle database using "Oracle Provider OLEDB" in SSIS I saw everything tables,viewa and all schema in database – Hossam Ahmed Apr 03 '12 at 16:21
  • @HossamAhmed - If you query `USER_TABLES`, you will only see the tables that the current user owns. If you want to see objects other than tables, you'd need to query other data dictionary views-- `USER_VIEWS` shows you views that the current user owns, for example. If you want to see objects that you have access to but do not own, you'd need to query `ALL_TABLES`, `ALL_VIEWS`, etc. That's what most GUI tools query. – Justin Cave Apr 03 '12 at 16:24