Oracle Interview Questions – Part 18
Q.How can a cross product be created?
By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
Q.What is a display item?
Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.
Q.What are the two ways to incorporate images into a oracle forms application?
Q.Where can one get more information about TCL? (for DBA)
One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL:
.The Tcl Developer Xchange – download and learn about TCL
.OraTCL at Sourceforge – Download the OraTCL package
.Tom Poindexter’s Tcl Page – Oratcl was originally written by Tom Poindexter
Q.What is import/export and why does one need it? (for DBA)
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another.
The imp/exp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database. For more information on how to load and unload data from files, read the SQL*Loader FAQ.
The export/import utilities are also commonly used to perform the following tasks:
. Backup and recovery (small databases only)
. Reorganization of data/ Eliminate database fragmentation
. Detect database corruption. Ensure that all the data can be read.
. Transporting tablespaces between databases
Q.How does one use the import/export utilities? (for DBA)
Look for the “imp” and “exp” executables in your $ORACLE_HOME/bin directory. One can run them interactively, using command line parameters, or using parameter files. Look at the imp/exp parameters before starting. These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes”.
The following examples demonstrate how the imp/exp utilities can be used:
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)
imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept
exp userid=scott/[email protected] parfile=export.txt
… where export.txt contains:
NOTE: If you do not like command line utilities, you can import and export data with the “Schema Manager” GUI that ships with Oracle Enterprise Manager (OEM).
Q.Can one write scripts to send alert messages to the console?
Start the OEM console and create a new event. Select option “Enable Unsolicited Event”. Select test “Unsolicited Event”. When entering the parameters, enter values similar to these:
Event Name: /oracle/script/myalert
One can now write the script and invoke the oemevent command to send alerts to the console. Look at this example: oemevent /oracle/script/myalert DESTINATION alert “My custom error message” where DESTINATION is the same value as entered in the “Monitored Destinations” field when you’ve registered the event in the OEM Console.
Q.Can one export a subset of a table? (for DBA)
From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example:
exp scott/tiger tables=emp query=”where deptno=10″
Q.Are there any troubleshooting tips for OEM? (for DBA)
. Create the OEM repository with a user (which will manage the OEM) and store it in a tablespace that does not share any data with other database users. It is a bad practice to create the repository with SYS and System.
. If you are unable to launch the console or there is a communication problem with the intelligent agent (daemon). Ensure OCX files are registered. Type the following in the DOS prompt (the current directory should be $ORACLE_HOMEBIN:
C:Orawin95Bin> RegSvr32 mmdx32.OCX
C:Orawin95Bin> RegSvr32 vojt.OCX
. If you have a problem starting the Oracle Agent
Solution A: Backup the *.Q files and Delete all the *.Q Files ($Oracle_home/network/agent folder)
Backup and delete SNMP_RO.ora, SNMP_RW.ora, dbsnmp.ver and services.ora files ($Oracle_Home/network/admin folder) Start the Oracle Agent service.
Solution B: Your version of Intelligent Agent could be buggy. Check with Oracle for any available patches. For example, the Intelligent Agent that comes with Oracle 8.0.4 is buggy.
Sometimes you get a Failed status for the job that was executed successfully.
Check the log to see the results of the execution rather than relying on this status.
Q.What are the types of visual attribute settings?
Custom Visual attributes Default visual attributes Named Visual attributes. Window
Q.Can one monitor how fast a table is imported? (for DBA)
If you need to monitor how fast rows are imported from a running import job, try one of the following methods:
select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name,
round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min
where sql_text like ‘INSERT %INTO “%’
and command_type = 2
and open_versions > 0;
For this to work one needs to be on Oracle 7.3 or higher (7.2 might also be OK). If the import has more than one table, this statement will only show information about the current table being imported.
Contributed by Osvaldo Ancarola, Bs. As. Argentina.
Use the FEEDBACK=n import parameter. This command will tell IMP to display a dot for every N rows imported.
Q.Can one import tables to a different tablespace? (for DBA)
Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One can alter this behaviour by following one of these procedures: Pre-create the table(s) in the correct tablespace:
. Import the dump file using the INDEXFILE= option
. Edit the indexfile. Remove remarks and specify the correct tablespaces.
. Run this indexfile against your database, this will create the required tables in the appropriate tablespaces
. Import the table(s) with the IGNORE=Y option.
Change the default tablespace for the user:
. Revoke the “UNLIMITED TABLESPACE” privilege from the user
. Revoke the user’s quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user’s default tablespace.
. Make the tablespace to which you want to import the default tablespace for the user
. Import the table
Q.How is possible to restrict the user to a list of values while entering values for parameters?
By setting the Restrict To List property to true in the parameter property sheet.
Q.What is SQL*Loader and what is it used for? (for DBA)
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
Q.How does one use the SQL*Loader utility? (for DBA)
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:
sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data:
into table emp
fields terminated by “,” optionally enclosed by ‘”‘
( empno, empname, sal, deptno )
The mydata.csv file may look like this:
10001,”Scott Tiger”, 1000, 40
10002,”Frank Naude”, 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify “*” as the name of the data file, and use BEGINDATA to start the data section in the control file.
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
POLY POLITICAL SCIENCE
Q.What do you mean by a block in forms4.0?
Block is a single mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.
Q.Is there a SQL*Unloader to download data to a flat file? (for DBA)
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
select col1 || ‘,’ || col2 || ‘,’ || col3
where col2 = ‘XYZ’;
Alternatively use the UTL_FILE PL/SQL package:
rem Remember to update initSID.ora, utl_file_dir=’c:oradata’ parameter
fp := utl_file.fopen(‘c:oradata’,’tab1.txt’,’w’);
utl_file.putf(fp, ‘%s, %sn’, ‘TextField’, 55);
You might also want to investigate third party tools like SQLWays from Ispirer Systems, TOAD from Quest, or ManageIT Fast Unloader from CA to help you unload data from Oracle.
Q.Can one load data into multiple tables at once? (for DBA)
Look at the following control file:
INTO TABLE emp
WHEN empno != ‘ ‘
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
INTO TABLE proj
WHEN projno != ‘ ‘
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
Q.Can one load variable and fix length data records? (for DBA)
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
INTO TABLE load_delimited_data
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
If you need to load positional data (fixed length), look at the following control file example:
INTO TABLE load_positional_data
( data1 POSITION(1:5),
Can one skip header records load while loading?
Q. Use the “SKIP n” keyword, where n = number of logical rows to skip. Look at this example:
INTO TABLE load_positional_data
( data1 POSITION(1:5),
Q.Can one modify data as it loads into the database? (for DBA)
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
INTO TABLE modified_data
( rec_no “my_db_sequence.nextval”,
region CONSTANT ’31’,
time_loaded “to_char(SYSDATE, ‘HH24:MI’)”,
data1 POSITION(1:5) “:data1/100”,
data2 POSITION(6:15) “upper(:data2)”,
data3 POSITION(16:22)”to_date(:data3, ‘YYMMDD’)”
INTO TABLE mailing_list
FIELDS TERMINATED BY “,”
mailing_addr “decode(:mailing_addr, null, :addr, :mailing_addr)”,
mailing_city “decode(:mailing_city, null, :city, :mailing_city)”,