for the beginners and laterals . . .

Sunday, January 23, 2011

You can check through following ways

Check in v$parameter view

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

SHOW PARAMETER SPFILE.

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

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.

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

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

insert ....

commit;

END;

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.

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.

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.

Oracle automatically acquires locks which are row-level locks. A transaction can held no of row locks and oracle does not work on it.
Row locking provides the lowest level of locking. It provides the best possible transaction concurrency.Readers of data do not wait for writers of the same data rows.
A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
A Transaction gets an exclusive DML row level lock for each row modified by any of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.