Database Reference
In-Depth Information
almighty DBA could do the same with index usage information and retrieve information for
other schemas than your own DBA schema—think again. Seriously, the only way to get index
usage information for a foreign schema is to connect to that schema, which requires knowledge
of the password or a temporary change of the password as discussed in Chapter 15. The tempo-
rary password change is risky, since any connect attempt by an application will fail while the
changed password is in effect. When done properly, the window where this can happen is small,
but nonetheless it may cause problems.
ALTER
SESSION
SET
CURRENT_SCHEMA
(see Chapter 5) won't
help, since it affects only the current schema name but not the logon user name.
Following are the column definitions of
V$OBJECT_USAGE
:
SQL> DESCRIBE v$object_usage
Name Null? Type
----------------------------------------- -------- ------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
Take a closer look at the columns
START_MONITORING
and
END_MONITORING
. Their data type is
VARCHAR2
. I think I vaguely remember that Oracle Corporation recommends using
DATE
columns
and not
VARCHAR2
to store date and time information. Well, maybe the design concept for this
view was approved on April Fools' Day. Let's have a look at the view's definition.
SQL> SET LONG 0815
SQL> SELECT text FROM dba_views WHERE owner='SYS' and
view_name='V$OBJECT_USAGE';
TEXT
--------------------------------------------------------------
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
There's the culprit. The view uses the undocumented parameter
SCHEMAID
in a call of the
function
USERENV
. Called in this way, it returns the numeric user identification in the same way
as the query
SELECT
user_id
FROM
all_users
WHERE
username=user
would. The numeric identifier
is used to filter
SYS.OBJ$
, the data dictionary base table underlying views such as
DBA_OBJECTS
.
As a consequence, a DBA cannot retrieve information on indexes in foreign schemas.
