DBMS ( 4 )

301. What is a Shared SQL pool?

The data dictionary cache is stored in an area in SGA called the
Shared SQL Pool. This will allow sharing of parsed SQL statements
among concurrent users.

302. What is hot backup and how it can be taken?

Taking backup of archive log files when database is open. For this
the ARCHIVELOG mode should be enabled. The following files need to
be backed up. All data files. All Archive log, redo log files. All
control files.

303. List the Optional Flexible Architecture (OFA) of Oracle
database? or How can we organize the tablespaces in Oracle database
to have maximum performance ?

SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.

304. How to implement the multiple control files for an existing
database ?

Shutdown the database Copy one of the existing control file to new
location Edit Config ora file by adding new control file. name
Restart the database.

305. What is advantage of having disk shadowing/ Mirroring ?

Shadow set of disks save as a backup in the event of disk failure.
In most Operating System if any disk failure occurs it automatically
switchover to place of failed disk. Improved performance because
most OS support volume shadowing can direct file I/O request to use
the shadow set of files instead of the main set of files. This
reduces I/O load on the main set of disks.

306. How will you force database to use particular rollback
segment ?

SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.

307. Why query fails sometimes ?

Rollback segment dynamically extent to handle larger transactions
entry loads. A single transaction may wipeout all available free
space in the Rollback Segment Tablespace. This prevents other user
using Rollback segments.

308. What is the use of RECORD LENGTH option in EXP command ?

Record length in bytes.

309. How will you monitor rollback segment status ?

Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction
involving a distributed database.

310. What is meant by Redo Log file mirroring ? How it can be
achieved?

Process of having a copy of redo log files is called mirroring. This
can be achieved by creating group of log files together, so that
LGWR will automatically writes them to all the members of the
current on-line redo log group. If any one group fails then database
automatically switch over to next group. It degrades performance.

311. Which parameter in Storage clause will reduce no. of rows per
block?

PCTFREE parameter
Row size also reduces no of rows per block.

312. What is meant by recursive hints ?

Number of times processes repeatedly query the dictionary table is
called recursive hints. It is due to the data dictionary cache is
too small. By increasing the SHARED_POOL_SIZE parameter we can
optimize the size of Data Dictionary Cache.

313. What is the use of PARFILE option in EXP command ?

Name of the parameter file to be passed for export.

314. What is the use of PARFILE option in EXP command ?

Name of the parameter file to be passed for export.

315. What is a logical backup?

Logical backup involves reading a set of database records and
writing them into a file. Export utility is used for taking backup
and Import utility is used to recover from backup.

316. What is the use of TABLES option in EXP command ?

List of tables should be exported.ze)

317. What is the OPTIMAL parameter?

It is used to set the optimal length of a rollback segment.

318. What is a Rollback segment entry ?

It is the set of before image data blocks that contain rows that are
modified by a transaction. Each Rollback Segment entry must be
completed within one rollback segment. A single rollback segment can
have multiple rollback segment entries.

319. What is mean by Program Global Area (PGA) ?

It is area in memory that is used by a Single Oracle User Process.

320. What is hit ratio ?

It is a measure of well the data cache buffer is handling requests
for data. Hit Ratio = (Logical Reads - Physical Reads - Hits
Misses)/ Logical Reads.

321. What are the different kind of export backups?

Full back - Complete database
Incremental - Only affected tables from last incremental date/full
backup date.
Cumulative backup - Only affected table from the last cumulative
date/full backup date.

322. How free extents are managed in Ver 6.0 and Ver 7.0 ?

Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the neighboring free
extent in Ver 7.0

323. What is the use of RECORD option in EXP command?

For Incremental exports, the flag indirects whether a record will be
stores data dictionary tables recording the export.

324. What is the use of ROWS option in EXP command ?

Flag to indicate whether table rows should be exported. If 'N' only
DDL statements for the database objects will be created.

325. What is the use of COMPRESS option in EXP command ?

Flag to indicate whether export should compress fragmented segments
into single extents.

326. How will you swap objects into a different table space for an
existing database ?

Export the user
Perform import using the command imp system/manager file=export.dmp
indexfile=newrite.sql.
This will create all definitions into newfile.sql. Drop necessary
objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.

327. How does Space allocation table place within a block ?

Each block contains entries as follows
Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)

328. What are the factors causing the reparsing of SQL statements in
SGA?

Due to insufficient Shared SQL pool size. Monitor the ratio of the
reloads takes place while executing SQL statements. If the ratio is
greater than 1 then increase the SHARED_POOL_SIZE. LOGICAL &
PHYSICAL ARCHITECTURE OF DATABASE.

329. What is dictionary cache ?

Dictionary cache is information about the databse objects stored in
a data dictionary table.

330. What is a Control file ?

Database overall physical architecture is maintained in a file
called control file. It will be used to maintain internal
consistency and guide recovery operations. Multiple copies of
control files are advisable.

331. What is Database Buffers ?

Database buffers are cache in the SGA used to hold the data blocks
that are read from the data segments in the database such as tables,
indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides
the size.

332. How will you create multiple rollback segments in a database ?

Create a database which implicitly creates a SYSTEM Rollback Segment
in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in
the SYSTEM tablespace. Make new rollback segment available (After
shutdown, modify init.ora file and Start database) Create other
tablespaces (RBS) for rollback segments. Deactivate Rollback Segment
R0 and activate the newly created rollback segments.

333. What is cold backup? What are the elements of it?

Cold backup is taking backup of all physical files after normal
shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)

334. What is meant by redo log buffer ?

Changes made to entries are written to the on-line redo log files.
So that they can be used in roll forward operations during database
recoveries. Before writing them into the redo log files, they will
first brought to redo log buffers in SGA and LGWR will write into
files frequently. LOG_BUFFER parameter will decide the size.

335. How will you estimate the space required by a non-clustered
tables?

Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to
calculate the initial extent size for a working table.

336. How will you monitor the space allocation ?

By querying DBA_SEGMENT table/view.

337. What is meant by free extent ?

A free extent is a collection of continuous free blocks in
tablespace. When a segment is dropped its extents are reallocated
and are marked as free.

338. What is the use of IGNORE option in IMP command ?

A flag to indicate whether the import should ignore errors encounter
when issuing CREATE commands.

339. What is the use of ANALYSE ( Ver 7) option in EXP command ?

A flag to indicate whether statistical information about the
exported objects should be written to export dump file.

340. What is the use of ROWS option in IMP command ?

A flag to indicate whether rows should be imported. If this is set
to 'N' then only DDL for database objects will be executed.

341. What is the use of INDEXES option in EXP command ?

A flag to indicate whether indexes on tables will be exported.

342. What is the use of INDEXES option in IMP command ?

A flag to indicate whether import should import index on tables or
not.

343. What is the use of GRANT option in EXP command?

A flag to indicate whether grants on databse objects will be
exported or not. Value is 'Y' or 'N'.

344. What is the use of GRANT option in IMP command ?

A flag to indicate whether grants on database objects will be
imported.

345. What is the use of FULL option in EXP command ?

A flag to indicate whether full databse export should be performed.

346. What is the use of SHOW option in IMP command ?

A flag to indicate whether file content should be displayed or not.

347. What is the use of CONSTRAINTS option in EXP command ?

A flag to indicate whether constraints on table need to be exported.

348. What is the use of CONSISTENT (Ver 7) option in EXP command ?

A flag to indicate whether a read consistent version of all the
exported objects should be maintained.

349. What are the different methods of backing up oracle database ?

- Logical Backups
- Cold Backups
- Hot Backups (Archive log)

350. What is the difference between ON-VALIDATE-FIELD trigger and a
POST-CHANGE trigger ?

When you changes the Existing value to null, the On-validate field
trigger will fire post change trigger will not fire. At the time of
execute-query post-change trigger will fire, on-validate field
trigger will not fire.

351. When is PRE-QUERY trigger executed ?

When Execute-query or count-query Package procedures are invoked.

352. How do you trap the error in forms 3.0 ?

using On-Message or On-Error triggers.

353. How many pages you can in a single form ?

Unlimited

354. While specifying master/detail relationship between two blocks
specifying the join condition is a must ? True or False. ?

True

355. EXIT_FORM is a restricted package procedure ?a. True b. False

True

356. What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE
TRIGGERS ?

These triggers are executes when inserting, deleting and updating
operations are performed and can be used to change the default
function of insert, delete or update respectively. For Eg, instead
of inserting a row in a table an existing row can be updated in the
same table.

357. What are the types of Pop-up window ?

the pop-up field editor
pop-up list of values
pop-up pages.
Alert :

358. What is an SQL *FORMS ?

SQL *forms is 4GL tool for developing and executing; Oracle based
interactive application.

359. How do you control the constraints in forms ?

Select the use constraint property is ON Block definition screen.
BLOCK

360. What is the difference between restricted and unrestricted
package procedure ?

Restricted package procedure that affects the basic functions of SQL
* Forms. It cannot used in all triggers except key triggers.
Unrestricted package procedure that does not interfere with the
basic functions of SQL * Forms it can be used in any triggers.

361. A query fetched 10 records How many times does a PRE-QUERY
Trigger and POST-QUERY Trigger will get executed ?

PRE-QUERY fires once.
POST-QUERY fires 10 times.

362. Give the sequence in which triggers fired during insert
operations, when the following 3 triggers are defined at the same
block level ?

a. ON-INSERT b. POST-INSERT c. PRE-INSERT

363. State the order in which these triggers are executed ?

POST-FIELD,ON-VALIDATE-FIELD,POST-CHANGE and KEY-NEXTFLD. KEY-
NEXTFLD,POST-CHANGE, ON-VALIDATE-FIELD, POST-FIELD. g.

364. What the PAUSE package procedure does ?

Pause suspends processing until the operator presses a function key

365. What do you mean by a page ?

Pages are collection of display information, such as constant text
and graphics

366. What are the type of User Exits ?

ORACLE Precompliers user exits
OCI (ORACLE Call Interface)
Non-ORACEL user exits.
Page :

367. What is the difference between an ON-VALIDATE-FIELD trigger and
a trigger ?

On-validate-field trigger fires, when the field Validation status
New or changed. Post-field-trigger whenever the control leaving form
the field, it will fire.

368. Can we use a restricted package procedure in ON-VALIDATE-FIELD
Trigger ?

No

369. Is a Key startup trigger fires as result of a operator pressing
a key explicitly ?

No

370. Can we use GO-BLOCK package in a pre-field trigger ?

No

371. Can we create two blocks with the same name in form 3.0 ?

No

372. What is Post-Block is a. ???

a. Navigational Trigger.
b. Key trigger
c. Transaction Trigger.

373. What does an on-clear-block Trigger fire?

It fires just before SQL * forms the current block.

374. Name the two files that are created when you generate the form
give the filex extension ?

INP (Source File)
FRM (Executable File)

375. What package procedure used for invoke sql *plus from sql
*forms ?

Host (E.g. Host (sqlplus))

376. What is the significance of PAGE 0 in forms 3.0 ?

Hide the fields for internal calculation.

377. What are the different types of key triggers ?

Function Key
Key-function
Key-others
Key-startup

378. What is the difference between a Function Key Trigger and Key
Function Trigger ?

Function key triggers are associated with individual SQL*FORMS
function keys You can attach Key function triggers to 10 keys or key
sequences that normally do not perform any SQL * FORMS operations.
These keys referred as key F0 through key F9.

379. Committed block sometimes refer to a BASE TABLE ?

False

380. Error_Code is a package proecdure ?

a. True b. false
False

381. How can you execute the user defined triggers in forms 3.0 ?

Execute Trigger (trigger-name)

382. What ERASE package procedure does ?

Erase removes an indicated global variable.

383. What Enter package procedure does ?

Enter Validate-data in the current validation unit.

384. What is the difference between NAME_IN and COPY ?

Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the
variable to which you apply.

385. What package procedure is used for calling another form ?

Call (E.g. Call(formname)

386. When the form is running in DEBUG mode, If you want to examine
the values of global variables and other form variables, What
package procedure command you would use in your trigger text ?

Break.
SYSTEM VARIABLES

387. The value recorded in system.last_record variable is of type
a. Number
b. Boolean
c. Character. ?

b. Boolean.

388. What are the unrestricted procedures used to change the popup
screen position during run time ?

Anchor-view
Resize -View
Move-View.

389. What is an Alert ?

An alert is window that appears in the middle of the screen
overlaying a portion of the current display.

390. Deleting a page removes information about all the fields in
that page ? a. True. b. False?

a. True.

391. Two popup pages can appear on the screen at a time ?Two popup
pages can appear on the screen at a time ? a. True. b. False?

a. True.

392. Classify the restricted and unrestricted procedure from the
following.
a. Call
b. User-Exit
c. Call-Query
d. Up
e. Execute-Query
f. Message
g. Exit-From
h. Post
i. Break?

a. Call - unrestricted
b. User Exit - Unrestricted
c. Call_query - Unrestricted
d. Up - Restricted
e. Execute Query - Restricted
f. Message - Restricted
g. Exit_form - Restricted
h. Post - Restricted
i. Break - Unrestricted.

393. What is an User Exits ?

A user exit is a subroutine which are written in programming
languages using pro*C pro *Cobol , etc., that link into the SQL *
forms executable.

394. What is a Trigger ?

A piece of logic that is executed at or triggered by a SQL *forms
event.

395. What is a Package Procedure ?

A Package procedure is built in PL/SQL procedure.

398. What is the maximum size of a form ?

255 character width and 255 characters Length.

399. What is the difference between system.current_field and
system.cursor_field ?

1. System.current_field gives name of the field.
2. System.cursor_field gives name of the field with block name.

400. List the system variables related in Block and Field?

1. System.block_status
2. System.current_block
3. System.current_field
4. System.current_value
5. System.cursor_block
6. System.cursor_field
7. System.field_status.