This discussion is archived
12 Replies Latest reply: Jul 14, 2013 7:27 PM by user12187546 RSS

Question about oracle 12c container's temp tablespace list

user12187546 Newbie
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    >

    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
    惜分飞 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    No different. Same Result.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points