Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem with MATERIALIZED VIEW (snapshot)

Posted on 2006-05-02
20
Medium Priority
?
769 Views
Last Modified: 2008-03-04
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!

0
Comment
Question by:dba_raf
  • 6
  • 4
  • 4
  • +2
18 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 336 total points
ID: 16585308
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
 

Author Comment

by:dba_raf
ID: 16585536
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
 
LVL 35

Expert Comment

by:johnsone
ID: 16586147
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:dba_raf
ID: 16586590
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
 

Author Comment

by:dba_raf
ID: 16587391
I run:

SQL> show parameter job

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

Now, How can I refreshed the materislized view ?
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 332 total points
ID: 16587483
you need to set job_queque_processes value at least 1 to do automatic refresh.
0
 
LVL 10

Assisted Solution

by:ravindran_eee
ravindran_eee earned 332 total points
ID: 16587496
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16587524
use:

alter system set job_queque_processes=1;
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16587595
>>alter system set job_queque_processes=1;

    sorry:
   
    alter system set job_queue_processes=1;

   usually 1 process should be enough.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16587730
>>START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')

     you only need to write it as:

     start with sysdate
0
 

Author Comment

by:dba_raf
ID: 16594323
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
 
LVL 35

Expert Comment

by:johnsone
ID: 16594954
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16595345
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
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 16595644
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16595793
oh, sorry, ravindran_eee, I i repeated it.
0
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 16595901
Thats ok :-))
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16596555
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20228871
Forced accept.

Computer101
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question