Hakan factor ORA-14642, ORA-14643

Hakan factor

The Hakan factor is used to unique map rows in a Bitmap Index to the rows in the base table. This factor is related to the max number of rows that can be stored in a single block of a table. Several factors like the column type and not null constraints influence the Hakan factor. The factor will also be recalculated when a table is modified until there is the first bitmap Index is created. Than the Hakan factor has to be protected for the existing bitmap indexes.

If a new table created to exchange data with the partitioned table, with a table layout, that include columns added after the bitmap index creation on the partitioned table, will most likely result in a different Hakan factor.

Errors

If the Hakan Factor do not match during exchange partitions, the prognosis is either of the error messages below:

  • ORA-14642: “Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
  • ORA-14643: “Hakan factor mismatch for tables in ALTER TABLE EXCHANGE PARTITION”

The error can appear only if you exchange a partition with bitmap indexes

Continue reading

Enterprise Manager Database Express Oracle Database 12C release 1 (12.1)

In previous release 11G to manage database you could use Oracle Enterprise Database Console. In Oracle 12C it is replaced by new lightweight administration tool Enterprise Manager Database Express.

Features of the new version:

  • out-of-box simple management for single database (or database cluster)
  • small footprint 50-100 MB in database
  • minimal requirements for CPU in database(sql calls), rendering is done on client side
  • simple configuration
  • includes
    • performance monitoring
    • configuration management
    • administration
    • diagnostic and tuning
  • interface in web browser(requires flash plugin)

Continue reading

Loading data in direct mode and unique index ORA-26026

If you want to load data in direct mode into a table you should set all dependent indexes in UNUSABLE state. Session parameter SKIP_UNUSABLE_INDEXES set to TRUE enables to ignore indexes with status UNUSABLE during direct load.

However If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

Continue reading

Install Suse Linux Enterprise Server 11 SP3

This article presents how to install Suse Linux Enterprise Server SP3 – service pack 3.

I assume you have already downloaded Suse Linux Enterprise Server SP3 64 bit(it’s about 4GB) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Suse openSuse 64 bit. 1GB ram(I set 4GB for future use) and 64G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.

Continue reading

PL/SQL Hierarchical Profiler Oracle Database 11G release 2 (11.2)

With 11g release 1 Oracle has introduced new package DBMS_HPROF. It’s very useful for DBAs and developers to analyze execution of PL/SQL code. It enables to gather, store and organize a lot of information about executed code.

Informations gathered by this package are supported by Oracle sqldeveloper which is another great news :).

The PL/SQL hierarchical profiler features:

  • easy to install and use
  • collect execution times for SQL and PL/SQL code
  • keeps results in database tables
  • Provides subprogram-level execution summary information, such as:
    • Number of calls to the subprogram
    • Time spent in the subprogram itself
    • Time spent in the subprogram itself and in its descendent’s subprograms
    • Detailed parent-children information, for example:
      • All callers of a given subprogram (parents)
      • All subprograms that a given subprogram called (children)
      • How much time was spent in subprogram x when called from y
      • How many calls to subprogram x were from y

Continue reading

Dynamic SQL changes in PL/SQL 11G – Oracle Database 11G release 2 (11.2)

New enhancements are added in 11g for dynamic SQL in PL/SQL :

  • SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
  • CLOB can be used for DBMS_SQL.PARSE
  • DBMS_SQL cursor can be converted to REF CURSOR and vice versa
  • Mored data types(including object types, collections) are supported in DBMS_SQL

Continue reading