How to Remove/Wipe long running KILLED and INACTIVE session in Oracle Database

SQL>
SQL> select SID, SERIAL#, USER#, USERNAME,SCHEMANAME,SCHEMA#, STATUS,PROCESS,OSUSER from v$session where USERNAME='SYS';

       SID    SERIAL#      USER# USERNAME             SCHEMANAME                        SCHEMA# STATUS   PROCESS                  OSUSER
---------- ---------- ---------- -------------------- ------------------------------ ---------- -------- ------------------------ ------------------------------
        10       4827          0 SYS                  SYS                                     0 INACTIVE 23017                    oracle
       209      33035          0 SYS                  SYS                                     0 ACTIVE   13138                    oracle
       226       5355          0 SYS                  TRACK_OWNER                            78 KILLED   8499                     oracle

SQL> select s.username,s.sid,s.serial#,p.spid,s.lockwait,s.logon_time
from v$session s, v$process p
where s.paddr = p.addr and s.USERNAME='SYS';  2    3

USERNAME                    SID    SERIAL# SPID                     LOCKWAIT         LOGON_TIME
-------------------- ---------- ---------- ------------------------ ---------------- ------------------
SYS                         226       5355 8500                                      24-FEB-16
SYS                          10       4827 23018                                     25-FEB-16
SYS                         209      33035 13139                                     25-FEB-16
  
SQL> !ps -aef|grep 8499
oracle    8499  8213  0 Feb24 ?        00:00:00 sqlplus   as sysdba
oracle    8500  8499  0 Feb24 ?        00:04:03 oracleTW1D (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14627 13138  0 13:25 pts/8    00:00:00 /bin/ksh -c ps -aef|grep 8499
oracle   14629 14627  0 13:25 pts/8    00:00:00 grep 8499

SQL> !ps -aef|grep 8500
oracle    8500  8499  0 Feb24 ?        00:04:03 oracleTW1D (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14631 13138  0 13:25 pts/8    00:00:00 /bin/ksh -c ps -aef|grep 8500
oracle   14633 14631  0 13:25 pts/8    00:00:00 grep 8500
oracle   32079  8500  0 Feb24 ?        00:00:02 /u001/app/oracle/product/11.2.0.4/ctx/bin/ctxhx /tmp/drgibUskeUq /tmp/drgitee7wti ASCII8 utf8 H NOMETA 120 HEURISTIC FORMAT NOPDFROTATE /tmp/drgitmp4I7029 ENCRYPTED

SQL> !kill -9 8500  8499

SQL> !kill -9 32079

SQL> !kill -9 23018 23017

SQL> exit
.
.
.
.
SQL>  select SID, SERIAL#, USER#, USERNAME,SCHEMANAME,SCHEMA#, STATUS,PROCESS,OSUSER from v$session where USERNAME='SYS';

       SID    SERIAL#      USER# USERNAME             SCHEMANAME                        SCHEMA# STATUS   PROCESS                  OSUSER
---------- ---------- ---------- -------------------- ------------------------------ ---------- -------- ------------------------ ------------------------------        
  209      33039          0 SYS                  SYS                                     0 ACTIVE   15044                    oracle

SQL>



So, now as you can see there is no KILLED/INACTIVE Session. Please use below Sql Queries:

1)To Find KILLED/INACTIVE Session Process to kill use PPID & PID Column "kill -9 31105 31103"
col username format a20
col SCHEMANAME format a20
col status format a20
col OSUSER format a20
col PPID format a10
col PID format a10

select s.OSUSER,s.username,s.SCHEMANAME,s.sid,s.serial#,p.spid as PPID,s.process as PID,s.logon_time,s.STATUS
from v$session s, v$process p 
where s.paddr = p.addr and s.USERNAME='SYS';   


OSUSER               USERNAME             SCHEMANAME                  SID    SERIAL# PPID       PID        LOGON_TIME         STATUS
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ------------------ --------------------
oracle               SYS                  SYS                           6      45904 31105      31103      26-FEB-16          ACTIVE

SQL>

2)To Generate oracle statemt to kill session.
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='USER_NAME';

How to Perform Oracle Data Pump with Different-2 Scenario on 11g

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later.Oracle Data Pump is made up of three distinct parts:

-The command-line clients, expdp and impdp
-The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
-The DBMS_METADATA PL/SQL package (also known as the Metadata API)

For Monitoring Job Status of Data Pump, Please select a query on DBA_DATAPUMP_JOBS,  USER_DATAPUMP_JOBS, or DBA_DATAPUMP_SESSIONS views. V$SESSION_LONGOPS dynamic performance view indicating the job progress (in megabytes of table data transferred). The entry contains the estimated transfer size and is periodically updated to reflect the actual amount of data transferred.

## How to estimate the size of Database
expdp FULL=Y ESTIMATE_ONLY=Y

## Perform Full Database Export/Import (We can perform any Import Operation if we have Full Database Dump.)
expdp system/password DIRECTORY=DIR_NAME DUMPFILE=Dump_File_Name.dmp LOGFILE=log_file.log FULL=Y
impdp system/password DIRECTORY=DIR_NAME DUMPFILE=Dump_File_Name.dmp LOGFILE=Imp_log_file.log FULL=Y

## On Specific Schema Export/Import.
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=user1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log SCHEMAS=user1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log SCHEMAS=user1 REMAP_SCHEMA=user1:user2 -Use Remap if username is Different

## Schema Table Export/Import from User1.table  to User2.
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log TABLES=user1.table1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1 REMAP_SCHEMA=user1:user2 

## Perform Export/Import on Quey
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log TABLES=user1.table1 QUERY=user1.table1:”WHERE Sex='Female'”
impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=Imp_log_file.log TABLES=user1.table1 REMAP_TABLE=user1.table1:table2  QUERY=user1.table1:”WHERE name like '%R%'”

Example: 
QUERY=user1.table1:"where DATE > '01-DEC-2013'"
QUERY=user1.table1:"where Department='IT' AND Location='Bangalore'"

## Perform Exclude/Include import/Export
expdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log FULL=Y EXCLUDE=TABLE:"IN('TABLE1','TABLE2')"

impdp system/password DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=user1 REMAP_SCHEMA=user1:user2 INCLUDE=TABLE:"IN('TABLE1','TABLE2')"

impdp DIRECTORY=EXP_DIR DUMPFILE=export_file.dmp LOGFILE=log_file.log SCHEMAS=User1 INCLUDE=INDEX SQLFILE=EXP_DIR:domain_idx.sql -To Generate DDL from from dump.

Example:
INCLUDE=TABLE:" like '%TAB%'"

## Take Import/Export Using Parameter (.par) File
vi imp_schema_parfile.par

directory=EXP_DIR
dumpfile=Dump_file.dmp
logfile=Dump_file.log
schemas=Rajesh
Version=COMPATIBLE
remap_schema=rajesh:rahul 
remap_tablespace=VGSM_STATIC:SM_STATIC,VGSM_INDEX:SM_STATIC_I
table_exists_action=truncate
PARALLEL=4 
tables=rajesh.table1, rajesh.table2, rajesh.table3
IGNORE=Y
:wq

impdp parfile=imp_schema_parfile.par
In the same way you can use export as well.

## Take Import/Export Using "NETWORK_LINK "Parameter.
Network_link parameter is very useful, when you've shortage of space in File system while performing import/export. with help of it you can take export in different server as well and you can import the same from there to target server.

Example:
Create a Database link on server(source/target) where you are performing export/import.

CREATE DATABASE LINK SYS_DBLNK CONNECT TO system IDENTIFIED BY password USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=servername)(Port=1521)))(CONNECT_DATA=(SID=REMORTE)))';
OR
CREATE DATABASE LINK SYS_DBLNK CONNECT TO system IDENTIFIED BY password USING 'REMORTE';

nohup expdp system/password directory=EXP_DIR dumpfile=file.dmp logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -Taking .dmp file in different location
                   
nohup impdp system/password directory=EXP_DIR dumpfile=file.dmp logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -Import .dmp file to target DB from .dmp file location
                   
nohup impdp system/password directory=EXP_DIR logfile=file.log SCHEMAS=User1,User2 network_link=SYS_DBLNK & -import data without .dmp file


## How to Run a job in background via shell script using par file or a Single line
nohup impdp '"/ as sysdba"' parfile=imp_schema_parfile.par &
nohup expdp '"/ as sysdba"' parfile=exp_schema_parfile.par &

OR
vi impdp_schema_shell.sh
echo "File import Starting at: `date`"
impdp '"/ as sysdba"' parfile=imp_schema_parfile.par
echo "File import Finish at: `date`"
:wq

nohup sh impdp_schema_shell.sh > impdp_schema_shell.log &


## Check status for running job both side from sql and file level
Find the Job name from import/export .log file location or also you can specify your own job name with JOB_NAME parameter while running job. And to for find a currently running job follow below data dictionary view as well.

SQL> select * from dba_datapump_jobs;
OR 
expdp "/ as sysdba" attach='' -At prompt type "status"
impdp "/ as sysdba" attach='' -At prompt type "status"


## How to kill running export/import job
expdp "/ as sysdba" attach='' -At prompt type "kill_job" Enter "Yes"
impdp "/ as sysdba" attach='' -At prompt type "kill_job" Enter "Yes"


## Create Datapump Directory in Oracle
CREATE DIRECTORY exp_dir AS '/path1/path2/';
CREATE OR REPLACE DIRECTORY exp_dir AS '/path1/path2/';
Grant read,write on DIRECTORY exp_dir to username|public;


Some Commonly Used Parameters are:
-COMPRESSION: Specifies whether to compress metadata before writing to the dump file set
-CONTENT: Enables you to filter what Export unloads: data only, metadata only, or both.
-DIRECTORY: Specifies the default location to which Export can write the dump file set and the log file
-DUMPFILE: Specifies the names, and optionally, the directory objects of dump files for an export job.
-LOGFILE: Specifies the name, and optionally, a directory, for the log file of the export job.
-ESTIMATE_ONLY: Instructs Export to estimate the space that a job would consume, without actually performing the export operation.
-EXCLUDE: Enables you to filter the metadata that is exported by specifying objects and object types that you want excluded from the export operation.
-FULL: Specifies that you want to perform a full database mode export.
-INCLUDE: Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.
-PARFILE: Specifies the name of an export parameter file.
-QUERY: Enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table
-SCHEMAS: Specifies that you want to perform a schema-mode export. This is the default mode for Export.
-TABLES: Specifies that you want to perform a table-mode export.
-REMAP_SCHEMA: Loads all objects from the source schema into a target schema.
-REMAP_TABLE: Allows you to rename tables during an import operation performed with the transportable method (REMAP_TABLE=hr.employees:emps)
-REMAP_TABLESPACE: Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.
-TABLE_EXISTS_ACTION: Tells Import what to do if the table it is trying to create already exists.

Refresh Database Objects, Stats Gathering and Password update with value

# Full Schema Refresh
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'CSI');

# How to change Oracle password and restore with old one if you don't know current password

Step 1: Get the password value of user
SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';

     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     A31A78DDCB990AE2

Step2: Change the user password with a new value and perform the activity.
SQL> ALTER USER CSI IDENTIFIED BY mynewpassword;

SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';
     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     D3998D50331ED867

Step3: After completion of activity revert back to original password.
SQL> ALTER USER CSI IDENTIFIED BY VALUES 'A31A78DDCB990AE2';

How to Find Oracle Locked Objects and Session Issue

# Query to Identify Locked object in session
set pages 200 lines 200
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, A.Session_id FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID;
select SID, SERIAL#, USER#, USERNAME,PROGRAM,SCHEMANAME,SCHEMA#, STATUS from v$session where USERNAME='SYS';
select SID,SERIAL#,SCHEMANAME,OSUSER, status from v$session where username='SYS';

# Create and Kill the session using below statements
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='USER';
alter system kill session '223,987' immediate;