Category: Oracle

How to Understand and Use ESTIMATE and ESTIMATE_ONLY Parameters in DataPump

Be aware LOB segment size is not included in the estimation size of datapump.

ESTIMATE Parameter

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS.

BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of:

  • The table was created with a much bigger initial extent size than was needed for the actual table data
  • Many rows have been deleted from the table, or a very small percentage of each block is used.

STATISTICS: The estimated space used is calculated by using statistics for each table. If the table has been recently analyzed, the “estimate=statistics” would be the most accurate.

ESTIMATE_ONLY Parameter

Using estimate_only parameter will not generate the dump file other than a logfile .

The value for this parameter is either Y (yes) or N (no = default).

Y: Export estimates the space that would be consumed, but quits without actually performing the export operation.
N: Export does not only estimate, it performs an actual export of data, too.

BLOB EXCLUDED FROM ESTIMATION

If a table has LOBs, ESTIMATE dump file size does NOT take LOB size into consideration.

The table segment size is 34.43GB, and the blog segment size is 38.3GB. It seems the dadapump estimation size excludes blob segment size.

SQL> select bytes/1024/1024/1024 
       from dba_segments 
       where owner='TESTUSER' 
         and segment_name='TEST' 
         and segment_type='TABLE';

BYTES/1024/1024/1024
--------------------
 34.4365234

SQL> desc TESTUSER.TEST
 Name    Null?     Type
 ------- -------  ----------
 NAME    NOT NULL VARCHAR2(30)
 CREATED NOT NULL DATE
 ......
 ......
 DOC              BLOB
 ......
 ......


SQL> select owner,table_name,column_name,segment_name 
       from dba_lobs 
      where owner='TESTUSER' and table_name='TEST';

OWNER      TABLE_NAME  COLUMN_NAME       SEGMENT_NAME 
---------- ----------  ----------------  ------------------------
TESTUSER   TEST        SESSIONITEMLONG   SYS_LOB0000062845C00010$

SQL> select owner, segment_Name,bytes/1024/1024/1024 
       from dba_segments 
      where segment_name='SYS_LOB0000062845C00010$' 
        and owner='TESTUSER';

OWNER       SEGMENT_NAME             BYTES/1024/1024/1024
----------- ------------------------ ------------------
TESTUSER    SYS_LOB0000062845C00010$  38.3


$ expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=BLOCKS tables=TESTUSER.TEST;

....
....
....
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 35.50 GB
Total estimation using BLOCKS method: 35.50 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:50:11

$expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=STATISTICS tables=TESTUSER.TEST;
......
......
......
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 28.96 GB
Total estimation using STATISTICS method: 28.96 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:51:31

From the above test, we can see LOB segment size is excluded from estimation size.

How to Tell If Oracle Database is Standard Edition or Enterprise Edition

Standard Edition and Enterprise Edition License.

Standard Edition

$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 14:13:15 2016

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

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

OR

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Enterprise Edition

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 3 20:16:33 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

OR

SQL> select * from v$version;

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

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

For 8k block size tablespace, maximum cluster chain block count is 65534. Otherwise bigger block size tablespace is recommended.

This article demonstrates how to reproduce  ORA-02475  error , and explains why it happens and how to resolve this issue.

  1. Create cluster tables and indexes.
SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3)) TABLESPACE users;

Cluster created.

SQL> CREATE TABLE dept 
     (
        deptno NUMBER(3) PRIMARY KEY,
        deptname VARCHAR2(15) NOT NULL
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE TABLE emp 
     (
        empno NUMBER(10) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
       deptno NUMBER(3) REFERENCES dept
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE INDEX emp_dept_index
         ON CLUSTER emp_dept
         TABLESPACE users ;

Index created.

SQL> insert into dept values ( 1,'DEPT1 1');

1 row created.

SQL> insert into dept values (2,'DEPT1 2');

1 row created.

SQL> insert into dept values (3, 'DEPT1 3');

1 row created.

SQL> commit;

Commit complete.

2.  Insert records into cluster table EMP until gets ORA-02475 error.

SQL> set serveroutput on;
begin
    for i in 1 .. 1000000000
    loop
       begin
           insert into emp values (i,'emp '||i, 3);
           commit;
       exception
       when others
       then
           dbms_output.put_line( SQLERRM );
          exit;
       end;
   end loop;
end;
/

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

PL/SQL procedure successfully completed.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Step by Step Installing Oracle 12c GI and RAC on Linux 7 Using VirtualBox

Great start to build your own RAC with detailed instructions and explanations. For building 18c/19c/20c GI/RAC, this post is still helpful. After completing this lab, you should be in better position as an Oracle RAC DBA.

Contents

Introduction

This article describes the detailed step by step installation of Oracle Database 12c  (12.1.0.2 64-bit) GI and RAC on Linux (Oracle Linux 7.1 64-bit) using VirtualBox (5.0.10). It applies to Oracle 18c and 19c GI and RAC as well.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ASM rebalance ASM_POWER_LIMIT parameter

Parameter ASM_POWER_LIMIT will affect the speed of rebalancing disks, and the resources consumed.

If COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher, then asm_power_limit value range is 0 to 1024.

If COMPATIBLE.ASM disk group attribute is set to lower than 11.2.0.2 , then asm_power_limit value range will be 0 to 11.

We can change the rebalance power either at the ASM instance level or at the individual disk group level.

Changing  the value at the instance level, this will alter the power limit for any new operations, but not the existing ones.

SQL > alter system set asm_power_limit=5 scope=both;

Change the value at the disk group level, this will change an existing operation on the diskgroup involved in the current operation.

SQL> alter diskgroup diskgroup1 rebalance power 8;

The effect of the change can be viewed from v$asm_operation:

SQL> select * from gv$asm_operation;