Home »» Placement Papers »» All Papers »» Oracle »» Placement Paper

### Oracle Interview Questions And Answers Set - 11

Oracle Interview
Questions And Answers Set - 11

What is the Oracle Intelligent Agent? (for DBA)
The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is responsible for:
. Discovering targets that can be managed (Database Servers, Net8 Listeners, etc.);
. Monitoring of events registered in Enterprise Manager; and
. Executing tasks associated with jobs submitted to Enterprise Manager.

How does one start the Oracle Intelligent Agent? (for DBA)
One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM.
For OEM 9i and above:
agentctl start agent
agentctl stop agent

For OEM 2.1 and below:
lsnrctl dbsnmp_start
lsnrctl dbsnmp_status

On Windows NT, start the "OracleAgent" Service.
If the agent doesn't want to start, ensure your environment variables are set correctly and delete the following files before trying again:
1) In $ORACLE_HOME/network/admin: snmp_ro.ora and snmp_rw.ora. 2) Also delete ALL files in$ORACLE_HOME/network/agent/.

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:
Object: *
Severity: *
Message: *
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.

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:
. Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter

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. 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 . Etc. 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. 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/tiger@orcl parfile=export.txt
... where export.txt contains:
BUFFER=100000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y
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).

What are the types of visual attribute settings?
Custom Visual attributes Default visual attributes Named Visual attributes. Window

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"

What are the two ways to incorporate images into a oracle forms application?
Boilerplate Images
Image_items

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:
Method 1:
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
from sys.v_\$sqlarea
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.
Method 2:
Use the FEEDBACK=n import parameter. This command will tell IMP to display a dot for every N rows imported.

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

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.

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.

What is SQL*Loader and what is it used for? (for DBA)

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:
This sample control file (loader.ctl) will load an external data file containing delimited data:
infile 'c:datamydata.csv'
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.
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

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.

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
from tab1
where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
rem Remember to update initSID.ora, utl_file_dir='c:oradata' parameter
declare
fp utl_file.file_type;
begin
utl_file.putf(fp, '%s, %s
', 'TextField', 55);
utl_file.fclose(fp);
end;
/
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.

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):
INFILE *
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example:
INFILE *
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:
INFILE *
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

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.
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
INFILE 'mail_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
city,
state,
zipcode,
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

Can one load data into multiple tables at once? (for DBA)
Look at the following control file:
INFILE *
REPLACE
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
)

What is the difference between boiler plat images and image items?
Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your form, such as company logos and maps. Image items are special types of interface controls that store and display either vector or bitmap images. Like other items that store values, image items can be either base table items(items that relate directly to database columns) or control items. The definition of an image item is stored as part of the form module FMB and FMX files, but no image file is actually associated with an image item until the item is populate at run time.

What are the triggers available in the reports?
Before report, Before form, After form , Between page, After report.

Why is a Where clause faster than a group filter or a format trigger?
Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.

Can one selectively load only the records that one need? (for DBA)
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: -- One cannot use POSTION(x:y) as it is stream data, there are no positional fields-the next field begins after some delimiter, not in column X. -->
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

How does one load multi-line records? (for DBA)
One can create one logical record from multiple physical records using one of the following two clauses:
. CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

How can get SQL*Loader to COMMIT only at the end of the load file? (for DBA)
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.

Can one improve the performance of SQL*Loader? (for DBA)
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads. Run multiple load jobs concurrently.

 Comment Tweet