call dbms_mview.refresh – GPO (Grupo de Profissionais Oracle)

23 de abril de 2010 às 11:59 pm #93749

Sousa04

Participante

Galera consegui !!!!!!

tipo fiz o seguinte esquema para a criação e o acesso ok

DANDO PERMISSÃO DE CRIAÇÃO DA VIEW

sqlplus sys@homolog as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sex Abr 23 16:17:23 2010

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> grant CREATE MATERIALIZED VIEW to alexsousa;

Grant succeeded.

SQL>exit;
=================================================================================================================================

CRIANDO A VIEW VW_TESTE

sqlplus alexsousa@homolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sex Abr 23 16:17:23 2010

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> create materialized VW_TESTE as
select a , b
from table
group by a , b;

Materialized view created.

SQL> select MVIEW_NAME, REWRITE_ENABLED, REFRESH_MODE, REFRESH_METHOD, BUILD_MODE
2 from all_mviews;

MVIEW_NAME R REFRES REFRESH_ BUILD_MOD
—————————— – —— ——– ———
VW_TESTE N DEMAND FORCE IMMEDIATE

SQL> EXECUTE DBMS_MVIEW.REFRESH(‘VW_TESTE’,’?’)

PL/SQL procedure successfully completed.

SQL> exit

==============================================================================================
CRIANDO UM USUÁRIO PARA ACESSAR A VIEW NO MEU ESQUEMA

SQL> create user userteste identified by alex
2 default tablespace td_orfeu
3 quota unlimited on td_orfeu
4 ;

User created.

SQL> grant connect to userteste;

Grant succeeded.

SQL> grant resource to userteste;

Grant succeeded.

SQL> grant create any view to userteste;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to userteste;

Grant succeeded.

SQL>exit;

===============================================================================================
DANDO PERMISSÃO DA VIEW VW_TESTE PARA O USUÁRIO TESTE

sqlplus alexsousa@homolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sex Abr 23 16:17:23 2010

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> grant all on vw_teste to userteste
2 ;

Grant succeeded.

SQL> exit

==============================================================================================
EXECUTANDO A VIEW VW_TESTE

sqlplus userteste@homolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sex Abr 23 16:17:23 2010

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> EXECUTE DBMS_MVIEW.REFRESH(‘alexsousa.vw_teste’,’?’);

PL/SQL procedure successfully completed.

SQL>