System & Database Related Solutions

Thursday 16 March 2017

Export/Import in Datapump using query parameter

Purpose

Enables you to filter the data that is exported by specifying a clause for a SELECT statement, which is applied to all/multiple tables in the export job or to a specific table.

Solution

[oracle@said ~]$ cat bak.sh
expdp system/passwd@orcl parfile=/home/oracle/parameter.par
[oracle@said ~]$

[oracle@said ~]$ cat parameter.par
tables=SCOTT.EMP
query=SCOTT.EMP:"WHERE (liability_issued - nvl(liability_reversed, 0) != 0
or paid_margin_amount_lcy - nvl(refunded_margin_lcy, 0) != 0
or indem_liability_issued - nvl(indem_liability_reversed, 0) != 0)"
directory=dpump
dumpfile=emp.dmp
logfile=emp.log
[oracle@said ~]$ ./back.sh

Export: Release 11.2.0.3.0 - Production on Thu Mar 16 14:39:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@orcl parfile=/home/oracle/parameter.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 683.1 GB
Processing object type TABLE_EXPORT/TABLE/TABLE

Wednesday 14 December 2016

Step by step Configure yum in RHEL 7

Step 1: Create a directory where packages will be copied

[root@rhel7 ~]# mkdir /yum

Step 2: Now copy all the packages from DVD to /yum location

[root@rhel7 ~]# cp -r * /yum

Step 3: Now configure the repository file

[root@rhel7 yum]# vim /etc/yum.repos.d/sirat.repo
[sirat]
name=Standard Bank Limited
baseurl=file:///yum
gpgcheck=0
[root@rhel7 yum]#

---Following file is use when package will be in network location

[root@rhel7 ~]# vim /etc/yum.repos.d/server.repo
[server]
name= Siratun Jannat
baseurl= http://station.network0.example.com/content/rhel7.0/x86_64/dvd
gpgcheck= 0

Step 4: Now execute the following command to check that yum repository configured or not

[root@rhel7 ~]# yum clean all
[root@rhel7 ~]# yum repolist

Loaded plugins: langpacks, product-id, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
repo id repo name status
!sirat Standard Bank Limited 4,305
repolist: 4,305
[root@rhel7 yum]#

[root@rhel7 yum]# yum install ftp
Loaded plugins: langpacks, product-id, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package ftp.x86_64 0:0.17-66.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================
Package Arch Version Repository Size
================================================================================================================
Installing:
ftp x86_64 0.17-66.el7 sirat 61 k

Transaction Summary
================================================================================================================
Install 1 Package

Total download size: 61 k
Installed size: 96 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : ftp-0.17-66.el7.x86_64 1/1
Verifying : ftp-0.17-66.el7.x86_64 1/1

Installed:
ftp.x86_64 0:0.17-66.el7

Complete!
[root@rhel7 yum]#

Thats it !!!!!!!!!!!!!!

Tuesday 6 December 2016

Find Oracle Database session from OS process

Execute the following query and find out the Database session

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and b.username is not null
order by a.spid asc; 2 3 4 5

SERVER CLIENT USERNAME STATUS
------------------------ ------------------------ ------------------------------ --------
14674 2868:6052 SYS INACTIVE
14679 2868:4792 SYS INACTIVE
14754 14753 SYS ACTIVE

SQL>

Execute the following command and find out the OS process mapping with Database session

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL

oracle 14674 0.0 1.0 1682420 48532 ? Ss 11:13 0:00 oracleorcl (LOCAL=NO)
oracle 14679 0.0 0.4 1679844 21852 ? Ss 11:13 0:00 oracleorcl (LOCAL=NO)
oracle 14754 0.0 0.4 1679844 23068 ? Ss 11:20 0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@saidrasel ~]$

Now create three new connection in Database and execute the query

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and b.username is not null
order by a.spid asc 2 3 4 5 ;

SERVER CLIENT USERNAME STATUS
------------------------ ------------------------ ------------------------------ --------
14674 2868:6052 SYS INACTIVE
14679 2868:4792 SYS INACTIVE
14754 14753 SYS ACTIVE
14785 2868:4792 SAID INACTIVE
14787 2868:4792 SAID INACTIVE
14790 3944:3764 RASEL INACTIVE

6 rows selected.

SQL>

Now find the these Database session from OS process also

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL
oracle 14674 0.0 1.0 1682420 48540 ? Ss 11:13 0:00 oraclestlbas (LOCAL=NO)
oracle 14679 0.0 0.4 1679844 21852 ? Ss 11:13 0:00 oraclestlbas (LOCAL=NO)
oracle 14754 0.0 0.4 1679844 23096 ? Ss 11:20 0:00 oraclestlbas (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 14785 0.1 0.8 1682420 40408 ? Ss 11:23 0:00 oraclestlbas (LOCAL=NO)
oracle 14787 0.1 0.8 1680868 42848 ? Ss 11:23 0:00 oraclestlbas (LOCAL=NO)
oracle 14790 0.0 0.4 1679844 22528 ? Ss 11:23 0:00 oraclestlbas (LOCAL=NO)
[oracle@saidrasel ~]$

Pages