Category: Uncategorized

Active Connections to Oracle Database

V$SESSION displays session information for each current session.

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
order by spid;

Source

Materialized View Refresh

Check refresh mode, refresh method and last refresh time from USER_MVIEWS:

SELECT mview_name, refresh_mode, refresh_method,
 last_refresh_type, last_refresh_date
 FROM user_mviews;

Change of refresh behaviour in 10g

Upgrading from Oracle 9i to Oracle 10g will change the MV refresh behaviour. Oracle 10g will use the DELETE command to remove rows and a normal INSERT to repopulate it. In Oracle 9i and earlier releases, Oracle did a TRUNCATE and INSERT /*+APPEND*/, which is more efficient, but had the side effect that users will see no rows while the refresh is taking place.

If you prefer the older truncate/append behaviour, change the refresh method to set atomic_refresh = false. Here is an example:

BEGIN
 -- use this with 10g/11g to return to truncate/append behavior
 dbms_mview.refresh('MY_TEST_MV', method=>'C', atomic_refresh=>false);
END;
/

ATOMIC_REFRESH

Atomic_refresh parameter is to allow either all of the tasks of a transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction.If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.

If you set that to FALSE, it’ll do a truncate + insert /*+ append */ on a FULL refresh.

When atomic_refresh=>true, Oracle performs deleting from MView table.
When atomic_refresh=>false, Oracle truncates the MView table.

Atomic refresh does a

a) truncate (data disappears right away, poof)
b) insert /*+ APPEND */ – direct path load, which maintains indexes
c) commits (data reappears)

The indexes won’t/don’t go unusable, but the materialized view “disappears” for the duration of the refresh.

Source / Reference Links:

Automatic Undo Management

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. With automatic undo management, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database. An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA).

There are three parameters associated with automatic undo management

  • UNDO_MANAGEMENT (default manual) – This is the only mandatory parameter and can be set to either auto or manual.
  • UNDO_TABLESPACE (default undo tablespace) – This specifies the tablespace to be used, of course the tablespace needs to be a undo tablespace. If you do not set this value oracle will automatically pick the one available. If no undo tablespace exists then oracle will use the system tablespace which is not a good idea (always create one).
  • UNDO_RETENTION (seconds) – Once a transaction commits the undo data for that transaction stays in the undo tablespace until space is required in which case it will be over written.

References / Source: