ENABLE INMEMORY FOR A TABLE IN ORACLE

Check if the inmemory area is enabled in the database with subpools for columnar and metadata

SQL> SELECT POOL,ALLOC_BYTES/ 1048576 ALLOC_MB,
          USED_BYTES/ 1048576 USED_MB,POPULATE_STATUS,
          CON_ID,
          case when POOL='64KB POOL' then 'columnar data pool'
          when POOL='1MB POOL' then 'metadata pool' end "IMCS_subpools"
  FROM v$inmemory_area;

POOL                         ALLOC_MB    USED_MB POPULATE_STATUS                CON_ID IMCS_subpools
-------------------------- ---------- ---------- -------------------------- ---------- ------------------
1MB POOL                         1399          0 DONE                                1 metadata pool
64KB POOL                         584          0 DONE                                1 columnar data pool
1MB POOL                         1399          0 DONE                                2 metadata pool
64KB POOL                         584          0 DONE                                2 columnar data pool
1MB POOL                         1399          0 DONE                                3 metadata pool
64KB POOL                         584          0 DONE                                3 columnar data pool

6 rows selected.

IMCO is the background process which

[oracle@IMTEST ~]$ ps -ef|grep imco|grep -v 'grep'
oracle      2877       1  0 20:47 ?        00:00:00 ora_imco_IMTEST

To place a table in inmemory heap, use the below steps. Check the size of the segment which is going to be placed in inmemory. Ensure that the table size is smaller than the inmemory capacity.

SQL> SELECT bytes/1048576 MB FROM dba_segments WHERE segment_name='YTBL';

        MB
----------
        72

SQL> ALTER TABLE ytbl INMEMORY;

Table altered.
SQL> SELECT inmemory FROM dba_tables WHERE table_name='YTBL';

INMEMORY
--------
ENABLED

To check the columns and their compression level, check v$im_column_level. If the whole table is placed in inmemory, then all the columns are enabled for inmemory processing.

SQL> SELECT * FROM v$im_column_level;

   INST_ID OWNER                   OBJ_NUM TABLE_NAME           SEGMENT_COLUMN_ID COLUMN_NAME          INMEMORY_COMPRESSION     CON_ID
---------- -------------------- ---------- -------------------- ----------------- -------------------- -------------------- ----------
         1 KISH                      73275 YTBL                                 1 XNAME                DEFAULT                       1
         1 KISH                      73275 YTBL                                 2 XID                  DEFAULT                       1
         1 KISH                      73275 YTBL                                 3 CITY                 DEFAULT                       1
         1 KISH                      73275 YTBL                                 4 COUNTRY              DEFAULT                       1
         1 KISH                      73275 YTBL                                 5 DEPARTMENT           DEFAULT                       1
         1 KISH                      73275 YTBL                                 6 DEP_ID               DEFAULT                       1
         1 KISH                      73275 YTBL                                 7 XDATE                DEFAULT                       1
         1 KISH                      73275 YTBL                                 8 SALARY               DEFAULT                       1

8 rows selected.

After placing the table inmemory, the plan shows “TABLE ACCESS INMEMORY FULL” operations in execution plan which means that the benefits of inmemory is utilized for cost based optimization by optimizer.

SQL> set autot traceonly explain
SQL> SELECT xid,city FROM ytbl WHERE salary > 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2390399380

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   999K|    17M|  2234   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS INMEMORY FULL| YTBL |   999K|    17M|  2234   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - inmemory("SALARY">1000)
       filter("SALARY">1000)

Tracing IMCO shows the following lines during a select on inmemory table. Also PMON is monitoring the IMCO process to check necessity of restart

     6.016085 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=262457}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000040 openat(AT_FDCWD, "/proc/2787/stat", O_RDONLY) = 9
     0.000029 read(9, "2787 (ora_pmon_imtest) S 1 2787 "..., 999) = 303
     0.000152 close(9)                  = 0
     0.000783 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=262933}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000507 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=263113}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000139 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=263140}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.003124 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=265951}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.002630 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=267968}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.001389 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=269039}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0

     5.952023 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=269943}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000069 openat(AT_FDCWD, "/proc/2787/stat", O_RDONLY) = 9
     0.000126 read(9, "2787 (ora_pmon_imtest) S 1 2787 "..., 999) = 303
     0.000151 close(9)                  = 0
     0.001354 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=271101}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000984 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=271886}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000118 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=271893}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000360 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=271959}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000275 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272000}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000248 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272059}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000110 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272069}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000328 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272111}, ru_stime={tv_sec=0, tv_usec=12450}, ...}) = 0
     0.000953 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272737}, ru_stime={tv_sec=0, tv_usec=12467}, ...}) = 0
     0.000263 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272748}, ru_stime={tv_sec=0, tv_usec=12468}, ...}) = 0
     0.000192 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272810}, ru_stime={tv_sec=0, tv_usec=12470}, ...}) = 0
     0.000104 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272817}, ru_stime={tv_sec=0, tv_usec=12471}, ...}) = 0
     0.000209 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272848}, ru_stime={tv_sec=0, tv_usec=12472}, ...}) = 0
     0.000171 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272860}, ru_stime={tv_sec=0, tv_usec=12473}, ...}) = 0
     0.000345 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272913}, ru_stime={tv_sec=0, tv_usec=12475}, ...}) = 0
     0.000133 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272930}, ru_stime={tv_sec=0, tv_usec=12476}, ...}) = 0
     0.000273 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272955}, ru_stime={tv_sec=0, tv_usec=12477}, ...}) = 0
     0.000130 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272986}, ru_stime={tv_sec=0, tv_usec=12478}, ...}) = 0
     0.000198 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=272995}, ru_stime={tv_sec=0, tv_usec=12479}, ...}) = 0
     0.000385 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273072}, ru_stime={tv_sec=0, tv_usec=12482}, ...}) = 0
     0.001023 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273079}, ru_stime={tv_sec=0, tv_usec=13235}, ...}) = 0
     0.000294 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273140}, ru_stime={tv_sec=0, tv_usec=13238}, ...}) = 0
     0.000110 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273150}, ru_stime={tv_sec=0, tv_usec=13238}, ...}) = 0
     0.000254 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273179}, ru_stime={tv_sec=0, tv_usec=13239}, ...}) = 0
     0.000103 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273188}, ru_stime={tv_sec=0, tv_usec=13240}, ...}) = 0
     0.000174 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273217}, ru_stime={tv_sec=0, tv_usec=13241}, ...}) = 0
     0.000350 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273269}, ru_stime={tv_sec=0, tv_usec=13244}, ...}) = 0
     0.000107 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273279}, ru_stime={tv_sec=0, tv_usec=13244}, ...}) = 0
     0.000187 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=273308}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001018 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274051}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000082 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274056}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000113 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274090}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000105 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274096}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000140 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274123}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000131 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274165}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000192 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274243}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000099 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=274271}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001034 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=275164}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000137 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=275220}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000131 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=275274}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001235 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=276368}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001235 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=277488}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001044 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=278411}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000608 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=278900}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001657 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=280432}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.002180 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=282491}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000926 stat("/etc/localtime", {st_mode=S_IFREG|0644, st_size=312, ...}) = 0
     0.000105 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=283301}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000133 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=283307}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000121 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=283350}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000126 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=283388}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000445 stat("/etc/localtime", {st_mode=S_IFREG|0644, st_size=312, ...}) = 0
     0.000488 stat("/etc/localtime", {st_mode=S_IFREG|0644, st_size=312, ...}) = 0
     0.000075 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=284091}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001061 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=284952}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001421 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=286244}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.005180 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=291149}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.001771 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=292768}, ru_stime={tv_sec=0, tv_usec=13246}, ...}) = 0
     0.000410 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000270 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000164 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000294 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000245 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000269 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000122 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000079 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000162 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000197 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000099 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000114 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000139 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000245 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000045 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000141 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000131 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000113 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000297 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000047 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000167 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000156 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000244 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000162 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000126 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000130 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000169 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000151 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000057 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000316 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000195 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000055 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000150 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000149 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000222 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000161 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000037 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000168 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000111 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000164 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000045 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000083 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000077 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000114 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000062 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000164 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000119 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000123 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000183 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000068 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000118 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000309 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000211 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000102 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000121 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000242 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000124 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000121 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000044 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000271 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000218 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000091 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000157 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000181 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000183 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000076 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000058 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000156 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000196 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000170 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000036 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000077 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000233 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000140 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000032 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000119 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000125 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000116 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000065 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000137 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000092 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000116 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000069 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000114 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000112 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000184 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
     0.000136 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000032 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000112 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000071 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000229 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000063 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000162 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000096 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000110 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000055 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000106 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000116 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000121 --- SIGSEGV {si_signo=SIGSEGV, si_code=SI_KERNEL, si_addr=NULL} ---
     0.000060 rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
     0.000111 rt_sigprocmask(SIG_UNBLOCK, [SEGV], NULL, 8) = 0
     0.000127 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
     0.000187 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=292868}, ru_stime={tv_sec=0, tv_usec=15201}, ...}) = 0
     0.001627 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=294306}, ru_stime={tv_sec=0, tv_usec=15220}, ...}) = 0

No match for argument: stressError: Unable to find a match: stress

Cause:

For stress testing the server and database, stress is a good tool. But in linux , the below error occur due to older epel release

[root@xhydra oracle]# yum install stress
Last metadata expiration check: 3:45:19 ago on Fri 05 May 2023 06:36:00 AM IST.
No match for argument: stress
Error: Unable to find a match: stress

Solution:

Install the latest release of epel and stress can be installed without any error.

[root@xhydra oracle]# yum install epel-release -y
Last metadata expiration check: 3:45:51 ago on Fri 05 May 2023 06:36:00 AM IST.
Dependencies resolved.
=======================================================================================================================================
 Package                                Architecture          Version                           Repository                        Size
=======================================================================================================================================
Installing:
 oracle-epel-release-el8                x86_64                1.0-5.el8                         ol8_baseos_latest                 15 k
Installing dependencies:
 yum-utils                              noarch                4.0.21-4.0.1.el8_5                ol8_baseos_latest                 73 k

Transaction Summary
=======================================================================================================================================
Install  2 Packages

Total download size: 88 k
Installed size: 41 k
Downloading Packages:
(1/2): oracle-epel-release-el8-1.0-5.el8.x86_64.rpm                                                     60 kB/s |  15 kB     00:00
(2/2): yum-utils-4.0.21-4.0.1.el8_5.noarch.rpm                                                         189 kB/s |  73 kB     00:00
---------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                  226 kB/s |  88 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                               1/1
  Installing       : yum-utils-4.0.21-4.0.1.el8_5.noarch                                                                           1/2
  Installing       : oracle-epel-release-el8-1.0-5.el8.x86_64                                                                      2/2
  Running scriptlet: oracle-epel-release-el8-1.0-5.el8.x86_64                                                                      2/2
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : oracle-epel-release-el8-1.0-5.el8.x86_64                                                                      1/2
  Verifying        : yum-utils-4.0.21-4.0.1.el8_5.noarch                                                                           2/2

Installed:
  oracle-epel-release-el8-1.0-5.el8.x86_64                             yum-utils-4.0.21-4.0.1.el8_5.noarch

Complete!
[root@xhydra oracle]# yum install stress -y
Oracle Linux 8 EPEL Packages for Development (x86_64)                                                   28 MB/s |  45 MB     00:01
Oracle Linux 8 EPEL Modular Packages for Development (x86_64)                                          1.5 MB/s | 322 kB     00:00
Dependencies resolved.
=======================================================================================================================================
 Package                    Architecture               Version                            Repository                              Size
=======================================================================================================================================
Installing:
 stress                     x86_64                     1.0.4-24.el8                       ol8_developer_EPEL                      40 k

Transaction Summary
=======================================================================================================================================
Install  1 Package

Total download size: 40 k
Installed size: 79 k
Downloading Packages:
stress-1.0.4-24.el8.x86_64.rpm                                                                          15 kB/s |  40 kB     00:02
---------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                   15 kB/s |  40 kB     00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                               1/1
  Installing       : stress-1.0.4-24.el8.x86_64                                                                                    1/1
  Running scriptlet: stress-1.0.4-24.el8.x86_64                                                                                    1/1
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : stress-1.0.4-24.el8.x86_64                                                                                    1/1

Installed:
  stress-1.0.4-24.el8.x86_64

Complete!

MVIEW REFRESH REMOTE USING DBLINK

Set the job queue process to 0 purposefully and donot set this in production system, since this may interrupt all scheduled jobs

kIsH@xHydra<>ALTER system set job_queue_processes=0;

System altered.

Create a database link from target to source. Before this, verify the tns connectivity and listener.

kIsH@xHydra<>conn kish/password
Connected.
kIsH@xHydra<>create database link mvremote connect to kish identified by "password" using 'db9zx';

Database link created.

kIsH@xHydra<>SELECT * from DUAL@mvremote;

D
-
X

Check the total records in base table from source

kIsH@Xhydra<>select count(*) from ytbl;

  COUNT(*)
----------
   16000000

Create materialized view in remote database using dblink

kIsH@xHydra<>CREATE MATERIALIZED VIEW mvremote
    BUILD IMMEDIATE
    REFRESH COMPLETE ON DEMAND
    AS SELECT * FROM KISH.YTBL@mvremote;

Materialized view created.

kIsH@xHydra<>select count(*) from mvremote;

  COUNT(*)
----------
  1000000

Create a scheduler job for complete refresh of mview

kIsH@xHydra<>BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'MV_MVREMOTE_REFRESH1',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'BEGIN DBMS_MVIEW.REFRESH(''MVREMOTE'');END;',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'This job refresh MVREMOTE every day'
    );
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16

PL/SQL procedure successfully completed.

Verify the job

kIsH@xHydra<>SELECT owner,job_name,program_name,job_type,job_action FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';

OWNER                JOB_NAME             PROGRAM_NAME         JOB_TYPE             JOB_ACTION
-------------------- -------------------- -------------------- -------------------- --------------------
KISH                 MV_MVREMOTE_REFRESH1                      STORED_PROCEDURE     REFRESH_MVREMOTE

kIsH@xHydra<>col USERNAME for a20
kIsH@xHydra<>set lines 200 pages 1000
kIsH@xHydra<>select s.username,s.sid,s.serial#,s.program,p.spid from v$process p inner join v$session s on (s.paddr = p.addr) where s.username='KISH';

USERNAME                    SID    SERIAL# PROGRAM                                          SPID
-------------------- ---------- ---------- ------------------------------------------------ ------------------------
KISH                          1      51465 sqlplus@staxhost (TNS V1-V3)                     5182

Check dbms_scheduler functions which can be used to run a job manually

kIsH@xHydra<>col text for a80
kIsH@xHydra<>SELECT name,type,text FROM dba_source WHERE text LIKE '%DBMS_SCHEDULER%';

NAME                 TYPE         TEXT
-------------------- ------------ --------------------------------------------------------------------------------
DBMS_SCHEDULER       PACKAGE        logging_level           IN PLS_INTEGER  DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
DBMS_WORKLOAD_CAPTUR PACKAGE        --    Jobs (DBMS_SCHEDULER/DBMS_JOB) will not be captured, no matter how
E

DBMS_REFRESH         PACKAGE        --   or FALSE indicates that DBMS_SCHEDULER will be used to create the
DBMS_FEATURE_REGISTE PROCEDURE       * DBMS_SCHEDULER   HWM is number of jobs per day
R_ALLHWM

DBMS_FEATURE_REGISTE PROCEDURE         HWM_DBMS_SCHEDULER_STR CONSTANT VARCHAR2(1000) :=
R_ALLHWM

DBMS_FEATURE_REGISTE PROCEDURE          ('HWM_DBMS_SCHEDULER',
R_ALLHWM

DBMS_FEATURE_REGISTE PROCEDURE           HWM_DBMS_SCHEDULER_STR,
R_ALLHWM

SYSLSBY_EDS_DDL_TRIG TRIGGER               DBMS_SCHEDULER.CREATE_JOB(
MGMT_CONFIG          PACKAGE BODY         DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id'
                                  ,inst_id);

MGMT_CONFIG          PACKAGE BODY       DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);
MGMT_CONFIG          PACKAGE BODY       -- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);
MGMT_CONFIG          PACKAGE BODY             DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_2_' || inst_id,'instan
                                  ce_id',inst_id);

MGMT_CONFIG          PACKAGE BODY           DBMS_SCHEDULER.ENABLE (p_job_name || '_2_' || inst_id);
MGMT_CONFIG          PACKAGE BODY       DBMS_SCHEDULER.RUN_JOB(JOB_NAME);
MGMT_CONFIG          PACKAGE BODY       DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);

15 rows selected.

DBMS_SCHEDULER.RUN_JOB can trigger the mview refresh job immediately.

kIsH@xHydra<>BEGIN
               DBMS_SCHEDULER.RUN_JOB
               (
                job_name => 'MV_MVREMOTE_REFRESH1',
                USE_CURRENT_SESSION => FALSE
               );
          END;
          /
BEGIN
*
ERROR at line 1:
ORA-27492: unable to run job "KISH"."MV_MVREMOTE_REFRESH1": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2

Set the job queue process parameter to higher value and execute the procedure again in target.

kIsH@xHydra<>ALTER system set job_queue_processes=20;

System altered.


kIsH@xHydra<>BEGIN
               DBMS_SCHEDULER.RUN_JOB
               (
                job_name => 'MV_MVREMOTE_REFRESH1',
                use_current_session => FALSE
               );
          END;
          /  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

Monitor the refresh from target database and all that can be seen is "SQL*Net more data from dblink" in general, unless there is a scarcity of any resource.

kIsH@xHydra<>col CURRMVOWNER for a20
kIsH@xHydra<>col CURRMVNAME for a20
kIsH@xHydra<>select * from v$mvrefresh;

       SID    SERIAL# CURRMVOWNER          CURRMVNAME               CON_ID
---------- ---------- -------------------- -------------------- ----------
        77      51278 KISH                 MVREMOTE                      1

kIsH@xHydra<>col ACTION for a20
kIsH@xHydra<>col EVENT for a20
kIsH@xHydra<>col username for a20
kIsH@xHydra<>col module for a20
kIsH@xHydra<>SELECT username,sid,serial#,action,module,program,event FROM v$session WHERE action='MV_MVREMOTE_REFRESH1';

USERNAME                    SID    SERIAL# ACTION               MODULE               PROGRAM              EVENT
-------------------- ---------- ---------- -------------------- -------------------- -------------------- --------------------
KISH                         77      51278 SQL*Net more data from dblink

Target mview is refreshed successfully in remote database.

kIsH@xHydra<>select count(*) from mvremote;

  COUNT(*)
----------
  16000000

ORA-16038: log 5 sequence# 290 cannot be archivedORA-19502: write error on file “”, block number (block size=)ORA-00312: online log 5 thread 1:

There was an archiver error during startup of the database which prevented the instance to start with ORA-03113. There was no space left on the filesystem which further caused pressure to prevent the instance from coming up.

kIsH@xHydra<>startup;
ORACLE instance started.

Total System Global Area 2415918568 bytes
Fixed Size                  9137640 bytes
Variable Size             553648128 bytes
Database Buffers         1845493760 bytes
Redo Buffers                7639040 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4796
Session ID: 1 Serial number: 21471

Alert log was flooded with archive errors

ORA-19502: write error on file "/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53xwqs1_.arc", block number 69632 (block size=512)
ARC0 (PID:6786): I/O error 19502 archiving LNO:5 to '/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53xwqs1_.arc'
ARC0 (PID:6786): Stuck archiver: inactive mandatory LAD:1
ARC0 (PID:6786): Stuck archiver condition declared
2023-05-03T11:26:39.855296+05:30
Errors in file /u01/app/oracle/diag/rdbms/exhydra/eXhydra/trace/eXhydra_arc0_6786.trc:
ORA-16038: log 5 sequence# 290 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/EXHYDRA/onlinelog/redo02.log'
ORA-00312: online log 5 thread 1: '/apps01/RECO/EXHYDRA/onlinelog/redo04.log'
ARC0 (PID:6786): Archival error occurred on a closed thread, archiver continuing
2023-05-03T11:26:39.857066+05:30
ORACLE Instance eXhydra, archival error, archiver continuing
2023-05-03T11:27:40.222395+05:30
...
2023-05-03T11:28:40.759985+05:30
Errors in file /u01/app/oracle/diag/rdbms/exhydra/eXhydra/trace/eXhydra_arc2_6790.trc:
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 122880
Additional information: 4294967295
ORA-19502: write error on file "/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53y0jmr_.arc", block number 122880 (block size=512)
Deleted Oracle managed file /apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53y0jmr_.arc
2023-05-03T11:28:40.765832+05:30
Errors in file /u01/app/oracle/diag/rdbms/exhydra/eXhydra/trace/eXhydra_arc2_6790.trc:
ORA-19502: write error on file "/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53y0jmr_.arc", block number 122880 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 122880
Additional information: 4294967295
ORA-19502: write error on file "/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53y0jmr_.arc", block number 122880 (block size=512)
ARC2 (PID:6790): I/O error 19502 archiving LNO:5 to '/apps01/RECO/EXHYDRA/archivelog/2023_05_03/o1_mf_1_290_l53y0jmr_.arc'
ARC2 (PID:6790): Stuck archiver: inactive mandatory LAD:1
ARC2 (PID:6790): Stuck archiver condition declared
2023-05-03T11:28:40.779566+05:30
Errors in file /u01/app/oracle/diag/rdbms/exhydra/eXhydra/trace/eXhydra_arc2_6790.trc:
ORA-16038: log 5 sequence# 290 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/EXHYDRA/onlinelog/redo02.log'
ORA-00312: online log 5 thread 1: '/apps01/RECO/EXHYDRA/onlinelog/redo04.log'
ARC2 (PID:6790): Archival error occurred on a closed thread, archiver continuing
2023-05-03T11:28:40.779642+05:30
ORACLE Instance eXhydra, archival error, archiver continuing
2023-05-03T11:29:19.007555+05:30
alter database clear logfile group 6
ORA-350 signalled during: alter database clear logfile group 6...

The mount point space is filled with datafiles and redo log files. Even all the archivelogs were deleted and backed up to tape. There are no files to be removed other than mandatory files

[oracle@staxhost oradata]$ df -h apps01
df: apps01: No such file or directory
[oracle@staxhost oradata]$ df -h /apps01
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        30G   30G   61M 100% /apps01
[oracle@staxhost oradata]$ cd /apps01/
[oracle@staxhost apps01]$ ls
oradata  prox2.xml  RECO
[oracle@staxhost apps01]$ cd RECO/
[oracle@staxhost RECO]$ du -sh *
4.8G    EXHYDRA
[oracle@staxhost RECO]$ cd EXHYDRA/
archivelog/                       backupset/                        EF2D7C66D65D5854E053C738A8C0C143/ onlinelog/
autobackup/                       controlfile/                      foreign_archivelog/
[oracle@staxhost RECO]$ cd EXHYDRA/
[oracle@staxhost EXHYDRA]$ du -sh *
0       archivelog
39M     autobackup
0       backupset
20M     controlfile
0       EF2D7C66D65D5854E053C738A8C0C143
0       foreign_archivelog
4.7G    onlinelog

Hence, mounted the database and tried dropping redo log groups. But none of them were archived. The goal is to open the database quickly without adding additional storage.

kIsH@xHydra<>startup mount;
ORACLE instance started.

Total System Global Area 2415918568 bytes
Fixed Size                  9137640 bytes
Variable Size             553648128 bytes
Database Buffers         1845493760 bytes
Redo Buffers                7639040 bytes
Database mounted.

kIsH@xHydra<>alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-00350: log 6 of instance eXhydra (thread 1) needs to be archived
ORA-00312: online log 6 thread 1: '/apps01/RECO/EXHYDRA/onlinelog/redo05.log'


kIsH@xHydra<>alter database clear logfile group 6;
alter database clear logfile group 6
*
ERROR at line 1:
ORA-00350: log 6 of instance eXhydra (thread 1) needs to be archived
ORA-00312: online log 6 thread 1: '/apps01/RECO/EXHYDRA/onlinelog/redo05.log'

Since, it takes more time to add additional storage, a quick workaround was applied by disabling the archive log and instance opened!

kIsH@xHydra<>alter database noarchivelog;

Database altered.

kIsH@xHydra<>alter database open;

Database altered.

PLS-00357: Table,View Or Sequence reference ‘MVREMOTE’ not allowed in this context

Cause:

Syntax issue

*** 2023-05-01T21:13:50.353139+05:30 (CDB$ROOT(1))
*** SESSION ID:(80.21755) 2023-05-01T21:13:50.353161+05:30
*** CLIENT ID:() 2023-05-01T21:13:50.353165+05:30
*** SERVICE NAME:(SYS$USERS) 2023-05-01T21:13:50.353167+05:30
*** MODULE NAME:(DBMS_SCHEDULER) 2023-05-01T21:13:50.353170+05:30
*** ACTION NAME:(MV_MVREMOTE_REFRESH1) 2023-05-01T21:13:50.353172+05:30
*** CLIENT DRIVER:() 2023-05-01T21:13:50.353175+05:30
*** CONTAINER ID:(1) 2023-05-01T21:13:50.353177+05:30

ORA-12012: error on auto execute of job "KISH"."MV_MVREMOTE_REFRESH1"
ORA-06550: line 1, column 783:
PLS-00357: Table,View Or Sequence reference 'MVREMOTE' not allowed in this context
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
~

Solution:

When specifying action for the mview refresh using scheduler jobs, double quotes should not be used inside DBMS_MVIEW.REFRESH package. Drop the job first and recreate it with single quotes.

kIsH@xHydra<>SELECT job_name,job_creator,job_action,comments FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';

JOB_NAME             JOB_CREATOR          JOB_ACTION           COMMENTS
-------------------- -------------------- -------------------- ----------------------------------------
MV_MVREMOTE_REFRESH1 KISH                 BEGIN DBMS_MVIEW.REF This job refresh MVREMOTE every day
                                          RESH("MVREMOTE");END
                                          ;

kIsH@xHydra<>conn kish/password
Connected.
kIsH@xHydra<>begin
  2  dbms_scheduler.drop_job('MV_MVREMOTE_REFRESH1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

kIsH@xHydra<>BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'MV_MVREMOTE_REFRESH1',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'BEGIN DBMS_MVIEW.REFRESH(''MVREMOTE'');END;',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'This job refresh MVREMOTE every day'
    );
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16

PL/SQL procedure successfully completed.

kIsH@xHydra<>col CURRMVOWNER for a20
kIsH@xHydra<>col CURRMVNAME for a20
kIsH@xHydra<>select * from v$mvrefresh;

       SID    SERIAL# CURRMVOWNER          CURRMVNAME               CON_ID
---------- ---------- -------------------- -------------------- ----------
        77      51278 KISH                 MVREMOTE                      1

ORA-12012: error on auto execute of job “KISH”.”MV_MVREMOTE_REFRESH1″

Cause:

*** 2023-05-01T19:05:19.376893+05:30 (CDB$ROOT(1))
*** SESSION ID:(73.30061) 2023-05-01T19:05:19.376926+05:30
*** CLIENT ID:() 2023-05-01T19:05:19.376932+05:30
*** SERVICE NAME:(SYS$USERS) 2023-05-01T19:05:19.376936+05:30
*** MODULE NAME:(DBMS_SCHEDULER) 2023-05-01T19:05:19.376940+05:30
*** ACTION NAME:(MV_MVREMOTE_REFRESH1) 2023-05-01T19:05:19.376945+05:30
*** CLIENT DRIVER:() 2023-05-01T19:05:19.376949+05:30
*** CONTAINER ID:(1) 2023-05-01T19:05:19.376953+05:30

ORA-12012: error on auto execute of job "KISH"."MV_MVREMOTE_REFRESH1"
ORA-06550: line 1, column 762:
PLS-00103: Encountered the symbol "DBMS_MVIEW" when expecting one of the following:
   := . ( @ % ;
The symbol ":=" was substituted for "DBMS_MVIEW" to continue.

Solution:

Use EXEC in sqlplus but not in scheduler job actions

kIsH@xHydra<>col COMMENTS for a40
kIsH@xHydra<>SELECT job_name,job_creator,job_action,comments FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';

JOB_NAME             JOB_CREATOR          JOB_ACTION           COMMENTS
-------------------- -------------------- -------------------- ----------------------------------------
MV_MVREMOTE_REFRESH1 KISH                 EXEC DBMS_MVIEW.REF This job refresh MVREMOTE every day
                                          RESH("MVREMOTE");                                          ;

Dropped the job and again recreated the job with ‘BEGIN <body of package block> END; ‘

kIsH@xHydra<>begin
  2  dbms_scheduler.drop_job('MV_MVREMOTE_REFRESH1');
  3  end;
  4  /

PL/SQL procedure successfully completed.


BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'MV_MVREMOTE_REFRESH1',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'BEGIN DBMS_MVIEW.REFRESH("MVREMOTE");END;',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'This job refresh MVREMOTE every day'
    );
END;
/ 

ORA-27492: unable to run job “KISH”.”MV_MVREMOTE_REFRESH1″: scheduler unavailable

Cause: The below error occur during lack of job queue processes or less job queues

kIsH@xHydra<>BEGIN
               DBMS_SCHEDULER.RUN_JOB
               (
                job_name => 'MV_MVREMOTE_REFRESH1',
                USE_CURRENT_SESSION => FALSE
               );
          END;
          /
BEGIN
*
ERROR at line 1:
ORA-27492: unable to run job "KISH"."MV_MVREMOTE_REFRESH1": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2

Solution:

kIsH@xHydra<>show parameter job_queue

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

kIsH@xHydra<>ALTER system set job_queue_processes=20;

System altered.


kIsH@xHydra<>BEGIN
               DBMS_SCHEDULER.RUN_JOB
               (
                job_name => 'MV_MVREMOTE_REFRESH1',
                USE_CURRENT_SESSION => FALSE
               );
          END;
          /  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

ORA-06576: not a valid function or procedure name

Cause:

This is syntax error due to missing argument.

kIsH@xHydra<>EXEC DBMS_SCHEDULER.RUN_JOB('MV_MVREMOTE_REFRESH1');
BEGIN DBMS_SCHEDULER.RUN_JOB('MV_MVREMOTE_REFRESH1'); END;

*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1


kIsH@xHydra<>EXEC DBMS_SCHEDULER.RUN_JOB('MV_MVREMOTE_REFRESH');
BEGIN DBMS_SCHEDULER.RUN_JOB('MV_MVREMOTE_REFRESH'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 775:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "" to continue.
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

Solution:

Check the text from dba_source to find the function and its argument. If this “use_current_session ” value is set to true, then the error occur but setting the value to false executes the precedure successfully

kIsH@xHydra<>select name,type,line,text from dba_source where text like '%DBMS_SCHEDULER%';


NAME                                                                                                                             TYPE               LINE
-------------------------------------------------------------------------------------------------------------------------------- ------------ ----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_SCHEDULER                                                                                                                   PACKAGE             667
  logging_level           IN PLS_INTEGER  DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,

DBMS_WORKLOAD_CAPTURE                                                                                                            PACKAGE              19
  --    Jobs (DBMS_SCHEDULER/DBMS_JOB) will not be captured, no matter how

DBMS_REFRESH                                                                                                                     PACKAGE              38
  --   or FALSE indicates that DBMS_SCHEDULER will be used to create the

MGMT_CONFIG                                                                                                                      PACKAGE BODY        105
        DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id',inst_id);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        109
      DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        111
      -- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        142
            DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_2_' || inst_id,'instance_id',inst_id);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        146
          DBMS_SCHEDULER.ENABLE (p_job_name || '_2_' || inst_id);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        164
        DBMS_SCHEDULER.RUN_JOB(JOB_NAME);

MGMT_CONFIG                                                                                                                      PACKAGE BODY        165
        DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);

DBMS_FEATURE_REGISTER_ALLHWM                                                                                                     PROCEDURE           277
   * DBMS_SCHEDULER   HWM is number of jobs per day

DBMS_FEATURE_REGISTER_ALLHWM                                                                                                     PROCEDURE           281
     HWM_DBMS_SCHEDULER_STR CONSTANT VARCHAR2(1000) :=

DBMS_FEATURE_REGISTER_ALLHWM                                                                                                     PROCEDURE           299
      ('HWM_DBMS_SCHEDULER',

DBMS_FEATURE_REGISTER_ALLHWM                                                                                                     PROCEDURE           301
       HWM_DBMS_SCHEDULER_STR,

SYSLSBY_EDS_DDL_TRIG                                                                                                             TRIGGER             103
         DBMS_SCHEDULER.CREATE_JOB(


15 rows selected.


kIsH@xHydra<>BEGIN
               DBMS_SCHEDULER.RUN_JOB
               (
                job_name => 'MV_MVREMOTE_REFRESH1',
                use_current_session => FALSE -- this parameter should be included with false
               );
          END;
          /  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

ORA-27475: unknown job “SYS”.”MV_MVREMOTE_REFRESH”

Cause:

Make sure that the job exists in the database

kIsH@xHydra<>BEGIN
  2           dbms_scheduler.drop_job(job_name => 'MV_MVREMOTE_REFRESH');
  3          END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-27475: unknown job "SYS"."MV_MVREMOTE_REFRESH"
ORA-06512: at "SYS.DBMS_ISCHED", line 278
ORA-06512: at "SYS.DBMS_SCHEDULER", line 751
ORA-06512: at line 2

Solution:

kIsH@xHydra<>SELECT owner,job_name,program_name,job_type,job_action FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';

OWNER                JOB_NAME             PROGRAM_NAME         JOB_TYPE             JOB_ACTION
-------------------- -------------------- -------------------- -------------------- --------------------
KISH                 MV_MVREMOTE_REFRESH                      STORED_PROCEDURE     REFRESH_MVREMOTE

kIsH@xHydra<>conn kish/password
Connected.
kIsH@xHydra<>BEGIN
  2  dbms_scheduler.drop_job(job_name => 'MV_MVREMOTE_REFRESH');
  3  END;
  4  /

PL/SQL procedure successfully completed.

ORA-27452: “BEGIN DBMS_MVIEW.REFRESH(“MVREMOTE”);END;” is an invalid name for adatabase object.

Cause:

When creating a scheduler job for mview refresh, Job type cannot be used as Stored procedure for dbms_mview.refresh because it is a pl/sql block which calls the stored procedure.

kIsH@xHydra<>BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'MV_MVREMOTE_REFRESH1',
    job_type            => 'STORED_PROCEDURE',
    job_action          => 'BEGIN DBMS_MVIEW.REFRESH("MVREMOTE");END;',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'This job refresh MVREMOTE every day'
    );
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16
BEGIN
*
ERROR at line 1:
ORA-27452: "BEGIN DBMS_MVIEW.REFRESH("MVREMOTE");END;" is an invalid name for a
database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 2

Solution:

Specify the job type as ‘PLSQL_BLOCK’ and procedure complete successfully

kIsH@xHydra<>BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'MV_MVREMOTE_REFRESH1',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'EXEC DBMS_MVIEW.REFRESH("MVREMOTE");',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'This job refresh MVREMOTE every day'
    );
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16

PL/SQL procedure successfully completed.