If you have a insert query and the ata has & in between , the database accepts that as escape character and there is no issues with inserts. for eg

Insert into TABLE_NAME
(COL_ID, COL1 , COL2, COL3)
Values
(5, ‘a random text ‘, ‘A/B with & sign’, 99);

You may find at times that oracle database is not accepting & although it is between quotes.

What you shoudl do ?

Before starting inserts or running sql files, write this

SET ESCAPE ON;

Although it is on by default but once you do this, you should not be facing any issues.

Some useful queries used in day to day operations

Checking locks in database

SELECT b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username,

a.owner AS object_owner,a.object_name,Decode(b.locked_mode, 0, ‘None’,

1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,

4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,

b.locked_mode) locked_mode,b.os_user_name

FROM   dba_objects a,v$locked_object b

WHERE  a.object_id = b.object_idORDER BY 1, 2, 3, 4;

Memory allocation per session: Read more…

Normal Views

* A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.
* Views have been used to hide the base tables that actually contain the data you are querying
* Ease in query parsing

Materialized views

Materialized views are schema objects that can be used to summarize pre compute replicate and distribute data

Difference between normal view and materialzed view :

* No physical memory used for views as it is just a definition stored in data dictionary
* A view do no derive the change made to it master table after the view is created.
* In materialized view we can not perform DML operation but the reverse is true in case of simple view.

To find more about materialized views Understanding materialized view in Oracle

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

Key Features

• Can define independent tablespace, storage parameters to Materialized views.
• Use Index & Partition
• Use query re-write feature – Process of modifying a query to use the view rather than the base table is called query rewrite.

Types of materialized views

• Materialized view with Aggregates – The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX
• Materialized views containing only joins
• Nested Materialized views – materialized view whose definition is based on another materialized view Read more…

In this post I will explore the common causes of performance issues in database , optimizer statistics , table/index statistics  and table maintenance

Common Causes of Performance issues in Database:

• Database Design : Poor system performance usually results from a poor database design. Consider partitioning, creating materialized views, proper indexes, etc.

• Poorly written  SQL : In about 80% of the cases, this is the cause of the slowness of the queries and load operations.

• Memory : Improper shared pool, buffer cache, log buffer sizes, can consider pining large objects to memory or creating stored outlines.

• Disk I/O : Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc

• Database Contention: Study database locks, latches and wait events carefully and eliminate where possible. Read more…

Further to may last post , Oracle 11g new Features – Part 1 , I am covering some more features here.

Client Query Result Cache

Oracle 11g allows client’s memory to cache query result sets which significantly improves the performance of repetitive queries by OCI-based clients.

Benefits of Client Cache feature

• Extending server-side query caching to client side memory, by leveraging cheaper client-side memory and caching each application’s working set locally.
• Ensuring better performance by eliminating round trips to the server.
• Improving server scalability by saving server resources
• offering transparent cache management: memory management, concurrent access of result-sets.

To start using client cache – set following initialization parameter:
CLIENT_RESULT_CACHE_SIZE = 1G  Read more…

Oracle 11g has some pretty cool new features. I am covering some of the Read Only tables and Server result cache features in this post

Read only Tables

In 10g or prior oracle releases,  table can be made read only by granting the  SELECT object privilege to them. Oracle 11g allows tables to be marked as read-only by ALTER TABLE command.
Example:

Create table
CREATE TABLE orders (
id  NUMBER
);

Mark table as read-only
ALTER TABLE orders READ ONLY;

DML operations
INSERT INTO orders VALUES (2) ;
UPDATE orders SET id = 2;
DELETE FROM orders; Read more…

Want to test your knowledge of PL/SQL ? Take the PL- SQL Interview questions test at skill-guru.

Some of the questions are specific to oracle but over all they would test the concept for PL SQL.  Some of the sample questions

Can a tablespace hold objects from different schemes?

Can a tablespace hold objects from different schemes?

What are Schema Objects?

What is a referential integrity rule?

Take the PLSQL Interview questions test and give us the feedback

Some other related tests which you might find interesting are

OCP 10g Certification

Oracle DBA Interview questions

Oracle 11g Certification Mock test

<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;"><span style="line-height: 19px; white-space: normal;"> </span></span>