Alter Materialized View ( for start time)

Note: Start Time was 9:20AM, but to 11AM.

declare
  no_primary_key exception;
  PRAGMA EXCEPTION_INIT (no_primary_key, -12014);
begin
  -- Oracle does not make it easy to reliably determine if a MV is
  -- created with the PRIMARY KEY clause or not.  So we're going to
  -- assume it does, but if that fails, we'll try again without it.
  execute immediate 'ALTER MATERIALIZED VIEW Schema_name.mv_name
 REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24
 WITH PRIMARY KEY';
exception
  when no_primary_key then
    execute immediate 'ALTER MATERIALIZED VIEW schema_name.mv_name
REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24';
  when others then
    raise;
end;

Query running more than 60 minutes

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;

QUERY for finding long operations (e.g. full table scans).

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/

QUERY will shows locks.

select
  object_name,
  object_type,
  session_id,
  type,   -- Type or system/user lock
  lmode,     -- lock mode in which session holds lock
  request,
  block,
  ctime   -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

This QUERY shows SQL that is currently "ACTIVE":-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

move table from one tablespace to another tablespace

alter table <table> move tablespace <Tablespace_name>;

Query - Find SQL being executed by a OS Process ID (PID)

set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;

Query for Unused Index

SELECT owner,index_name, status
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND status != 'VALID'
  AND
  (
   status != 'N/A'
   OR index_name IN
   (
    SELECT index_name
     FROM all_ind_partitions
     WHERE status != 'USABLE'
      AND
      (
       status != 'N/A'
       OR index_name IN
       (
        SELECT index_name
         FROM all_ind_subpartitions
         WHERE status != 'USABLE'
       )
      )
   )
  );

How to increase SESSION initialization parameter

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated 
by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba
 sqlplus / as sysdba
 
2. Check Current Setting of Parameters
 sql> show parameter sessions
 sql> show parameter processes
 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase 
"processes and "transactions" parameters.
A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1
  
4. These paramters can't be modified in memory. You have to modify the spfile only 
(scope=spfile) and bounce the instance.
 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup