ORACLE FREQUENTLY ASKED QUESTIONS - II
What are the Built-ins
used for sending Parameters to forms?
You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
Can you have more than
one content canvas view attached with a window?
Yes. Each window you create must have at least one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
Yes. Each window you create must have at least one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
Is the After report
trigger fired if the report execution fails?
Yes.
Yes.
Does a before form
trigger fire when the parameter form is suppressed?
Yes.
Yes.
What is SGA?
The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.
The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.
What is a shared pool?
The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.
The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.
What is mean by Program
Global Area (PGA)?
It is area in memory that is used by a single Oracle user process.
It is area in memory that is used by a single Oracle user process.
What is a data segment?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
What are the factors
causing the reparsing of SQL statements in SGA?
Due to insufficient shared 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.
Due to insufficient shared 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.
What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
What is cluster key?
The related columns of the tables in a cluster are called the cluster key.
The related columns of the tables in a cluster are called the cluster key.
Do a view contain data?
Views do not contain or store data.
Views do not contain or store data.
What is user Account in
Oracle database?
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.
How will you enforce
security using stored procedures?
Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
What are the dictionary
tables used to monitor a database space?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
Can a property clause
itself be based on a property clause?
Yes
Yes
If a parameter is used in
a query without being previously defined, what diff. exist between report 2.0
and 2.5 when the query is applied?
While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.
While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.
What are the sql clauses
supported in the link property sheet?
Where start with having.
Where start with having.
What is trigger associated
with the timer?
When-timer-expired.
When-timer-expired.
What are the trigger
associated with image items?
When-image-activated fires when the operators double clicks on an image item when-image-pressed fires when an operator clicks or double clicks on an image item
When-image-activated fires when the operators double clicks on an image item when-image-pressed fires when an operator clicks or double clicks on an image item
What are the different
windows events activated at runtimes?
When_window_activated
When_window_closed
When_window_deactivated
When_window_resized
Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.
When_window_activated
When_window_closed
When_window_deactivated
When_window_resized
Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.
When do you use data
parameter type?
When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to products invoked with the run_product built-in subprogram.
When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to products invoked with the run_product built-in subprogram.
What is difference
between open_form and call_form?
When one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. When one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.
When one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. When one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.
What is new_form
built-in?
When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
What is the "LOV of
Validation" Property of an item? What is the use of it?
When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
What is the diff. when
Flex mode is mode on and when it is off?
When flex mode is on, reports automatically resizes the parent when the child is resized.
When flex mode is on, reports automatically resizes the parent when the child is resized.
What is the diff. when
confine mode is on and when it is off?
When confine mode is on, an object cannot be moved outside its parent in the layout.
When confine mode is on, an object cannot be moved outside its parent in the layout.
What are visual
attributes?
Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.
Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.
Which of the two views
should objects according to possession?
View by structure.
View by structure.
What are the two types of
views available in the object navigator (specific to report 2.5)?
View by structure and view by type.
View by structure and view by type.
What are the vbx
controls?
Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the MS visual basic environments and include such items as sliders, rides and knobs.
Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the MS visual basic environments and include such items as sliders, rides and knobs.
What is the use of transactional
triggers?
Using transactional triggers we can control or modify the default functionality of the oracle forms.
Using transactional triggers we can control or modify the default functionality of the oracle forms.
How do you create a new
session while open a new form?
Using open_form built-in setting the session option Ex. Open_form ('Stocks ', active, session). When invoke the multiple forms with open form and call_form in the same application, state whether the following are true/False
Using open_form built-in setting the session option Ex. Open_form ('Stocks ', active, session). When invoke the multiple forms with open form and call_form in the same application, state whether the following are true/False
What are the ways to
monitor the performance of the report?
Use reports profile executable statement. Use SQL trace facility.
Use reports profile executable statement. Use SQL trace facility.
If two groups are not
linked in the data model editor, what is the hierarchy between them?
Two group that is above are the left most rank higher than the group that is to right or below it.
Two group that is above are the left most rank higher than the group that is to right or below it.
An open form cannot be
execute the call_form procedure if you chain of called forms has been initiated
by another open form?
True
True
Explain about horizontal,
vertical tool bar canvas views?
Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window
Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window
What is the purpose of
the product order option in the column property sheet?
To specify the order of individual group evaluation in a cross products.
To specify the order of individual group evaluation in a cross products.
What is the use of
image_zoom built-in?
To manipulate images in image items.
To manipulate images in image items.
How do you reference a
parameter indirectly?
To indirectly reference a parameter use the NAME IN, COPY 'built-ins to indirectly set and reference the parameters value' Example name_in ('capital parameter my param'), Copy ('SURESH’, ‘Parameter my_param')
To indirectly reference a parameter use the NAME IN, COPY 'built-ins to indirectly set and reference the parameters value' Example name_in ('capital parameter my param'), Copy ('SURESH’, ‘Parameter my_param')
What is a timer?
Timer is an "internal time clock" that you can programmatically create to perform an action each time the times.
Timer is an "internal time clock" that you can programmatically create to perform an action each time the times.
What are the two phases
of block coordination?
There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.
There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.
What are Most Common
types of Complex master-detail relationships?
There are three most common types of complex master-detail relationships:
master with dependent details
master with independent details
detail with two masters
There are three most common types of complex master-detail relationships:
master with dependent details
master with independent details
detail with two masters
What is a text list?
The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that cannot be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.
The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that cannot be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.
What is term?
The term is terminal definition file that describes the terminal form which you are using r20run.
The term is terminal definition file that describes the terminal form which you are using r20run.
What is use of term?
The term file which key is correspond to which oracle report functions.
The term file which key is correspond to which oracle report functions.
What is pop list?
The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.
The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.
What is the maximum no of
chars the parameter can store?
The maximum no of chars the parameter can store is only valid for char parameters, which can be up to 64K. No parameters default to 23Bytes and Date parameter default to 7Bytes.
The maximum no of chars the parameter can store is only valid for char parameters, which can be up to 64K. No parameters default to 23Bytes and Date parameter default to 7Bytes.
What are the default
extensions of the files created by library module?
The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary
The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary
What are the Coordination
Properties in a Master-Detail relationship?
The coordination properties are
Deferred
Auto-Query
These Properties determine when the population phase of block
coordination should occur.
The coordination properties are
Deferred
Auto-Query
These Properties determine when the population phase of block
coordination should occur.
How do you display
console on a window?
The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.
The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.
What are the different
Parameter types?
Text ParametersData Parameters
Text ParametersData Parameters
State any three mouse
events system variables?
System.mouse_button_pressedSystem.mouse_button_shift
System.mouse_button_pressedSystem.mouse_button_shift
What are the types of
calculated columns available?
Summary, Formula, Placeholder column.
Summary, Formula, Placeholder column.
Explain about stacked
canvas views?
Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.
Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.
How does one do off-line
database backups? (For DBA)
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
What is the difference
between SHOW_EDITOR and EDIT_TEXTITEM?
Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.
Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.
What are the built-ins
that are used to attach an LOV programmatically to an item?
set_item_property
get_item_property
(by setting the LOV_NAME property)
set_item_property
get_item_property
(by setting the LOV_NAME property)
How does one do on-line
database backups? (For DBA)
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
How does one backup a
database using RMAN? (For DBA)
The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
( database );
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
( database );
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
What are the different
file extensions that are created by oracle reports?
Rep file and Rdf file.
Rep file and Rdf file.
What is strip sources
generate options?
Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file
Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file
How does one put a
database into ARCHIVELOG mode? (For DBA)
The main reason for running in archive log mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archive log;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling archive log mode. Without it one would not be able to recover. Also, implement an archive log backup to prevent the archive log directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=... and log_archive_format=...
NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.
The main reason for running in archive log mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archive log;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling archive log mode. Without it one would not be able to recover. Also, implement an archive log backup to prevent the archive log directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=... and log_archive_format=...
NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.
What is the basic data
structure that is required for creating an LOV?
Record Group.
Record Group.
