Register Login

Oracle Database Interview Questions and Answers

Updated May 18, 2018

1. On which component can I open an SAP message if there are problems with the Oracle database?

The following components are available:

BC-DB-ORA: Oracle

BC-DB-ORA-DBA: BR*TOOLS for the database administration

BC-DB-ORA-CCM: Problems with CCMS functions in the Oracle area

BC-DB-ORA-INS: Installation of the Oracle database

BC-DB-ORA-RAC: Oracle Real Application Cluster

BC-DB-ORA-SYS: DBI and DBSL Oracle functions

BW-SYS-DB-ORA: Oracle problems in the BW area

2. Where can I find the Oracle Alert Log?

You can find the Alert Log in the R/3 environment in the following directory:

UNIX: $SAPDATA_HOME/saptrace/background
WINDOWS: %SAPDATA_HOME%saptracebackground

The file name is alert_.log, < sid>alrt.log or similar.

3. Why are some Oracle errors not logged in the Alert Log?

Oracle only logs critical errors in the Alert Log. Most of the Oracle error codes are not logged (unfortunately this may also include error codes that are genuinely critical). It is therefore normal for you to find errors in the Oracle application that are not displayed in the Alert Log.

4. How can I prevent the Alert Log from becoming very large?

 Note 786032 describes how you can ensure the Alert Log remains within its limits by regularly renaming the file.

5. How do I obtain an explanation of Oracle error codes?

You can use the OERR tool on UNIX to obtain more exact information about a - error code. For the ORA-01555 error, you can call the tool as follows:

oerr ora 1555

Alternatively, the Oracle online documentation contains a complete overview of all error messages including explanations (in the 9i documentation under "Error messages", for example).

6. How I can determine which SQL statement triggered an Oracle error?

You can generally look in the corresponding log or trace file to find out which SQL statement has triggered an error. If this is not possible in your particular case, you can activate a more precise logging process in Oracle for a specific error code (for example, tracing for ORA-00942 -> = 942):

   errorstack forever, level 1';

              If an SQL statement now triggers an error, Oracle creates a detailed trace file in the user trace directory. This file includes, among other things, the triggering SQL statement.

7. What are the parameter recommendations for Oracle?

See Note 124361 and, depending on the product you are using, the notes that are referenced in the note (for example, Note 180605 for BW).

For Oracle 10g and higher, see Note 830576.

8. Where can I find the Oracle parameter file (init.ora or spfile.ora)?

You can find the init.ora parameter file under

WINDOWS: database

If you use a SPFILE with Oracle 9i or higher, you can also find spfile.ora in this directory.

9. Why are changes that I made in init.ora not implemented?

If changes in init. ora are not implemented by Oracle, check the following points:

a) To activate the changes, you must restart Oracle.

b) Do not use an SPFILE. Otherwise, the changes must be executed in the SPFILE instead of in init.ora (see Note 596423).

c) Parameters must not be defined twice. In this regard, check to see whether there are IFILE entries that include additional parameter files. If a parameter is defined in these files, this setting may overwrite changes to the parameter of the same name in init.ora. It is a good idea to copy all parameters to init.ora and delete the IFILE entries.

d) If several event entries are specified, these must be found in consecutive rows of init.ora.

10. Which combinations of Oracle, R/3 and operating systems are permitted?

 Depending on the R/3 kernel release, the supported combinations are described in the following notes:

Note 23875 (3.x)

Note 85838 (4.0x/4.5x)

Note 156548 (4.6x)

Note 407314 (6.x)

11. Where can I find the Oracle installation or upgrade CDs?

To install the Oracle software, use only the Oracle RDBMS CDs delivered by SAP. You must not use CDs obtained directly from Oracle.

              For example, you can order the RDBMS CDs by opening a message on component XX-SER-SWFL-SHIP. It is important that you correctly specify the database release and operating system.

12. Where can I find information about the Oracle upgrade?

The sequence of an Oracle upgrade is described in the Oracle upgrade guides for the relevant Oracle target release and operating system (these guides are available on SAP Service Marketplace under the quick link "instguides"):
-> Other Documentation
-> Database Upgrades
-> Oracle

              The guides also contain all notes that are required for the upgrade.

              The Oracle upgrade is not integrated into the upgrade of other components such as R/3, but can always be carried out separately instead.

              Note 551542 contains further information about the Oracle upgrade.

13. Why is a short dump written during an offline backup when the R/3 system is running?

The current R/3 system cannot know that BRBACKUP stops the database during an offline backup. Therefore, the first work process that loses the connection to the database writes a short dump. All work processes then go into reconnect mode until the database is available again. Consequently, it is normal that one (or more) short dumps are written during an offline backup, because the database cannot be accessed. For more information about the reconnect mechanism, see Note 98051.

14. Can I use backup/restore to copy an Oracle database to a system with a different software level?

If possible, the source and target software should be identical as far as the release, patch set and bug fixes are concerned. However, under the following restrictions, you can also make a copy on another software level:

Switch to the identical target release with a different patch set status or different bug fixes:

           Since changes to the Oracle ABAP are sometimes carried out within the framework of patch sets and bug fixes, the patch set and bug fix status should be identical when you change to an identical target release. Otherwise, strange errors can occur if the Oracle ABAP already contains a change but the relevant patch has not yet been installed in the target system (for example, ORA-00947 by SMON, if the umonmods.sql change from Note 692214 is already active but the patch is still missing in the target system). Patches that do not make any changes to the Oracle ABAP are referred to as rolling patches and can, in accordance with Note 306408, be identified by means of "opatch query -is_rolling".  For all non-rolling patches, the relevant change scripts must be used to implement the ABAP changes manually. If the target system contains fewer patches and fixes, it is very difficult to carry out the "downgrade" of non-rolling patches that is required as a result.

           Due to the likelihood of errors occurring, we advise you not to use these procedures.

Switch from 32-bit to 64-bit and vice versa if you have identical source and target releases

           After the copy, you must execute utlirp.sql (or utlip.sql and utlrp.sql) on the target system as described in Note 192822.

Switch from Release 8.x to a more recent Release 8.y

           After the copy, you must execute the relevant upgrade script from $ORACLE_HOME/rdbms/admin on the target system. The name of the script is derived from the Oracle source release, which corresponds to the third, fifth and seventh characters of the name (for example, u0800060.sql for 8.0.6 source release or u0801050.sql for 8.1.5 source release).

Switch from 8.1.7 to 9.x

           After the copy, you must execute all of the upgrade steps that are described in the guide for upgrading to Oracle 9.x. In the case of WINDOWS, proceed according to the manual method as described in Note 635974.

Switch to a newer patch set of the same release

           After the copy, you have to carry out all of the steps that have to be carried out when you install the patch set.

              Several points can also be connected (for example, a change from 8.0.6 (32-bit) to 8.1.7 (64-bit). The only essential step is that postprocessing is carried out fully.

15. Between which operating systems are homogeneous system copies and the structure of a standby database scenario possible?

Homogeneous system copies may only be carried out between systems with the same operating system. Standby databases must also run on the same operating system as the primary database. Differences within an operating system are generally allowed:

ITANIUM vs. non-ITANIUM; with the exception: On HP-UX, standby databases and system copies with online backups are not allowed between ITANIUM and non-ITANIUM.

Different releases (for example, AIX 4.3 versus AIX 5.2); with the exception: you cannot combine HP-UX 10.20 and HP-UX 11.0 or higher.

You can only change from 32-bit to 64-bit or vice versa on the basis of an offline backup, while the use of an online backup is not supported. You are also not allowed to restart a 64-bit standby database with a 32-bit primary database and vice versa.

16. How can I adjust the new tablespace and user names when backup/restore is used to carry out a system copy?

If the new database layout is used with the user SAP (instead of SAPR3) and tablespace names with an integrated SID, these names remain unchanged after backup/restore is used to carry out a system copy. If, for example, you copy a system with SID PRD to a system with SID DEV, users and tablespaces in DEV have "PRD" in their names instead of "DEV". This is confusing.

              This situation is normal, it does not cause any technical problems and, at most, may cause some confusion with regard to administration.

              However, one possible solution is to define an SID-independent schema ID as described in Note 617444.

17. Can I perform transports between the databases of different releases?

Since the TP and R3TRANS transport tools work with a database-independent data format, transports can be performed between databases with different Oracle releases.

18. Which Oracle releases are currently supported?

Of the Oracle releases released by SAP, Oracle only supports 9.2 and 10.2. Oracle will provide free support for 9.2 up to 07/31/08. See Notes 1021844 and 1110995. Oracle 10g is supported by Oracle until July 7, 2010.

19. When will SAP support Oracle 10g?

See Note 720886 for further information.

20. Where can I find information about Oracle patch sets and bug fixes?

 A bug fix (also known as an interim patch as of Oracle 9i) is a correction provided by Oracle (depending on the patch set used) to eliminate a particular problem. A patch set is a collection of corrections provided as a unit. The current patch set version is logged in the fourth digit of the Oracle release specification (for example, Oracle -> patch set 3 installed for Oracle 9.2.0). Bug fixes always depend on the current patch set version. If there is a bug fix for a patch set version, the fix is usually already contained in the next-highest patch set.

              Notes 539921 (9.2) and 871735 (10. 2) contain information about the Oracle patch sets currently released by SAP. Notes 938986 (9.2),  and 871096 (10.2) contain information about important bug fixes.

21. Which Oracle patches should generally be imported?

We recommend that you always use the latest patch set. However, since bug fixes may have negative side effects, you should not import them without making absolutely sure that they are essential.

22. How does changing the system time affect Oracle (for example, when daylight saving time is switched to standard time, and vice versa)?

 Internally, Oracle works with System Change Numbers (SCN) and not with real time stamps. Therefore, a system time change does not have any impact on Oracle, which means that - from a database point of view - you do not need to stop Oracle to carry out a time change. However, you should bear the following restrictions in mind:

If a time interval occurs twice as the result of a time change, the system cannot determine at which of the two identical points of time it should stop when you perform a point-in-time recovery with an end time in the duplicate interval. However, in this scenario, you can carry out the recovery with a target SCN instead of a target time.

If you carry out an RMAN backup during a time change, so that the start time of the backup is after the end time, subsequent "list backup" commands may terminate with errors such as ORA-01455 because the RMAN function cannot handle this constellation. You can solve this problem by recreating the control files that contain the backup information.

If you do not convert the system time on WINDOWS both on the database server and on the domain controller, the KERBEROS authentication with SQLPLUS or SVRMGRL can fail because the time variance is too large, and the problems described in Notes 614036 and 620540 occur.

If you convert the time zone itself, the conversion may affect columns of the type "TIMESTAMP WITH LOCAL TIME ZONE" or " TIMESTAMP WITH TIME ZONE". These columns are not used in the SAP environment, but may appear in the Oracle ABAP as of Oracle 10g. In this case, you may need to make certain changes when you change the time zone. Refer to Note 1019133 for more information about the "Daylight Saving Time" change in the USA in 2007.

23. How can I access remote Oracle databases from the R/3 system?

 In addition to standard solutions such as database links, you can - as of R/3 4.0B - define a secondary database connection (as described in Notes 323151 and 339092) to access remote Oracle databases.  This is a good solution because you do not need to make any changes at Oracle level (except for adjustments to tnsnames.ora) and the connection is set up in the same way as the primary database connection.

24. How can I access the Oracle database of the R/3 system from an external system?

Generally, you can access the Oracle database of your R/3 system from an external system in various ways (Net8, database links, ODBC, and so on). However, you should bear in mind that license agreements often do not cover using the R/3 database in this way. For more information, see Note 581312. SAP Support is not responsible for setting up these connections.

25. Can I open Oracle in read-only mode?

Yes - for more information, refer to Note 817253.

26. What are maximum sizes permitted for data files?

For more information, see Note 129439.

27. Can I use Oracle functions that are not used in the SAP standard system?

 Note 105047 discusses the extent to which you are permitted to use functions such as Oracle Enterprise Manager (OEM), RMAN, partitioning, Index Organized Tables (IOTs), Connection Manager, Oracle names, advanced security or Oracle Data Guard.

              See also Note 740897.

              For Oracle 10g, see Note 828268.

28. Which are new features offered by Oracle 9i and to what extent may these be used?

 For more information, see Note 598678.

29. What factors determine the space occupancy of an object at database level?

The space occupancy of an object (such as a table or index) at database level is mainly determined by the quantity of data. However, in the following cases, space occupancy may be very different despite a comparable dataset:

Fragmentation due to data deletions or unfavourable PCTFREE/PCTUSED values (Note 821687)

Use of different tablespace types (dictionary-managed vs. locally-managed)

Use of different extent sizes (INITIAL, NEXT)

Use of different index types (B* tree vs. Bitmap)

30. How can I determine how the database is occupied on R/3 components (such as clients, company codes, plants)?

 Note 118823 contains reports that you can use to calculate the size of individual clients.

              There are no such reports for other structures, such as company codes, plants or sales organizations. Therefore, an evaluation of these sizes is not possible in the standard system.

31. Why is the database still occupied to the same extent after extensive data archiving?

 If data is deleted from the database, the blocks of the affected objects are cleared, but the amount of space the affected objects occupy is not reduced. To make the space that is no longer occupied available for general use again, you must reorganize the objects concerned. See Notes 572060 and 541538 for more information.

              Also refer to Note 821687, which contains detailed information about space occupancy and fragmentation.

32. How I can access R/3 pool and cluster tables at Oracle level?

Unlike transparent tables, the pool and cluster tables defined in the R/3 system do not have a direct equivalent at Oracle level. Instead, their data is stored in relevant table pools or table clusters. Only these pools and clusters have an equivalent at database level.

              The logic, which is used to extract the data of the pool and cluster tables from the table pools or table clusters, is integrated in the R/3 kernel. You do not have the option to copy this logic at Oracle level. Therefore, you can only ever access R/3 pool and cluster tables with R/3 tools.

              Also refer to Note 581312, which explains that direct access to SAP data via non-SAP interfaces is only allowed on a restricted basis.

33. Why do the Oracle processes run under the <sid>adm user on my system?

 The UNIX PS command displays the real user by default, not the actual user. Therefore, it is not a problem if the displayed user for the Oracle processes is adm. The only important thing here is that the Oracle executable has the correct authorizations, as described in Note 583861.  For more information, see Note 206916.

34. How can I access the Oracle metalink?

 For more information, see Note 758563.

35. Which CSI number can I use?

If you have acquired the Oracle license from SAP, you do not need a CSI number. You receive Oracle support from SAP and you can also access Oracle metalink as described above.

36. What must I do if I cannot use SE16 to make recommended changes in configuration tables?

For various reasons, it may be necessary to change data in configuration tables (such as DBDIFF, TAORA, IAORA). Due to system and client changeability, this is not possible, especially in live environments. However, you can proceed in one of the following ways:

Change the system changeability (SE06) and/or the client changeability (SCC4) temporarily, so that you can use SE16 to carry out a change.

Check if there is an SM30 maintenance dialog that you can use to maintain the table.

Use database tools (for example, SQLPLUS) to execute the change directly. Caution: Misuse can lead to data loss.

37. Why does the sequence of the online redo logs change sporadically?

If the next online redo log is still archived, while another redo log is available for overwriting again, this redo log becomes the next redo log. This changes the sequence of the online redo logs. This behaviour is desirable because the alternative would be an archiver stuck, at least temporarily.

This situation can only occur if several archive processes run in parallel and if they do not run again when the redo logs are archived. To avoid this problem, check if the archiver performance can be optimized (I/O tuning). Also, you must avoid an archiver stuck situation because of a fully run archive file system.

This problem cannot occur if the number of archiver processes is limited to one by LOG_ARCHIVE_MAX_PROCESSES and if you have taken into account Note 445226.

38. What common misconceptions are there about the Oracle environment?

There are several widespread misconceptions about the Oracle database (for example, ONLINE operations do not set any locks or the CBO always selects the access path with the most favorable costs). For more information, see Note 825653.

39. Can the data of the Oracle database be encrypted?

SAP does not use or support the safety measures (such as the "Advanced Security Option" or the DBMS_OBFUSCATION toolkit) that are possible at Oracle level.

At SAP level, there is also no global option available for encrypting data on the database. Instead, sensitive data is stored in the SAP pool or cluster tables so that the contents cannot be read correctly at operating system level.

In some particularly sensitive areas, SAP also provides encryption mechanisms (for example, credit card encryption -> Note 766703; encrypting payment card data -> Note 858200).

You can protect the data effectively in the database by using a restrictive authorization concept at SAP level and by using security measures in the network and operating system area.

40. How can database accesses be logged?

If you want to log certain database operations or accesses to certain tables, the trace mechanisms described in Notes 139418 and 115224 such as SQL audit or logging table changes are available in SAP.

In Oracle, you can activate auditing using parameters such as AUDIT_TRAIL or AUDIT_SYS_OPERATIONS, configure them using AUDIT commands, and deactivate them using NOAUDIT commands. For more detailed information, see the Oracle documentation.

41. What are the disadvantages of using a large database?

At first glance, a large database, which may increase significantly in size, does not have any major negative effects on productive use. Once the dimensioning of the hardware is adequate, there are hardly any performance-related disadvantages.

The main problem with a large database is that administrative handling becomes increasingly difficult. Consider the following points in relation to this:

Backup and restore runtimes can increase by a significant amount, which - in serious cases - can lead to an unnecessarily long downtime. In addition to this, an online backup that is running can negatively affect the production operation, which is why online backups should also run for as short a time as possible. Note 842240 contains information about optimizing the backup and restore processes for large databases.

The runtimes of operations on the database (such as consistency checks, database checks, reorganizations, index rebuilds or statistics creation) are relatively high and guarantee an increased system load over a longer period of time. See also Note 806554 for possible optimization measures.

Operations in the SAP system (such as archiving sessions, system copies or client operations) also take a long time if the dataset is large.

For the above reasons, it is a good idea to use a comprehensive archiving concept to limit database growth right from the start.

42. What size should data files be?

It is not possible to give a simple answer to this question. Generally, the size of the data files has no effect on activities in the database. However, you should bear in mind the following points:

Ensure that the DB_FILES Oracle parameter is set high enough. Otherwise, new data files cannot be created once this limit is reached.

The smaller datafiles are, the more quickly they can be individually restored during a backup.

The smaller the data files are, and therefore the more data files there are, the longer BEGIN BACKUP operations are likely to take in online backups. For more information, see Note 875477.

Data files that are too large aggravate performance problems that are caused by inode locking, since parallel processes may become serialized on the data file inode. See Note 793113.

On occasion, size restrictions may prevent the system from using data files that exceed a certain size (often 2GB). See Note 553854 for more information.

When there is a large number of data files and, at the same time, OS resources are not adequately configured, critical errors such as "file table overflow" can occur, which - at worst - may even cause the database to crash (see, for example, Note 9748). Therefore, depending on the number of data files selected, it is important to also adjust the relevant operating system parameters.

In general, it seems a good idea to restrict data file sizes to between 2GB and 20GB.

43. How can I delete a data file that was created by mistake?

 If a data file was created with the wrong size or in the incorrect directory, in most cases you can use RESIZE or RENAME to correct the error. If you want to drop the data file again instead, you have the following options:

Up to and including Oracle 9i, a created data file can only be deleted during a tablespace reorganization. There are no other realistic options.

As of Oracle 10g, an empty data file can also be dropped with the following command:


           If there are still extents in the data file, this command fails with ORA-03262. In this case, the affected segments must first be relocated so that the extents are released.

44. What are OCI_7 and OCI_8?

Log files and tool outputs contain information relating to OCI_7 or OCI_8, such as "Now I'm connected to ORACLE using OCI_7 API". OCI_7 and OCI_8 are programming interfaces, and each provides a dedicated command record for client programs such as the SAP kernels. All SAP kernels up to and including 4.6D (including _EXT and _COM kernels) use OCI_7, while OCI_8 is used as of 6.20.  The version does not depend on the Oracle client and the Oracle server release you use.

45. Can I also administrate the database with non-SAP tools?

SAP recommends you use BR*TOOLS to administrate the Oracle database (see Note 651812), but you can also use non-SAP tools to do so. Note the following restrictions:

SAP does not provide any support for problems related to non-SAP tools. This also includes Oracle tools such as Enterprise Manager (see Note 386413).

In the case of certain activities, such as reorganizations, SAP tools also change entries in the ABAP DDIC. When you use external tools, these changes should be made in the same way. For more information, see Note 154193.

Generally, you should only use the functions of the external tools if you know exactly how they work and what consequences they have. For example, a parallel processing level of tables or indexes, which is continuously increased within the framework of reorganizations or rebuilds, can have serious effects on database accesses and performance.

46. Can I use directory structures that differ from the SAP standard?

SAP only allows you to use the directory structure and naming conventions described in Note 27428. Using other directory structures and naming conventions can cause problems with the SAP tools you use, as well as problems during the analysis of errors. This is why SAP only permits the use of the directory structure and naming conventions described in Note 27428.

However, you can (if absolutely necessary) define the sapdata directories, or the subdirectories that it contains, as symbolic links to other directories (for example, /oracle//sapdata1/stabd_1 -> /oradata/stabd_1).

47. How can I determine the cause of changes to table data?

If it is not clear why entries in tables disappear or change, technically, the simplest way to investigate the cause is to display the Oracle Shared Cursor Cache (Note 766349). There you can display and analyze all accesses to certain tables simply. If INSERT, UPDATE or DELETE accesses appear, you can determine at which ABAP source code button these statements are triggered.

There are other ways of investigating the causes (such as auditing or triggers), but in most cases these are not necessary.

48. Why do online and offline redo logs differ in size?

 Note 998675 contains information about possible causes as to why the size differs between offline and online redo logs.

49. Can Oracle files be stored on NFS?

You may not have data files, redo logs and control files on standard NFS file systems. Only special, trusted NFS variants, such as NetApp-Filer, are allowed. See Notes 834343 and 999524 for more information.

50. How can I determine the Oracle internal demand for resources?

You can monitor resources, such as the number of processes, sessions, segments or locks, as follows:

V$RESOURCE_LIMIT contains information about the current demand, about the peak demand and about the maximum possible value:


           Note that the entry for MAX_ROLLBACK_SEGMENT is not important if you use Automatic Undo Management.

As of Oracle Release 10g, DBA_HIGH_WATER_MARK_STATISTICS contains information about current demand and about peak demand for resources:


51. How can I determine when the Oracle database should be started, and with which version it should be started?

As of Oracle Release 10g, you can take the history of the database start from the DBA_HIST_DATABASE_INSTANCE view:


              In earlier Oracle versions, you can take this information from the alert log, in which all of the starts are logged.

52. How can I determine which fixes are installed?

You can use "opatch lsinventory" to display the bug fixes that are currently implemented on the operating system.

As of BRCONNECT 7. 00 (20) you can also use BRCONNECT to display this information:

brconnect -F lsinv

As of Basis Release 7.10 and Release 7. 00 Support Package 11, Release 6.40 Support Package 20 and Release 6.20 Support Package 62, report RSORAPATCHINFO is available, which provides the same information from the SAP system side.

As of Oracle Release 10g the V$SYSTEM_FIX_CONTROL is also available, and this view displays at least a few of the fixes that were implemented.

53. How can I determine at what times the database was upgraded?

As of Oracle 10g, you can use DBA_REGISTRY_HISTORY to determine at what times the database was upgraded and downgraded and at what times patch sets were applied and CPUs were carried out (Note 1017936).

54. Where can I find an overview of the tasks of the background processes?

At database level, you can get a good overview of the tasks of the background processes of the table X$MESSAGES:


55. Where can I find more information about Oracle?

You can find a very comprehensive overview of Oracle functions in the Oracle online documentation.

You can find information about Oracle bugs and frequent errors under the components BC-DB-ORA*, BW-SYS-DB-ORA and (in the event of performance problems) SV-BO-DB in the SAP Note Administration.

You can also find information about bugs or technical problems at

You can find detailed information about the SAP tools used in the Oracle environment, for example BRBACKUP, BRARCHIVE, BRCONNECT, BRRESTORE, BRTOOLS, BRSPACE and BRGUI and other documents about current topics under

56. What does ASFU mean?

ASFU means "Application Specific Full Use" and is the name for Oracle licenses that are purchased from SAP. Therefore, each customer who licenses the Oracle database via SAP has an ASFU license.