3 Replies Latest reply: Apr 29, 2012 4:01 PM by 924630 RSS

    R8.2 Extended Schema Tables Question

    924630
      Can someone explain the difference between the extended schema’s tables with the same name, but different suffixies? For example: NAME, NAME_FULL, NAME_S and NAME_U. They seemingly have the same columns and data (rows).
        • 1. Re: R8.2 Extended Schema Tables Question
          srhcompcon
          The extended tables are used for the ERD. Since 8.1 a lot of the tables have been brought back into the main schema rather than being in a seperate schema.

          Have a look at the P6 extended schema

          http://docs.oracle.com/cd/E20686_01/English/Technical_Documentation/Reporting_Database/P6%20Extended%20Schema%20White%20Paper.pdf

          or

          http://docs.oracle.com/cd/E25030_01/index.htm

          the tables are used in the process as staging, fact etc.
          • 2. Re: R8.2 Extended Schema Tables Question
            924630
            Thanks for the reply. I have read the white paper before and my question really boils down to which table to report from:

            NAME
            NAME_FULL
            NAME_S
            NAME_U
            • 3. Re: R8.2 Extended Schema Tables Question
              924630
              I took some time today to look at the selects of the views. Here are some of my initial findings:

              NAME - The select on the production tables, lots of column aliases to make the naming more intuitive. Filters out deleted sessions. The ETL, if you will.

              NAME_FULL - This is simply a view of the above view with no new aliases or where statement. Basically SELECT * FROM NAME. This is presumably the view they want you to hit.

              NAME_U - I assume the U stands for User. It basically does an INNER JOIN from the data (NAME view) you need to the PROJECTSECURITY, RESOURCESECURITY, GLOBALSECURITY, etc table(s) to limit the data. But not all of these views look at the security views/tables, therefore, you get the same data as the original view. No WHERE statement on these - the INNER JOIN effectively does it for you.

              NAME_S (NOT MANY VIEWS INCL THIS ONE) - I assume the S stands for Secure Code, although one DBA mistakenly wrote out SECURITY instead of S on the pxrptuser.RESOURCE view. This view uses an inner joins to look at the object userIDs, secure code flags, etc.

              This is what I needed for an immediate assignment, but I will be digging into these more and will edit this and/or post a better white paper on my web site.