|
-
To alapps:
This is some information about size of dictionary:
This is medium db, but it keep designer repositary tables:
SQL> select count(*) from sys.source$;
COUNT(*)
----------
818623
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
10142
SQL> select count(*) from sys.col$;
COUNT(*)
----------
45288
Some or all table are members of reingeneering process.
Then this is example only 1 view, that may use in reingeneering process.
and in the view some tables are X$ tables (x$kzsro) from shared memory.
SQL> select view_name,text from dba_views where view_name like 'ALL_TAB_COLUMNS';
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
ALL_TAB_COLUMNS select u.name, o.name,
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name,
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZO
NE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'), ut.name,
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'), c.col#, c.deflength,
c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), h.timestamp#, h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln
from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u,
sys.coltype$ ac, sys.obj$ ot, sys.user$ ut
where o.obj# = c.obj#
and o.owner# = u.user#
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and (o.type# in (3, 4) /* cluster, view */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
and (o.owner# = userenv('SCHEMAID')
or
o.obj# in ( select obj#
from sys.objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
What do u thing it may have effect to perfomance or not.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|