Oracle Interview Questions – Part 28
Q.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.
Q.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.
Q.What are the default parameter that appear at run time in the parameter screen? What are the various sub events a mouse double click event involves?
Destype and Desname.
Q.What are the built-ins used for Creating and deleting groups?
Q.What are the differences between EBU and RMAN? (for DBA)
Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7’s EBU utility. However, there is no direct upgrade path from EBU to RMAN.
Q.How does one create a RMAN recovery catalog? (for DBA)
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
SQL>create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman catalog rman/rman
RMAN>create catalog tablespace tools;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
Q.What are disadvantages of having raw devices ?
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
Q.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.
Q.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.
Q.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.
Q.What is an SQL *FORMS ?
SQL *forms is 4GL tool for developing and executing; Oracle based interactive application.
Q.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.
Q.What are the types of TRIGGERS ?
1. Navigational Triggers.
2. Transaction Triggers.
Q.Identify package function from the following ?
8. Anchor View
Q.Can you change the alert messages at run-time?
If yes, give the name of the built-in to change the alert messages at run-time. Yes. Set_alert_property.
Q.What is the built-in used to get and set lov properties during run-time?
Q.What are the types of canvas-views?
Content View, Stacked View.
Q.Explain types of Block in forms4.0?
Base table Blocks.
1. A base table block is one that is associated with a specific database table or view.
2. A control block is a block that is not associated with a database table. ITEMS
Q.How image_items can be populate to field in forms 4.0?
A fetch from a long raw database column PL/Sql assignment to executing the read_image_file built_in procedure to get an image from the file system.
Q.What is the content view and stacked view?
A content view is the “Base” view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned
Q.What are the modes of parameters that can be passed to a procedure ?
Q.Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
Q.What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
Q.What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
Q.What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
Q.minvalue.sql Select the Nth lowest value from a table?
select level, min(‘col_name’) from my_table where level = ‘&n’ connect by prior (‘col_name’) < ‘col_name’)
group by level;
Given a table called emp with the following columns:
— id number
— name varchar2(20)
— sal number
— For the second lowest salary:
— select level, min(sal) from emp
— where level=2
— connect by prior sal < sal
— group by level
Q.What are various constraints used in SQL?
Q.Can cursor variables be stored in PL/SQL tables. If yes how. If not why?
No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
Q.What is a Package ?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.
Q.What is difference between Procedures and Functions ?
A Function returns a value to the caller where as a Procedure does not.