May 29, 2009

Pinning in M/R // Background Process

Pinning stored procedure/function to shared pool in 11G

execute dbms_shared_pool.keep(owner.trigger, 'R');

---------------------------------------------

Script displays instance background process information. The script works when the database is MOUNTed or OPENed.

select A.SID,A.SERIAL#,A.PROGRAM,P.PID,P.SPID,A.OSUSER, /* Who Started INSTANCE */A.TERMINAL,
A.MACHINE,A.LOGON_TIME,B.NAME,B.Description,P.PGA_USED_MEM,P.PGA_FREEABLE_MEM,P.PGA_MAX_MEM
from v$session A,v$process P,v$bgprocess B where A.PADDR=B.PADDR AND A.PADDR=P.ADDR and
A.type='BACKGROUND';

ESCAPE With LIKE Operator

ESCAPE With LIKE Operator

select ename from scott.emp where ename like '%_%';

But you will be surprised to see the results:

ENAME
------------
Will dispaly all Data in the Table

it is because _ is a wild card character. That is _ stands for any character. So the query yielded all the rows.

modified query to get the desired output as below:


select ename from scott.emp where ename like '%#_%' escape '#';


ENAME
------------
FRA_KLIN

May 28, 2009

About TNS file

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.

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.

Benefit of Cursor For loop for 10g

Which will run much slower than the other two, and why?

a.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
BEGIN
FOR employee_rec IN employees_cur LOOP
do_stuff (employee_rec);
END LOOP;
END;

b.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
l_employee employees%ROWTYPE;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur INTO l_employee;
EXIT WHEN employees_cur%NOTFOUND;
do_stuff (l_employee);
END LOOP;
CLOSE employees_cur;
END;

c.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT () = 0;
FOR indx IN 1 .. l_employees.COUNT
LOOP
do_stuff (l_employees (indx));
END LOOP;
END LOOP;
CLOSE employees_cur;
END;

(b) Is the slowest. That's because on Oracle 10g and higher, the PL/SQL optimizer will automatically rewrite cursor FOR loops so that they are executed in the same way as the BULK COLLECT query.

Tuning the LIKE-clause

Tuning the LIKE-clause (by using reverse key indexes)

For tuning Like operator (like '%SON') is to create a REVERSE index - and then programmatically reverse the LIKE-clause to read LIKE 'NOS%'

Steps:

CREATE INDEX Cust_Name_reverese_idx
ON customer(Cust_Name) REVERSE;

2. Programmatically reverse the SQL LIKE-clause to read '%saliV%':

SELECT * FROM customer WHERE Cust_Name LIKE '%Vilas%'

New Query:

SELECT * FROM customer WHERE Cust_Name LIKE '%saliV%';

Quote Operator

select 'Oracle''s web blog. It''s personal.' str from dual;

By Q - quote operator the above statement can also be represented as any one of the below.


Different ways to Use Quote Operator.


select q'(Oracle's web blog. It's personal.)' str from dual;
select q'[Oracle's web blog. It's personal.]' str from dual;
select q'Oracle's web blog. It's personal.A' str from dual;
select q'/Oracle's web blog. It's personal./' str from dual;
select q'ZOracle's web blog. It's personal.Z' str from dual;
select q'|Oracle's web blog. It's personal.|' str from dual;
select q'+Oracle's web blog. It's personal.+' str from dual;
select q'zOracle's web blog. It's personal.z' str from dual;

Locks , Dummy Table

To get the locks on an object

SELECT oracle_username USERNAME,owner OBJECT_OWNER,object_name, object_type, s.osuser,s.SID SID,s.SERIAL# SERIAL,DECODE(l.block,
0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD
FROM gv$locked_object v, dba_objects d,gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) and v.session_id = s.sid ORDER BY oracle_username, session_id;

-------------------------------------------------------------
To create our own much faster DUMMY Table


CREATE TABLE MYDUAL(DUMMY VARCHAR2(1) PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;

Database vs Data warehouse

Similarities in Database and Data warehouse:

* Both database and data warehouse are databases.
* Both database and data warehouse have some tables containing data.
* Both database and data warehouse have indexes, keys, views etc.

Differences

* The Application database is not your Data Warehouse for the simple reason that your application database is never designed to answer queries.

* The database is designed and optimized to record while the data warehouse is designed and optimized to respond to analysis questions that are critical for your business.

* Application databases are On-Line Transaction processing systems where every transition has to be recorded, and super-fast at that.

* A Data Warehouse on the other hand is a database that is designed for facilitating querying and analysis

* A data warehouse is designed as On-Line Analytical processing systems . A data warehouse contains read-only data that can be queried and analyzed far more efficiently as compared to regular OLTP application databases. In this sense an OLAP system is designed to be read-optimized.

* Separation from your application database also ensures that your business intelligence solution is scalable, better documented and managed and can answer questions far more efficiently and frequently.

Data warehouse is better than a database:

The Data Warehouse is the foundation of any analytics initiative. You take data from various data sources in the organization, clean and pre-process it to fit business needs, and then load it into the data warehouse for everyone to use. This process is called ETL which stands for ‘Extract, transform, and load'.

Suppose you are running your reports off the main application database. Now the question is would the solution still work next year with 20% more customers, 50% more business, 70% more users, and 300% more reports? What about the year after next? If you are sure that your solution will run without any changes, great!! However, if you have already budgeted to buy new state-of-the-art hardware and 25 new Oracle licenses with those partition-options and the 33 other cool-sounding features, you might consider calling up Oracle and letting them know. There's a good chance they'd make you their brand ambassador.

Creation of a data warehouse leads to a direct increase in quality of analyses as you can keep only the needed information in simpler tables, standardized, and denormalized to reduce the linkages between tables and the corresponding complexity of queries.

A data warehouse drastically reduces the cost-per-analysis and thus permits more analysis per FTE.


(Data Adopted)

Check Constraint

Check Constraint

CREATE TABLE EMP_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));

This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.

Some Good Queries

To get size of a table

select segment_name table_name,sum(bytes)/(1024*1024) table_size_meg
from user_extents where segment_type='TABLE'
and segment_name = 'EMP_MAST' group by segment_name

---------------------------------------------------------------
To Select only unlocked rows

select * from emp_MAST for update skip locked;

CHECK THIS USING TWO SQL PLUS AND SESSION BROWSER IN TOAD

UPDATE EMP_MAST SET ENAME='p' WHERE EMPNO=7369 ; (IN FIRST SQLPLUS DONT EXECUTE COMMIT )

select * from emp_MAST for update skip locked; (IN SECOND SQLPLUS)
--------------------------------------------------------
To get numbers of records in all tables in a Schema

select table_name,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name)
),'/ROWSET/ROW/C')) count from user_tables order by 1

---------------------------------------------------------
To generate a CSV output of a Table

select regexp_replace(column_value,' *<[^>]*>[^>]*>',';')
from table(xmlsequence(cursor(select * from EMP_MAST)));

--------------------------------------------------------------
To get Version and login naem

select OLAPSYS.version,sys.LOGIN_USER from dual
--------------------------------------------------

select APEX_UTIL.get_since(sysdate-10) /* get how many days ago */,APEX_UTIL.url_encode('http://www.oracle4u.com')   from dual;

-------------------------------------------
select round(12.55555E78,2) from dual ---- For numeric overflow  round function will not work

------------------------------------------
How to do pattern search in a subquery using LIKE


with dt as( select distinct  BOTTLETYPE  from  DRINKS WHERE BOTTLETYPE is not null )
select * from  tabled,dt where name like ''||dt.BOTTLETYPE||'%' order by 2

OR

select * from  tabled df,(select distinct BOTTLETYPE from DRINKS WHERE BOTTLETYPE is not null) gh   where name like ''||gh.BOTTLETYPE||'%' order by 2


OR

SELECT  * FROM  tabled df WHERE EXISTS (SELECT distinct BOTTLETYPE  FROM DRINKS gh WHERE BOTTLETYPE is not null and df.name LIKE ''||gh.BOTTLETYPE||'%') ;
----------------
Check for hidden database user
----------------
Run OS Commands via PLSQL
----------------
Run OS Commands via DBMS_SCHEDULER
----------------
Run OS Commands via Create Table
--------------------------------

May 25, 2009

Much More About Constraints

PRIMARY KEY LOOK UP DURING FOREIGN KEY CREATION


The lookup of matching primary keys at time of foreign key insertion takes time.In realease Oracle 9i , the first 256 primary keys can be cached so the addition of multiple foreign keys become significantly faster .The cache is only set up after the second row is processed.this avoid overhead of setting up a cache for single row DML.

----------------------
Constraints On View
--------------------
Constraint definitions are done on View from Oracle 9i onwards.
Views constraint definitions are declarative in nature;therefore DML operations on view are subject to the constraints defined on base tables.
defining constraints on base table is necessary ,not only for data correctness and cleanliness but also for MV query

NOT NULL and CHECK constraint are not supported on Views

For Creating View with constraints you must specify [RELY|NORELY ](Allows/disallows query rewrites) DISABLE NOVALIDATE (valid state for view constraint )
Otherwise it will result an error message.

May 21, 2009

Trigger (Insertion on Same Table)

Trigger to insert a field from sequence at user insertion itself by avoiding mutating table error. For example ; for a table as 'TABLE_NAME' with fields N_FIELDNAME,N_CODE,N_ID,VC_NAME,DT_DATE here excluding N_FIELDNAME field all other fields are user input; N_FIELDNAME field value is retrieved from SEQUENCE.If we use
select query on same table 'TABLE_NAME' it will result into Mutating table error
.So this piece of trigger code is used to do the above.

CREATE OR REPLACE TRIGGER TRG_NMAE
BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
DECLARE
N_VARIABLE NUMBER(10):=0;
BEGIN
SELECT SEQUENCE_NAME.NEXTVAL INTO N_VARIABLE FROM DUAL;
:NEW.N_FIELDNAME :=N_VARIABLE;
END ;

DB LINK

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Here example are given from Oracle database to another Oracle database .

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY password
USING sid_name

In Some situation even though user_name,sid_name and password is correct database link created will result to a failure ;this is because Oracle cannot able to get the connection because of some reason ;in-order to meet this situation we want to specify more details for DBLink creation then this script cam be used.

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY 'password'
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521)))
(CONNECT_DATA=(SID=sid_name)))';

Errors

1)
By using DBLink DDL operations are not allowed .Such as Create ,Drop ,Truncate etc

Truncate table TABLENAME@DBLINK
ORA-02021: DDL operations are not allowed on a remote database


2)
If a Table which containing LOB datatypes are not able select using dblink

create table test_db ( fild_db clob,num number)

select * from test_db@convert;
ORA-22992: cannot use LOB locators selected from remote tables

Here only selection of datatypes other than LOB are allowed

select num from test_db@convert; --> This Works Fine.

3)

Before dropping a DBLink all transactions should be end else this error will occur.
ORA-02018: database link of same name has an open connection

---------------------------------

May 11, 2009

JOBS /// IsNumeric

Job will execute in a current schema only ;It will not switch between two schemas .

------------------------------
IsNumeric in ORACLE
----------------

select ISNUM from (
select LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) ISNUM from table_name ) where ISNUM is null


create or replace function isnumeric (param in char) return boolean as
dummy varchar2(100);
begin
dummy:=to_char(to_number(param));
return(true);
exception
when others then
return (false);
end;
/