Wednesday, May 11, 2011

PL/SQL -Q&A - 2011 - Part 3


What is difference between Materialized View and Materialized Log?

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse or in multi-master replication environment.

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.


Materialized View Log - When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.


When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.


Difference between Bigfile & Smallfile?

Big and small file tablespaces in the Oracle 10g database
It is the Oracle Database 10g feature. A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size and on the other hand, a smallfile tablespace can contain many data files.
The size of a bigfile can reach to 128TB depending on the Oracle block size.

An Oracle database can contain both bigfile and smallfile tablespaces.
You can change the default tablespace type to BIGFILE or SMALLFILE.


How to sorting (Order By) LOB column data?
DBMS_LOB.SUBSTR is used to sort the LOB column


Can you give an example for the above?
Yes.

SQL> create table tab1(col1 number, col2 clob);
Table created.


SQL> insert into tab1 values (1,'this is a test record fro clob column');
1 row created.


SQL> insert into tab1 values (2, 'alangottai');
1 row created.


SQL>  insert into tab1 values (3,'mannargudi');
1 row created.


SQL> select * from tab1 order by col2;
select * from tab1 order by col2
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes


SQL> select col1, dbms_lob.substr(col2,50,1) from tab1 order by 2;
         col1 DBMS_LOB.SUBSTR(col2,50,1)
---------- ----------------------------------------------
         2 alangottai
         3 mannargudi
         1 this is a test record fro clob column
 

What are the uses of database trigger?

  • Automatically generate derived column values
  • Enforce complex security authorizations
  • Prevent invalid transactions
  • Enforce referential integrity across nodes in a distributed database
  • Enforce complex business rules
  • Provide transparent event logging
  • Provide auditing
  • Maintain synchronous table replicates
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views


Difference between Row Level Trigger and Statement Trigger?

Row Level Trigger:
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-2 illustrates a row trigger that uses the values of each row affected by the triggering statement.

Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:

Make a complex security check on the current time or working hours, weekday, or user
Generate a single audit record.


Latest Interview QA for PLSQL with basic DBA experience Part 1


Where is the alert log files located on Oracle11g?

The alert.log file normally is in the $ORACLE_BASE//bdump location
Oracle 11g in the database specific location in the diagnostic_dest. Run the below query to find the location,
select value from v$parameter where name='background_dump_dest';


One of the most vexing problems that Oracle DBAs around the world face every day? What is that?
ORA-01555


How to resolve that "ORA-01555: snapshot too old" issue?
The common reason is that the Rollback segments are too small. If in AUM (Automatic Undo Management) mode, increase UNDO_RETENTION setting, otherwise use larger Rollback segments.

Info:  In Oracle database during the DML  (Insert / Update / Delete) operations for any changes to the data records, a snapshot of the record before the changes were made is copied to a rollback segment.


What is the main difference between Data Guard and Oracle RAC (Real Application Clusters)?
An Oracle Data Guard configuration can consist of any combination of single-instance and Oracle Real Application Clusters (RAC) multiple-instance databases.


How does one get the value of a sequence into a PL/SQL variable?
One can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;

But direct assignment can be used in Oracle11g, it is a new feature in 11g.
i := sq_sequence.NEXTVAL;


Can create two before_insert trigger on same table?
Yes.
Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers.

The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.


What is Compound trigger?
Compound trigger is one the new feature in Oracle11g. It allows for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER.


Oracle Forms Interview Questions & Answers Part 1


Oracle Forms – Technical – Interview – Question Answers

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 existing
b) Isolated = The Master can be deleted when the child is existing
c) Cascading = The child gets deleted when the Master is deleted.

What are the master-detail triggers?
On-heck_delete_master
On_clear_details
On_populate_details These are automatically created when you create Master-Details block.

What are the types of Blocks in Forms?
Base Table block  - based on database table/views
Control Block  - non-database items are placed here like Calculation values,buttons,checkbox etc.

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


Can a property clause itself be based on a property clause?
Yes


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.

What are the trigger associated with image items?
When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item


What is trigger associated with the timer?
When-timer-expired.

What is the difference between CALL_FORM, NEW_FORM and OPEN_FORM?
CALL_FORM: start a new form and pass control to it. The parent form will be suspended until the called form is terminated.
NEW_FORM: terminate the current form and replace it with the indicated new form. The old form's resources (like cursors and locks) will be released.
OPEN_FORM: Opens the indicated new form without suspending or replacing the parent form.

When a form is invoked with call_form, Does oracle forms issues
a save point?
Yes


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.

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.


Can one issue DDL statements from Forms?
DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not directly supported from Forms because your Forms are not suppose to manipulate the database structure.
A statement like CREATE TABLE X (A DATE); will result in error:
Encountered the symbol "CREATE" which is a reserved word.
However, you can use the FORMS_DDL built-in to execute DDL statements. Eg:
FORMS_DDL('CREATE TABLE X (A DATE)');

Can one execute dynamic SQL from Forms?
Yes, use the FORMS_DDL built-in or call the DBMS_SQL database package from Forms. Eg:
FORMS_DDL('INSERT INTO X VALUES (' || col_list || ')');
Just note that FORMS_DDL will force an implicit COMMIT and may de-synchronize the Oracle Forms COMMIT mechanism.

What is the difference between the following statements?
Form A:   Insert into emp(ename) values ('MK Maran');
Form B:  FORMS_DDL('insert into emp(ename) values('||''MK Maran')');

User have to commit the form manually for Form A
Once the Form B statement executes, it will be implicitly commited.