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
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
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
agentctl start blackout ORCL # Blackout database ORCL
agentctl stop blackout ORCL # Resume normal monitoring
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:
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :
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;
. Executing tasks associated with jobs submitted to
How does one start the Oracle Intelligent Agent? (for
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:
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
2) Also delete ALL files in $ORACLE_HOME/network/agent/.
Can one write scripts to send alert messages to the
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
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
. 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
. 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
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
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
The export/import utilities are also commonly used to
perform the following tasks:
. Backup and recovery (small databases only)
. Reorganization of data/ Eliminate database
. Detect database corruption. Ensure that all the data
can be read.
. Transporting tablespaces between databases
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
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
exp userid=scott/tiger@orcl 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).
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?
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)
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.
Can one import tables to a different tablespace? (for
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
. Import the dump file using the INDEXFILE= option
. Edit the indexfile. Remove remarks and specify the
. Run this indexfile against your database, this will
create the required tables in the appropriate
. Import the table(s) with the IGNORE=Y option.
Change the default tablespace for the user:
. Revoke the "UNLIMITED TABLESPACE" privilege from the
. 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
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:
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
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
Is there a SQL*Unloader to download data to a flat file?
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'
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', '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
Can one load variable and fix length data records? (for
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?
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),
Can one modify data as it loads into the database? (for
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)",
Can one load data into multiple tables at once? (for
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
What is the difference between boiler plat images and
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 :