2 Replies Latest reply on Apr 23, 2014 6:07 PM by user12037272





      recently we've noticed that mgmt$db_tablespaces info looks outdated, so when the report arrives it is showing 'old' information

      is there any way to force or to change the way that this table info is updated??



        • 1. Re: mgmt$db_tablespaces
          Courtney Llamas-Oracle

          What report and what information are you trying to obtain?    One db or multiple?    EM  11g or 12c?    DB version?

          • 2. Re: mgmt$db_tablespaces



            we are using the following query in order to obtain all tablespace status from our grid control 11


            select target_name, tablespace_name, status, extent_management, hostname, tstype, allocation, size_mb, used_mb,

            (size_mb - used_mb) free_mb,

            decode(size_mb, 0,0,trunc( (used_mb/size_mb)*100,2))  "%", ae  from (

            SELECT target_name,   tablespace_name AS TABLESPACE_NAME,

                                   status AS STATUS,

                                   extent_management AS EXTENT_MANAGEMENT,

                                   host_name AS HOSTNAME,

                                   contents AS TSTYPE,

                                   allocation_type AS ALLOCATION,

                                   --logging AS TBSP_ENABLE_LOGGING,

                                    trunc(NVL (tablespace_size / 1024 / 1024, 0),2)        AS SIZE_MB,

                                    trunc(NVL (TABLESPACE_USED_SIZE / 1024 / 1024, 0),0)                        AS USED_MB,                          

                                   NVL (initial_ext_size / 1024, 0)    AS TBSP_INIT_EXT_K,

                                   NVL (increment_by, 0)                        AS TBSP_INC_PCT,

                                                          -- target_type AS LBL_TARGETTYPE

                                                          ( select count(*) from MGMT$DB_DATAFILES  df where DF.TARGET_NAME = ts.target_name and DF.TABLESPACE_NAME = TS.TABLESPACE_NAME and autoextensible  = 'YES' ) ae 

                              FROM mgmt$db_tablespaces ts

                              where  contents not in  ( 'UNDO')

            and target_name not in ('PINREP_230','PINPRD.colombiamovil.corp_PINPRD1','PINPRD.colombiamovil.corp_PINPRD2','PNUM_PNUM1','PNUM_PNUM2')


            where  decode(size_mb, 0,0,trunc( (used_mb/size_mb)*100,2))  > 84

            and ae = 0

            order by target_name, tablespace_name