tag:blogger.com,1999:blog-73225346967496832322024-10-06T20:13:19.679-07:00Database Administrator Tips & Tricks applying with every version of Oracle (i.e. Oracle 8i/9i/10g/11g) including PL/SQL, Trigger, Store Procedure. Also provide how to install Oracle on Linux platform and MS Windows platform and Oracle Error and The Solution to solve themUnknownnoreply@blogger.comBlogger136125tag:blogger.com,1999:blog-7322534696749683232.post-47201254941401610202015-04-08T20:22:00.002-07:002015-04-08T20:22:54.153-07:00

When we are working with multiple applications on Oracle, there might be a need to link those applications together. One of the way to integrate them is though bank end DB Link.  There is one technique I would like to introduce to help on the DBLink performance. This little tuning tip will help a lot when you join the tables from both side of the DBLink.  When we join 2 or more

Anonymoushttp://www.blogger.com/profile/12367462020804776116noreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-75525169235467970222014-02-18T22:53:00.001-08:002014-02-18T22:53:19.641-08:00

There is pro and con for having indexes on the table. The pro is when you use select statement indexes will help boost up the query. However, when you do DML statement (insert, update, delete) you might run into performance issue. The work around of this when you need to insert a lot of data is just don’t use the indexes when you do the insertion. In other words, disable the 

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-85017969573738575632013-08-20T10:40:00.001-07:002013-08-20T10:40:39.560-07:00

This is a good Oracle Performance Tuning video I have came across on youtube. This is a good video for both Developer and DBA. The presenter gave a very good and clear explanation. This is one of the Oracle tutorial series from the same presenter. There were 2 different type of tuning that were discussed in this video System Tuning and SQL Tuning. Roughly, System Tuning involves the appropriate

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-42102846922819788012013-08-20T04:56:00.000-07:002013-08-20T04:56:31.869-07:00

 I came a cross one video on PL/SQL tutorial, and it is a good one. This video give us picture of what oracle PL/SQL is with the very clear and simple presentation. This is a very recommended video for PL/SQL newbie. What were talked on the video were: Why PL SQL? Types of PL/SQL Code Data types - Variables Statements control flow Cursors Exception handling

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-27597550945563019852013-08-17T13:34:00.000-07:002013-08-20T11:03:51.224-07:00

I came across this basic Oracle Explain Plan fundamental tutorial on Youtube. The explain plan is the  basic knowledge that the developers and DBAs may consider getting to understand. The presenter in this video tutorial gave the theory and then went through the explain plan demonstration. What were presented in this tutorial were: Understand of Basic Explain Plan. How to setup Oracle

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-31983850844439629852013-08-06T04:12:00.000-07:002013-08-06T04:12:54.458-07:00

  When you encounter an oracle error ORA-28001: the password has expired and you do not have sysdba priviledge, here is how to handle it. What you need is sqlplus, which is located in ORACLE_HOME/bin. The step to resolve this are: 1. Login to you account by using sqlplus in command prompt     c:\sqlplus or $sqlplus 2. Enter your user and the service     Enter

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-5501774542882587582013-07-31T01:21:00.000-07:002013-08-20T06:01:12.639-07:00

The easy way to check the installed oracle client version in windows operating system: The first way to check  is checking from sqlplus binary. 1. Run sqlplus in command line, and leave it prompting for user name as shown below 2. Check oracle version from windows task manager: If the process shows "sqlplus.exe*32" as in the picture then it's the 32bit version client. If it shows "

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-85545285891431980572013-05-14T14:14:00.000-07:002013-08-20T11:11:08.742-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-29256601932063166272013-02-28T12:13:00.001-08:002013-02-28T12:16:13.580-08:00

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

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7322534696749683232.post-59098764047319281142013-02-28T11:24:00.000-08:002013-02-28T11:24:10.809-08:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-1047046866178866222013-01-24T04:31:00.001-08:002013-05-14T14:22:03.713-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-79181805445113241252012-10-15T21:28:00.001-07:002012-10-15T21:29:42.886-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-2095256211617118022012-09-14T02:31:00.002-07:002013-08-15T01:07:15.461-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-37413464422005356172012-04-16T16:36:00.000-07:002012-04-16T16:36:31.854-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-73949229547677760272011-11-25T05:16:00.000-08:002011-12-20T15:40:06.602-08:00

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  

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-50272128142958553702011-04-12T13:57:00.000-07:002011-11-28T21:53:20.113-08:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-73088681886872957072011-04-12T13:51:00.000-07:002011-04-12T13:51:53.475-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-24305589291979520862011-04-12T13:49:00.000-07:002011-04-12T13:49:37.710-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-54111173974763186822011-04-12T13:27:00.000-07:002011-04-12T13:27:30.523-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-23487293883062423892011-03-22T14:43:00.000-07:002011-03-22T14:43:35.721-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-69082783564625810312011-03-22T14:26:00.000-07:002011-03-22T14:26:51.220-07:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-20011985061038068472010-11-11T23:52:00.001-08:002010-11-11T23:52:51.282-08:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-17386142062961639282010-10-28T03:26:00.000-07:002010-10-28T03:26:25.962-07:00

There might be the case that you find it difficult to recreate the table with another table having foreign keys dependent on it. When you need to recreate the table that mean you need to drop that table first. Apart form losing data and other objects related to that table you will also lost foreign key constraint on every tables which referring to it. There is a little tip top help you get all

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-58056226451436293752010-08-22T11:14:00.000-07:002013-02-28T12:19:27.438-08:00

When trying to add oracle user on unix or linux there might be the case that you get the following error: solaris UX: useradd: ERROR: Inconsistent password files. See pwconv(1M). Here are steps to fix this problem, and what causes this error. The reason behind this error is that the /etc/passwd and /etc/shadow files are out of synchronization on your machine. To fix this, run the following

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7322534696749683232.post-38101984173275280002009-11-05T13:06:00.000-08:002009-11-05T13:10:33.061-08:00

Oracle Real Application ClusterThe video talk about Oracle RAC architecture there are 2 parts of it.Video IVideo II

Unknownnoreply@blogger.com0