Create list of oracle users/schema's via PLSQL by reading list from a file.

CREATE DIRECTORY DUMP_MDSP_DIR AS '/u01/oracle/work';
GRANT READ,WRITE ON DUMP_MDSP_DIR TO PUBLIC;

--Now create a file called users.lst and list all the username in it. Save it in /u01/oracle/work.

CREATE OR REPLACE PROCEDURE CREATEUSERS
IS
  fHandler UTL_FILE.FILE_TYPE;
  buf      varchar2(100);
BEGIN
  fHandler:= UTL_FILE.FOPEN('DUMP_MDSP_DIR', 'users.lst', 'r');
  loop
  BEGIN

UTL_FILE.GET_LINE(fHandler, buf);
EXECUTE IMMEDIATE 'create user '||buf||' '||' identified by admin default tablespace users001 temporary tablespace temp001';
EXECUTE IMMEDIATE 'grant connect,resource,create session to '||buf;

  EXCEPTION
     WHEN NO_DATA_FOUND THEN EXIT;
  END;
  end loop;
  UTL_FILE.FCLOSE(fHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/

Agent failed to start

Files to look into:

Make sure that the server name is correctly defined:

/oracle/product/10.2.0/agent10g/sysman/emd/targets.xml

/oracle/product/10.2.0/agent10g/sysman/config/emd.properties

Oracle Character set

Problem This technote should be used to identify what Oracle Character set is in use, which can be important to know when a text has been replaced with question marks.

Cause

Oracle allows you to specify which character set it will use, both on the Oracle client and on the Oracle server. When the Oracle client flags the Oracle server to store a character it does not understand the Oracle server stores the character as a question mark. This usually happens when the character set on the client is not a subset of the character set in the database.

Resolving the problem

To resolve this problem you must identify what the character set is on your client, on your server, and if these character sets are compatible: The character set on an Oracle client is stored in the following key: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG Note: There may be other HOME Registry branches as well. The Oracle client character set is the last section of the value. For example: If the registry value is: AMERICAN_AMERICA.WE8ISO8859P1 then the character set is WE8ISO8859P1. Other values might include WE8MSWIN1252, UTF8 orAL32UTF8. To determine what character set in on the server submit the following query: Select SYS.PROPS$.VALUE$ From SYS.PROPS$ Where ((SYS.PROPS$.NAME = 'NLS_CHARACTERSET')) ; Note: To perform this query you need to be logged in with SYSDBA privileges such as a system/manager The following chart is a brief list of Oracle character sets and their compatibility. Superset Subset WE8ISO8859P1 US7ASCII ALT24UTFFSS US7ASCII UTF8 US7ASCII The follow is an example of two code pages that are not completely compatible. If ISO-8859-1 was used on the database server and CP1252 was used on the client the top two rows of character in CP1252 could not be stored in the database server. The server would save these characters as question marks. CP1252 Encoding Table ISO-8859-1 Encoding Table

Remove Control characters from unix file

One way is to use the tr command to delete the control characters using octal values. Since the octal values of ^M, ^H, ^G are 015, 010, 007 respectively, this command deletes the 3 control characters:

tr -d '\015\010\007' <> new.data.file

If you need the value of other control characters, but do not have an ASCII chart, use this method to deterine the value: from within vi and in an edit mode, press control-v and then press the control character. Save the file and perform an od -b command on the file. This displays the octal value.

Oracle Agent Not Loading

This summary is not available. Please click here to view the post.

Find Number of CPU on Linux

cat /proc/cpuinfo | grep processor | awk '{a++} END {print a}'

Monitor Materialized Views

Working with Materialized Views Creation, Refresh and Monitoring

The purpose of this document is to provide the steps to create MV's, to diagnose the refresh and monitoring.

What are Materialized Views?

Materialized views are often used to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables or aggregations such as SUM, or both. These operations are very expensive in terms of time

and processing power.

Creating a Materialized View

There are different ways to create MV's see below how we are creating them and what clauses we can use, first step is to create the MV and later schedule the refresh.

CREATE MATERIALIZED VIEW MV_CRE_SC_LIST NOLOGGING USING INDEX REFRESH FORCE ON DEMAND AS (Select statement)

FORCE Clause

Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

ON DEMAND Clause

Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

QUERY REWRITE Clause

Actually we are not using query rewrite clause lets you specify whether the materialized view is eligible to be used for query rewrite.

DEFERRED

Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite.

USING INDEX Clause

The USING INDEX clause lets you establish the value of the INITRANS and STORAGE parameters for the default index Oracle Database uses to maintain the materialized view data.

If USING INDEX is not specified, then default values are used for the index. Oracle Database uses the default index to speed up incremental (fast) refresh of the materialized view.

Logging_clause

Specify LOGGING or NOLOGGING to establish the logging characteristics for the materialized view. The default is the logging characteristic of the tablespace in which the materialized view resides.

Specify NOLOGGING if you do not want these operations to be logged.

Automatic Refresh Times for Materialized Views: Example

The following statement creates the complex materialized view all customers, it includes storage clauses and refresh schedule.

CREATE MATERIALIZED VIEW all_customers

PCTFREE 5 PCTUSED 60

TABLESPACE example

STORAGE (INITIAL 50K NEXT 50K)

USING INDEX STORAGE (INITIAL 25K NEXT 25K)

REFRESH START WITH ROUND(SYSDATE + 1) + 11/24

NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24

AS SELECT

How to monitor the progress of a materialized view refresh

Steps to diagnose the refresh once Materialized View is created. It addresses the following questions:

Is a refresh currently running?

Is the refresh hanging or moving slowly?

When did the next and last refresh occur?

What is the ongoing phase of the refresh?

Overview of the Refresh Process

a refresh synchronizes the MVIEW with it's master table. In the case of updateable MVIEWs, changes from the MVIEW site are first propagated to master site if the refresh's push_deferred_rpc parameter is TRUE. Then, changes are pulled from the master site. Pulling rows from master can be either performed using the mview log on the master table (this type of refresh is a FAST refresh), or without using the mview log (this type of refresh is a COMPLETE refresh).

How to schedule the MV refresh

ALTER MATERIALIZED VIEW SMARTSTREAM.MV_HYPERION REFRESH FORCE START WITH to_date('05-23-2008 23:00:00','MM-dd-yyyy hh24:mi:ss') NEXT trunc(sysdate + 1) + 23/24;

Determine if a Specific MVIEW is Being Refreshed:

select o.owner, o.object_name mview, username, s.sid

from v$lock l, dba_objects o, v$session s

where o.object_id=l.id1 and

l.type='JI' and

l.lmode=6 and

s.sid=l.sid and

o.object_type='TABLE'

OWNER MVIEW USERNAME SID

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

SMARTSTREAM MV_HYPERION SMARTSTREAM 407

Identify the Last and Next Refresh Dates

select what, last_date, next_date

from dba_jobs

where upper(what) like '%DBMS_REFRESH.REFRESH(%SMARTSTREAM%'

WHAT LAST_DATE NEXT_DATE

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

dbms_refresh.refresh('"SMARTSTREAM"."MV_CMR_ENHANCED"'); 27-05-2008 21:00:01 01-01-4000 00:00:00

dbms_refresh.refresh('"SMARTSTREAM"."MV_CMR"'); 22-05-2008 15:38:17 22-05-2008 22:00:00

dbms_refresh.refresh('"SMARTSTREAM"."SCOPELISTENING_CS_SYSDATE"'); 28-05-2008 23:00:02 01-01-4000 00:00:00

dbms_refresh.refresh('"SMARTSTREAM"."MV_HYPERION_SYSDATE"'); 28-05-2008 20:00:01 01-01-4000 00:00:00

dbms_refresh.refresh('"SMARTSTREAM"."MV_CS_SC_LIST_NEW"'); 29-05-2008 10:30:13 01-06-2008 10:30:08

dbms_refresh.refresh('"SMARTSTREAM"."MV_RSR_SYSDATE"'); 27-05-2008 23:30:04 01-01-4000 00:00:00

dbms_refresh.refresh('"SMARTSTREAM"."MV_EFS_SC_LIST_NEW"'); 29-05-2008 11:00:30 01-06-2008 11:00:29

dbms_refresh.refresh('"SMARTSTREAM"."MV_CRE_SC_LIST_NEW"'); 29-05-2008 10:06:26 01-06-2008 10:02:55

dbms_refresh.refresh('"SMARTSTREAM"."MV_HFS_SC_LIST_NEW"'); 29-05-2008 11:30:28 01-06-2008 11:30:27

dbms_refresh.refresh('"SMARTSTREAM"."MV_REC_STAT_NEW"'); 29-05-2008 12:00:50 01-06-2008 12:00:49

dbms_refresh.refresh('"SMARTSTREAM"."MV_HYP_NEW"'); 29-05-2008 13:00:35 01-06-2008 13:00:30

dbms_refresh.refresh('"SMARTSTREAM"."MV_CRE_SC_LIST"'); 29-05-2008 11:45:27 01-06-2008 13:30:22

dbms_refresh.refresh('"SMARTSTREAM"."MV_EFS_SC_LIST"'); 29-05-2008 18:45:35

dbms_refresh.refresh('"SMARTSTREAM"."MV_HFS_SC_LIST"'); 29-05-2008 11:15:59 01-06-2008 11:15:57

dbms_refresh.refresh('"SMARTSTREAM"."MV_CS_SC_LIST"'); 29-05-2008 12:15:34 01-06-2008 11:45:32

dbms_refresh.refresh('"SMARTSTREAM"."MV_REC_STAT_EFS"'); 29-05-2008 12:45:01 01-06-2008 12:15:00

dbms_refresh.refresh('"SMARTSTREAM"."MV_HYPERION"'); 29-05-2008 13:15:00

Following query can be used to determine which MVIEW is currently being refreshed:

select sql_text

from v$session ses, v$sqlarea sql

where ses.sql_hash_value = sql.hash_value(+) and

ses.sql_address = sql.address(+) and

ses.sid=407;

SQL_TEXT

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

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN dbms_refresh.refresh('"SMARTSTREAM"."MV_HYPERION"'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

Check Refresh Subphase

Once refresh finishes the pushing and purging of defcalls it continues with the refresh. The refresh is done in three subphases, SETUP, INSTATNIATION and WRAPUP.

column "MVIEW BEING REFRESHED" format a30

column INSERTS format 9999999

column UPDATES format 9999999

column DELETES format 9999999

select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR

"MVIEW BEING REFRESHED",

decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,

decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE',

3, 'WRAPUP', 'UNKNOWN' ) STATE,

TOTAL_INSERTS_KNSTMVR INSERTS,

TOTAL_UPDATES_KNSTMVR UPDATES,

TOTAL_DELETES_KNSTMVR DELETES

from X$KNSTMVR X

WHERE type_knst=6 and

exists (select 1 from v$session s

where s.sid=x.sid_knst and

s.serial#=x.serial_knst);

MVIEW BEING REFRESHED REFTYPE STATE INSERTS UPDATES DELETES

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

SMARTSTREAM.MV_HYPERION COMPLETE INSTANTIATE 0 0 779535

Determine refresh time MVIEW

The DBA_JOBS VIEW, regarding this view we have important columns like BROKEN and TOTAL_TIME.

Name Null? Type

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

JOB NOT NULL NUMBER

LOG_USER NOT NULL VARCHAR2(30)

PRIV_USER NOT NULL VARCHAR2(30)

SCHEMA_USER NOT NULL VARCHAR2(30)

LAST_DATE DATE

LAST_SEC VARCHAR2(8)

THIS_DATE DATE

THIS_SEC VARCHAR2(8)

NEXT_DATE NOT NULL DATE

NEXT_SEC VARCHAR2(8)

TOTAL_TIME NUMBER

BROKEN VARCHAR2(1)

INTERVAL NOT NULL VARCHAR2(200)

FAILURES NUMBER

WHAT VARCHAR2(4000)

NLS_ENV VARCHAR2(4000)

MISC_ENV RAW(32)

INSTANCE NUMBER

Example below show TOTAL_TIME and BROKEN columns, when BROKEN is Y means that the MV is scheduled but broken so refresh is not happening for it.

JOB LAST_REFRESH NEXT_REFRESH TOTAL_TIME B FAILURES WHAT

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

61 270508 21:00 010100 00:00 1548897 Y 0 dbms_refresh.refresh('"SMARTSTREAM"."MV_CMR_ENHANCED"');

41 220508 15:38 220508 22:00 427498 Y 0 dbms_refresh.refresh('"SMARTSTREAM"."MV_CMR"');

141 280508 23:00 010100 00:00 1822 Y 0 dbms_refresh.refresh('"SMARTSTREAM"."SCOPELISTENING_CS_SYSDATE"');

142 280508 20:00 010100 00:00 57447 Y 0 dbms_refresh.refresh('"SMARTSTREAM"."MV_HYPERION_SYSDATE"');

182 290508 10:30 010608 10:30 96 N 0 dbms_refresh.refresh('"SMARTSTREAM"."MV_CS_SC_LIST_NEW"');

144 270508 23:30 010100 00:00 84433 Y 0 dbms_refresh.refresh('"SMARTSTREAM"."MV_RSR_SYSDATE"');

NOTE: TOTAL_TIME is cumulative time that was spent running jobs itself - in seconds

Most recently refresh and the average time to refresh using both full and incremental methods.

select MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,SUMMARY,FULLREFRESHTIM,INCREFRESHTIM

from ALL_MVIEW_ANALYSIS

MVIEW_NAME LAST_REFRESH_DATE REFRESH_ FULLREFRESHTIM INCREFRESHTIM

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

MV_CMR 22-05-2008 15:38:17 FORCE 1545 0

MV_RSR_SYSDATE 27-05-2008 23:31:13 FORCE 6215 0

MV_CS_SC_LIST_NEW 29-05-2008 10:30:13 FORCE 96 0

MV_CMR_ENHANCED 27-05-2008 21:00:41 FORCE 40950 0

MV_HYPERION_SYSDATE 28-05-2008 20:00:03 FORCE 6353 0

SCOPELISTENING_CS_SYSDATE 28-05-2008 23:00:02 FORCE 241 0

MV_CMR_NEW 10-05-2008 02:37:33 FORCE 0 0

MV_REC_STATUS_CRE 11-05-2008 08:03:00 FORCE 0 0

MV_EFS_SC_LIST 29-05-2008 02:35:27 FORCE 2504 0

MV_HFS_SC_LIST 29-05-2008 11:15:59 FORCE 2 0

MV_CRE_SC_LIST 29-05-2008 11:45:27 FORCE 237 0

MV_CS_SC_LIST 29-05-2008 12:15:34 FORCE 88 0

How to disable/enable the MV's schedule, it needs to be done under schema user.

exec dbms_job.broken(61,TRUE);

exec dbms_job.broken(61,FALSE);

Make sure that job_queue_processes is set up

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs.

Ie.

NAME TYPE VALUE

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

job_queue_processes integer 12

NOTE: if the job_queue_processes is set to 0, even though the MVÂ?s are already scheduled wont run because of the job_queue_processes value it MUST be > 0

Lessons Learned

1.- Make sure that master tables have the lastest statistics

2.- Make sure that master tables have proper indexes

3.- to create one by one

4.- to compare stats and tables structure from another database.

5.- to check space on the default tablespace

6.- to check space on temporary tablespace