Understanding Shared Pool Memory Structures: Tips on How to Optimize Usage and Avoid Errors

The Oracle shared pool provides critical services for sharing of complex objects among large numbers of users. Prior to 10g R1, DBAs often spent much time learning about the shared pool memory management to configure and tune shared pool usage; with the tight time constraints and ever-changing workloads, many found this task daunting. The Automatic Shared Memory Management (ASMM) features introduced in 10gR1 solved this problem by providing the DBA a simple, automatic self-tuning mechanism for configuring shared memory components of the SGA, including the buffer cache and shared pool. The Automatic Database Diagnostic Monitor (also introduced in 10gR1) further simplified shared pool related tuning efforts by providing automatic diagnosis and recommendations for application specific issues.

More information : http://download.oracle.com/oowsf2005/003wp.pdf

Below are the video to help you understand more on Oracle Database Architecture.

Read More

ORA-06530: Reference to uninitialized composite

ORA-06530: Reference to uninitialized composite
ORA-06512: at "PROCEDURE/PACKAGE", line 88
ORA-06512: at "PROCEDURE/PACKAGE", line 446
ORA-06512: at line 11
06530. 00000 -  "Reference to uninitialized composite"
*Cause:    An object, LOB, or other composite was referenced as a
           left hand side without having been initialized.
*Action:   Initialize the composite with an appropriate constructor
           or whole-object assignment.

Cause:

This error would be found when using oracle collection table type, and trying to assign the value to the collect. When compiling the code, it would be fine. But, when execute the package/procedure, you will have this error message.

The error caused when trying to extend the define table type object without being initialized. See the scenario below:

CREATE TYPE obj_test AS OBJECT
       (tst_id       number,
        tst_name    VARCHAR2(50));
/
CREATE TYPE tab_test AS TABLE OF obj_test;
/

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/
Solution:

To fix this, simply add initialized object line as shown below:

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last) := type_supplier(NULL, NULL);

        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/

Read More

ORA-06052: PL/SQL: numeric or value error on PL/SQL Collections First and Last

Cause:
The error ORA-06052: PL/SQL: numeric or value error would be found when using for loop on the type collection object, as shown below, and the collection is empty:

FOR i IN type_variable.FIRST..t_variable.LAST LOOP
     ----your code
End Loop ;

When the collection is not empty, error ORA-06052: PL/SQL: numeric or value error would not be found.
This is because FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.


Solution:

Use  FOR i IN 1..t_variable.count instead of FOR i IN type_variable.FIRST..t_variable.LAST , and also apply exception block.

BEGIN
      FOR i IN 1..t_variable.count
           ----your code
      End Loop;
EXCEPTION WHEN OTHERS
     -- error handling code
END; 

Read More

Migrate SUSE Linux to Oracle Linux

If you are thinking of migrating your SUSE to Oracle Linux, there are things to think of and things to be prepared before you do it.

The preinstallation process and pro installation process need to be prepared. Below is the link to get information on the migration. Please read it before you do it.

http://www.oracle.com/technetwork/articles/servers-storage-admin/dell-linux-1498654.html?ssSourceSiteId=ocomen

Read More

The Reason to Upgrade to Oracle 11g Release 2

If you are using Oracle 9i, 10g, and decide to move forward to use 11g, below are what will you get form the 11g R2 upgrade:-

Oracle Database 11g Release 2, the latest release of the award-winning Oracle
Database 11g, enables IT professionals to deliver more information with higher quality of
service, make more-efficient use of their budgets, and reduce the risk of change in
datacenters. By deploying Oracle Database 11g Release 2 as their data management
foundation, organizations can utilize the full power of the world’s leading database to

• Reduce server costs by a factor of 5.

• Reduce storage requirements by a factor of 10. 

• Improve mission-critical system performance by a factor of 10.

• Increase DBA and developer productivity by a factor of 2.

• Maximize availability and eliminate idle redundancy.

• Maximize security and enable compliance.

• Simplify their overall IT software portfolio.

For more information, please visit http://www.oracle.com/us/products/database/039448.pdf.

Read More

How many database Indexes is too many?

Having a lot of indexes on your table will affect the performance of your table either bad side or good side. It depends on what operation you use most.
       
Index will help fasten your query, and at the same time might slow your query down. When you do a lot of insertion, update, deletion based on the index columns, this will slow the query down. On the other hand, it will also help with select statement.

So how many indexes you should have just depends on the characteristic of statement you will use. If you have a lot of insert, update, delete statement, then too many indexes is not so good. While having most of query as select statement, then it is worth having many of them.

Read More

ORA-06512: at line

ORA-06512: at stringline string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.
PostingPosting

---

Read More

For Loop in PL/SQL

One of the statement  in oracle I use most is for loop in pl/sql. This for loop is very handy to use and the basic statement comes like this:


for variable in 1..x 
loop
   statement;
end loop;


The reverse loop statement is:

for variable in reverse 1..x 

loop

   statement;

end loop;

The example code comes like this

for v_cnt in 1 .. 100

       

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

    for v_cnt in reverse 1 .. 100

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

For in pl/sql could also work with cursor, this make handy as I said before. I like to use for loop in pl/sql with cursor so I don't have open and close the cursor, the for loop will do it automatically. Here is how for loop in pl/sql works:


for c_cur in (select statement)
loop
  statement;
end loop;

for c_cur in c_name 
loop
  statement;
end loop;


Where c_name is the declared cursor and c_cur is the variable (you don't need to declare it).

For example:


for c_cur in (select a, b from tab_x) 
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;



for c_cur in declared_cursor
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;

Read More

PL/SQL Best Practices

PL/SQL Best Practices with Steven Feuerstein

Renowned Oracle PL/SQL expert, Steven Feuerstein, presents "PL/SQL Best Practices" -- offering high-level principles to guide our work.

Here are some example of the PL/SQL best practices derived from PL_SQL_Best_Practices2.pdf:


DEV-01: Set Standards and Guidelines for Your Application Before Anyone Starts Writing
DEV-02: Ask for Help if you find Yourself Spending More Than 30 Minutes to Solve a problem
DEV-03: Make Code Review a Regular Part of Your Development Process
DEV-04: Validate Standards by using SQL to Analyze Source Code Stored in the Database
DEV-05: Generate Code Whenever Possible and Appropriate
DEV-06: Set Up —and Use! —A Formal Unit Testing Procedure
DEV-07: Get Someone Else to Perform Functional Tests on your Code

Read More

Database Change Tracking for Oracle

It has often been said that the only constant is change. IT's ability to manage change is an important business success criteria in today's competitive markets. Change can either be planned or unplanned. This presentation will help you understand the effects of change, and how to manage change properly. It will help database administrators better prepare to implement changes and measure the positive (or negative) affects those changes produce on the system.

Read More

Automating SQL Optimization

Quest® SQL Optimizer for Oracle proactively identifies potential performance issues and automates SQL optimization by scanning and analyzing running SQL statements, PL/SQL, or other source code. * The value of SQL tuning to you and your organization * What the ideal solution looks like * How to use the key functions and new features in Quest SQL Optimizer for Oracle v7.2

Read More

Common Mistakes in Oracle PL/SQL Programming

I just came a cross this video about oracle pl/sql programming, which provide good technique to write pl/sql code.
Very good video to take a look for pl/sql programmer.



You wrote an explicit cursor? You declared a variable using the VARCHAR2 datatype? You raised an exception to skip over unnecessary lines of code?

If slip-ups like these sound familiar (or if you don't know why these are mistakes), join Quest for a one-hour Webcast featuring guest speaker, Steven Feuerstein, as he examines common mistakes in Oracle PL/SQL programming. Learn how to correct mistakes to improve the maintainability and performance of your application.

This Webcast will also demonstrate CodeXpert — a powerful feature available within Toad® that will help you avoid common mistakes and ensure you implement PL/SQL best practices in your daily activities.

About the Guest Speaker:
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written nine published books on PL/SQL (all from O'Reilly & Associates). Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and serves as a Senior Technology Advisor to Quest Software.

Read More

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:
The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Solution:
1. Check your tnsnames.ora as shown in my post on ora-12154.
2. Check if your listener service are on, if not start your lisnter using lsnrctl. More information on this take a look at the same post at ora-12154. 
3. Check if the service is on using command line lsnrctl services
4. Check your listener.log 

Read More

ORA-12154: TNS:could not resolve the connect identifier specified

Cause:
A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Solution:
1. Check your tnsnames.ora.
Check your tnsnames.ora if it is configured property the key to check is SID, dba host name or ip address. The syntax are something similar this this:

ORA_TNS =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA-10)
 )
)

2. Check your listener.ora if sid_list_listenner setup is there.
By Default when you install new oracle database the listener.ora would contain only listener configuration section but no sid_list_listenner setup. In many occasion when I have this ora-12154 problem the solution that help me is modifying listener.ora, adding SID_LIST_LISTENER section the syntax should be something similar to the below syntax:

SID_LIST_LISTENER =

   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
      (SID_NAME = ORA-10)
    )
  )

The complete file are as shown below

# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
       (SID_NAME = ORA-10)
     )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

After adding SID_LIST_LISTENER in your listener.ora, restart or reload your listener service. I prefer using command line lnsrctl follow by parameter reload or restart, or you can just type lnsrctl and then enter and type reload or restart. With this command line (lsnrctl) you can also check oracle listener status by enter parameter status. 

3. Check the environmental variables
Check if the environmental variables like ORACLE_HOME, ORACLE_SID, ORACLE_BASE, ORACLE_PATH do exist. The most important environmental variables are the first two variable ORACLE_HOME, ORACLE_SID.

Tools
The very handy tool you can use to check the connection is the command tnsping follow by your service name configured in your tnsnames.ora from the example above is ORA_TNS.

Read More

SQLLoader with text file format

When loading text file into db via sqlloader, there might be the case that varchar2 column with Thai character data turn out weird (i.e. instead of showing "รถยนต์นั่งไม่เกิน 7 คน" it turn out to be "เธžเธฃเธš เธฃเธ–เธขเธ™เธ•เนŒเธ™เธฑเนˆเธ‡เน„เธกเนˆเน€เธเธดเธ™ 7 เธ„เธ™").

The cause of this problem, from what I experienced, is that the format/encoding of text file is invalid.
To solve this you need to open the file with your fave text editor, then use "save as" menu and choose file format/encoding as ANSI/ASCII (default one is the current format/encoding of the file).

Read More