Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Thursday, December 27, 2012

impdp to Import Packages but No Overwrite / Create or Replace

Oracle say that you should be using impdp for all 11g import operations.

If you’re using impdp to import a dump file which contains some packages, you need to be aware that when you run an import with impdp, the package source is not automatically recreated i.e. your export will have exported the package create header with “CREATE PACKAGE ...” and not “CREATE OR REPLACE PACKAGE ...”.

Consequently, when you run the import of the package(s), if they already exist in the database, they will not be overwritten or replaced.
There is no command line option for impdp to change this feature (like “table_exists_action=replace” for TABLES).
You have to manually extract the package source from the exported dump file using the impdp “SQLFILE” command line option, which will create a separate SQL file containing the package sources.
Then modify the generated script file to change the “CREATE PACKAGE ...” to “CREATE OR REPLACE PACKAGE ...”.

If you're running on UNIX, you can use the power of SED to accomplish this task:

> impdp <user>/<password> DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=scriptfile.sql

> cat scriptfile.sql | sed ‘s/^CREATE PACKAGE/CREATE OR REPLACE PACKAGE/’ > scriptfile2.sql

NOTE: You need the “^” in the command so that it only replaces the first occurrence of “CREATE PACKAGE” on a line, otherwise you might replace something you need.

Thursday, December 20, 2012

Estimating Oracle Export Space Requirements

To estimate a full Oracle export space requirements, you can use DataPump.
The below command estimates for a full export of the database.
It makes use of the ESTIMATE_ONLY option on the expdp command line.

First you need to create your directory in the database:

> sqlplus / as sysdba

SQL> create directory dp_dump as '/your_path';

SQL> quit;

> expdp \"/ as sysdba\" full=y directory=DP_EXPORT logfile=full_exp_estimate.log estimate_only=yes

Total estimation using BLOCKS method: 2.418 GB

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 18:50:48

The method above runs through each object to be exported and calculates the number of blocks relevant to the block size of each object, that will be required on disk.

If you have a large database and your statistics are up-to-date, then you could use the additional “ESTIMATE=STATISTICS” option, which uses the data gathered from the Oracle statistics collections to estimate the space required. This is a lot quicker but needs accurate stats.

The example above took 1 min.

With the “ESTIMATE=STATISTICS” option, it took 46 seconds, but estimated only 991.3 MB would be required (half as much as the BLOCKS method).  There’s obviously some missing stats on objects in my DB.

Friday, December 14, 2012

HowTo: Find the Transaction Codes used in SPRO

It's not easy trying to grant specific access to customising transactions when you;re not sure which ones will be required.

If you know they are accessible from SPRO, then you can find them using the following query, which lists TCODES from SPRO customising that has been performed after installation (note the date of 2001):

SELECT distinct cobj.tcode
FROM cus_actobj cobj,
cus_acth cacth
WHERE cobj.act_id = cacth.act_id
AND cacth.fdatetime > '20019999999999'

Sunday, December 09, 2012

Controlling FUGR Access

If you have a super user role in your production system, it’s quite possible that use of the SUPRN_INS_OR_DEL_PROFILE or BAPI_USER_PROFILES_ASSIGN function modules could be used from SE37 to give SAP_ALL and then proceed to get around other restrictions.

Since SUIM relies upon S_DEVELOP and activity 16, you can’t just remove it unless you leave the admins to find their own way to the underlying transactions.
Instead, you could lock down the S_DEVELOP authorisation object by removing access to the specific function group (FUGR).

When editing the authorisations of a role, set one of the S_DEVELOP authorisation objects to exclude FUGR (and DEBUG):
Activity = 03 & 16 (Display & Execute)
Package = *
Object Type =
4              to DE
DEVC     to FU
FUGS     to Z
Z              to $TM

Then add another S_DEVELOP authorisation object “MANUALLY”.
Set this to exclude the SUPR, SUPRN and SU_USER function groups:

Activity = 03 & 16 (Display & Execute)
Package = *
Object Name =
0              to SUNI
Z*           to $*
Object Type = FUGR