martes, 27 de septiembre de 2011

SYS_CONTEXT FROM DUAL


http://www.techonthenet.com/oracle/functions/sys_context.php


  1* select sys_context( 'userenv', 'CURRENT_SCHEMA' ) from dual
SQL> /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SQL> ed
Wrote file afiedt.buf

  1* select sys_context( 'userenv', 'HOST' ) from dual
SQL> /

SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
localhost.localdomain

SQL> ed
Wrote file afiedt.buf

  1* select sys_context( 'userenv', 'INSTANCE_NAME' ) from dual
SQL> /

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
acme

select SYS_CONTEXT('USERENV','SESSION_USER'),
    SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','OS_USER')
    from dual;


OPCIONES

Parameter
ACTION
AUDITED_CURSORID
AUTHENTICATED_IDENTITY
AUTHENTICATION_DATA
AUTHENTICATION_METHOD
AUTHENTICATION_TYPE
BG_JOB_ID
CLIENT_IDENTIFIER
CLIENT_INFO
CURRENT_BIND
CURRENT_SCHEMA
CURRENT_SCHEMAID
CURRENT_SQL
CURRENT_SQL_LENGTH
CURRENT_USER
CURRENT_USERID
DB_DOMAIN
DB_NAME
DB_UNIQUE_NAME
ENTRYID
ENTERPRISE_IDENTITY
EXTERNAL_NAME
FG_JOB_ID
GLOBAL_CONTEXT_MEMORY
GLOBAL_UID
HOST
IDENTIFICATION_TYPE
INSTANCE
INSTANCE_NAME
IP_ADDRESS
ISDBA
LANG
LANGUAGE
MODULE
NETWORK_PROTOCOL
NLS_CALENDAR
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT
NLS_TERRITORY
OS_USER
POLICY_INVOKER
PROXY_ENTERPRISE_IDENTITY
PROXY_GLOBAL_UID
PROXY_USER
PROXY_USERID
SERVER_HOST
SERVICE_NAME
SESSION_USER
SESSION_USERID
SESSIONID
SID
STATEMENTID
TERMINAL


SYS_CONTEXT

http://www.techonthenet.com/oracle/functions/sys_context.php


Parameter
ACTION
AUDITED_CURSORID
AUTHENTICATED_IDENTITY
AUTHENTICATION_DATA
AUTHENTICATION_METHOD
AUTHENTICATION_TYPE
BG_JOB_ID
CLIENT_IDENTIFIER
CLIENT_INFO
CURRENT_BIND
CURRENT_SCHEMA
CURRENT_SCHEMAID
CURRENT_SQL
CURRENT_SQL_LENGTH
CURRENT_USER
CURRENT_USERID
DB_DOMAIN
DB_NAME
DB_UNIQUE_NAME
ENTRYID
ENTERPRISE_IDENTITY
EXTERNAL_NAME
FG_JOB_ID
GLOBAL_CONTEXT_MEMORY
GLOBAL_UID
HOST
IDENTIFICATION_TYPE
INSTANCE
INSTANCE_NAME
IP_ADDRESS
ISDBA
LANG
LANGUAGE
MODULE
NETWORK_PROTOCOL
NLS_CALENDAR
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT
NLS_TERRITORY
OS_USER
POLICY_INVOKER
PROXY_ENTERPRISE_IDENTITY
PROXY_GLOBAL_UID
PROXY_USER
PROXY_USERID
SERVER_HOST
SERVICE_NAME
SESSION_USER
SESSION_USERID
SESSIONID
SID
STATEMENTID
TERMINAL

miércoles, 14 de septiembre de 2011

REFRESH VISTAS MATERIALIZADAS

create or replace
procedure dbo.ACT_VIEW_MATERIA
is
begin
FOR REG IN (SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE LIKE'%MATERIA%' AND STATUS='VALID')LOOP
DBMS_MVIEW.REFRESH(REG.OWNER||'.'||REG.OBJECT_NAME,'C');
END LOOP;
end;


job

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"DBO"."ACT_VIEW_MATE"',
job_type => 'STORED_PROCEDURE',
job_action => 'DBO.ACT_VIEW_MATERIA',
repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'America/Bogota',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'JOB ACTUALIZAR LA VISTAS MATERILIZADAS todos los sabados 8 pm',
auto_drop => FALSE,
enabled => TRUE);
END;