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]
Wednesday, August 31, 2011
Friday, August 26, 2011
Bulk load insert - Example
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;
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;
Thursday, August 25, 2011
Database high availability best practices 10g-11g
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
Monday, August 22, 2011
Evaluating 1Z0-007: Introduction to Oracle9i SQL® Prepkits by ucertify
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
Saturday, August 20, 2011
Managing OCR and Voting disk in Oracle 10gR2 RAC Database
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
Thursday, August 4, 2011
Remove or delete virtual disk from Virtual box
Remove or delete virtual disk from Virtual box
C:\Program Files\Oracle\VirtualBox>VBoxManage closemedium disk F:\VBOX-DISKS\LINUX5.vdi
Monday, August 1, 2011
PL/SQL examples
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;
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;
end;
/
create or replace package vat
is
t1 number :=1;
t2 number :=1;
end;
/
create or replace package body vat
is
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;
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;
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;
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
is
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
is
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;
end;
/
EKTORAS> exec pkg_state.setA(10); --First call
a:10
b:
PL/SQL procedure successfully completed.
EKTORAS> exec pkg_state.print; --first call
a:
b:
PL/SQL procedure successfully completed.
create or replace package pkg_state
is
a number;
b number;
procedure print;
procedure setA(a1 number);
procedure setB(b1 number);
end;
/
create or replace package body pkg_state
is
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
is
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
is
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;
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
is
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
is
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
is
procedure findAllCustomers(customers OUT all_customer)
is
begin
open customers for select * from customer;
end;
procedure printCustomers
is
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;
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
is
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
is
procedure findAllCustomers(customers OUT all_customer)
is
begin
open customers for select * from customer;
end;
procedure printCustomers
is
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;
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
is
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
is
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
+-------------------------------------------------------------------------------------------+
| |
| 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
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;
--------------------------------------------------------------------------
Partitioning in 10g
+---------------------------------- |
| 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
------------------------------
P1
P2
P3
P4
P5
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
+-------------------------------------------------------------------------------------------+
| |
| 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/
| |
| 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/
Subscribe to:
Posts (Atom)