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.

What is the need of rebuilding of an index?

          ANALYZE INDEX  INDEX_NAME VALIDATE STRUCTURE;
  • Using above statement we can rebuild a Index.
  • Each command creates a single row in the V$INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. 
  • The 'badness' of the index can then be identified DEL_LF_ROWS/LF_ROWS.