Rem
Rem $Header: catspace.sql 30-aug-99.10:41:30 smuthuli Exp $
Rem
Rem catspace.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catspace.sql - CATalog SPACE management 
Rem
Rem    DESCRIPTION
Rem      declares all space management views and includes relevant space
Rem      management related packages.
Rem
Rem    NOTES
Rem      currently dbms_space is in dbmsutil.sql - we should probably move 
Rem      that here.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    smuthuli    08/30/99 - more space views: merge from 817
Rem    smuthuli    07/12/99 - rename dbmsspace.sql to dbmsspc.sql
Rem    nireland    06/21/99 - Remove meaningless values for temp tbs. #891996  
Rem    rshaikh     05/24/99 - remove com so views dont show in dictionary view
Rem    akruglik    07/01/98 - modify definition of SYS_OBJECTS to include LOB 
Rem                           partitions and subpartitions
Rem    bhimatsi    06/22/98 - fix space views to use correct file#
Rem    jwlee       04/29/98 - add column PLUGGED_IN to dba_tablespaces
Rem    sbasu       04/27/98 - add index subpartition to sys_objects list
Rem    nireland    04/24/98 - Don't show dropped tablespaces in
Rem                           USER_TABLESPACES. #553723
Rem    bhimatsi    04/14/98 - bitmap ts - fixed views synonyms
Rem    bhimatsi    04/08/98 - tempfiles - view changes
Rem    bhimatsi    04/03/98 - dba_temp_files - fix
Rem    atsukerm    03/31/98 - revised bitmapped syntax.
Rem    bhimatsi    03/26/98 - temp tablespaces
Rem    bhimatsi    03/26/98 - temp tablespaces
Rem    bhimatsi    03/20/98 - bitmapped ts - temp tablespaces
Rem    atsukerm    03/19/98 - put hints in the dba_extents.
Rem    bhimatsi    03/11/98 - fix _segs
Rem    bhimatsi    03/07/98 - eliminate index_stats
Rem    bhimatsi    02/27/98 - create file for space mgmgmt views and procedure
Rem    bhimatsi    02/27/98 - Created
Rem

-- load in the dbms_space_admin package
@@dbmsspc
-- load in the plb for above
@@prvtspad.plb

-- now definr the views - this has to be done in this order as the view
-- depends on functions from dbms_space_admin package

remark  FAMILY "SEGMENTS"
remark  Storage for all types of segments
remark  This family has no ALL member.
remark  define SYS_OBJECTS for use by segments views
remark  The sys_objects view is a basis for segments views 
remark  The sys_user_segs view is a basis for user_segments and 
remark  user_extents views
remark  The sys_dba_segs view is a basis for dba_segments and 
remark  dba_extents views
remark  the value of extents/blocks in coming out of sys_user_segs and
remark  sys_dba_segs is correct only only for non-bitmapped tablespaces
remark  for bitmapped tablespaces, we have a procedure which we execute
remark  on the view on top of these to reduce the calls to the least
remark  selective set
remark
remark IMPORTATN NOTE ON SYS_OBJECTS : The definition for sys_objects is
remark is replicated in kttm.c. So if you ever want to change the definition
remark of sys_objects please notify the owner of kttm.c.
remark 
create or replace view SYS_OBJECTS
    (OBJECT_TYPE, OBJECT_TYPE_ID, SEGMENT_TYPE_ID,
     OBJECT_ID, HEADER_FILE, HEADER_BLOCK, TS_NUMBER)
as
select decode(bitand(t.property, 8192), 8192, 'NESTED TABLE', 'TABLE'), 2, 5,
       t.obj#, t.file#, t.block#, t.ts#
from sys.tab$ t
where bitand(t.property, 1024) = 0               /* exclude clustered tables */
union all
select 'TABLE PARTITION', 19, 5,
       tp.obj#, tp.file#, tp.block#, tp.ts#
from sys.tabpart$ tp
union all
select 'CLUSTER', 3, 5,
       c.obj#, c.file#, c.block#, c.ts#
from sys.clu$ c
union all
select decode(i.type#, 8, 'LOBINDEX', 'INDEX'), 1, 6,
       i.obj#, i.file#, i.block#, i.ts#
from sys.ind$ i
where i.type# in (1, 2, 3, 4, 6, 7, 8, 9)
union all
select 'INDEX PARTITION', 20, 6,
       ip.obj#, ip.file#, ip.block#, ip.ts#
from sys.indpart$ ip
union all
select 'LOBSEGMENT', 21, 8,
       l.lobj#, l.file#, l.block#, l.ts#
from sys.lob$ l
union all
select 'TABLE SUBPARTITION', 34, 5,
       tsp.obj#, tsp.file#, tsp.block#, tsp.ts#
       from sys.tabsubpart$ tsp
union all
select 'INDEX SUBPARTITION', 35, 6,
       isp.obj#, isp.file#, isp.block#, isp.ts#
from sys.indsubpart$ isp
union all
select decode(lf.fragtype$, 'P', 'LOB PARTITION', 'LOB SUBPARTITION'),
       decode(lf.fragtype$, 'P', 40, 41), 8,
       lf.fragobj#, lf.file#, lf.block#, lf.ts#
from sys.lobfrag$ lf
/
grant select on SYS_OBJECTS to select_catalog_role
/
create or replace view SYS_USER_SEGS
    (SEGMENT_NAME,
     PARTITION_NAME,
     SEGMENT_TYPE, SEGMENT_TYPE_ID,
     TABLESPACE_ID, TABLESPACE_NAME, BLOCKSIZE,
     HEADER_FILE, HEADER_BLOCK,
     BYTES, BLOCKS, EXTENTS, 
     INITIAL_EXTENT, NEXT_EXTENT, 
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
     FREELISTS, FREELIST_GROUPS, BUFFER_POOL_ID, SEGMENT_FLAGS, SEGMENT_OBJD)
as
select o.name,
       o.subname,
       so.object_type, s.type#,
       ts.ts#, ts.name, ts.blocksize,
       s.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents, 
       s.iniexts * ts.blocksize, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extsize * ts.blocksize), 
       s.minexts, s.maxexts, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.cachehint, NVL(s.spare1, 0), s.hwmincr
from sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = userenv('SCHEMAID')
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
union all
select un.name, NULL,
       'ROLLBACK', s.type#,
       ts.ts#, ts.name, ts.blocksize,
       s.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, 
       s.maxexts, s.extpct,
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.cachehint, NVL(s.spare1, 0), s.hwmincr
from sys.ts$ ts, sys.undo$ un, sys.seg$ s
where s.file# = un.file#
  and s.block# = un.block#
  and s.ts# = un.ts#
  and s.ts# = ts.ts#
  and s.user# = userenv('SCHEMAID')
  and s.type# = 1
  and un.status$ != 1  
union all
select to_char(f.file#) || '.' || to_char(s.block#),
       NULL,
       decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',
                      4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,
       ts.ts#, ts.name, ts.blocksize,
       s.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extsize * ts.blocksize), 
       s.minexts, s.maxexts, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
  and s.ts# = f.ts#
  and s.file# = f.relfile#
  and s.user# = userenv('SCHEMAID')
  and s.type# not in (1, 5, 6, 8)
/
grant select on SYS_USER_SEGS to select_catalog_role
/
Rem note that the way dataobj number is returned from this view. It is taken
Rem from obj$ and undo$ rather than seg$. these are going to be same and hence
Rem it is ok. The reason it is done this way is because we use the same
Rem view for migrate purposes and at that time, seg$ doesnt have the objd
Rem also note that undo segment no is passed for objd for undo segs. this
Rem is exactly what we wish. 
create or replace view SYS_DBA_SEGS
    (OWNER, SEGMENT_NAME,
     PARTITION_NAME,
     SEGMENT_TYPE, SEGMENT_TYPE_ID,
     TABLESPACE_ID, TABLESPACE_NAME, BLOCKSIZE,
     HEADER_FILE, HEADER_BLOCK,
     BYTES, BLOCKS, EXTENTS, 
     INITIAL_EXTENT, NEXT_EXTENT,
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
     RELATIVE_FNO, BUFFER_POOL_ID, SEGMENT_FLAGS, SEGMENT_OBJD)
as
select u.name, o.name, o.subname,
       so.object_type, s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents, 
       s.iniexts * ts.blocksize, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extsize * ts.blocksize), 
       s.minexts, s.maxexts, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
     sys.file$ f
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = u.user#
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
  and s.ts# = f.ts#
  and s.file# = f.relfile#
union all
select u.name, un.name, NULL,
       'ROLLBACK', s.type#,
       ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, 
       s.maxexts, s.extpct,
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups), 
       s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
  and s.block# = un.block#
  and s.ts# = un.ts#
  and s.ts# = ts.ts#
  and s.user# = u.user#
  and s.type# = 1
  and un.status$ != 1
  and un.ts# = f.ts#
  and un.file# = f.relfile#
union all
select u.name, to_char(f.file#) || '.' || to_char(s.block#), NULL,
       decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',
                      4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL), 
				      s.extsize * ts.blocksize), 
       s.minexts, s.maxexts, 
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
  and s.user# = u.user#
  and s.type# not in (1, 5, 6, 8)
  and s.ts# = f.ts#
  and s.file# = f.relfile#
/
grant select on SYS_DBA_SEGS to select_catalog_role
/
remark USER_SEGMENTS masks out tablespace number from sys_user_segs
create or replace view USER_SEGMENTS
    (SEGMENT_NAME,
     PARTITION_NAME,
     SEGMENT_TYPE,
     TABLESPACE_NAME,
     BYTES, BLOCKS, EXTENTS, 
     INITIAL_EXTENT, NEXT_EXTENT, 
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
     BUFFER_POOL)
as
select segment_name, partition_name, segment_type, tablespace_name,
       dbms_space_admin.segment_number_blocks(tablespace_id, header_file,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, blocks)*blocksize,
       dbms_space_admin.segment_number_blocks(tablespace_id, header_file,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, blocks),
       dbms_space_admin.segment_number_extents(tablespace_id, header_file,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, extents),
       initial_extent, next_extent,
       min_extents, max_extents, pct_increase, freelists, freelist_groups,
       decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys_user_segs
/
comment on table USER_SEGMENTS is
'Storage allocated for all database segments'
/
comment on column USER_SEGMENTS.SEGMENT_NAME is
'Name, if any, of the segment'
/
comment on column USER_SEGMENTS.PARTITION_NAME is
'Partition/Subpartition Name, if any, of the segment'
/
comment on column USER_SEGMENTS.SEGMENT_TYPE is
'Type of segment:  "TABLE", "CLUSTER", "INDEX", "ROLLBACK", "DEFERRED ROLLBACK", "TEMPORARY", "SPACE HEADER", or "CACHE"'
/
comment on column USER_SEGMENTS.TABLESPACE_NAME is
'Name of the tablespace containing the segment'
/
comment on column USER_SEGMENTS.BYTES is
'Size, in bytes, of the segment'
/
comment on column USER_SEGMENTS.BLOCKS is
'Size, in Oracle blocks, of the segment'
/
comment on column USER_SEGMENTS.EXTENTS is
'Number of extents allocated to the segment'
/
comment on column USER_SEGMENTS.INITIAL_EXTENT is
'Size, in bytes, of the initial extent of the segment'
/
comment on column USER_SEGMENTS.NEXT_EXTENT is
'Size, in bytes, of the next extent to be allocated to the segment' 
/
comment on column USER_SEGMENTS.MIN_EXTENTS is
'Minimum number of extents allowed in the segment'
/
comment on column USER_SEGMENTS.MAX_EXTENTS is
'Maximum number of extents allowed in the segment'
/
comment on column USER_SEGMENTS.PCT_INCREASE is
'Percent by which to increase the size of the next extent to be allocated'
/
comment on column USER_SEGMENTS.FREELISTS is
'Number of process freelists allocated to this segment'
/
comment on column USER_SEGMENTS.FREELIST_GROUPS is
'Number of freelist groups allocated to this segment'
/
comment on column USER_SEGMENTS.BUFFER_POOL is
'The default buffer pool to be used for blocks from this segment'
/
drop public synonym USER_SEGMENTS
/
create public synonym USER_SEGMENTS for USER_SEGMENTS
/
grant select on USER_SEGMENTS to PUBLIC with grant option
/
remark DBA_SEGMENTS masks out tablespace number from sys_dba_segs
create or replace view DBA_SEGMENTS
    (OWNER, SEGMENT_NAME,
     PARTITION_NAME,
     SEGMENT_TYPE,
     TABLESPACE_NAME,
     HEADER_FILE, HEADER_BLOCK,
     BYTES, BLOCKS, EXTENTS, 
     INITIAL_EXTENT, NEXT_EXTENT,
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
     RELATIVE_FNO, BUFFER_POOL)
as
select owner, segment_name, partition_name, segment_type, tablespace_name,
       header_file, header_block,
       dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, blocks)*blocksize,
       dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, blocks),
       dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,
       header_block, segment_type_id, buffer_pool_id, segment_flags,
       segment_objd, extents),
       initial_extent, next_extent, min_extents, max_extents, pct_increase,
       freelists, freelist_groups, relative_fno,
       decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys_dba_segs
/
drop public synonym DBA_SEGMENTS
/
create public synonym DBA_SEGMENTS for DBA_SEGMENTS
/
grant select on DBA_SEGMENTS to select_catalog_role
/
comment on table DBA_SEGMENTS is
'Storage allocated for all database segments'
/
comment on column DBA_SEGMENTS.OWNER is
'Username of the segment owner'
/
comment on column DBA_SEGMENTS.SEGMENT_NAME is
'Name, if any, of the segment'
/
comment on column DBA_SEGMENTS.PARTITION_NAME is
'Partition/Subpartition Name, if any, of the segment'
/
comment on column DBA_SEGMENTS.SEGMENT_TYPE is
'Type of segment:  "TABLE", "CLUSTER", "INDEX", "ROLLBACK",
"DEFERRED ROLLBACK", "TEMPORARY","SPACE HEADER",  or "CACHE"'
/
comment on column DBA_SEGMENTS.TABLESPACE_NAME is
'Name of the tablespace containing the segment'
/
comment on column DBA_SEGMENTS.HEADER_FILE is
'ID of the file containing the segment header'
/
comment on column DBA_SEGMENTS.HEADER_BLOCK is
'ID of the block containing the segment header'
/
comment on column DBA_SEGMENTS.BYTES is
'Size, in bytes, of the segment'
/
comment on column DBA_SEGMENTS.BLOCKS is
'Size, in Oracle blocks, of the segment'
/
comment on column DBA_SEGMENTS.EXTENTS is
'Number of extents allocated to the segment'
/
comment on column DBA_SEGMENTS.INITIAL_EXTENT is
'Size, in bytes, of the initial extent of the segment'
/
comment on column DBA_SEGMENTS.NEXT_EXTENT is
'Size, in bytes, of the next extent to be allocated to the segment' 
/
comment on column DBA_SEGMENTS.MIN_EXTENTS is
'Minimum number of extents allowed in the segment'
/
comment on column DBA_SEGMENTS.MAX_EXTENTS is
'Maximum number of extents allowed in the segment'
/
comment on column DBA_SEGMENTS.PCT_INCREASE is
'Percent by which to increase the size of the next extent to be allocated'
/
comment on column DBA_SEGMENTS.FREELISTS is
'Number of process freelists allocated in this segment'
/
comment on column DBA_SEGMENTS.FREELIST_GROUPS is
'Number of freelist groups allocated in this segment'
/
comment on column DBA_SEGMENTS.RELATIVE_FNO is
'Relative number of the file containing the segment header'
/
comment on column DBA_SEGMENTS.BUFFER_POOL is
'The default buffer pool to be used for segments blocks'
/
remark
remark  FAMILY "EXTENTS"
remark  Extents associated with their segments.
remark
create or replace view USER_EXTENTS
    (SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
     EXTENT_ID, BYTES, BLOCKS)
as
select ds.segment_name, ds.partition_name, ds.segment_type, 
       ds.tablespace_name, e.ext#, e.length * ds.blocksize, e.length
from sys.uet$ e, sys.sys_user_segs ds
where e.segfile# = ds.header_file
  and e.segblock# = ds.header_block
  and e.ts# = ds.tablespace_id
  and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select /*+ ordered use_nl(e) */
       ds.segment_name, ds.partition_name, ds.segment_type, 
       ds.tablespace_name, e.ktfbueextno, e.ktfbueblks * ds.blocksize,
       e.ktfbueblks
from sys.sys_user_segs ds, sys.x$ktfbue e
where e.ktfbuesegfno = ds.header_file
  and e.ktfbuesegbno = ds.header_block
  and e.ktfbuesegtsn = ds.tablespace_id
  and bitand(NVL(ds.segment_flags,0), 1) = 1
/
comment on table USER_EXTENTS is
'Extents comprising segments owned by the user'
/
comment on column USER_EXTENTS.SEGMENT_NAME is
'Name of the segment associated with the extent'
/
comment on column USER_EXTENTS.PARTITION_NAME is
'Partition/Subpartition Name, if any, of the segment'
/
comment on column USER_EXTENTS.TABLESPACE_NAME is
'Name of the tablespace containing the extent'
/
comment on column USER_EXTENTS.SEGMENT_TYPE is
'Type of the segment'
/
comment on column USER_EXTENTS.EXTENT_ID is
'Extent number in the segment'
/
comment on column USER_EXTENTS.BYTES is
'Size of the extent in bytes'
/
comment on column USER_EXTENTS.BLOCKS is
'Size of the extent in ORACLE blocks'
/
drop public synonym USER_EXTENTS
/
create public synonym USER_EXTENTS for USER_EXTENTS
/
grant select on USER_EXTENTS to PUBLIC with grant option
/
create or replace view DBA_EXTENTS
    (OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
     EXTENT_ID, FILE_ID, BLOCK_ID,
     BYTES, BLOCKS, RELATIVE_FNO)
as
select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, 
       ds.tablespace_name,
       e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file#
from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f
where e.segfile# = ds.relative_fno
  and e.segblock# = ds.header_block
  and e.ts# = ds.tablespace_id
  and e.ts# = f.ts#
  and e.file# = f.relfile#
  and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select /*+ ordered use_nl(e) use_nl(f) */
       ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, 
       ds.tablespace_name,
       e.ktfbueextno, f.file#, e.ktfbuebno,
       e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno
from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
  and e.ktfbuesegbno = ds.header_block
  and e.ktfbuesegtsn = ds.tablespace_id
  and e.ktfbuesegtsn = f.ts#
  and e.ktfbuefno = f.relfile#
  and bitand(NVL(ds.segment_flags, 0), 1) = 1
/
drop public synonym DBA_EXTENTS
/
create public synonym DBA_EXTENTS for DBA_EXTENTS
/
grant select on DBA_EXTENTS to select_catalog_role
/
comment on table DBA_EXTENTS is
'Extents comprising all segments in the database'
/
comment on column DBA_EXTENTS.OWNER is
'Owner of the segment associated with the extent'
/
comment on column DBA_EXTENTS.SEGMENT_NAME is
'Name of the segment associated with the extent'
/
comment on column DBA_EXTENTS.PARTITION_NAME is
'Partition/Subpartition Name, if any, of the segment'
/
comment on column DBA_EXTENTS.TABLESPACE_NAME is
'Name of the tablespace containing the extent'
/
comment on column DBA_EXTENTS.SEGMENT_TYPE is
'Type of the segment'
/
comment on column DBA_EXTENTS.FILE_ID is
'Name of the file containing the extent'
/
comment on column DBA_EXTENTS.BLOCK_ID is
'Starting block number of the extent'
/
comment on column DBA_EXTENTS.EXTENT_ID is
'Extent number in the segment'
/
comment on column DBA_EXTENTS.BYTES is
'Size of the extent in bytes'
/
comment on column DBA_EXTENTS.BLOCKS is
'Size of the extent in ORACLE blocks'
/
comment on column DBA_EXTENTS.RELATIVE_FNO is
'Relative number of the file containing the segment header'
/
remark
remark  This view selects all the used extents in locally managed
remark  tablespaces. Built on top of x$ktfbue.
remark
create or replace view DBA_LMT_USED_EXTENTS
        (SEGMENT_FILEID, SEGMENT_BLOCK, TABLESPACE_ID,
         EXTENT_ID, FILEID, BLOCK, LENGTH)
as
select  ktfbuesegfno, ktfbuesegbno, ktfbuesegtsn,
        ktfbueextno, ktfbuefno, ktfbuebno,
        ktfbueblks
from    sys.x$ktfbue;
/
drop public synonym DBA_LMT_USED_EXTENTS
/
create public synonym DBA_LMT_USED_EXTENTS for DBA_LMT_USED_EXTENTS
/
grant select on DBA_LMT_USED_EXTENTS to select_catalog_role
/
comment on table DBA_LMT_USED_EXTENTS is
'All extents in the locally managed tablespaces'
/
comment on column DBA_LMT_USED_EXTENTS.SEGMENT_FILEID is
'File number of segment header of the extent'
/
comment on column DBA_LMT_USED_EXTENTS.SEGMENT_BLOCK is
'Block number of segment header of the extent'
/
comment on column DBA_LMT_USED_EXTENTS.TABLESPACE_ID is
'ID of the tablespace containing the extent'
/
comment on column DBA_LMT_USED_EXTENTS.EXTENT_ID is
'Extent number in the segment'
/
comment on column DBA_LMT_USED_EXTENTS.FILEID is
'File Number of the extent'
/
comment on column DBA_LMT_USED_EXTENTS.BLOCK is
'Starting block number of the extent'
/
comment on column DBA_LMT_USED_EXTENTS.LENGTH is
'Number of blocks in the extent'
/
remark
remark  This view selects the used extents in dictionary managed
remark  tablespaces. Built on top of uet$
remark
create or replace view DBA_DMT_USED_EXTENTS
        (SEGMENT_FILEID, SEGMENT_BLOCK, TABLESPACE_ID,
         EXTENT_ID, FILEID, BLOCK, LENGTH)
as
select  segfile#, segblock#, ts#,
        ext#, file#, block#, length
from    sys.uet$;
/
drop public synonym DBA_DMT_USED_EXTENTS
/
create public synonym DBA_DMT_USED_EXTENTS for DBA_DMT_USED_EXTENTS
/
grant select on DBA_DMT_USED_EXTENTS to select_catalog_role
/
comment on table DBA_DMT_USED_EXTENTS is
'All extents in the dictionary managed tablespaces'
/
comment on column DBA_DMT_USED_EXTENTS.SEGMENT_FILEID is
'File number of segment header of the extent'
/
comment on column DBA_DMT_USED_EXTENTS.SEGMENT_BLOCK is
'Block number of segment header of the extent'
/
comment on column DBA_DMT_USED_EXTENTS.TABLESPACE_ID is
'ID of the tablespace containing the extent'
/
comment on column DBA_DMT_USED_EXTENTS.EXTENT_ID is
'Extent number in the segment'
/
comment on column DBA_DMT_USED_EXTENTS.FILEID is
'File Number of the extent'
/
comment on column DBA_DMT_USED_EXTENTS.BLOCK is
'Starting block number of the extent'
/
comment on column DBA_DMT_USED_EXTENTS.LENGTH is
'Number of blocks in the extent'
/
remark
remark  FAMILY "FREE_SPACE"
remark  Free extents.
remark  This family has no ALL member.
remark
create or replace view USER_FREE_SPACE
    (TABLESPACE_NAME, FILE_ID, BLOCK_ID,
     BYTES, BLOCKS, RELATIVE_FNO)
as
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.fet$ f, sys.ts$ ts, sys.file$ fi
where f.ts# = ts.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )
/
comment on table USER_FREE_SPACE is
'Free extents in tablespaces accessible to the user'
/
comment on column USER_FREE_SPACE.TABLESPACE_NAME is
'Name of the tablespace containing the extent'
/
comment on column USER_FREE_SPACE.FILE_ID is
'ID number of the file containing the extent'
/
comment on column USER_FREE_SPACE.BLOCK_ID is
'Starting block number of the extent'
/
comment on column USER_FREE_SPACE.BYTES is
'Size of the extent in bytes'
/
comment on column USER_FREE_SPACE.BLOCKS is
'Size of the extent in ORACLE blocks'
/
comment on column USER_FREE_SPACE.RELATIVE_FNO is
'Relative number of the file containing the extent'
/
drop public synonym USER_FREE_SPACE
/
create public synonym USER_FREE_SPACE for USER_FREE_SPACE
/
grant select on USER_FREE_SPACE to PUBLIC with grant option
/
create or replace view DBA_FREE_SPACE
    (TABLESPACE_NAME, FILE_ID, BLOCK_ID,
     BYTES, BLOCKS, RELATIVE_FNO)
as
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
/
drop public synonym DBA_FREE_SPACE
/
create public synonym DBA_FREE_SPACE for DBA_FREE_SPACE
/
grant select on DBA_FREE_SPACE to select_catalog_role
/
comment on table DBA_FREE_SPACE is
'Free extents in all tablespaces'
/
comment on column DBA_FREE_SPACE.TABLESPACE_NAME is
'Name of the tablespace containing the extent'
/
comment on column DBA_FREE_SPACE.FILE_ID is
'ID number of the file containing the extent'
/
comment on column DBA_FREE_SPACE.BLOCK_ID is
'Starting block number of the extent'
/
comment on column DBA_FREE_SPACE.BYTES is
'Size of the extent in bytes'
/
comment on column DBA_FREE_SPACE.BLOCKS is
'Size of the extent in ORACLE blocks'
/
comment on column DBA_FREE_SPACE.RELATIVE_FNO is
'Relative number of the file containing the extent'
/
remark
remark Free extents in locally managed tablespaces
remark Built on top of x$ktfbfe
remark
create or replace view DBA_LMT_FREE_SPACE
        (TABLESPACE_ID, FILE_ID, BLOCK_ID, BLOCKS)
as
select  ktfbfetsn, ktfbfefno, ktfbfebno, ktfbfeblks
from    x$ktfbfe;
/
drop public synonym DBA_LMT_FREE_SPACE
/
create public synonym DBA_LMT_FREE_SPACE for DBA_LMT_FREE_SPACE
/
grant select on DBA_LMT_FREE_SPACE to select_catalog_role
/
comment on table DBA_LMT_FREE_SPACE is
'Free extents in all locally managed tablespaces'
/
comment on column DBA_LMT_FREE_SPACE.TABLESPACE_ID is
'ID of the tablespace containing the extent'
/
comment on column DBA_LMT_FREE_SPACE.FILE_ID is
'ID number of the file containing the extent'
/
comment on column DBA_LMT_FREE_SPACE.BLOCK_ID is
'Starting block number of the extent'
/
comment on column DBA_LMT_FREE_SPACE.BLOCKS is
'Size of the extent in blocks'
/
remark
remark Free extents in dictionary managed tablespaces
remark Built on top of fet$
remark
create or replace view DBA_DMT_FREE_SPACE
        (TABLESPACE_ID, FILE_ID, BLOCK_ID, BLOCKS)
as
select  ts#, file#, block#, length
from    fet$;
/
drop public synonym DBA_DMT_FREE_SPACE
/
create public synonym DBA_DMT_FREE_SPACE for DBA_DMT_FREE_SPACE
/
grant select on DBA_DMT_FREE_SPACE to select_catalog_role
/
comment on table DBA_DMT_FREE_SPACE is
'Free extents in all dictionary managed tablespaces'
/
comment on column DBA_DMT_FREE_SPACE.TABLESPACE_ID is
'ID of the tablespace containing the extent'
/
comment on column DBA_DMT_FREE_SPACE.FILE_ID is
'ID number of the file containing the extent'
/
comment on column DBA_DMT_FREE_SPACE.BLOCK_ID is
'Starting block number of the extent'
/
comment on column DBA_DMT_FREE_SPACE.BLOCKS is
'Size of the extent in blocks'
/
remark
remark  FAMILY "FREE_SPACE_COALESCED"
remark  Free extents which are Coalesced
remark  This family has only DBA member
remark
remark the following view is just used for constructing the main view 
remark
create or replace view DBA_FREE_SPACE_COALESCED_TMP1 as
select ts#, count(*) extents_coalesced, sum(length) blocks_coalesced
from sys.fet$ a 
where not exists (
  select * from sys.fet$ b 
  where b.ts#=a.ts# and
	b.file#=a.file# and
	a.block#=b.block#+b.length)
group by ts#
/
grant select on DBA_FREE_SPACE_COALESCED_TMP1 to select_catalog_role
/
REM comment on table DBA_FREE_SPACE_COALESCED_TMP1 is
REM 'Coalesced Free Extents for all Tablespaces'
REM /
comment on column DBA_FREE_SPACE_COALESCED_TMP1.ts# is
'Number of Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP1.extents_coalesced is
'Number of Coalesced Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP1.blocks_coalesced is
'Total Coalesced Free Oracle Blocks in Tablespace'
/
remark
remark this view is just used for constructing the main view 
remark
create or replace view 
  DBA_FREE_SPACE_COALESCED_TMP2(ts#, total_extents, total_blocks)
as
  select ts#, count(*), sum(length)
    from sys.fet$ 
  group by ts#
/
grant select on DBA_FREE_SPACE_COALESCED_TMP2 to select_catalog_role
/
REM comment on table DBA_FREE_SPACE_COALESCED_TMP2 is
REM 'Free Extents in Tablespaces'
REM /
comment on column DBA_FREE_SPACE_COALESCED_TMP2.ts# is
'Number of Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP2.total_extents is
'Number of Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP2.total_blocks is
'Total Free Blocks in Tablespace'
/
remark
remark this view is just used for constructing the main view 
remark
create or replace view 
  DBA_FREE_SPACE_COALESCED_TMP3(ts#, total_extents, total_blocks)
as
  select ktfbfetsn, count(*), sum(ktfbfeblks)
    from sys.x$ktfbfe
  group by ktfbfetsn
/
grant select on DBA_FREE_SPACE_COALESCED_TMP3 to select_catalog_role
/
REM comment on table DBA_FREE_SPACE_COALESCED_TMP3 is
REM 'Free Extents in Tablespaces'
REM /
comment on column DBA_FREE_SPACE_COALESCED_TMP3.ts# is
'Number of Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP3.total_extents is
'Number of Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED_TMP3.total_blocks is
'Total Free Blocks in Tablespace'
/
remark
remark MAIN VIEW for this family
remark Free extents which do not have any other free extents before them are
remark considered coalesced. This implies that if there is contiguous free
remark space represented as 5 free extents, then we consider the first of
remark these to be coalesced and the rest 4 to be non coalesced.
remark
create or replace view DBA_FREE_SPACE_COALESCED
    (TABLESPACE_NAME, TOTAL_EXTENTS, EXTENTS_COALESCED, PERCENT_EXTENTS_COALESCED, TOTAL_BYTES, BYTES_COALESCED, TOTAL_BLOCKS, BLOCKS_COALESCED, PERCENT_BLOCKS_COALESCED)
as
select name,total_extents, extents_coalesced, 
       extents_coalesced/total_extents*100,total_blocks*c.blocksize, 
       blocks_coalesced*c.blocksize, total_blocks, blocks_coalesced,
       blocks_coalesced/total_blocks*100
from DBA_FREE_SPACE_COALESCED_TMP1 a, DBA_FREE_SPACE_COALESCED_TMP2 b, 
      sys.ts$ c 
where a.ts#=b.ts# and a.ts#=c.ts#
union all
select name, total_extents, total_extents, 100, total_blocks*c.blocksize,
       total_blocks*c.blocksize, total_blocks, total_blocks, 100
from DBA_FREE_SPACE_COALESCED_TMP3 b, sys.ts$ c
where b.ts# = c.ts#
/
drop public synonym DBA_FREE_SPACE_COALESCED
/
create public synonym DBA_FREE_SPACE_COALESCED for DBA_FREE_SPACE_COALESCED
/
grant select on DBA_FREE_SPACE_COALESCED to select_catalog_role
/
comment on table DBA_FREE_SPACE_COALESCED is
'Statistics on Coalesced Space in Tablespaces'
/
comment on column DBA_FREE_SPACE_COALESCED.TABLESPACE_NAME is
'Name of Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.TOTAL_EXTENTS is
'Total Number of Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.EXTENTS_COALESCED is
'Total Number of Coalesced Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.PERCENT_EXTENTS_COALESCED is
'Percentage of Coalesced Free Extents in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.TOTAL_BYTES is
'Total Number of Free Bytes in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.BYTES_COALESCED is
'Total Number of Coalesced Free Bytes in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.TOTAL_BLOCKS is
'Total Number of Free Oracle Blocks in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.BLOCKS_COALESCED is
'Total Number of Coalesced Free Oracle Blocks in Tablespace'
/
comment on column DBA_FREE_SPACE_COALESCED.PERCENT_BLOCKS_COALESCED is
'Percentage of Coalesced Free Oracle Blocks in Tablespace'
/
remark
remark  FAMILY "DATA_FILES"
remark  Information about database files.
remark  This family has a DBA member only.
remark  (we also have added filext$ for compatibility with 7.2 release)
remark
create or replace view DBA_DATA_FILES
    (FILE_NAME, FILE_ID, TABLESPACE_NAME,
     BYTES, BLOCKS, STATUS, RELATIVE_FNO, AUTOEXTENSIBLE,
     MAXBYTES, MAXBLOCKS, INCREMENT_BY, USER_BYTES, USER_BLOCKS)
as
select v.name, f.file#, ts.name,
       ts.blocksize * f.blocks, f.blocks,
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
       ts.blocksize * f.maxextend, f.maxextend, f.inc,
       ts.blocksize * (f.blocks - 1), f.blocks - 1
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
where v.file# = f.file#
  and f.spare1 is NULL
  and f.ts# = ts.ts#
union all
select /*+ ordered use_nl(f) use_nl(hc) */
       v.name, hc.ktfbhcafno, ts.name, 
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL), 
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#, 
       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts
where v.file# = f.file#
  and f.spare1 is NOT NULL
  and f.file# = hc.ktfbhcafno
  and hc.ktfbhctsn = ts.ts#
/
drop public synonym DBA_DATA_FILES
/
create public synonym DBA_DATA_FILES for DBA_DATA_FILES
/
grant select on DBA_DATA_FILES to select_catalog_role
/
comment on table DBA_DATA_FILES is
'Information about database data files'
/
comment on column DBA_DATA_FILES.FILE_NAME is
'Name of the database data file'
/
comment on column DBA_DATA_FILES.FILE_ID is
'ID of the database data file'
/
comment on column DBA_DATA_FILES.TABLESPACE_NAME is
'Name of the tablespace to which the file belongs'
/
comment on column DBA_DATA_FILES.BYTES is
'Size of the file in bytes'
/
comment on column DBA_DATA_FILES.BLOCKS is
'Size of the file in ORACLE blocks'
/
comment on column DBA_DATA_FILES.STATUS is
'File status:  "INVALID" or "AVAILABLE"'
/
comment on column DBA_DATA_FILES.RELATIVE_FNO is
'Tablespace-relative file number'
/
comment on column DBA_DATA_FILES.AUTOEXTENSIBLE is
'Autoextensible indicator:  "YES" or "NO"'
/
comment on column DBA_DATA_FILES.MAXBYTES is
'Maximum size of the file in bytes'
/
comment on column DBA_DATA_FILES.MAXBLOCKS is
'Maximum size of the file in ORACLE blocks'
/
comment on column DBA_DATA_FILES.INCREMENT_BY is
'Default increment for autoextension'
/
comment on column DBA_DATA_FILES.USER_BYTES is
'Size of the useful portion of file in bytes'
/
comment on column DBA_DATA_FILES.USER_BLOCKS is
'Size of the useful portion of file in ORACLE blocks'
/
create or replace view FILEXT$
    (FILE#, MAXEXTEND, INC)
as
select f.file_id, f.maxblocks, f.increment_by
from sys.dba_data_files f where f.increment_by <> 0
/
comment on table FILEXT$ is
'Information about extensible files'
/
comment on column FILEXT$.FILE# is
'ID of the database file'
/
comment on column FILEXT$.MAXEXTEND is
'Maximum size of the file in ORACLE blocks'
/
comment on column FILEXT$.INC is
'Default increment for autoextension'
/
grant select on FILEXT$ to select_catalog_role
/
remark
remark  FAMILY "TABLESPACES"
remark  CREATE TABLESPACE parameters, except datafiles.
remark  This family has no ALL member.
remark
create or replace view USER_TABLESPACES
    (TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
     MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN,
     STATUS, CONTENTS, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE)
as select ts.name, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL), 
                        ts.blocksize * ts.dflincr), 
          ts.dflminext,
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext), 
          decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
          ts.blocksize * ts.dflminlen,
          decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
                 4, 'READ ONLY', 'UNDEFINED'),
          decode(ts.contents$, 0, 'PERMANENT', 1, 'TEMPORARY'),
          decode(ts.dflogging, 0, 'NOLOGGING', 1, 'LOGGING'),
          decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
          decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
	         'UNDEFINED')
from sys.ts$ ts
where ts.online$ != 3
      and (   exists (select null from sys.tsq$ tsq
                 where tsq.ts# = ts.ts#
                   and tsq.user# = userenv('SCHEMAID') and 
                   (tsq.blocks > 0 or tsq.maxblocks != 0))
           or exists
              (select null
              from sys.v$enabledprivs
              where priv_number = -15 /* UNLIMITED TABLESPACE */))
/
comment on table USER_TABLESPACES is
'Description of accessible tablespaces'
/
comment on column USER_TABLESPACES.TABLESPACE_NAME is
'Tablespace name'
/
comment on column USER_TABLESPACES.INITIAL_EXTENT is
'Default initial extent size'
/
comment on column USER_TABLESPACES.NEXT_EXTENT is
'Default incremental extent size'
/
comment on column USER_TABLESPACES.MIN_EXTENTS is
'Default minimum number of extents'
/
comment on column USER_TABLESPACES.MAX_EXTENTS is
'Default maximum number of extents'
/
comment on column USER_TABLESPACES.PCT_INCREASE is
'Default percent increase for extent size'
/
comment on column USER_TABLESPACES.MIN_EXTLEN is
'Minimum extent size for the tablespace'
/
comment on column USER_TABLESPACES.STATUS is
'Tablespace status: "ONLINE", "OFFLINE", or "READ ONLY"'
/
comment on column USER_TABLESPACES.CONTENTS is
'Tablespace contents: "PERMANENT", or "TEMPORARY"'
/
comment on column USER_TABLESPACES.LOGGING is
'Default logging attribute'
/
comment on column USER_TABLESPACES.EXTENT_MANAGEMENT is
'Extent management tracking: "DICTIONARY" or "LOCAL"'
/
comment on column USER_TABLESPACES.ALLOCATION_TYPE is
'Type of extent allocation in effect for this tablespace'
/
drop public synonym USER_TABLESPACES
/
create public synonym USER_TABLESPACES for USER_TABLESPACES
/
grant select on USER_TABLESPACES to PUBLIC with grant option
/
create or replace view DBA_TABLESPACES
    (TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
     MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN,
     STATUS, CONTENTS, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN)
as select ts.name, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL), 
                 ts.blocksize * ts.dflincr), 
          ts.dflminext,
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext), 
          decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
          ts.blocksize * ts.dflminlen,
          decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
                 4, 'READ ONLY', 'UNDEFINED'),
          decode(ts.contents$, 0, 'PERMANENT', 1, 'TEMPORARY'),
          decode(ts.dflogging, 0, 'NOLOGGING', 1, 'LOGGING'),
          decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
          decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
	         'UNDEFINED'),
          decode(ts.plugged, 0, 'NO', 'YES')
from sys.ts$ ts
where ts.online$ != 3
/
drop public synonym DBA_TABLESPACES
/
create public synonym DBA_TABLESPACES for DBA_TABLESPACES
/
grant select on DBA_TABLESPACES to select_catalog_role
/
comment on table DBA_TABLESPACES is
'Description of all tablespaces'
/
comment on column DBA_TABLESPACES.TABLESPACE_NAME is
'Tablespace name'
/
comment on column DBA_TABLESPACES.INITIAL_EXTENT is
'Default initial extent size'
/
comment on column DBA_TABLESPACES.NEXT_EXTENT is
'Default incremental extent size'
/
comment on column DBA_TABLESPACES.MIN_EXTENTS is
'Default minimum number of extents'
/
comment on column DBA_TABLESPACES.MAX_EXTENTS is
'Default maximum number of extents'
/
comment on column DBA_TABLESPACES.PCT_INCREASE is
'Default percent increase for extent size'
/
comment on column DBA_TABLESPACES.MIN_EXTLEN is
'Minimum extent size for the tablespace'
/
comment on column DBA_TABLESPACES.STATUS is
'Tablespace status: "ONLINE", "OFFLINE", or "READ ONLY"'
/
comment on column DBA_TABLESPACES.CONTENTS is
'Tablespace contents: "PERMANENT", or "TEMPORARY"'
/
comment on column DBA_TABLESPACES.LOGGING is
'Default logging attribute'
/
comment on column DBA_TABLESPACES.EXTENT_MANAGEMENT is
'Extent management tracking: "DICTIONARY" or "LOCAL"'
/
comment on column DBA_TABLESPACES.ALLOCATION_TYPE is
'Type of extent allocation in effect for this tablespace'
/
remark 
remark  Following views related to temporary tablespaces
remark  FAMILY "TEMP_FILES"
remark  Information about database temp files.
remark  This family has a DBA member only.
remark  ### we should probably not use kccfn here
remark
create or replace view DBA_TEMP_FILES
    (FILE_NAME, FILE_ID, TABLESPACE_NAME,
     BYTES, BLOCKS, STATUS, RELATIVE_FNO, AUTOEXTENSIBLE,
     MAXBYTES, MAXBLOCKS, INCREMENT_BY, USER_BYTES, USER_BLOCKS)
as
select /*+ ordered use_nl(hc) */
       v.fnnam, hc.ktfthctfno, ts.name, 
       decode(hc.ktfthccval, 0, ts.blocksize * hc.ktfthcsz, NULL), 
       decode(hc.ktfthccval, 0, hc.ktfthcsz, NULL), 'AVAILABLE',
       decode(hc.ktfthccval, 0, hc.ktfthcfno, NULL),
       decode(hc.ktfthccval, 0, decode(hc.ktfthcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfthccval, 0, ts.blocksize * hc.ktfthcmaxsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcmaxsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcinc, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcusz * ts.blocksize, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcusz, NULL)
from sys.x$kccfn v, sys.x$ktfthc hc, sys.ts$ ts
where v.fntyp = 7 and v.fnnam is not null
  and v.fnfno = hc.ktfthctfno
  and hc.ktfthctsn = ts.ts#
/
drop public synonym DBA_TEMP_FILES
/
create public synonym DBA_TEMP_FILES for DBA_TEMP_FILES
/
grant select on DBA_TEMP_FILES to select_catalog_role
/
comment on table DBA_TEMP_FILES is
'Information about database temp files'
/
comment on column DBA_TEMP_FILES.FILE_NAME is
'Name of the database temp file'
/
comment on column DBA_TEMP_FILES.FILE_ID is
'ID of the database temp file'
/
comment on column DBA_TEMP_FILES.TABLESPACE_NAME is
'Name of the tablespace to which the file belongs'
/
comment on column DBA_TEMP_FILES.BYTES is
'Size of the file in bytes'
/
comment on column DBA_TEMP_FILES.BLOCKS is
'Size of the file in ORACLE blocks'
/
comment on column DBA_TEMP_FILES.STATUS is
'File status: "AVAILABLE"'
/
comment on column DBA_TEMP_FILES.RELATIVE_FNO is
'Tablespace-relative file number'
/
comment on column DBA_TEMP_FILES.AUTOEXTENSIBLE is
'Autoextensible indicator:  "YES" or "NO"'
/
comment on column DBA_TEMP_FILES.MAXBYTES is
'Maximum size of the file in bytes'
/
comment on column DBA_TEMP_FILES.MAXBLOCKS is
'Maximum size of the file in ORACLE blocks'
/
comment on column DBA_TEMP_FILES.INCREMENT_BY is
'Default increment for autoextension'
/
comment on column DBA_TEMP_FILES.USER_BYTES is
'Size of the useful portion of file in bytes'
/
comment on column DBA_TEMP_FILES.USER_BLOCKS is
'Size of the useful portion of file in ORACLE blocks'
/

create or replace view v_$temp_extent_map as select * from v$temp_extent_map;
drop public synonym v$temp_extent_map;
create public synonym v$temp_extent_map for v_$temp_extent_map;
grant select on v_$temp_extent_map to SELECT_CATALOG_ROLE;

create or replace view gv_$temp_extent_map as select * from gv$temp_extent_map;
drop public synonym gv$temp_extent_map;
create public synonym gv$temp_extent_map for gv_$temp_extent_map;
grant select on gv_$temp_extent_map to SELECT_CATALOG_ROLE;

create or replace view v_$temp_extent_pool as select * from v$temp_extent_pool;
drop public synonym v$temp_extent_pool;
create public synonym v$temp_extent_pool for v_$temp_extent_pool;
grant select on v_$temp_extent_pool to SELECT_CATALOG_ROLE;

create or replace view gv_$temp_extent_pool as select * from gv$temp_extent_pool;
drop public synonym gv$temp_extent_pool;
create public synonym gv$temp_extent_pool for gv_$temp_extent_pool;
grant select on gv_$temp_extent_pool to SELECT_CATALOG_ROLE;

create or replace view v_$temp_space_header as select * from v$temp_space_header;
drop public synonym v$temp_space_header;
create public synonym v$temp_space_header for v_$temp_space_header;
grant select on v_$temp_space_header to SELECT_CATALOG_ROLE;

create or replace view gv_$temp_space_header as select * from gv$temp_space_header;
drop public synonym gv$temp_space_header;
create public synonym gv$temp_space_header for gv_$temp_space_header;
grant select on gv_$temp_space_header to SELECT_CATALOG_ROLE;

