How to Resolve Concurrent Manager Performance Issue

Concurrent Manager is the main part of an oracle EBS so we have to be proactive with Concurrent Manager's performance issue so best is to schedule below twice in a month or as required for getting optimum concurrent manager.



Gather schema stats request with parameters Schemas = All, Percentage = 99

Analyze all index columns  submit it with default options 

purge concurrent data keep only 7 days request log/output and delete old ones.

Gather table stats on the following tables and give parameter Schema= APPLSYS and percentage=99

FND_CONCURRENT_PROCESSES

FND_CONCURRENT_PROGRAMS

FND_CONCURRENT_REQUESTS

FND_CONCURRENT_QUEUES

How To Force All Applications Users To Change Their Password

In real time environments, we might have the requirement to force all apps users to change their passwords after certain period of time so we can force them to do so by using below.

Either to run Sql Script like below or same you can achieve by submitting concurrent request.

sqlplus -s APPS/<pwd> @AFCPEXPIRE.sql
or
Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords

Above script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program.  The user will need to create a new password upon the next login.


For further detail please see Metalink ID # 414976.1

How to find APIs in E-Business suite

Following script will help you to find all the packages related to APIs in Oracle applications, from which you can select APIs that relates to your required module. You can change the name like to PA or AR or FA etc and can check for different modules


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by
a.owner, a.name;

Usefull Metalink IDs for managing an Oracle E-Business Suite

Helpfull metalink notes.

How To Enable Debug On The Workflow Functionality [ID 1324952.1]
How to Run Script wfver.sql and Collect its Output ? (Doc ID 1364295.1)
Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test (Doc ID 748421.1)
 (and for 11i10RUP4 and above)
Configuring the Oracle Workflow 2.6/11i.OWF.H Java-based Notification Mailer with Oracle Applications 11i (Doc ID 268085.1) --similar in R12
How to Run Script wfmlrdbg.sql and Collect its Output ? (Doc ID 1364300.1)
Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException
(Doc ID 602971.1)

How To Make Workflow Notification Mailer Faster To Retry Sending The Notifications?
(Doc ID 828936.1)
Excessive CPU/Resource Usage By Advance Queue Related SQL in Applications 11i
(Doc ID 273848.1)

Note 452120.1/(Note 329985.1) How to locate the log files and troubleshoot RapidWiz
Note 802195.1/(Note 289821.1) MD5 Checksums for Rapid Install Media

Note 603104.1/(Note 364565.1) Troubleshooting RapidClone issues with Oracle Applications
Note 406982.1/(Note 230672.1) Cloning Oracle Applications with Rapid Clone

Note 260887.1 Steps to Clean Nonexistent Nodes/IP Addresses from FND_NODES
Note 387859.1/(Note 165195.1) Using AutoConfig to Manage System Configurations
Note 804603.1 How to find location of Install, Autoconfig, Patching, Clone in R12

Note 422419.1 How To Enable and Collect Debug for HTTP, OC4J and OPMN in R12
Note 335538.1 HTTP Tunneling and HTTP Headers Debugging
Note 742107.1 How To Clear Caches (Apache/iAS, Browser, Jinitiator, Java,etc)
Note 454178.1 Oracle Application Server Diagnostic Tools and Log Files in R12
Note 364439.1 Tips and Queries for Troubleshooting Advanced Topologies
Note 438652.1 Forms Runtime Diagnostics (FRD),tracing and logging For Forms
Note 975459.1 Oracle Application Framework Profile Options R12.1.2
Note 362851.1 Guidelines to setup the JVM in Apps Ebusiness Suite
Note 557194.1 monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite

Note 225165.1 Patching Best Practices and Reducing Downtime
Note 550654.1 How to Get The Patchset Level of Oracle Applications
Note 139684.1 Oracle Applications Current Patchset Comparison Utility
Note 443761.1 How to check if a certain Patch was applied to Oracle Applications

Note 215527.1 Master Reference List for the Maintenance Wizard

Note 740767.1/(Note 268837.1) Gathering Debug OAM Log In Oracle Applications
Note 976188.1 Patch Wizard Utility

Note 560350.1 How to trace Applications Management Pack (AMP) related
Note 1065774.1 Troubleshooting ACP - Setup Manager Issues

Note 235307.1 E-Business Diagnostic Tools FAQ and Troubleshooting Guide
Note 358831.1 eBusiness Suite Diagnostics Execution Instructions
Note 421245.1 E-Business Suite Diagnostics References for R12

Note 174605.1 bde_chk_cbo.sql - Reports Database Initialization
Note 396009.1/(Note 216205.1) Database Init. Parameters Oracle Applications
Note 169706.1 Oracle RDBMS on AIX, HP-UX, Solaris, Tru64, Linux etc...
Note 742477.1 How to create, update or rebuild the Central Inventory for R12
Note 376442.1 Master Note: Recommended Method for Obtaining 10046 trace
Note 219968.1 SQL*Net, Net8, Oracle Net Services - Tracing and Logging
Note 104457.1 Invalid Objects FAQ
Note 215187.1 sqltxplain.sql Enhanced Explain Plan and related

Note 1072636.1/(Note 1072807.1) E-Business High Availability Documentation Roadmap
Note 783188.1 Certified RAC Scenarios for E-Business Suite Cloning

Note 404954.1 How to run OATM migration utility
Note 269293.1 Oracle Applications Tablespace Model FAQs

Note 560719.1 How to Troubleshoot iSetup issues
Note 843397.1 Oracle iSetup Framework Developer's Guide, R12.0.6 and later

Note 399789.1 NLS Frequently Asked Questions
Note 333785.1 Oracle Applications Internationalization Guide
Note 393861.1 Globalization Guide for Oracle Applications R12
Note 405992.1 Oracle Applications R12 Translation Scope and Availability
Note 299080.1 Troubleshooting Incorrect Translations with FNDLOAD
Note 252422.1 Requesting Translation Synchronization Patches
Note 740834.1 R12 Troubleshooting iHelp

Note 414992.1 Using Cygwin to Maintain E-Business R12 on Windows

Note 110888.1 How to Trace Unix System Calls
Note 28720.1 Debugging shell scripts using set -x

Note 421409.1 Unbreakable Linux Enviroment check before R12 install

Note 883202.1 Minimum Baseline Patch Requirements for Extended Support 11i
Note 742617.1 How do I Generate Forms
Note 189367.1 Best Practices for Securing the E-Business Suite
Note 245974.1 Accessing ATG Content Using My Oracle Support
Note 887438.1 Useful Scripts For Finanical Applications Analysts
Note 182699.  1  Validate and rebuilds indexes occupying more space

How to Change APPS and other Applications usernames passwords

That is the most frequent requirements in any Oracle E-Business Suite environments which DBAs has to do so just follows below.

 Login on apps server with application owner like ‘appldev, applvis, applprod

 Source the environment file like “ . ./APPSDEV.env Write FNDCPASS commands on server prompt it will show you all password change detail

like FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS newpwd

      FNDCPASS apps/apps 0 Y system/manager ORACLE GL      GL1      FNDCPASS apps/apps 0 Y system/manager USER   VISION  WELCOME

 Down the apps services

 Runautoconfig on appstier

 Runautoconfig on dbtier

 Start apps services

For detail see Metalink IDs

437260.1

809155.1

How to Set up Pasta to see Oracle Standard Report output in Arabic

Output of R12 standard reports were not coming in Arabic so see the below action plan.

1- Query for the profile : %Encoding% and check the profiles :
FND: NATIVE CLIENT ENCODING ---set the Arabic Characterset
ICX: Client IANA Encoding

2- Please add the line
Text , apps/bidi , pasta viewer for text
that we have added in the test under the Install >> Viewer options

3- Please modify the pasta.cfg file under $FND_TOP/resource
make the direction=rtl
and the dolayout=n

4- In the PSTLL.prt, L.prt files under $FND_TOP/reports
add the 4 lines :

nls locale "arabic"
nls datastorageorder "logical"
nls contextuallayout "no"
nls contextualshaping "yes"

5- Query for the profile : %Viewer% and check the profiles :
Make the profile : Viewer: Application for Text to be null
Viewer: Text to be Browser

6- Bounce the concurrent managers , exit the application and login and retest.


For detail please see Metalink ID # 356501.1

Oracle Applications Desktop Integrator (ADI)

The Oracle E-Business Suite task you perform on the desktop is determined by the

integrator you select in Web ADI. Each integrator is delivered with the E-Business Suite

product that provides the functionality being integrated with the desktop. Additional

information specific to each integrator can be found in the product-specific.

We can easily upload the required data in word, excel format on our desktop by using Oracle web ADI options, simply assign oracle ADI responsibility and start using.

For Details Follow Metalink ID # 452452.1, Further details can be seen from below standard documentation.

Implementation and Administration Guide Release 12

Part No. B31411-01

Data Loading/Uploading Got stuck in Web Adi

we had this issue and we solved this with the help of below:-


Open Internet Explorer browser window.
Click on Tools Menu.
When the Tools menu appears, click "Internet Options" at the bottom of the Tools menu.
Internet Options dialog box appears as shown below. Click on the security tab.
When the Security page appears, click the "Internet" icon in the "Select a zone" box and then click the

"Custom Level.. " button.
When the "Security Settings" dialog box appears, scroll down to "Scripting" on the menu. Enable

“Allow status bar updates script.” Select OK
You may receive a Warning. Please select "Yes"
Finally, click "OK" to close the Internet Options dialog box.
Close the browser window so that new settings can be used.
Recheck the issue and migrate it to appropriate  environments.

For see Metalink ID # 459536.1

Error ORA-20002: [WF_INVAL_USER_ROLE] has been detected in while assigning new or modifying old responsibilities

We were getting below errors while assigning or modifying user responsibilities.




Oracle error-1502: ORA-01502: index
APPLSYS.WD_LOCAL_USER_ROLES_U1 or partition of such index
is in unusable state.
ORA-20002: [WF_INVAL_USER_ROLE] has been detected in
FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT.


Solution.


It has 2 solutions, for the time being you can rebuild the below indexes then this error will disappear.

Solution # 1.


select * from dba_indexes di
where di.index_name in ('WF_LOCAL_USER_ROLES_N2',
'WF_LOCAL_USER_ROLES_N4', 'WF_LOCAL_USER_ROLES_N5',
'WF_LOCAL_USER_ROLES_N6', 'WF_LOCAL_USER_ROLES_N7',
'WF_LOCAL_USER_ROLES_U1')


alter index applsys.WF_LOCAL_USER_ROLES_N4 rebuild online;
alter index applsys.WF_LOCAL_USER_ROLES_N2 rebuild online;
alter index applsys.WF_LOCAL_USER_ROLES_N5 rebuild online;
alter index applsys.WF_LOCAL_USER_ROLES_N6 rebuild online;
alter index applsys.WF_LOCAL_USER_ROLES_N7 rebuild online;
alter index applsys.WF_LOCAL_USER_ROLES_U1 rebuild online;


Solution # 2 (which is permanent one)


But after some time you will see this error again while assigning new responsibility or modifying to particular user in sysadmin so for permanent solution follow the below option:-

Stop this Concurrent request permanently because it was causing issue that was bug in 11i and it was mandatory to run below request while synchonization of workflow table but it is fixed in R12 so it is not harmful to stop below request.

Synchronize WF LOCAL tables

How to change port in E-Business Suite

Below step will help in changing EBS Apache Login page port.



1- Open your  Context XML file for edit  through OAM

2- Change the value from 8001 to be 8002 on the following lines:
      <web_port oa_var="s_webport" oa_type="PORT" base="8000" step="1" range="-1" label="Web Listener Port">8001</web_port>

      <activewebport oa_var="s_active_webport" oa_type="DUP_PORT"  base="8000" step="1" range="-1" label="Active Web Port">8001</activewebport>

    <login_page oa_var="s_login_page">http://myhost.oracle.com:8001/OA_HTML/AppsLogin</login_page>

    <chronosURL oa_var="s_chronosURL">http://myhost.oracle.com:8001/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</chronosURL> 

    <oa_var="s_endUserMonitoringURL">http://myhost.oracle.com:8001/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</EndUserMonitoringURL>

   <externURL oa_var="s_external_url">http://myhost.oracle.com:8001</externURL>

  <httplistenparameter oa_var="s_http_listen_parameter">8001</httplistenparameter> 

3- Save your settings

4- Run Autoconfig for changes to take place

5- Try to login again through:
        http://myhost.oracle.com:8002
    The URL should be displayed successfully


For more detail, please follow metalink ID #  760590.1

How to Create Custom TOP in E-Business Suite

This is recommended to create your own Custom Top for all your custom needs hence it will make DBAs life easy while up-gradation of EBS.

1)  Make the directory structure for your custom application files.

 cd $APPL_TOP i.e /u01/appldev/apps/apps_st/appl

 mkdir xxaks
 mkdir xxaks/12.0.0
 mkdir xxaks/12.0.0/admin
 mkdir xxaks/12.0.0/admin/sql
 mkdir xxaks/12.0.0/admin/odf
 mkdir xxaks/12.0.0/sql
 mkdir xxaks/12.0.0/bin
 mkdir xxaks/12.0.0/reports
 mkdir xxaks/12.0.0/reports/US
 mkdir xxaks/12.0.0/forms
 mkdir xxaks/12.0.0/forms/US
 mkdir xxaks/12.0.0/$APPLLIB
 mkdir xxaks/12.0.0/$APPLOUT
 mkdir xxaks/12.0.0/$APPLLOG

2)  Add the custom module into the environment:

Add the entry in “Topfile.txt” as a standard product top entry (follow the existing model in the file)  

3) Customized environment variables needed to add in Source files.

Cd $APPL_TOP

vi customdev_dev.env or vi customprod_prod.env
XXAKS_TOP="/u01/appldev/apps/apps_st/appl/xxaks/12.0.0"
export XXAKS_TOP

SAVE THE FILE.

4) RUN AUTOCONFIG. ($INST_TOP - down apps services firsts then run on db tier then on apps tier)

Double check,

If you run $echo $XXAKS_TOP it should reflect the path if paths are set in profiles.

5)  Create new tablespace for database objects as sys or system user.

 Create tablespace xxaks datafile '/u01/oradev/db/apps_st/data/xxaks.dbf' size 500M   default storage(initial 10k next 10k)

6) Create schema

  Sql>create user xxaks identified by xxaks
  default tablespace xxaks
  temporary tablespace temp
  quota unlimited on xxaks
  quota unlimited on temp;

 sql>grant connect, resource to xxaks;

7)  Register your Oracle Schema.

 Login to Applications with System Administrator responsibility
 Navigate to Application-->Register
 Application             = xxaks Custom
 Short Name            = xxaks
 BasePath                = XXAKS_TOP
 Description             = xxaks Custom Application

8)  Register Oracle User

 Navigate to Security-->Oracle-->Register
 Database User Name  = xxaks
 Password                   = xxaks
 Privilege                     = Enabled
 Install Group              = 0
 Description                 = xxaks Custom Application User

9)  Add Application to a Data Group

 Navigate to Security-->Oracle-->DataGroup
 Query the” Data Group  = standard”

 Insert the following lines.

 Application = xxaks Custom
 Oracle ID   = APPS
 Description = xxaks Custom Application

Run a report/ concurrent program from the OS level

In special circumstance we might have to run a report/ concurrent program from the Operating System level without having to log on to Oracle Applications, you have to use CONCSUB utility for this purpose.

This utility can be found at $FND_TOP/bin

[applvis@oracle]$ find . -name CONCSUB
/u01/applvis/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB

Here are the CONCSUB Parameter Arguments:

NOTE: In green you can see the mandatory parameters.

<APPS username>/<APPS password> \
<Responsibility Application Short Name> \
<Responsibility Name> \
<User Name> \
[WAIT=N|Y|<n seconds>] \
CONCURRENT \
<Program application short name> \
<Program Name>  [PROGRAM_NAME=”<description>”] \
[REPEAT_TIME=<resubmission time>] \
[REPEAT_INTERVAL=<number>] \
[REPEAT_INTERVAL_UNIT=<resubmissionunit>] \
[REPEAT_INTERVAL_TYPE=<resubmissiontype>]
[REPEAT_END=<resubmission end date andtime>] \
[START=<Requested Start Date>] \
[REPEAT_DAYS=<number of days>] \
[IMPLICIT=<type of concurrent request> ] [PRINTER=<name of printer>] \
[NUMBER_OF_COPIES=<number of copies>]

Default values:

WAIT=N

REPEAT_INTERVAL_UNIT=DAYS

START = current time

IMPLICIT=NO

Example:

[applvis@oracle]$ cd $FND_TOP/bin
[applvis@oracle bin]$ Source your APPS Environment file.

[applvis@oracle bin]$ ./CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN WAIT=N CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 2720951 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"

This report is the equivalent of :

 

NOTES: 

·                    the parameters are added at the end of the command

·                    The log and out file for this program is created at the $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT 

·                    for a NULL parameter we have to use '""'

·                    WAIT=Y/N is used to specify whether to wait for the first concurrent request to be completed before the second is submitted or not.

·                    CONCSUB utility could be used to manage the Concurrent Managers:

          Startup the ICM:

          cd $FND_TOP/bin

          startmgr.sh sysmgr=apps/apps@VIS
 

          Shutdown the ICM:

CONCSUB apps/apps SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN

CONCSUB apps/apps SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND ABORT   

R12 System Architecture

Base Directory: The directory where the Oracle Applications is installed. This is a generic directory. 

                          
 I will suppose that the Base Directory is /APPS

$ORACLE_HOME (for the database tier): /APPS/db/tech_st/10.2.0

$APPL_TOP:   /APPS/apps/apps_st/appl

$COMMON_TOP:  /APPS/apps/apps_st/comn

$ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.2

$IAS_ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.3

$INST_TOP:   /APPS/inst/apps/<SID_localhost> contains certifications, configurations, logs for the servers.

Oracle eBusiness Suite Electronic Technical Reference

For Technical Reference of EBS objects, please go through below site with proper metalink userid.

http://etrm.oracle.com/

Download Oracle APPS from edelivery

We can downlaod all oracle software from below link.

http://edelivery.oracle.com

Import/Export in Oracle Database

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files. It is the least option of backup and recoveries but it is useful in different scenarios.

 Modes of Import/Export

a) Full export/export

       The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. 

b) Tablespace
Use the tablespaces export parameter for a tablespace export.

c) User
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import. 

d) Table
Specific tables (or partitions) can be exported/imported with table export mode. Use the tables export parameter for a table export/ import mode. 

 Possibility of exp/ imp to multiple files

Yes, is possible. Here is an example:  

exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log

 Use of exp/ imp in different Oracle database versions

·         exp must be of the lower version

·         imp must match the target version

Steps before importing database objects

Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.  If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import.
  

Possible to import a table in a different tablespace

By default we do not have possibility do to this. Because there is no tablespace parameter for the import operation.

However this could be done in the following manner:

·                    (re)create the table in another tablespace (the table will be empty)

·                    import the table using INDEXFILE parameter (the import is not  done, but a file which contains the indexes creation is generated)

·                    modify this script to create the indexes in the tablespace we want

·                    import the table using IGNORE=y option (because the table exists)

·                    recreate the indexes

 Frequent use of  imp/exp

Eliminate database fragmentation

·                    Schema refresh (move the schema from one database to another)

·                    Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)

·                    Transporting tablespaces between databases

·                    Backup database objects

Enhancement of EXPORT performance

·                     Set the BUFFER parameter to a high value (e.g. 2M)

·                     If you run multiple export sessions, ensure they write to different physical disks. 

Enhancement of IMPORT performance

·         Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes

·         Store the dump file to be imported on a separate physical disk from the oracle data files

·         If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import

·         Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)

·         use the direct path to import the data (DIRECT=y)

·         (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init<SID>.ora file

·         (if possible) Set the LOG_BUFFER to a big value and restart oracle.

  Available EXP options

·         Keyword    Description (Default)      Keyword      Description (Default)

·         --------------------------------------------------------------------------

·         USERID     username/password          FULL         export entire file (N)

·         BUFFER     size of data buffer        OWNER        list of owner usernames

·         FILE       output files (EXPDAT.DMP)  TABLES       list of table names

·         COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record

·         GRANTS     export grants (Y)          INCTYPE      incremental export type

·         INDEXES    export indexes (Y)         RECORD       track incr. export (Y)

·         DIRECT     direct path (N)            TRIGGERS     export triggers (Y)

·         LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)

·         ROWS       export data rows (Y)       PARFILE      parameter filename

·         CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

·        

·         OBJECT_CONSISTENT    transaction set to read only during object export (N)

·         FEEDBACK             display progress every x rows (0)

·         FILESIZE             maximum size of each dump file

·         FLASHBACK_SCN        SCN used to set session snapshot back to

·         FLASHBACK_TIME       time used to get the SCN closest to the specified time

·         QUERY                select clause used to export a subset of a table

·         RESUMABLE            suspend when a space related error is encountered(N)

·         RESUMABLE_NAME       text string used to identify resumable statement

·         RESUMABLE_TIMEOUT    wait time for RESUMABLE

·         TTS_FULL_CHECK       perform full or partial dependency check for TTS

·         VOLSIZE              number of bytes to write to each tape volume

·         TABLESPACES          list of tablespaces to export

·         TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

·         TEMPLATE             template name which invokes iAS mode export 

Example: exp system/s              file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

      or      exp userid=system/s file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

USERID must be the first parameter on the command line.

  

Available IMP options

Keyword  Description (Default)       Keyword      Description (Default)

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

USERID   username/password           FULL         import entire file (N)

BUFFER   size of data buffer         FROMUSER     list of owner usernames

FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames

SHOW     just list file contents (N) TABLES       list of table names

IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record

GRANTS   import grants (Y)           INCTYPE      incremental import type

INDEXES  import indexes (Y)          COMMIT       commit array insert (N)

ROWS     import data rows (Y)        PARFILE      parameter filename

LOG      log file of screen output   CONSTRAINTS  import constraints (Y)

DESTROY                overwrite tablespace data file (N)

INDEXFILE              write table/index info to specified file

SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)

FEEDBACK               display progress every x rows(0)

TOID_NOVALIDATE        skip validation of specified type ids

FILESIZE               maximum size of each dump file

STATISTICS             import precomputed statistics (always)

RESUMABLE              suspend when a space related error is encountered(N)

RESUMABLE_NAME         text string used to identify resumable statement

RESUMABLE_TIMEOUT      wait time for RESUMABLE

COMPILE                compile procedures, packages, and functions (Y)

STREAMS_CONFIGURATION  import streams general metadata (Y)

STREAMS_INSTANTIATION  import streams instantiation metadata (N)

VOLSIZE                number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

Example:  imp system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs     (Unix)

   or          imp userid=system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs   (Unix)

Note: USERID must be the first parameter on the command line.

 Common IMP/EXP errors

·                     ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..

·                     IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

·                     ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).

·                     ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter ) while importing.