This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 4, 2013 2:53 PM by jgarry RSS

Interesting Oracle Question

Trith Newbie
Currently Being Moderated
Hi Gurus,

Happy New Year to Everyone!
A very simple question related to Oracle!
This thought comes out while working on Database Level Masking for Sensitive Data/Information!

Can two different Oracle tables (residing in same or different schema) have the same or similar columns (containing same or similar data) ?

For example:
Column Name = PRINTER_NAME
Table Name = FND_PRINTER and FND_PRINTER_TL

I know Oracle is relational DBMS but any feedback from the Gurus would be really helpful!

Thanks,
Trith
  • 1. Re: Interesting Oracle Question
    Brian Bontrager Expert
    Currently Being Moderated
    Certainly.
    create table MY_USER_OBJECTS as select * from USER_OBJECTS;
    You now have 2 different tables with the same columns and the same data.
    The data in each table can have a life of its own and may or may not be related.
  • 2. Re: Interesting Oracle Question
    jgarry Guru
    Currently Being Moderated
    You might google for the word: normalization. You may find that not only may the column names be the same, but it actually is very useful. You don't want to copy table information willy-nilly, because then you will have synchronization problems. But if you are asking about creating tables from other tables with sensitive information masked, there are a number of ways to do that, including products you can buy.
  • 3. Re: Interesting Oracle Question
    Trith Newbie
    Currently Being Moderated
    Thanks Brian, in your example we are creating our table.
    What I wanted to know is that does Oracle really have multiple tables (similar to my example, Standard Oracle Tables) with the same columns, the life of which (data) is the same ......
    In other words, If I restrict access to column X in table Y in schema Z, then is it possible for someone else to access the same column X from some other table A in any other schema S.

    Thanks again!
  • 4. Re: Interesting Oracle Question
    Brian Bontrager Expert
    Currently Being Moderated
    In my example, yes, they are multiple separate tables, with their own security. You would have to mask or prevent access to each version of the data in each schema/table it appears.

    (I'm not sure what you mean by "Standard Oracle Tables", so I can't say more on that.)
  • 5. Re: Interesting Oracle Question
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    These are seeded tables shipped with E-Business Suite - the ER diagram is available at https://etrm.oracle.com - there is a method to the madness ;-)

    HTH
    Srini
  • 6. Re: Interesting Oracle Question
    EdStevens Guru
    Currently Being Moderated
    Trith wrote:
    Thanks Brian, in your example we are creating our table.
    What I wanted to know is that does Oracle really have multiple tables (similar to my example, Standard Oracle Tables) with the same columns, the life of which (data) is the same ......
    In other words, If I restrict access to column X in table Y in schema Z, then is it possible for someone else to access the same column X from some other table A in any other schema S.

    Thanks again!
    If column X is in "some other table A in any other scheama" then it is not the same column as column X in table Y in schema Z. Regardless of what they are named, how the data is typed, how the tables and columns are structured, the tables in schema A are totally separate objects from tables in schema Z. The possible fact that they may have the same names and data types is - as far as Oracle is concerned - mere coincidence.
  • 7. Re: Interesting Oracle Question
    Dave Rabone Journeyer
    Currently Being Moderated
    ... and is the EBS physical data model denormalised? Almost certainly ... but I'm so far away from an EBS machine right now I can't give you an example (sigh)
  • 8. Re: Interesting Oracle Question
    jgarry Guru
    Currently Being Moderated
    The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
  • 9. Re: Interesting Oracle Question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    This thought comes out while working on Database Level Masking for Sensitive Data/Information!
    Can two different Oracle tables (residing in same or different schema) have the same or similar columns (containing same or similar data) ?
    Simple answer is Yes.

    But how it may relate to "Database Level Masking for Sensitive Data/Information"?

    BTW, you may be interested to look into following Oracle security/encryption features:
    - Virtual Private Database
    - Label Security Option
    - Transparent Data Encryption
    - DBMS_CRYPTO
    - Database Vault
  • 10. Re: Interesting Oracle Question
    EdStevens Guru
    Currently Being Moderated
    jgarry wrote:
    The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
    ???
  • 11. Re: Interesting Oracle Question
    jgarry Guru
    Currently Being Moderated
    EdStevens wrote:
    jgarry wrote:
    The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
    ???
    Yes, it made more sense 20 years ago, when all database engines were going to be standard and relational. In our terms, it's automatically creating a foreign key just 'cause the columns have the same name as a primary identifier of another table (implementing the meaning of domain in relational theory). So for example, if you have company_code at the beginning of all the tables, when you insert a row in any table the company_code has to exist. And if you define indices, the first one alphabetically is the "primary identifier". Except, there are no constraint_type 'P' when looking at dba_constraints (except for tables internal to the app). The optimizer and things like MV love that. The words mean exactly what someone else wants them to mean. Wheeee!

    Note to OP: This is not how Oracle works, just showing that apps can implement things in bizzaro world. Oracle objects have numeric ID's to distinguish themselves from each other.
  • 12. Re: Interesting Oracle Question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Oracle objects have numeric ID's to distinguish themselves from each other.
    Not exactly. Internally - yes. But from user perspective Oracle distinguishes objects by name. It is impossible to create two objects of same name in one namespace.
  • 13. Re: Interesting Oracle Question
    jgarry Guru
    Currently Being Moderated
    It depends on the namespace. In the schema namespace, you can have different types of objects with the same name. In various hashing schemes used by oracle internally to name things, there [url https://forums.oracle.com/forums/message.jspa?messageID=10446701#10446701]can be collisions. I've seen some more obscure things, but can't remember what just now.

    But the internal representation is the answer to the OP question (at least, one interpretation of the question). And it is visible to users, think of the recycle bin and describe user_objects. A user unfamiliar with the difference between views and tables might get completely confused (it had occurred to me that might possibly be the real question, asked incorrectly).
  • 14. Re: Interesting Oracle Question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    It depends on the namespace.
    That is what I said.
    In the schema namespace, you can have different types of objects with the same name. In various hashing schemes used by oracle internally to name things, there can be collisions.
    Could you give an example?

    There is no pure "schema" namespace. Inside a schema you can create a table and an index or a constraint with same name as the table. Same for supplemental log group. It is because they are associated with the table. However they are in a separate namespace than columns. you can create a constraint with same name as a column of the table.

    For PL/SQL name is not only differentiator. Here you can use overloading - a signature, that is a combination of name and set of positions and types of parameters must be unique for different PL/SQLs.
    But also there must not be other objects like tables with same name as the proc.
    So namespaces may overlap.
    I agree it is a bit confusing. :)

    But still, users/programmers should use and rely on object names, not IDs.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points