Friday, November 19, 2010

Another example to prove using index, rather than full table scan to delete large amount of data

The envrionment is Redhat 5.3 + Oracle 10.2.0.4 (x64) + Dell PowerEdge 2950 + EMC Clarrion

Click here to refer to original test case on PC Server: http://www.dbaglobe.com/2010/11/deleting-large-amount-of-data-using.html


SQL> create table large1
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> create table large2
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> set timing on
SQL> begin
2 for i in 1..4000000
3 loop
4 insert into large1 values (i,dbms_random.string('X',20),' ');
5 insert into large2 values (i,dbms_random.string('X',20),' ');
6 if mod(i,1000)=0 then
7 commit;
8 end if;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:08:43.77
SQL> create unique index large1_pk on large1(id);

Index created.

Elapsed: 00:00:43.64
SQL> create unique index large2_pk on large2(id);

Index created.

Elapsed: 00:00:43.66
SQL> create index large1_n1 on large1(char20);

Index created.

Elapsed: 00:00:43.47
SQL> create index large2_n1 on large2(char20);

Index created.

Elapsed: 00:01:00.06
SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

Elapsed: 00:03:25.59
SQL> set timing off
SQL> col segsize format 999,999,999,999
SQL> col segment_name for a30
SQL> select segment_name,sum(bytes) segsize from user_extents
2 where segment_name like 'LARGE%'
3 group by segment_name
4 order by segment_name;

SEGMENT_NAME SEGSIZE
------------------------------ ----------------
LARGE1 973,078,528
LARGE1_N1 149,946,368
LARGE1_PK 75,497,472
LARGE2 973,078,528
LARGE2_N1 149,946,368
LARGE2_PK 75,497,472

6 rows selected.

SQL> explain plan for
2 delete from large1 where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 2256 (1)| 00:00:28 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 1000K| 25M| 2256 (1)| 00:00:28 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"<1000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 25917 (1)| 00:05:12 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 999K| 25M| 25917 (1)| 00:05:12 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"<1000000)

14 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> delete from large1 where id<1000000;

999999 rows deleted.

Elapsed: 00:03:05.79
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> delete /*+ full(t) */ from large2 t where id<1000000;

999999 rows deleted.

Elapsed: 00:05:15.56
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Deleting large amount of data using index or full table scan?

Setup:

[11gr2@rh5 ~]$ uname -snrio
Linux rh5.lab.dbaglobe.com 2.6.18-194.26.1.el5 i386 GNU/LinuxTable created.


SQL> create table large1
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> create table large2
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> begin
2 for i in 1..4000000
3 loop
4 insert into large1 values (i,dbms_random.string('X',20),' ');
5 insert into large2 values (i,dbms_random.string('X',20),' ');
6 if mod(i,1000)=0 then
7 commit;
8 end if;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> create unique index large1_pk on large1(id);

Index created.

SQL> create unique index large2_pk on large2(id);

Index created.

SQL> create index large1_n1 on large1(char20);

Index created.

SQL> create index large2_n1 on large2(char20);

Index created.

SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

SQL> col segsize format 999,999,999,999
SQL> col segment_name for a30

SQL> select segment_name,sum(bytes) segsize from user_extents
2 where segment_name like 'LARGE%'
3 group by segment_name
4 order by segment_name;

SEGMENT_NAME SEGSIZE
------------------------------ ----------------
LARGE1 964,689,920
LARGE1_N1 150,994,944
LARGE1_PK 75,497,472
LARGE2 964,689,920
LARGE2_N1 150,994,944
LARGE2_PK 75,497,472

6 rows selected.


Round 1:

SQL> explain plan for
2 delete from large1 where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 2230 (1)| 00:00:27 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 999K| 25M| 2230 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("ID"<1000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 31892 (1)| 00:06:23 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 999K| 25M| 31892 (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("ID"<1000000)

14 rows selected.

SQL> set timing on
SQL> delete from large1 where id<1000000;

999999 rows deleted.

Elapsed: 00:03:23.35
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<1000000;

999999 rows deleted.

Elapsed: 00:40:21.74

SQL> commit;

Commit complete.


Round 2:

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:29.88
SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.85

SQL> set timing off

SQL> explain plan for
2 delete from large1 where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 2249 (1)| 00:00:27 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 1000K| 25M| 2249 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("ID"<2000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 31874 (1)| 00:06:23 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 1000K| 25M| 31874 (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("ID"<2000000)

14 rows selected.

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

SQL> delete from large1 where id<2000000;

1000000 rows deleted.

Elapsed: 00:03:35.38
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<2000000;

1000000 rows deleted.

Elapsed: 00:45:13.68
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

SQL> select /*+ full(t) */ count(*) from large2 t;

COUNT(*)
----------
1979001

Elapsed: 00:00:14.37


Round 3: table without index

SQL> drop index large2_pk;

Index dropped.

Elapsed: 00:00:00.86

SQL> drop index large2_n1;

Index dropped.

Elapsed: 00:00:00.36

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.25

SQL> delete /*+ full(t) */ from large2 t where id<3000000;

1000000 rows deleted.

Elapsed: 00:02:50.25

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Wednesday, November 17, 2010

Loopback database link & ORA-02082: a loopback database link must have a connection qualifier


SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE



SQL> create database link orcl.local connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select * from dual@orcl.local;
select * from dual@orcl.local
*
ERROR at line 1:
ORA-02085: database link ORCL.LOCAL connects to ORCL.LAB.DBAGLOBE.COM


SQL> drop database link orcl.local;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl';
create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier



SQL> create database link orcl.lab.dbaglobe.com@loopback
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@loopback;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@loopback;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com@l
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@l;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@l
2 ;

Database link dropped.

Tuesday, November 16, 2010

Instance Caging in Oracle database

About Instance Caging

You might decide to run multiple Oracle database instances on a single multi-CPU server. A typical reason to do so would be server consolidation—using available hardware resources more efficiently. When running multiple instances on a single server, the instances compete for CPU. One resource-intensive database instance could significantly degrade the performance of the other instances. For example, on a 16-CPU system with four database instances, the operating system might be running one database instance on the majority of the CPUs during a period of heavy load for that instance. This could degrade performance in the other three instances. CPU allocation decisions such as this are made solely by the operating system; the user generally has no control over them.

A simple way to limit CPU consumption for each database instance is to use instance caging. Instance caging is a method that uses an initialization parameter to limit the number of CPUs that an instance can use simultaneously. In the previous example, if you use instance caging to limit the number of CPUs to four for each of the four instances, there is less likelihood that one instance can interfere with the others. When constrained to four CPUs, an instance might become CPU-bound. This is when the Resource Manager begins to do its work to allocate CPU among the various database sessions according to the resource plan that you set for the instance. Thus, instance caging and the Resource Manager together provide a simple, effective way to manage multiple instances on a single server.

Examples:



Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn donghua/donghua
Connected.
SQL> set echo on
SQL> @resource_plan.sql
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3
4 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
5 PLAN => 'MAXCAP_PLAN',
6 COMMENT => 'Limit overall database CPU');
7
8 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
9 PLAN => 'MAXCAP_PLAN',
10 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
11 COMMENT => 'This group is mandatory',
12 MAX_UTILIZATION_LIMIT => 90);
13
14 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
15 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
16 END;
17 /

PL/SQL procedure successfully completed.

SQL> alter system set resource_manager_plan='';

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
INTERNAL_PLAN OFF OFF

SQL> alter system set resource_manager_plan=MAXCAP_PLAN;

System altered.

SQL> alter system set cpu_count=2;

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
MAXCAP_PLAN ON ON

Monday, November 15, 2010

ORA-01502, constraint, unique index and skip_unusable_indexes=true

This example shows the difference between unique indexes and non-unique indexes used to enforce primary key/unique constraints.

beside this, the only way to enable primary key/unique constraint to deferred is using non-unique index.


SQL> define _editor=vi
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users),
6 constraint emp_unique_ssn unique(ssn) using index
7 (create unique index emp_u1 on emp(ssn) tablespace users)
8* )
9 /

Table created.

SQL> alter index emp_pk unusable;

Index altered.

SQL>
SQL>

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL>
SQL> show parameter index

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_use_invisible_indexes boolean FALSE
skip_unusable_indexes boolean TRUE
SQL> drop index emp_pk;

Index dropped.

SQL> create index emp_pk on emp(empno) unusable;

Index created.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SSQL> commit;

Commit complete.

SQL> alter table emp enable constraint emp_pk;
alter table emp enable constraint emp_pk
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key


SQL> alter index emp_pk rebuild;

Index altered.

SQL> alter table emp enable constraint emp_pk;

Table altered.

SQL> truncate table emp;

Table truncated.

SQL> alter index emp_pk unusable;

Index altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SQL>

Examples: Specifying the index associated with a constraint


donghuas-MacBook:~ donghua$ sqlplus donghua/donghua@orcl @create_emp1

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 15 22:37:46 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> create table emp (
2 empno number(5) primary key,
3 ssn number(5))
4 enable primary key using index (create unique index emp_pk on emp(empno) tablespace users)
5 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK SYS_C0012761

SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5) primary key,
3 ssn number(5))
4 enable primary key using index tablespace users
5 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
SYS_C0012762 SYS_C0012762

SQL>
SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno)
5 )
6 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users)
6 )
7 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users),
6 constraint emp_unique_ssn unique(ssn) using index
7 (create unique index emp_u1 on emp(ssn) tablespace users)
8 )
9 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK
EMP_U1 EMP_UNIQUE_SSN

SQL> drop table emp purge;

Table dropped.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sunday, November 14, 2010

Deferred segment creation and dbms_space_admin.materialize_deferred_segments


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
SQL> show parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE




SQL> create table t1 (id number);

Table created.

SQL> create table t2 (id number);

Table created.

SQL> create index t1_pk on t1(id);

Index created.
SQL> create table t3 (doctype clob) lob(doctype) store as securefile;

Table created.

SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 NO
T2 NO
T3 NO

SQL> select segment_name from user_segments;

no rows selected





SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /
dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

SQL> conn / as sysdba
Connected.
SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /

PL/SQL procedure successfully completed.




SQL> conn donghua/donghua
Connected.
SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 YES
T2 YES
T3 YES

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T1
T2
T3
T1_PK
SYS_IL0000021560C00001$$
SYS_LOB0000021560C00001$$

6 rows selected.

Oracle compression table restrictions


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production



SQL> create table compress_table (id number, name varchar2(30)) compress basic;

Table created.

SQL> insert into compress_table select object_id,object_name from dba_objects;

17392 rows created.

SQL> commit;

Commit complete.
SQL> select table_name,pct_free from user_tables
2 order by pct_free;

TABLE_NAME PCT_FREE
------------------------------ ----------
COMPRESS_TABLE 0
DEPARTMENTS 10
TEST_CHAINED_TABLE 10
CHAINED_ROWS 10
T1 10
EMPLOYEES 10



SQL> alter table compress_table add (col3 varchar2(20));

Table altered.

SQL> alter table compress_table add (col4 varchar2(20) default 'col4');
alter table compress_table add (col4 varchar2(20) default 'col4')
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table nocompress;

Table altered.

SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables



SQL> alter table compress_table shrink space;
alter table compress_table shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

Claim freespace using shrink and shrink space compact



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


SQL> insert into test_chained_table
2 select object_id, rpad(object_name,1000,' ') from dba_objects;

17392 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL>


SQL> set serveroutput on
SQL> @show_space_table.sql
SQL> DECLARE
2 p_segname VARCHAR2 (30);
3 p_owner VARCHAR2 (30) DEFAULT USER;
4 p_type VARCHAR2 (30) DEFAULT 'TABLE';
5 p_partition VARCHAR2 (30) DEFAULT NULL;
6 l_free_blks NUMBER;
7 l_total_blocks NUMBER;
8 l_total_bytes NUMBER;
9 l_unused_blocks NUMBER;
10 l_unused_bytes NUMBER;
11 l_lastusedextfileid NUMBER;
12 l_lastusedextblockid NUMBER;
13 l_last_used_block NUMBER;
14 l_segment_space_mgmt VARCHAR2 (255);
15 l_unformatted_blocks NUMBER;
16 l_unformatted_bytes NUMBER;
17 l_fs1_blocks NUMBER;
18 l_fs1_bytes NUMBER;
19 l_fs2_blocks NUMBER;
20 l_fs2_bytes NUMBER;
21 l_fs3_blocks NUMBER;
22 l_fs3_bytes NUMBER;
23 l_fs4_blocks NUMBER;
24 l_fs4_bytes NUMBER;
25 l_full_blocks NUMBER;
26 l_full_bytes NUMBER;
27
28 PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER)
29 IS
30 BEGIN
31 DBMS_OUTPUT.put_line ( RPAD (p_label, 40, '.')
32 || TO_CHAR (p_num, '999,999,999,999')
33 );
34 END;
35 BEGIN
36 p_segname := 'TEST_CHAINED_TABLE';
37 p_owner := 'DONGHUA';
38 p_type := 'TABLE';
39
40 IF p_partition IS NOT NULL
41 THEN
42 SELECT ts.segment_space_management
43 INTO l_segment_space_mgmt
44 FROM dba_segments seg, dba_tablespaces ts
45 WHERE seg.segment_name = p_segname
46 AND seg.partition_name = p_partition
47 AND seg.owner = p_owner
48 AND seg.tablespace_name = ts.tablespace_name;
49 ELSE
50 SELECT ts.segment_space_management
51 INTO l_segment_space_mgmt
52 FROM dba_segments seg, dba_tablespaces ts
53 WHERE seg.segment_name = p_segname
54 AND seg.owner = p_owner
55 AND seg.tablespace_name = ts.tablespace_name;
56 END IF;
57
58 IF l_segment_space_mgmt = 'AUTO'
59 THEN
60 DBMS_SPACE.space_usage (p_owner,
61 p_segname,
62 p_type,
63 l_unformatted_blocks,
64 l_unformatted_bytes,
65 l_fs1_blocks,
66 l_fs1_bytes,
67 l_fs2_blocks,
68 l_fs2_bytes,
69 l_fs3_blocks,
70 l_fs3_bytes,
71 l_fs4_blocks,
72 l_fs4_bytes,
73 l_full_blocks,
74 l_full_bytes,
75 p_partition
76 );
77 --
78 p ('Unformatted Blocks ', l_unformatted_blocks);
79 p ('FS1 Blocks (0-25) ', l_fs1_blocks);
80 p ('FS2 Blocks (25-50) ', l_fs2_blocks);
81 p ('FS3 Blocks (50-75) ', l_fs3_blocks);
82 p ('FS4 Blocks (75-100)', l_fs4_blocks);
83 p ('Full Blocks ', l_full_blocks);
84 ELSE
85 DBMS_SPACE.free_blocks (segment_owner => p_owner,
86 segment_name => p_segname,
87 segment_type => p_type,
88 freelist_group_id => 0,
89 free_blks => l_free_blks,
90 partition_name => p_partition
91 );
92 --
93 p ('Free Blocks', l_free_blks);
94 END IF;
95
96 DBMS_SPACE.unused_space (segment_owner => p_owner,
97 segment_name => p_segname,
98 segment_type => p_type,
99 partition_name => p_partition,
100 total_blocks => l_total_blocks,
101 total_bytes => l_total_bytes,
102 unused_blocks => l_unused_blocks,
103 unused_bytes => l_unused_bytes,
104 last_used_extent_file_id => l_lastusedextfileid,
105 last_used_extent_block_id => l_lastusedextblockid,
106 last_used_block => l_last_used_block
107 );
108 p ('Total Blocks', l_total_blocks);
109 p ('Total Bytes', l_total_bytes);
110 p ('Total MBytes', TRUNC (l_total_bytes / 1024 / 1024));
111 p ('Unused Blocks', l_unused_blocks);
112 p ('Unused Bytes', l_unused_bytes);
113 p ('Last Used Ext FileId', l_lastusedextfileid);
114 p ('Last Used Ext BlockId', l_lastusedextblockid);
115 p ('Last Used Block', l_last_used_block);
116 END;
117 /
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 60
Full Blocks ..................... 8,267
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL>


SQL> delete from test_chained_table where mod(id,3)=0;

11589 rows deleted.

SQL> commit;

Commit complete.

SQL> set echo off
SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 7,742
FS3 Blocks (50-75) ..................... 254
FS4 Blocks (75-100)..................... 62
Full Blocks ..................... 271
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL> alter table test_chained_table shrink space compact;
alter table test_chained_table shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table test_chained_table enable row movement;

Table altered.

SQL> alter table test_chained_table shrink space compact;

Table altered.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 3,169
Full Blocks ..................... 5,159
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL> alter table test_chained_table shrink space;

Table altered.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
43057152

SQL> @show_space_table.sql
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 5,159
Total Blocks............................ 5,256
Total Bytes............................. 43,057,152
Total MBytes............................ 41
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 11,648
Last Used Block......................... 4

PL/SQL procedure successfully completed.

SQL> alter table test_chained_table disable row movement;

Table altered.

SQL>

Sunday, November 7, 2010

Use SQL to check datafile highwater mark for resizing


select t.file_id,t.file_name,
t.tablespace_name,
round(t.bytes/1024/1024) mbytes,
autoextensible,
round(hw_mark/1024/1024) hw_mark,
round((t.bytes-hw_mark)/1024/1024) resizable,
round((f.free_bytes)/1024/1024) free_mbytes
from dba_data_files t,
(select file_id, max(block_id+blocks)*8192
hw_mark from dba_extents group by file_id) w,
(select file_id,sum(bytes) free_bytes
from dba_free_space group by file_id) f
where t.file_id=w.file_id
and t.file_id=f.file_id(+)

How to fix "ORA-08104 : this index object 75350 is being online built or rebuilt"


$ sqlplus donghua2/donghua2

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 18:40:36 2010

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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01;
alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt




Errors in file /u01/app/oracle/admin/appprd/udump/appprd1_ora_25717.trc:
ORA-00600: internal error code, arguments: [kdtdelrow-2], [6], [6], [], [], [], [], []
ORA-01502: index 'APP_OWNER.SYS_C007740' or partition of such index is in unusable state



SQL> conn / as sysdba
Connected.
SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563



SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.


SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2051


SQL> conn donghua2/donghua2
Connected.
SQL> alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01;

delete from large tables using loop+commit


-- delete_large_table.sql
set serveroutput on

-- this script will not use the index on where clause,
-- it will fullscan from the beginning, or add additional rowid
-- rownum does notw work well with index to filter large amount data
declare
-- commit every 'i_commit' rows
i_commit pls_integer := 1000;
-- row counter, 'i_rowcount' rows deleted
i_rowcount pls_integer := 0;

begin
-- define 'infinite' loop
loop
-- delete 'i_commit' rows
delete from sec_owner.large_table
where requesttime< '2010-10-01 01' and rownum <= i_commit;
i_rowcount := i_rowcount + sql%rowcount;
-- now it's time to exit the loop
dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows');
if sql%rowcount = 0 then
commit;
exit;
end if;
commit;
end loop;
-- feedback
dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) || ' rows deleted') ;
dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows (done)');
end;
/





SQL> select action from v$session where username='NCSDBA1' and action is not null;

ACTION
--------------------------------
deleted 130000 rows




SQL> select s.*, n.name
2 from v$sesstat s ,v$statname n
3 where s.statistic# = n.statistic#
4 and n.name = 'user commits'
5* and s.sid=513
SQL> /

SID STATISTIC# VALUE
---------- ---------- ----------
NAME
----------------------------------------------------------------
513 4 130
user commits


SQL>

Add/remove disks to ASM Diskgroup


[11gr2@rh5 ~]$ sqlplus '/ as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 7 20:35:35 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> alter diskgroup data add disk '/dev/hda2','/dev/hda3';

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 1 1 33 1567 246
6


SQL> alter diskgroup data drop disk DATA_0006,DATA_0001;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 1 1 1 2399 0
0


SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 1 1 1 2399 0
0


SQL> alter diskgroup data rebalance power 11;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 11 11 0 1889 0
0


SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 11 11 0 1889 0
0


SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 11 11 435 1687 343
3


SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED NORMAL 5383 641 0
4742 856 -107 0
11.2.0.0.0
10.1.0.0.0 N


SQL> /

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED NORMAL 5383 641 0
4742 856 -107 0
11.2.0.0.0
10.1.0.0.0 N


SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
1 REBAL RUN 11 11 1595 1819 344
0


SQL> select name,path,state from v$asm_disk;
.

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
STATE
--------

/dev/hda8
NORMAL


/dev/hda9
NORMAL


/dev/hda12
NORMAL


/dev/hda13
NORMAL

DATA_0000
/dev/hda1
NORMAL

DATA_0002
/dev/hda2
NORMAL

DATA_0007
/dev/hda3
NORMAL

DATA_0003
/dev/hda5
NORMAL

DATA_0004
/dev/hda6
NORMAL

DATA_0005
/dev/hda7
NORMAL

DATA_0006
/dev/hda10
DROPPING

DATA_0001
/dev/hda11
DROPPING


12 rows selected.

SQL> SQL> col path for a30
SQL> l
1* select name,path,state from v$asm_disk
SQL> /

NAME PATH STATE
------------------------------ ------------------------------ --------
/dev/hda10 NORMAL
/dev/hda11 NORMAL
/dev/hda8 NORMAL
/dev/hda9 NORMAL
/dev/hda12 NORMAL
/dev/hda13 NORMAL
DATA_0000 /dev/hda1 NORMAL
DATA_0002 /dev/hda2 NORMAL
DATA_0007 /dev/hda3 NORMAL
DATA_0003 /dev/hda5 NORMAL
DATA_0004 /dev/hda6 NORMAL
DATA_0005 /dev/hda7 NORMAL

12 rows selected.

[11gr2@rh5 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 5383 645 857 -106 0 N DATA/
ASMCMD [+] > lsdsk
Path
/dev/hda1
/dev/hda2
/dev/hda3
/dev/hda5
/dev/hda6
/dev/hda7
ASMCMD [+] > lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Library Label UDID Product Redund Path
503 26 503 DATA_0000 DATA_0000 System UNKNOWN /dev/hda1
976 129 976 DATA_0002 DATA_0002 System UNKNOWN /dev/hda2
976 130 976 DATA_0007 DATA_0007 System UNKNOWN /dev/hda3
976 119 976 DATA_0003 DATA_0003 System UNKNOWN /dev/hda5
976 119 976 DATA_0004 DATA_0004 System UNKNOWN /dev/hda6
976 122 976 DATA_0005 DATA_0005 System UNKNOWN /dev/hda7

ASMCMD [+] > lsdsk -p
Group_Num Disk_Num Incarn Mount_Stat Header_Stat Mode_Stat State Path
1 0 3915928005 CACHED MEMBER ONLINE NORMAL /dev/hda1
1 2 3915928015 CACHED MEMBER ONLINE NORMAL /dev/hda2
1 7 3915928016 CACHED MEMBER ONLINE NORMAL /dev/hda3
1 3 3915928008 CACHED MEMBER ONLINE NORMAL /dev/hda5
1 4 3915928009 CACHED MEMBER ONLINE NORMAL /dev/hda6
1 5 3915928010 CACHED MEMBER ONLINE NORMAL /dev/hda7

ASMCMD [+] > lsdsk --candidate -p
Group_Num Disk_Num Incarn Mount_Stat Header_Stat Mode_Stat State Path
0 0 3915928017 CLOSED FORMER ONLINE NORMAL /dev/hda10
0 1 3915928018 CLOSED FORMER ONLINE NORMAL /dev/hda11
0 10 3915928003 CLOSED CANDIDATE ONLINE NORMAL /dev/hda12
0 11 3915928004 CLOSED CANDIDATE ONLINE NORMAL /dev/hda13
0 6 3915927999 CLOSED CANDIDATE ONLINE NORMAL /dev/hda8
0 7 3915928000 CLOSED CANDIDATE ONLINE NORMAL /dev/hda9
ASMCMD [+] > lsdsk --statistics
Reads Write Read_Errs Write_Errs Read_time Write_Time Bytes_Read Bytes_Written Voting_File Path
1416 3504 0 0 305.382626 429.681339 425697280 656793600 N /dev/hda1
77 1039 0 0 32.462891 652.342351 48402432 900378624 N /dev/hda2
54 1228 0 0 22.873951 624.192306 33648640 892911616 N /dev/hda3
2480 3350 0 0 492.988123 774.127265 685920256 958545920 N /dev/hda5
2642 4277 0 0 422.318022 809.221004 656359424 968208384 N /dev/hda6
2528 3378 0 0 450.579072 868.434136 636690432 1029386240 N /dev/hda7

sysdba VS sysasm in 11gR2


[11gr2@rh5 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 7 20:35:17 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> alter diskgroup DATA rebalance power 0;
alter diskgroup DATA rebalance power 0
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group


SQL> alter diskgroup data add disk '/dev/hda10';
alter diskgroup data add disk '/dev/hda10'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option
[11gr2@rh5 ~]$ sqlplus '/ as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 7 20:35:35 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Automatic Storage Management option

SQL> alter diskgroup DATA rebalance power 0;

Diskgroup altered.

SQL>
SQL> alter diskgroup data add disk '/dev/hda10';

Diskgroup altered.

Saturday, November 6, 2010

What happens if db_recovery_file_dest_size too smaller to cater for db_flashback_retention_target?

Assumation here is the archivelog is not stored in the flash_recovery_area.

if the db_recovery_file_dest_size is too smaller, Oracle does not hang, it will purge old flashback logs to ensure the transaction can continue to run.
But the performance will be degraded due to oracle keeping to housekeep old flashback logs.

Test Envrionment: Oracle 10.2.0.4 on Linux x86.
Test script: (to insert 4029000 rows)
[10gr2@rh5 adhoc]$ cat perform_flashback_test.sh
sqlplus donghua/donghua <select sysdate from dual;
declare
cursor c is select object_id,owner,object_name,object_type from dba_objects;
begin
for i in 1..100 loop
for c1 in c loop
insert into fbtable values(c1.object_id,c1.owner,c1.object_name,c1.object_type);
commit;
end loop;
end loop;
end;
/
select sysdate from dual;
exit
EOD



Test 1:
DB_RECOVERY_FILE_DEST_SIZE=10GB.
The script toke 00:17:34 to complete.
3,949 transactions per seconds.

Test 2:
DB_RECOVERY_FILE_DEST_SIZE=100MB.
The script toke 00:24:29 to complete.
2,712 transactions per seconds.

Test 3:
FLASHBACK DATABSE OFF
The script toke 00:11:44 to complete.