How to calculate the bandwidth required between Primary and Standby Database for Archive log shipping?

Below is the detailed explaination for calculation of bandwith between primary and standby database.

The formula used (assuming a conservative TCP/IP network overhead of 30%) for calculating the network bandwidth is :
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Measuring the Peak Redo Rate
Use the Oracle Statspack utility for an accurate measurement of the redo rate.
Based on your business you should have a good idea as to what your peak periods of normal business activity are. For example, you may be running an online store which historically sees the peak activity for 4 hours every Monday between 10:00 am - 2:00 pm. Or, you may be running a merchandising database which batch-loads a new catalog every Thursday for 2 hours between 1 am - 3 am. Note that we say "normal" business activity - this means that in certain days of the year you may witness much heavier business volume than usual, e.g. the 2-3 days before Mother's Day or Valentine's Day for an online florist business. Just for those days, perhaps you may allocate higher bandwidth than usual, and you may not consider those as "normal" business activity.However, if such periodic surges of traffic are regularly expected as part of your business operations, you must consider them in your redo rate calculation.

During the peak duration of your business, run a Statspack snapshot at periodic intervals. For example, you may run it three times during your peak hours, each time for a five-minute duration. The Statspack snapshot report will include a "Redo size" line under the "Load Profile" section near the beginning of the report. This line includes the "Per Second" and "Per Transaction" measurements for the redo size in bytes during the snapshot interval. Make a note of the "Per Second" value. Take the highest "Redo size" "Per Second" value of these three snapshots, and that is your peak redo generation rate.

Note that if your primary database is a RAC database, you must run the Statspack snapshot on every RAC instance. Then, for each Statspack snapshot, sum the "Redo Size Per Second" value of each instance, to obtain the net peak redo generation rate for the primary database. Remember that for a RAC primary database, each node generates its own redo and independently sends that redo to the standby database - hence the reason to sum up the redo rates for each RAC node, to obtain the net peak redo rate for the database.

Example:
Let us assume the redo rate is a 500 KB/sec.(500*1024=512000)
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps Required bandwidth = ((512000/0.7) * 8) /1,000,000 Required bandwidth = 4.85 Mbps

Hope this helps...

How to change the location of datafiles in Oracle database?

Below are steps to move the datafiles without shutting down the database.

1. Make the tablespaces offline.
ALTER TABLESPACE tablespace_name offline;

2.copy the files to the new location at the OS level

3.Modify the location of the datafiles
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'datafile_name' to 'datafile_name';

4.Make the tablesapces online
ALTER TABLESPACE tablespace_name online;

Changing the ODS schema password in OAS!!!

OID Database Schema Owner

The Oracle Internet Directory runs on an Oracle database

and creates two database users: ODS and ODSCOMMON. ODS

is the schema owner that contains all of the database

objects (tables, views, objects, etc.) used for OID

functionality and directory storage. When the OID needs

to login to the database, it uses the ODS database

account which has a default password of ODS. You should

secure this database user account before putting the

LDAP directory into production.

1. Change the ODS password to the desired value, and

create the new wallet:

oidpasswd connect= change_oiddb_pwd=true

oidpasswd connect= create_wallet=true

2. Restart OID:

opmnctl stopproc process-type=OID

opmnctl status

opmnctl startproc process-type=OID


3. Change the password in Enterprise Manager:

1. Login to EM

2. Click Targets tab > Click All Targets tab

3. Find the Name reported in the emagent.trc file (e.g.,10_1_2_portoidd.libby.oracle.com_LDAP) > click on it

4. Click Monitoring Configuration at the bottom right

5. Enter:

Username = ods

Password =

Using the OID Database Password Utility


The DBA can change this password by using the OID

Database Password Utility (included with the OID

installation). The following example uses this utility

to change the database password for ODS:


# $ORACLE_HOME/bin/oidpasswd

current password: ods

new password: new_secret_password

confirm password: new_secret_password

password set.

How oidpasswd Works

The oidpasswd utility connects as the ODSCOMMON user and

uses the role ODS_SERVER with the original password to

perform the following:

1. Changes the password for the ODS user for the OID

schema database.

2. Updates the SYSTEM.ODSINSTANCES table with the new,

encrypted password for ODS.

3. Changes the password for the ODS_SERVER role to the

new password.

4. Updates the $ORACLE_HOME/ldap/admin/oidpwdr file with

the encrypted password.


NOTE: Some Oracle OID patches and scripts may assume the

user ODS/ODS. It is advised to change the password for

ODS back to its default of ODS during application of

patches or when running OID scripts.


But what about ODSCOMMON?

When connecting to the database schema, the OID

executables, such as oidctl or oidldapd servers connect

as the database user ODSCOMMON. The password for

ODSCOMMON is ODSCOMMON, and that password cannot be

changed. It is hardcoded in the executables and changing

it on the database level will cause an ORA-01017 error.

There is no security risk, however, not being able to

change the ODSCOMMON password, as it has only CONNECT

privilege. Once connected as ODSCOMMON, the executable

will obtain the privileges it needs via the role

ODS_SERVER, which is protected by the ODS password. The

password is obtained by the executable from the

SYSTEM.ODSINSTANCES table. This is the password

encrypted and set by the oidpasswd utility.

How to Unlock/Reset Super User cn=orcladmin When the ODS's Password Has Been Forgotten or is Unknown?

Solution

If the ODS password has been lost then follow steps 1-7:

1. Shutdown the OID processes

% opmnctl stopproc ias-component=OID

2. Verify that there is no OID process running

% ps -ef | grep -i oidmon
% ps -ef | grep -i oidldapd
% ps -ef | grep -i odisrv

3. Go to the $ORACLE_HOME/ldap/admin directory and rename the files oidpwdlldap1 and oidpwdrxxxx

where xxxx is the instance name

4. Login to the OID database via sqlplus as an user with DBA privileges (example. SYS or SYSTEM) and reset the ODS schema password:

SQL> alter user ods identified by password;

Note: replace password for the desirable value.

5. Verify that you are able to login to the database using following command.

% sqlplus ods/password@connect_string

Where connect_string is the alias defined in the tnsnames.ora to connect to the OID database.

6. Execute the oidpasswd command to recreate the files renamed in step 3

%oidpasswd connect=connect_string create_wallet=true

Note: To use in this step the same password defined in step 4.

7. Verify that the files renamed in step 3 have been recreated under $ORACLE_HOME/ldap/admin directory

8. If the orcladmin account is locked then use following command

% oidpasswd connect=connect_string unlock_su_acct=true

9. If the password for orcladmin needs to be changed then use following command

% oidpasswd connect=connect_string reset_su_password=true

10. Start the OID process

% opmnctl startproc ias-component=OID

11. Verify that the account orcladmin has been unlocked or that the password has been changed successfully

% $ORACLE_HOME/bin/ldapbind -p port -D cn=orcladmin -w passwd

For example:

$ORACLE_HOME/bin/ldapbind -p 389 -D cn=orcladmin -w welcome1

ORA-08104: this index object string is being online built or rebuilt

Run the below script with the object id and your problem will be resolved.

DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;

BEGIN
OBJECT_ID := 63556;
WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;

After running the above script your index will be ready for use.

How to apply CPU Patches in Oracle Database?

Today I am going to give little insight about Opatch and CPU patch.Opatch is a tool is used to patch all oracle homes for oracle applications. CPU is stands for Critical patch updates, in other way its a security patches for Apps.

How to use Opatch?

Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches

1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.

CPU patch
CPU patch has to keep uptodate. Use opatch to apply CPU patches. Follow the opatch steps to apply the CPU patch. As part of post installation step run catcpu.sql and utlrp.sql

Issues will face
1. When you are applying CPU patch you will get conflict with some patches .Report to Oracle Corporation they will provide a new merged patch if its actually a conflict.

How to backup control file on windows?

Below is the command to backup control file to where ever location we want to.

alter database backup controlfile to trace as 'e:\ctl.txt';

How to backup control file to trace on unix???

===========================================================
#------------------------------------------------------------
# backup controlfile
#------------------------------------------------------------
#------------------------------------------------------------
# find the user_dump_dest directory through a sqlplus call
# and assign it to an environment variable
#------------------------------------------------------------
ORA_DUMP=`${ORACLE_HOME}/bin/sqlplus -s << EOF
/ as sysdba
set heading off
set feedback off
SELECT value
FROM v\\$parameter
WHERE name='user_dump_dest';
exit
EOF`
#------------------------------------------------------------
# create backup controlfile
#------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -s <<> /dev/null 2>&1
/ as sysdba
set heading off
set feedback off
ALTER DATABASE BACKUP CONTROLFILE TO trace;
exit
EOF
#------------------------------------------------------------
# find the trace file that the backup controlfile is in
# this should be the last trace file generated
# validate it is a controlfile and copy to backup_control.sql
#------------------------------------------------------------
DATETIME="`date '+%d%m%y'`"
CONTROL=`ls -t $ORA_DUMP/*.trc | head -1`
if [ ! -z "$CONTROL" ]; then
grep 'CONTROL' ${CONTROL} 1> /dev/null
if test $? -eq 0; then
cp ${CONTROL} /home/oracle/backup_control_file_$DATETIME.sql
fi
fi
#end
=========================================================

Script for Datafile usage in Oracle Database

This script will list datafile wise Allocated size, Used Size and Free Size

For running this query you must have SELECT privileges to V$DATAFILE and DBA_FREE_SPACE views or run the sript as sysdba

The Size details are displayed in MB (Mega Bytes)
-----------------------------------------------------------------------

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Script to check tablespace usage in Oracle Database

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to enable primary key on a column having duplicate values in oracle database

You may come across a situation where you have to put Primary key on a column in table that is having duplicate rows and you cannot delete the duplicate rows.

Now this is a tricky situation.....below is the solution for the above problem.

for example you have to put primary key on "B column" "on ABC table" that is having duplicate values.

1) create index ABC_B_INX on B tablespace ABC;

2) alter table ABC add constraint ABC_B_PK primary key(B) disable;

3) alter table ABC enable novalidate constraint ABC_B_PK;

Hope this helps.....

How to check last ddl on a table in Oracle Database

drop table test;

CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables where table_name='TEST';

SELECT current_scn
FROM v$database;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT current_scn
FROM v$database;

UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

UPDATE test
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

CREATE TABLE test2 AS
SELECT * FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test2;

INSERT INTO test VALUES ('ABC');

UPDATE test SET testcol = 'DEF' WHERE rownum = 1;

UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT scn_to_timestamp(ORA_ROWSCN), rowid, testcol FROM test2;

SELECT scn_to_timestamp(ORA_ROWSCN) FROM test2;

SELECT current_scn
FROM v$database;

Keep observing the output of the above select queries.

How to drop Oracle Database

To drop the database follow the below steps:

shutdown abort;

startup mount exclusive restrict;

drop database;

exit;

Hope this helps.....

How to check locks and kill sessions in Oracle Database

The below script will tell which session is holding the lock

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id
/


The below command is used to kill the session from oracle

ALTER SYSTEM KILL SESSION '1626,19894' IMMEDIATE

ALTER SYSTEM DISCONNECT SESSION '13, 8' IMMEDIATE

Steps to change character set of Oracle Database

If you are trying to change the character set, do the following:

% svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup mount
SVRMGR> alter system enable restricted session;
SVRMGR> alter system set job_queue_processes=0;
SVRMGR> alter system set aq_tm_processes=0;
SVRMGR> alter database open;
SVRMGR> alter database character set WE8ISO8859P1;
SVRMGR> alter database national character set WE8ISO8859P1;
SVRMGR> shutdown immediate;
SVRMGR> startup restrict;
SVRMGR> shutdown immediate;
SVRMGR> startup
SVRMGR> shutdown immediate;
SVRMGR> startup

This procedure is outlined in http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=66320.1 and explains why the database has to be shutdown/startup so many times.

query to find database size completely

The below query will give the size of the Oracle Database completely.

select round((a.data_size+b.temp_size+c.redo_size)/1024/1024/1024,2) "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

script for tablespace useage

Below is the script to get data about all the tablespaces in a database.

SELECT Total.name "Tablespace Name",
total_space "Total Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
nvl(Free_space, 0) "Free Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

oracle kernel paramters for solaris10

steps for oracle kernel paramters setup for solaris 10.

1. Login as root

2. To add the project run the following command
# projadd user.oracle

3. To view the current values of the resource control, enter the following commands

. # id -p // to verify the project id
uid=0(root) gid=0(root) projid=1 (user.root)

4. # prctl -n project.max-shm-memory -i project user.root

5. # prctl -n project.max-sem-ids -i project user.root


If you must change any of the current values, then:
a. To modify the value of max-shm-memory to 6 GB:

# prctl -n project.max-shm-memory -v 6gb -r -i project user.root


d. To modify the value of max-sem-ids to 256:

# prctl -n project.max-sem-ids -v 256 -r -i project user.root


For changing the values for user.oracle run the following commands
#projmod -a -K "project.max-shm-memory=(priv,6,deny)" user.oracle
#projmod -a -K "project.max-sem=(priv,256,deny)" user.oracle

-------------------------------------------------------------------------------------------------
The Solaris 10 Operating System introduces the following new resource controls to replace the old /etc/system tunables:Code: Old Old New New
Resource control tunable default Max value default
---------------------- ------------- ------- --------- ----------
process.max-msg-qbytes msginfo_msgmnb 4096 ULONG_MAX 65536
process.max-msg-messages msginfo_msgtql 40 UINT_MAX 8192
process.max-sem-ops seminfo_semopm 10 INT_MAX 512
process.max-sem-nsems seminfo_semmsl 25 SHRT_MAX 512
project.max-shm-memory shminfo_shmmax 0x800000 UINT64_MAX 1/4 physmem
project.max-shm-ids shminfo_shmmni 100 2**24 128
project.max-msg-ids msginfo_msgmni 50 2**24 128
project.max-sem-ids seminfo 10 2**24 128

Data Pump.....New Feature in Oracle 10g

Data Pump:
Data Pump is a server side application therefore the dump files will be generated on the server.It is about 5-6 times faster than export/import.

Steps for configuration of Data Pump:

1.Login as sysdba

2.Create directory at OS and database level with the following commands
mkdir d:\datapump
SQL>CREATE DIRECTORY test_dir AS 'd:\datapump';

3.Grant read and write access to the user.
SQL>GRANT READ, WRITE ON DIRECTORY dmpdir TO ;

4.Ensure that the user have the EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges. To see a list of all roles assigned to you within your security domain, issue the following command
Connect to
SQL> SELECT * FROM SESSION_ROLES;

Now the required setting for Data Pump has been done.

The following are some of the different options that we can use in Data Pump.

Table Exports/Imports:

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports:

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports:

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Suspending and Resuming Export Jobs (Attaching and Re-Attaching to the Jobs):

You can suspend running export jobs and later on resume these jobs or kill these jobs using Data Pump Export. You can start a job in one client machine and then, if because of some work, you can suspend it. Afterwards when your work has been finished you can continue the job from the same client, where you stopped the job, or you can restart the job from another client machine.
For Example, suppose a DBA starts a full database export by typing the following command at one client machine CLNT1 by typing the following command

$expdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob

After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Export> prompt where he can type interactive commands
Now he wants to stop this export job so he will type the following command
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.

After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp hr/hr@mydb ATTACH=myfulljob

After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
A message is displayed that the job has been reopened, and processing status is output to the client.

Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t want to continue with the export job.

There is one impdp parameter "SQLFILE", if you use this in impdp command it will not import the data into the database instead of that it will create one SQL file for the data that has to be imported.

copy command in oracle!!!

Suppose you have to make exact replica of a table from one database to another.....

How will you do it?
The solution that comes into mind is through exp-imp or data pump (Oracle 10g feature), this can difficult sometimes as you have to move the .dmp file from one database to another or do some other settings like tns entries, instead of this there is one better solution for the same "copy command"

Lets understand it through an example
Suppose you want to make exact replica of "emp" table from database A to database B.

Steps:
1. Login on database B.

2. Create structure of emp table in database B same as in database A.

3. Now execute the below copy command
copy from username/password@A insert emp using select * from emp;

Now you have exact replica of table emp in database B also.

Please let me know if you have any queries.

How to change datatype of a column if the data in that column exists?

The above question many of you might have faced that how to change the datatype of the column if the data in the column already exists.

Here is the solution.
1. Suppose for example the below is the table
desc emp
id number
name varchar2(50)

Now you want to change the datatype of id to varchar2(20)

2. Add a dump column say xyz to the table emp
alter table emp
add xyz varchar2(20)

3. Copy the data from the id column to the xyz column
update emp
set xyz=id;
Crosscheck the data and commit the transaction.
commit;

4. Update all the rows of the id solution with NULL Values.
update emp
set id=NULL;
Crosscheck and commit the transaction.
commit;

5. Now the id column is having no data so change its datatype.
alter table emp
modify id varchar2(20);

6. Now copy the data back from xyz column to id column.
update emp
set id=xyz;
Crosscheck the data and commit the transaction.
commit;

7. Now drop the column xyz as its purpose is over.
alter table emp
drop column xyz;

Hope this helps.....

100 keyboard shortcuts!!!

Keyboard shorcuts are of great help.....Below is the list of some of the shortcuts....

CTRL+C (Copy)
CTRL+X (Cut)
CTRL+V (Paste)
CTRL+Z (Undo)
DELETE (Delete)
SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
CTRL while dragging an item (Copy the selected item)
CTRL+SHIFT while dragging an item (Create a shortcut to the selected item) F2 key (Rename the selected item)
CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
CTRL+A (Select all)
F3 key (Search for a file or a folder)
ALT+ENTER (View the properties for the selected item)
ALT+F4 (Close the active item, or quit the active program)
ALT+ENTER (Display the properties of the selected object)
ALT+SPACEBAR (Open the shortcut menu for the active window)
CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
ALT+TAB (Switch between the open items)
ALT+ESC (Cycle through items in the order that they had been opened)
F6 key (Cycle through the screen elements in a window or on the desktop)
F4 key (Display the Address bar list in My Computer or Windows Explorer)
SHIFT+F10 (Display the shortcut menu for the selected item)
ALT+SPACEBAR (Display the System menu for the active window)
CTRL+ESC (Display the Start menu)
ALT+Underlined letter in a menu name (Display the corresponding menu)
Underlined letter in a command name on an open menu (Perform the corresponding command) F10 key (Activate the menu bar in the active program)
RIGHT ARROW (Open the next menu to the right, or open a submenu)
LEFT ARROW (Open the next menu to the left, or close a submenu)
F5 key (Update the active window)
BACKSPACE (View the folder one level up in My Computer or Windows Explorer)
ESC (Cancel the current task)
SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing) Dialog Box Keyboard Shortcuts
CTRL+TAB (Move forward through the tabs)
CTRL+SHIFT+TAB (Move backward through the tabs)
TAB (Move forward through the options)
SHIFT+TAB (Move backward through the options)
ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
ENTER (Perform the command for the active option or button)
SPACEBAR (Select or clear the check box if the active option is a check box)
Arrow keys (Select a button if the active option is a group of option buttons)
F1 key (Display Help)
F4 key (Display the items in the active list)
BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box) m*cro$oft Natural Keyboard Shortcuts Windows Logo (Display or hide the Start menu) Windows Logo+BREAK (Display the System Properties dialog box)
Windows Logo+D (Display the desktop)
Windows Logo+M (Minimize all of the windows)
Windows Logo+SHIFT+M (Restore the minimized windows)
Windows Logo+E (Open My Computer)
Windows Logo+F (Search for a file or a folder)
CTRL+Windows Logo+F (Search for computers)
Windows Logo+F1 (Display Windows Help)
Windows Logo+ L (Lock the keyboard)
Windows Logo+R (Open the Run dialog box)
Windows Logo+U (Open Utility Manager)
Accessibility Keyboard Shortcuts
Right SHIFT for eight seconds (Switch FilterKeys either on or off)
Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
SHIFT five times (Switch the StickyKeys either on or off)
NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
Windows Logo +U (Open Utility Manager)
Windows Explorer Keyboard Shortcuts END (Display the bottom of the active window)
HOME (Display the top of the active window)
NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder) NUM LOCK+Plus sign (+) (Display the contents of the selected folder)
NUM LOCK+Minus sign (-) (Collapse the selected folder)
LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder) RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder) Shortcut Keys for Character Map
After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:
RIGHT ARROW (Move to the right or to the beginning of the next line)
LEFT ARROW (Move to the left or to the end of the previous line)
UP ARROW (Move up one row)
DOWN ARROW (Move down one row)
PAGE UP (Move up one screen at a time)
PAGE DOWN (Move down one screen at a time)
HOME (Move to the beginning of the line)
END (Move to the end of the line)
CTRL+HOME (Move to the first character)
CTRL+END (Move to the last character)
SPACEBAR (Switch between Enlarged and Normal mode when a character is selected) m*cro$oft Management Console (MMC)
Main Window Keyboard Shortcuts
CTRL+O (Open a saved console)
CTRL+N (Open a new console)
CTRL+S (Save the open console)
CTRL+M (Add or remove a console item)
CTRL+W (Open a new window)
F5 key (Update the content of all console windows)
ALT+SPACEBAR (Display the MMC window menu)
ALT+F4 (Close the console)
ALT+A (Display the Action menu)
ALT+V (Display the View menu)
ALT+F (Display the File menu)
ALT+O (Display the Favorites menu)
MMC Console Window Keyboard Shortcuts
CTRL+P (Print the current page or active pane)
ALT+Minus sign (-) (Display the window menu for the active console window)
SHIFT+F10 (Display the Action shortcut menu for the selected item)
F1 key (Open the Help topic, if any, for the selected item)
F5 key (Update the content of all console windows)
CTRL+F10 (Maximize the active console window)
CTRL+F5 (Restore the active console window)
ALT+ENTER (Display the Properties dialog box, if any, for the selected item)
F2 key (Rename the selected item)
CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)
Remote Desktop Connection Navigation
CTRL+ALT+END (Open the m*cro$oft Windows NT Security dialog box)
ALT+PAGE UP (Switch between programs from left to right)
ALT+PAGE DOWN (Switch between programs from right to left)
ALT+INSERT (Cycle through the programs in most recently used order)
ALT+HOME (Display the Start menu)
CTRL+ALT+BREAK (Switch the client computer between a window and a full screen) ALT+DELETE (Display the Windows menu)
CTRL+ALT+Minus sign (-) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
CTRL+ALT+Plus sign (+) (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.)
m*cro$oft Internet Explorer Navigation
CTRL+B (Open the Organize Favorites dialog box)
CTRL+E (Open the Search bar)
CTRL+F (Start the Find utility)
CTRL+H (Open the History bar)
CTRL+I (Open the Favorites bar)
CTRL+L (Open the Open dialog box)
CTRL+N (Start another instance of the browser with the same Web address)
CTRL+O (Open the Open dialog box, the same as CTRL+L)
CTRL+P (Open the Print dialog box)
CTRL+R (Update the current Web page)
CTRL+W (Close the current window)

Hope this helps in you daya to day works.....

Run command is very useful in Windows!!!

Run command is very useful, but sometimes it is forgotten. Use it sometimes and you will find how powerful is run command.

1. Accessibility Controls - access.cpl
2. Accessibility Wizard - accwiz
3. Add Hardware Wizard - hdwwiz.cpl
4. Add/Remove Programs - appwiz.cpl
5. Administrative Tools - control admintools
6. Automatic Updates - wuaucpl.cpl
7. Bluetooth Transfer Wizard - fsquirt
8. Calculator - calc
9. Certificate Manager - certmgr.msc
10. Character Map - charmap
11. Check Disk Utility - chkdsk
12. Clipboard Viewer - clipbrd
13. Command Prompt - cmd
14. Component Services - dcomcnfg
15. Computer Management - compmgmt.msc
16. Control Panel - control
17. Date and Time Properties - timedate.cpl
18. DDE Shares - ddeshare
19. Device Manager - devmgmt.msc
20. Direct X Troubleshooter - dxdiag
21. Disk Cleanup Utility - cleanmgr
22. Disk Defragment - dfrg.msc
23. Disk Management - diskmgmt.msc
24. Disk Partition Manager - diskpart
25. Display Properties - control desktop
26. Display Properties - desk.cpl
27. Dr. Watson System Troubleshooting Utility - drwtsn32
28. Driver Verifier Utility - verifier
29. Event Viewer - eventvwr.msc
30. Files and Settings Transfer Tool - migwiz
31. File Signature Verification Tool - sigverif
32. Findfast - findfast..cpl
33. Firefox - firefox
34. Folders Properties - control folders
35. Fonts - control fonts
36. Fonts Folder - fonts
37. Free Cell Card Game - freecell
38. Game Controllers - joy.cpl
39. Group Policy Editor (for xp professional) - gpedit.msc
40. Hearts Card Game - mshearts
41. Help and Support - helpctr
42. HyperTerminal - hypertrm
43. Iexpress Wizard - iexpress
44. Indexing Service - ciadv.msc
45. Internet Connection Wizard - icwconn1
46. Internet Explorer - iexplore
47. Internet Properties - inetcpl.cpl
48. Keyboard Properties - control keyboard
49. Local Security Settings - secpol.msc
50. Local Users and Groups - lusrmgr.msc
51. Logs You Out Of Windows - logoff
52. Malicious Software Removal Tool - mrt
53. Microsoft Chat - winchat
54. Microsoft Movie Maker - moviemk
55. Microsoft Paint - mspaint
56. Microsoft Syncronization Tool - mobsync
57. Minesweeper Game - winmine
58. Mouse Properties - control mouse
59. Mouse Properties - main.cpl
60. Netmeeting - conf
61. Network Connections - control netconnections
62. Network Connections - ncpa.cpl
63. Network Setup Wizard - netsetup.cpl
64. Notepad notepad
65. Object Packager - packager
66. ODBC Data Source Administrator - odbccp32.cpl
67. On Screen Keyboard - osk
68. Outlook Express - msimn
69. Paint - pbrush
70. Password Properties - password.cpl
71. Performance Monitor - perfmon.msc
72. Performance Monitor - perfmon
73. Phone and Modem Options - telephon.cpl
74. Phone Dialer - dialer
75. Pinball Game - pinball
76. Power Configuration - powercfg.cpl
77. Printers and Faxes - control printers
78. Printers Folder - printers
79. Regional Settings - intl.cpl
80. Registry Editor - regedit
81. Registry Editor - regedit32
82. Remote Access Phonebook - rasphone
83. Remote Desktop - mstsc
84. Removable Storage - ntmsmgr.msc
85. Removable Storage Operator Requests - ntmsoprq.msc
86. Resultant Set of Policy (for xp professional) - rsop.msc
87. Scanners and Cameras - sticpl.cpl
88. Scheduled Tasks - control schedtasks
89. Security Center - wscui.cpl
90. Services - services..msc
91. Shared Folders - fsmgmt.msc
92. Shuts Down Windows - shutdown
93. Sounds and Audio - mmsys.cpl
94. Spider Solitare Card Game - spider
95. SQL Client Configuration - cliconfg
96. System Configuration Editor - sysedit
97. System Configuration Utility - msconfig
98. System Information - msinfo32
99. System Properties - sysdm.cpl
100. Task Manager - taskmgr
101. TCP Tester - tcptest
102. Telnet Client - telnet
103. User Account Management - nusrmgr.cpl
104. Utility Manager - utilman
105. Windows Address Book - wab
106. Windows Address Book Import Utility - wabmig
107. Windows Explorer - explorer
108. Windows Firewall - firewall.cpl
109. Windows Magnifier - magnify
110. Windows Management Infrastructure - wmimgmt.msc
111. Windows Media Player - wmplayer
112. Windows Messenger - msmsgs
113. Windows System Security Tool - syskey
114. Windows Update Launches - wupdmgr
115. Windows Version - winver
116. Windows XP Tour Wizard - tourstart
117. Wordpad - write

Now Enjoyy...

Identifying Your Oracle Database Software Release

Identifying Your Oracle Database Software Release
Because the Oracle database server continues to evolve and can require maintenance, Oracle periodically produces new releases. Because only some users initially subscribe to a new release or require specific maintenance, multiple releases of the product can exist simultaneously.
As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed below.

Release Number Format
To understand the release level nomenclature used by Oracle, examine the following example of an Oracle database server labeled "Release 9.2.0.1.0."

Figure 1-1 Example of an Oracle Release Number

Note:
Starting with release 9.2, maintenance releases of Oracle are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number
This is the most general identifier. It represents a major new edition (or version) of the software that contains significant new functionality.

Database Maintenance Release Number
This digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number
This digit reflects the release level of the Oracle9i Application Server (Oracle9iAS).

Component Specific Release Number
This digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform Specific Release Number
This digit identifies a platform specific release. Usually this is a patch set. Where different platforms require the equivalent patch set, this digit will be the same across the effected platforms.

Checking Your Current Release Number
To identify the release of the Oracle database server that is currently installed and to see the release levels of other Oracle components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query is shown below. Other product release levels may increment independently of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15

SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS

----------------------------------- --------------- ---------------

NLSRTL 9.2.0.1.0 Production

Oracle9i Enterprise Edition 9.2.0.1.0 Production

PL/SQL 9.2.0.1.0 Production

TNS for Solaris: 9.2.0.1.0 Production

It's important to convey to Oracle the information displayed by this query when you report problems with the software.
Optionally, you can query the V$VERSION view to see component-level information.

What is an Index Organized Table (IOT) and when should an IOT be used?

An IOT keeps its data stored according to the primary key column values for the table. IOTs store the data as if the entire table was stored in an index.A normal index only stores the indexed columns in the index; an IOT stores all of the table's columns in the index. IOTs are new in Oracle8.As the table's data is stored as an index, the rows of the table do not have physical RowIDs. Therefore, you cannot select the RowID pseudocolumn values from an IOT.

Oracle8i introduces a new feature for IOTs called logical RowIDs. Logical RowIDs allow the construction of secondary indexes and improve index performance allowing the IOT performance to match the performance of regular indexes.

Prior to Oracle8i you could not create additional indexes on an IOT. The only valid index being the primary key index. As of Oracle 8i you can create secondary indexes on IOTs. An IOT will require less space than a regular table since there are no RowIDs.

Suppose you create the following IOT:

CREATE TABLE iottest
(areacode CHAR(3) primary key,
city VARCHAR2 (50))
ORGANIZATION INDEX TABLESPACE users
PCTTHRESHOLD 10
OVERFLOW TABLESPACE users;

An IOT is appropriate if you will always be accessing the areacode information by the areacode column in the where clause of your queries. To limit the amount of internal management with the IOT you should only create an IOT if the data is very static. If the data in your table changes frequently you should use a regular table with indexes. In general, IOTs are most efffective when the primary key constitutes a large part of the tables columns.If the IOT contains many frequently accessed columns that are not part of the primary key, then the IOT will need to repeatedly access it's overflow area.

Use of the pctthreshold clause is optional. When specified, pctthreshold indicates a threshold limit that any row of data can occupy in an index-organized table block.In the example, the value set for this clause is 10, which means that any row in the table that takes up 10 percent or more of its data block for storage will have nonkey column data moved into an overflow tablespace defined using the overflow tablespace keywords. If overflow tablespace is specified without defining a pctthreshold, the default pctthreshold of 50 will be used.A row bigger than pctthreshold in an IOT might destroy the dense storage of the rows in the index. In cases where this condition might occur, an overflow tablespace clause should be used.

To retrieve Oracle8
IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------
SELECT t.table_name AS ?IOT?, o.table_name AS ?Overflow?,
i.index_name AS ?Index?, o.tablespace_name AS ?Overflow TS?,
i.tablespace_name AS ?Index TX?, i.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes i
WHERE t.owner = o.owner
AND t.table_name = o.iot_name
AND t.owner = i.owner
AND t.table_name = i.table_name AND t.owner = ?SCOTT?;

To retrieve post Oracle8

IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------

SQL> SELECT table_name, tablespace_name, iot_name, iot_type
FROM dba_tables
where table_name = 'IOTTEST'

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IOT_NAME IOT_TYPE
------------------------------ ------------
IOTTEST
IOT
SQL> SELECT object_name, object_id
FROM dba_objects
WHERE object_name = 'IOTTEST';

OBJECT_NAME OBJECT_ID
------------------------------------- ---------
IOTTEST 74858

SQL> SELECT index_name, index_type, tablespace_name, table_name
FROM dba_indexes
WHERE table_name = 'IOTTEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IOT_TOP_74858 IOT - TOP
USERS IOTTEST


Now using the object_id obtained from dba_objects

SQL> SELECT segment_name, tablespace_name, segment_type
FROM dba_segments
WHERE segment_name like '%74858%';

SEGMENT_NAME
--------------------------------------------------------------
TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_74858
USERS TABLE

SYS_IOT_TOP_74858 USERS INDEX

IOT Restrictions:
-----------------
1. A primary key must be specified otherwise ORA-25175 is returned.
2. If pctthreshold is defined and an overflow segment is not defined,
rows exceeding the threshold are rejected with an ORA-1429 error.
3. Cannot create an IOT of object types.
4. An IOT can contain columns of LOB and nested table types, but only
if the table is not partitioned.
5. IOTs must be reorganized using the move clause of the alter table
command.

in Vs Exists???

The two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

As opposed to

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

for x in ( select * from t1 ) loop
if ( exists ( select null from t2 where y = x.x ) then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

Clustered table???

Clustered table
A clustered table provides an optional method for storing table data. A cluster is made up of a table or group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.

Clusters have the following characteristics:

· Clusters have a cluster key, which is used to identify the rows that need to be stored together.

· The cluster key can consist of one or more columns.

· Tables in a cluster have columns that correspond to the cluster key.

· Clustering is a mechanism that is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it were stored in a regular table.

· Updating one of the columns in the cluster key may migrate the row.

· The cluster key is independent of the primary key. The tables in a cluster can have a primary key, which may be the cluster key or a different set of columns.

· Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scans on clustered tables are generally slower.

· Clusters renormalize the physical storage of tables without affecting the logical structure.

When to Rebuild an Index???

You have to periodically check your indexes to see if they become skewed and,therefore, good candidates for rebuild.

A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.

Here is a sample procedure on how to identify the skewed indexes:

1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.

For example:

SQL> analyze index A1_PK compute statistics;

Index analyzed.

2. Run the script given below - index_check.sql - to find out how skewed each index is.

This query checks on all indexes that belong to user SCOTT:

SQL> select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner='SCOTT';

INDEX_NAME BLEVEL OK
---------------------------------------- ------ ----
A1_PK BLEVEL HIGH
A1_UK BLEVEL HIGH
BUDVERPORT_BUDVERPORT2_UK 1 OK BLEVEL
BUDVERPORT_BV_FK_I 2 OK BLEVEL
BUDVERPORT_CHAR_CL_FK_I 1 OK BLEVEL
BUDVERPORT_DIRCTE_FK_I 3 OK BLEVEL
BUDVERPORT_FUND_TYPE_FK_I 1 OK BLEVEL
BUDVERPORT_OMB_SUBFCT_FK_I 1 OK BLEVEL
BUDVERPORT_ORG_FK_I 0 OK BLEVEL
BUDVERPORT_PL_TITLE_FK_I 1 OK BLEVEL
BUDVERPORT_RDC_FK_I 1 OK BLEVEL
S_INVENTORY_PRODID_WARID_PK BLEVEL HIGH
S_ITEM_ORDID_ITEMID_PK BLEVEL HIGH
S_ITEM_ORDID_PRODID_UK BLEVEL HIGH
S_LONGTEXT_ID_PK BLEVEL HIGH
S_ORD_ID_PK BLEVEL HIGH
S_PRODUCT_ID_PK BLEVEL HIGH
S_PRODUCT_NAME_UK BLEVEL HIGH
S_REGION_ID_PK BLEVEL HIGH
S_REGION_NAME_UK BLEVEL HIGH
S_TITLE_TITLE_PK BLEVEL HIGH
S_WAREHOUSE_ID_PK BLEVEL HIGH

3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4,it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.

Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.

4. Gather more index statistics using the VALIDATE STRUCTURE option
of the ANALYZE command to populate the INDEX_STATS virtual table. This table does not contain an OWNER column and assumes you are looking for statistics for indexes created by your active session only.

SQL> analyze index SCOTT.ORG_PK validate structure;

Index analyzed.

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS)
PCT_DELETED, (LF_ROWS-DISTINCT_KEYS)*100/
decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats
where NAME='&index_name';

Enter value for index_name: ORG_PK

PCT_DELETED DISTINCTIVENESS
----------- ---------------
0 0

The PCT_DELETED column shows what percent of leaf entries (index
entries) have been deleted and remain unfilled. The more deleted
entries exist on an index, the more unbalanced the index becomes.
If the PCT_DELETED is 20% or higher, the index is candidate for
rebuilding.If you can afford to rebuild indexes more frequently,
then do so if the value is higher than 10%. Leaving indexes with
high PCT_DELETED without rebuild might cause excessive redo
allocation on some systems.

The DISTINCTIVENESS column shows how often a value for the
column(s) of the index is repeated on average. For example, if a
table has 10000 records and 9000 distinct SSN values, the formula
would result in (10000-9000) x 100 / 10000 = 10. This shows a good
distribution of values.If, however, the table has 10000 records
and only 2 distinct SSN values, the formula would result in
(10000-2) x 100 /10000 = 99.98. This shows that there are very few
distinct values as a percentage of total records in the column.
Such columns are not candidates for a rebuild but good candidates
for bitmapped indexes.

How to check Corruption in Database Using DBVERIFY

The primary tool for checking for corruption in an Oracle database is DBVERIFY. It can be used to perform a physical data structure integrity check on data files whether the database is online or offline. The big benefit of this is that DBVERIFY can be used to check backup data files without adding load to the database server. You invoke DBVERIFY from the operating system command line like this:

$ dbv file=data01.dbf logfile=verify.log blocksize=8192 feedback=100

In this example data01.dbf is the data file to check, and the tablespace this file belongs to has a block size of 8192 bytes. The feedback parameter tells DBVERIFY to draw a period on the screen after every 100 pages (blocks) of the file are verified.
In the log file you’ll see output like this:

DBVERIFY - Verification starting : FILE = data01.dbf

DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 631
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 9
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

The Total Pages Failing values show the number of blocks that failed either the data block or index block checking routine. The Total Pages Marked Corrupt figure shows the number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type. And the Total Pages Influx is the number of blocks for which DBVERIFY could not get a consistent image. (This could happen if the database is open when DBVERIFY is run. DBVERIFY reads blocks multiple times to try to get a consistent image, but DBVERIFY cannot get a consistent image of pages that are in flux.)
If you want to verify only a portion of a data file, you can specify a starting and ending block when running DBVERIFY. If you want to verify the entire database, you can generate a short shell script to run DBVERIFY on every data file in the database.
You can do this easily using SQL*Plus:
SQL> SPOOL dbv_on_all_files.sh
SQL> SELECT ‘dbv file=’ file_name
2 ‘ logfile=file’ ROWNUM
3 ‘.log blocksize=8192′
4 FROM dba_data_files;
SQL> SPOOL OFF
After running the shell script you can quickly scan all of the DBVERIFY log files with Unix commands like:
$ grep Failing file*.log
$ grep Corrupt file*.log
$ grep Influx file*.log
You can also use DBVERIFY to validate a single data or index segment. To do this you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.

There are other ways to check for database corruption besides DBVERIFY. You can take a full database export, with the dump file optionally specified as a null device. This will read every row in every user table in the database, discovering any corrupted data blocks along the way. However, this technique does not access every index entry or the entire data dictionary.

If you want to check one table and all of its indexes, you can use the ANALYZE statement to read every row of the table, read every entry in each of the table’s indexes, and make sure the table and index data are consistent with each other:

SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
This will lock the table, preventing DML on the table, unless you specify the ONLINE keyword. Online validation reduces the amount of validation performed to allow for concurrency.
There are several ways to check for corruption in an Oracle database, but the DBVERIFY tool is the most versatile. DBVERIFY does not limit concurrency or DML while it is running, and it can be run against a database backup. Just remember that if DBVERIFY detects corruption in your database and you are planning to recover the corrupt file from a backup, you should perform a DBVERIFY validation on the backup file before beginning the recovery. This will tell you if the corruption exists in the backup also.

Upgrading OracleAS 10g Portal from 10.1.2.0.2 to 10.1.4.2 on Windows

Introduction
Upgrading Portal can be a puzzle and a challenge, and it's not always fun.

This guide assumes that:
you have a fresh installation of Oracle Application Server 10g Release 2 (10.1.2.0.2)

Important:
Throughout these instructions, you need to replace example paths (e.g. "D:\ora101202mt") with your paths, example instance names (e.g. "ora101202mt") with your instance names, and example server names (e.g. "donald.disney.com") with your server names, unless you actually use similar values as the ones showed here (which makes things much easierWhy?). To be on the safe side, avoid using spaces or periods in your permanent and temporary paths!

You should always read the documentation and notes that come with all the products and patches so that you may be prepared for some of the expected and unexpected situations!

Before you begin
1. If you have not rebooted since you started installing Application Server, then reboot now.

2. Ensure that the database, TNS and Infrastructure processes are running:
net start OracleServiceORCL
net start Oracleora102dbTNSListener
cd /d D:\ora101202infra\opmn\binopmnctl startall

3. Ensure that all Middle-Tier processes are stopped:
cd /d D:\ora101202mt\opmn\bin
opmnctl stopall

Download software
Portal Upgrade (10.1.4) - 176 MB
Patch 5983622 (OracleAS 10.1.2.3) - 1.7 GB

Upgrading Portal to 10.1.4
1. If your metadata repository database is version 10.2.0.2 or higher, you need to edit C:\stage\mrua_051020\mrua\mrua.xml, adding the following entry (using version 10.2.0.2 as an example):
<Version ReleaseNumber="10.2.0.2"
DisplayName="OracleAS supported database">
</Version>
For more information, see Note 361176.1 on MetaLink.

2. Run the upgrade script (check the parameter values by inspecting the ias.properties file in the D:\ora101202infra\config folder):
cd /d C:\stage\mrua_051020\mrua
mrua.bat -oracle_home D:\ora101202infra -oid_host donald.disney.com -oid_ssl_port 636

3. When prompted, enter the password for the database SYS user account, e.g. welcome1

4. When prompted, enter the Oracle Internet Directory administrator password, e.g. welcome1

5. When the script finishes (after approximately 30 minutes), check the upgrade.log file in the D:\ora101202infra\upgrade\temp\portal folder

6. Review the Post-Upgrade Tasks in the online documentation.

7. If you would like to configure Oracle Text Indexes to Synchronize on Commit:
cd /d D:\ora101202mt\portal\admin\plsql\wws

sqlplusw portal@orclexec

wwv_context.commit_sync('WWSBR_CORNER_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_DOC_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_PERSP_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_THING_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_TOPIC_CTX_INDX', true);exec wwv_context.commit_sync('WWSBR_URL_CTX_INDX', true);

@textstat.sql

8. If you need to add text elements, please see "Configuring OracleAS 10g (10.1.2.0.2)".

Upgrading OracleAS to 10.1.2.3
1. Download Patch 5983622 (1.7 GB)
2. Unzip patch to C:\stage\ias\p5983622

Middle-Tier
1. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
2. Click Next
3. As Destination, select ora101202mt (folder D:\ora101202mt)
4. Click Next
5. Select Oracle Application Server and Developer Suite 10g Rel 2 Software Update 10.1.2.3.0 (already selected)
6. Click Next
7. Enter ias_admin password
8. Click Next
9. Enter password for cn=orcladmin
10. Click Next
11. Click OK to accept that processes will be shut down
12. If Windows Security Alert pops up, click Unblock
13. Click Install
14. When update is finished, click Exit and then Yes

Infrastructure
1. Stop Middle-Tier processes and all other clients
2. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
3. Click Next
4. As Destination, select ora101202infra (folder D:\ora101202infra)
5. Click Next
6. Select Oracle Application Server and Developer Suite 10g Rel 2 Software Update 10.1.2.3.0 (already selected)
7. Click Next
8. Enter ias_admin password
9. Click Next
10. Enter password for cn=orcladmin
11. Click Next
12. Enter password for Oracle Certificate Authority Administrator
13. Click Next
14. Enter password for the SYS schema in the Metadata Repository database
15. Click Next
16. Click OK to accept that processes will be shut down
17. If Windows Security Alert pops up, click Unblock
18. Click Install
19. When update is finished, click Exit and then Yes

Upgrading Portal Metadata Repository to 10.1.4.2
1. Verify that component PORTAL is found in app_registry:
connect sys@orcl as sysdba
select version, status from app_registry where comp_id = 'PORTAL';
2. Stop Middle-Tier processes and clients, but leave Infrastructure and Database processes running
3. Start Installer, e.g. C:\stage\ias\p5983622\Disk1\setup.exe
4. Click Next
5. As Destination, select ora101202mt (folder D:\ora101202mt)
6. Click Next
7. Select Oracle Application Server 10g Portal Metadata Repository Update
8. Click Next
9. Click OK to verify that the database version is correct and that the software update has been performed on the Middle-Tier and the Infrastructure
10. Select Immediately install the update (already selected)
11. Click Next
12. Enter ias_admin password
13. Click Next
14. Enter password for cn=orcladmin
15. Click Next
16. Click Next to accept the Database Connection String (e.g. donald.disney.com:1521:orcl.disney.com:ORCL.DISNEY.COM)
17. Click Next
18. Enter password for the SYS schema in the Metadata Repository database
19. Click Next
20. Select OracleAS 10g Portal (schema PORTAL) and OracleAS 10g Wireless (schema WIRELESS)
21. Click Next
22. Click OK to verify that processes have been stopped
23. Click Install
24. When update is finished, click Exit and then Yes

More Information
Staging a Test Environment from a Production Environment
Oracle Application Server Portal Installation and Upgrade Guide
OracleAS Portal Upgrade
Readme for Patch 4960210
Note 260554.1: Master Note for OracleAS Portal Install Issues
Note 262623.1: Master Note for OracleAS Portal Upgrade Issues
Note 281400.1: Troubleshooting Portal Errors when applying MR Updates from Oracle AS Patchsets 9.0.4.x / 10.1.2.x
Note 361176.1: Database Version 10.2.0.2.0 or 10.2.0.3.0 Is Not Supported When Upgrading Portal to 10.1.4
Note 276620.1: How to "clone" a Portal Repository
Note 249212.1: Support Position for Oracle Products Running on VMWare Virtualized Environments

Upgrade the Oracle database from 10.1.0.2.0 to 10.1.0.5.0

This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.2 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.

1. Patch Set Overview
You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.

2. Oracle Universal Installer Version Requirements
This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.

3. System Requirements
Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof
Oracle DB : 10gr1(10.1.0.2) or later

4. Pre-installation Tasks
I. Identify the Oracle Database Installation
This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.

II. Download and Extract the Installation Software
Download and extract the p4505133_10105_WINNT.zip patch set.

III. Shut Down Oracle Databases
C:\>sqlplus sys@ORCL/pwd as sysdba
SQL>shutdown immediate;
C:\>sqlplus sys@CATDB/pwd as sysdba
SQL>shutdown immediate;

IV. Stop All Services
C:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
C:\>net stop OracleServiceORCL
The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services.
OracleDBConsoleorcl
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleorcl service is stopping....
The OracleDBConsoleorcl service was stopped successfully.
The OracleServiceORCL service is stopping.
The OracleServiceORCL service was stopped successfully.
C:\>net stop OracleServiceCATDB
The OracleServiceCATDB service is stopping.......
The OracleServiceCATDB service was stopped successfully.

V. Stop the listener:
C:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 13-JUN-2007 16:30
:58
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
C:\>net stop OracleCSService
The OracleCSService service was stopped successfully.

VI. Back up the SYSTEM.

5.Installation Tasks
I. Run SETUP.EXE from where you download/extract your patch.
In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.

On the Summary screen, click Install.

Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)

1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.

SQL> alter tablespace SYSTEM
2 add datafile 'c:\oracle\product\10.1.0\oradata\orcl\system02.dbf' size 20m;
Tablespace altered.

SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;
System altered.

SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;
System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> SPOOL patch.log

SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################DOC>######################################################################DOC>#PL/SQL procedure successfully completed
.....
[OUTPUT CUT]

SQL> SPOOL OFF

Restart the database
SQL>SHUTDOWN
SQL>STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @C:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Known Issues
select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236

IGNORE
---> If you do not use Java in the database, then ignore this error message.

Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.

If RMAN CATALOG is also configure then upgrade RMAN CATALOG
C:\>rman CATALOG=rman/cat@CATDB
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> UPGRADE CATALOG;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG;
recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00

How To Remove and Add Right-Click Menu Items from Files and Folders???

How To Remove and Add Right-Click Menu Items from Files and Folders

Removing Items

A lot of programs you install will add themselves to the right-click menu of your files and/or folders. And most times, you have no choice in the matter and, as a result, your right-click menu can get very long with added items you don't even use. The last person I was helping with this had a right context menu so long that the Rename option was no longer visible!
Fortunately, you can easily remove those unwanted menu items, if you know the registry values to edit. And it's not at all difficult once you know the keys responsible for the additions.For Files, the secret lies in the "context menu handlers" under the shellex subkey for "All Files" which, in the registry, is nothing but an asterisk - like a dos wildcard, which means the values entered apply to all files. It is at the very top of the Root key, right here:

HKEY_CLASSES_ROOT\*\shellex\ContextMenuHandlers

Click the the + sign next to the ContextMenuHandlers key, to expand it.Now you will see some of the programs that have added items to your right-click menu. Simply delete the program keys you don't want. Yup! It's that simple. If deleting makes you uneasy, just export the key before deleting it. Or, instead of deleting the values, disable them. Simply double click the default value for the program on the right hand pane and rename the clsid value by placing a period or dash in front of it.
ie; - {b5eedee0-c06e-11cf-8c56-444553540000}
Then exit the registry, refresh, and right click a file to see if the item was removed from the menu.Some programs - like WinZip or WinRar - will add several items to your right click menu but all of them will be removed by deleting or disabling their one context menu handler. Note that the above key only applies to the right click menu of files.To remove entries from the right click context menu of folders, you need to navigate to the Folder and Drive keys:

HKEY_CLASSES_ROOT\Folder\shellex\ContextMenuHandlers

HKEY_CLASSES_ROOT\Drive\shellex\ContextMenuHandlers

All you have to do is follow the same procedure as for Files - either disable or delete items you wish to remove.

Adding Items

Adding Items to the right click menu of Files and Folders is also fairly simple using the Registry. It just involves the creation of a few new keys for each item you wish to add. You edit the same keys used for removing items. Let's use Notepad as an example of an item you'd like to add to the right click menu of all your files or folders.For folders, go to this key:
HKEY_CLASSES_ROOT\Folder
Click the + sign next to Folder and expand it so that the Shell key is visible. Right click the Shell key and choose New>Key and name the key Notepad or whatever else you'd prefer (whatever the key is named is what will appear in the right-click menu). Now right click the new key you made and create another key named Command. Then, in the right hand pane, double click "Default" and enter Notepad.exe as the value.Exit the registry, refresh, and right click any folder. Notepad should now be on the context menu.For files, go here again:
HKEY_CLASSES_ROOT\*
Expand the * key and see if a Shell key exists. If it does exist, follow the same procedure as for folders. If it does not exist, you'll have to create a new Shell first. Just right click the * key and choose New>Key and name it Shell. Then right click the Shell key and continue on the same way you did for adding items to the right click menu of folders.
Once done, Notepad should appear as an option in the right click menu of all your files.

How to run Oracle Forms 10g applications on vista???

To make Oracle forms Services to run on Vista you need to have Oracle forms version 10.1.2.3.0. For more information follow the below link for software matrix
http://www.oracle.com/technology/products/forms/htdocs/10gR2/clientsod_forms10gR2.html

To upgrade to Oracle Forms 10.1.2.3.0 and Webutil Version 10.1.2.3 follow the below steps:

Step 1:
--------
To upgrade Oracle Application Server to 10.1.2.3 .0 follow Note number: 329361.1 on http://metalink.oracle.com .
This note explains all the steps and patches needed to be applied to make Oracle Application Server 10.1.2.3.0.
Patch 4505133 to upgrade Database homes to 10.1.0.5.
Patch 5983622 to upgrade Middle and infrastructure tier to 10.1.2.3.0

Step 2:
--------
After that you need to follow Note Number 421930.1 on http://metalink.oracle.com .
This note explains how to obtain FRMWEBUTIL.JAR file from the IAS/IDS Patchset kit (10.1.2.x). This file is needed when you upgrade the IAS from 10.1.2.0.2 to 10.1.2.2/3 to fix the Webutil issues.
Current signature for frmwebutil.jar from 10.1.2.3 patch expires on January 2009. You can get a new frmwebutil.jar that is signed with a new certificate that is valid beyond January 2009 from Patch 7384879 . Please pay attention to the version you download 10.1.2.2 or 10.1.2.3.

Step 3:
--------
Configuration of your Application on the Application Server.
Jinitiator will not work with Vista so you have to change your application setting to use Sun JRE. You should use SUN JRE version 1.6 to make Application run to Vista.

Default.env file
Add “rt.jar” and “frmall.jar” paths in your CLASSPATH. Your CLASSPATH should look like below.

CLASSPATH=E:\10g_mid\j2ee\OC4J_BI_Forms\applications\formsapp\formsweb\
WEB-INF\lib\frmsrv.jar;E:\10g_mid\jlib\repository.jar;E:\10g_mid\jlib\ldapjclnt10.jar;
E:\10g_mid\jlib\debugger.jar;E:\10g_mid\jlib\ewt3.jar;E:\10g_mid\jlib\share.jar;
E:\10g_mid\jlib\utj.jar;E:\10g_mid\jlib\zrclient.jar;E:\10g_mid\reports\jlib\rwrun.jar;
E:\10g_mid\forms\java\frmwebutil.jar;
E:\10g_mid\jdk\jre\lib\rt.jar;E:\10g_mid\forms\java\frmall.jar

Formsweb.cfg
Your Application configuration should look like below

WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljpi.htm
baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar
archive=frmall.jar
lookAndFeel=oracle
workingDirectory=E:\webutil demo
form=WU_TEST_106.fmx
userid=username/password@host string
pageTitle=Webutil Demo
jpi_classid=clsid:CAFEEFAC-0016-0000-0011-ABCDEFFEDCBA
# Parameter related to the version of the Java Plugin
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6u10-windows-i586.cab#Version=1,6,0,11
# Parameter related to the version of the Java Plugin
jpi_mimetype=application/x-java-applet;jpi-version=1.6.0_11

After performing the above steps run your application on Vista with Admin Privileges for the first time as it will download some .dll’s from the Application Server to the Client Machine for webutil purpose. After that you can run the application as a Normal User on Vista.

Please leave comment if you still have any issues.

How to attach calender to a field in oracle apps?

Steps to attach calendar to a field

1. Go to properties of that field and put the lov as ENABLE_LIST_LAMP.
2. Put the validate from list in that properties NO.
3. Put the KEY-LISTVAL trigger on that item and in that write clender.show ();

Code for calling oracle report and oracle form in oracle apps

The below is the code to call oracle report from oracle form in oracle apps.

DECLARE
v_order_imp_id number;
v_admno VARCHAR2(20);
begin
v_admno := :STD_PER_INFO_50448.ADMNO;
v_order_imp_id:= fnd_request.submit_request ('CUSTOM','EMP98','hi', NULL, FALSE,
v_admno,'','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','');
COMMIT;
MESSAGE ('Your Request Id is'v_order_imp_id);
MESSAGE ('Your Request Id is'v_order_imp_id);
END;

The below is the code to call oracle form from oracle form in oracle apps.

FND_FUNCTION.execute(‘Function Name you want to call and function should exist in your menu’);

ORA-12514 TNS: listener does not currently know of service requested in connect

Unable to connect to Oracle remotely,
ORA-12514 TNS: listener does not currently know of service requested in connect

Problem Description:

Problem accessing an Oracle instance remotely. Suppose we have two systems/machines, one is running Oracle 10g database server, the other has oracle 10g client software installed. The problem is accessing the instance "asdb" from the client machines using sqlplus.

We can access the instance on the server, using sqlplus. However, from client, I'm getting the following message:
======================================================
% sqlplus username/password@asdb
SQL*Plus: Release 10.1.0.3.0 - Production on Wed May 4 23:03:04 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connectdescriptor

Tnsping reveals that the Net8 connectivity is OK.

Now the question is how can i connect to the "asdb" instance remotely?

Solution:

1. Copy the exact entries of tnsnames.ora from server to client machine.

2. Check the host entry in tnsnames.ora and /etc/hosts file. Both should have same entries for Client machine.

3. If still problem persists, Please check carefully your service name. You can do that by login to your server using SQLPLUS using any account and execute the following command:
SQL> show parameter service_name
Copy the value and set it in SERVICE_NAME of your TNSNAMES.ORA file.

Hope this helps....