Re: About PDB CONTAINERS Clause in 12.1.0.2
- From: Kamus@Gmail <kamusis@xxxxxxxxx>
- To: oracle_l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Oct 2014 14:47:23 -0700
I fotgot to paste the error message, BTW, in concept, I can understand why
can’t aggregate the local user’s table, but just try to confirm if I missed
something, since the manual do say, “The CONTAINERS clause accepts a table or
view name as an input parameter that is expected to exist in all PDBs in that
container. "
—Blank table in CDB$ROOT
SQL> select count(*) from TT;
COUNT(*)
----------
0
—aggregate count(*) from all the PDBs
SQL> select count(*) from CONTAINERS(TT);
COUNT(*)
----------
117362
—aggregate common user’s view is OK
SQL> select count(*) from CONTAINERS(C##KAMUS.TT);
COUNT(*)
----------
117362
—aggregate local user’s table got error.
SQL> select count(*) from CONTAINERS(KAMUS.TT) where OBJECT_NAME like 'ICOL%';
select count(*) from CONTAINERS(KAMUS.TT) where OBJECT_NAME like 'ICOL%'
*
ERROR at line 1:
ORA-00942: table or view does not exist
—of course, KAMUS.TT is there.
SQL> select count(*) from KAMUS.TT@PDB1;
COUNT(*)
----------
58693
SQL> select count(*) from KAMUS.TT@PDB2;
COUNT(*)
----------
58669
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
On October 2, 2014 at 2:34:18 PM, Kamus@Gmail (kamusis@xxxxxxxxx) wrote:
Hi lists
I’m trying the new features of 12.1.0.2, one of them is PDB CONTAINERS clause,
let us aggregate data across many PDBs from the root.
But I can’t try it out just like the “New Feature” said:
SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);
I have to follow the process that mentioned in “Administrators Guide” to
achieve the goal:
1. create a common user can access the that table (e.g., scott.emp) in all the
PDBs
2. create a view as select * from that table in all the PDBs by common user
3. create a blank table in CDB$ROOT exactly the same name as that table, then
we can count(*) aggregately
4. If want to query the column data, should add the same column into the blank
table in CDB, data type is irrelevant, just need the same column name.
Although the above process looks flexible, but a little cumbersome.
Is the “New Feature” manual wrong? Or do I miss something?
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
Other related posts:
