Hi,
i currently try out Multitenant Database and after reading the docs and create statement for PDBs i had assumed that a PDB could either use an own TEMP tablespace or share the CDBs TEMP tablespace. Yet i seem to be unable to archive the latter one.
create pluggable database pdb1 admin user admin identified by XXX;
create pluggable database pdb2 admin user admin identified by XXX storage (maxsize 2G max_shared_temp_size 200M);
--PDBs Temp tablespace is always 200MB in size and i can only specify wether i want to reuse a temp datafile in case it exists, but nothing on size on create nor can i drop it later. I can only change it to some other tablespace within the PDB if i want
alter session set container=PDB1;
drop tablespace temp including contents and datafiles;
alter session set container=PDB2;
drop tablespace temp including contents and datafiles;
--ORA-12906: cannot drop default temporary tablespace
--set small size to temp and disabled autoextend in PDB and ensure it can grow in CDB
alter database tempfile 1 resize 100M;
alter database tempfile 1 autoextend on next 100M maxsize 5G;
alter session set container=PDB1;
alter database tempfile 3 resize 100M;
alter database tempfile 3 autoextend off;
alter session set container=PDB2;
alter database tempfile 4 resize 100M;
alter database tempfile 4 autoextend off;
--reduce pga in root to force a temp file usage on sort
alter session set container=CDB$ROOT;
alter system set pga_aggregate_target=10M;
--now run a statement in the two PDBs that should cause temp file to grow if it can
select * from dba_objects o1,dba_objects o2 order by 1,2,3,4,5,6,7;
That always fails with ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.
The tempfiles in CDB and PDB stay at 100MB in size. For PDB1 that is what i did expect.
For PDB2 i had expected that it did use the tempfile of the CDB to spill over after its local temp file is full. Atleast this is the only remaining use of the "shared_temp" that comes to my mind if i can't get entirely ridden of the PDBs local temp tablespace.
So i can't remove the temp tablespace entirely in a PDB (to switch to CDB's temp tablespace) and i can't use the temp tablespace of the CDB in any manner as spill over area?
What is then the purpose of the max_shared_temp_size parameter during PDB creation?
Any way to use a common temp tablespace in the CDB that i'm missing?
Regards
Thomas