Oracle's Common v$ view scripts

1. Basic Database Information

Version information:

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod

Pl/sql Release 10.2.0.1.0-production

CORE 10.2.0.1.0 Production

TNS for 32-bit windows:version 10.2.0.1.0-production

Nlsrtl Version 10.2.0.1.0-production

Database information:

Sql> select name, created, Log_mode from V$database;

NAME CREATED Log_mode

--------- -------------- ------------

TEST 1 March-September -09 Archivelog

2. Basic information of automated workload warehouse (AWR)

Automatic work warehouse (AWR) By default, the warehouse is populated with hours, with a retention period of 7 days.

How much space awr use

Sql>select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR% ' ;

Occupant_n Occupant_desc Space_usage_kbytes

---------- --------------------------------------------------   ------------------

Sm/awr Server manageability-automatic Workload Repository 51200

What is the most original AWR information on the system?

Sql> select dbms_stats.get_stats_history_availability from dual;

Get_stats_history_availability

---------------------------------------------------------------------------

2 January-August-09 09.18.15.359000000 a.m. +08:00

What is the retention period for awr information?

Sql> select dbms_stats.get_stats_history_retention from dual;

Get_stats_history_retention

---------------------------

31

Change the retention period for awr information to 15 days?

Sql> EXEC dbms_stats.alter_stats_history_retention (15);

The PL/SQL process has completed successfully.

3. Basic license Information

The V$license view allows DBAs to monitor the number of all system activities related to the number of databases at any time within the system. A session warning level of 0 indicates that the Init.ora session warning parameter is not set, so the system does not display a warning message. The maximum session level of 0 means that no Init.ora session maximum parameter is set, so the system does not limit the number of sessions. Query the V$license view to see the maximum number of sessions allowed. You can also set a warning when approaching the maximum number.

Scripts should be executed on a regular basis to provide the DBA with the actual number of sessions in a day to ensure proper license authorization. Set the Init.ora parameter license_max_sessions = 110 to limit the number of sessions to 110. Set the Init.ora parameter license_sessions_warning = 100, the system will display a warning message to each user after the 100th session so that they will notify the DBA that the system is shutting down because of a problem (hopefully). The Init.ora parameter license_max_users is used to set the number of named users that can be created in the database. In the following list of programs, the value is 0, so there is no limit.

Sql> select * from V$license;

Sessions_max sessions_warning sessions_current Sessions_highwater Users_max

------------ ---------------- ---------------- ------------------ ----------

0 0 9 18 0

4. Installed product items in the database

Query V$option view to get the Oracle product items that you have installed. The V$version view gives the version of the base product item that is installed.

Sql> select * from V$option;

PARAMETER VALUE

---------------------------------------------------------------- -----

Partitioning TRUE

Objects TRUE

Real application Clusters FALSE

Advanced Replication TRUE

bit-mapped Indexes TRUE

Oracle Data Guard TRUE

Oracle Label Security FALSE

Flashback Database TRUE

Data Mining scoring Engine FALSE