The following script compiles all invalid objects in the schema. Login through SQL Plus and execute this file. In case all objects are not compiled even after the execution, execute the same file once or twice more. This is to ensure that all the dependent objects are compiled.
compile.sql
set echo off
set feed off
set sqlp ''
set head off
set pages 0
spool compile_objects.sql
select 'ALTER '||
DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '||
LOWER(object_name)|| ' COMPILE '||
DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects
where status = 'INVALID'
order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ;
spool off
set echo on
set feed on
set sqlp 'SQL>'
set head on
Once the compile.sql is executed, execute the compile_objects.sql generated by compile.sql
MSSQL-Creating a table as duplicate of another table
I was very much frustrated with the simple but syntactical change between Oracle and MS-SQL Server. I was trying to just create a table with the normal Oracle syntax in MS-SQL Server:
CREATE TABLE test AS SELECT * FROM existing_table;
I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL.
The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows:
SELECT *
INTO{new_table}
FROM {existing_table}
WHERE {if_clause if any};
Thus our example can be translated to as:
SELECT *
INTO test
FROM existing_table;
This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.
CREATE TABLE test AS SELECT * FROM existing_table;
I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL.
The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows:
SELECT *
INTO
FROM {existing_table}
WHERE {
Thus our example can be translated to as:
SELECT *
INTO test
FROM existing_table;
This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.
Deleting duplicate rows from Oracle Table
Tip Courtesy: Oracle Tips by Burleson Consulting (dba-oracle)
table_name A
WHERE
a.rowid >
ANY (
SELECT B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);
For a script that does not uses ROWID concept, read this post.
Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This post shows some examples of using SQL to delete duplicate table rows using an SQL sub query to identify duplicate rows, manually specifying the join columns:
DELETE FROMtable_name A
WHERE
a.rowid >
ANY (
SELECT B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);
For a script that does not uses ROWID concept, read this post.
Deleting/Listing duplicate records without ROWID
How to validate values in PL/SQL
I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one.
Validating NULL Values
NVL Function
This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function.
Syntax:
NVL(variable1, variable2)
Example:
SELECT NVL(NULL, 'This is the output') null_test FROM dual;
Result:
null_test
This is the output
Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query.
NVL2 Function
NVL2 function is an extension of NVL function that it can return two values, one when the variable to be tested is NULL and when the variable to be tested is NOT NULL.
Syntax:
NVL2(variable1, variable1_if_not_null, variable1_if_null)
Example 1:
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NULL
Example 2:
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NOT NULL
Validating NOT NULL Values
While validating NOT NULL values there is no need to use such functions as the value will be present already in the variable. But if the check is for whether the value is null or not then NVL2 function will be best suitable for the purpose.
Validating NULL Values
NVL Function
This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function.
Syntax:
NVL(variable1, variable2)
Example:
SELECT NVL(NULL, 'This is the output') null_test FROM dual;
Result:
null_test
This is the output
Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query.
NVL2 Function
NVL2 function is an extension of NVL function that it can return two values, one when the variable to be tested is NULL and when the variable to be tested is NOT NULL.
Syntax:
NVL2(variable1, variable1_if_not_null, variable1_if_null)
Example 1:
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NULL
Example 2:
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NOT NULL
Validating NOT NULL Values
While validating NOT NULL values there is no need to use such functions as the value will be present already in the variable. But if the check is for whether the value is null or not then NVL2 function will be best suitable for the purpose.
Script for getting Oracle table size
There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?
Anyway you can choose to save this query as a function for easy retrieval.
select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;
Read more on what all to remember while getting the size of a table. Click here
Create your own function for the purpose:
CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;
RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/
Example:
SELECT get_table_size('EMP') Table_Size from dual;
Result:
Table_Size
0.0625
Anyway you can choose to save this query as a function for easy retrieval.
select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;
Read more on what all to remember while getting the size of a table. Click here
Create your own function for the purpose:
CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;
RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/
Example:
SELECT get_table_size('EMP') Table_Size from dual;
Result:
Table_Size
0.0625
How to enter a single quotation mark in Oracle
Q: How to enter a single quotation mark in Oracle?
Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.
Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.
SELECT 'test single quote''' from dual;
The output of the above statement would be:
test single quote'
Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.
This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.
Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.
The same example inside PL/SQL I will use like following:
DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;
The output above is same as the Method 1.
Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:
SELECT 'test single quote'||CHR(39) FROM dual;
The output is same in all the cases.
Now don't ask me any other methods, when I come to know of any other methods I will share here.
Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.
Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.
SELECT 'test single quote''' from dual;
The output of the above statement would be:
test single quote'
Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.
This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.
Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.
The same example inside PL/SQL I will use like following:
DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;
The output above is same as the Method 1.
Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:
SELECT 'test single quote'||CHR(39) FROM dual;
The output is same in all the cases.
Now don't ask me any other methods, when I come to know of any other methods I will share here.
Create your own Oracle OTN login for FREE
OTN Login enables you to download Oracle softwares, post in Oracle Forums etc. This is a unique email id which you will use across all Oracle websites excluding Oracle Metalink.
Click here to create your account
Click the Create your Oracle account now. link.
Upon getting the screen enter your email address and a password for accessing the Oracle sites. (Not your email account password)
Click Submit button in the Screen. You will be asked some confirmations hereafter, click them and you will be shown the login screen again. THIS IS ALL you need to do to create your FREE login. You may use this login for downloading softwares from otn website, login to oracle forum for posting queries or answering them.
Click here to create your account
| You will be shown a screen like the following: |
 |
Click the Create your Oracle account now. link.
Upon getting the screen enter your email address and a password for accessing the Oracle sites. (Not your email account password)
| Fill in the mandatory fields in the following screen. Once you click Submit the following screen will be displayed: |
 |
Click Submit button in the Screen. You will be asked some confirmations hereafter, click them and you will be shown the login screen again. THIS IS ALL you need to do to create your FREE login. You may use this login for downloading softwares from otn website, login to oracle forum for posting queries or answering them.
PLSQL predefined exceptions
An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and SQLCODE return values.
ACCESS_INTO_NULL
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE
For a brief description of these exceptions please check this link:
http://www.cs.umbc.edu/help/oracle8/server.815/a67842/06_errs.htm#784
ORA-06502: PL/SQL: numeric or value error
This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.
Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.
For example:
declare
test varchar2(1);
begin
test := 'I am here';
end;
The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4
Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.
Now consider the following example:
declare
test number(2);
begin
test := 100;
end;
The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
So now you know more about this frequently occuring error.
Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;
The above declaration methodology is a very efficient one in handling ORA-06502 error.
2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;
3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.
Why Function based index
Who can read this: A basic understanding of indexes, updates and their effects on indexes would help better understand the topic of this document. This article is intended to be an introduction to the topic of Function based index.
Who should not read this: This document is meant to be a basic manual for those who would like to learn what are function based indexes and what magic can they do. So the persons who are already aware of this concepts, please do not waste your time. Also this concept is confined to Oracle database. There are no general topics discussed in this article.
Now let's start with the topic straightaway:
What and why a function based index is used?
Function based indexes were introduced with Oracle 8i. This was the most coveted features that Oracle came up with its 8i version of database. Traditionally there was no such feature that will use an index if your select statement has a function in it. But with this feature it was possible to use functions such as average, sum and still Oracle will use an index. This is a huge performance gain considering that the data handled by such databases were enormous.
So now you might have got a small idea of what a function based index can do. In a nutshell it is a mechanism whereby frequently accessed functions to a table can be created as a function index and oracle will use the index while such a query is fired. Isn't this a huge performance bonus?
A function based index is created just the same way as the normal index is created. Yes it is with the CREATE INDEX statement that the function based index is created. The following example is borrowed from Tom Kyte (see references for the URL):
SQL> create table emp as select * from scott.emp;
Table created.
SQL> update emp set ename = initcap(ename);
14 rows updated.
SQL> commit;
Commit complete.
SQL> create index emp_upper_idx on emp(upper(ename));
Index created.
In the above section of code, first we are creating a table with the same copy as that of scott.emp, and updating the values with InitCaps. Once the data is committed, the index for UPPER function is created. There is no difference in the clause rather than we can use functions here. Now if any query from an application or from the user with case insensitive queries will use this index.
SQL> set autotrace on explain
SQL> select ename, empno, sal from emp where upper(ename) = 'KING';
ENAME EMPNO SAL
---------- ---------- ----------
King 7839 5000
This is the execution plan of the select query:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=40)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=40)
2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1 Card=1)
It is visible that the INDEX (RANGE SCAN) is used in this case rather than a FULL TABLE SCAN.
This feature becomes more powerful when you consider the fact that user-defined functions could be used as indexes to tables. This will make the application work faster. An example of such a feature is available in the URL provided for Kyte's article.
Privileges required for creating a function based index
1. You must have the query rewrite privilege for your schema
2. You must have the global query rewrite privilege for creating in other schemas
3. If the optimizer has to use a function based index, the following parameters must be set:
a. QUERY_REWRITE_ENABLED=TRUE
b. QUERY_REWRITE_INTEGRITY=TRUSTED (These are modifyable through ALTER SYSTEM, ALTER SESSION statements and as well through init.ora file)
Where not to use function based index
As it is with index, the same criteria holds good for function based index.
References:
Ask Tom: http://asktom.oracle.com/tkyte/article1/
Oracle-Base: http://www.oracle-base.com/articles/8i/FunctionBasedIndexes.php
EXP-00056: Oracle Error when trying to export 10.2.0.3 database
I am trying to export a 10.2.0.3 database, and I am getting an error. I understand that one of the most common causes of these errors is that I am using a higher version of the export utility compared to my RDBMS version. But this is not the case. My database version is 10.2.0.3 and the exp version 10.2.0.3
EXP-00056: ORACLE error 6550 encountered ORA-06550: line 1, column 41:PLS-00302: component 'SET_NO_OUTLINES' must be declared ORA-06550: line 1, column 15: PL/SQL: Statement ignoredEXP-00000: Export terminated unsuccessfully
>> Possbile Solutions :
It could be that your Data Dictionary is not set to support this higher version of export. Sign on to your database as SYS and run the @?/rdbms/admin/catexp.sql script. If that doesn't fix the problem, try rerunning @?/rdbms/admin/catalog.sql and @?/rdbms/admin/catproc.sql scripts.
EXP-00056: ORACLE error 6550 encountered ORA-06550: line 1, column 41:PLS-00302: component 'SET_NO_OUTLINES' must be declared ORA-06550: line 1, column 15: PL/SQL: Statement ignoredEXP-00000: Export terminated unsuccessfully
>> Possbile Solutions :
It could be that your Data Dictionary is not set to support this higher version of export. Sign on to your database as SYS and run the @?/rdbms/admin/catexp.sql script. If that doesn't fix the problem, try rerunning @?/rdbms/admin/catalog.sql and @?/rdbms/admin/catproc.sql scripts.
XML Type conversions in Oracle
Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa.
Consider the following XML
<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>
Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/
The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed
And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed
Consider the following XML
<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>
Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/
The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed
And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:
DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed
Multitable Inserts using INSERT ALL
Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.
--Unconditional Insert into all tables
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes;
-- Pivoting insert to split non-relational data
INSERT ALL
INTO Sales_info VALUES (employee_id,week_id,sales_MON)
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
INTO Sales_info VALUES (employee_id,week_id,sales_WED)
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees GROUP BY department_id;
--Unconditional Insert into all tables
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes;
-- Pivoting insert to split non-relational data
INSERT ALL
INTO Sales_info VALUES (employee_id,week_id,sales_MON)
INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
INTO Sales_info VALUES (employee_id,week_id,sales_WED)
INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees GROUP BY department_id;
Deleting/Listing duplicate records without ROWID
I was hunting for an article when my friend came up and asked me this question.
How to list/delete duplicate records without using ROWID?
I never asked why he do not want to use ROWID, because I was caught in some thinking.
I done some googling, and derived the method, but first the ROWID method
First how to delete records with ROWID, and why?
ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them.
We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this:
Suppose the data after migration stands like this:
Employee_Id Employee_Name
10 Scott
11 Tiger
10 Amanda
10 Nagpal
20 Rawat
It is clear from this data that there are duplicate records, but in a normal data migration there will be millions of records from which identifying the duplicate records will be a nightmare. This is where the following query will save our day:
SELECT Employee_Id, Employee_Name
FROM Employee e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM Employee f
WHERE e.Employee_Id = f.Employee_Id);
The above query will list the following duplicate records:
Employee_Id Employee_Name
10 Amanda
10 Nagpal
We will not get into the decision of what to do with the duplicate records as it is not our discussion topic.
Now what does the MIN(ROWID) implies in the query? It implies that the first record inserted cannot be a duplicate record, and the rest of the data are. Simple isn't it?
Now how to select the same data without ROWID?
Consider the following select statement:
SELECT Employee_Id, Employee_Name,
decode(row_number() over( PARTITION BY employee_id ORDER BY employee_id),1,'Not duplicate','Dupilcate') Dupicate_Criteria
FROM employee;
The following is the data from the query:
EMPLOYEE_ID EMPLOYEE_NAME DUPICATE_CRITERIA
10 Scott Not duplicate
10 Amanda Dupilcate
10 Nagpal Dupilcate
11 Tiger Not duplicate
20 Rawat Not duplicate
In this, you can see the usage of row_number() function with over() clause. In the over() clause we are partitioning the data on basis of the duplicate criteria, that is the Employee_Id. The Order By clause is mandatory so we will use the same Employee_Id. Now the row_number() function assigns running numbers for each Employee_Id. If there are any duplicate records, the row_number() function will return any value greater than 1 (in the running order).
So it is very easy for us to identify the usage of row_number() function and to find an alternative method of getting duplicate rows without using ROWID in our query. Now it is in your hands to do more research and find out more usages.
Thanks for Subesh Kumar who prompted me for a short research in this topic, and hence this article.
How to list/delete duplicate records without using ROWID?
I never asked why he do not want to use ROWID, because I was caught in some thinking.
I done some googling, and derived the method, but first the ROWID method
First how to delete records with ROWID, and why?
ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them.
We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this:
Suppose the data after migration stands like this:
Employee_Id Employee_Name
10 Scott
11 Tiger
10 Amanda
10 Nagpal
20 Rawat
It is clear from this data that there are duplicate records, but in a normal data migration there will be millions of records from which identifying the duplicate records will be a nightmare. This is where the following query will save our day:
SELECT Employee_Id, Employee_Name
FROM Employee e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM Employee f
WHERE e.Employee_Id = f.Employee_Id);
The above query will list the following duplicate records:
Employee_Id Employee_Name
10 Amanda
10 Nagpal
We will not get into the decision of what to do with the duplicate records as it is not our discussion topic.
Now what does the MIN(ROWID) implies in the query? It implies that the first record inserted cannot be a duplicate record, and the rest of the data are. Simple isn't it?
Now how to select the same data without ROWID?
Consider the following select statement:
SELECT Employee_Id, Employee_Name,
decode(row_number() over( PARTITION BY employee_id ORDER BY employee_id),1,'Not duplicate','Dupilcate') Dupicate_Criteria
FROM employee;
The following is the data from the query:
EMPLOYEE_ID EMPLOYEE_NAME DUPICATE_CRITERIA
10 Scott Not duplicate
10 Amanda Dupilcate
10 Nagpal Dupilcate
11 Tiger Not duplicate
20 Rawat Not duplicate
In this, you can see the usage of row_number() function with over() clause. In the over() clause we are partitioning the data on basis of the duplicate criteria, that is the Employee_Id. The Order By clause is mandatory so we will use the same Employee_Id. Now the row_number() function assigns running numbers for each Employee_Id. If there are any duplicate records, the row_number() function will return any value greater than 1 (in the running order).
So it is very easy for us to identify the usage of row_number() function and to find an alternative method of getting duplicate rows without using ROWID in our query. Now it is in your hands to do more research and find out more usages.
Thanks for Subesh Kumar who prompted me for a short research in this topic, and hence this article.
What is PRESENTATION=RO in tnsnames.ora file
Check the entry in tnsnames.ora file:
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
In this article we will be discussing about the PRESENTATION clause in the entry.
Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).
The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.
Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
In this article we will be discussing about the PRESENTATION clause in the entry.
Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).
The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.
Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
Error logging using DBMS_ERRLOG
If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible.
DBMS_ERRLOG, a package introduced in Oracle10g Release 2, allows you to do precisely that. Here is a quick review of the way this package works.
First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS.
1. Create an error log table for the table against which you will execute DML statements:
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP');
END;
Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it makes sense).
For example in our case, the EMP table has this structure:
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
The ERR$_EMP table has this structure:
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ UROWID(4000)
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB VARCHAR2(4000)
MGR VARCHAR2(4000)
HIREDATE VARCHAR2(4000)
SAL VARCHAR2(4000)
COMM VARCHAR2(4000)
DEPTNO VARCHAR2(4000)
From this we can understand that there are columns ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$ which will be created for extra information on the error encountered, the data at the time of the error etc.
2. Add the LOG ERRORS clause to your DML statement:
BEGIN
UPDATE emp
SET sal = sal * 2
LOG ERRORS REJECT LIMIT UNLIMITED;
END;
3. After running your code, you can check the contents of the ERR$ table to see if any errors occurred. You can then perform row by row recovery, or transfer that error information to your application error log, etc.
This approach will not only allow you to continue past exceptions; it will also greatly improve performance of DML processing in which lots of exceptions would normally be raised, because exception handling is quite slow compared to writing a row to a log table via an autonomous transaction procedure.
Check out DBMS_ERRLOG. It could offer some very powerful alternatives ways of executing large numbers of DML statements, each of which might change many rows.
ConclusionThe disadvantage of this approach is in maintenance. The user is likely to report errors like "my data is not showing up, and no errors are shown". My advice on using this feature is to have a highly sophasticated error handling mechanism that handles such errors and throw messages to users. This way the debugging person will also have exact data as to what caused the error. Unless used responsibly this feature can be a disaster.
Jinitiator 1.3.1.28 - Download Page
Download Oracle Jinitiator - Version 1.3.1.28
Download the file and rename the executable to jinit.exe
Click here to download the file
When the download finishes you can install Oracle JInitiator by locating the file using the Windows Explorer and double-clicking on it to start the installation process.
When the installation process has finished, exit your browser and restart it. Return to the initial application starting page and the browser will then load Oracle JInitiator and start your Oracle Developer application.
Oracle JInitiator supports the following browser in Microsoft Windows environment:
Platform IE 5.5 IE 6.0 Netscape Navigator 4.7.x
Windows NT Yes Yes Yes
Windows 2000 Yes Yes Yes
Windows XP No Yes Yes
Windows Vista -- No Not Known
But do make sure with Oracle documentation before installing for a browser.
Other minimum system requirements for Oracle JInitiator are:
Pentium 166 MHz or better processor
70 MB free hard disk space before installing
32-48 MB system RAM minimum for running Oracle Forms applications
Read a brief introduction about JInitiator
Download the file and rename the executable to jinit.exe
Click here to download the file
When the download finishes you can install Oracle JInitiator by locating the file using the Windows Explorer and double-clicking on it to start the installation process.
When the installation process has finished, exit your browser and restart it. Return to the initial application starting page and the browser will then load Oracle JInitiator and start your Oracle Developer application.
Oracle JInitiator supports the following browser in Microsoft Windows environment:
Platform IE 5.5 IE 6.0 Netscape Navigator 4.7.x
Windows NT Yes Yes Yes
Windows 2000 Yes Yes Yes
Windows XP No Yes Yes
Windows Vista -- No Not Known
But do make sure with Oracle documentation before installing for a browser.
Other minimum system requirements for Oracle JInitiator are:
Pentium 166 MHz or better processor
70 MB free hard disk space before installing
32-48 MB system RAM minimum for running Oracle Forms applications
Read a brief introduction about JInitiator
Oracle Jinitiator a brief introduction
Hereafter referred to as Jinit, it is a JVM distributed by Oracle Corporation. Jinit enables the Oracle Forms client application (otherwise known as Oracle D2K in earlier versions) in a web browser.
Earlier the Oracle forms where only available as client installed stand-alone applications which accesses the database and file system to run the application. With the advent of Jinit, the client installation of Oracle Forms run time is not required. The browser plug in for Jinit only must be installed.
While accessing the application for the first time if the plug in is not installed the browser will prompt for the installation. At this point, you must be logged in with Admin rights. You can revert the rights once the installation is done.
Versions
The Jinit referred to in this article is 1.3.1.28 which was rolled on August 2007. The versions is based on some practises. The first two numbers that is 1.3 in our case refers to the Sun Microsystem's JDK version 1.3 or later.
The Jinit referred to in this article is 1.3.1.28 which was rolled on August 2007. The versions is based on some practises. The first two numbers that is 1.3 in our case refers to the Sun Microsystem's JDK version 1.3 or later.
Installing Jinit
Once the URL for the application is configured, use this URL from a supported browser. The browser should prompt for installation of the Jinit plug in. If the browser is not prompting for the installation, then most probably there is problems with configuration. Sometimes the browser may block the plug ins to be installed. Check for the information bar which appears in the browser. Also check the settings for installation of plug ins in your browser.
Once the URL for the application is configured, use this URL from a supported browser. The browser should prompt for installation of the Jinit plug in. If the browser is not prompting for the installation, then most probably there is problems with configuration. Sometimes the browser may block the plug ins to be installed. Check for the information bar which appears in the browser. Also check the settings for installation of plug ins in your browser.
Once the browser prompts for the installation, Install it. Just install with no changes as prompted by Oracle. Once the installation is complete, the application should open in the browser window. Though there are problems with Windows XP, with Internet Explorer the application may not open in the same browser window. Close the browser window and access the application URL once again. If the installation is properly done, the application should open.
Problems with Jinit
Jinit is known not to work with Windows Vista. There is no plans for Sun to support JDK 1.3 on Vista. So Oracle has not certified Jinit for Vista. Vista users should consider using Sun's plug in 1.5.0_06 or 1.6.0_04. This will work for Internet Explorer as well as FireFox browsers.
Jinit is known not to work with Windows Vista. There is no plans for Sun to support JDK 1.3 on Vista. So Oracle has not certified Jinit for Vista. Vista users should consider using Sun's plug in 1.5.0_06 or 1.6.0_04. This will work for Internet Explorer as well as FireFox browsers.
Can I run Jinit In Linux?
Jinit is not only certified for Microsoft Windows platform. Other operating systems must use the Sun's plug in for accessing Oracle forms application through browsers.
For accessing Oracle Forms application from inside Linux operating system, Oracle certifies the usage of Sun plug in for Mozilla 1.5 browser. Use plug in 1.4.2_06 for Mozilla 1.5
What is it's future of Jinit?
The future of Jinit is not bright (thanks to Oracle), it is being replaced by the Sun's JDK 1.5/1.6 in 11g. But it is just a rumour, not a confirmed news.
Appendix: Supported operating systems and web browsers for Jinit
Apple Max OS - Safari Browser - Sun Plug in
Microsoft Windows (XP, 2003, 2000) - Internet Explorer (6), FireFox, Mozilla, Netscape browsers - Jinit / Sun Plug in
Microsoft Vista - Internet Explorer, FireFox Browsers - Sun Plug in
Solaris (2.8/2.9) - Netscape Browser - Sun Plug in
Linux - Mozilla - Sun Plug in
References: Oracle Technology, Wikipedia
How to design a database - The untold way
Rule #1
The design of a database must only be thought after the through analysis of the user requirements.
Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.
Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.
What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.
Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.
This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.
Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.
For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.
Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.
For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.
It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.
Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.
This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.
The design of a database must only be thought after the through analysis of the user requirements.
Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.
Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.
What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.
Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.
This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.
Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.
For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.
Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.
For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.
It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.
Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.
This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.
Oracle Software Patching Using OPatch
The OPatch utility is a tool that allows the application and rollback of interim patches to Oracle products.
Patches are a small collection of files that are copied over an existing installation. They are associated to particular versions of Oracle Products. Patches, when applied to the correct version of an installed product, results in an upgraded version of the product.Interim patches are bug fixes that are made available to customers in response to specific bugs. They require a particular base release or patchset to be installed before they can be applied. They generally address specific bugs for a particular customer. These patches are not versioned and are generally made available in a future patchset as well as the next product release.
About OPatch
OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems.
OPatch supports the following:
-
Applying an interim patch.
-
Rolling back the application of an interim patch.
-
Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
-
Reporting on installed products and interim patch.
Requirements for OPatch
The OPatch utility requires the following:
-
The Oracle home environment variable (
ORACLE_HOME) must point to a valid Oracle home directory and match the value used during installation of the Oracle home directory. -
Java SDK 1.4 or higher, Java commands for Windows and ar, cp, fuser, and, make commands for UNIX must be made available.
-
The library path must be set correctly for Oracle Real Application Clusters environments. OPatch uses some APIs to detect if the system is Real Application Clusters. Ensure that the library path is set correctly as follows:
For Solaris LD_LIBRARY_PATH = $ORACLE_HOME/lib32:$ORACLE_HOME/lib For HP-UX - SHLIB_PATH=$ORACLE_HOME/lib32:/usr/lib
For the latest information about the OPatch utility, and to check for updates, and to get latest versions refer to OracleMetaLink at
Oracle Index and Like clause
Topic: Beginners Level
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
LONG to BLOB Migration
In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype.
Create the LOB Tablespace
CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;
Disable temporarily all Foreign Keys
set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;
Convert LONG to LOB in temporary Table
Create a temporary table with converted BLOB field.
CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;
Drop and Rename Tables
DROP TABLE document;
RENAME lob_tmp TO document;
Create the necessary Constraints and enable the Foreign Keys again
set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_ena_cons.sql;
Courtesy: akadia
Create the LOB Tablespace
CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;
Disable temporarily all Foreign Keys
set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;
Convert LONG to LOB in temporary Table
Create a temporary table with converted BLOB field.
CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;
Drop and Rename Tables
DROP TABLE document;
RENAME lob_tmp TO document;
Create the necessary Constraints and enable the Foreign Keys again
set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_ena_cons.sql;
Courtesy: akadia
Switch to another Oracle User and back
Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is, or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself.
Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose.
Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value. Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.
The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.
spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
spool off
Generated Script setpw.sql
alter user SYS identified by whatever;
alter user SYSTEM identified by whatever;
alter user DES identified by whatever;
alter user ELAN identified by whatever;
Generated Script resetpw.sql
alter user SYS identified by values '5638228DAF52805F';
alter user SYSTEM identified by values 'D4DF7931AB130E37';
alter user DES identified by values 'ABFEC5AC2274E54D';
alter user ELAN identified by values 'B53CE8493EC6FB92';
Source & Credits: www.akadia.com
Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose.
Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value. Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.
The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.
spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
spool off
Generated Script setpw.sql
alter user SYS identified by whatever;
alter user SYSTEM identified by whatever;
alter user DES identified by whatever;
alter user ELAN identified by whatever;
Generated Script resetpw.sql
alter user SYS identified by values '5638228DAF52805F';
alter user SYSTEM identified by values 'D4DF7931AB130E37';
alter user DES identified by values 'ABFEC5AC2274E54D';
alter user ELAN identified by values 'B53CE8493EC6FB92';
Source & Credits: www.akadia.com
How to switch Oracle in Single User-Multi Session Mode
During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this
On Table Level using LOCK TABLE
On Database Level using RESTRICTED SESSION
Exclusive Table Locks (X)
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
$ sqlplus scott/tiger
SQL> GRANT ALL ON emp TO PUBLIC;
SQL> LOCK TABLE emo IN EXCLUSIVE MODE;
$ sqlplus test/test
SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)
Permitted Operations
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
Prohibited Operations
An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
On Table Level using LOCK TABLE
On Database Level using RESTRICTED SESSION
Exclusive Table Locks (X)
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
$ sqlplus scott/tiger
SQL> GRANT ALL ON emp TO PUBLIC;
SQL> LOCK TABLE emo IN EXCLUSIVE MODE;
$ sqlplus test/test
SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)
Permitted Operations
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
Prohibited Operations
An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
Automatically Calculating Percentages in Queries
Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task.
Example: Show percentage of salaries for each department
Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.
column percentage format 99.9
select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;
With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:
column percentage format 99.9
select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;
The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.
Source: www dot akadia dot com
Example: Show percentage of salaries for each department
Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.
column percentage format 99.9
select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;
With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:
column percentage format 99.9
select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;
The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.
Source: www dot akadia dot com
Read a file word by word using DBMS_LOB
Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files.
There are two solutions to read a file with DBMS_LOB
-
The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed.
-
The file is read and processed directly from the filesystem location. This Tip shows exactly this case.
Example:
Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file.
First we have to create an ORACLE directory as the schema owner. Do not add a trailing "/" at the end of the directory path.
sqlplus scott/tiger
SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';
Next we create the procedure READ_FILE_LOB, which reads the file word by word.
CREATE OR REPLACE
Procedure READ_FILE_LOB IS
----------------------------------------------------------------
-- Read an ASCII file word by word with DBMS_LOB package.
--
-- Before you can use this procedure create an ORACLE directory
-- object as the owner of this peocedure.
--
-- sqlplus scott/tiger
-- SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';
--
-- Do not add a trailing "/" at the end of the directory path.
--
----------------------------------------------------------------
-- Input Directory as specified in create directory
l_dir CONSTANT VARCHAR2(30) := 'READ_LOB_DIR';
-- Input File which is read word by word
l_fil CONSTANT VARCHAR2(30) := 'testfile.txt';
-- Separator Character between words is a BLANK (ascii = 32)
l_seb CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(32));
-- Character at the end of the file is NEWLINE (ascii = 10)
l_sen CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10));
-- Pointer to the BFILE
l_loc BFILE;
-- Current position in the file (file begins at position 1)
l_pos NUMBER := 1;
-- Amount of characters have been read
l_sum BINARY_INTEGER := 0;
-- Read Buffer
l_buf VARCHAR2(500);
-- End of the current word which will be read
l_end NUMBER;
-- Return value
l_ret BOOLEAN := FALSE;
BEGIN
-- Mapping the physical file with the pointer to the BFILE
l_loc := BFILENAME(l_dir,l_fil);
-- Check if the file exists
l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1;
IF (l_ret) THEN
dbms_output.put_line('File ' ||
l_fil || ' in Directory ' || l_dir || ' exists');
-- Open the file in READ_ONLY mode
DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
LOOP
-- Calculate the end of the current word
l_end := DBMS_LOB.INSTR(l_loc,l_seb,l_pos,1);
-- Process end-of-file
IF (l_end = 0) THEN
l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
l_sum := l_end - l_pos - 1;
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
EXIT;
END IF;
-- Read until end-of-file
l_sum := l_end - l_pos;
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
l_pos := l_pos + l_sum + 1;
END LOOP;
DBMS_LOB.CLOSE(l_loc);
ELSE
dbms_output.put_line('File ' ||
l_fil || ' in Directory ' || l_dir || ' does not exist');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error:' || SQLERRM);
DBMS_LOB.CLOSE(l_loc);
END;
/
The file testfile.txt has the following content
martin zahn seftigen
Output of the procedure
sqlplus scott/tiger
SQL> exec read_file_lob;
File testfile.txt in Directory READ_LOB_DIR exists
martin
zahn
seftigen
PL/SQL procedure successfully completed.
Article Source: akadia dot com
ORA-06502: PL/SQL: numeric or value error
The docs note this on the ORA-06502 error:
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
More Information by Anantha:
This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.
Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.
For example:
declare
test varchar2(1);
begin
test := 'I am here';
end;
The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4
Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.
Now consider the following example:
declare
test number(2);
begin
test := 100;
end;
The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
So now you know more about this frequently occuring error.
Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;
The above declaration methodology is a very efficient one in handling ORA-06502 error.
2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;
3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.
ORA-04031: Unable to allocate %s bytes of shared memory
ORA-04031: Unable to allocate %s bytes of shared memory
Solutions:
alter system set shared_pool_size=100M; -- size you have to decide based
alter system set large_pool_size=100M; -- on sga_max_size
Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE parameter is not enough for increasing the memory of dynamic parameters, you will get a ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or decrease the memory of dynamic parameter.
alter system set db_cache_size=135m;
alter system set db_cache_size=135m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
alter system set SGA_MAX_SIZE=150M scope=spfile;
System altered.
The above command change the limit of max size to 150M. You have to shutdown and restart the system to make this effect. As of Oracle 9.0.1.1.1, people mostly get ORA-03113: end-of-file on communication channel, when they startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.
The following SQL query will help you to calculate the approximate size of SGA:
select SUM(VALUE)+(1024*1024) from v$parameter where name in (
'db_16k_cache_size','db_2k_cache_size','db_32k_cache_size',
'db_4k_cache_size','db_8k_cache_size','db_cache_size',
'db_keep_cache_size','db_recycle_cache_size',
'java_pool_size','large_pool_size',
'shared_pool_size','log_buffer');
SUM(VALUE)+(1024*1024)
----------------------
156762112
show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- --------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string OFF
db_cache_size big integer 33554432
DB_CACHE_SIZE is a replacement of DB_BLOCK_BUFFERS in the older version of oracle 8i and before. The other parameters db_8k_cache_size, db_4k_cache_size, db_32k_cache_size, db_2k_cache_size, db_16k_cache_size have the initial default value "0". You can change values of these parameters dynamically.
To configure, the multiple block size feature, alter the system as : -
Syntax :- ALTER SYSTEM SET <PARAMETER NAME> = < NEW SIZE> [M|K]
Let us say your block size is 4K and you want to configure database for creating a tablespace of block size 4K and 8K respectively.
For tablespace of blocksize 4K, alter the database as
ALTER SYSTEM SET DB_2K_CACHE_SIZE = 4M;
System altered.
For Tablespace of blocksize 8K, alter the database as
ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M;
System altered.
ALTER SYSTEM SET DB_8K_CACHE_SIZE = 50M;
System altered.
Subscribe to:
Posts
(
Atom
)