This short article will focus on simple hints/list to help you make decision how to manipulate large data in Oracle for both DML and DDL operations using parallel option.
Effective parallel execution for DML, DDL in Oracle
Reply
Install Oracle 12C Release 1 (12.1) on Oracle Linux 7 (OEL7)
This article presents how to install Oracle 12C(12.1) Release 1 on Oracle Enterprise Linux 7 (OEL7).
Read following article how to install Oracle Enterprise Linux 7: Install Oracle Linux 7 (OEL7) (for comfort set 4G memory for your virtual machine before proceeding with Oracle software installation).
Software
Software for 12CR1 is available on OTN or edelivery
- OTN: Oracle Database 12c Release 1 (12.1.0.2) Software (64-bit).
- edelivery: Oracle Database 12c Release 1 (12.1.0.2) Software (64-bit)
Database software
linuxamd64_12102_database_1of2.zip linuxamd64_12102_database_2of2.zip
Install Oracle 11G Release 2 (11.2) on Oracle Linux 7 (OEL7)
This article presents how to install Oracle 11G on Oracle Enterprise Linux 7 (OEL7).
Read following article how to install Oracle Enterprise Linux 7: Install Oracle Linux 7 (OEL7) (for comfort set 2G memory for your virtual machine before proceeding with Oracle software installation).
Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.
Oracle software which I verified
release 11.2.0.4
p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip
Install Oracle Linux 7 (OEL7)
This article presents how to install Oracle Enterprise Linux 7.
I assume you have already downloaded Oracle Enterprise Linux 7 64 bit(about 4 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Oracle Linux 64 bit. 2GB ram (I specified 4GB for future Oracle database software installation) and 64G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.
Statistics improvements Oracle Database 11G release 2 (11.2)
New interesting features are available in 11G related to statistics collection
- New statistics preferences
- PUBLISH
- INCREMENTAL
- STALE_PERCENT
- Hash based sampling for column statistics
- Multicolumn statistics
- Expression statistics
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
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)
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.
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.
How to trace session – event sql_trace
In case you want to collect trace statistics you can do it in many ways in Oracle. One of the most popular method is by setting parameter event with value sql_trace on session level.
alter session set events 'sql_trace level <trace_level>'