At my work, we have an Oracle database that I can connect to using different accounts.

The server name and port are identical between the accounts, but each account sees different data, and can see different table schemas. (I do mean different columns in different tables. i.e. if I add a column to a table in one account, I don't see that change reflected in the other account.)

Is there a name for this practice? I would like to read up on it, but I could not find anything in google.

Database version: Oracle Database 11g Enterprise Edition Release - 64bit Production

  • Two users have two different schemas, so it's absolutely normal that you would see different tables, columns and data between the two users. – a_horse_with_no_name Mar 16 '17 at 7:17

I see two possibilities, and they are not mutually exclusive.

First is that you are not really looking at the same table. If you connect as SCOTT and select from EMP without qualifying the table name, you are selecting from SCOTT.EMP. If you connect as FRED and do the same, you will be selecting from FRED.EMP. Two different schemas with the same table name. The two tables are totally unrelated and could very well have different structures in addition to certainly having different data.

The other is Virtual Private Database (VPD). Two users actually selecting from the same table, but only seeing the rows they are allowed to see, based on the VPD policies.

With SQLplus or SQL Developer, connect with one account and SELECT TABLE_NAME FROM USER_TABLES; Do the same with the other account. Be sure to select from USER_TABLES, not ALL_TABLES or DBA_tables. That will show you just the tables owned by the connected user.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.