Example 19-10 Querying a Table Owned by a Common User Across All Containers

This example makes the following assumptions:

• An organization has several PDBs, and each PDB is for a different department in the organization.

• Each PDB has an employees table that tracks the employees in the department, but the table in each PDB contains different employees.

• The CDB root also has an empty employees table.

• The employees table in each container is owned by the same common user.

With the CDB root as the current container and the common user that owns the table as the current user, run the following query with the CONTAINERS clause to return all employees in the employees table in all PDBs:

SELECT * FROM CONTAINERS(employees);

Example 19-11 Querying a Table Owned by Local Users Across All Containers

This example makes the following assumptions:

• An organization has several PDBs, and each PDB is for a different department in the organization.

• Each PDB has an hr.employees table that tracks the employees in the department, but the table in each PDB contains different employees.

• The CDB root also has an empty employees table owned by a common user.

To run a query that returns all employees in all PDBs, first connect to each PDB as a common user, and create a view with the following statement:

CREATE OR REPLACE VIEW employees AS SELECT * FROM hr.employees;

The common user that owns the view must be the same common user that owns the employees table in the CDB root. After you run this statement in each PDB, the common user has a view named employees in each PDB.

With the CDB root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all employees in the hr.employees table in all PDBs:

SELECT * FROM CONTAINERS(employees);

You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:

SELECT * FROM CONTAINERS(employees) WHERE CON_ID IN(3,4);

Example 19-12 Querying an Application Common Object Across All Application PDBs

This example makes the following assumptions:

• An organization has several application PDBs, and each application PDB is for a different department in the organization.

• Each application PDB has an employees table that tracks the employees in the department, but the table in each application PDB contains different employees.

• The application root also has an empty employees table.

• The employees table in each container is owned by the same common user.

• A company has multiple tenants that use an application in an application container, and each tenant has its own application PDB.

• The company uses metadata-linked application common objects to keep the structure of the data the same in all application PDBs, but the data is different in each application PDB.

• Each application PDB has a metadata-linked sales.customers table that stores information about each tenant’s customers.

With the application root as the current container and the application common user that owns the table as the current user, run the following query with the CONTAINERS clause to return all customers in the sales.customers table in all application PDBs:

SELECT * FROM CONTAINERS(sales.customers);

Example 19-17 Viewing Details About the Applications in an Application Container

This query shows the name, the latest version, and the status of each user-created application in the application container.

COLUMN APP_NAME FORMAT A15
COLUMN APP_VERSION FORMAT A15
COLUMN APP_STATUS FORMAT A15
 
SELECT APP_NAME, APP_VERSION, APP_STATUS
FROM   DBA_APPLICATIONS
WHERE  APP_IMPLICIT='N';

The following sample output shows the salesapp application:

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ---------------
SALESAPP        1.2             NORMAL

Example 19-18 Viewing Information About Application Status

This query shows the name of the application PDB, the name of the application, the version number of the application, and the status of the application.

COLUMN PDB_NAME FORMAT A15
COLUMN APP_NAME FORMAT A15
COLUMN APP_VERSION FORMAT A20
COLUMN APP_STATUS FORMAT A12
 
SELECT p.PDB_NAME, s.APP_NAME, s.APP_VERSION, s.APP_STATUS
  FROM DBA_PDBS p, DBA_APP_PDB_STATUS s
  WHERE p.CON_UID = s.CON_UID;

Your output is similar to the following:

PDB_NAME        APP_NAME        APP_VERSION          APP_STATUS
--------------- --------------- -------------------- ------------
SALES1          SALESAPP        4.2                  NORMAL

Example 19-19 Viewing Information About Application Statements

This query shows the statement ID, capture time, SQL statement, and application name for the SQL statements for applications in the application container.

SET LONG  8000
SET PAGES 8000
COLUMN STATEM_ID FORMAT NNNNN
COLUMN CAPTURE_TIME FORMAT A12
COLUMN APP_STATEMENT FORMAT A36
COLUMN APP_NAME FORMAT A15
 
SELECT STATEMENT_ID AS STATEM_ID, CAPTURE_TIME, APP_STATEMENT, APP_NAME
FROM   DBA_APP_STATEMENTS
ORDER BY STATEMENT_ID;

Your output is similar to the following:

STATEM_ID CAPTURE_TIME APP_STATEMENT                        APP_NAME
--------- ------------ ------------------------------------ ---------------
        1 30-AUG-15    SYS                                  APP$1E87C094764
                                                            1142FE0534018F8
                                                            0AA6C5
        2 30-AUG-15    ALTER PLUGGABLE DATABASE APPLICATION APP$1E87C094764
                        APP$CON BEGIN INSTALL '1.0'         1142FE0534018F8
                                                            0AA6C5
        3 30-AUG-15    ALTER PLUGGABLE DATABASE APPLICATION APP$1E87C094764
                        APP$CON END INSTALL '1.0'           1142FE0534018F8
                                                            0AA6C5
        4 30-AUG-15    SYS                                  SALESAPP
        5 30-AUG-15    ALTER PLUGGABLE DATABASE APPLICATION SALESAPP
                        salesapp BEGIN INSTALL '1.0'
        6 30-AUG-15    CREATE TABLE oe.cmtb SHARING=METADAT SALESAPP
                       A (
                          value   VARCHAR2(30),
                          country VARCHAR2(30))
        7 30-AUG-15    CREATE TABLE conmap (                SALESAPP
                           country VARCHAR2(30) NOT NULL)
                       PARTITION BY LIST (country) (
                       PARTITION AMER VALUES ('US','MEXICO'
                       ,'CANADA'),
                       PARTITION EURO VALUES ('UK','FRANCE'
                       ,'GERMANY'),
                       PARTITION ASIA VALUES ('INDIA','CHIN
                       A','JAPAN'))
        8 30-AUG-15    ALTER TABLE oe.cmtb ENABLE CONTAINER SALESAPP
                       _MAP
        9 30-AUG-15    ALTER PLUGGABLE DATABASE APPLICATION SALESAPP
                        salesapp END INSTALL '1.0'
.
.
.

Example 19-20 Viewing Information About Application Versions

This query shows the name of the application that was versioned, the version number, and the comment for the version.

COLUMN APP_NAME FORMAT A15
COLUMN APP_VERSION FORMAT A20
COLUMN APP_VERSION_COMMENT FORMAT A25
 
SELECT APP_NAME, APP_VERSION, APP_VERSION_COMMENT
  FROM DBA_APP_VERSIONS;

Your output is similar to the following:

APP_NAME        APP_VERSION          APP_VERSION_COMMENT
--------------- -------------------- -------------------------
SALESAPP        1.0                  Sales Application

Example 19-21 Viewing Information About Application Patches

This query shows the name of the application that was patched, the patch number, the minimum application version for the patch, and the status of the patch for each patch in the application container.

COLUMN APP_NAME FORMAT A15
COLUMN PATCH_NUMBER FORMAT NNNNNNNN
COLUMN PATCH_MIN_VERSION FORMAT A10
COLUMN PATCH_STATUS FORMAT A15
 
SELECT APP_NAME, PATCH_NUMBER, PATCH_MIN_VERSION, PATCH_STATUS
  FROM DBA_APP_PATCHES;

Your output is similar to the following:

APP_NAME        PATCH_NUMBER PATCH_MIN_ PATCH_STATUS
--------------- ------------ ---------- ---------------
SALESAPP                   1 1.2        INSTALLED

Example 19-22 Viewing Details About Errors Raised During Application Synchronization

This query shows the application name, the SQL statement that raised the error, the error number, and the error message for errors raised during application synchronization.

SET LONG  8000
SET PAGES 8000
COLUMN APP_NAME FORMAT A15
COLUMN APP_STATEMENT FORMAT A36
COLUMN ERRORNUM FORMAT NNNNNNNN
COLUMN ERRORMSG FORMAT A20

SELECT APP_NAME, APP_STATEMENT, ERRORNUM, ERRORMSG
  FROM DBA_APP_ERRORS;

Example 19-23 Listing the User-Created Shared Database Objects in an Application Container

This query shows the owner and name of the user-created shared database objects in the application container. It also shows whether each shared database object is a metadata-linked application common object or a data-linked application common object. The query excludes Oracle-supplied shared database objects.

COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A25
COLUMN SHARING FORMAT A13 

SELECT OWNER, OBJECT_NAME, SHARING 
   FROM DBA_OBJECTS WHERE SHARING != 'NONE' 
   AND ORACLE_MAINTAINED = 'N';

Your output is similar to the following:

OWNER           OBJECT_NAME               SHARING
--------------- ------------------------- -------------
SALESADM        CONMAP                    METADATA LINK
OE              PRODUCT_DESCRIPTIONS_OB   DATA LINK
OE              CMTB                      METADATA LINK

Example 19-24 Listing the Extended Data-Linked Tables in an Application Container

This query shows the owner and name of the extended data-linked tables in the application container.

COLUMN OWNER FORMAT A20
COLUMN TABLE_NAME FORMAT A30
 
SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE EXTENDED_DATA_LINK='YES';

Your output is similar to the following:

OWNER                TABLE_NAME
-------------------- ------------------------------
SALESADM             ZIPCODES