Oracle_Dba

Increasing Session and Process on Database session=1.5*process implement the parameter changes : Parameter               Value   old Value sessions                   500       335 processes                 450      300 sqlnet.ora to include  SQLNET.INBOUND_CONNECT_TIMEOUT=0  parameter. [The process will wait and there will be no connection time out] Implementation Steps: ALTER SYSTEM SET SESSIONS=500 SCOPE=BOTH; ALTER SYSTEM SET PROCESSES=450 SCOPE=BOTH; And We have to Bounce the database;

Hi I am going to present you how to take export/import backup using datapump. CREATE DIRECTORY EXP_PRD AS  '/export_backup'; sqlplus / as sysdba set lines 200 pages 200 select name, open_mode, host_name from v$database, v$instance; column owner format a10 column DIRECTORY_PATH format a80 select * from dba_directories;   YOU SHOULD BE ABLE TO SEE THE DIRECTORY(LOGICAL) you created.   CREATE A PAR FILE.   PRODMETAIMP.par DIRECTORY=EXPIMP DUMPFILE=prdmetexp%U.dmp FILESIZE=5G LOGFILE=prdmetaimp.log JOB_NAME=PRDMETAIMP SCHEMAS=GAKHAN CONTENT=METADATA_ONLY PARALLEL=4 __________________________________ PRE IMPORT TASK ------------------------------------------------------- Select distinct TABLESPACE_NAME from dba_segments where owner='GAKHAN' ORDER BY 1; Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set lines 200 pages 200 S

COMPRESSION parameter in expdp One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them by using parameter COMPRESSION in the expdp command line. The parameter has three options: METDATA_ONLY - only the metadata is compressed, This is the default setting DATA_ONLY - only the data is compressed; the metadata is left alone. ALL - both the metadata and data are compressed. NONE - this is the default; no compression is performed. Datapump in 11g has a good feature to reduce size of exports and resources used on machines and tapes by compressing the dumps as and when the export happens. In version 10g, datapump did not have the compress option for data – only the default option to compress METADATA only. Example in 10g - TEST schema using datapump - expdp system/xxxxxx schemas=test directory=data_pump_dir dumpfile=test.dmp logfile=test.lo

Oracle Locks User must request a resource (i.e row,table,index etc) before using it and must release that resource after using it. Without following above approach there would be collision among user requests which result data to be in inconsistent.To avoid data inconsistency a lock mechanisim adopted within different database,Every database has its own locking mechanism. Oracle uses two modes in a multiuser database either its on table or row Exclusive lock mode Exclusive lock mode never allowed the associates resource from being shared.This lock mode is obtained to modify data.Only one exclusive lock can be placed on a resource (such as a row or a table). i.e From one session i acquire the exclusive lock on resource (emp table) Session a SQL> lock table emp in exclusive mode nowait   2   / Table(s) Locked. Note : The optional keyword NOWAIT tells Oracle not to wait for a table if it has been locked by another user. From another session i try to acqu

Buffer Cache Hit Ratio When you query v$sysstat you will find the below as described. Consistent gets - number of the data blocks which were accessed in the buffer cache for SQL statements that do not modify data - just SELECT statements DB block gets - number of the data blocks which were accessed in the buffer cache for SQL statements that modify data - INSERT, UPDATE, DELETE and SELECT FOR UPDATE statements; Physical reads - number of the data blocks that where read from the disk. Script to find Buffer Cache Hit Ratio SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Buffer Cache Hit Ratio Is" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads'; O/P Buffer Cache Hit Ratio Is -------------------------                     99.08