Triggers in Oracle 11g -> Execution Order in Oracle 11g

Before Oracle 11g, more than trigger can be created on one table but Oracle doesn't guarantee the order of execution of trigger.  Oracle has introduced the feature in Oracle 11g to execute the trigger in any order as per requirements.

Let’s look the example:

Virtual Column in Oracle

Oracle has introduced new feature in version 11 ie. Virtual column. Virtual column values are not stored on disk. They are generated at runtime using their associated expression.

Lets have a look how to create a table with Virtual Column 

CREATE TABLE testvirtual
(
   id1   NUMBER (10)
 , id2   NUMBER (10)
 , id3   NUMBER (10) GENERATED ALWAYS AS (id1 + id2) VIRTUAL
);
Virtual keyword is optional.

Table Compression in Oracle 11g

Table compression was first introduced in Oracle 9i as a space saver feature only for data warehousing projects. In oracle 11g , this is acceptable as mainstream feature which is acceptable for OLTP Databases. This will result in saving storage space as this will compress the table, increased I/O performance and reduced memory use in the buffer cache. Definitely, this incurs CPU overhead because of compression.

Compression can be done at the tablespace, table or partition level . Options/Features which are available with Compression :

Regular Expression In Oracle

REGULAR EXPRESSION:

            A regular expression is a string or set of characters that describes the structure of some text .This expression helps to search a particular pattern on the line. It is composed of Literals and Metacharacters.

Drop columns from table in fastest way

Columns in the oracle table can have more than 1000 rows of data. If the user asked to drop such columns from the table, dropping the same will take significant amount of time.

By using “Set Unused Column”, it is possible to reduce the significant amount of time while dropping the column from the table.

The syntax used to drop the column from the table is:

Alter table Table Name drop column Column Name;

ORA-32332: Resolution

ORA-32332: Resolution

ERROR at line 1:

ORA-32332: cannot refresh materialized view "Materialized View Name"

as type evolution has occured

Resolution:

1)      Check if the materialized view is valid .

SELECT * from dba_objects where object_name =<view_name>

2)      Check if underneath compoennts are valid

3)      If Underneath all tables are there, check if any field has been added to the same.

If yes, get a complete refresh for materialized view.

In my case, one of the underneath view was invalid.