Author: Admin

get_vmstat.ksh

23-Nov-2005 Admin No Comments

select a.measured_date, a.total_users, a.active_users, b.user_cpu, b.system_cpu, b.idle_cpu, b.wait_cpu from stats$totalusers a, stats$vmstat2 b where a.measured_date = b.start_date and to_char(measured_date,’DD-MON-YY’)=’22-NOV-05′ and to_char(measured_date,’HH24′) > ’14’ and to_char(measured_date,’HH24′) < '19' order by measured_date desc Script that populates stats$vmstat2 table is dbblkabva01:/export/home/oracle/oraprocs/get_vmstat2.ksh ——– vmstats.ksh ————- #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=BLINKT export...

DBMS_UTILITY PACKAGE

18-Nov-2005 Admin No Comments

DBMS_UTILITY Oracle provides two procedures under the DBMS_UTILITY package related to statistics generation. (Oracle recommends use of DBMS_STATS package for generating statistics). DBMS_UTILITY.ANALYZE_SCHEMA This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes. It takes the following parameters: schema – Name of the schema method…

DBMS_UTILITY.ANALYZE_SCHEMA

18-Nov-2005 Admin No Comments

10:17:18 SYS@WEBP:-> execute dbms_utility.analyze_schema(‘CS’, ‘COMPUTE’, NULL, NULL, NULL);

Oracle, SQL scripts

Gathering statistics with DBMS_STATS

18-Nov-2005 Admin No Comments

———————————————- Gathering statistics with DBMS_STATS ———————————————- DBMS_STATS.GATHER_TABLE_STATS DBMS_STATS.GATHER_TABLE_STATS gathers statistics for a table and its columns, and optionally the associated indexes. Call syntax dbms_stats.gather_table_stats(ownname, tabname, partname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, statown); The first two parameters are mandatory, the rest are defaulted to a value. ownname – owner tabname – table name…

Oracle, SQL scripts

VIvek Encryption Package and Its Usage

18-Nov-2005 Admin No Comments

create or replace package subs_cipher is function scramble ( obfs_in varchar2 ) return varchar2; — pragma not required as of 8.1.5 pragma restrict_references( scramble, wnds, wnps, wnps); function unscramble ( obfs_in varchar2 ) return varchar2; — pragma not required as of 8.1.5 pragma restrict_references( unscramble, wnds, rnds, wnps ); end; create or replace package body…

Oracle, SQL scripts

create database link syntax

14-Nov-2005 Admin No Comments

In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING ‘local’;

Oracle, SQL scripts

Adding or Dropping Online Redo Log Files When Physical Standby in place

09-Nov-2005 Admin No Comments

Changing the size and number of the online redo log files is sometimes done to tune the database. You can add online redo log file groups or members to the primary database without affecting the standby database. Similarly, you can drop log file groups or members from the primary database without affecting your standby database….

Oracle, rman-dataguard

Example of How To Resize the Online Redo Logfiles Note:1035935.6

09-Nov-2005 Admin No Comments

EXAMPLE OF HOW TO RESIZE THE ONLINE REDO LOGS: ============================================== Often times the online redo logs are sized too small causing database performance problems. The following is an example of how to resize the online log groups: 1. First see the size of the current logs: SVRMGR> connect internal (with 9i/10g use SQL> connect /…

Oracle, rman-dataguard

Standby Database Behavior when a Datafile is Resized on the Primary Database Note:123883.1

09-Nov-2005 Admin No Comments

Purpose: ======== The purpose of this article is to describe the behavior of the standby database when one or more datafiles on the primary database are resized. Standby Database Behavior When a Datafile is Resized on the Primary Database: ============================================================================= On the primary database the initial size of the datafile is 113246208. You decide to…

Oracle, rman-dataguard

AWR settings- MMON is not taking snapshot.

07-Nov-2005 Admin No Comments

POTENTIAL SOLUTION(S) for AWR Snapshot Error ============================================ (*) To take the snapshots we need to populate WRM$_SNAPSHOT table successfully and for that instance metadata should be present in WRM$_DATABASE_INSTANCE. (*) To regenerate the AWR objects run catnoawr.sql and catawr.sql scripts from $ORACLE_HOME/rdbms/admin (*) Then RESTART the database So, if we have regenerated the AWR objects…

Oracle, SQL scripts