Change Tablespace and DataPump Utility in Oracle

Changing Tablespaces in Oracle database

Working on  the ERP project for our Client i found that all data was getting stored in the systems tablespace and because the client data was huge it was downgrading the performance of the system. It was something which i and my team at Adaptive IT would never let go. Even though the client was not aware of problem. 

We told the problem to client but client was reluctant to do that as they were more worried about their legacy data. We manage to convence the client  and changed the tablespace from SYSTEM to user specific. Client was happy to see the performance improvements. I share my experiences in conversion process

About System Tablespace in oracle database:

The SYSTEM tablespace contains the data dictionary tables for the entire database. All data stored on behalf of stored PL/SQL program units procedures, functions, packages, and triggers resides in the SYSTEM tablespace. Earlier to 9i the System table space use to be Dictionary Managed but later it come as Default to Locally managed ie managed by Oracle by itself are best to use. LMTs are faster in some operations, like allocating space, truncating tables, dropping objects.

 

Guidelines on using tablespace Use Multiple tablespaces

Using multiple tablespaces allows you more flexibility in performing database operations.

• Separate user data from data dictionary data to reduce I/O contention.
• Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
• Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
• Take individual tablespaces offline while others remain online, providing better overall availability and allows you to back up individual tablespaces.

You create datafile inside the tablespaces. These file remain open when the database is up, some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.

 

 How we changed the tablespaces? 

We used datapump utility for Oracle 11g. to change the tablespace. This is two step process which involves Datapump Export and import. While Importing the data it gives an option to REMAP- schema and tablespaces from source to destination.

 

Which parameter file my Oracle database using pifile or spfile

Some time you wonder why the start up parameter changes and not getting getting applied to my database.

Oracle database uses a parameter file when starting up. the parameter file could be spfile ( server parameter file or pfile. Normally pfile is used with STARTUP command to start the database. By default Oracle uses spfile to start the database.

sql> select value from sys.v$parameter where name = 'spfile'

sql> STARTUP pfile='C:\app\vikas\admin\orcl\pfile\initORCL.ora'

The spfile as the name suggest is the parameter file maintain by the oracle database server and should not be modified. any manual modification can corrupt it and database may not start further.

Modifying spfile
The spfile are modified by using ALTER Command.
ALTER system SET <parameter_name=value> SCOPE=MEMORY for eg.
ALTER SYSTEM SET open_cursor=400 SCOPE=spfile 

The other option for SCOPE are MEMORY (default) or FILE or BOTH

Converting SPFILE and PFILE.

SPFILE and PFILE are convertible. You can create any of it using other. use to following syntex
SQL> CREATE PFILE FROM SPFILE; 
SQL> CREATE SPFILE FROM PFILE;

Merge Operation In Oracle - ORA-30926

I had a situation to update or insert a table on the basis of source table. The Source and the destination tables were same except that the source table had some extra columns to indicate the operation type Insert update or delete to perform when inserting or updating the data.

I thought of using MERGE which is one that fastest way of doing it. After spending a few hours in came to now that i cannot use MERGE. As my source table can have one or more entries for pk_row. Which merge cannot handle and give  ORA-30926: unable to get a stable set of rows in the source tables

Actually i thought merge statement is some thing like

Select source table....

update destination table....

if sql%rowcount 0 then
    insert ....
end if;

But actually it is

for x in ( select * from source )

loop
    if record exists
    then
        if record hasn't been modified by us yet
        then
              update it
        else
              fail
        end if
    else
        insert it
    end if
end loop

Resizing of Virtualbox disk

Resizing of Virtualbox disk

civiCRM

CIVICRM, A great product that i have come across for Not for Profit organizations other civic sector organizations to manage their Contacts relationship management need that comprise of Membership, Events, Contributions, Case managements.

I started working with this product since 2009, i have handled number of CIVICRM implementation and customization projects with my team at Adaptive IT. My team love working on this product they learn lot from a well structured code of CIVICRM which also required good and experienced programing skills to understand it.

Every CIVICRM is project is a challenge for us. Although CIVICRM takes care of most of the requirement of our customers but we also come across with customer specific requirements. I always prefer to customize it in such a way so that future upgrades are not the problem to our customer in future.