Tag Archives: delete

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

Managed Stored Scripts in RMAN

Managed Stored Scripts in RMAN

We can create Scripts for RMAN backup and restore operation for better and easy way to use. Scripts can be local or global.
Local Script is linked with target database and global script is registered in recovery catalog so that every database can use it from catalog.
 
Create Stored RMAN Scripts
Create Script command is used to create the stored script and stored in target database. If mention Global, then it stored in recovery catalog.

1. Connect RMAN to target database and recovery catalog

— create a full backup script used default setting of show all command.
Create script full_backup
(
crosscheck archivelog all;
Backup database plus archivelog;
)–you can create global script with following syntax:
create global script full_backup_global
(
Backup database plus archivelog;
)

— Create script from file
Create script full_backup from file ‘C:\script\rmanbackup.txt’;

 
Replace Stored Scripts
Replace script command is used to modified the existing script present in target/catalog database.

— Only database backup
Replace script full_backup
(
Backup database;
)

 
Execute Stored RMAN Script
Execute script is command to run the script on RMAN prompt.

Run
{
EXECUTE SCRIPT full_backup;
}
–For global script:
RUN
{
EXECUTE GLOBAL SCRIPT full_backup_global;
}

 
List Stored RMAN Script
List script command to display the name fo script present in database.

— List all the script from connected target database
LIST SCRIPT NAMES;
— List global script
LIST GLOBAL SCRIPT NAMES;

–LIST ALL SCRIPT
LIST ALL SCRIPT NAMES;

 
Print Stored RMAN Scripts
PRINT SCRIPT Command is used for display the content of script.

— Print the local script
PRINT SCRIPT full_backup;
— Pring the global script
PRINT GLOBAL SCRIPT full_backup_global;

— Pring the script to text file
PRINT SCRIPT full_back TO FILE ‘C:\scripts\fullbackup.txt’;

 
Delete RMAN Stored script
Delete command is used to delete the stored script in catalog/target database

DELETE SCRIPT ‘full_backup’;
DELETE GLOBAL SCRIPT ‘full_backup_global’;

 
Execute script direct from OS command prompt

Rman target / catalog rman@catdb script ‘C:\script\fullbackup.txt’;

 

Secure password in Scripts using Wallet feature of Oracle

Secure password in Scripts using Wallet feature of Oracle

1. Configure the Wallet and set the password for wallet:

Mkstore –wrl "wallet location" -create

Example:

Mkstore  -wrl E:\oracle\wallet –create

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:

 2. Secure the password of user which you want to use in script in wallet.

mkstore –wrl "wallet location" -createCredential

db_connection_string is an entry on your tnsnames.ora or any service name to connect to the desired database.
 
Example:

mkstore –wrl     E:\oracle\wallet  -createCredential  prod   username   password

Note:prod is an entry on your tnsnames.ora file

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -createCredential prod sys
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1

 3. Adding the following lines in sqlnet.ora for mentioned the location of wallet.

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD= FILE)
(METHOD_DATA = (DIRECTORY=E:\oracle\wallet)
))

 4. You can list the credential you set in wallet.

Mkstore –wrl E:\oracle\wallet -listcredential

Example:

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:   l
List credential (index: connect_string username)
1: prod sys

5. Delete the added credential from the wallet

Mkstore –wrl "wallet location" –deleteCredential

Example:

mkstore -wrl E:\oracle\wallet -deleteCredential prod

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -deleteCredential prod
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Delete credential
Delete 1

 6. Delete the wallet created for use

mkstore -wrl “wallet location”  -delete

Example

mkstore -wrl E:\oracle\wallet –delete

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -delete
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:

 7. Check wallet is deleted or not

mkstore -wrl E:\oracle\wallet –list
Use the wallet user in scripts as follows
Sqlplus /@prod

DBMS SERVICE package for managed service in single instance oracle

DBMS_SERVICE package for managed service in oracle

In Oracle, you can also create service on database through package.

For creation of Service:

exec dbms_service.create_service ( service_name => 'SERVICE1' , network_name => 'SERVICE1');

Check the service status:

select  service_id , name, network_name,creation_Date from dba_services;

Start the service:

exec dbms_service.start_Service('SERVICE1');

Stop the service:

exec dbms_service.stop_service('SERVICE1');

Delete the service:
Service must be stopped before deletion command.

exec dbms_service.delete_service('SERVICE1');

Disconnect the session from service:

exec dbms_service.disconnect_session('SERVICE1');