|
Oracle Interview Questions and Answers
Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows selected by both
queries.MINUS - returns all distinct rows selected by
the first query but not by the second.UNION - returns
all distinct rows selected by either queryUNION ALL -
returns all rows selected by either query, including all
duplicates.
Should the OEM Console be displayed at all times (when
there are scheduled jobs)? (for DBA)
When a job is submitted the agent will confirm the
status of the job. When the status shows up as
scheduled, you can close down the OEM console. The
processing of the job is managed by the OIA (Oracle
Intelligent Agent). The OIA maintains a .jou file in the
agent's subdirectory. When the console is launched
communication with the Agent is established and the
contents of the .jou file (binary) are reported to the
console job subsystem. Note that OEM will not be able to
send e-mail and paging notifications when the Console is
not started.
Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),INSTR returns the position
of the mth occurrence of the string 2 instring1. The
search begins from nth position of string1.SUBSTR
(String1 n,m)SUBSTR returns a character string of size m
in string1, starting from nth position of string1.
What kind of jobs can one schedule with OEM? (for DBA)
OEM comes with pre-defined jobs like Export, Import, run
OS commands, run sql scripts, SQL*Plus commands etc. It
also gives you the flexibility of scheduling custom jobs
written with the TCL language.
What are the pre requisites ?
I. to modify data type of a column ? ii. to add a column
with NOT NULL constraint ? To Modify the datatype of a
column the column must be empty. to add a column with
NOT NULL constrain, the table must be empty.
How does one backout events and jobs during maintenance
slots? (for DBA)
Managemnet and data collection activity can be suspended
by imposing a blackout. Look at these examples:
agentctl start blackout # Blackout the entrire agent
agentctl stop blackout # Resume normal monitoring and
management
agentctl start blackout ORCL # Blackout database ORCL
agentctl stop blackout ORCL # Resume normal monitoring
and management
agentctl start blackout -s jobs -d 00:20 # Blackout jobs
for 20 minutes
What are the types of SQL Statement ?
Data Definition Language :
CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language:
INSERT,UPDATE,DELETE,LOCK
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :
ALTER SYSTEM.
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:
Event Name: /oracle/script/myalert
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.
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
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_HOME\BIN:
C:\Orawin95\Bin> RegSvr32 mmdx32.OCX
C:\Orawin95\Bin> 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,
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
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)
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.
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:
load data
infile 'c:\data\mydata.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.
load data
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.
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
spool oradata.txt
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
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', '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):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
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:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical
rows to skip. Look at this example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
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.
LOAD DATA
INFILE *
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')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
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:
LOAD DATA
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.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
