How to find definitions of V$, GV$ views?

From v$fixed_view_definition one can find the definitions of V$views which may one in understand them.

Example:
SQL> set pages 0
SQL> SELECT * FROM v$fixed_view_definition where view_name = 'V$SESSION';

V$SESSION
select SADDR , SID , SERIAL# , …. SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV('Instance')

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Immediately after a successful RDBMS installation (perhaps even including a sample DB instance creation), sqlplus will not start:

[oracle@test ~]$ sqlplus ‘/as sysdba’
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running in “enforcing” mode.

You can check it on file /etc/pam.d/login

Oracle development has recommended the following workaround while they correct the problem:

Switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode.

Commands, as root:
======================
getenforce       (returns “Enforcing”)
setenforce 0
getenforce       (returns “Permissive”)

This allows SELinux to continue running, and logging denial messages, but SELinux will not actually deny any operations. Once Development has resolved this issue, you can (and should) return SELinux to the default “Enforcing” mode as follows:

Commands, as root:
======================
setenforce 1
getenforce       (returns “Enforcing”)

Internal view for Oracle to track features used

In 10g there is a new view called DBA_FEATURE_USAGE_STATISTICS which keeps features have been used.

Here is description of some of the columns:
name – feature name
first_usage_date – first date when the feature was used
last_usage_date – latest date when it was detected the feature was used
last_sample_date – latest date when the sample was taken
last_sample_period – time in seconds between sample taken at last_sample_date and sample before last_sample_date
currently_used – value is TRUE or FALSE, TRUE if the feature was used since it was last checked

SQL> SELECT name, first_usage_date, last_usage_date, last_sample_date, last_sample_period FROM dba_feature_usage_statistics WHERE currently_used = ‘TRUE’;

NAME FIRST_USA LAST_USAG LAST_SAMP
—————————————- ——— ——— ———
Locally Managed Tablespaces (user) 30-SEP-08 08-FEB-09 08-FEB-09
Multiple Block Sizes 25-NOV-08 08-FEB-09 08-FEB-09
Partitioning (system) 30-SEP-08 08-FEB-09 08-FEB-09
Protection Mode – Maximum Performance 30-SEP-08 08-FEB-09 08-FEB-09
Segment Advisor 07-OCT-08 08-FEB-09 08-FEB-09
Streams (system) 30-SEP-08 08-FEB-09 08-FEB-09
Virtual Private Database (VPD) 30-SEP-08 08-FEB-09 08-FEB-09
Change-Aware Incremental Backup 02-DEC-08 08-FEB-09 08-FEB-09
Recovery Area 30-SEP-08 08-FEB-09 08-FEB-09
Streams (user) 30-SEP-08 08-FEB-09 08-FEB-09

View password history

One can view the password change history by querying oracle’s internal table SYS.USER_HISTORY$, note this table gets updated only if the user is assigned a profile that has password reuse limit (i.e. PASSWORD_REUSE_TIME set not to UNLIMITED)

SYS@TEST> SELECT name, password_date
FROM sys.user$, sys.user_history$
WHERE user$.user# = user_history$.user#;

NAME PASSWORD_
—————————— ———–
SCOTT 31-JAN-08
SCOTT 11-JUN-08