V$SYSMETRIC and V$SYSMETRIC_HISTORY

In an earlier example, I looked at buffer cache hit ratio. The result was an average value since instance startup.

 

The example here was tested on Oracle 11.2. You can now see the current value of buffer cache hit ratio in V$SYSMETRIC. The first value is calculated over 1 minute and the second is calculated over 15 seconds:

SQL> l

  1  select

  2  to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') begin_time,

  3  to_char(end_time,'DD-MON-YYYY HH24:MI:SS') end_time,

  4  value

  5  from v$sysmetric

  6  where metric_name = 'Buffer Cache Hit Ratio'

  7* order by begin_time

SQL> /

 

BEGIN_TIME           END_TIME                  VALUE

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

25-SEP-2012 16:28:57 25-SEP-2012 16:29:57  97.194687

25-SEP-2012 16:30:27 25-SEP-2012 16:30:42  91.864139

 

SQL>

 

You can get the values for the last hour from V$SYSMETRIC_HISTORY:

 

SQL> l

  1  select

  2  to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') begin_time,

  3  to_char(end_time,'DD-MON-YYYY HH24:MI:SS') end_time,

  4  value

  5  from v$sysmetric_history

  6  where metric_name = 'Buffer Cache Hit Ratio'

  7* order by begin_time, end_time

SQL> /

 

BEGIN_TIME           END_TIME                  VALUE

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

25-SEP-2012 15:49:57 25-SEP-2012 15:50:57 97.5303316

25-SEP-2012 15:50:57 25-SEP-2012 15:51:57 96.2754449

25-SEP-2012 15:51:57 25-SEP-2012 15:52:57 96.6650953

25-SEP-2012 15:52:57 25-SEP-2012 15:53:57 96.7953002

25-SEP-2012 15:53:57 25-SEP-2012 15:54:57 98.1617859

25-SEP-2012 15:54:57 25-SEP-2012 15:55:58 98.1856913

25-SEP-2012 15:55:58 25-SEP-2012 15:56:58 99.1994269

25-SEP-2012 15:56:58 25-SEP-2012 15:57:58 99.8990431

25-SEP-2012 15:57:58 25-SEP-2012 15:58:57 99.8517867

25-SEP-2012 15:58:57 25-SEP-2012 15:59:57 99.6710275

25-SEP-2012 15:59:57 25-SEP-2012 16:00:57 90.5124721

25-SEP-2012 16:00:57 25-SEP-2012 16:01:57 96.7805768

25-SEP-2012 16:01:57 25-SEP-2012 16:02:57 95.9747129

25-SEP-2012 16:02:57 25-SEP-2012 16:03:57 95.3531629

25-SEP-2012 16:03:57 25-SEP-2012 16:04:57 94.5228342

25-SEP-2012 16:04:57 25-SEP-2012 16:05:58 95.6838002

25-SEP-2012 16:05:58 25-SEP-2012 16:06:58 95.4702619

25-SEP-2012 16:06:58 25-SEP-2012 16:07:58 99.7447147

25-SEP-2012 16:07:58 25-SEP-2012 16:08:57 97.2875844

25-SEP-2012 16:08:57 25-SEP-2012 16:09:57 96.3138593

25-SEP-2012 16:09:57 25-SEP-2012 16:10:57 97.4352075

25-SEP-2012 16:10:57 25-SEP-2012 16:11:57 97.8508368

25-SEP-2012 16:11:57 25-SEP-2012 16:12:57 97.2685509

25-SEP-2012 16:12:57 25-SEP-2012 16:13:57 95.4675739

25-SEP-2012 16:13:57 25-SEP-2012 16:14:57 97.9981712

25-SEP-2012 16:14:57 25-SEP-2012 16:15:58 96.6777483

25-SEP-2012 16:15:58 25-SEP-2012 16:16:58 97.6958152

25-SEP-2012 16:16:58 25-SEP-2012 16:17:58 98.3318701

25-SEP-2012 16:17:58 25-SEP-2012 16:18:57 97.3715671

25-SEP-2012 16:18:57 25-SEP-2012 16:19:57 98.7109485

25-SEP-2012 16:19:57 25-SEP-2012 16:20:57 96.2517824

25-SEP-2012 16:20:57 25-SEP-2012 16:21:57 96.4621925

25-SEP-2012 16:21:57 25-SEP-2012 16:22:57 98.2080659

25-SEP-2012 16:22:57 25-SEP-2012 16:23:57  97.953139

25-SEP-2012 16:23:57 25-SEP-2012 16:24:57 89.2580395

25-SEP-2012 16:24:57 25-SEP-2012 16:25:58 97.5403648

25-SEP-2012 16:25:58 25-SEP-2012 16:26:58  97.578567

25-SEP-2012 16:26:58 25-SEP-2012 16:27:58 93.7777583

25-SEP-2012 16:27:58 25-SEP-2012 16:28:57 85.2168157

25-SEP-2012 16:28:57 25-SEP-2012 16:29:57  97.194687

25-SEP-2012 16:29:57 25-SEP-2012 16:30:57  98.117032

25-SEP-2012 16:30:57 25-SEP-2012 16:31:57 98.2240788

25-SEP-2012 16:31:57 25-SEP-2012 16:32:57  97.110952

25-SEP-2012 16:32:57 25-SEP-2012 16:33:57 94.5899407

25-SEP-2012 16:33:57 25-SEP-2012 16:34:57   96.80441

25-SEP-2012 16:34:57 25-SEP-2012 16:35:57  98.133023

25-SEP-2012 16:35:57 25-SEP-2012 16:36:58 97.5633308

25-SEP-2012 16:36:58 25-SEP-2012 16:37:58 98.9988528

25-SEP-2012 16:37:58 25-SEP-2012 16:38:58 93.9215307

25-SEP-2012 16:38:58 25-SEP-2012 16:39:57   98.35585

25-SEP-2012 16:39:57 25-SEP-2012 16:40:57 96.7044666

25-SEP-2012 16:40:57 25-SEP-2012 16:41:57 98.1190344

25-SEP-2012 16:41:57 25-SEP-2012 16:42:57 97.3454653

25-SEP-2012 16:42:57 25-SEP-2012 16:43:57 98.8355824

25-SEP-2012 16:43:57 25-SEP-2012 16:44:57 97.4232899

25-SEP-2012 16:44:57 25-SEP-2012 16:45:57 94.0274377

25-SEP-2012 16:45:57 25-SEP-2012 16:46:57 95.7594464

25-SEP-2012 16:46:57 25-SEP-2012 16:47:58 96.1082737

25-SEP-2012 16:47:58 25-SEP-2012 16:48:58 96.9696389

25-SEP-2012 16:48:28 25-SEP-2012 16:48:43 98.4661672

25-SEP-2012 16:48:43 25-SEP-2012 16:48:58  95.309712

25-SEP-2012 16:48:58 25-SEP-2012 16:49:13 89.0358259

25-SEP-2012 16:48:58 25-SEP-2012 16:49:58 98.8512452

25-SEP-2012 16:49:13 25-SEP-2012 16:49:28 96.9536584

25-SEP-2012 16:49:28 25-SEP-2012 16:49:43 99.4072881

25-SEP-2012 16:49:43 25-SEP-2012 16:49:58 99.5088527

25-SEP-2012 16:49:58 25-SEP-2012 16:50:13 97.4177137

25-SEP-2012 16:49:58 25-SEP-2012 16:50:57 97.3807871

25-SEP-2012 16:50:13 25-SEP-2012 16:50:28 88.0492281

25-SEP-2012 16:50:28 25-SEP-2012 16:50:42 98.4126273

25-SEP-2012 16:50:42 25-SEP-2012 16:50:57 98.6083744

25-SEP-2012 16:50:57 25-SEP-2012 16:51:12 99.7137951

25-SEP-2012 16:51:12 25-SEP-2012 16:51:27 96.7031208

25-SEP-2012 16:51:27 25-SEP-2012 16:51:42 97.4735416

 

74 rows selected.

 

SQL>

Drop Database

In version 10g, Oracle introduced the drop database SQL statement. This removes all datafiles, online redo log files, control files and server parameter files. I created a database with the Database Configuration Assistant so that I could try out this new command. To make it easier to demonstrate, I put the datafiles, online redo log files and control files all in the same directory:

SQL> col file_name format a55
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\USERS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSAUX01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\UNDOTBS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\TEMP01.DBF
  
SQL> col member format a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO03.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO02.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO01.LOG

SQL> col name format a55
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL01.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL02.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL03.CTL

SQL>

It’s easier to see in the screen print below (click to enlarge it):
   

The server parameter file was in a different directory:

SQL> col value format a50
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILETEST10.ORA

SQL>

Then I tried to drop the database:

SQL> set lines 60
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL>

I followed the instructions and tried again: 

C:\Documents and Settings\Andrew>set ORACLE_SID=TEST10

C:\Documents and Settings\Andrew>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 09:35:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 176163692 bytes
Database Buffers 432013312 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL>

Then putting the database into restricted session mode appeared to have the desired effect:

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The datafiles were removed:

C:\Documents and Settings\Andrew\test10>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\Documents and Settings\Andrew\test10

24/12/2010 10:01 <DIR> .
24/12/2010 10:01 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,795,968 bytes free

C:\Documents and Settings\Andrew\test10>

And so was the spfile:

C:\oracle\product\10.2.0\db_1\dbs>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\oracle\product\10.2.0\db_1\dbs

24/12/2010 10:00 <DIR> .
24/12/2010 10:00 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,415,040 bytes free

C:\oracle\product\10.2.0\db_1\dbs>