Tag Archives: update

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Advertisements

Select, Update, Insert AND Delete Operations on a Table

SELECT STATEMENT:
SELECTING ALL COLUMNS OF THE TABLE:
A ‘SELECT’ statement is used as a DATA RETRIEVAL statement i.e. It retrieves information from the database.

SYNTAX:
SQL> SELECT * FROM TABLE NAME;
• SELECT identifies WHAT COLUMNS.
• FROM identifies WHICH TABLE.
Simply, SELECT clause specify which column is to be displayed & FROM clause specify the table containing the columns listed in the SELECT clause.
Here, ‘*’ is used to select all columns.

SELECTING SPECIFIC COLUMNS OF THE TABLE:
SYNTAX:
SQL> SELECT ENAME,JOB FROM EMP
 
SELECTING DISTINCT ELEMENTS FROM THE TABLE:
SYNTAX:
SQL> SELECT DISTINCT ENAME,JOB FROM EMP
The SELECT DISTINCT statement is used to return only distinct (different) values.It is used to remove duplicate values.

SQL> select * from empxyz;

NAME                   AGE
----------------------- ------
anju                    23
jkg                     34
anju                    23

SQL> select distinct name,age from empxyz;

NAME                    AGE
----------------------- -------
anju                    23
jkg                     34


INSERT STATEMENT:
SYNTAX:
SQL> INSERT into CSE(student,rollno) VALUES ('MONIKA',651);
INSERT statement is used to ADD NEW ROW TO A TABLE.
Using INSERT We can only insert on row at a time. As shown in above example,
In above example CSE is the name of the TABLE & STUDENT, ROLLNO are its two ATTRIBUTES.
Enclose CHARACTER & DATE values within a SINGLE QUOTATION MARKS.
 
DELETE STATEMENT:
SYNTAX:
SQL> DELETE from CSE where rollno BETWEEN 605 AND 630;
i.e. DELETE FROM table [WHERE condition];
If we OMIT WHERE CLAUSE then ALL ROWS OF THE COLUMN ARE DELETED.

UPDATE STATEMENT:
SYNTAX:
SQL> UPDATE cse SET rollno=21 WHERE student='ITIKA';
Here, If we do not use WHERE clause then ALL ROWS OF THE TABLE ARE UPDATED.
SPCIFIED ROW or ROWS are modified if we specify the WHERE clause

Download latest Opatch (patch 6880880) from oracle support

Download latest Opatch (patch 6880880) from oracle support.

  1. Login to oracle support site https://support.oracle.com
  2. Go to tab    —> patches and updates
  3. Go to patches search window and type patch number : 6880880
  4. select platform type like windows or linux
  5. Press the search button.

patchsearch



6. After search button you will found the list of all patches available for different version of oracle. Please download as required.

Patchresult


msdtc.exe is causing lock/active on windows system

msdtc.exe is causing lock/active on windows system.

Issue: If you need to update any application on windows sometime we face the issue with active/locked file by windows services or process.

Solution:

C:\windows>tasklist /m oci*

Image Name     PID      Modules
============   ======   ========
explorer.exe   3136     oci.dll
vmtoolsd.exe   36108    oci.dll
msdtc.exe      6932     oci.dll


If you kill the PID process for msdtc.exe , it open again as shown below:

taskkill /pid 6932 /F
SUCCESS: The process with PID 6932 has been terminated.

C:\windows>tasklist /m oci*

Image Name     PID     Modules
============   =====   ========
explorer.exe   3136    oci.dll
vmtoolsd.exe   36108   oci.dll
msdtc.exe      23492   oci.dll


So, for fix it we need to stop the services for msdtc.exe process and disable it for the duration of our activity and then start it. Open the Services.msc of microsoft windows and find the following service and stopped or disabled it for during the activity, then start it.

Service name is Distributed Transaction Coordinator

6.JPG