Michael A. Georgiou マイケル

Find logs for java application that is deployed on OC4J

2012-01-23T15:36:00.002+02:00

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Find logs for java application that is deployed on OC4J

2012-01-23T15:36:00.000+02:00

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Deploy ear application on Standalone oc4j

2012-01-20T09:50:00.003+02:00

Deploy ear application on Standalone oc4j
1. Right click on “Project” and select “New”.
2. Select “Deployment Profiles” and on the right screen select “WAR file”. Click OK
3. Write the “Deployment Profile Name”: webapp1. Click OK.
4. Select under WEB-INF/classes, the Contributors and on the right screen select all the checkboxes. Click OK.
5. Right click on the webapp1 /weapp2 and select “Deploy to EAR file”.
6. Put ear under : C:\FileMover\oc4j_home\oc4j\j2ee\home\applications
7. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\server.xml.
---application name="FileMoverMg" path="../../home/applications/FileMoverMg.ear" auto-start="true" />
8. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\default-web-site.xml
---web-app application="FileMoverMg" name="FileMoverMg" root="/FileMoverMg" />

Revoke object granted to PUBLIC for a specific USER

2011-12-28T12:37:00.000+02:00

SELECT ' revoke all on '||owner||'.'||table_name||' from public;'
FROM table_privileges
WHERE grantee = 'PUBLIC'
AND OWNER='UM'
ORDER BY owner, table_name;

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.

2011-12-28T12:34:00.002+02:00

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.
====================================================================================
--Create the tigger and the hr_s role under sys.

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
DECLARE
main_sql_text varchar2(100);
job_name_ varchar2(30);
job_type_ varchar2(30);
job_action_ varchar2(4000);
sql_text varchar2(400);
job_id_ varchar2(30);
start_date_ timestamp;
BEGIN

if (ora_sysevent='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner='HR' )then
sql_text := 'grant select on '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to hr_s';
select to_char(systimestamp,'DDMMYYYYHH24MISS') into job_id_ from dual;
select SYSTIMESTAMP + interval '1' minute into start_date_ from dual;
main_sql_text:=''''||sql_text||'''';
job_name_:='job'||job_id_;
job_type_:='PLSQL_BLOCK';
job_action_:='begin execute immediate('||main_sql_text||'); end;';
execute immediate ('begin DBMS_SCHEDULER.create_job (job_name=> :a,job_type=> :b,job_action => :c,start_date=> :d,repeat_interval => NULL,end_date=> NULL,enabled=>TRUE,comments=> NULL); end;') using job_name_,job_type_,job_action_,start_date_;
end if;
END ddl_trig;
/

--Grant the role on a privelges users.

Prepare Oracle Linux 6 for Installing Oracle 11gR2 (rpm packages))

2011-12-27T12:48:00.003+02:00

Prepare Oracle Linux 6 for Installing Oracle 11gR2
by Michael Georgiou
============================================================
mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
binutils-2
glibc-2
nss-softokn-freebl-3
glibc-2
nss-softokn-freebl-3
compat-libstdc++-33
glibc-common-2
glibc-devel-2
glibc-headers-2
elfutils-libelf-0
elfutils-libelf-devel-0
gcc-4
gcc-c++-4
ksh-
libaio-0
libaio-devel-0
libgcc-4
libstdc++-4
libstdc++-devel-4
make-3.81mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
numactl-devel-2
sysstat-9
compat-libstdc++-33
compat-libcap

All packages are available :/media/OL6.0 i386 Disc 1 20110203/Server/Packages

SQL Performance

2011-12-23T12:45:00.000+02:00

DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start number DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000
LOOP
OPEN l_rc FOR
'select object_name
from all_objects
where object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
dbms_output.put_line(l_dummy);
END LOOP;
dbms_output.put_line(ROUND((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' );
END;
/

Create schema statistics using DBMS_STATS

2011-12-22T13:03:00.002+02:00

dbms_stats.gather_schema_stats
(
ownname=>'HR',
cascade=>TRUE, ---Indexes are included
method_opt=>'FOR ALL INDEXED COLUMNS',--Create Histograms
estimate_percent => dbms_stats.auto_sample_size, --Takes Sample size for the tables and Indexes
degree => 2 --Parallel degree 2
);

Create Service in a single Instance database

2011-12-21T14:09:00.002+02:00

exec DBMS_SERVICE.CREATE_SERVICE('TPCH','TPCH');
exec DBMS_SERVICE.START_SERVICE('TPCH','ORCL');

TPCH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TPCH)
)
)

sqlplus system/xxxxxx@TPCH

Package persistent stage - Example

2011-11-14T14:08:00.002+02:00

Package persistent stage - Example
--------------------------------------------------------------------------------------- Every time ( in the same session ) that a procedure EMP is executed it returns the next 30 rows from table employees until the cursor returns all rows.

CREATE OR REPLACE PACKAGE EMP AS

/* TODO enter package declarations (types, exceptions, methods etc) here */
cursor c is select * from employees;
return_rows number:=30;
procedure PRINTEMPLOYEES ;
END EMP;
----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY EMP AS

procedure PRINTEMPLOYEES
as
cr c%ROWTYPE;
fetched number:=0;
BEGIN

IF NOT c%ISOPEN THEN
OPEN c;
END IF;
loop
fetch c into cr;
EXIT WHEN c%ROWCOUNT > return_rows OR c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cr.last_name);
end loop;
return_rows:=return_rows+30;
END PRINTEMPLOYEES;

END EMP;
/
----------------------------------------------------------------------------------------

Check Alert.log for ORA- errors using PLSQL

2011-11-11T11:14:00.001+02:00

create or replace procedure write_file

( DIR IN VARCHAR2,filename IN VARCHAR2,text IN varchar2)

file utl_file.file_type;

begin

file:=utl_file.fopen(DIR, filename,'a');

utl_file.put_line(file,text);

utl_file.new_line(file);

utl_file.fclose(file);

end;

create or replace

procedure read_file ( DIR IN VARCHAR2,filename IN VARCHAR2, report IN VARCHAR2)

file utl_file.file_type; --file handler

buffer char(1000);

--EOF boolean:=false;

begin

if not utl_file.is_open(file) then

file := utl_file.fopen(DIR,filename,'R');

end if;

loop

begin

utl_file.get_Line(file,buffer,32767);

--dbms_output.put_line(buffer);

if instr(buffer,'ORA-')=1 then

---dbms_output.put_line(buffer);

write_file(DIR, report,buffer);

end if;

exception

when no_data_found then

exit;

end;

end loop;

-- To run everynight at midnight starting tonight

exec dbms_job.submit

(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

How to call c shared library (.so) from PLSQL

2011-11-11T11:10:00.004+02:00

The example uses Oracle Linux 5 32bits , Oracle 11gR2 EE

Call C

csum

int csum(int a, int b)
{
return (a+b);
}

gcc -c -fPIC csum.c -o csum.o
gcc -shared -Wl,-soname,csum.so.1 -o csum.so.1.0.1 csum.o

mv csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so.1

create or replace library calc_sum as '/oracle/product/11.2.0/dbhome_1/lib/csum.so';
/

create or replace function getCSUM( a binary_integer,b binary_integer) return binary_integer
as language c
library calc_sum
name "csum";
/

select getCSUM(10,2) from dual;

How to Call C and Java from PLSQL

2011-11-11T11:10:00.001+02:00

Call C

csum

int csum(int a, int b)
{
return (a+b);
}

gcc -c -fPIC csum.c -o csum.o
gcc -shared -Wl,-soname,csum.so.1 -o csum.so.1.0.1 csum.o

create or replace library calc_sum as '/oracle/product/11.2.0/dbhome_1/lib/csum.so';
/

create or replace function getCSUM( a binary_integer,b binary_integer) return binary_integer
as language c
library calc_sum
name "csum";
/

select getCSUM(10,2) from dual;

Call Java

java

public class JSUM

{

public static int sum ( int a , int b){return a+b;}

}

[oracle@db11g myC]$ /oracle/product/11.2.0/dbhome_1/jdk/bin/javac JSUM.java

[oracle@db11g myC]$ loadjava -user hr/hr JSUM.class

create or replace function javaSUM( a number,b number) return number

language java

name 'JSUM.sum(int,int) return int';

select javaSUM(1,2) from dual;

PLSQL Large objects example

2011-11-11T11:09:00.001+02:00

Large objects exercise

As sys create the oracle directory EMP_DATA. Grant the appropriate privileges to user HR to read and write on this directory.

Modify your employees table (_emp) add columns emp_resume clob and emp_photo bfile. For each employee that exist in your table update the new columns as follows: (a) for emp_resume add the String 32000 bytes String emp_name ||' resume is: '||plus dots(....) till 32000 characters (b) add the anonymous photo for all employee.

Write a procedure to print your table's date. For the resume print its length,for photo just print the photo size.

Solution

As sys:

create or replace directory IMG as 'C:\Libraries\Pictures';

grant read,write on directory IMG to hr;

As hr:

drop table michael_emp purge;

create table michael_emp

(

emp_id number primary key,

emp_name varchar2(30) not null

);

insert into michael_emp values ( 1,'mmmmm');

insert into michael_emp values ( 2,'ggggg');

commit;

alter table michael_emp add emp_resume clob;

alter table michael_emp add emp_photo bfile;

create or replace procedure updateEMP

cursor c is select emp_id,emp_name from michael_emp FOR UPDATE;

cr c%ROWTYPE;

begin

for cr in c loop

update michael_emp set emp_resume=RPAD(cr.emp_name||' resume is: ',32000,'.') ,

emp_photo=bfilename('EMP_DATA','anonymous.jpg')

where current of c;

end loop;

commit;

end;

CREATE OR REPLACE PROCEDURE PRINTEMP AS

cursor c is select * from michael_emp ;

cr c%ROWTYPE;

BEGIN

for cr in c loop

dbms_lob.fileopen(cr.emp_photo);

dbms_output.put_line('Employee ID:'||cr.emp_id||' Name:'||cr.emp_name||' Resume size: '||dbms_lob.getlength(cr.emp_resume) ||' Photo size: '||dbms_lob.getlength(cr.emp_photo));

dbms_lob.fileclose(cr.emp_photo);

end loop;

END PRINTEMP;

Create a small plsql parser

2011-11-11T11:08:00.001+02:00

CREATE OR REPLACE FUNCTION pl_parser ( SQL_ IN VARCHAR2) RETURN varchar2
AS
cursor_name INTEGER;
ecode NUMBER;
emesg VARCHAR2(200);
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name,SQL_,DBMS_SQL.NATIVE);
return 0;
EXCEPTION WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
return SQLERRM;
END;
/

Using PLSQL Collections -- Simple Example with Nested Table

2011-11-11T11:06:00.001+02:00

Scenario:

============

Three intelligence sensors are used to keep the temperature and humidity of an Area XYZ .

Create a table that will be host sensors results.

Create a procedure to add the temperature (Celsius) and humidity per sensor.

Create a procedure to display temperature (Fahrenheit or Celsius) and humidity per sensor.

Use the TABLE expression to display the table's results

Note : You ust use a NESTED TABLE for your COLLECTION.

create or replace type sensor_measure is object

(

temperature number,

humidity number

);

create or replace type sensor_measure_tab as table of sensor_measure;

create table sensors

(

sensor_id number primary key,

sensor_name char(1) unique,

location char(3)

);

create table weather

(

sensor_id number,

m_date number(8) not null,

time number(4) not null,

s_measures sensor_measure_tab

)

NESTED TABLE s_measures STORE AS s_measures_storage;

alter table weather add constraint weather_fk foreign key(sensor_id) references sensors ( sensor_id);

insert into sensors values ( 1,'A','XYZ');

insert into sensors values ( 2,'B','XYZ');

insert into sensors values ( 3,'C','XYZ');

commit;

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

CREATE OR REPLACE PROCEDURE ADD_SENSOR_MEASURES

(

SENSOR_ID_ IN NUMBER

, TEMPERATURE_ IN NUMBER

, HUMIDITY_ IN NUMBER

)

sm sensor_measure;

s sensor_measure_tab;

i integer;

m_date_ number;

r_date_ number;

BEGIN

sm:=sensor_measure(TEMPERATURE_,HUMIDITY_);

s:=sensor_measure_tab(sm);

m_date_ := to_number(to_char(sysdate,'YYYYMMDD'));

r_date_ := to_number(to_char(sysdate,'HH24MI'));

insert into weather values (

SENSOR_ID_,

m_date_,

r_date_,

s);

commit;

END ADD_SENSOR_MEASURES;

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

CREATE OR REPLACE PROCEDURE PRINT_WEATHER_PER_SENSOR

(

SENSOR_ID_ IN NUMBER

)

cursor data_(sensorid number) is

select w.sensor_id, w.m_date,w.time,w.s_measures

from weather w

where w.sensor_id=SENSORID;

r data_%ROWTYPE;

s sensor_measure_tab;

sm sensor_measure;

BEGIN

dbms_output.put_line('Data for sensor:'||SENSOR_ID_);

For r_data IN data_(sensor_id_) loop

for i in r_data.s_measures.first..r_data.s_measures.last loop

sm:=r_data.s_measures(i);

dbms_output.put_line('Date:'||r_data.m_date||' '||'Time:'||r_data.time||' Temperature:'||sm.temperature||' Humidity:'||sm.humidity);

end loop;

END PRINT_WEATHER_PER_SENSOR;

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

select w.sensor_id, w.m_date,time,ws.temperature,ws.humidity from weather w,table(w.s_measures) ws

PLSQL Fine Grained Control Example

2011-11-11T11:03:00.000+02:00

-- sys --

CREATE OR REPLACE

PACKAGE PROTECT_HR_SALARY_PKG AS

procedure set_app_context(emp_id_value IN number);

function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2;

END PROTECT_HR_SALARY_PKG;

CREATE OR REPLACE

PACKAGE BODY PROTECT_HR_SALARY_PKG

procedure set_app_context(emp_id_value IN number) AS

BEGIN

dbms_session.set_context ('protect_hr_salaries','emp_id_value',''||emp_id_value);

NULL;

END set_app_context;

function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2

v_r varchar2(2000);

employee_id_ number;

BEGIN

employee_id_:=nvl(sys_context('protect_hr_salaries','emp_id_value'),0);

v_r := 'department_id=( select department_id from hr.departments where manager_id='||employee_id_||')';

return v_r;

END the_predicate;

END PROTECT_HR_SALARY_PKG;

create or replace context protect_hr_salaries using PROTECT_HR_SALARY_PKG;

-- Apply the policy function to the table.

BEGIN

DBMS_RLS.ADD_POLICY (object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'PROTECT_EMPLOYEES',

function_schema => 'sys',

policy_function => 'PROTECT_HR_SALARY_PKG.the_predicate',

statement_types => 'SELECT,INSERT,UPDATE,DELETE',

enable => TRUE);

END;

grant execute on PROTECT_HR_SALARY_PKG to hr

begin

sys.PROTECT_HR_SALARY_PKG. set_app_context(103);

end;

SQL> select first_name,salary from employees;

FIRST_NAME SALARY

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

Alexander 9000

Bruce 6000

David 4800

Valli 4800

Diana 4200

begin

sys.PROTECT_HR_SALARY_PKG. set_app_context(102);

end;

SQL> select first_name,salary from employees;

no rows selected

Using PLSQL Collections -- Simple Example with VARRAY

2011-11-11T11:02:00.000+02:00

Using Collections

===================

Simple Example with VARRAY

create or replace type colors as varray (10) of varchar2(10);

set serveroutput on size 1000000;

DECLARE

rainbow colors;

BEGIN

rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');

for i in rainbow.first..rainbow.last loop

DBMS_OUTPUT.put_line(rainbow(i));

end loop;

END;

create table my_rainbows_per_day

(

r_date number(8),

rainbow colors

);

---INSERT DATA---

insert into my_rainbows_per_day values ( '20111024',Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet'));

insert into my_rainbows_per_day values ( '20111025',Colors('Red','Orange','Yellow','Green'));

insert into my_rainbows_per_day values ( '20111026',Colors('Red','Orange','Yellow','Green'));

--- Read data using first and last methods

set serveroutput on size 1000000

DECLARE

cursor c is

select r_date ,rainbow

from MY_RAINBOWS_PER_DAY;

c_r c%rowtype;

BEGIN

FOR c_r IN c LOOP

DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);

for i in c_r.rainbow.first..c_r.rainbow.last loop

DBMS_OUTPUT.put_line(c_r.rainbow(i));

end loop;

END LOOP;

END;

--- Read data using first and next methods

set serveroutput on size 1000000

DECLARE

cursor c is

select r_date ,rainbow

from MY_RAINBOWS_PER_DAY;

c_r c%rowtype;

i integer;

BEGIN

FOR c_r IN c LOOP

DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);

i:=c_r.rainbow.first;

while i is not null loop

DBMS_OUTPUT.put_line(c_r.rainbow(i));

i:=c_r.rainbow.next(i);

end loop;

END LOOP;

END;

---- Read data using the TABLE expression

select * from TABLE( select r.rainbow from my_rainbows_per_day r where r.r_date=20111024);

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

Summarizing RAC's service control utility ( SRVCTL) 10gR2

2011-10-16T23:20:00.000+02:00

Summarizing RAC's service control utility ( SRVCTL) 10gR2

by Michael Georgiou

Srvctl is the main Oracle Real Application Cluster (RAC) utility which administer RAC objects. RAC objects are databases, instances, asm, nodeapps and services. The purposes of the srvctl is divided in three categories:

Oracle cluster database configuration tasks
General Oracle cluster database administration tasks
Node level tasks

Oracle cluster database configuration tasks

Add, remove modify databases, instances, services

Set and unset environment from databases, instances, services

General Oracle cluster database administration tasks

Start, stop, enable, disable and get statuses for databases, instances, services

Relocate a service back to the preferred instance.

Node level tasks

Add, remove modify node level applications

Set and unset environment from node level applications

Start , stop applications ( Applications can be Virtual IP Adress, Listeners, Oracle notification services and Oracle enterprise manager) .

Srvctl commands

add
config
enable
disable
start
stop
modify
relocate
status
getenv
setenv
unsetenv
remove

Commands description

add

database

instance

service

asm

nodeapps

examples:

srvctl add database -d ORCL -o $ORACLE_HOME

srvctl add instance -d ORCL -i ORCL1 -n racnode1

srvctl add service -d ORCL -s ORCLSRV -r racnode1 -a racnode2

srvctl add asm -n racnode1 -i asm1 -o $ORACLE_HOME

srvctl add nodeapps -n racnode1 -o $ORACLE_HOME \

-A 192.168.0.1/255.255.255.0

config

database

service

asm

nodeapps

listener

examples:

srvctl config database -ORCL

srvctl config -d ORCL -s ORCLSRV

srvctl config asm -n noderac1

srvctl config nodeapps -n noderac1

srvctl config listener -n noderac1

enable|disable

database

instance

service

asm

examples:

srvctl enable database -d ORCL

srvctl enable instance -d ORCL -i ORCL1,ORCL2

srvctl enable service -d ORCL -s ORCLSRV

srvctl enable asm -n noderac1 -i asm1

srvctl disable database -d ORCL

srvctl disable instance -d ORCL -i ORCL2,ORCL3

srvctl disable service -d ORCL -s ORCLSRV

srvctl disable asm -n noderac1 -i asm1

start|stop

database

service

asm

nodeapps

listener

examples:

srvctl start database -d ORCL -o open

srvctl start instance -d ORCL -i ORCL1,ORCL2

srvctl start service -d ORCL -s ORCLSRV

srvctl start asm -n noderac1 -i asm1

srvctl start nodeapps -n noderac1

srvcrl start listener -n noderac1

srvctl stop database -d ORCL -o immediate -c sys/****@ORCL1

srvctl stop instance -d ORCL -i ORCL1,ORCL2

srvctl stop service -d ORCL -s ORCLSRV

srvctl stop asm -n noderac1 -i asm1

srvctl stop nodeapps -n noderac1

srvcrl stop listener -n noderac1

modify

database

instance

service

nodeapps

Use modify database when:

-change ORACLE_HOME location

-change spfile location

-change the database role (primary,physical_standby,logical_standby)

-change startup option

-change management policy(automatic,manual

Use modify instance when:

-relocate a database instance

-establish a dependency between an asm instance and database instance

Use modify service when:

-Move service from one instance to another

-Changes which instances to be preferred and available

examples:

srvctl modify database -d ORCL -r logical_standby

srvctl modify instance -d ORCL -i ORCL1 -s ASM1

srvctl modify service -d ORCL -s ORCLSRV -r ORCL2 -a ORCL1

srvctl modify nodeapps -n noderac1 -A 192.168.100.2/255.255.255.0/etch0

More service examples:

-Move a service from instance ORCL1 to ORCL2

srvctl modify service -d ORCL -s ORCLSRV -i ORCL1 -t ORCL2

-Change an available instance to be a preferred instance

srvctl modify service -d ORCL -s ORCLSRV -i ORCL1 -r

relocate

service

example:

srvctl relocate service -d ORCL -s ORCLSRV -i ORCL1 -t ORCL3

status

database

instance

service

nodeapps

asm

examples:

srvctl status database -d ORCL -f

srvctl status instance -d ORCL -i ORCL1

srvctl status service -d ORCL -s ORCLSRV

srvctl status asm -n noderac1

srvctl status nodeapps -n noderac1

getenv

database

instance

service

nodeapps

examples:

srvctl getenv database -d ORCL

srvctl getenv instance -d ORCL -i ORCL1

srvctl getenv service -d ORCL -s ORCLSRV

srvctl getenv nodeapps -n noderac1

setenv

database

instance

service

nodeapps

examples:

srvctl setenv database -d ORCL -t LANG=EN

srvctl setenv instance -d ORCL -i ORCL1 -t LANG=EN

srvctl setenv service -d ORCL -s ORCLSRV -t CLASSPATH=/usr/local/jdk/jre/rt.jar

srvctl setenv nodeapps -n noderac1 -t CLASSPATH=/usr/local/jdk/jre/rt.jar

unsetenv

database

instance

service

nodeapps

examples:

srvctl unsetenv database -d ORCL -t LANG

srvctl unsetenv instance -d ORCL -i ORCL1 -t LANG=EN

srvctl unsetenv service -d ORCL -s ORCLSRV -t CLASSPATH

srvctl unsetenv nodeapps -n noderac1 -t CLASSPATH

remove

database

instance

service

nodeapps

asm

examples:

srvctl remove database -d ORCL

srvctl remove instance -d ORCL -i ORCL1

srvctl remove service -d ORCL -s ORCLSRV

srvctl remove service -d ORCL -s ORCLSRV -i ORCL3,ORCL4

srvctl remove nodeapps -n noderac1

srvctl remove asm -n noderac1

Note : use crs_stat -t or crs_stat -u for validating commands

Table: Available commands per RAC's object

Command	Database	Instance	Service	ASM	Nodeapps
add	√	√	√	√	√
config	√		√	√	√
enable\|disable	√	√	√	√
start\|stop	√	√	√	√	√
modify	√	√	√		√
relocate			√
Status	√	√	√	√	√
getenv	√	√	√		√
setenv	√	√	√		√
unsetenv	√	√	√		√
remove	√	√	√	√	√

For more details please read oracle's reference Oracle database clusterware and oracle real application clusters administration and deployment appendix E.

Oracle 10g RAC Utilities

2011-10-14T21:14:00.000+02:00

Oracle 10g RAC Utilities by michael A. Georgiou

This article provides a small tutorial for all the RAC utilities, that are available in oracle rac 10g r2. the srvctl utility is not covered in this document.

olsnodes

olsnodes : is used to list cluster's members

olsnodes -n --returns the cluster's nodes associated with each cluster number

olsnodes -i --returns cluster's nodes associated with each number and vip name

olsnodes -l --returns the local member name and cluster number

crsctl ( as root)

crsctl : The cluster control is responsible manage the cluster components , the main options are the following:

crsctl stop crs --stop the cluster the same as /etc/init.d/init.crs stop

crsctl start crs --start the cluster , the same as /etc/init.d/init.crs start

crsctl disable crs --disable cluster to start automatically

crsctl enable crs --enable cluster to start automatically

crsctl lsmodule [crs|evm|css] --list the available modules per cluster components, these modules can be used later for debugging purposes.

crsctl check crs --check all daemons(crsd,evmd,cssd) if are healthy

crsctl add css vodedisk -- add a new voding disk

crsctl delete css vodedisk --delete existing voding disk

crsctl debug log [crs|evm|css] "MODULE:DEGUG_LEVEl[,..]" --enable debugging

crsctl debug log res "RESOURCE:DEGUG_LEVEl[,..]" --enable debugging

All logs are available under

$CRS_HOME/log/hostname/crs

/cssd

/evmd

/crsd

$CRS_HOME/bin/diagcollection.pl -- this perl script collect information for diagnostic purposes.

racgons

racgons : It used to register a middle tier node[s] , such a ons server

racgons add_config node1:port,nodeN:port

racgons remove_config node1:port

ocrcheck

ocrcheck : Block by block check on ocr and ocr mirror files , it is returns

ocr file space details ( total space,available space,used space)
ocr id
ocr and ocr mirror location

ocrdump

ocrdump : consolitate ocr into a dump file

ocrdump --create an ocr dump , by default the OCRDUMPFILE is created

ocrdump mydump

ocrdump mydump -stdout -keyname system --create an ocr dump ,dumping only the system keyname

ocrdump mydump -stdout xml

evmwatch

evmwatch : waits for event to happen

evmwatch -A -t "@ timestamp @@"

evmpost

evmpost : port event to node[s]

evmpost -u "hello" node

ocrconfig

ocrconfig : ocr file administration

ocrconfig -backuploc ----change ocr backup location

ocrconfig -showbackup ---show the backups that are done

ocrconfig -overwrite ---overwrite the contents of the ocr loacted in local sga to disk

ocrconfig -repair --repair ocr file

ocrconfig -export -- export ocr file

ocrconfig -import --import and exported file

ocrconfig -replace ocr| ocr mirror --change ocr file

ocrconfig -restore --restore ocr backup as listed via showbackup option

ocrconfig -upgrade

ocrconfig -downgrade

oifcfg

oifcfg : Oracle's network interface configuration tools . This tools set which network interface will plays the role of public or private interconnect

oifcfg iflist ---> returns the if_name and the subnet ( if_name can be the eth0,eth1 etc)

oifcfg getif ---> return the if_name,subnet and if_type ( the if_type can be global public or private interconnect)

oifcfg setif -global eth3/192.168.10.0:private_interconnect ---> add a second private_interconnect network interface in cluster, the global option is applied in all nodes.

oifcfg delif -global eth3/192.168.10.0:private_interconnect

oifcfg delif -global --delete all global interfaces

clscfg

clscfg : Cluster configuration , with this tool you can register/add a new node in a cluster

clscfg -add -nn node3,3 -pn node3-priv,3 -hn node3,3

cluvfy

cluvfy: Cluster verification utility for following areas in the cluster

Installation verification ( inst)
Storage verification (ssa,space)
Cluster integrity (clu)
Cluster components (crs,ocr,cfs)
user and Permissions (admprv)
Connectivity (conn)
System requirements verification (sys)
Arguments and Options (peer)

cluvfy comp -list ---list of the valid components for verification

cluvfy stage -list ---list the valid stage of the verification ( pre stage and post stage e.g the system is able for oracle database installation)

clucfy comp -clu ----check the integrity of your entire cluster, which is mean to verify that all of the nodes in the cluster have the same view of the cluster configuration

cluvfy comp -sys -n all -p crs -verbose ---Verify the min system requirement on cluster nodes prior to installing oracle cluster ware.

cluvfy comp -ssa -n all -s /dev/sda ----Discover all of the shared storage system available on cluster on system /dev/sda

cluvfy comp -space -n all -l /oracle -z 2G ---Verify the availability of at least 2G of free space

cluvfy comp cfs -n all -F /ocfs-disk ---Verify the integrity of the cluster file system

cluvfy comp -admprv -n all

-o user_priv

-o crs_inst

-o db_inst

-o db_config -d $ORACLE_HOME

Verify user privileges , user privileges to install crs and install and configure database

cluvfy comp -conn -n all --- Discover all of the network interfaces that are available on the cluster nodes

cluvfy comp peer -n all -r 10gR2 -- compare all the node in the cluster

cluvfy comp peer -refnode rac1 -n rac2,rac3 -r 10gR2 -- compare all the node in the cluster base on rac1

cluvfy comp -stage -post crsinst -n all ---Verify the crs installation

cluvfy comp -stage -pre dbinst -n all -r 10gR2 --Verify that the system is configured properly in order to proceed with database installation

cluvfy stage -pre dbcfg -n all -d $ORACLE_HOME ---Verify whether your system meet all of the criteria for creating a database or for making a database configuration change

Insert into clob using plsql bind variable

2011-09-23T07:27:00.000+02:00

Insert into clob using plsql bind variable

create table tab1 ( id number primary key , big_text clob);

create or replace procedure insert_into_tab1(a number,b varchar2)
as
begin
insert into tab1 values(a,b);
commit;
end;
/

exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'))

SQL> exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'));

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1;

COUNT(*)
----------
1

SQL> select big_text from tab1;

BIG_TEXT
--------------------------------------------------------------------------------
Start string from here .........................................................

SQL> select dbms_lob.getlength(big_text) from tab1;

DBMS_LOB.GETLENGTH(BIG_TEXT)
----------------------------
32000

SQL>
PLSQL limits my bind variable to 32000.
SQL, the limit is 4,000 characters.
Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters.

refence : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:388196216305

How to Use Flash Recovery Area as your archivelog location

2011-09-18T06:54:00.001+02:00

First set :
db_recovery_file_dest='/oracle/flashRecoveryArea'
db_recovery_file_dset_size=<90% of the size '/oracle/flashRecoveryArea'>
Second :
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;

Adding instance on RAC database without dbca ( 10gR2)

2011-09-13T22:53:00.000+02:00

The suggested method to add an instance is via the dbca utility ( below is the silent version)

dbca -silent -addInstance -nodeList rac3 -gdbName ocfs -instanceName ocf3 -sysDBAUserName sysdba -sysDBAPassword xxxxx

If a dbca failed , try to add your new instance manually as follows

Add a new Listener using netca
Add a new Listener information in tnsnames.ora ( Remote Listener section)

Edit the tnsnames.ora for all instances

Prepare the database

Create a new thread
====================

ALTER DATABASE
ADD LOGFILE THREAD 3
GROUP 5 ('/ocfs-disk/ocfs/redo05.log') size 50M,
GROUP 6 ('/ocfs-disk/ocfs/redo06.log') size 50M;

alter database enable thread 3;

Create a new Undo tablespace
=============================

create undo tablespace UNDOTBS3 datafile '/ocfs-disk/ocfs/undotbs03.dbf' size 100M autoextend off;

Create init<>.ora and password file from the new node and assign the new thread and undo tablespace

add the instance using srvctl
==============================

srvctl add instance -d ocfs -i ocfs3 -n rac3
srvctl config database -d ocfs
srvctl start instance -d ocfs -i ocfs3

Ensure that the services_names is not set
ALTER SYSTEM SET service_names='' SCOPE=SPFILE SID='*';

NetManager does not display Advance Security Option Under Profile.

2011-09-02T11:36:00.000+02:00

Add

INSTALLEDCOMPONENTS=CLIENT,NET8,JAVAVM,SERVER,ANO

under

$ORACLE_HOME/network/tools/NetProperties

Restart netMgr

Flashback table is not working with Materialized views

2011-08-31T10:56:00.002+02:00

create table t ( id number);
alter table t enable row movement;

insert into t values (1);
insert into t values (1);
insert into t values (1);
commit;
select sys.dbms_flashback.get_system_change_number,min(id), max(id), count(*) from t;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
799014346 1 1 3
update t set id=2;
commit;
select sys.dbms_flashback.get_system_change_number,min(id), max(id), count(*) from t;
GET_SYSTEM_CHANGE_NUMBER MIN(ID) MAX(ID) COUNT(*)
------------------------ ---------- ---------- ----------
799014354 2 2 3

create materialized view t_mv
as
select id from t;

ORA-08194: Flashback Table operation is not allowed on materialized views
flashback table t to scn 799014346;

drop materialized view t_mv

flashback table t to scn 799014346;

How to flashback MVs and MV referred tables [ID 781112.1]

Bulk load insert - Example

2011-08-26T11:37:00.001+02:00

create or replace
PROCEDURE MIKE_BULK_EXAMPLE
AS
/* Fetch into user defined PL/SQL table */
TYPE CC IS TABLE OF TAB1%ROWTYPE INDEX BY BINARY_INTEGER;
plsql_tab_TAB1 CC;

BEGIN

DBMS_MVIEW.REFRESH('TAB1','C');

delete ccinfo;
commit;

SELECT
a,
b,
c,
d
BULK COLLECT INTO plsql_tab_TAB1
FROM TAB1;

FOR ALL i IN plsql_tab_tab1.FIRST .. plsql_tab_tab1.LAST
INSERT MAIN_TABLE VALUES plsql_tab_tab(i);
COMMIT;
commit;

END MIKE_BULK_EXAMPLE;

Database high availability best practices 10g-11g

2011-08-25T15:47:00.001+02:00

Database high availability best practices , for single-instance,RAC databases and data guard standby database.

Always use spfile
Multiplex control files
Set control_file_record_keep_time long enough
Multiplex production and standby redo log
Log checkpoint to the alert
Use auto tune checkpoints
Enable archive mode and use flash recovery area
Enable flashback database
Enable block checking (db_block_checksum=true)
Use automatic undo management
Use locally managed tablespace
Use segment space management auto
Use re-sumable space allocation
Use database resource manager
Register all instances with remote Listeners
Use Temporary tablespaces

Reference: Oracle database 10g : Real Application clusters (d17276gc10) p.11-16

Evaluating 1Z0-007: Introduction to Oracle9i SQL® Prepkits by ucertify

2011-08-22T11:22:00.000+02:00

1. Simple, intuitive, user-friendly interface

2. One click dashboard makes it easy to find what you need

3. Guided learning steps you through the process of learning and test preparation, including crucial information about the exam format and test preparation tips

4. Reference Notes and Study Guides organized according to the actual test objectives

5. Numerous study aids, including study notes, flash cards,pop quizzes and more

6. Useful Technical Articles section contains information

written by industry experts and How To’s that help for easy look up to specific questions

7. Collaboration

8. Exhaustive practice questions and tests, starting with Diagnostic tests to determine your initial level

9. Learning and test modes

10. Customize your tests – decide how many questions,

combine one or more topics of your choice, quiz yourself

on a study note, increase the level of difficulty based on

your performance at any point in time, even create a test

based on the amount of time you have to take a test!

11. Feedback and assessment when you need it, including Gap Analysis that clearly indicate your areas of strength and weakness

12. Full length Final Practice test that closely simulates those on the certification exam to gauge your preparation level for the actual exam

See more at http://www.ucertify.com

Managing OCR and Voting disk in Oracle 10gR2 RAC Database

2011-08-20T19:22:00.001+02:00

Both files are managed by the crsd daemon. Voting disk is used for monitoring information for the members of the cluster. OCR is the cluster configuration file and contains everything about the cluster such as list of nodes , mapping between nodes and instances, application profiles and etc.Both file must be located on share device with RAW,CFS,OCFS filesystems. In 10gR2 RAC Database these files can not be hosted in ASM.

Manage OCR files

First determine the ocr and ocrmirror location

Way 1:

[oracle@rac1 crs]$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 4644

Available space (kbytes) : 257476

ID : 1900957196

Device/File Name : /ocfs-disk/ocr0.ora

Device/File integrity check succeeded

Device/File Name : /ocfs-disk/ocr1.ora

Device/File integrity check succeeded

Cluster registry integrity check succeeded

Way 2:

[oracle@rac1 ocfs-disk]$ cat /etc/oracle/ocr.loc

ocrconfig_loc=/ocfs-disk/ocr0.ora

ocrmirrorconfig_loc=/ocfs-disk/ocr1.ora

local_only=FALSE

Ensure you have backup

[oracle@rac1 crs]$ ocrconfig -showbackup

rac1 2011/07/07 22:00:25 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2011/07/07 18:00:11 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2011/07/07 14:00:10 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2011/07/06 01:59:58 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2011/06/27 02:43:25 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

Move OCR and OCR mirror in a new Location

The cluster is UP

*** su - oracle ***

cp /dev/null /ocfs-disk/OCR-DISKS/ocr1.ora

cp /dev/null /ocfs-disk/OCR-DISKS/ocr2.ora

*** su root ( using oracle's profile) ****

ocrconfig -replace ocr /ocfs-disk/OCR-DISKS/ocr1.ora

ocrconfig -replace ocrmirror /ocfs-disk/OCR-DISKS/ocr2.ora

chown root /ocfs-disk/OCR-DISKS/ocr1.ora

chown root /ocfs-disk/OCR-DISKS/ocr2.ora

[oracle@rac2 ~]$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 4644

Available space (kbytes) : 257476

ID : 1900957196

Device/File Name : /ocfs-disk/OCR-DISKS/ocr1.ora

Device/File integrity check succeeded

Device/File Name : /ocfs-disk/OCR-DISKS/ocr2.ora

Device/File integrity check succeeded

Cluster registry integrity check succeeded

Post Actions after file Remove

Remove the old files manually

Export the new ocr

*** su root ( using oracle's profile) ****

ocrconfig -export /ocfs-disk/OCR-EXPORTS/ocrExp.dmp

Manage Voting files

First determine the current voting disks location

[root@rac2 OCR-EXPORTS]# crsctl query css votedisk

0. 0 /ocfs-disk/vot0.ora

1. 0 /ocfs-disk/vot1.ora

2. 0 /ocfs-disk/vot2.ora

located 3 votedisk(s).

Backup the voting disks

*** su - oracle ***

dd if=/ocfs-disk/vot0.ora of=/ocfs-disk/VOT-BACKUP/vot0.ora.dmp bs=4k

dd if=/ocfs-disk/vot1.ora of=/ocfs-disk/VOT-BACKUP/vot1.ora.dmp bs=4k

dd if=/ocfs-disk/vot2.ora of=/ocfs-disk/VOT-BACKUP/vot2.ora.dmp bs=4k

Stop your rac system (databases,services, listener etc) using srvctl

Stop your crs as root

crsctl stop crs

as oracle

touch /ocfs-disk/VOT-DISKS/vdisk1.ora

touch /ocfs-disk/VOT-DISKS/vdisk2.ora

Use the touch since the file system is OCFS2

crsctl add css votedisk /ocfs-disk/VOT-DISKS/vdisk1.ora -force

Now formatting voting disk: /ocfs-disk/VOT-DISKS/vdisk1.ora

successful addition of votedisk /ocfs-disk/VOT-DISKS/vdisk1.ora.

crsctl add css votedisk /ocfs-disk/VOT-DISKS/vdisk2.ora -force

Now formatting voting disk: /ocfs-disk/VOT-DISKS/vdisk2.ora

successful addition of votedisk /ocfs-disk/VOT-DISKS/vdisk2.ora.

crsctl delete css votedisk /ocfs-disk/vot0.ora

crsctl delete css votedisk /ocfs-disk/vot1.ora

crsctl delete css votedisk /ocfs-disk/vot2.ora

[oracle@rac2 ~]$ crsctl query css votedisk

0. 0 /ocfs-disk/VOT-DISKS/vdisk1.ora

1. 0 /ocfs-disk/VOT-DISKS/vdisk2.ora

crsctl start crs

Metalink References

OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) [ID 428681.1]

Failed to Start CRS stack After Adding a New Voting Disk [ID 460874.1]

Web Reference

http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_65.shtml#Relocate a Voting Disk

Remove or delete virtual disk from Virtual box

2011-08-04T17:14:00.000+02:00

Remove or delete virtual disk from Virtual box

C:\Program Files\Oracle\VirtualBox>VBoxManage closemedium disk F:\VBOX-DISKS\LINUX5.vdi

PL/SQL examples

2011-08-01T15:28:00.001+02:00

create or replace package name is

f_name varchar2(30):='Anonymous '; --global varibles

l_name varchar2(30):='Anonymous '; --global varibles

initial char := '_'; --global varibles

procedure name1;

procedure name1(f_name varchar2,last_name varchar2 default 'Amonymous',initial_v char:=initial);

procedure name1(f_name varchar2,l_name varchar2,initial char);

end;

create or replace package body name is

procedure name1 is

begin

dbms_output.put_line('1-Name:'||f_name||' '||initial||' '||l_name);

end name1;

procedure name1 (f_name varchar2,last_name varchar2 default 'Amonymous',initial_v char:=initial)is

begin

dbms_output.put_line('2-Name:'||f_name||' '||initial_v||' '||l_name);

end name1;

procedure name1(f_name varchar2,l_name varchar2,initial char)is

begin

dbms_output.put_line('3-Name:'||f_name||' '||initial||' '||l_name);

end name1;

end;

EKTORAS> exec name.name1;

1-Name:Anonymous _ Anonymous

PL/SQL procedure successfully completed.

EKTORAS> exec name.name1(f_name=>'MIKE',last_name=>'Georgiou',initial_v=>'A');

2-Name:MIKE A Anonymous

PL/SQL procedure successfully completed.

EKTORAS> exec name.name1(f_name=>'MIKE',initial_v=>'A');

2-Name:MIKE A Anonymous

PL/SQL procedure successfully completed.

EKTORAS> exec name.name1('MIKE',initial_v=>'A');

2-Name:MIKE A Anonymous

PL/SQL procedure successfully completed.

EKTORAS> exec name.name1('MIKE',initial=>'A',f_name=>'GEORGIOU);

ERROR:

ORA-01756: quoted string not properly terminated

EKTORAS> exec name.name1('MICHAEL');

2-Name:MICHAEL _ Anonymous

PL/SQL procedure successfully completed.

EKTORAS> exec name.name1('MICHAEL','GEORGIOU','A');

BEGIN name.name1('MICHAEL','GEORGIOU','A'); END;

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00307: too many declarations of 'NAME1' match this call

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

EKTORAS> exec name.name1(f_name=>'MICHAEL',l_name=>'GEORGIOU',initial=>'A');

3-Name:MICHAEL A GEORGIOU

PL/SQL procedure successfully completed.

EKTORAS>

create or replace package forwDecl is

procedure t2( id number );

end;

create or replace package body forwDecl is

procedure t1( id number ); --forward declaration below

procedure t2( id number ) is

begin

t1(10);

end;

procedure t1( id number ) is

begin

dbms_output.put_line(id);

end;

create or replace package hr_standard is

function to_char(id number) return number;

end;

create or replace package body hr_standard is

function to_char(id number) return number is

begin

return null;

end;

create or replace package vat

t1 number :=1;

t2 number :=1;

end;

create or replace package body vat

t3 number ;

begin

t1 :=10;

t2 :=20;

t3 :=30;

end;

create or replace package funct_test is

function em_max_id return number;

end;

create or replace package body funct_test is

function em_max_id return number IS

id number;

begin

select max(employee_id) into id from employees;

return id+1;

end;

insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)

values

(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');

select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID

from employees;

create or replace package funct_test is

function em_max_id return number;

end;

create or replace package body funct_test is

function em_max_id return number IS

id number;

begin

select max(employee_id) into id from employees;

commit;

return id+1;

end;

insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)

values

(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');

select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID

from employees;

EKTORAS> insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)

2 values

3 (funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');

(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR')

ERROR at line 3:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

ORA-06512: at "HR.FUNCT_TEST", line 6

create or replace package funct_test is

function em_max_id return number;

end;

create or replace package body funct_test is

function em_max_id return number IS

id number;

begin

select max(employee_id) into id from employees;

return id+1;

end;

update employees

set employee_id=funct_test.em_max_id

where employee_id=207;

commit;

EKTORAS> update employees

2 set employee_id=funct_test.em_max_id

3 where employee_id=207;

set employee_id=funct_test.em_max_id

ERROR at line 2:

ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it

ORA-06512: at "HR.FUNCT_TEST", line 5

update employees

set employee_id=funct_test.em_max_id

where employee_id=207;

create or replace package pkg_state

PRAGMA SERIALLY_REUSABLE;--must be declared first

--tha dosei tin time sta variables tha ginei to job

-- kai amesos meta ta variable tha ginoun set opos itan

a number;

b number;

procedure print;

procedure setA(a1 number);

procedure setB(b1 number);

end;

create or replace package body pkg_state

PRAGMA SERIALLY_REUSABLE;--must be declared first

procedure print is

begin

dbms_output.put_line('a:'||a);

dbms_output.put_line('b:'||b);

end;

procedure setA(a1 number) is

begin

a :=a1;

print;

end;

procedure setB(b1 number) is

begin

b :=b1;

print;

end;

EKTORAS> exec pkg_state.setA(10); --First call

a:10

PL/SQL procedure successfully completed.

EKTORAS> exec pkg_state.print; --first call

PL/SQL procedure successfully completed.

create or replace package pkg_state

a number;

b number;

procedure print;

procedure setA(a1 number);

procedure setB(b1 number);

end;

create or replace package body pkg_state

procedure print is

begin

dbms_output.put_line('a:'||a);

dbms_output.put_line('b:'||b);

end;

procedure setA(a1 number) is

begin

a :=a1;

print;

end;

procedure setB(b1 number) is

begin

b :=b1;

print;

end;

begin

a:=0;

b:=0;

end;

create table mike_sequences ( sequence_name varchar2(30)unique,increment_by number default 1,currval number);

create or replace package seq_pkg

a number:=0;

session_id :=0;

function nextval(name mike_sequences.sequence_name%type)return number;

function currval(name mike_sequences.sequence_name%type)return number;

procedure setSequence(sequence_name varchar2,increment_by number default 1);

end;

create or replace package body seq_pkg

function currval(name mike_sequences.sequence_name%type);

function nextval(name mike_sequences.sequence_name%type);

function nextval(name mike_sequences.sequence_name%type)return number is

begin

if session_id = 0 then

a:=currval(name)+1;

return a

else

return a := a +1 ;

end if;

end;

function currval(name mike_sequences.sequence_name%type)return number is

current_value number ;

begin

if session_id = 0 then

select currval into current_value from mike_sequences where sequence_name=Upper(name);

session_id := SYS_CONTEXT('USERENV','SESSIONID');

return current_value;

else

a := a +1 ;

e mike_sequences set currval=a where sequence_name=Upper(name);

commit;

return a;

end if;

end;

procedure setSequence(sequence_name varchar2,increment_by number default 1)is

begin

null;

end;

CONNECT Scott/Tiger

DROP PACKAGE Sr_pkg;

CREATE OR REPLACE PACKAGE Sr_pkg IS

PRAGMA SERIALLY_REUSABLE;

TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

Num NUMBER := 10;

Str VARCHAR2(200) := 'default-init-str';

Str_tab STR_TABLE_TYPE;

PROCEDURE Print_pkg;

PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);

END Sr_pkg;

CREATE OR REPLACE PACKAGE BODY Sr_pkg IS

-- the body is required to have the pragma because the

-- specification of this package has the pragma

PRAGMA SERIALLY_REUSABLE;

PROCEDURE Print_pkg IS

BEGIN

DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);

DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);

DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);

FOR i IN 1..Sr_pkg.Str_tab.Count LOOP

DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i));

END LOOP;

END;

PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS

BEGIN

-- init the package globals

Sr_pkg.Num := N;

Sr_pkg.Str := V;

FOR i IN 1..n LOOP

Sr_pkg.Str_tab(i) := V || ' ' || i;

END LOOP;

-- print the package

Print_pkg;

END;

END Sr_pkg;

SET SERVEROUTPUT ON;

Rem SR package access in a CALL:

BEGIN

-- initialize and print the package

DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');

Sr_pkg.Init_and_print_pkg(4, 'abracadabra');

-- print it in the same call to the server.

-- we should see the initialized values.

DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');

Sr_pkg.Print_pkg;

END;

Initing and printing pkg state..

num: 4

str: abracadabra

number of table elems: 4

abracadabra 1

abracadabra 2

abracadabra 3

abracadabra 4

Printing package state in the same CALL...

num: 4

str: abracadabra

number of table elems: 4

abracadabra 1

abracadabra 2

abracadabra 3

abracadabra 4

REM SR package access in subsequent CALL:

BEGIN

-- print the package in the next call to the server.

-- We should that the package state is reset to the initial (default) values.

DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');

Sr_pkg.Print_pkg;

END;

Statement processed.

Printing package state in the next CALL...

num: 10

str: default-init-str

number of table elems: 0

drop table customer purge;

create table customer

(

customerid number,

email varchar2(100)

)

tablespace users;

create unique index customer_customerid_pk on customer(customerid) tablespace tools;

alter table customer

add constraint customer_customerid_pk primary key(customerid);

alter table customer

add constraint customer_email_nn check (email <> null);

comment on table customer is 'The customer table contains information for demo customers';

create sequence key_customerid;

insert into customer values(key_customerid.nextval,'michaelg@netu.com.cy');

insert into customer values(key_customerid.nextval,'andreas@netu.com.cy');

insert into customer values(key_customerid.nextval,'katerina@netu.com.cy');

set serveroutput on size 1000000

create or replace procedure selectCustomer

cust_data_rec customer%ROWTYPE;

customerid customer.customerid%type;

email customer.email%type;

n number:=0;

cursor cust_data is

select customerid,email from customer;

begin

if not cust_data%isopen then

open cust_data;

end if;

Loop

n:=n+1;

fetch cust_data into customerid,email;

exit when cust_data%notfound;

dbms_output.put_line('['||n||'] '||customerid||' '||email);

end loop;

if cust_data%isopen then

close cust_data;

end if;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('error:');

end;

create or replace package customer_pkg

type all_customer is ref cursor;

procedure findAllCustomers(customers OUT all_customer);

procedure printCustomers;

--procedure findByPrimaryKey(customerid IN number ,customers OUT all_customer);

end;

create or replace package body customer_pkg

procedure findAllCustomers(customers OUT all_customer)

begin

open customers for select * from customer;

end;

procedure printCustomers

tmp_customer all_customer;

all_customers customer%rowtype;

n number;

begin

findAllCustomers(tmp_customer);

dbms_output.put_line('Printing customers '||tmp_customer%rowcount);

loop

fetch tmp_customer into all_customers;

exit when tmp_customer%notfound;

dbms_output.put_line(all_customers.customerid||':'||all_customers.email);

end loop;

end;

declare

type a is table of char(3200) index by binary_integer;

data a;

begin

for i IN 1..10 loop

data(i):= 'abcd';

end loop;

for i IN 1..10 loop

dbms_output.put_line(data(i));

end loop;

end;

begin

FOR i IN (SElect * from customer) LOOP

dbms_output.put_line(i.email);

END LOOP;

end;

--wrap pl/sql

create or replace package customer_pkg2

type all_customer is ref cursor;

procedure findAllCustomers(customers OUT all_customer);

procedure printCustomers;

--procedure findByPrimaryKey(customerid IN number ,customers OUT all_customer);

end;

create or replace package body customer_pkg2

procedure findAllCustomers(customers OUT all_customer)

begin

open customers for select * from customer;

end;

procedure printCustomers

tmp_customer all_customer;

all_customers customer%rowtype;

n number;

begin

findAllCustomers(tmp_customer);

dbms_output.put_line('Printing customers '||tmp_customer%rowcount);

loop

fetch tmp_customer into all_customers;

exit when tmp_customer%notfound;

dbms_output.put_line(all_customers.customerid||':'||all_customers.email);

end loop;

end;

--dbms_output

set serveroutput on size 1000000

declare

buffer varchar(100);

status number;

begin

dbms_output.put_line('Write a line');

dbms_output.get_line(buffer,status);

dbms_output.put_line('Your line is :'||buffer);

end;

undefine username

set verify off

define username

ACCEPT username PROMPT "Username:"

begin

dbms_output.put_line('Username from check is:'||'&username');

end;

define username

--------

UTL_FILE.GET_LINE (

file IN FILE_TYPE,

buffer OUT VARCHAR2,

linesize IN NUMBER,

len IN PLS_INTEGER DEFAULT NULL);

--UTL_FILE

create or replace procedure write_file

file utl_file.file_type;

begin

file:=utl_file.fopen('ORA_DIR2','test.txt','w');

utl_file.put_line(file,'first line');

utl_file.new_line(file);

utl_file.put_line(file,'Second line');

utl_file.new_line(file);

utl_file.fclose(file);

file:=utl_file.fopen('ORA_DIR2','test.txt','a');

utl_file.put_line(file,'Thrird line');

utl_file.new_line(file);

utl_file.fclose(file);

end;

create or replace procedure read_file

file utl_file.file_type; --file handler

buffer char(1000);

--EOF boolean:=false;

begin

if not utl_file.is_open(file) then

file := utl_file.fopen('ORA_DIR2','test.txt','R',32767);

end if;

loop

begin

utl_file.get_Line(file,buffer,32767);

dbms_output.put_line(buffer);

--exit when EOF=true;

exception

when no_data_found then

--EOF:=true;

exit;

end;

end loop;

if utl_file.is_open(file) then

utl_file.fclose(file);

end if;

exception

when utl_file.INVALID_FILEHANDLE then

dbms_output.put_line('INVALID_FILEHANDLE');

when utl_file.INVALID_OPERATION then

dbms_output.put_line('INVALID_OPERATION');

when utl_file.READ_ERROR then

dbms_output.put_line('READ_ERROR');

when others then

dbms_output.put_line('Unknow error');

end;

SET SERVEROUTPUT ON SIZE 100000

BEGIN -- Generates:

htp.htmlOpen; --------->

htp.headOpen; --------->

htp.title('Welcome'); -->

htp.headClose; --------->

htp.bodyOpen; --------->

htp.print('My home page');

htp.bodyClose; --------->

htp.htmlClose; --------->

END;

EXECUTE OWA_UTIL.SHOWPAGE

BEGIN

UTL_MAIL.SEND('oracle@tom.domain.local','oracle@tom.domain.local',

message => 'For latest downloads visit OTN',

subject => 'OTN Newsletter');

END;

DECLARE

file utl_file.file_type;

CURSOR RES IS SELECT ID,ID2 FROM A;

BEGIN

file:=utl_file.fopen('ORA_DIR','test.txt','w');

FOR I IN RES LOOP

--utl_file.put_line(file,I.ID);

NULL;

END LOOP;

--utl_file.fclose(file);

END;

Transportable tablespaces in Oracle 10g

2011-08-01T15:25:00.001+02:00

+-------------------------------------------------------------------------------------------+

| |

| Transportable tablespaces in Oracle 10g |

| |

+-------------------------------------------------------------------------------------------+

| Techonology : Oracle database |

+-------------------------------------------------------------------------------------------+

| Created : 11/7/2007 |

+-------------------------------------------------------------------------------------------+

SYS@ORCL > exec dbms_tts.transport_set_check('TBS1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SYS@ORCL > select * from transport_set_violations;

VIOLATIONS

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

Sys owned object SYS_C007034 in tablespace TBS1 not allowed in pluggable set

Sys owned object DATAE in tablespace TBS1 not allowed in pluggable set

Sys owned object SYS_IL0000021148C00002$$ in tablespace TBS1 not allowed in plu

ggable set

Sys owned object SYS_LOB0000021148C00002$$ in tablespace TBS1 not allowed in pl

uggable set

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

[oracle@tom datafiles]$ expdp "'sys/oracle as sysdba'" transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:08:54

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29341: The transportable set is not self-contained

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

[oracle@tom datafiles]$ expdp system/oracle transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:16:42

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/home/oracle/tbs.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:18:21

Name (192.168.0.2:oracle): ftp

331 Password required for ftp.

Password:

230 User ftp logged in.

Remote system type is UNIX.

Using binary mode to transfer files.

ftp> bin

200 Type set to I.

ftp> put tbs1_01.dbf

local: tbs1_01.dbf remote: tbs1_01.dbf

227 Entering Passive Mode (192,168,0,2,9,101).

150 Opening data connection for tbs1_01.dbf.

226 File received ok

10493952 bytes sent in 9.2 seconds (1.1e+03 Kbytes/s)

RMAN> convert datafile 'tbs1_01.dbf'

2> from platform='Linux IA (32-bit)'

3> db_file_name_convert="ts","win";

Starting backup at 12-JUL-07

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TBS1_01.DBF

converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCL_I-1121005791_TS-TBS1_FNO-7_

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02

Finished backup at 12-JUL-07

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:49:14

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d

atafiles=tbs_win01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29342: user MICHAEL does not exist in the database

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:49:20

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

SQL> create user michael identified by michael;

User created.

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

C:\>create user michael identified by michael;

'create' is not recognized as an internal or external command,

operable program or batch file.

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:50:07

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d

atafiles=tbs_win01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:50:12

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

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

TBS1

SQL> alter tablespace tbs1 read write;

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

Partitioning in 10g

2011-08-01T15:24:00.001+02:00

+---------------------------------- |

| Partitioning in 10g |

| |

+-------------------------------------------------------------------------------------------+

| Techonology : Oracle database 10.2.0 |

+-------------------------------------------------------------------------------------------+

| Created : 28/7/2007 |

+-------------------------------------------------------------------------------------------+

Types of Partitioning

Range

Hash

Composite

List

Range

=====

Based on range of the partition key values.

It is used most commonly for dates.

Range partitioning is defined by the PARTITION BY RANGE(partition_key_column), and for

each sub partition in VALUES LESS THAN(value_list).The literal MAXVALUE represent a

virtual infinite value that sorts higher that any other value for the data type

including the null value.

Range

=====

( Partitioning historical data )

The example below shows how to create a table with range partitioning

CREATE TABLE PROFILE_HISTORY

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CONSTRAINT PROFILE_HISTORY_PK PRIMARY KEY (PROFILE_ID,ACCESS_DATE)

)

PARTITION BY RANGE(ACCESS_DATE)

(

PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),

PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),

PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),

PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))

);

The example below shows how to create a INDEX with range partitioning on a normal

table

CREATE TABLE PROFILE_HISTORY2

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CONSTRAINT PROFILE_HISTORY_PK2 PRIMARY KEY (PROFILE_ID)

);

CREATE INDEX PROFILE_HISTORY2_INDX

ON PROFILE_HISTORY2(ACCESS_DATE)

GLOBAL

PARTITION BY RANGE(ACCESS_DATE)

(

PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),

PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),

PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),

PARTITION P5 VALUES LESS THAN (MAXVALUE)

);

The example below shows how to create a INDEX with range partitioning on a partition

table

CREATE INDEX PROFILE_HISTORY_INDX

ON PROFILE_HISTORY(ACCESS_DATE)

GLOBAL

PARTITION BY RANGE(ACCESS_DATE)

(

PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),

PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),

PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),

PARTITION P5 VALUES LESS THAN (MAXVALUE)

);

The Local partition index

=========================

The Local partitiom index is created by specified the literal LOCAL.

The local index is created based on partition table.

CREATE TABLE PROFILE_HISTORY3

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CONSTRAINT PROFILE_HISTORY_PK3 PRIMARY KEY (PROFILE_ID)

)

PARTITION BY RANGE(ACCESS_DATE)

(

PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),

PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),

PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),

PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))

);

create index PROFILE_HISTORY3_indx on PROFILE_HISTORY3(PROFILE_ID,ACCESS_DATE)

LOCAL;

SQL> select partition_name

from dba_ind_partitions

where index_name='PROFILE_HISTORY3_INDX';

PARTITION_NAME

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

CREATE TABLE PROFILE_HISTORY4

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CUSTOMER_ID NUMBER,

CONSTRAINT PROFILE_HISTORY_PK4 PRIMARY KEY (PROFILE_ID)

)

PARTITION BY RANGE(ACCESS_DATE,CUSTOMER_ID)

(

PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')AND CUSTOMER_ID 10),

PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),

PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),

PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))

);

CREATE TABLE PROFILE_HISTORY6

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CUSTOMER_ID NUMBER,

CONSTRAINT PROFILE_HISTORY_PK6 PRIMARY KEY (PROFILE_ID)

)

PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID);

CREATE TABLE PROFILE_HISTORY7

(

PROFILE_ID NUMBER,

ACCESS_DATE DATE,

CUSTOMER_ID NUMBER,

CONSTRAINT PROFILE_HISTORY_PK7 PRIMARY KEY (PROFILE_ID)

)

PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID) PARTITION 4;

Explain Data Block Addres

2011-08-01T15:21:00.000+02:00

+-------------------------------------------------------------------------------------------+
| |
| Explain Data Block Address |
| |
+-------------------------------------------------------------------------------------------+
| Techonology : Oracle database 10.2.0 |
+-------------------------------------------------------------------------------------------+
| Created : 29/7/2007 |
+-------------------------------------------------------------------------------------------+
| Document Scope: |
The scope of the document is to understand and use Data Block Address .
The Second argument of ORA-600 [3374] corresponds to the expected Data Block Address.
They are 11206682 and 2617431000. The blocks in these DBA are corrupted.
Let's convert DBA to corresponding (file#,block#)

Example1 :ORA-00600: internal error code, arguments: [3374], [16777298], [825371952],[875376697], [11824], [], [], []

SQL> SELECT dbms_utility.data_block_address_block(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777298)
-----------------------------------------------
82

SQL>
SQL> SELECT dbms_utility.data_block_address_file(16777298)
2 FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777298)
----------------------------------------------
4

The corruption block is the block_id 82 in file_id 4. To find which database objects
belong the corrupted block,use the following query.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 4
and 82 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
USERS TABLE SYS
T3

The example below , shows how to find the data block addreess base on file_id and some block_id.

SQL> select block_id,block_id + blocks - 1,relative_fno,file_id from dba_extents where segment_name='T3';

BLOCK_ID BLOCK_ID+BLOCKS-1 FILE_ID
---------- ----------------- ----------
81 88 4

SQL> SELECT dbms_utility.make_data_block_address(4,82)
2 FROM dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,82)
------------------------------------------
16777298

Relate Note :
Ref : http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

List a complete directory tree

2011-07-14T11:57:00.001+02:00

List a complete directory tree

You can use the tree command. If tree command does not exist in your system use the following

find . -type d -print | sed -e 's;[^/]*/;|____;g;s;____|; |;g'

Reference: http://www.unix.com/shell-programming-scripting/20702-tree-unix.html

Enable client tracing in SQLNET

2011-07-12T09:18:00.002+02:00

add in the %ORACLE_HOME%\network\admin\sqlnet.ora

TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = SQLNetTrace
TRACE_TIMESTAMP_CLIENT = ON

Configure RMAN to use all RAC nodes (Parallel backups)

2011-07-04T21:28:00.002+02:00

Consider a two-node rac with 1 cpu per node.

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/oracle@rt1';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/oracle@rt2';

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/10.2.0/db_1/dbs/snapcf_RT2.f'; # default

RMAN> backup database plus archivelog;

Starting backup at 04-JUL-11

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 instance=RT1 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=132 instance=RT2 devtype=DISK

channel ORA_DISK_1: starting compressed archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=10 recid=6 stamp=755647649

input archive log thread=1 sequence=11 recid=10 stamp=755648045

input archive log thread=1 sequence=12 recid=14 stamp=755648207

input archive log thread=1 sequence=13 recid=16 stamp=755648344

input archive log thread=1 sequence=14 recid=22 stamp=755648490

input archive log thread=1 sequence=15 recid=26 stamp=755648538

input archive log thread=1 sequence=16 recid=30 stamp=755648596

input archive log thread=2 sequence=12 recid=2 stamp=755647601

channel ORA_DISK_1: starting piece 1 at 04-JUL-11

channel ORA_DISK_2: starting compressed archive log backupset

channel ORA_DISK_2: specifying archive log(s) in backup set

input archive log thread=2 sequence=13 recid=4 stamp=755647644

input archive log thread=2 sequence=14 recid=8 stamp=755648043

input archive log thread=2 sequence=15 recid=12 stamp=755648206

input archive log thread=2 sequence=16 recid=18 stamp=755648344

input archive log thread=2 sequence=17 recid=20 stamp=755648488

input archive log thread=2 sequence=18 recid=24 stamp=755648536

input archive log thread=2 sequence=19 recid=28 stamp=755648594

channel ORA_DISK_2: starting piece 1 at 04-JUL-11

channel ORA_DISK_1: finished piece 1 at 04-JUL-11

piece handle=+DISKGROUP01/rt/backupset/2011_07_04/annnf0_tag20110704t222317_0.314.755648601 tag=TAG20110704T222317 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_2: finished piece 1 at 04-JUL-11

piece handle=+DISKGROUP01/rt/backupset/2011_07_04/annnf0_tag20110704t222317_0.315.755648603 tag=TAG20110704T222317 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07

Finished backup at 04-JUL-11

Starting backup at 04-JUL-11

using channel ORA_DISK_2

using channel ORA_DISK_1

....

Reference : http://oraclehandson.wordpress.com/2008/01/10/parallel-backup-using-rman-in-rac-environment/

....

Migrating Existing Backups to ASM Flash Recovery Area

2011-07-02T21:40:00.000+02:00

Migrating Existing Backups to ASM Flash Recovery Area

This article coming from http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmasm.htm

In this example, it is assumed that you have already set the initialization parameters for a flash recovery area in ASM storage, using the process in "Setting Initialization Parameters for Flash Recovery Area in ASM".

Note:

After you configure the database to change the location of the flash recovery area, backups created in the old flash recovery area location remain in their old location, still count against the total disk quota of the flash recovery area, are deleted from the old flash recovery area as space is required for other files, and can still be managed by RMAN and used in RMAN recovery operations. There is no need to move existing backups to the new ASM flash recovery area, unless you need the disk space used by those files for other purposes.

If you do need to free the space taken up by leftover non-ASM flash recovery area files, your options include backing them up to tape (for example, by using BACKUP RECOVERY AREA DELETE INPUT) or moving the backups from the old flash recovery area location to the new one, as described in this section.

To back up the existing archived redo log files to the new flash recovery area, use this command:

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

To move backup sets to the new flash recovery area, use this command:

RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

To move all datafile copies to the new flash recovery area location, use this command:

RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;

Start and Stop RAC Listeners

2011-06-27T22:49:00.002+02:00

lsnrctl stop listener affect only the connected node

On two nodes RAC cluster use the following commands to stop and start RAC's listeners

[oracle@rac2 ~]$ srvctl start listener -n rac1

[oracle@rac2 ~]$ srvctl start listener -n rac2

Restore Oracle RAC OCR file( Oracle Cluster Registry)

2011-06-26T21:07:00.001+02:00

oracle@rac2 dbs]$ ocrconfig -showbackup

rac2 2011/01/01 22:42:51 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac2 2011/01/01 18:42:50 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac2 2011/01/01 14:42:50 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2010/12/31 20:40:10 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

rac1 2010/12/23 16:52:33 /oracle/crs/oracle/product/10.2.0/crs/cdata/crs

/etc/init.d/init.crs stop ## Do in all nodes

ocrconfig -restore /oracle/crs/oracle/product/10.2.0/crs/cdata/crs/backup01.ocr

/etc/init.d/init.crs start ## Do in all nodes

How to calculate Undo Space (Undo tablespace) for a specific UNDO_RETENTION

2011-06-14T11:22:00.000+02:00

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
TO_NUMBER(&&Undo_retention) "UNDO RETENTION [Sec]",
(TO_NUMBER(&Undo_retention) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

Create a version query

2011-06-10T12:23:00.001+02:00

According the init. parameter UNDO_RETENTION = 900 (15 min) you can see the tuples versions as below:

select * from

VERSIONS BETWEEN TIMESTAMP
to_timestamp('2011/06/07 9:30:00', 'YYYY/MM/DD HH24:MI:SS')
AND
to_timestamp('2011/06/07 9:35:00', 'YYYY/MM/DD HH24:MI:SS')

Clean Oracle's Report Server Jobs Queue

2011-06-02T11:06:00.000+02:00

Valid for Oracle Application server 10.2

Stop Report Server
Rename the dat file present in $ORACLE_HOME/reports/server directory and restart the server again

Five distinct types of database uncertainty

2011-05-31T11:33:00.003+02:00

Five distinct types of database uncertainty :

Uncertainty -- There might be uncertainty about the database fact " the age of john is 38"
Imprecision -- The information in the model is not as specific as it should be. For example, when distinct value is required, the information available might be:
range : " The age of john is between 37 and 43"
disjunctive: " The age of john is either 37 or 43"
negative : " The age of john is not 37"
unknown : "unknown" i।e। null
Vagueness -- The model includes elements (e.g predicates or quantifiers) that are inherently vague i.e. " John is in early middle age".
Inconsistency-- The model contains two or more alternatives that cannot be true at the same time e.g. " the age of john is between 37 and 43" and the "age of john is 35".
Ambiguity -- Some elements of the model lack complete semantics leading to several possible interpretations e.g. " employees' commission are given per month or per year" .

Ref: Amihai Motro, Management of Uncertainty in database Systems.

Tablespace Encryption

2011-05-12T10:11:00.000+02:00

Transparent Data Encryption (TDE) and the use of a component known as the Oracle Wallet. Oracle 10.2.4 +

CREATE TABLESPACE encrypted_ts
DATAFILE 'C:\ORACLE\ORADATA\SECURE\encrypted_ts01.dbf' SIZE 128K
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING 'AES192'
DEFAULT STORAGE(ENCRYPT);

The link shows you how you are able to create a wallet.
http://mikegeorgiou.blogspot.com/2011/05/column-ecryption-in-oracle-10g.html

Disabling and Enabling Oracle Database Vault

2011-05-12T09:10:00.000+02:00

http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/server.102/b25166/dvdisabl.htm

Column ecryption in Oracle 10g

2011-05-12T09:00:00.000+02:00

Column ecryption in Oracle 10g

Step 1
=======
Add the following lines in $ORACLE_HOME/network/admin/sqlnet.ora

# Create a wallet for column ecryption
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=C:\oracle\oradata\SECURE)))

Step 2
========
create the wallet

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "password";

Step 3
=========
Login as test1/test1

create table test1
(
id number,
password varchar2(10) encrypt
);

insert into test1 values (1,'pass1234');

Any schema (having the appropriate privileges )is able to select the data decrypted.

SQL> select * from test1;
select * from test1
*
ERROR at line 1:
ORA-28365: wallet is not open

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted columns.

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "password";

ALTER SYSTEM SET WALLET CLOSE;

References : http://www.oraclebase.com/articles/10g/TransparentDataEncryption_10gR2.php

Compile Oracle forms,reports etc. on Unix

2011-04-28T13:39:00.002+02:00

#!/bin/ksh
#
EXE=/home/oraclefrm/OraHome_1/forms/bin
SRC=/home/oraclefrm/OraHome_1/forms/src
print "######################################################################################"
print "Compile individual components for forms 10.1.2.0.2 on Linux and Solaris"
print "Before start ensure you have database schema name, password and the connection string"
print "######################################################################################"
print "Compile Form press [1]"
print "Compile Menu press [2]"
print "Compile Library press [3]"
print "Compile All Forms press [4]"
print "Compile ALL ( Forms,Libraries and Menus) [5]"
print "For Exit press [0]"
read answer
case $answer in
1)
print "Database schema"
read schema
print "Schema password"
read schema_pass
print "Connection string"
read connection
print "Write the form name"
read form_name
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$form_name module_type=Form
;;
2)
print "Database schema"
read schema
print "Schema password"
read schema_pass
print "Connection string"
read connection
print "Write the Menu name"
read menu_name
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$menu_name module_type=Menu
;;
3)
print "Database schema"
read schema
print "Schema password"
read schema_pass
print "Connection string"
read connection
print "Write the Library name"
read library_name
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$library_name module_type=Library
;;
4) print "Database schema"
read schema
print "Schema password"
read schema_pass
print "Connection string"
read connection
for i in `ls *.fmb`; do
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$i output_file=$EXE module_type=Form
done
;;
5) print "Database schema"
read schema
print "Schema password"
read schema_pass
print "Connection string"
read connection
print " Compiling libaries .."
for i in `ls *.pll`; do
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$i output_file=$EXE module_type=Libary
done
for i in `ls *.fmb`; do
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$i output_file=$EXE module_type=Form
done
for i in `ls *.mmb`; do
frmcmp_batch.sh userid=$schema/$schema_pass@$connection batch=yes module=$i output_file=$EXE module_type=Menu
done

;;
*)
exit 0
;;
esac

Set User's password never exprired in AIX 5L

2011-04-20T08:18:00.002+02:00

smitty user

change/show characteristics of a user

type the username in user name field

set password max age to 0 ( disables feature)

set password min age to 0 ( you can change the password at any time –manual)

set disable to remote login ( false) and use the user as su - for the best security.

Rolling Patch Upgrade Using RAC

2011-04-13T22:33:00.001+02:00

Supported on single patches that are marked as “rolling upgrade compatible” . You can determine if the patch is flagged as rolling upgradeable by checking

patch#/etc/config/inventory file see

online_rac_installable>true
Consider a RAC with 4 nodes
Stop the instance on node1
apply the patch
Restart node
Stress node in order to see patch’s impact
Start instance
do the same for each node , until you apply the patch in all nodes

Create Oracle User

2011-04-13T08:33:00.002+02:00

As system :
--Create application's users profile
--Create user
--Grant Roles,System and Object privellege to user.

CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;

CREATE USER michael
IDENTIFIED BY michael
DEFAULT TABLESPACE example
QUOTA UNLIMITED ON example
TEMPORARY TABLESPACE temp
PROFILE app_user
PASSWORD EXPIRE;

grant connect,resource to michael;

Ref:
1.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8003.htm
2. http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6010.htm#i2065

As is Apr, 2011

Deleting Expired RMAN Backups and Archives Logs after CROSSCHECK

2011-04-04T10:44:00.000+02:00

export ORACLE_SID= (UNIX)
or
set
ORACLE_SID=
RMAN TARGET / catalog rman/rman@rman cmdfile=C:\cleanObsoleteArchives\clean.rman

clean.rman
=============
change archivelog all crosscheck;
crosscheck backupset ;
DELETE EXPIRED BACKUP;
crosscheck archivelog all;
delete noprompt obsolete;
restore validate database;
list backup;
exit;

Ref: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/maint003.htm#i1008500

Prepare a stress testing using TPC-H benchmark

2011-04-02T21:57:00.002+02:00

Step1
==============================
TPCH Database creation
==============================
C:\>sqlplus / as sysdba

SQL> CREATE DATABASE TPCH
MAXDATAFILES 100
MAXLOGFILES 6
MAXLOGHISTORY 100
MAXINSTANCES 1
ARCHIVELOG
LOGFILE
GROUP 1 ('/home/oracle/DB11G/redo01.dbf') size 10M,
GROUP 2 ('/home/oracle/DB11G/redo02.dbf') size 10M
DATAFILE '/home/oracle/DB11G/system01.dbf' size 512M AUTOEXTEND ON MAXSIZE UNLIMITED
SYSAUX DATAFILE '/home/oracle/DB11G/sysaux01.dbf' size 512M AUTOEXTEND ON MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP tempfile '/home/oracle/DB11G/temp01.tmp' size 128M AUTOEXTEND OFF
UNDO TABLESPACE UNDO1 DATAFILE '/home/oracle/DB11G/undo101.dbf' size 128M AUTOEXTEND ON
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8;

SQL> create user tpch identified by tpch;
SQL> grant connect,resource to tpch;
=========================================================
Step 2
========================================================
Download and uncompress the tpc-h source code from http://www.tpc.org
Edit the makefile.suite as follows and Compile/make tpc-h source usning make –f command.

CC = gcc
DATABASE = SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH

$ make -f makefile.suite
=====================================================================================
Step 3
====================================================================================
Create tpc-h’s data files using dbgen

[root@s01 tpch]# ./dbgen -f -v -s 1 # where 1 means = 1g of data
TPC-H Population Generator (Version 2.7.0 build 47)
Copyright Transaction Processing Performance Council 1994 - 2008
Generating data for suppliers table [pid: 4566]/
Preloading text ... 100%
done.
Generating data for customers table [pid: 4566]done.
Generating data for orders/lineitem tables [pid: 4566]done.
Generating data for part/partsupplier tables [pid: 4566]done.
Generating data for nation table [pid: 4566]done.
Generating data for region table [pid: 4566]done.
=====================================================================================
Step 3
=============================================================================

Create the tpc-h schema using the dss.ddl for Oracle 11g

-- Sccsid: @(#)dss.ddl 2.1.8.1
CREATE TABLE NATION ( N_NATIONKEY NUMBER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY NUMBER NOT NULL,
N_COMMENT VARCHAR2(152));

CREATE TABLE REGION ( R_REGIONKEY NUMBER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR2(152));

CREATE TABLE PART ( P_PARTKEY NUMBER NOT NULL,
P_NAME VARCHAR2(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR2(25) NOT NULL,
P_SIZE NUMBER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE NUMBER(15,2) NOT NULL,
P_COMMENT VARCHAR2(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY NUMBER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR2(40) NOT NULL,
S_NATIONKEY NUMBER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL NUMBER(15,2) NOT NULL,
S_COMMENT VARCHAR2(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY NUMBER NOT NULL,
PS_SUPPKEY NUMBER NOT NULL,
PS_AVAILQTY NUMBER NOT NULL,
PS_SUPPLYCOST NUMBER(15,2) NOT NULL,
PS_COMMENT VARCHAR2(199) NOT NULL );

CREATE TABLE CUSTOMER ( C_CUSTKEY NUMBER NOT NULL,
C_NAME VARCHAR2(25) NOT NULL,
C_ADDRESS VARCHAR2(40) NOT NULL,
C_NATIONKEY NUMBER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL NUMBER(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR2(117) NOT NULL);

CREATE TABLE ORDERS ( O_ORDERKEY NUMBER NOT NULL,
O_CUSTKEY NUMBER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE NUMBER(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY NUMBER NOT NULL,
O_COMMENT VARCHAR2(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY NUMBER NOT NULL,
L_PARTKEY NUMBER NOT NULL,
L_SUPPKEY NUMBER NOT NULL,
L_LINENUMBER NUMBER NOT NULL,
L_QUANTITY NUMBER(15,2) NOT NULL,
L_EXTENDEDPRICE NUMBER(15,2) NOT NULL,
L_DISCOUNT NUMBER(15,2) NOT NULL,
L_TAX NUMBER(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR2(44) NOT NULL);
================================================================================
Step 4
=================================================================================

Loading benchmark’s data using sqlldr

***** Ensure that your NLS_DATE_FORMAT="YYYY-MM_DD" *******************(ANSI)

sqlldr tpch_usr/tpch_usr control=customer.tbl
sqlldr tpch_usr/tpch_usr control=lineitem.tbl
sqlldr tpch_usr/tpch_usr control=nation.tbl
sqlldr tpch_usr/tpch_usr control=orders.tbl
sqlldr tpch_usr/tpch_usr control=partsupp.tbl
sqlldr tpch_usr/tpch_usr control=part.tbl
sqlldr tpch_usr/tpch_usr control=region.tbl
sqlldr tpch_usr/tpch_usr control=supplier.tbl

Example of modification

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE "NATION"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
N_NATIONKEY,
N_NAME,
N_REGIONKEY,
N_COMMENT
)
begindata
0|ALGERIA|0| haggle. carefully final deposits detect slyly agai
1|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon
2|BRAZIL|1|y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
3|CANADA|1|eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
===================================================================
Step 5
==================================================================
When you complete the TPC-H installation (create of data and database loading) , run the following java class (A multithreaded application that contains TPC-H's queries) that execute TPC-H queries randomly. The class allows you to define the number of connections. For example if you define 5 threads, then 5 connections are established to your database, which run randomly 20 TPC-H queries.

package mypackage;
import java.sql.*;
import java.util.Random;
import java.util.Vector;
import oracle.sql.*;
import oracle.jdbc.*;

public class Action implements Runnable
{
int action_id;
int query_id;
String query_text="";
Vector temp = new Vector();
String thread_status= "IDLE";
private Connection conn=null;
private Statement stm=null;

///Database connection
private String server="192.168.10.3";
private String service_name="orcl";
private String port="1521";
private String username="tpch";
private String password="tpch";

public Action()
{
}
public Action(int action_id)
{
this.action_id=action_id;
}

private void datasourse() throws Exception
{
try
{

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn=DriverManager.getConnection("jdbc:oracle:thin:@"+server+":"+port+":"+service_name, username,password);
this.stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}

public void executeQuery(String query)
{
ResultSet rst = null;
try
{
rst = this.stm.executeQuery(query);
while (rst.next()){}

}
catch (Exception e1)
{
System.out.println(e1.getMessage());
}
}

public void run()
{
try
{
datasourse();
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
this.thread_status = "RUNNING";
temp.clear();
for (int i=1; i <= 20 ; i++)
{
Random g = new Random();
this.query_id=g.nextInt(10)+1;
while ( exist(new Integer(this.query_id)))
{
this.query_id=g.nextInt(10)+1;
}
temp.add(new Integer(this.query_id));
System.out.println(" The Worker ["+this.action_id+"] executed the query "+this.query_id);
this.query_text=getQueryText();
//System.out.println(" Query Text "+this.query_text);
executeQuery(this.query_text);
this.thread_status = "CLOSED";
}
try
{
conn.close();
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
}

public String getThreadStatus()
{
return this.thread_status;
}

public int getThreadID()
{
return this.getThreadID();
}

public String getThreadQueryText()
{
return this.query_text;
}

public boolean exist( Integer a)
{
int b = 1;
Integer c = new Integer(-1);

for (int i=0; i {
//System.out.println((Integer)temp.elementAt(i));
c=(Integer)temp.elementAt(i);
if (c.equals(a))
{
//System.out.println("equal");
b = 0;
}
}
if (b==0)
{
return true;
}
else
{
return false;
}
}

private String getQueryText()
{
if (query_id==1)
{
return "\n"+
" select "+
" l_returnflag, " +
" l_linestatus, " +
" sum(l_quantity) as sum_qty, " +
" sum(l_extendedprice) as sum_base_price, " +
" sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, " +
" sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, " +
" avg(l_quantity) as avg_qty, " +
" avg(l_extendedprice) as avg_price, " +
" avg(l_discount) as avg_disc, " +
" count(*) as count_order " +
" from " +
" lineitem " +
" where " +
" l_shipdate <= to_date('1998-12-01','YYYY-MM-DD') - interval '3' day " +
" group by " +
" l_returnflag, " +
" l_linestatus " +
" order by " +
" l_returnflag, " +
" l_linestatus " ;
}
else if (query_id==2)
{
return "\n"+
" select " +
" s_acctbal, " +
" s_name, " +
" n_name, " +
" p_partkey, " +
" p_mfgr, " +
" s_address, " +
" s_phone, " +
" s_comment " +
" from " +
" part, " +
" supplier, " +
" partsupp, " +
" nation, " +
" region " +
" where " +
" p_partkey = ps_partkey " +
" and s_suppkey = ps_suppkey " +
" and p_size = 41 " +
" and p_type like 'PROMO%' " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'AFRICA' " +
" and ps_supplycost = ( " +
" select " +
" min(ps_supplycost) " +
" from " +
" partsupp, " +
" supplier, " +
" nation, " +
" region " +
" where " +
" p_partkey = ps_partkey " +
" and s_suppkey = ps_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'AFRICA' " +
" ) " +
" order by " +
" s_acctbal desc, " +
" n_name, " +
" s_name, " +
" p_partkey ";
}
else if (query_id==3)
{
return "\n"+
" select " +
" l_orderkey, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue, " +
" o_orderdate, " +
" o_shippriority " +
" from " +
" customer, " +
" orders, " +
" lineitem " +
" where " +
" c_mktsegment = 'BUILDING' " +
" and c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and o_orderdate < to_date('1998-08-02','YYYY-MM-DD') " +
" and l_shipdate > to_date('1992-01-02','YYYY-MM-DD') " +
" and rownum <= 10 " +
" group by " +
" l_orderkey, " +
" o_orderdate, " +
" o_shippriority " +
" order by " +
" revenue desc, " +
" o_orderdate " ;
}
else if (query_id==4)
{
return "\n"+
" select " +
" o_orderpriority, " +
" count(*) as order_count " +
" from " +
" orders " +
" where " +
" o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1998-08-02','YYYY-MM-DD') + interval '3' month " +
" and exists ( " +
" select " +
" * " +
" from " +
" lineitem " +
" where " +
" l_orderkey = o_orderkey " +
" and l_commitdate < l_receiptdate " +
" ) " +
" and rownum <= 10 " +
" group by " +
" o_orderpriority " +
" order by " +
" o_orderpriority " ;
}
else if (query_id==5)
{
return "\n"+
" select " +
" n_name, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue " +
" from " +
" customer, " +
" orders, " +
" lineitem, " +
" supplier, " +
" nation, " +
" region " +
" where " +
" c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and l_suppkey = s_suppkey " +
" and c_nationkey = s_nationkey " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'EUROPE' " +
" and o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1996-01-01','YYYY-MM-DD') + interval '1' year " +
" and rownum <= 10 " +
" group by " +
" n_name " +
" order by " +
" revenue desc ";
}
else if (query_id==6)
{
return "\n"+
" select " +
" sum(l_extendedprice * l_discount) as revenue " +
" from " +
" lineitem " +
" where " +
" l_shipdate >= to_date('1993-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1998-01-01','YYYY-MM-DD') + interval '1' year " +
" and l_discount between - 0.01 and + 0.01 " +
" and l_quantity < 2 " ;
}
else if (query_id==7)
{
return "\n"+
" select " +
" supp_nation, " +
" cust_nation, " +
" l_year, " +
" sum(volume) as revenue " +
" from " +
" ( " +
" select " +
" n1.n_name as supp_nation, " +
" n2.n_name as cust_nation, " +
" extract(year from l_shipdate) as l_year, " +
" l_extendedprice * (1 - l_discount) as volume " +
" from " +
" supplier, " +
" lineitem, " +
" orders, " +
" customer, " +
" nation n1, " +
" nation n2 " +
" where " +
" s_suppkey = l_suppkey " +
" and o_orderkey = l_orderkey " +
" and c_custkey = o_custkey " +
" and s_nationkey = n1.n_nationkey " +
" and c_nationkey = n2.n_nationkey " +
" and ( " +
" (n1.n_name = 'RUSSIA' and n2.n_name = 'FRANCE') " +
" or (n1.n_name = 'CANADA' and n2.n_name = 'RUSSIA') " +
" ) " +
" and l_shipdate between to_date('1995-01-01','YYYY-MM-DD') " +
" and to_date('1996-12-31','YYYY-MM-DD') " +
" ) shipping " +
" where rownum <= 10 " +
" group by " +
" supp_nation, " +
" cust_nation, " +
" l_year " +
" order by " +
" supp_nation, " +
" cust_nation, " +
" l_year " ;
}
else if (query_id==8)
{
return "\n"+
" select " +
" o_year, " +
" sum(case " +
" when nation = '' then volume " +
" else 0 " +
" end) / sum(volume) as mkt_share " +
" from " +
" ( " +
" select " +
" extract(year from o_orderdate) as o_year, " +
" l_extendedprice * (1 - l_discount) as volume, " +
" n2.n_name as nation " +
" from " +
" part, " +
" supplier, " +
" lineitem, " +
" orders, " +
" customer, " +
" nation n1, " +
" nation n2, " +
" region " +
" where " +
" p_partkey = l_partkey " +
" and s_suppkey = l_suppkey " +
" and l_orderkey = o_orderkey " +
" and o_custkey = c_custkey " +
" and c_nationkey = n1.n_nationkey " +
" and n1.n_regionkey = r_regionkey " +
" and r_name = 'EUROPE' " +
" and s_nationkey = n2.n_nationkey " +
" and o_orderdate between to_date('1995-01-01','YYYY-MM-DD') " +
" and to_date('1996-12-31','YYYY-MM-DD') " +
" and p_type = 'STANDARD PLATED BRASS' " +
" )all_nations " +
" where rownum <= 10 " +
" group by " +
" o_year " +
" order by " +
" o_year " ;
}
else if (query_id==9)
{
return "\n"+
" select " +
" nation, " +
" o_year, " +
" sum(amount) as sum_profit " +
" from " +
" ( " +
" select " +
" n_name as nation, " +
" extract(year from o_orderdate) as o_year, " +
" l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount " +
" from " +
" part, " +
" supplier, " +
" lineitem, " +
" partsupp, " +
" orders, " +
" nation " +
" where " +
" s_suppkey = l_suppkey " +
" and ps_suppkey = l_suppkey " +
" and ps_partkey = l_partkey " +
" and p_partkey = l_partkey " +
" and o_orderkey = l_orderkey " +
" and s_nationkey = n_nationkey " +
" and p_name like '%lemon%' " +
" )profit " +
" where rownum <= 10 " +
" group by " +
" nation, " +
" o_year " +
" order by " +
" nation, " +
" o_year desc " ;

}
else if (query_id==10)
{
return "\n"+
" select " +
" c_custkey, " +
" c_name, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue, " +
" c_acctbal, " +
" n_name, " +
" c_address, " +
" c_phone, " +
" c_comment " +
" from " +
" customer, " +
" orders, " +
" lineitem, " +
" nation " +
" where " +
" c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1996-01-01','YYYY-MM-DD') + interval '3' month " +
" and l_returnflag = 'R' " +
" and c_nationkey = n_nationkey " +
" and rownum <= 10 " +
" group by " +
" c_custkey, " +
" c_name, " +
" c_acctbal, " +
" c_phone, " +
" n_name, " +
" c_address, " +
" c_comment " +
" order by " +
" revenue desc " ;
}
else if (query_id==11)
{
return "\n"+
" select " +
" ps_partkey, " +
" sum(ps_supplycost * ps_availqty) as value " +
" from " +
" partsupp, " +
" supplier, " +
" nation " +
" where " +
" ps_suppkey = s_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_name = 'UNITED KINGDOM' " +
" and rownum <= 10 " +
" group by " +
" ps_partkey having " +
" sum(ps_supplycost * ps_availqty) > ( " +
" select " +
" sum(ps_supplycost * ps_availqty) * 0.00015 " +
" from " +
" partsupp, " +
" supplier, " +
" nation " +
" where " +
" ps_suppkey = s_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_name = 'UNITED KINGDOM' " +
" ) " +
" order by " +
" value desc " ;
}
else if (query_id==12)
{
return "\n"+
" select " +
" l_shipmode, " +
" sum(case " +
" when o_orderpriority = '1-URGENT' " +
" or o_orderpriority = '2-HIGH' " +
" then 1 " +
" else 0 " +
" end) as high_line_count, " +
" sum(case " +
" when o_orderpriority <> '1-URGENT' " +
" and o_orderpriority <> '2-HIGH' " +
" then 1 " +
" else 0 " +
" end) as low_line_count " +
" from " +
" orders, " +
" lineitem " +
" where " +
" o_orderkey = l_orderkey " +
" and l_shipmode in ('MAIL', 'AIR') " +
" and l_commitdate < l_receiptdate " +
" and l_shipdate < l_commitdate " +
" and l_receiptdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_receiptdate < to_date('1997-01-01','YYYY-MM-DD') + interval '1' year " +
" and rownum <= 10 " +
" group by " +
" l_shipmode " +
" order by " +
" l_shipmode ";
}
else if (query_id==13)
{
return "\n"+
" select " +
" c_count, " +
" count(*) as custdist " +
" from " +
" ( " +
" select " +
" c_custkey, " +
" count(o_orderkey) as c_count " +
" from " +
" customer left outer join orders on " +
" c_custkey = o_custkey " +
" and o_comment not like '%deposits%' " +
" group by " +
" c_custkey " +
" ) c_orders " +
" group by " +
" c_count " +
" order by " +
" custdist desc, " +
" c_count desc; " ;
}
else if (query_id==14)
{
return "\n"+
" select " +
" 100.00 * sum(case " +
" when p_type like 'PROMO%' " +
" then l_extendedprice * (1 - l_discount) " +
" else 0 " +
" end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue " +
" from " +
" lineitem, " +
" part " +
" where " +
" l_partkey = p_partkey " +
" and l_shipdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1997-01-01','YYYY-MM-DD') + interval '1' month ";

}
else if (query_id==15)
{
return "\n"+
" create view revenue0 (supplier_no, total_revenue) as " +
" select " +
" l_suppkey, " +
" sum(l_extendedprice * (1 - l_discount)) " +
" from " +
" lineitem " +
" where " +
" l_shipdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1996-01-01','YYYY-MM-DD') + interval '3' month " +
" group by " +
" l_suppkey; " +
" " +
" " +
" select " +
" s_suppkey, " +
" s_name, " +
" s_address, " +
" s_phone, " +
" total_revenue " +
" from " +
" supplier, " +
" revenue0 " +
" where " +
" s_suppkey = supplier_no " +
" and total_revenue = ( " +
" select " +
" max(total_revenue) " +
" from " +
" revenue0 " +
" ) " +
" order by " +
" s_suppkey; " +
" drop view revenue0 " ;
}
else if (query_id==16)
{
return "\n"+
" select " +
" p_brand, " +
" p_type, " +
" p_size, " +
" count(distinct ps_suppkey) as supplier_cnt " +
" from " +
" partsupp, " +
" part " +
" where " +
" p_partkey = ps_partkey " +
" and p_brand <> 'Brand#24' " +
" and p_type not like 'PROMO%' " +
" and p_size in (34,36,44,48,6,32,11,21) " +
" and ps_suppkey not in ( " +
" select " +
" s_suppkey " +
" from " +
" supplier " +
" where " +
" s_comment like '%Customer%Complaints%' " +
" ) " +
" group by " +
" p_brand, " +
" p_type, " +
" p_size " +
" order by " +
" supplier_cnt desc, " +
" p_brand, " +
" p_type, " +
" p_size ";
}
else if (query_id==17)
{
return "\n"+
" Select sysdate from dual ";
/*
* select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container = 'SM BOX'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);
*/
}
else if (query_id==18)
{
return "\n"+
" select " +
" c_name, " +
" c_custkey, " +
" o_orderkey, " +
" o_orderdate, " +
" o_totalprice, " +
" sum(l_quantity) " +
" from " +
" customer, " +
" orders, " +
" lineitem " +
" where " +
" o_orderkey in ( " +
" select " +
" l_orderkey " +
" from " +
" lineitem " +
" group by " +
" l_orderkey having " +
" sum(l_quantity) > 10 " +
" ) " +
" and c_custkey = o_custkey " +
" and o_orderkey = l_orderkey " +
" and rownum < 10 " +
" group by " +
" c_name, " +
" c_custkey, " +
" o_orderkey, " +
" o_orderdate, " +
" o_totalprice " +
" order by " +
" o_totalprice desc, " +
" o_orderdate; " ;
}
else if (query_id==19)
{
return "\n"+
" select "+
" sum(l_extendedprice* (1 - l_discount)) as revenue " +
" from " +
" lineitem, " +
" part " +
" where " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#24' " +
" and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') " +
" and l_quantity >=9.00 and l_quantity <=9.00 + 10 " +
" and p_size between 1 and 5 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" or " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#33' " +
" and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') " +
" and l_quantity >=13.00 and l_quantity <=13.00 + 10 " +
" and p_size between 1 and 10 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" or " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#13' " +
" and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') " +
" and l_quantity >=47.00 and l_quantity <=47.00 + 10 " +
" and p_size between 1 and 15 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" and rownum < 10 " ;
}
else if (query_id==20)
{
return "\n"+
" select " +
" cntrycode, " +
" count(*) as numcust, " +
" sum(c_acctbal) as totacctbal " +
" from " +
" ( " +
" select " +
" substr(c_phone,1,2) as cntrycode, " +
" c_acctbal " +
" from " +
" customer " +
" where " +
" substr(c_phone,1,2) in " +
" ('25', '23', '11', '14', '12', '20', '16') " +
" and c_acctbal > ( " +
" select " +
" avg(c_acctbal) " +
" from " +
" customer " +
" where " +
" c_acctbal > 0.00 " +
" and substr(c_phone,1,2) in " +
" ('25', '23', '11', '14', '12', '20', '16') " +
" ) " +
" and not exists ( " +
" select " +
" * " +
" from " +
" orders " +
" where " +
" o_custkey = c_custkey " +
" ) " +
" ) custsale " +
" group by " +
" cntrycode " +
" order by " +
" cntrycode";
}
else
{
return "\n"+
" Select sysdate from dual ";
}
}

public static void main(String[] args)
{
int max_thread_number = 1;
for (int i = 0; i <= max_thread_number ; i++)
{
Runnable task = new Action(i);
Thread worker = new Thread(task);
worker.start();
}
}

}

Generate Random Data, Using bind variables to improve performance

2011-03-29T11:52:00.001+02:00

create table salary_rates
(
dept char(1),
rate number(2,1)
);

alter table salary_rates cache;

create table emp
(
id number,
dept char,
salary number
);

create unique index emp_id_idx on emp(id);

create sequence emp_id;

CREATE OR REPLACE TRIGGER emp_trg
BEFORE INSERT ON emp
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT emp_id.NEXTVAL INTO :new.id FROM dual;
END;
/

--------Salary rates Loading data------------------------

insert into salary_rates values( 'A',0.1);
insert into salary_rates values( 'B',0.2);
insert into salary_rates values( 'C',0.3);
insert into salary_rates values( 'D',0.4);
commit;
--------Salary rates Loading data ------------------------

------------------------- Generate random depts -------------------------
CREATE OR REPLACE FUNCTION GET_RANDOM_DPT RETURN CHAR
IS
X NUMBER;
RTV CHAR;
BEGIN
X:=ROUND(dbms_random.value(1, 5));

IF (X=1) THEN
RTV := 'A';
ELSIF (X=2) THEN
RTV := 'B';
ELSIF (X=3) THEN
RTV := 'C';
ELSE
RTV := 'D';
END IF;

RETURN RTV;
END;
/
------------------------- Generate random depts -------------------------

create or replace procedure gen_data ( emp_population number)
is
begin
FOR i IN 1 .. emp_population loop
insert into emp(dept,salary) values(GET_RANDOM_DPT,ROUND(dbms_random.value(1,1000000)));
commit;
END LOOP;
end;
/
-----------------------------------------------------------------------------------------------------------

/* Scenario : Update employee salary , ONLY those employees current salary is less than 3000 according the employee's department increase rates.
------------------------------------------------------------------------------------------------------
update 1
Elapsed: 00:00:01.70

CREATE OR REPLACE PROCEDURE UPDATE1
AS
rate_ number;
begin
---open c_emp;
for i IN ( select id,dept,salary from emp) loop
if (i.salary < 3000 ) then
select rate into rate_ from salary_rates where dept=i.dept;
update emp set salary=(salary * rate_/100)+salary where id=i.id;
commit;
else
null;
end if;
end loop;
END UPDATE1;

CREATE OR REPLACE PROCEDURE UPDATE2
AS
rate_ number;
type rc is ref cursor;
q rc;
begin
for i IN ( select id,dept,salary from emp) loop
if (i.salary < 3007 ) then
open q for ' select rate from salary_rates where dept= :y ' using i.dept;
fetch q into rate_;
execute immediate ( ' update emp set salary=(salary * :r / 100) + salary where id= :x') using rate_,i.id;
commit;
else
null;
end if;
end loop;
END UPDATE2;
Elapsed: 00:00:01.32

How to move a table in new tablespace with NOLOGGING option

2011-03-29T08:03:00.000+02:00

/*
Login as sys or system
create a tablespace with NOLOGGING option
*/

create tablespace sh_rate
datafile 'E:\ORADATA\PROD\DATAFILE\RATES.DBF' size 128M autoextend on maxsize 2048M
segment space management auto
NOLOGGING;
/*
Grant user (The owner of the table[s], indexes etc) to access the new tablespace
*/
alter user parameters quota unlimited on sh_rate;

/*
Move tables in the new tablespace
*/

alter table parameters.sh_rates_temp move tablespace sh_rate;
alter table parameters.sh_rate move tablespace sh_rate;

/*
create,Recreate, or rebuild indexes

Rebuild index : alter index xyz rebuild tablespace sh_rate;
*/
create index parameters.sh_rates_temp_idx1 on parameters.sh_rates_temp(code) tablespace sh_rate;
create index parameters.sh_rate_idx1 on parameters.sh_rate(code) tablespace sh_rate;

Find out DBID from RMAN catalog

2011-03-24T01:17:00.001+02:00

In the case that you need to restore control file or
spfile using RMAN , you need to set the DBID first.
In order to determine the DBID login as rman in rman catalog
and issue the following sql:

sqlplus rman/***@rman

sql>select dbid from rc_database where name='DBNAME';

Another way to find out the DBID is from your RMAN logs.

Example:

RMAN> set dbid 1150787593

executing command: SET DBID
database name is "ORCL" and DBID is 1150787593

RMAN> restore spfile;

Oracle RAC 10g Manage Server-Side Callouts

2011-03-19T22:56:00.001+02:00

In Oracle 10g RAC, every time a node , database , instance , service and etc. goes up or down, that event can be catched and used to make user defined callouts. So every time a state change occurs, a FAN event is posted to ONS immediately. When a node receives an event through ONS, it will asynchronously execute all executables in the server side callouts directory.

Callouts directory is under $CRS_HOME/racg/usrco

The example below creates a file in /home/oracle directory when the ocfs database is down. You can modify it with further functionality i.e. send mail to administrator people etc.

create the file parseCallout.sh under the $CRS_HOME/racg/usrco
chmod +x parseCallout.sh

#!/bin/sh
NOTIFY_EVENTTYPE=$1
echo $NOTIFY_EVENTTYPE
for ARCS in $*; do
PROPERTY=`echo $ARCS | awk -F"=" '{print $1}'`
echo $PROPERTY
VALUE=`echo $ARCS | awk -F"=" '{print $2}'`
echo $VALUE
case $PROPERTY in
VERSION|version)
NOTIFY_VERSION=$VALUE ;;
SERVICE|service)
NOTIFY_SERVICE=$VALUE ;;
DATABASE|database)
NOTIFY_DATABASE=$VALUE ;;
INSTANCE|instance)
NOTIFY_INSTANCE=$VALUE ;;
HOST|host)
NOTIFY_HOST=$VALUE ;;
STATUS|status)
NOTIFY_STATUS=$VALUE ;;
REASON|reason)
NOTIFY_REASON=$VALUE ;;
CARD|card)
NOTIFY_CARDINALITY=$VALUE ;;
TIMESTAMP|timestamp)
NOTIFY_LOGDATE=$VALUE ;;
??:??:??)
NOTIFY_LOGTIME=$PROPERTY ;;
esac
done
if ([ "$NOTIFY_STATUS" == "down" ] && [ "$NOTIFY_DATABASE" == "ocfs" ]) then
echo "$NOTIFY_DATABASE is down, please investigate ... " > /home/oracle/"$NOTIFY_LOGDATE".callout
fi

Rename file using the current date and time

2011-03-10T11:58:00.001+02:00

( Unix Version )

mv /file/path/filename.txt /file/path/filename_$(date +"%m%d%y%H%M%S").txt

Rename file using the current date and time ( Windows Version )

ren C:\file\path\filename.txt %time:~0,2%%time:~3,2%%time:~6,2%_%date:~4,2%%date:~7,2%%date:~10,4%_filename.txt

Convert Oracle Date to Unix epoch and vice versa

2011-03-10T11:44:00.002+02:00

From Oracle Date to Unix epoch

select ( TO_DATE('2010-06-29', 'YYYY-MM-DD')- TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400000 from dual;

From UNIX epoch to Oracle date

select TO_DATE('1970-01-01', 'YYYY-MM-DD') + 1277769600000 / 86400000 from dual

JAVA
=====
package mypackage;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class TestClear
{
public TestClear()
{
}
public String getUnixTime1()
{
java.lang.Long unixTime= new Long(System.currentTimeMillis()/1000L);
String unixTimeString = new String(""+unixTime);
//return unixTimeString.substring(0,6);
return unixTimeString;
}
public int getUnixTime2()
{
int year = 2010;
int month = 5;
int date = 29;
Calendar cal = Calendar.getInstance();
cal.clear();
cal.set(Calendar.YEAR, year);
cal.set(Calendar.MONTH, month);
cal.set(Calendar.DATE, date);
java.util.Date utilDate = cal.getTime();
java.lang.Long unixTime= new Long(cal.getTimeInMillis()/1000L);
String unixTimeString = new String(""+unixTime);
unixTimeString= unixTimeString.substring(0,5);
int unixTimeInt = Integer.parseInt(unixTimeString);
return unixTimeInt;
}

/**
*
* @param args
*/
public static void main(String[] args)
{
TestClear t= new TestClear();
System.out.println(t.getUnixTime2());
}
}

Migrate a normal file-system Oracle database to ASM database

2011-02-02T23:52:00.001+02:00

Migrate a normal file-system Oracle database to ASM database

by Michael Georgiou

Oracle Linux 5EL 64 bits , Oracle 10.2.0.4 64 bits

On ASM instance create a new disk group that will host the new database

CREATE DISKGROUP Dgroup2 NORMAL REDUNDANCY

FAILGROUP controller1 DISK

'ORCL:DISK5',

'ORCL:DISK6'

FAILGROUP controller2 DISK

'ORCL:DISK7' ,

'ORCL:DISK8'

/

Connect to Database ORCLF that is created using OMF files ( Filesystem)

[oracle@asm_machine oracle]$ export ORACLE_SID=orclf

[oracle@asm_machine oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 2 22:28:46 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Check if the block change tracking is enable

SQL> select status from V$block_change_tracking;

STATUS

----------

DISABLED

if enable the disable it using the command

alter database disable block change tracking;

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

Change the db_create_file_dest and db_recovery_file_dest to point on '+DGROUP2'

SQL> show parameter db_create

NAME TYPE VALUE

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

db_create_file_dest string /oracle/oradata

NAME TYPE VALUE

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

db_recovery_file_dest string /oracle/flash_recovery_area

db_recovery_file_dest_size big integer 2G

SQL>

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

Change OMF to ASM diskgroup Dgroup2

alter system set db_create_file_dest='+DGROUP2' scope=spfile;

alter system set db_recovery_file_dest='+DGROUP2' scope=spfile;

--Oracle recommends to use a different disk group for the recovery area. For the testing purposes I use the same disk group.

Recreate Control Files in new ASM location

In order to create the control file in the ASM , you need to change the control_files in spfile as follows:

alter system set control_files='+DGROUP2' scope=spfile;

shutdown database

SQL>shutdown immediate

connect to rman

RMAN> connect target

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 260046848 bytes

Fixed Size 2083168 bytes

Variable Size 83887776 bytes

Database Buffers 167772160 bytes

Redo Buffers 6303744 bytes

RMAN> restore controlfile from '/oracle/oradata/ORCLF/controlfile/o1_mf_6nmgt4mo_.ctl';

Starting restore at 02-FEB-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DGROUP2/orclf/controlfile/current.256.742086217

Finished restore at 02-FEB-11

RMAN>exit

[oracle@asm_machine dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 2 23:05:29 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

select open_mode from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter system set control_files='+DGROUP2/orclf/controlfile/current.256.742086217' scope=spfile;

System altered.

SQL> alter database mount;

Database altered.

SQL>exit

Migrate the whole database using RMAN COPY command

rman

RMAN> connect target

connected to target database: ORCLF (DBID=3004244068, not open)

RMAN> backup as copy database format '+DGROUP2';

Starting backup at 02-FEB-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/oracle/oradata/ORCLF/datafile/o1_mf_system_6nmgrm37_.dbf

output filename=+DGROUP2/orclf/datafile/system.257.742086491 tag=TAG20110202T230810 recid=1 stamp=742086537

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/oracle/oradata/ORCLF/datafile/o1_mf_sysaux_6nmgrm3d_.dbf

output filename=+DGROUP2/orclf/datafile/sysaux.258.742086545 tag=TAG20110202T230810 recid=2 stamp=742086573

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/oracle/oradata/ORCLF/datafile/o1_mf_undotbs1_6nmgrm64_.dbf

output filename=+DGROUP2/orclf/datafile/undotbs1.259.742086581 tag=TAG20110202T230810 recid=3 stamp=742086586

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/oracle/oradata/ORCLF/datafile/o1_mf_users_6nmgrm6c_.dbf

output filename=+DGROUP2/orclf/datafile/users.260.742086587 tag=TAG20110202T230810 recid=4 stamp=742086590

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=+DGROUP2/orclf/controlfile/backup.261.742086591 tag=TAG20110202T230810 recid=5 stamp=742086595

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 02-FEB-11

channel ORA_DISK_1: finished piece 1 at 02-FEB-11

piece handle=+DGROUP2/orclf/backupset/2011_02_02/nnsnf0_tag20110202t230810_0.262.742086599 tag=TAG20110202T230810 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 02-FEB-11

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGROUP2/orclf/datafile/system.257.742086491"

datafile 2 switched to datafile copy "+DGROUP2/orclf/datafile/undotbs1.259.742086581"

datafile 3 switched to datafile copy "+DGROUP2/orclf/datafile/sysaux.258.742086545"

datafile 4 switched to datafile copy "+DGROUP2/orclf/datafile/users.260.742086587"

RMAN> recover database;

Starting recover at 02-FEB-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 02-FEB-11

RMAN> alter database open;

database opened

RMAN>exit

Migrate Temp tablespace and Redo logs

SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;

FILE_NAME

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

TABLESPACE_NAME

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

/oracle/oradata/ORCLF/datafile/o1_mf_temp_6nmgtqkx_.tmp

TEMP

SQL> create temporary tablespace TEMP01 tempfile '+DGROUP2' size 128M autoextend off;

Tablespace created.

SQL> alter database default temporary tablespace TEMP01;

Database altered.

SQL> drop tablespace TEMP;

Tablespace dropped.

SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;

FILE_NAME

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

TABLESPACE_NAME

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

+DGROUP2/orclf/tempfile/temp01.263.742086965

TEMP01

SQL>

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 1;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 2;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 3;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 1;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 2;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 3;

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 '+DGROUP2' ;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 4;

SQL>ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 4;

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS

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

1 4 INACTIVE

2 4 INACTIVE

3 4 INACTIVE

4 2 CURRENT

SQL> select member from v$logfile;

MEMBER

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

/oracle/oradata/ORCLF/onlinelog/o1_mf_3_6nmgtdfp_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_3_6nmgtfpq_.log

/oracle/oradata/ORCLF/onlinelog/o1_mf_2_6nmgtb0c_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_2_6nmgtc7f_.log

/oracle/oradata/ORCLF/onlinelog/o1_mf_1_6nmgt7lv_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_1_6nmgt8wc_.log

+DGROUP2/orclf/onlinelog/group_1.264.742087309

+DGROUP2/orclf/onlinelog/group_2.265.742087325

+DGROUP2/orclf/onlinelog/group_3.266.742087431

+DGROUP2/orclf/onlinelog/group_4.267.742087451

+DGROUP2/orclf/onlinelog/group_1.268.742087647

MEMBER

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

+DGROUP2/orclf/onlinelog/group_2.269.742087669

+DGROUP2/orclf/onlinelog/group_3.270.742087687

+DGROUP2/orclf/onlinelog/group_4.271.742087703

14 rows selected.

SQL>

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_2_6nmgtb0c_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_2_6nmgtc7f_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_3_6nmgtdfp_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_3_6nmgtfpq_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_1_6nmgt7lv_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_1_6nmgt8wc_.log';

SQL> select member from v$logfile;

MEMBER

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

+DGROUP2/orclf/onlinelog/group_1.264.742087309

+DGROUP2/orclf/onlinelog/group_2.265.742087325

+DGROUP2/orclf/onlinelog/group_3.266.742087431

+DGROUP2/orclf/onlinelog/group_4.267.742087451

+DGROUP2/orclf/onlinelog/group_1.268.742087647

+DGROUP2/orclf/onlinelog/group_2.269.742087669

+DGROUP2/orclf/onlinelog/group_3.270.742087687

+DGROUP2/orclf/onlinelog/group_4.271.742087703

8 rows selected.

Create a database using the ASM storage

2011-01-31T23:43:00.002+02:00

Tested environment : Oracle Linux 5 64 bits
Oracle 10.2.0.4 64bits for Linux

Create the database files init and password file

initorcl.ora

audit_file_dest='/oracle/admin/orcl/adump'
background_dump_dest='/oracle/admin/orcl/bdump'
compatible='10.2.0.4.0'
core_dump_dest='/oracle/admin/orcl/cdump'
db_block_size=8192
db_file_multiblock_read_count=32
db_name='orcl'
job_queue_processes=10
open_cursors=300
pga_aggregate_target=128M
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=512M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/oracle/admin/orcl/udump'
db_create_file_dest='+DGROUP1'
control_files='+DGROUP1'
log_archive_dest="LOCATION='+DGROUP1'"
db_create_online_log_dest_1='+DGROUP1'
db_create_online_log_dest_2='+DGROUP1'
db_recovery_file_dest='+DGROUP1'
db_recovery_file_dest_size=6G
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

Password file

orapwd file=orapworcl password=oracle entries=5

Create the dumps directories as described in initorcl.ora

mkdir -p /oracle/admin/orcl/adump
mkdir -p /oracle/admin/orcl/cdump
mkdir -p /oracle/admin/orcl/bdump
mkdir -p /oracle/admin/orcl/udump

chmod 755 /oracle/admin

Start the instance using the nomount startup option

export ORACLE_SID=orcl
sqlplus / as sysdba
sql> startup nomount

Create the database

sql>CREATE DATABASE orcl
MAXDATAFILES 100
MAXLOGFILES 6
MAXLOGHISTORY 100
MAXINSTANCES 1
ARCHIVELOG
LOGFILE
GROUP 1 ('+DGROUP1') size 10M,
GROUP 2 ('+DGROUP1') size 10M
DATAFILE '+DGROUP1'
SYSAUX DATAFILE '+DGROUP1'
DEFAULT TEMPORARY TABLESPACE TEMP tempfile '+DGROUP1'
UNDO TABLESPACE UNDOTBS1 DATAFILE '+DGROUP1'
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8;

Create the data dictionary and the catalog

@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/utlrp

connect system/manager
@?/sqlplus/admin/pupbld

Important Note:
When you restart the database , the instance will not recognize the control file path ,since in init.ora this parameter is defined with ASM's incomplete name. Thus in order to start the database you need to replace it with an ASM qualified name or ASM numeric name or ASM alias . Thus , Open the asmcmd and find the control file qualifies name and use it your database's initorcl.ora file. Alternate , select the group_name,file and incarnation from v$ASM_FILE from ASM instance and use this format as my second example.

control_files="+DGROUP1/controlfile/current.256.740948485"
control_files="+DGROUP1.256.740948485

create spfile='+dgroup1(PARAMETERFILE)' from pfile;

ASM Files after database creation

ASMCMD>cd +DGROUP1/orcl
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
flash_recovery_area/
ASMCMD>
ASMCMD> cd ONLINELOG
ASMCMD> ls
group_1.257.740948489
group_2.258.740948491
ASMCMD> cd ../CONTROLFILE
ASMCMD> ls
Current.256.740948485
ASMCMD> cd ../DATAFILE
ASMCMD> ls
SYSAUX.261.740948525
SYSTEM.259.740948495
UNDOTBS1.260.740948513
ASMCMD> cd ../TEMPFILE
ASMCMD> ls
TEMP.262.740948535
ASMCMD> pwd
+DGROUP1/orcl/ARCHIVELOG/2011_01_21
ASMCMD> ls
thread_1_seq_43.264.741044093
thread_1_seq_44.265.741044381

Backup the database using RMAN , the only way to backup Oracle database having ASM files.

rman TARGET sys/oracle@orcl NOCATALOG

change archivelog all crosscheck;
run
{
allocate channel ch1 type disk;
backup incremental level 1 as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}

Find all ASM aliases files

set heading off
select '+'||dg.name||'/'||al.name
from v$asm_diskgroup dg,v$asm_alias al
where dg.group_number=al.group_number
/

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';

My system aliases
+DGROUP1/orcl
+DGROUP1/utilities.dbf
+DGROUP1/ARCHIVELOG
+DGROUP1/2011_01_21
+DGROUP1/thread_1_seq_43.264.741044093
+DGROUP1/thread_1_seq_44.265.741044381
+DGROUP1/thread_1_seq_45.268.741045337
+DGROUP1/thread_1_seq_46.269.741045341
+DGROUP1/thread_1_seq_47.272.741045529
+DGROUP1/thread_1_seq_48.273.741045531
+DGROUP1/thread_1_seq_49.274.741046177
+DGROUP1/thread_1_seq_50.275.741046187
+DGROUP1/thread_1_seq_51.276.741046197

+DGROUP1/thread_1_seq_52.277.741046205
+DGROUP1/thread_1_seq_53.278.741046213
+DGROUP1/thread_1_seq_54.279.741046223
+DGROUP1/thread_1_seq_55.280.741046231
+DGROUP1/thread_1_seq_56.281.741046243
+DGROUP1/thread_1_seq_57.282.741046257
+DGROUP1/thread_1_seq_58.283.741046279
+DGROUP1/2011_01_22
+DGROUP1/thread_1_seq_59.285.741053405
+DGROUP1/CONTROLFILE
+DGROUP1/Current.256.740948485
+DGROUP1/ONLINELOG
+DGROUP1/group_1.257.740948489

+DGROUP1/group_2.258.740948491
+DGROUP1/DATAFILE
+DGROUP1/SYSTEM.259.740948495
+DGROUP1/UNDOTBS1.260.740948513
+DGROUP1/SYSAUX.261.740948525
+DGROUP1/USERS.263.740964343
+DGROUP1/UTILITIES.284.741048253
+DGROUP1/TEMPFILE
+DGROUP1/TEMP.262.740948535
+DGROUP1/BACKUPSET
+DGROUP1/2011_01_21
+DGROUP1/nnndn1_ORCL_L1_0.266.741045261
+DGROUP1/ncsnn1_ORCL_L1_0.267.741045327

+DGROUP1/nnndn1_ORCL_L1_0.270.741045483
+DGROUP1/ncsnn1_ORCL_L1_0.271.741045519
+DGROUP1/control01.ctl
+DGROUP1/flash_recovery_area

alter diskgroup DGROUP1 add alias '+DGROUP1/users.dbf' for '+DGROUP1/orcl/datafile/users.263.740964343';

Create ASM instance Manualy on Oracle 10gR2 ( Using Virtual Box)

2011-01-16T00:18:00.002+02:00

Automatic Storage Manage on Oracle 10gR2 RAC
Oracle Virtual Box 3.2.12 using Oracle Linux 5

Create the VM on Oracle Virtual Box 3.2.12 ( ASM_MACHINE)

Create VM's disks

"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk1.vdi --size 2048 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk2.vdi --size 2048 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk3.vdi --size 2048 --format VDI --variant Fixed

"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk4.vdi --size 2048 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk5.vdi --size 2048 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk6.vdi --size 2048 --format VDI --variant Fixed

"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk7.vdi --size 2048 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage" createhd --filename D:\ora-virtual-box\asm-disks\asm-disk8.vdi --size 2048 --format VDI --variant Fixed

Assign the disk to the machine ASM_MACHINE

"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 1 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk1.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 2 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk2.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 3 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk3.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 4 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk4.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 5 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk5.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 6 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk6.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 7 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk7.vdi
"C:\Program Files\Oracle\VirtualBox\VBoxManage" storageattach ASM_MACHINE --storagectl "SATA Controller" --port 8 --device 0 --type hdd --medium D:\ora-virtual-box\asm-disks\asm-disk8.vdi

Install Oracle Linux 5 64bit , prepare Oracle's kernel parameters OS depended rpm packages.
....

Install ASMLib rpms using the following command

for the kernel: 2.6.18-194.el5
rpm -ivh oracleasm-support-2.1.3-1.el5.x86_64.rpm
rpm -ivh oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm

Configure ASMLib using the following command
oracleasm configure -i

Load the kernel module using the following command.
usr/sbin/oracleasm init

Make sure you have the correct version of the driver
/usr/sbin/oracleasm update-driver

Mark the five shared disks as follows
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/usr/sbin/oracleasm createdisk DISK2 /dev/sdc1
/usr/sbin/oracleasm createdisk DISK3 /dev/sdd1
/usr/sbin/oracleasm createdisk DISK4 /dev/sde1

/usr/sbin/oracleasm createdisk DISK5 /dev/sdf1
/usr/sbin/oracleasm createdisk DISK6 /dev/sdg1
/usr/sbin/oracleasm createdisk DISK7 /dev/sdh1
/usr/sbin/oracleasm createdisk DISK8 /dev/sdi1

Configuration the local CSS
( run it as root)

#localconfig delete
/etc/oracle does not exist. Creating it now.
/u01/app/oracle/product/10.2.0/db_1/bin/localconfig: line 737: /etc/init.d/init.cssd: No such file or directory

#localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
asm_machine
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)

ps -ef | grep css
oracle 14277 1 0 21:36 ? 00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/ocssd.bin

Create the ASM instance
Directories:
mkdir -p /oracle/admin/+ASM/bdump
mkdir -p /oracle/admin/+ASM/cdump
mkdir -p /oracle/admin/+ASM/udump

Pfile :
create the init+ASM.ora under $ORACLE_HOME/dbs and add the following :

background_dump_dest=/oracle/admin/+ASM/bdump
core_dump_dest=/oracle/admin/+ASM/cdump
user_dump_dest=/oracle/admin/+ASM/udump
instance_type=asm
compatible=10.2.0.4
large_pool_size=12M
remote_login_passwordfile=exclusive
ASM_DISKGROUPS=Dgroup1
ASM_DISKSTRING='ORCL:DISK*'

Password File:
orapwd file=orapw+asm password=oracle entries=5

/etc/oratab
+ASM:/u01/app/oracle/product/10.2.0/db_1:N

Start the ASM instance and Create Diskgroups
Note:
NORMAL REDUNDANCY (2-way mirroring by default for most file types)
HIGH REDUNDANCY (3-way mirroring for all files)

[oracle@asm_machine dbs]$ export ORACLE_SID=+ASM
[oracle@asm_machine dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 15 23:30:46 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ASM instance started

Total System Global Area 130023424 bytes
Fixed Size 2082208 bytes
Variable Size 102775392 bytes
ASM Cache 25165824 bytes
SQL>

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- ------------
0 1 CLOSED PROVISIONED NORMAL ORCL:DISK2
0 2 CLOSED PROVISIONED NORMAL ORCL:DISK3
0 7 CLOSED PROVISIONED NORMAL ORCL:DISK8
0 4 CLOSED PROVISIONED NORMAL ORCL:DISK5
0 5 CLOSED PROVISIONED NORMAL ORCL:DISK6
0 6 CLOSED PROVISIONED NORMAL ORCL:DISK7
0 3 CLOSED PROVISIONED NORMAL ORCL:DISK4

•UNKNOWN - Automatic Storage Management disk header has not been read

•CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

•INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.

•PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement.
The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED
implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

•MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group.
The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

•FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

•CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

•FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

SQL>CREATE DISKGROUP Dgroup1 NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'ORCL:DISK1',
'ORCL:DISK2'
FAILGROUP controller2 DISK
'ORCL:DISK3' ,
'ORCL:DISK4'
/

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- ---------------
0 4 CLOSED PROVISIONED NORMAL ORCL:DISK5
0 5 CLOSED PROVISIONED NORMAL ORCL:DISK6
0 6 CLOSED PROVISIONED NORMAL ORCL:DISK7
0 7 CLOSED PROVISIONED NORMAL ORCL:DISK8
1 0 CACHED MEMBER NORMAL ORCL:DISK1
1 1 CACHED MEMBER NORMAL ORCL:DISK2
1 2 CACHED MEMBER NORMAL ORCL:DISK3
1 3 CACHED MEMBER NORMAL ORCL:DISK4

Mount new disk in linux

2010-12-21T13:22:00.000+02:00

fdisk -l
fdisk /dev/sdl
n
p
w

mkfs.ext3 -b 4096 /dev/sdl1
mkdir /oracle
# add in /etc/fstab the following line
/dev/sdl1 /oracle ext3 defaults 0 0
mount /oracle

Find if port 1521 is open using netstat( windows, unix )

2010-01-22T09:19:00.002+02:00

Windows
========
netstat -an |find /i "listening" |find /i "1521"

Unix ( Aix - Linux etc)
==========================
netstat -an | grep LISTEN |grep 1521

How to find which tables have FK bases on PK parent table

2009-12-16T12:52:00.005+02:00

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and status='ENABLED'
and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name='TABLE_NAME');

Dump Oracle String

2009-11-30T12:21:00.003+02:00

SQL> select dump('Michael Georgiou') from dual;

DUMP('MICHAELGEORGIOU')
-------------------------------------------------------------------------
Typ=96 Len=16: 77,105,99,104,97,101,108,32,71,101,111,114,103,105,111,117

SQL> select ascii('M') from dual;

ASCII('M')
----------
77

SQL> select ascii('i') from dual;

ASCII('I')
----------
105

Complete list of ascii characters are listed below:

Dec Hex Oct Char Description
0 0 000 null
1 1 001 start of heading
2 2 002 start of text
3 3 003 end of text
4 4 004 end of transmission
5 5 005 enquiry
6 6 006 acknowledge
7 7 007 bell
8 8 010 backspace
9 9 011 horizontal tab
10 A 012 new line
11 B 013 vertical tab
12 C 014 new page
13 D 015 carriage return
14 E 016 shift out
15 F 017 shift in
16 10 020 data link escape
17 11 021 device control 1
18 12 022 device control 2
19 13 023 device control 3
20 14 024 device control 4
21 15 025 negative acknowledge
22 16 026 synchronous idle
23 17 027 end of trans. block
24 18 030 cancel
25 19 031 end of medium
26 1A 032 substitute
27 1B 033 escape
28 1C 034 file separator
29 1D 035 group separator
30 1E 036 record separator
31 1F 037 unit separator
32 20 040 space
33 21 041 !
34 22 042 "
35 23 043 #
36 24 044 $
37 25 045 %
38 26 046 &
39 27 047 '
40 28 050 (
41 29 051 )
42 2A 052 *
43 2B 053 +
44 2C 054 ,
45 2D 055 -
46 2E 056 .
47 2F 057 /
48 30 060 0
49 31 061 1
50 32 062 2
51 33 063 3
52 34 064 4
53 35 065 5
54 36 066 6
55 37 067 7
56 38 070 8
57 39 071 9
58 3A 072 :
59 3B 073 ;
60 3C 074 <
61 3D 075 =
62 3E 076 >
63 3F 077 ?
Dec Hex Oct Char
64 40 100 @
65 41 101 A
66 42 102 B
67 43 103 C
68 44 104 D
69 45 105 E
70 46 106 F
71 47 107 G
72 48 110 H
73 49 111 I
74 4A 112 J
75 4B 113 K
76 4C 114 L
77 4D 115 M
78 4E 116 N
79 4F 117 O
80 50 120 P
81 51 121 Q
82 52 122 R
83 53 123 S
84 54 124 T
85 55 125 U
86 56 126 V
87 57 127 W
88 58 130 X
89 59 131 Y
90 5A 132 Z
91 5B 133 [
92 5C 134 \
93 5D 135 ]
94 5E 136 ^
95 5F 137 _
96 60 140 `
97 61 141 a
98 62 142 b
99 63 143 c
100 64 144 d
101 65 145 e
102 66 146 f
103 67 147 g
104 68 150 h
105 69 151 i
106 6A 152 j
107 6B 153 k
108 6C 154 l
109 6D 155 m
110 6E 156 n
111 6F 157 o
112 70 160 p
113 71 161 q
114 72 162 r
115 73 163 s
116 74 164 t
117 75 165 u
118 76 166 v
119 77 167 w
120 78 170 x
121 79 171 y
122 7A 172 z
123 7B 173 {
124 7C 174 |
125 7D 175 }
126 7E 176 ~
127 7F 177 DEL

Transportable tablespaces in Oracle 10g

2009-11-20T18:23:00.000+02:00

SYS@ORCL > exec dbms_tts.transport_set_check('TBS1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SYS@ORCL > select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object SYS_C007034 in tablespace TBS1 not allowed in pluggable set
Sys owned object DATAE in tablespace TBS1 not allowed in pluggable set
Sys owned object SYS_IL0000021148C00002$$ in tablespace TBS1 not allowed in plu
ggable set

Sys owned object SYS_LOB0000021148C00002$$ in tablespace TBS1 not allowed in pl
uggable set

-----------------------------------------------------------------------------------
[oracle@tom datafiles]$ expdp "'sys/oracle as sysdba'" transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:08:54

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29341: The transportable set is not self-contained

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

[oracle@tom datafiles]$ expdp system/oracle transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:16:42

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:18:21

Name (192.168.0.2:oracle): ftp
331 Password required for ftp.
Password:
230 User ftp logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> put tbs1_01.dbf
local: tbs1_01.dbf remote: tbs1_01.dbf
227 Entering Passive Mode (192,168,0,2,9,101).
150 Opening data connection for tbs1_01.dbf.
226 File received ok
10493952 bytes sent in 9.2 seconds (1.1e+03 Kbytes/s)

RMAN> convert datafile 'tbs1_01.dbf'
2> from platform='Linux IA (32-bit)'
3> db_file_name_convert="ts","win";

Starting backup at 12-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TBS1_01.DBF
converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCL_I-1121005791_TS-TBS1_FNO-7_
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 12-JUL-07

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:49:14

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user MICHAEL does not exist in the database

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:49:20

--------------------------------------------------------------------
SQL> create user michael identified by michael;

User created.
--------------------------------------------------------------------

C:\>create user michael identified by michael;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:50:07

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:50:12

-------------------------------------------------------------------------
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TBS1

SQL> alter tablespace tbs1 read write;
--------------------------------------------------------------------------