12 Replies Latest reply: Jul 14, 2013 9:27 PM by user12187546 RSS

    Question about oracle 12c container's temp tablespace list

    user12187546

      select con_id, name From v$containers;  ---> 4 rows returned.

       

      1CDB$ROOT
      2PDB$SEED
      3PDBRED
      4

      PDBBLUE

       

       

      select con_id, name  From v$tempfile;  ---> 4 rows returned.

       

      1/u01/app/oracle/oradata/cdb1/temp01.dbf
      2/u01/app/oracle/oradata/cdb1/pdbseed/pdbseed_temp01.dbf
      3/u01/app/oracle/oradata/cdb1/pdbred/temp01.dbf
      4

      /u01/app/oracle/oradata/cdb1/pdbblue/pdbseed_temp01.dbf

       

       

      select CON_ID, TABLESPACE_NAME from cdb_tablespaces where TABLESPACE_NAME like 'TEMP%';   ---> 4 rows returned.

      4TEMP
      3TEMP
      2TEMP
      1

      TEMP

       

      select * from v$temp_space_header; <---------- why return only one row ???

       

      1

      TEMP

       

      12c 's bug?

        • 1. Re: Question about oracle 12c container's temp tablespace list
          Reid Ricks-Oracle

          Are you able to select the file_id from the view in v$temp_space_header?

          • 2. Re: Question about oracle 12c container's temp tablespace list
            Reid Ricks-Oracle

            Not a bug but related to the following:

             

            What is a multitenant container database ?

            A 12c database is either a non-Container Database or a Container Database -  referred to as non-CDB or CDB respectively.
            A CDB is an Oracle database that includes zero, one, or many customer-created Containers or Pluggable Databases referred to as PDB.
            The CDB has:

            • one ROOT container (CDB$ROOT) containing SYSTEM, SYSAUX, UNDO, and TEMP tablespaces, Controlfiles and Redologs
            • one SEED container (PDB$SEED) containing SYSTEM, SYSAUX, TEMP, EXAMPLE tablespaces, used as a template to create new PDBs


            What is a Pluggable Database
            ?

             

            A pluggable Database (PDB) is a user-created container holding the data for any application.
            A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains  other user created tablespaces in it.

            Change done to Pdb database would be written to the Undo /redo present in the CDB$ROOT.

             

            By Default When you Connect as  sysdba without any servicename you connect to Root Container  <===========  the answer is here

            • 3. Re: Question about oracle 12c container's temp tablespace list
              user12187546

              Sorry for my poor enligsh.

               

              I executed queries in ROOT contaner .

               

              select con_id, name From v$tablespace order by 1,2;   --> root + seed + pluggable

              1SYSAUX
              1SYSTEM
              1TEMP
              1UNDOTBS1
              1USERS
              2SYSAUX
              2SYSTEM
              2TEMP
              3SYSAUX
              3SYSTEM
              3TEMP
              3USERS
              4SYSAUX
              4SYSTEM
              4

              TEMP

               

               

              select con_id, name from v$tempfile;     --> root + seed + pluggable

              1/u01/app/oracle/oradata/cdb1/temp01.dbf
              2/u01/app/oracle/oradata/cdb1/pdbseed/pdbseed_temp01.dbf
              3/u01/app/oracle/oradata/cdb1/pdbred/temp01.dbf
              4/u01/app/oracle/oradata/cdb1/pdbblue/pdbseed_temp01.dbf

               

              select con_id, file_id, tablespace_name from v$temp_space_header;  --> only root????  I think that this shoud show other con_id(2,3,4) 's temp also. but only root containers' temp tbs; why ???

              11

              TEMP

              • 4. Re: Question about oracle 12c container's temp tablespace list
                Reid Ricks-Oracle

                Best to file a service request to see if there is some issue or by design.

                • 5. Re: Question about oracle 12c container's temp tablespace list
                  StefanKoehler

                  Hi,

                  why raising a SR and annoy the Oracle Support for such simple re-searching tasks.

                   

                  @user12187546: Can you please post the output of the following two queries:

                   

                  SQL> select ts#, name, contents$, bitmapped, online$, plugged from ts$;
                  SQL> select KTFTHCTSN, CON_ID, KTFTHCCVAL from x$ktfthc;
                  
                  

                   

                  The view GV$TEMP_SPACE_HEADER is based on the table ts$ and fixed (in memory) table x$ktfthc.

                  You already have the answer, if the column ts# is increasing for each TS and the corresponding entry is missing in x$ktfthc. Please run the above SQLs to verify this.

                   

                  Regards

                  Stefan

                  • 6. Re: Question about oracle 12c container's temp tablespace list
                    rp0428

                    I agree - with 12c when the docs don't explain something like this (and examining the docs and view DDL do not explain it) a user with a support contract should file an SOR.

                     

                    For new functionality there simply isn't anyway for anyone but Oracle to know exactly what these views should return so can't know if the results OP is getting are correct or not.

                     

                    If the results are NOT correct Oracle needs to know about it and OP has a simple test case that shows the problem.

                    • 7. Re: Question about oracle 12c container's temp tablespace list
                      user12187546

                      16:45:29 SYS@cdb1>select ts#, name, contents$, bitmapped, online$, plugged from ts$;

                             TS# NAME                            CONTENTS$  BITMAPPED    ONLINE$    PLUGGED
                      ---------- ------------------------------ ---------- ---------- ---------- ----------
                               0 SYSTEM                                  0          8          1          0
                               1 SYSAUX                                  0          8          1          0
                               2 UNDOTBS1                                0          8          1          0
                               3 TEMP                                    1        128          1          0
                               4 USERS                                   0          8          1          0
                               5 UNDOTBS2                                0          8          3          0

                      6 rows selected.

                      16:45:35 SYS@cdb1>select KTFTHCTSN, CON_ID, KTFTHCCVAL from x$ktfthc;

                      KTFTHCTSN     CON_ID KTFTHCCVAL
                      ---------- ---------- ----------
                               3          1          0
                               2          2          0
                               2          3          0
                               2          4          0

                      16:45:56 SYS@cdb1>

                      • 8. Re: Question about oracle 12c container's temp tablespace list
                        StefanKoehler

                        Hi,

                        and here you got the answer. The dictionary table ts$ contains only the "local tablespaces" (in your case the ones of the CDB).

                         

                        FYI - the join condition of the view GV$TEMP_SPACE_HEADER is based on the following clause:

                        from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0
                        

                         

                        The view V$TABLESPACE is based on the control file and not on the Oracle dictionary table (as mentioned in the official documentation here: http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn30277.htm#i1420852 - "V$TABLESPACE displays tablespace information from the control file."), but the control file (fixed table x$kccts) is missing the needed block size information for calculation. You can write your own query based on x$kccts and x$ktfthc, if you are using a specific block size (in every environment and every time), but Oracle itself needs to be more generic of course.

                         

                        Regards

                        Stefan

                        • 9. Re: Question about oracle 12c container's temp tablespace list
                          user12187546

                          Thank you for detail explanation

                           

                          I think that

                          Oracle Document need to mention above description

                          or

                          GV$TEMP_SPACE_HEADER 's defintion script need to be changed using x$kccts and x$ktfthc

                           

                           

                          Sorry for my poor english.

                           

                           

                           

                          V$TEMP_SPACE_HEADER

                          V$TEMP_SPACE_HEADER displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how much is free as identified in the space header.

                          ColumnDatatypeNULLDescription
                          TABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the temporary tablespace
                          FILE_IDNUMBER Absolute file number
                          BYTES_USEDNUMBER How many bytes are in use
                          BLOCKS_USEDNUMBER How many blocks are in use
                          BYTES_FREENUMBER How many bytes are free
                          BLOCKS_FREENUMBER How many blocks are free
                          RELATIVE_FNONUMBER The relative file number for the file
                          CON_IDNUMBER The ID of the container to which the data pertains. Possible values include:
                          • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
                          • 1: This value is used for rows containing data that pertain to only the root
                          • n: Where n is the applicable container ID for the rows containing data
                          • 10. Re: Question about oracle 12c container's temp tablespace list
                            rp0428

                            >

                            I think that

                            Oracle Document need to mention above description

                            or

                            GV$TEMP_SPACE_HEADER 's defintion script need to be changed using x$kccts and x$ktfthc

                            >

                            Which is exactly why you should open an SOR with Oracle.

                             

                            Only Oracle knows whether that view is displaying the information that should be displayed or if there is an error that needs to be fixed. All of the container technology is new and that means that only Oracle knows what they INTEND to expose and display in the new system views.

                             

                            Knowing why you are getting the info you got is nice but it doesn't answer the question of whether that is the CORRECT info.

                            • 11. Re: Question about oracle 12c container's temp tablespace list
                              惜分飞

                              You should ensure that your pdb is read write, rather than mount state. You try to open pdb, then select V $ TEMP_SPACE_HEADER

                              • 12. Re: Question about oracle 12c container's temp tablespace list
                                user12187546

                                No different. Same Result.