This article brings you relevant knowledge about Oracle, which mainly organizes issues related to data dictionary, data dictionary view and dynamic performance view. The data dictionary records the most basic information of the database. Including data dictionary basic tables and data dictionary views, let’s take a look at them together. I hope it will be helpful to everyone.
Recommended tutorial: "Oracle Video Tutorial"
The data dictionary records the most basic information of the database, including basic data dictionary tables and data dictionary views; the basic data dictionary tables are created by $ORACLE_HOME\RDBMS\ADMIN\sql.bsq
The basic table of the data dictionary belongs to the sys user and is stored in the system table space. Users cannot manually modify the basic table of the data dictionary;
In order to simplify the basic table of the data dictionary For use, Oracle provides a data dictionary view and also creates public synonyms for the data dictionary view to facilitate user use. The data dictionary view and synonyms are created through catalog.sql
The USER_ type of view represents the information owned by the currently logged in user;
The ALL_ type of view represents the information that the currently logged in user has permission to see; The type view represents the information that the database administrator can see
select * from dba_objects d where d.OBJECT_NAME='USER_TABLES';
Query the view corresponding to USER_TABLES:
select * from dba_views d where d.view_name='USER_TABLES';
Query the creation statement of the view corresponding to USER_TABLES:
SQL> SET LONG 10000;SQL> select d.text from dba_views d where d.view_name='USER_TABLES';TEXT--------------------------------------------------------------------------------select o.name,
decode(bitand(t.property,2151678048), 0, ts.name,
decode(t.ts#, 0, null, ts.name)),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
decode(bitand(t.property, 17179869184), 17179869184,
ds.initial_stg * ts.blocksize,
s.iniexts * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.next_stg * ts.blocksize,
s.extsize * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.minext_stg, s.minexts),
decode(bitand(t.property, 17179869184), 17179869184,
ds.maxext_stg, s.maxexts),
decode(bitand(ts.flags, 3), 1, to_number(NULL),
decode(bitand(t.property, 17179869184), 17179869184,
ds.pctinc_stg, s.extpct)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
ds.frlins_stg, decode(s.lists, 0, 1, s.lists)))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
ds.maxins_stg, decode(s.groups, 0, 1, s.groups)))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 3),
1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 12)/4,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 48)/16,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
else
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
end,
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4,
case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP'
else decode(ds.cmplvl_stg, 1, 'QUERY LOW',
2, 'QUERY HIGH',
3, 'ARCHIVE LOW',
'ARCHIVE HIGH') end,
null)
else
decode(bitand(s.spare1, 2048), 0, null,
case when bitand(s.spare1, 16777216) = 16777216 -- 0x1000000
then 'OLTP'
when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000
then 'QUERY LOW'
when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000
then 'QUERY HIGH'
when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000
then 'ARCHIVE LOW'
when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
then 'ARCHIVE HIGH'
else 'BASIC' end)
end,
decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 17179869184), 17179869184, 'NO',
decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
decode(bitand(t.property,16492674416640),2199023255552,'FORCE',
4398046511104,'MANUAL','DEFAULT')from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.deferred_stg$ ds, sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv,
x$ksppi ksppiwhere o.owner# = userenv('SCHEMAID')
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.obj# = ds.obj# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx and ksppi.ksppinm = '_dml_monitoring_enabled'select * from dba_synonyms d where d.synonym_name='USER_TABLES'
Record the definition information of all dynamic performance views
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of oracle data dictionary, data dictionary view and dynamic performance view (summary sharing). For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
