Sunday, January 23, 2011

Explain the Perfomance Tuning.


  • It is the Improved throughput in OLTP System or decrease response time in datawarehouse(DWH). It s like that how fast we get output of a query/transaction/page/form from one to another.
  • Tuning can be applied at
    Query level /Object level / Database level /Application level / System level
  • Tuning is mainly responsible for   
    Database designer, Database developer, Database administrator(DBA), Application developer, etc.

How to send email from 10g Oracle Database (UTL_MAIL)?

To enable mailing from database using below steps.
1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora

alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;

Use default SMTP Port as 25.
If database instance had been started with spfile

alter system set smtp_out_server = ’10.10.10.10:25′ scope=both;

If database instance had been started with pfile


alter system set smtp_out_server = ’10.10.10.10:25′;


Also make below entry in your initSID.ora 
smtp_out_server = ’10.10.10.10:25′

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

grant execute on utl_mail to USERS;

Now,database configuration finished.
To send email, use below code in your procedure/package,

exec utl_mail.send((sender => ‘abc@abc.com’, recipients => ‘xyz@xyz.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘TESTMAIL USING  UTL_MAIL PACKAGE.’);
 
Check the inbox of the email id, to verify the email received or not.

How I can find a running Oracle database is started using PFILE or SPFILE?


You can check through following ways

Check in v$parameter view

Select * from v$parameter where  upper(name)  like ‘SPFILE’;                              
or
SHOW PARAMETER SPFILE. 


If you find any data in above query then it started using SPFILE else with PFILE.

Wednesday, January 19, 2011

What is Oracle Data Pump (impdp and expdp)?

  • Oracle Data Pump is a new feature of Oracle Database 10 g that provides high speed, parallel, bulk data and metadata movement of Oracle database contents.
  • DBMS_DATAPUMP package  provides a server-side infrastructure for fast data and metadata movement. expdp and impdp is used to import/export activites.
  • It vastly improved performance and greatly enhanced functionality, such as restartability, flexible object selection, and better monitoring and control of export and import jobs.
  • Data Pump is server-based, rather than client-based. Dump files, log files, and SQL files are accessed relative to server-based directory paths, so that appropriate file security can be enforced.
  • Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory name on the file system.
  • Before you can run Data Pump Export or Data Pump Import, a directory object must be created by a DBA or by any user with CREATE ANY DIRECTORY privilege. 

Friday, December 31, 2010

What is autonomous transaction in Oracle?

  • An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction).
  • An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction. 
  • The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues. 
  • An autonomous transactions is available from Oracle 8i. 
  • An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
  • This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block. 
  • Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.
      CREATE OR REPLACE PROCEDURE test
      IS
             PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
             insert ....
             commit;
      END;

What are ORACLE PRE-COMPILERS?

  • Using ORACLE PRE-COMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. 
  • The Precompilers are known as Pro*C,Pro*Cobol,This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. 
  • The pre-compiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to create an executable.

How you can coalesce free space in oracle?

  • SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time. 
  • SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. 
  • With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE tblspace_temp COALESCE; command, until then use 
          SQL> alter session set events 'immediate trace name coalesce level n'
          Where 'n' is the tablespace number.
          SELECT TS#, NAME FROM SYS.TS$;
  • You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.