Friday, January 16, 2009

Oracle Apps DBA Interview Questions Links

at 10 comments:
Labels:

Wednesday, January 14, 2009

Job Scheduling in Oracle (dba_jobs)

Job Scheduling in Oracle (dba_jobs)

Solution:

Tables Used:

dba_jobs
dba_jobs_running
job_queue_processes - init parameter
Submitting a job

--exec dbms_job.submit('', , , TRUE);--

Remove a job

--exec dbms_job.remove()--

Reset a broken job:

--exec dbms_job.broken(, FALSE);--

Killing a Oracle DBMS_JOB

Viewing scheduled dbms_jobs

When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;


What Jobs are Actually Running

A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.

running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs

Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.

session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.

As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.

Source : it-tool-box/blogs

Troubleshooting with DBMS_JOBS

Metalink Note : 313102.1
at 1 comment:
Labels:

Upgrade JDK to Latest Java 6.0 in R12

Upgrade JDK to Latest Java 6.0 in R12

Solution:

Oracle Note : 455492.1

Upgrade Oracle E-Business Suite Release 12 JDK to Java 6.0 latest update
You can upgrade Java 6.0 after finishing 10.1.3.3 PS upgrade

Steps:

a) Download Latest Update of JDK 6.0 (32 bit)
http://java.sun.com/javase/downloads/index.jsp
Java SE Development Kit (JDK) 6 Update 10

b) Stop the FE services, if not down already

c) install JDK on FE code tree $IAS_ORACLE_HOME/appsutil/jdk

. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
cd $IAS_ORACLE_HOME/appsutil/

Install the downloaded latest update of JDK 6.0 here, i.e., [IAS_ORACLE_HOME]/appsutil

chmod +x jdk-6u10-linux-i586.bin
./jdk-6u10-linux-i586.bin
ln -sf jdk1.6.0_10 jdk

d) Restart FE Services

e) Download Latest Update of JRE 6.0 for DB Nodes (32 bit)
http://www.hp.com/products1/unix/java/java2/jdkjre6_0/index.html

download the tar version
gunzip jre6_16002_ia.tar.Z

NOTE: Need to check the below OS Patches are there or not?
http://docs.hp.com/en/JDKJRE60RN/jdk_rnotes_6.0.02.html#installation

The Java 5.0 Quality Pack patches must be installed
Linker patch PHSS_37201 for 11.23 systems (This patch fixes a problem loading libjli.sl)


f) Install the jre on DB nodes $ORACLE_HOME/appsutil/jre
cd $ORACLE_HOME/appsutil/
tar -xvf jre6_16002_ia.tar

NOTE: to fix java -version error export SHLIB_PATH=$SHLIB_PATH:$ORACLE_HOME/appsutil/jre/lib/PA_RISC2.0/jli

g) verify the upgraded configurations on all fe nodes...as in readme

$ADJVAPRG -version
$AFJVAPRG -version
submit a conc req

http://test-inst-09.cisco.com:8118/OA_HTML/JDKtest.jsp
at No comments:
Labels:

Upgradation of 10g Application Server Home (10.1.3 Home to 10.1.3.3) in R12

Upgradation of 10g Application Server Home (10.1.3 Home to 10.1.3.3) in R12

Solution:

Oracle Note : 454811.1

Upgrading Oracle E-Business Suite Release 12 10.1.3 Oracle Home to Oracle 10gAS Patch Set 3 (10.1.3.3)

Steps:

To check Oracle Home Version : $ORACLE_HOME/config/ias.properties

1) make sure the FE services are up and running
$INST_TOP/admin/scripts/adopmnctl.sh status


2) 6148874
========

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that make,ar,ld,nm are in $PATH

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/COEPCH_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

Apply the above patch through runinstaller on all the non shared FS's

unzip the patch under dir 6148874
export DISPLAY=local_host:0.0
cd 6148874/Disk1
./runInstaller -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

Enter the user password 'secret' if default one was not changed..

3. Run root.sh

4. shutdown any services started by above installation
$INST_TOP/admin/scripts/adstpall.sh

5. Run autoconfig on all application tier nodes after 10.1.3.3 patch set installtion.

6. 6655812
=======
Apply SSO-OID Patch

Unzip the downloaded zip file into a folder
set the environment to 10.1.3

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that make,ar,ld,nm are in $PATH

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TEST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

Apply the above patch through runinstaller on all the non shared FS's

unzip the patch under dir 6655812
export DISPLAY=local_host:0.0
cd 6655812/Disk1
./runInstaller -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

During installation, OUI may ask you to choose option 'Yes/No' in "Register Oracle Application Server Metadata Repository" OUI screen.
Choose 'No' and proceed further.

shutdown any services started by above installation

7. Run autoconfig on all the application tier nodes after 10.1.3.3 SSO-OID patch installation.

8. Apply the below additional patches through opatch:
6311835
5919967

6311835
=======
set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/COEPCH/inst/apps/TEST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

cd 6311835
/apps/test_inst/PATCHES/2617419/OPatch/opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc

apply the patch with optach 2617419 version to avoid below error
------------------------error ------------------------------
Invoking fuser to check for active processes.

OPatch detects your platform as 46 while this patch 6311835 supports platforms:
0 (Generic Platform)

This patch is not suitable for this operating system.
Please contact support for the correct patch.

ERROR: OPatch failed during pre-reqs check.

# Workaround: Apply the fix for OPatch bug 2617419 from Oracle Metalink at
# http://metalink.oracle.com. Then other Oracle SES one-off patches can be
# applied using OPatch.

FIX:
apply patch with opatch 2617419 version
--------------------------------------------------------------------

5919967
=======

set the env to 10.1.3 home
. $INST_TOP/ora/10.1.3/TEST_INST_test-inst-09.env
echo $ORACLE_HOME
should point to ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.3
export PATH=$PATH:$ORACLE_HOME/OPatch

make sure that /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TST_INST_test-inst-09/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc
Make sure the output is correct. This should not error out.

cd 5919967
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.3/oraInst.loc


9) Generate product JAR files with no force option from adadmin

10) verify patch application
Invoke runInstaller on Unix and Linux or setup.exe on the Windows platform.
Click on Installed products.
Select the 10.1.3 Oracle Home and expand the tree. You will see Oracle Application Server Patchset 10.1.3.3 and 10.1.3.3 SSO-OID patch installed in the list.
at No comments:
Labels:

Upgradation of Forms and Reports Oracle Home(10g) in R12

Upgradation of Forms and Reports Oracle Home(10g) in R12

Solution:

Metalink Note :

437878.1 Upgrading Forms and Reports 10g in Oracle Applications Release 12:

Steps:

1) Stop all services and take backup of DB and all code tree's with inventory.
2) Let us Say Form and Report OH is /apps/test_inst/tech_stat/10.1.2
3) make sure that /apps/test_inst/tech_stat/10.1.2/oraInst.loc is pointing to the right inventory location (/var/opt/oracle/oraInst.loc)
4) Apply patch 4960210 through Runinstaller ..
5) export DISPLAY=local_host:0.0
6) cd Disk1 (If you unzip the patch, you will find it)
./runinstaller (or)
./runInstaller -invPtrLoc /apps/test_inst/tech_stat/10.1.2/oraInst.loc
7)check the installed products

click next

enter the full path of the products to be installed
/apps/test_inst/PATCHES/Disk1/stage/products.xml

select destination oracle home
apps_test_inst_apps_apps_tech_st_10_1_2

path
/apps/test_inst/apps/tech_st/10.1.2/

click next

select product to update Oracle Application Server Software Update
choose "Oracle Application Server and Developer Suite 10g R2 Software Update 10.1.2.2.0"

Supply the ias_admin default password 'secret' when it prompts.

8. run root.sh when it prompts.
9. post patch:
Update File Permissions
chmod ug+x $ORACLE_HOME/sso/bin/ssoreg.sh

10. Deploy new forms.ear file as per Note 397174.1
----------------------------------------------

cp $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml_org_10152008

vi $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

oc4jadmin
OC4J Administrator
OC4J Administrator
{903}7pt3cBV4AkL6tlFqYHmHdhHbrTpDcTS8


oc4jadmin
OC4J Administrator
OC4J Administrator
!welcome


$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
Enter Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? welcome
Run Autoconfig ? No

NOTE: mention the password as welcome only.

restore back the xml files for previous password

cp $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml_org_10152008 $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

Run autoconfig on all the nodes..

11.Apply patch 5861907

a) Shutdown the iAS instance.
b) Backup the $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml file.
c) Copy the patch oraclehomeproperties.xml to $ORACLE_HOME/inventory/ContentsXML.

12) Apply the below additional patches in same order

5841985
6002686
5985861
6146823
5637184
5466491


5841985
=======
export PATH=$PATH:/apps/test_inst/apps/tech_st/10.1.2/OPatch
export ORACLE_HOME=/apps/test_inst/apps/tech_st/10.1.2

make sure that /apps/tst_inst/apps/tech_st/10.1.2/oraInst.loc is pointing to the right inventory location
(/apps/local/TEST_INST/inst/apps/TEST_INST_coe-dev-17/admin/oraInventory)
If no inventory exists under $INST_TOP/admin then copy the central inventory to this location.

make sure that JRE_LIB_DIR in /apps/test_inst/apps/tech_st/10.1.2/sysman/lib/env_sysman.mk is pointing to the correct location

opatch lsinventory -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc
Make sure the output is correct. This should not error out.

cd /apps/test_inst/PATCHES/5841985
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc


6002686
=======
cd /apps/test_inst/PATCHES/6002686
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

5985861
=======
cd /apps/test_inst/PATCHES/5985861
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

6146823
=======
pre req

echo $OPATCH_PLATFORM_ID
export OPATCH_PLATFORM_ID=46
chmod +X $ORACLE_HOME/bin/genshlib
unzip p6146823_101220_Linux.zip
cd 6146823

sh apply.sh | tee 6146823_apply.log

Note: This will apply all these patches (5456500,5527100,5650051,5753922,5893392 and 5985840)

unset OPATCH_PLATFORM_ID

5637184
=======

cd /apps/test_inst/PATCHES/5637184
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc

5466491
=======

cd /apps/test_inst/PATCHES/5466491
opatch apply -invPtrLoc /apps/test_inst/apps/tech_st/10.1.2/oraInst.loc


13. Apply interoper patch 5411711

using adpatch


14.Regenerate Jar files if the time stamp is not current in $OA_JAVA/oracle/apps/fnd/jar directory

Using adadmin

select Generate Applications Files > Generate Product JAR Files
(Do not force the regeneration of all JAR files)

15. Start services and verify the upgrade.

a) Log on to Applications as any user, launch any form, select About Applications from the Help menu,
and confirm that the Forms version (under Forms Server) is 10.1.2.2.0.

b) Execute following command,to display various component versions.
This command requires DISPLAY environment variable to be set correctly.

$ORACLE_HOME/bin/frmcmp help=y
at 1 comment:
Labels: ,

parallelism in Oracle?

Complete about Oracle Parallelism?

Solution:

Following initialization parameters are required for parallelism setup in database.

PARALLEL_SERVER,
PARALLEL_SERVER_INSTANCES,
PARALLEL_MIN_SERVERS,
PARALLEL_MAX_SERVERS?
PARALLEL_THREADS_PER_CPU

Parallel Queries and Parallel jobs execution

1. Select query with parallelism example:

Select /*+ parallel (a,32) */ * from dba_segments a;

32 is degreee here(Numbers of parallel processes)

Gather statistics of a table using parallelism Example

exec FND_STATS.GATHER_TABLE_STATS (ownname => '&owner', tabname => '&table_name', percent => 20 ,degree => 30 , granularity => 'ALL', cascade => TRUE);

Comple objects in the database with parallelism Example

exec sys.utl_recomp.recomp_parallel(32);

Table used to know number of parallel processes running are

v$px_session - Standard
gv$px_session - For RAC
at No comments:
Labels: , ,

Difference between DROP,Truncate and Delete in Oracle?

Difference between Truncate and Delete in Oracle?

Answer:

1.TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

2. DELETE is a DML command and can be rolled back.

3. TRUNCATE : You can't use WHERE clause and DELETE : You can use WHERE clause

4. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

5.Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.Delete: You can keep object's statistics and all allocated space.

6. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

7. Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.

8. Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

9. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

10. Truncate will not use the undo TBS, whereas a delete will.
at No comments:
Labels: ,

What is High Water Mark in Oracle?

What is High Water Mark in Oracle?

Answer:

1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation won't reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

I hope now you understand well. see the below example.

for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.


Explanation in Detail:
http://asktom.oracle.com/pls/asktomf?p=100:11:0::::P11_QUESTION_ID:492636200346818072
at No comments:
Labels: ,
Subscribe to: Posts (Atom)

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...