Friday, April 9, 2010

Moving or Renaming oracle datafiles from one drive to another drive

Moving or Renaming oracle datafiles from one drive to another drive
==================================================================

SQL> conn sys@db11g as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>host move C:\oracle11G\app\era-1\oradata\db11g\SYSAUX01.DBF
E:\oracle11g_datafile\SYSAUX01.DBF;

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 176161684 bytes
Database Buffers 352321536 bytes
Redo Buffers 5844992 bytes
Database mounted.


SQL> alter database rename file 'C:\oracle11G\app\era-1\oradata\db11g\SYSAUX01.DBF'
2 to 'E:\oracle11g_datafile\SYSAUX01.DBF';

Database altered.

SQL> alter database open
2 ;

Database altered.

SQL>

Thursday, April 8, 2010

search string value in an Oracle Database schema

==========================More fast one is ===========
Author:- Tom kyte

CREATE OR REPLACE PROCEDURE basel2.find_string (p_str IN VARCHAR2)
AUTHID CURRENT_USER
AS
l_query LONG;
l_case LONG;
l_runquery BOOLEAN;
l_tname VARCHAR2 (2000);
l_cname VARCHAR2 (4000);

TYPE rc IS REF CURSOR;

l_cursor rc;
BEGIN
DBMS_APPLICATION_INFO.set_client_info ('%' || UPPER (p_str) || '%');

FOR x IN (SELECT *
FROM user_tables)
LOOP
l_query :=
'select distinct '''
|| x.table_name
|| ''', $$
from '
|| x.table_name
|| '
where ( 1=0 ';
l_runquery := FALSE;
l_case := NULL;

FOR y IN (SELECT *
FROM user_tab_columns
WHERE table_name = x.table_name
AND data_type IN ('VARCHAR2', 'CHAR')) ----you add here more datatype
LOOP
l_runquery := TRUE;
l_query :=
l_query
|| ' or upper('
|| y.column_name
|| ') like userenv(''client_info'') ';
l_case :=
l_case
|| '||'' ''|| case when upper('
|| y.column_name
|| ') like userenv(''client_info'') then '''
|| y.column_name
|| ''' else NULL end';
END LOOP;

IF (l_runquery)
THEN
l_query := REPLACE (l_query, '$$', SUBSTR (l_case, 8)) || ')';

BEGIN
OPEN l_cursor FOR l_query;

LOOP
FETCH l_cursor
INTO l_tname, l_cname;

EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ('Found in ' || l_tname || '.' || l_cname);
END LOOP;

CLOSE l_cursor;
END;
END IF;
END LOOP;
END;
/

--grant execute on find_string to public;


exec basel2.find_string ('halim');

How to pass parameter to a oracle view

How to pass parameter to a oracle view
========================================

you can't design a view that automatically asks the user for parameters at run time.

You can have a view based on parameters, which can be stored in a number of ways, including
(1) SYS_CONTEXT variables
(2) GLOBAL TEMPORARY TABLE
(3) Package variable
(4) Regular table
(5) With a Function (pipeline function is better)

The user has to remember to set the parameters before using the view.


1. First create view like follow
---------------------------------
CREATE OR REPLACE VIEW scott.test_parameter_view_date
AS
SELECT *
FROM emp
WHERE hiredate = TO_DATE (USERENV ('client_info'), 'dd-mon-yyyy');

--to_char(timstamp,'ddmmrrrr') =userenv('client_info')
--To_Date(SYS_CONTEXT ('userenv','client_info'),'dd-mon-yyyy')


2. then execute the following ...
--------------------------------

exec dbms_application_info.set_client_info('22-Feb-1981');


3. Done your view , now select the view
----------------------------------------

SELECT * FROM scott.test_parameter_view_date;


Note:-
======
You can also use dbms_session.set_context in 8i and up to
greatly increase the namespace/number of parameters you can
pass and reference via the SYS_CONTEXT() functions.


==================Another Example===============================

On remote db
================
REMOTEDB> create view myview
as select * from dual
where sys_context ('userenv', 'client_info') = 1

View created.


REMOTEDB> grant select on myview to public
/

Grant succeeded.


On LOCAL DB:
===============

DEV> exec dbms_application_info.set_client_info@REMOTEDB(1)

PL/SQL procedure successfully completed.

DEV> select DUMMY from test_schema.myview@REMOTEDB
/
-----------
X



3) example of package variable



CREATE TABLE CITIES
(
REGION VARCHAR2 (30),
COUNRTY VARCHAR2 (30),
CITY VARCHAR2 (30)
);

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATED','NEW YORK');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','FRANCE','PARIS');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','JAPAN','TOKYO');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','INDIA','MUMBAI');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','UNITED KINGDOM','LONDON');

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATES','WASHINGTON DC');

COMMIT;

CREATE OR REPLACE PACKAGE PKG_PARAM AS
PROCEDURE SET_REGION (P_REGION IN VARCHAR2);
FUNCTION GET_REGION RETURN VARCHAR2;
END PKG_PARAM;
/

CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS
REGION VARCHAR2 (30);
PROCEDURE SET_REGION (P_REGION IN VARCHAR2)
IS
BEGIN
REGION := P_REGION;
END;
FUNCTION GET_REGION RETURN VARCHAR2
IS
BEGIN
RETURN REGION;
END;
END PKG_PARAM;
/

CREATE OR REPLACE VIEW PARAM_VIEW AS
SELECT *
FROM CITIES
WHERE REGION=PKG_PARAM.GET_REGION;

SELECT * FROM PARAM_VIEW;


EXEC PKG_PARAM.SET_REGION('ASIA');


SELECT * FROM PARAM_VIEW;



==========================================================
Multiple parametrized view in oracle with package variable
==========================================================


CREATE OR REPLACE PACKAGE pkg_parameter_in_view
AS
PROCEDURE set_parameter (
p_brancd IN VARCHAR2,
p_actype IN VARCHAR2,
p_actnum IN VARCHAR2
);

FUNCTION get_parameter_brancd
RETURN VARCHAR2;

FUNCTION get_parameter_actype
RETURN VARCHAR2;

FUNCTION get_parameter_actnum
RETURN VARCHAR2;
END pkg_parameter_in_view;
/




CREATE OR REPLACE PACKAGE BODY pkg_parameter_in_view
AS
s_brancd VARCHAR2 (30);
s_actype VARCHAR2 (50);
s_actnum VARCHAR2 (50);

PROCEDURE set_parameter (
p_brancd IN VARCHAR2,
p_actype IN VARCHAR2,
p_actnum IN VARCHAR2
)
IS
BEGIN
s_brancd := p_brancd;
s_actype := p_actype;
s_actnum := p_actnum;
END;

FUNCTION get_parameter_brancd
RETURN VARCHAR2
IS
BEGIN
RETURN s_brancd;
END;

FUNCTION get_parameter_actype
RETURN VARCHAR2
IS
BEGIN
RETURN s_actype;
END;

FUNCTION get_parameter_actnum
RETURN VARCHAR2
IS
BEGIN
RETURN s_actnum;
END;
END pkg_parameter_in_view;
/



CREATE OR REPLACE FORCE VIEW atmutl.vw_account_transaction_test (brancd,
doctyp,
docnum
)
AS
SELECT brancd, doctyp, docnum
FROM test.act_master
WHERE brancd = pkg_parameter_in_view.get_parameter_brancd
AND actype = pkg_parameter_in_view.get_parameter_actype
AND actnum = pkg_parameter_in_view.get_parameter_actnum
/


select * from test.vw_account_transaction_test;


EXEC test.PKG_PARAMeter_in_view.SET_parameter('333','S01','333456789333') ;



select * from test.vw_account_transaction_test ;




4) example of Regular table
=====================


We created a little table that had criteria in it that could be changed on occasion. The table only had 1 row on it. The view joined to the table and used the fields.

Lets just say we called it Parm_Tbl. It had a few fields that served as controls to the view

select col1, col2, col3
from tableX, parm_tbl
where col5 = Parm_tbl.var1
and col6 = Parm_tbl.var2

This may or may not help depending on how often the variables need to change.




Following are the advantages of parameterized views.
=====================================================

1. The biggest advantage is that it allows you to
join the view with other tables/views thus
allowing you to write set based code. An approach
using procedure that returns a ref cursor does not
allow this and I found that to be a major limitation.
This of course assumes that you want to write the
view with supplying the values at run time (i.e.
write a parameterized view.)
2. In corner cases, it may help performance as you
mentioned above (in rare cases when optimizer is
not able to merge.)

Cons:
1. It is a painful thing to set the context values
each time before testing. If your views were not
parameterized you can run simple selects from the
views (e.g. select * from v1) which of course
parameterized views dont allow untill you run
the context setting code.

Overall, I felt that in general, one is better off not
using them - use straight forward views. In case of
performance issues, try the param views out. But dont
start out with it in the first place if your needs
are fulfilled without them (Which would be the case
most of the time.)

How to Find, Which product are installed or not installed in oracle database

How to Find, Which product are installed or not installed in oracle database
==========================================================

1. Execute the query then value is true means installed
and false means not installed.

SELECT parameter, VALUE
FROM v$option

2. in unix

$> opatch lsinventory -detail