problem with MATERIALIZED VIEW (snapshot)

Hi,
I've any problem with creating MATERIALIZED VIEW (snapshot)

My table is ROOMS:

SQL> DESC ROOMS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LS_ID                                              VARCHAR2(32)
 BL_ID                                     NOT NULL VARCHAR2(32)   PRIMARY KEY1
 FL_ID                                     NOT NULL VARCHAR2(4)    PRIMARY KEY2
 RM_ID                                     NOT NULL VARCHAR2(8)    PRIMARY KEY3
 SITE_ID                                            VARCHAR2(32)


SQL> SELECT COUNT(*)
  2  FROM ROOMS;

  COUNT(*)                                                                      
----------
    203973                                                                      

SQL> SELECT COUNT (TOT)
  2   FROM (SELECT COUNT(*) TOT, LS_ID, SITE_ID
  3   FROM ROOMS
  4   GROUP BY LS_ID, SITE_ID);

COUNT(TOT)
----------
     11673

I'd like to create one MATERIALIZED VIEW that refresh every 30 seconds when I insert, update or delete on ROOMS table.

I tried this:


CREATE MATERIALIZED VIEW ROOMS_SNAP
   BUILD IMMEDIATE
   REFRESH complete
   START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
   NEXT  sysdate + 30/86400
   disable QUERY REWRITE
   AS
   SELECT LS_ID, SITE_ID
    FROM ROOMS
   GROUP BY LS_ID, SITE_ID
   
but when I insert, update or delete one record on ROOMS table, ROOMS_SNAP not refresh.

What I wrong?
How Can I write MATERIALIZED VIEW to maintain synchronization
between  ROOMS_SNAP AND LS_ID, SITE_ID by ROOMS?

Thanks!

dba_rafAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
How long does it take to do the refresh?  Since this is a complete refresh, it may take a while to refresh, then it would wait about 30 seconds to start again.

How many job processes are running?  Check the job_queue_processes parameter.  Jobs run serially, so if there are not enough processes to handle the current queue, the queue waits.

Keep in mind that it will probably not be exactly 30 seconds.  The job processes only check for jobs every 5 seconds (check the now hidden parameter _job_queue_interval), depending on where the job is scheduled in that 5 second window it could be longer.
0
 
dba_rafAuthor Commented:
this is my parameter file:

NAME                                 TYPE VALUE
------------------------------ ---------- ------------------------------        
O7_DICTIONARY_ACCESSIBILITY             1 FALSE                                
active_instance_count                   3                                      
aq_tm_processes                         3 0                                    
archive_lag_target                      3 0                                    
audit_sys_operations                    1 FALSE                                
audit_trail                             2 NONE                                  
background_core_dump                    2 partial                              
background_dump_dest                    2 C:\oracle\admin\IMM1\bdump            
backup_tape_io_slaves                   1 FALSE                                
bitmap_merge_area_size                  3 1048576                              
blank_trimming                          1 FALSE                                
buffer_pool_keep                        2
buffer_pool_recycle                     2
circuits                                3 170                                  
cluster_database                        1 FALSE                                
cluster_database_instances              3 1                                    
cluster_interconnects                   2                                      
commit_point_strength                   3 1                                    
compatible                              2 8.1.0                                
control_file_record_keep_time           3 7                                    
core_dump_dest                          2 %ORACLE_HOME%\RDBMS\TRACE
cpu_count                               3 1                                    
create_bitmap_area_size                 3 8388608                              
cursor_sharing                          2 EXACT                                
cursor_space_for_time                   1 FALSE                                
db_16k_cache_size                       6 0                                    
db_2k_cache_size                        6 0                                    
db_32k_cache_size                       6 0                                    
db_4k_cache_size                        6 0
db_8k_cache_size                        6 0
db_block_buffers                        3 12637                                
db_block_checking                       1 FALSE                                
db_block_checksum                       1 TRUE                                  
db_block_size                           3 8192                                  
db_cache_advice                         2 ON                                    
db_cache_size                           6 0                                    
db_create_file_dest                     2                                      
db_create_online_log_dest_1             2                                      
db_create_online_log_dest_2             2                                      
db_create_online_log_dest_3             2
db_create_online_log_dest_4             2
db_create_online_log_dest_5             2                                      
db_domain                               2                                      
db_file_multiblock_read_count           3 8                                    
db_file_name_convert                    2                                      
db_files                                3 1024                                  
db_keep_cache_size                      6 0                                    
db_name                                 2 IMM1                                  
db_recycle_cache_size                   6 0                                    
db_writer_processes                     3 1                                    
dblink_encrypt_login                    1 FALSE
dbwr_io_slaves                          3 0                                    
dg_broker_config_file1                  2 %ORACLE_HOME%\DATABASE\DR1%ORA        
                                          CLE_SID%.DAT
                                                                               
dg_broker_config_file2                  2 %ORACLE_HOME%\DATABASE\DR2%ORA        
                                          CLE_SID%.DAT                          
                                                                               
dg_broker_start                         1 FALSE                                
disk_asynch_io                          1 TRUE                                  
dispatchers                             2 (PROTOCOL=TCP)(PRE=oracle.auro        
distributed_lock_timeout                3 60
dml_locks                               3 748                                  
drs_start                               1 FALSE                                
enqueue_resources                       3 1792                                  
event                                   2                                      
fal_client                              2
fal_server                              2                                      
fast_start_io_target                    3 0                                    
fast_start_mttr_target                  3 0                                    
fast_start_parallel_rollback            2 LOW
file_mapping                            1 FALSE                                
filesystemio_options                    2                                      
fixed_date                              2
gc_files_to_locks                       2                                      
global_context_pool_size                2                                      
global_names                            1 TRUE                                  
hash_area_size                          3 131072                                
hash_join_enabled                       1 TRUE                                  
hi_shared_memory_address                3 0                                    
hs_autoregister                         1 TRUE                                  
ifile                                   4
instance_groups                         2                                      
instance_name                           2 IMM1                                  
instance_number                         3 0
java_max_sessionspace_size              3 0                                    
java_pool_size                          6 25165824                              
java_soft_sessionspace_limit            3 0                                    
job_queue_processes                     3 0                                    
large_pool_size                         6 8388608                              
license_max_sessions                    3 0                                    
license_max_users                       3 0                                    
license_sessions_warning                3 0
local_listener                          2                                      
lock_name_space                         2                                      
lock_sga                                1 FALSE
log_archive_dest                        2                                      
log_archive_dest_1                      2                                      
log_archive_dest_10                     2                                      
log_archive_dest_2                      2                                      
log_archive_dest_3                      2                                      
log_archive_dest_4                      2                                      
log_archive_dest_5                      2                                      
log_archive_dest_6                      2
log_archive_dest_7                      2                                      
log_archive_dest_8                      2                                      
log_archive_dest_9                      2
log_archive_dest_state_1                2 enable                                
log_archive_dest_state_10               2 enable                                
log_archive_dest_state_2                2 enable                                
log_archive_dest_state_3                2 enable                                
log_archive_dest_state_4                2 enable                                
log_archive_dest_state_5                2 enable                                
log_archive_dest_state_6                2 enable                                
log_archive_dest_state_7                2 enable
log_archive_dest_state_8                2 enable                                
log_archive_dest_state_9                2 enable                                
log_archive_duplex_dest                 2
log_archive_format                      2 ARC%S.%T                              
log_archive_max_processes               3 2                                    
log_archive_min_succeed_dest            3 1                                    
log_archive_start                       1 FALSE                                
log_archive_trace                       3 0                                    
log_buffer                              3 32768                                
log_checkpoint_interval                 3 10000                                
log_checkpoint_timeout                  3 1800
log_checkpoints_to_alert                1 FALSE                                
log_file_name_convert                   2                                      
log_parallelism                         3 1
logmnr_max_persistent_sessions          3 1                                    
max_commit_propagation_delay            3 700                                  
max_dispatchers                         3 5                                    
max_dump_file_size                      2 10240                                
max_enabled_roles                       3 30                                    
max_rollback_segments                   3 37                                    
max_shared_servers                      3 20                                    
mts_circuits                            3 170
mts_dispatchers                         2 (PROTOCOL=TCP)(PRE=oracle.auro        
                                          ra.server.SGiopServer)                

mts_listener_address                    2                                      
mts_max_dispatchers                     3 5                                    
mts_max_servers                         3 20                                    
mts_multiple_listeners                  1 FALSE                                
mts_servers                             3 1                                    
mts_service                             2 IMM1                                  
mts_sessions                            3 165                                  
nls_calendar                            2
nls_comp                                2                                      
nls_currency                            2                                      
nls_date_format                         2
nls_date_language                       2                                      
nls_dual_currency                       2                                      
nls_iso_currency                        2                                      
nls_language                            2 AMERICAN                              
nls_length_semantics                    2 BYTE                                  
nls_nchar_conv_excp                     2 FALSE                                
nls_numeric_characters                  2                                      
nls_sort                                2
nls_territory                           2 AMERICA                              
nls_time_format                         2                                      
nls_time_tz_format                      2
nls_timestamp_format                    2                                      
nls_timestamp_tz_format                 2                                      
object_cache_max_size_percent           3 10                                    
object_cache_optimal_size               3 102400                                
olap_page_pool_size                     3 33554432                              
open_cursors                            3 300                                  
open_links                              3 4                                    
open_links_per_instance                 3 4
optimizer_dynamic_sampling              3 1                                    
optimizer_features_enable               2 9.2.0                                
optimizer_index_caching                 3 0
optimizer_index_cost_adj                3 100                                  
optimizer_max_permutations              3 2000                                  
optimizer_mode                          2 CHOOSE                                
oracle_trace_collection_name            2                                      
oracle_trace_collection_path            2 %ORACLE_HOME%\OTRACE\ADMIN\CDF        
                                          \
oracle_trace_collection_size            3 5242880
oracle_trace_enable                     1 FALSE                                
oracle_trace_facility_name              2 oracled                              
oracle_trace_facility_path              2 %ORACLE_HOME%\OTRACE\ADMIN\FDF
                                          \                                    
os_authent_prefix                       2
os_roles                                1 FALSE                                
parallel_adaptive_multi_user            1 FALSE                                
parallel_automatic_tuning               1 FALSE                                
parallel_execution_message_siz          3 2148                                  
parallel_instance_group                 2
parallel_max_servers                    3 5                                    
parallel_min_percent                    3 0                                    
parallel_min_servers                    3 0
parallel_server                         1 FALSE                                
parallel_server_instances               3 1                                    
parallel_threads_per_cpu                3 2                                    
partition_view_enabled                  1 FALSE                                
pga_aggregate_target                    6 0                                    
plsql_compiler_flags                    2 INTERPRETED
plsql_native_c_compiler                 2                                      
plsql_native_library_dir                2                                      
plsql_native_linker                     2
plsql_native_make_file_name             2
plsql_native_make_utility               2                                      
plsql_v2_compatibility                  1 FALSE                                
pre_page_sga                            1 FALSE                                
processes                               3 150
query_rewrite_enabled                   2 false                                
query_rewrite_integrity                 2 enforced                              
rdbms_server_dn                         2
read_only_open_delayed                  1 FALSE                                
recovery_parallelism                    3 0                                    
remote_archive_enable                   2 true                                  
remote_dependencies_mode                2 TIMESTAMP                            
remote_listener                         2                                      
remote_login_passwordfile               2 EXCLUSIVE                            
remote_os_authent                       1 FALSE                                
remote_os_roles                         1 FALSE
replication_dependency_trackin          1 TRUE
g                                                                              
resource_limit                          1 FALSE
resource_manager_plan                   2                                      
rollback_segments                       2                                      
row_locking                             2 always                                
serial_reuse                            2 DISABLE                              
serializable                            1 FALSE                                
service_names                           2 IMM1                                  
session_cached_cursors                  3 0
session_max_open_files                  3 10                                    
sessions                                3 170                                  
sga_max_size                            6 204782052
shadow_core_dump                        2 partial                              
shared_memory_address                   3 0                                    
shared_pool_reserved_size               6 2097152                              
shared_pool_size                        6 41943040                              
shared_server_sessions                  3 165                                  
shared_servers                          3 1                                    
sort_area_retained_size                 3 65536
sort_area_size                          3 65536
spfile                                  2                                      
sql92_security                          1 FALSE                                
sql_trace                               1 FALSE
sql_version                             2 NATIVE                                
standby_archive_dest                    2 %ORACLE_HOME%\RDBMS                  
standby_file_management                 2 MANUAL                                
star_transformation_enabled             2 FALSE                                
statistics_level                        2 TYPICAL                              
tape_asynch_io                          1 TRUE                                  
thread                                  3 0                                    
timed_os_statistics                     3 0
timed_statistics                        1 TRUE                                  
trace_enabled                           1 TRUE                                  
tracefile_identifier                    2
transaction_auditing                    1 TRUE                                  
transactions                            3 187                                  
transactions_per_rollback_segm          3 5                                    
ent                                                                            
undo_management                         2 MANUAL
undo_retention                          3 900                                  
undo_suppress_errors                    1 FALSE
undo_tablespace                         2                                      
use_indirect_data_buffers               1 FALSE                                
user_dump_dest                          2 C:\oracle\admin\IMM1\udump
utl_file_dir                            2 c:\risk_analysis                      
workarea_size_policy                    2 MANUAL                                
0
 
johnsoneSenior Oracle DBACommented:
What version of Oracle?  Compatible is set to 8.1, but I do not see job_queue_interval, which would indicate you are on at least 9.

How long does the query take?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dba_rafAuthor Commented:
my oracle version is 9i

I tried this example:

SQL> create table rooms2 (id number);
Table created.

SQL> CREATE MATERIALIZED VIEW ROOMS_SNAP
2  BUILD IMMEDIATE  
3  REFRESH complete
4  START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')  
5  NEXT sysdate + 30/86400  
6  disable QUERY REWRITE  
7  AS  
8  SELECT id  
9  FROM ROOMS2
10  GROUP BY id
11  
/
Materialized view created.

SQL> insert into rooms2 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from ROOMS_SNAP;
no rows selected

after 5 minutes

/
no rows selected

but IF I run:

SQL> execute dbms_refresh.refresh('ROOMS_SNAP');

PL/SQL procedure successfully completed.

select * from ROOMS_SNAP;

        ID
---------    
        1    

What means?
My refresh is manual?

How can I refresh automatically my tables ROOMS?

Thanks
0
 
dba_rafAuthor Commented:
I run:

SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer                  0

Now, How can I refreshed the materislized view ?
0
 
actonwangCommented:
you need to set job_queque_processes value at least 1 to do automatic refresh.
0
 
ravindran_eeeCommented:
Hmmmm.. The parameter job_queue_processes should hold a value greater than 0.. U can set it to 3..
Then the materialized views would get refreshed properly..
U can either the update the init<instance>.ora file or use ALTER SYSTEM command (as shown below) to update the same..

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=3 SCOPE=BOTH
0
 
actonwangCommented:
use:

alter system set job_queque_processes=1;
0
 
actonwangCommented:
>>alter system set job_queque_processes=1;

    sorry:
   
    alter system set job_queue_processes=1;

   usually 1 process should be enough.
0
 
actonwangCommented:
>>START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')

     you only need to write it as:

     start with sysdate
0
 
dba_rafAuthor Commented:
OK,

with ALTER SYSTEM SET JOB_QUEUE_PROCESSES=3
run correctly

JOB_QUEUE_PROCESSES is Dynamic parameters?

don't I have to shutdown and restart the Database to applied the change?

Thanks!
0
 
johnsoneSenior Oracle DBACommented:
I don't have the Oracle 9 documentation handy.  However if the alter system worked, then no shut down is necessary.

Be sure the change gets back into your init.ora or spfile, whichever you are using so the next time the database is started the parameter remains in effect.
0
 
actonwangCommented:
you can issue as:

  alter system set job_queue_processes=1 scope=both;

it will take effect now and next time it starts.

Acton
0
 
ravindran_eeeCommented:
As actonwang said thats the reason i had given scope=both..
That would make sure that when u restart the database, the changes are retained..
0
 
actonwangCommented:
oh, sorry, ravindran_eee, I i repeated it.
0
 
ravindran_eeeCommented:
Thats ok :-))
0
 
johnsoneSenior Oracle DBACommented:
I was responding to dba_raf's post.  The scope is not included in the statement he posted.  I just wanted to be sure it was checked it was changed for the next restart.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.