|
Oracle Interview Questions and Answers
What are Procedure, functions and Packages ?
Procedures and functions consist of set of PL/SQL
statements that are grouped together as a unit to solve
a specific problem or perform set of related tasks.
Procedures do not Return values while Functions return
one One Value Packages :: Packages Provide a method of
encapsulating and storing related procedures, functions,
variables and other Package Contents
What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures
that are automatically executed as a result of insert
in, update to, or delete from table.
Database triggers have the values old and new to denote
the old value in the table before it is deleted and the
new indicated the new value that will be used. DT are
useful for implementing complex business rules which
cannot be enforced using the integrity rules.We can have
the trigger as Before trigger or After Trigger and at
Statement or Row level. e.g:: operations insert,update
,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level(
for every execution ) 6 * 2 A total of 12. Thus a total
of 12 combinations are there and the restriction of
usage of 12 triggers has been lifted from Oracle 7.3
Onwards.
Stored Procedures :: Stored Procedures are Procedures
that are stored in Compiled form in the database.The
advantage of using the stored procedures is that many
users can use the same procedure in compiled and ready
to use format.
How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule
enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that
the relationship between the foreign key and the primary
key has to be enforced.When there is data in Child
Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule
is about the complex business processes which cannot be
implemented by the above 2 rules.
What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a
child is exisiting
b) Isolated :: The Master can be deleted when the child
is exisiting
c) Cascading :: The child gets deleted when the Master
is deleted.
What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate Default Setting. The Detail records are
shown when the Master Record are shown.
b) Deffered with Auto Query Oracle Forms defer fetching
the detail records until the operator navigates to the
detail block.
c) Deffered with No Auto Query The operator must
navigate to the detail block and explicitly execute a
query
What are the Different
Optimization Techniques
The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and
change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from
dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a
single cursor for all SQL statements.This slow downs the
processing because for evertime the SQL must be parsed
whenver they are executed.
f45run module = my_firstform userid = scott/tiger
optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns
seperate cursor only for each query SELECT statement.
All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger
optimize_Tp = No
How does one change an Oracle user's password?(for DBA)
Issue the following SQL command:
ALTER USER <username> IDENTIFIED BY <new_password>;
From Oracle8 you can just type "password" from SQL*Plus,
or if you need to change another user's password, type
"password user_name". Look at this example:
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:
How does one create and drop database users?
Look at these examples:
CREATE USER scott
IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESACE tools -- Assign space for table and
index segments
TEMPORARY TABLESPACE temp; -- Assign sort space
DROP USER scott CASCADE; -- Remove user
After creating a new user, assign the required
privileges:
GRANT CONNECT, RESOURCE TO scott;
GRANT DBA TO scott; -- Make user a DB Administrator
Remember to give the user some space quota on its
tablespaces:
ALTER USER scott QUOTA UNLIMITED ON tools;
Who created all these users in my database?/ Can I drop
this user? (for DBA)
Oracle creates a number of default database users or
schemas when a new database is created. Below are a few
of them:
SYS/CHANGE_ON_INSTALL or INTERNAL
Oracle Data Dictionary/ Catalog
Created by: ?/rdbms/admin/sql.bsq and various cat*.sql
scripts
Can password be changed: Yes (Do so right after the
database was created)
Can user be dropped: NO
SYSTEM/MANAGER
The default DBA user name (please do not use SYS)
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the
database was created)
Can user be dropped: NO
OUTLN/OUTLN
Stored outlines for optimizer plan stability
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the
database was created)
Can user be dropped: NO
SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and
BLAKE/PAPER.
Training/ demonstration users containing the popular EMP
and DEPT tables
Created by: ?/rdbms/admin/utlsampl.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all
production environments
HR/HR (Human Resources), OE/OE (Order Entry), SH/SH
(Sales History).
Training/ demonstration users containing the popular
EMPLOYEES and DEPARTMENTS tables
Created by: ?/demo/schema/mksample.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all
production environments
CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge) administrator user
Created by: ?/ctx/admin/dr0csys.sql
TRACESVR/TRACE
Oracle Trace server
Created by: ?/rdbms/admin/otrcsvr.sql
DBSNMP/DBSNMP
Oracle Intelligent agent
Created by: ?/rdbms/admin/catsnmp.sql, called from
catalog.sql
Can password be changed: Yes - put the new password in
snmp_rw.ora file
Can user be dropped: YES - Only if you do not use the
Intelligent Agents
ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series,
etc.
Created by: ?/ord/admin/ordinst.sql
ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series,
etc
Created by: ?/ord/admin/ordinst.sql
DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
Created by: ?/ds/sql/dssys_init.sql
MDSYS/MDSYS
Oracle Spatial administrator user
Created by: ?/ord/admin/ordinst.sql
AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
Created by: ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes
UTLBSTAT/UTLESTAT
Created by: ?/rdbms/admin/statscre.sql
Remember to change the passwords for the SYS and SYSTEM
users immediately after installation!
Except for the user SYS, there should be no problem
altering these users to use a different default and
temporary tablespace.
How does one enforce strict password control? (for DBA)
By default Oracle's security is not extremely good. For
example, Oracle will allow users to choose single
character passwords and passwords that match their names
and userids. Also, passwords don't ever expire. This
means that one can hack an account for years without
ever locking the user.
From Oracle8 one can manage passwords through profiles.
Some of the things that one can restrict:
. FAILED_LOGIN_ATTEMPTS - failed login attempts before
the account is locked
. PASSWORD_LIFE_TIME - limits the number of days the
same password can be used for authentication
. PASSWORD_REUSE_TIME - number of days before a password
can be reused
. PASSWORD_REUSE_MAX - number of password changes
required before the current password can be reused
. PASSWORD_LOCK_TIME - number of days an account will be
locked after maximum failed login attempts
. PASSWORD_GRACE_TIME - number of days after the grace
period begins during which a warning is issued and login
is allowed
. PASSWORD_VERIFY_FUNCTION - password complexity
verification script
Look at this simple example:
CREATE PROFILE my_profile LIMIT
PASSWORD_LIFE_TIME 30;
ALTER USER scott PROFILE my_profile;
How does one switch to another user in Oracle? (for DBA)
Users normally use the "connect" statement to connect
from one database user to another. However, DBAs can
switch from one user to another without a password. Of
course it is not advisable to bridge Oracle's security,
but look at this example: SQL> select password from
dba_users where username='SCOTT';
PASSWORD
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values
'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.
Note: Also see the su.sql script in the Useful Scripts
and Sample Programs Page.
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
|
