Oracle 11g Logo

How to kill oracle datapump export job

We can kill oracle datapump job by two methods, First method includes killing data pump job via data pump export prompt and another method includes running SQL package on SQL prompt as sysdba.

//To simulate both the scenario, i am going to start oracle datapump export as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 16:43:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
. .. …

First Method: Kill Data pump job by datapump export prompt:

//After initiating export backup, Kindly make sure datapump job by issuing the following query as sysdba:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3

//Now connect to datapump export prompt with JOB_NAME(attach) as below & issue the datapump command: KILL_JOB.

[oracle@dbserver ~]$ expdp system/manager attach=SYS_EXPORT_FULL_01
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 17:01:13 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: F6C3A9B1D87AC043E0430100007F07F7
Start Time: Friday, 11 April, 2014 17:00:38
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/Export/Full_export.dmp
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ELET
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 80
Worker Parallelism: 1

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@dbserver ~]$

//Datapump export job has been killed successfully. Same message will be display in datapump logfile as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 16:43:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
. .. …
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ stopped due to fatal error at 16:45:35

—x—

Second Method: Kill Datapump job by running SQL package:

//After inititating the oracle datapump export, ensure datapump job by issuing the following query as sysdba:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3

//To kill datapump job, We need two parameter as input to SQL package are: JOB_NAME of the datapump job & OWNER_NAME who initiated export.

SQL> DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH(‘SYS_EXPORT_FULL_01‘,’SYSTEM‘);
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

PL/SQL procedure successfully completed.
SQL>

//Datapump export job has been killed successfully, same message will be display in datapump logfile as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 17:00:37 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
. .. …
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ stopped due to fatal error at 17:01:23

By above two methods, we can kill oracle datapump export job.

***********************************************************************

abc

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Delete Pluggable Database with Database Configuration Assistant

We can delete Pluggable database using Database Configuration Assistant OR manually from SQL prompt as sysdba privilege.

Here we covered Database Configuration Assistant to delete Pluggable database.

In this post, we considered Pluggable database already created, if not then kindly have a look at: Create Pluggable Database using Database Configuration Assistant

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Del_plugdb_1 Del_plugdb_2

 

On “Database Operation” prompt, select the “Manage Pluggable Databases”

In this we can create new pluggable database delete it as well as manage existing pluggable databases.

Click Next to continue.

Del_plugdb_3

 

On “Manage Pluggable Databases” prompt, select the “Delete a Pluggable Database” radio button.

& click Next.

Del_plugdb_4

 

On “Database List” prompt, select the Container Database.

In my case, “orcl” is Container Database.

& click Next.

Del_plugdb_5

 

On “Delete Pluggable Database” prompt, select pluggable database to be delete from drop down list & click Next.

& click Next.

Del_plugdb_6

 

Ensure the summary about your provided database, if u wish to continue click Finish button.

Mentioned system01.dbf, sysaux01.dbf & PlugDB_users01.dbf file will be deleted permanently from the container database.

Del_plugdb_7

PlugDB – Pluggable database has been deleted successfully.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Plugin a Pluggable database using Database Configuration Assistant

We can plugin back a Pluggable database using Database Configuration Assistant OR manually from SQL prompt as sysdba privilege.

Here we covered Database Configuration Assistant to plugin back a Pluggable database.

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Plugin_pluggdb_1

Plugin_pluggdb_2

 

On “Database Operation” prompt, select the “Manage Pluggable Databases”

In this we can create new pluggable database as well as manage existing pluggable databases.

Click Next to continue.

Plugin_pluggdb_3

On “Manage Pluggable Databases” prompt, select the “Create a Pluggable Database” radio button.

& click Next.

Plugin_pluggdb_4

 

On “Database List” prompt, select the Container Database.

In my case, “orcl” is Container Database.

& click Next.

Plugin_pluggdb_5

 

On “Create Pluggable Database” prompt, select the “Create Pluggable Database using PDB file set” radio button.

In my case, I have created PDB file set at the time of unplug pluggable database, that’s why I would like to go with “Create Pluggable Database using PDB file set” option.

If you unplug your Pluggable database with the help of “Create Pluggable Database From PDB archive” then choose same for re-plug pluggable database.

& click Next.

Plugin_pluggdb_6

Choose .xml file of your Pluggable database that you want to plug in back.

& click Ok.

Plugin_pluggdb_7

 

On “Pluggable Database Option” prompt.

Enter the pluggable database name that you want to plugin back, also provide PDB user details as below.

& click Next.

Plugin_pluggdb_8

 

Ensure the summary about your provided database, if u wish to continue click Finish button.

Plugin_pluggdb_9

 

Progress page will show you the progress of same.

Plugin_pluggdb_10

 

On completion of installation, click the “OK” button on the message dialog and the “Close” button on the main screen.

Plugin_pluggdb_11

PlugDB – Pluggable database has been plugin successfully.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Unplug Pluggable Database using Database Configuration Assistant

We can unplug Pluggable database using Database Configuration Assistant OR manually from SQL prompt as sysdba privilege.

Here we covered Database Configuration Assistant to unplug Pluggable database.

In this post, we considered Pluggable database already created, if not then kindly have a look at: Create Pluggable Database using Database Configuration Assistant.

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Unplug_plugdb_1

Unplug_plugdb_2

 

On “Database Operation” prompt, select the “Manage Pluggable Databases”

In this we can create new pluggable database as well as manage existing pluggable databases.

Click Next to continue.

Unplug_plugdb_3

 

On “Manage Pluggable Databases” prompt, select the “Unplug a Pluggable Database” radio button.

& click Next.

Unplug_plugdb_4

 

On “Database List” prompt, select the Container Database.

In my case, “orcl” is Container Database.

& click Next.

Unplug_plugdb_5

 

On “Unplug Pluggable Database” prompt, select pluggable database to be unplug from drop down list & select “Generate Pluggable Database File Set” radio button for future purpose.

& click Next.

You can also select “Generate Pluggable Database Archive” radio button, as per your convenience. In my case, I would like to go with “Generate Pluggable Database File Set”.

Unplug_plugdb_6

 

Ensure the summary about your provided database, if u wish to continue click Finish button.

Unplug_plugdb_7

 

Progress page will show you the progress of the same.

Unplug_plugdb_8

On completion of process, click the “OK” button on the message dialog and the “Close” button on the main screen.

Unplug_plugdb_9

 

PlugDB – Pluggable database has been unplugged successfully.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Create Pluggable Database using Database Configuration Assistant

We can create Pluggable database at the time of creation of Container database & we can also create Pluggable database with the help of Database Configuration Assistant.

Here we considered Container database already installed, if not then kindly follow my one of the post: Oracle 12c Installation

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Continue reading

Oracle 11g Logo

ORA-39171: Job is experiencing a resumable wait

While exporting my one of the database with data pump backup utility, Backup has been stuck with ORA-01691: unable to extend lob segment & ORA-39171: Job is experiencing a resumable wait ora errors.

Oracle error pretty much self explanatory. After diagnosing ORA-01691 & ORA-39171, I came across the system tablespace having lack of space allocated, after adding new datafile ( alter tablespace… ) to system tablespace backup job has been resumed & successfully completed.

//Export Logs as below:

OracleDB:ora 19> expdp system/manager directory=dirdmp schemas=Prod dumpfile=Prod_260314%U.dmp logfile=Prod_260314.log compression=all parallel=4 Continue reading

Oracle 11g Logo

Export Import Performance Optimization Tips

We can improve Export-Import performance optimization by considering following points.

Export Optimization tips:

  • Set the BUFFER parameter to high value (eg 2M)
  • Set the RECORDLENGTH parameter to a high value (e.g. 64K)
  • Stop unnecessary applications to free-up resources for your job.
  • DO NOT export to an NFS mounted filesystem. Continue reading
Oracle 12c Logo

Oracle 12c installation on Oracle Linux release-6 (64-bit)

Here we are installing Oracle 12c Release-1 (12.1)(64-bit) on Oracle Linux release-6 (64-bit)

Pre-requisite installation check on CentOS:

Minimum swap size 2GB.
IP Tables off.
SELinux set to permissive.

// Ensure swap size by issuing following command, if swap size do not meet minimum requirement then increase it. Continue reading

sql server 2008 logo

SQL server 2012 stand alone installation

This article show you step by step installation guide of SQL server 2012 ( stand alone ) on windows data center edition 64bit OS.

Pre-requisites for SQL server 2012:

  • .NET 3.5 sp1
  • .NET 4.0 (SQL server installs this during feature installation steps )
  • Windows Power shell 2.0
  • Minimum 6GB disk space
  • Minimum 1GB RAM, in case of Express edition it need 512MB

Continue reading

sql server 2008 logo

Daily Backup Configuration in SQL Server 2008

As we are aware of how backup is more important in day to day life, please consider following methodologies to backup SQL server 2008 databases through Microsoft SQL Server management studio.

We can backup database with 2 methods as below:

Method 1: Individual backup:
We can take individual database backup by following steps:

Step 1: connect to Microsoft SQL Server management studio. Continue reading

Oracle 11g Logo

How to extract current Oracle user name and schema name?

Issue following query to extract current user name ( Session name ):

Syntax:
SELECT
sys_context(‘USERENV’,’SESSION_USER’) as “USER NAME”, 
sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) as “CURRENT SCHEMA” 
FROM dual;

sys_context() : This function returns the value of parameter associated with the context namespace. Continue reading

Oracle 11g Logo

How to extract the Oracle table definition (Data Defining Language)

How to extract the Oracle table creation definition (Data Defining Language) from an Oracle database without digging dictionary views?

With the help of GET_DDL() function of DBMS_METADATA metadata package.

Syntax:

select DBMS_METADATA.GET_DDL(‘TABLE’,’TABLE_NAME’) from DUAL;

SET LONG 1000

SET PAGESIZE 0 Continue reading

Oracle 11g Logo

How to fetch last inserted row from any oracle table

This is easily done with the date datatype or timestamp datatype.

Ex:

select empno,ename,to_char(hiredate,’dd mon yyyy’) as hiredate
from emp
where hiredate =( select max(hiredate) from emp );

EMPNO      ENAME      HIREDATE
———- ———- ———–
7876       ADAMS      23 may 1987 Continue reading

Oracle 11g Logo Oracle 11g Logo Oracle 11g Logo

Oracle Enterprise/Standard/Standard-one Edition Installation on CentOS 6.2

Installation of Oracle Enterprise/Standard/Standard-one Edition on CentOS release 6.2 (Final)

//Find memory information by issuing following command:

grep -i memtotal /proc/meminfo
[root@centos ~]# grep -i memtotal /proc/meminfo
MemTotal: 497680 kB
//Find swap memory information by issuing following command:
grep -i swaptotal /proc/meminfo Continue reading

Oracle 11g Logo

Oracle 11gR2 installation failed due to “error in invoking target ‘irman ioracle’ of makefile bms.mk”

Oracle 11gR2 installation failed due to error in invoking target ‘irman ioracle’ of makefile bms.mk

I had ensured all oracle packages were installed properly prior to starting installation & oracle installer did not give any warning in prerequisites check.

so i ensure swap memory is sufficient or not, In my case swap space utmost consumed, so after increasing swap space setup has been started successfully.

Continue reading

Oracle 11g Logo

Script to extract size of Oracle tablespaces & associated datafiles

By issuing below script you will extract the total size of tablespces along with Total space available in MB, Free space available in MB & Free percentage(%)

Syntax:
COLU Free% FORMA A10
COLUMN tablespace_name FORMAT A17
SELECT t.tablespace_name, t.total_space_in_MB, f.free_space_in_MB,
TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000')
"Free%"

FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 Total_space_in_MB FROM DBA_DATA_FILES GROUP BY tablespace_name) t,
(SELECT tablespace_name, SUM(bytes)/1024/1024 Free_space_in_MB FROM DBA_FREE_SPACE GROUP BY
tablespace_name) f WHERE t.tablespace_name= f.tablespace_name;

OR

 

select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 1;

 

 

By issuing below script you will extract the size & path of datafiles along with tablespace name, allocated space in MB, used space in MB & free space in MB.

Syntax:
set linesize 200
set pagesize 2000
COLUMN tablespace_name format a10
COLUMN file_name format a45
COLUMN free% format a7
SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
round(((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),1) used_mb,
round(NVL (SUM (dfs.bytes) / 1024 / 1024, 0),2) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME
ORDER BY df.tablespace_name;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Script to extract total objects & size of all Schemas

Script to extract total objects & size of all Schema’s:

By issuing below script you will find out all schema’s along with size they have yet consumed, script will find out all users. ( system users + user defined users)
Also you can find out total number of objects contains that schema.

 

Issue following query as sys user:

select obj.owner "Owner", obj_cnt "Objects",
decode(seg_size, NULL, 0, seg_size) "size MB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

Owner                          Objects     size MB
——————————     ———-      ———-
SYS                            30965       990
XDB                            842          128
APEX_030200             2406         78
SYSMAN                     3491         46

. .. …

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Oracle 11g Logo

Script to extract Up time of Oracle database

By issuing following script you will extract exact uptime of database cum Hostname, Instance Name & Database Start time:

Syntax:
 column hostname format a26
 column “Instance Name” format a16
 column “Started At” format a26
 column “Database Uptime” format a52
 SELECT
 host_name as Hostname,
 instance_name as “Instance Name”,
 to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) as “Started_At”,
 floor(sysdate – startup_time) || ‘ days(s) ‘ ||
 trunc( 24*((sysdate-startup_time) -
 trunc(sysdate-startup_time))) || ‘ hour(s) ‘ ||
 mod(trunc(1440*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||’ minute(s) ‘ ||
 mod(trunc(86400*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||’ seconds’ as “Database_Uptime”
 FROM
 sys.v_$instance;

HOSTNAME

——–
Instance Name Started_At
——– —————
Database_Uptime
————————————–
localhost.localdomain
DemoDB 13-APR-2013 12:11:40
216 days(s) 22 hour(s) 1 minute(s) 52 seconds

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.