Oracle Materialized view

Materialized View

Materialized view is a database object which stores the query as well as the results in it.
Normal view will not store the results. Materialized view physically stored in the Database.

Using Materialized view we can
1. Pre-calculate Complex joins
2. Execute the aggregate function

Oracle automatically rewrites the SQL query, so the performance of the execution is faster.

Materialized view is created based on the base table. The table can be in the same database or a remote database.

There are 3 methods available to synchronize with base table
1. Refresh Fast
2. Refresh Complete
3. Refresh Force

There are two modes for Refresh Techniques
1.On Demand (need manual refresh procedure)
2.On Commit (automatic)


1.On Demand (need manual refresh procedure)

Create table learner (learner_id number(10), learner_name varchar2(100), topic varchar2(200), hours number(5) );

create materialized view mv_learner
as
select learner_id , learner_name  from learner;

(or)
create materialized view mv_learner
refresh on demand
as
select learner_id , learner_name  from learner;

Refresh procedure:
sql> execute dbms_mview.refresh('mv_learner ');

when we execute the above procedure materialized view ("mv_learner") will synchronize with the base table ("learner")

2.On Commit (automatic)

create materialized view mv_learner
on commit
as
select learner_id , learner_name  from learner;

Restrictions
Materialized views can only refresh ON COMMIT

1. The materialized view cannot contain object types or Oracle-supplied types.
2. The base tables will never have any distributed transactions applied to them.

Materialized view allows to copy the object from Remote Data base, so we can do the replication using the materialized view.

Oracle forms 11g Admin server as a windows service

Oracle forms 11g

1. Admin server as a winservice

Step: 1

Take a backup copy of installSvc.cmd and save it as “classicdomain12_Adminserver_installSvc.cmd” which is available in the below location.

F:\Weblogic_MW_Home\wlserver_10.3\server\bin

Save the file as “classicdomain12_Adminserver_installSvc.cmd” in the same location.

Edit the newly created file

Add the below lines

>> 

set WL_HOME=F:\Weblogic_MW_Home\wlserver_10.3

set MYDOMAIN=F:\Weblogic_MW_Home\user_projects\domains\ClassicDomain12

call "%MYDOMAIN%\bin\setDomainEnv.cmd"

After the modification it will look like as below..

>> 

set WL_HOME=F:\Weblogic_MW_Home\wlserver_10.3

call "%WL_HOME%\common\bin\commEnv.cmd"

set MYDOMAIN=F:\Weblogic_MW_Home\user_projects\domains\ClassicDomain12

call "%MYDOMAIN%\bin\setDomainEnv.cmd"

Modify the below entry beasvc service entry in the same file “classicdomain12_Adminserver_installSvc.cmd”

Before Modification

rem *** Install the service

"%WL_HOME%\server\bin\beasvc" -install -svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%" -javahome:"%JAVA_HOME%" -execdir:"%USERDOMAIN_HOME%" -maxconnectretries:"%MAX_CONNECT_RETRIES%" -host:"%HOST%" -port:"%PORT%" -extrapath:"%EXTRAPATH%" -password:"%WLS_PW%" -cmdline:%CMDLINE%

After Modification

rem *** Install the service

"%WL_HOME%\server\bin\beasvc" -install -debug -log:"C:\Temp\stdout.txt" -svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%" -javahome:"%JAVA_HOME%" -execdir:"%USERDOMAIN_HOME%" -maxconnectretries:"%MAX_CONNECT_RETRIES%" -host:"%HOST%" -port:"%PORT%" -extrapath:"%EXTRAPATH%" -password:"%WLS_PW%" -cmdline:%CMDLINE%

Note : we can give the required log filename and respective path for our convenience

Step: 2

Create an install batch script that contains the domain, server details as below and save as a .cmd  like “createAdminserver.cmd” in “F:\Weblogic_MW_Home\user_projects\domains\ClassicDomain12\”

>> 

echo off

SETLOCAL

set DOMAIN_NAME=ClassicDomain12

set USERDOMAIN_HOME=F:\Weblogic_MW_Home\user_projects\domains\ClassicDomain12

set SERVER_NAME=AdminServer

set WL_HOME=F:\Weblogic_MW_Home\wlserver_10.3

set PRODUCTION_MODE=true

set WLS_USER=weblogic

set WLS_PW=xxxxxxxxxx

set MEM_ARGS=-Xms40m -Xmx250m

cd %USERDOMAIN_HOME%

call "F:\Weblogic_MW_Home\wlserver_10.3\server\bin\classicdomain12_Adminserver_installSvc.cmd"

ENDLOCAL 

Find out the missing table in all schema's query

select * from all_tables where table_name like '%XYZ%';

select * from recyclebin where original_name like '%XYZ%';