An article : How to Use Oracle VM VirtualBox Templates
.
.
.
Published with Blogger-droid v2.0.6
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
24 July, 2012
How to use Oracle Virtual Box templates
11 July, 2012
ON COMMIT Refresh without a Primary Key
Today's question : How can you build an ON COMMIT Refresh Materialized View for a table that has no Primary Key ?
.
.
.
09 July, 2012
Materialized View Refresh ON COMMIT
A quick demonstration of a Materialized View that is refreshed ON COMMIT.
NOTE : This blog post has been updated after Yuri pointed out an error.
.
NOTE : This blog post has been updated after Yuri pointed out an error.
SQL> create table source_table
2 as select rownum as source_pk, dbms_random.string('X',15) as col_2, sysdate-1000+rownum as col_3
3 from dual connect by level < 1001
4 /
Table created.
SQL>
SQL> alter table source_table add constraint source_pk primary key (source_pk);
Table altered.
SQL>
SQL> create materialized view log on source_table ;
Materialized view log created.
SQL>
SQL> create materialized view my_mv refresh on commit
2 as select source_pk as key_col, col_3 as data_col from source_table
3 /
Materialized view created.
SQL>
SQL> select count(*) from source_table;
COUNT(*)
----------
1000
SQL> select count(*) from my_mv;
COUNT(*)
----------
1000
SQL>
SQL> exec dbms_session.session_trace_enable;
PL/SQL procedure successfully completed.
SQL> insert into source_table values (5001,'XXXXX',sysdate+50);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_session.session_trace_disable;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from my_mv where key_col=5001;
KEY_COL DATA_COL
---------- ---------
5001 03-OCT-12
SQL>
The trace file shows, among other SQLs :
INSERT /*+ IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABLE" (dmltype$$,old_new$$,
snaptime$$,change_vector$$,xid$$,"SOURCE_PK")
VALUES
(:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 1 2 29 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 1 2 29 1
DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
"KEY_COL" IN (SELECT DISTINCT LOG$."KEY_COL" FROM (SELECT MLOG$."SOURCE_PK"
"KEY_COL" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1
AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."KEY_COL") NOT IN (SELECT
MAS_TAB$."SOURCE_PK" "KEY_COL" FROM "SOURCE_TABLE" "MAS_TAB$" WHERE
LOG$."KEY_COL" = MAS_TAB$."SOURCE_PK"))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 10 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 10 0 0
MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."KEY_COL",
CURRENT$."DATA_COL" FROM (SELECT "SOURCE_TABLE"."SOURCE_PK" "KEY_COL",
"SOURCE_TABLE"."COL_3" "DATA_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE")
CURRENT$, (SELECT DISTINCT MLOG$."SOURCE_PK" "KEY_COL" FROM
"HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1 AND
("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."KEY_COL" = LOG$."KEY_COL")"AV$"
ON ("SNA$"."KEY_COL" = "AV$"."KEY_COL") WHEN MATCHED THEN UPDATE SET
"SNA$"."KEY_COL" = "AV$"."KEY_COL","SNA$"."DATA_COL" = "AV$"."DATA_COL"
WHEN NOT MATCHED THEN INSERT (SNA$."KEY_COL",SNA$."DATA_COL") VALUES
(AV$."KEY_COL",AV$."DATA_COL")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 1 33 26 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 1 33 26 1
delete from "HEMANT"."MLOG$_SOURCE_TABLE"
where
xid$$ = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 7 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 7 2 1
.
05 July, 2012
An Oracle Installer that automatically switches to Console mode
The WebLogic installer can switch automatically to console mode.
UPDATE : However, quickstart.sh still requires an X-display as I discovered after the install completed.
UPDATE : However, quickstart.sh still requires an X-display as I discovered after the install completed.
-sh-3.2$ echo $HOME
/newfs/wlogic
-sh-3.2$ ./wls1211_linux32.bin
Extracting 0%....................................................................................................100%
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified
Unable to instantiate GUI, defaulting to console mode.
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Welcome:
--------
This installer will guide you through the installation of WebLogic 12.1.1.0. Type "Next" or enter
to proceed to the next prompt. If you want to change data entered previously, type "Previous".
You may quit the installer at any time by typing "Exit".
Enter [Exit][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Choose Middleware Home Directory:
---------------------------------
"Middleware Home" = [Enter new value or use default "/newfs/wlogic/Oracle/Middleware"]
Enter new Middleware Home OR [Exit][Previous][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Register for Security Updates:
------------------------------
Provide your email address for security updates and to initiate configuration manager.
1|Email:[]
2|Support Password:[]
3|Receive Security Update:[Yes]
Enter index number to select OR [Exit][Previous][Next]> 3
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Register for Security Updates:
------------------------------
Provide your email address for security updates and to initiate configuration manager.
"Receive Security Update:" = [Enter new value or use default "Yes"]
Enter [Yes][No]? No
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Register for Security Updates:
------------------------------
Provide your email address for security updates and to initiate configuration manager.
"Receive Security Update:" = [Enter new value or use default "Yes"]
** Do you wish to bypass initiation of the configuration manager and
** remain uninformed of critical security issues in your configuration?
Enter [Yes][No]? Yes
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Register for Security Updates:
------------------------------
Provide your email address for security updates and to initiate configuration manager.
1|Email:[]
2|Support Password:[]
3|Receive Security Update:[No]
Enter index number to select OR [Exit][Previous][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Register for Security Updates:
------------------------------
Provide your email address for security updates and to initiate configuration manager.
1|Email:[]
2|Support Password:[]
3|Receive Security Update:[No]
Enter index number to select OR [Exit][Previous][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Choose Install Type:
--------------------
Select the type of installation you wish to perform.
->1|Typical
| Install the following product(s) and component(s):
| - WebLogic Server
| - Oracle Coherence
2|Custom
| Choose software products and components to install and perform optional configuration.
Enter index number to select OR [Exit][Previous][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Choose Product Installation Directories:
----------------------------------------
Middleware Home Directory: [/newfs/wlogic/Oracle/Middleware]
Product Installation Directories:
1|WebLogic Server: [/newfs/wlogic/Oracle/Middleware/wlserver_12.1]
2|Oracle Coherence: [/newfs/wlogic/Oracle/Middleware/coherence_3.7]
Enter index number to select OR [Exit][Previous][Next]>
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
The following Products and JDKs will be installed:
--------------------------------------------------
WebLogic Platform 12.1.1.0
|_____WebLogic Server
| |_____Core Application Server
| |_____Administration Console
| |_____Configuration Wizard and Upgrade Framework
| |_____Web 2.0 HTTP Pub-Sub Server
| |_____WebLogic SCA
| |_____WebLogic JDBC Drivers
| |_____Third Party JDBC Drivers
| |_____WebLogic Server Clients
| |_____Xquery Support
| |_____Evaluation Database
|_____Oracle Coherence
| |_____Coherence Product Files
|_____JDKs
|_____SUN SDK 1.6.0_29
|_____Oracle JRockit 1.6.0_29 SDK
*Estimated size of installation: 1,178.0 MB
Enter [Exit][Previous][Next]>
Jul 5, 2012 3:23:23 AM java.util.prefs.FileSystemPreferences$2 run
INFO: Created user preferences directory.
<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->
Installing files..
0% 25% 50% 75% 100%
[------------|------------|------------|------------]
[******************
Cool. Isn't it ?
Subscribe to:
Posts (Atom)