How to identify corrupts blocks in the Oracle database and correct it

Comment

Last updated on July 28th, 2019 at 07:58 amThere are two type of corruption. a. Physical corruption: This happens generally due to media corruption. In this case,block does have invalid checksum.it may contains zero. This is generally the result of infrastructure issue like media,network ,memory. RMAN block recover can be used to remove physical corruption We see the error ORA-01578: ORACLE data block corrupted in … Continue reading How to identify corrupts blocks in the Oracle database and correct it »

What is Skew in Database?

Comment

Data skew primarily refers to a non uniform distribution in a data-set. A non-uniform distribution might impact the system if the proper execution plan is not selected depending on the data values. Data is generally said to be skewed if there is presence of significant popular values in the column.But it can happen with non-uniformity of range also. Let us go through example for both to … Continue reading What is Skew in Database? »

How to Solve ORA-00942 table or view does not exist

Comment

ORA-00942 is one of the many errors which Oracle developer ,DBA often gets. Lets first look at the OERR output Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required … Continue reading How to Solve ORA-00942 table or view does not exist »

How to resolve the ORA-00936 missing expression

Comment

Last updated on July 10th, 2019 at 05:22 amDescription ORA-00936 missing expression is one of the common error everybody working in Oracle SQL must have faced some time. This generally happens when you omit important thing in the Sql statement i.e you left out an important chunk of what you were trying to run Reference : Oracle documentation Cause of ORA-00936 missing expression This Oracle error is … Continue reading How to resolve the ORA-00936 missing expression »

ORA-01652: unable to extend temp segment: Solutions

Comment

Last updated on July 10th, 2019 at 05:21 am We receive this error  ORA-01652 while inserting data,creating index, table when we don’t have enough space in tablespace. Solution is to add sufficient space in the tablespace identified in the error ORA-01652 There are three ways to do  it Add datafile Resize the existing datafile auto-extent the datafile This can happen with permanent tablespace  and Temporary … Continue reading ORA-01652: unable to extend temp segment: Solutions »

How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility

Comment

Last updated on July 17th, 2019 at 03:53 pmTracing is a important part for Oracle database tuning. This will help capture all the problem statement , wait event which are responsible for slow running of the session. Oracle database has provided has many quickest ways to trace the local session, other user session   and format the trace to make readable Let us take some look … Continue reading How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility »

How to easily find the Oracle database startup and shutdown time using sqlplus

Comment

Here are the steps required on How to easily find the Oracle database startup and shutdown time using sqlplus step 1)  Create an database directory object create or replace directory data_dir as ‘Specify the Backgound dump Dest location’ / Directory created. CREATE TABLE alert_log ( text_line varchar2(255)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( records delimited by newline fields REJECT ROWS WITH … Continue reading How to easily find the Oracle database startup and shutdown time using sqlplus »

Gathering Statistics in Release 11i and R12

Comment

In order to use Cost Based Optimizer effectively, the statistics must be current. Fortunately, Oracle has provided some scripts and concurrent programs to make this administrative task easier. FND_STATS is a PL/SQL package for the Applications that has numerous functions that will assist in this administrative task. This package can be invoked either from a seeded Concurrent Program, or directly from SQL*Plus. It is not … Continue reading Gathering Statistics in Release 11i and R12 »

how to find indexes on a table in oracle

Comment

Last updated on July 10th, 2019 at 04:11 amIt is very common that you want to Find indexes status and assigned columns for a oracle table And also you sometime want to know the columns which are assigned to the indexes. Following views would be helpful in these cases and to find indexes on a table in oracle dba_ind_columns all_ind_columns Query to Find the indexes … Continue reading how to find indexes on a table in oracle »

How to resolve ORA-29913 with external tables

1 Comment

Last updated on July 10th, 2019 at 05:22 amORA-29913 is one of the common error when working with external tables in Oracle database. We would be looking at various ways the error can be produced and what can be done to resolve the error External File not found or permission incorrect SQL> select * from Scott.example_external_table; select * from example_external_table * ERROR at line 1: … Continue reading How to resolve ORA-29913 with external tables »

Top 30 Most Useful Concurrent Manager Queries

Comment

Last updated on October 8th, 2019 at 05:30 pm What are Concurrent Managers Concurrent Managers are the controllers of background processing for Oracle Applications. The main function of Concurrent Managers is to regulate and control process requests based upon a set of rules. It is a technique used to execute non interactive, data-dependent programs simultaneously in the background. Oracle Applications comes with predefined managers, including … Continue reading Top 30 Most Useful Concurrent Manager Queries »