EQ = Equals
LT = Less than
LTE = Less then or equal to
GT = Greater Than
GTE = Greater than or equal to
LIKE = SQL Like operator
N = Null
NN = Not Null
V$SESSION displays session information for each current session.
from v$session b, v$process a
b.paddr = a.addr
order by spid;
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 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:
So far CodeIgniter was the only PHP development frame work I had tasted. Now I’m moving onto CakePHP!
Display all queues and queue tables
select owner, name, queue_table
Check various stats for all queues
set lines 100 pages 999
col queue format a50
select owner || ‘.’ || name queue, waiting, ready, expired
from dba_queues dq, v$aq aq
where dq.qid = aq.qid
order by 2, 3, 4, 1
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:
Any Oracle database that contains important data should be running in ARCHIVELOG mode. Running in Archive log mode enables you to take hot backups and perform point-in-time recovery.