How to fine User name of current session

User name of current session
===============================

SQL> SELECT USERNAME
from v$session
where audsid = sys_context('userenv','sessionid');

How to get Client ip address in oracle

How to get client ip address in oracle
=====================================

1. For Client IP Address

SELECT SYS_CONTEXT ('userenv', 'ip_address')
FROM DUAL

How to get server ip address in oracle

how to get server ip address in oracle
================================
1. For IP Address

SELECT UTL_INADDR.get_host_address
FROM DUAL

2. For Host Name

SELECT UTL_INADDR.get_host_name
FROM DUAL

3. For Domain and host name

SELECT sys_context('USERENV', 'HOST')
FROM dual;

4. For host name

SELECT sys_context('USERENV', 'SERVER_HOST')
FROM dual;

materialized view Refresh

About materialized view Refresh
=============================

As I find, Materialized View create and ("atomic_refresh => FALSE") refresh is faster and more logical Then create table ,truncate and direct insert.

about materialized view works
=================
1. create a MV with "complete refresh on demand" (means it refresh when we want, not automatic)
2. when we refresh that MV (ON DEMAND REFRESH MODE) then
• ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
• Can be used with all types of materialized views
• Manual Refresh Procedures
• DBMS_MVIEW.REFRESH(, )

• When refresh_option use ("atomic_refresh => TRUE") its means DELETE operation is used to empty the table.
( in this time if job is broken then rollback the transaction and MV remain useable)
• When refresh_option use ("atomic_refresh => FALSE") its means TRUNCATE operation is used to empty the table.
( in this time if job is broken then MV don't remain useable state before another refresh)

• AND we can reduce the LOG generation by creating MV with nologging mode.

Ultimately
================
Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)
1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
2. The materialized base view is truncated/delete.
3. All rows selected from the master table are inserted into the snapshot base table.
4. sys.slog$ is updated to reflect the time of the refresh.


• N.B :- I have attached a “trace file .txt” file. Just search in this file with MV_STTRAILB_038 and find the prob.

ORA-08004: sequence exceeds MAXVALUE and cannot be instantiated

ORA-08004: sequence exceeds MAXVALUE and cannot be instantiated

Soution:=
==============

alter sequence maxvalue ;

Sequence syntax:
---------------
CREATE SEQUENCE
INCREMENT BY
START WITH
MAXVALUE / NOMAXVALUE
MINVALUE / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;


CREATE TABLE TEST (ID NUMBER, data VARCHAR2(255));


CREATE SEQUENCE seq_for_test
START WITH 1
INCREMENT BY 1
MAXVALUE 10;


CREATE OR REPLACE TRIGGER trigger_for_seq_test
BEFORE INSERT
ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT seq_for_test.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;


CREATE OR REPLACE TRIGGER trigger_for_seq_test
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
SELECT nvl(:new.id,seq_for_test.NEXTVAL)
INTO :NEW.ID
FROM DUAL;
END;



ALTER SEQUENCE seq_for_test MAXVALUE 15



Sequence in PL/sql
==================


INSERT INTO TEST (id, data)
VALUES (seq_for_test.NEXTVAL, 'halim');

INSERT INTO TEST (id, data)
VALUES (seq_for_test.CurrVAL, 'halim'); --(what happen)


SELECT 'CREATE SEQUENCE test_sequence MINVALUE 0 START WITH '||MAX(id_no)+1||' INCREMENT BY 1 CACHE 20'
INTO v_test
FROM table_name:

EXECUTE IMMEDIATE v_sql;


Sequence Related Queries
==========================
SELECT sequence_name, last_number
FROM user_sequences;

Next Number From Sequence
--------------------------

SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;