[OracleDatabase12cNewFeature] aggrespondataac

In the latest version of OracleDatabase12.1.0.2, the new feature provides the PDBContainers clause to directly aggregate and query data in the same table of multiple PDBs from the CDB $ ROOT layer. The section in the new feature document is described as follows: But the implementation does not seem so simple. The current test environment is as follows: currently, CDB has two PDB types, PDB1 and

In the latest version of Oracle Database 12.1.0.2, the new feature provides the PDB Containers clause to directly aggregate and query data in the same table of multiple PDBs from the CDB $ ROOT layer. The section in the new feature document is described as follows: But the implementation does not seem so simple. The current test environment is as follows: currently, CDB has two PDB types, PDB1 and

In the latest version of Oracle Database 12.1.0.2, the new feature provides the PDB Containers clause to directly aggregate and query data in the same table of multiple PDBs from the CDB $ ROOT layer. The section in the new feature document is described as follows:

But it does not seem so easy to implement.

The current test environment is as follows: currently, CDB has two PDB types, PDB1 and PDB2. Each PDB has a Local User with the same name, which is KAMUS; each KAMUS user has an TT table with the same table structure and different data.

• First, as expected, using SYS user queries directly in CDB $ ROOT will report a ORA-00942 error.

SQL> SHOW USERUSER IS "SYS"SQL> SHOW con_name CON_NAME------------------------------CDB$ROOT SQL> SELECT COUNT(*) FROM CONTAINERS(KAMUS.TT) WHERE CON_ID IN (3);SELECT COUNT(*) FROM CONTAINERS(KAMUS.TT) WHERE CON_ID IN (3)                                      *ERROR at line 1:ORA-00942: TABLE OR VIEW does NOT exist

• This requires that we create a Common User first. And grant it sufficient permissions. Grant the select any table permission to facilitate the test. in the real environment, you may need to plan the permission more precisely.

SQL> CREATE USER C##KAMUS IDENTIFIED BY oracle DEFAULT tablespace users; USER created. SQL> GRANT dba TO C##KAMUS CONTAINER=ALL; GRANT succeeded. SQL> GRANT SELECT any TABLE TO C##KAMUS CONTAINER=ALL; GRANT succeeded.

• Next, we need to use the Common User to connect all the PDBs that need to be aggregated and query, and create a view with the same name as the table.

sqlplus "C##KAMUS/oracle@db-cluster-scan:1521/pdb1"CREATE OR REPLACE VIEW tt AS SELECT * FROM kamus.tt;ALTER SESSION SET container=pdb2;CREATE OR REPLACE VIEW tt AS SELECT * FROM kamus.tt;

• You also need to create an empty table with the same name in the Common User, otherwise the query still reports a ORA-00942 error.

SQL> SHOW USERUSER IS "C##KAMUS"SQL> SHOW con_name CON_NAME------------------------------CDB$ROOTSQL> CREATE TABLE TT (dummy CHAR(1)); TABLE created. SQL> SELECT COUNT(*) FROM CONTAINERS(TT);   COUNT(*)----------    117362

• You only need to create a table with the same name and can aggregate the Query count. However, if a specific column is involved in an SQL statement, the problem persists. According to the P000 process disclosed in the error report, Oracle uses parallel queries in this process. Different parallel subprocesses query related tables in different PDB, the summary is displayed at the CDB level.

SQL> SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11;SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11*ERROR at line 1:ORA-12801: error signaled IN parallel query server P000, instance dbserver1.vbox.com:cdb12c_1 (1)ORA-00904: "OBJECT_NAME": invalid identifier  SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME='XX';SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME='XX'*ERROR at line 1:ORA-12801: error signaled IN parallel query server P000, instance dbserver1.vbox.com:cdb12c_1 (1)ORA-00904: "OBJECT_NAME": invalid identifier

• Therefore, you can add all columns for the expected aggregate query to the TT table of the C # KAMUS user. The OBJECT_NAME field is added here. You can see that the OBJECT_NAME field of the number type is added to the test, the OBJECT_NAME field in PDB is of the varchar2 type. Therefore, you only need to have the same column name and no need to have the same type.

SQL> ALTER TABLE TT ADD OBJECT_NAME NUMBER(10); TABLE altered. SQL> SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11; OBJECT_NAME------------------------------------ICOL$I_CDEF3TS$CDEF$I_FILE2I_OBJ5I_OBJ1I_OBJ4I_USER2I_COL2 10 ROWS selected. SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME LIKE 'ICOL%';   COUNT(*)----------        12

• As shown above, if it is simpler, create an empty table in C # KAMUS that has the same structure as the KAMUS. TT table in PDB. Impdp is used here.

impdp C##KAMUS/oracle@db-cluster-scan:1521/cdb12c DIRECTORY=dpump DUMPFILE=expdat.dmp EXCLUDE=TABLE_DATA TABLES=KAMUS.TT REMAP_SCHEMA=KAMUS:C##KAMUS SQL> SHOW con_name CON_NAME------------------------------CDB$ROOTSQL> SHOW USERUSER IS "C##KAMUS"SQL> SELECT TABLE_NAME FROM tabs; TABLE_NAME------------------------------TT SQL> SELECT COUNT(*) FROM TT;   COUNT(*)----------         0 SQL>SQL> SELECT COUNT(*) FROM CONTAINERS(TT);   COUNT(*)----------    117386 SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE CON_ID IN (3);   COUNT(*)----------     58693

• Check the execution plan. The name of the final TABLE is not displayed in the execution plan, but the fixed table name such as X $ CDBVW $ is displayed, it is difficult to determine the actual execution path in the execution plan of CDB.

SQL> SET autot ONSQL> SELECT COUNT(*) FROM CONTAINERS(TT);    COUNT(*)----------    117386  Execution Plan----------------------------------------------------------Plan hash VALUE: 3954817379 ---------------------------------------------------------------------------------------------------------------------------| Id  | Operation                 | Name     | ROWS  | Cost (%CPU)| TIME     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT          |          |     1 |     1 (100)| 00:00:01 |       |       |        |      |            ||   1 |  SORT AGGREGATE           |          |     1 |            |          |       |       |        |      |            ||   2 |   PX COORDINATOR          |          |       |            |          |       |       |        |      |            ||   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  ||   4 |     SORT AGGREGATE        |          |     1 |            |          |       |       |  Q1,00 | PCWP |            ||   5 |      PX PARTITION LIST ALL|          | 58693 |     1 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            ||   6 |       FIXED TABLE FULL    | X$CDBVW$ | 58693 |     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |---------------------------------------------------------------------------------------------------------------------------  Statistics----------------------------------------------------------     117574  recursive calls          0  db block gets      58796  consistent gets          0  physical reads        124  redo SIZE        544  bytes sent via SQL*Net TO client        551  bytes received via SQL*Net FROM client          2  SQL*Net roundtrips TO/FROM client         13  sorts (memory)          0  sorts (disk)          1  ROWS processed

Conclusion: The operation is a little complex and the function is normal.

Share/Save

Related posts:

1. How to resolve ORA-24005 when drop tablespace
2. How to Use DBMS_ADVANCED_REWRITE in Oracle 10g
3. [Oracle Database 12c New Feature] How to Learn Oracle (12c New Feature) from Error