Export/Import Data pump – Killing Job

Do you know the fact that when you kill the data pump job in Oracle 10g (using KILL_JOB), it leaves the dump file created thus far . In order to re-execute the job, either you have to delete the dump file or use the set REUSE_DUMPFILES=Y. Some time you also need to change the JOB_NAME as master table is left behind OR table needs to be dropped.

But in 11g, it is improved so killing Data pump jobs cleans up – partially created dump files, Master table. It is very useful as we don’t have to perform manual steps in order to re-execute same data pump.

Read Full Post »

Oracle 11g Memory Advisor

One of the issues that Database DBA face is oracle database memory set correctly or do I need to increase it.

Oracle 11g has cool solution.

Querying from table: v$memory_target_advice , tells if increasing memory size would provide any advantage, if so how much should you increase.

Here is the example:

Review the results in the screen shot. Currently database has a memory_target of 2048 MB (First line shows this).It can be compared with DB initialization parameter MEMORY_TARGET.

Line 2 shows that increasing memory size to 2560 MB (which is 25% more) did not add further benefit which can be seen in the column : EST_DB_TIME_FACTOR value 1. However, in line 3 if Memory size is 3072 (i.e. 50% more ) estimated time factor is 0.9602 that means 1 sec process can be done in 0.9602 sec this is an improvement.

Even if you increase memory by 75%, there is no additional benefit, but there is very minor benefit when memory is  double.

In this scenario increasing memory by 50% is adding benefit for the database.

Read Full Post »

List aggregation (LISTAGG) in 11g

As you may already found that LISTAGG function is new in Oracle DB 11g. This function performs a string aggregation (concatenation) natively without implementing user-defined objects (most popular STRAGG) and other function that loops through data.

Here is the format of the function:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

Here is the simple example :

SQL> select first_name from employees where department_id =20;

FIRST_NAME
——————–
Michael
Pat

SQL> select listagg(first_name,’;’) within group (order by null) as fnames
2  from employees
3  where department_id = 20;

FNAMES
——————————————————————————–
Michael;Pat

Lets say there are multiple employees whose first name is same. But I would like to concatenate those names uniquely. That means I don’t want to list duplicate names, just concatenate distinct first names.

Here is example of department which contains employees of same first name.

SQL> r
1  select  listagg(first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;David;Eleni;E
lizabeth;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Lou
ise;Mattea;Nanette;Oliver;Patrick;Peter;Peter;Sarath;Sundar;Sundita;Tayler;Willi
am

As you see in result there are two David s and two Peter s. How can I select only distinct data.

LISTAGG function doesn’t let you use the distinct key. See below what happens if I try to do this.

SQL> r
1  select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
*
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

But this can be resolved using sub query as below.

SQL> r
1  select listagg(first_name,’;’) within group (order by first_name) as fnames
2  from
3  (
4  select distinct department_id, first_name
5  from employees
6* where department_id = 80)
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;Eleni;Elizabe
th;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Louise;Ma
ttea;Nanette;Oliver;Patrick;Peter;Sarath;Sundar;Sundita;Tayler;William

Now Results display distinct names as highlighted.

Read Full Post »