SQL queries to check ACTIVE / INACTIVE Sessions

Total Count of sessions
-----------------------------
select count(s.status) TOTAL_SESSIONS from gv$session s;

Total Count of Inactive sessions
----------------------------------
select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1 HOUR
---------------------------------------------------------------------------------------------
select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS
--------------------------------------
select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM
--------------------------------------------------------------
col program for a30
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE
-----------------------------------------------------------------
col module  for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION
---------------------------------------------------------------
col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.action;

INACTIVE SESSIONS
--------------------------------
prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE
-------------------
prompt INACTIVE SESSIONS
select count(s.status) INACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE PROGRAMS
-------------------------------------
col module for a40             
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, v$process p
where  p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads
---------------------------------------------------
prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM
-------------------------------------------------------------
col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.program
order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1 HOUR
--------------------------------------------------------------
col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY  MODULE
-------------------------------------------------------------------
col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR
-----------------------------------------------------------------
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;

INACTIVE PROGRAM  --ANY--
------------------------------------------
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

INACTIVE MODULES  --ANY--
--------------------------------------------
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

INACTIVE JDBC SESSIONS
-----------------------------------------
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR
-------------------------------------------------------------------------
SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

FORMS
===========
TOTAL FORM SESSIONS
------------------------------------
SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

FORMS SESSIONS DETAILS
-------------------------------------
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;                      


col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;         
order by 4;                           

INACTIVE FORMS SESSIONS DETAILS
-------------------------------------------------
col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

UNIQUE SPID
--------------------
select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

COUNT FORMS
------------------------------
select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

ZERO HASH VALUE
----------------------------
select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME
---------------------------------------
select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

GROUP BY ACTION
--------------------------------
SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

FROM A SPECIFIC USERNAME
------------------------------------------
SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

INACTIVE FORM
-----------------------------
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;

INACTIVE FORM SESSIONS
----------------------------------------------
col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

ACTIVE
-------------------
prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

MODULE
---------------------------
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr 
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr 
and s.MODULE like ('%TOAD%')
Order by last_call_et;

TOAD SESSIONS
----------------------------
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr 
and s.MODULE like ('%TOAD%')
Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT
--------------------------------------------------
select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions
----------------------------
col module for a40             
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY  program
----------------------------------
col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr  AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;

How to Register Shell Script as a concurrent program.

Steps to Register Shell Script as a concurrent program

step 1:

=======

Place the <name>.prog script under the bin directory for your applications top directory.

For example, call the script ERPS_DEMO.prog and place it under $CUSTOM_TOP/bin

step 2:

=======

Make a symbolic link from your script to $FND_TOP/bin/fndcpesr

For example, if the script is called ERPS_DEMO.prog use this:

ln -s $FND_TOP/bin/fndcpesr ERPS_DEMO

This link should be named the same as your script without the .prog extension.

Put the link for your script in the same directory where the script is located.

step 3:

=======

Register the concurrent program, using an execution method of ’Host’. Use the name of your script without the .prog extension as the name of the executable.

For the example above:

Use ERPS_DEMO

step 4:

=======

Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd

$2 = userid(apps)

$3 = username,

$4 = request_id

Any other parameters you define will be passed in as $5 and higher.

Make sure your script returns an exit status.

** Sample Shell Script to copy the file from source to destination:

Shell Script to copy the file from source to destination

#Note: If you see # in front of any line it means that it’s a comment line not the actual code

#** ********************************************************************

# Created By : Shahanwaz uddin

# Creation Date : 19-DEC-2013

# Script Name : ERPS_DEMO.prog

# Description : This Script accepts three parameters

# 1)Data File Name

# 2)Source Directory Path

# 3)Target Directory Path

# Then copy the file from source location to target location.

# If copy fails send the error status/message to concurrent program so that user can see status.

#

#

# ========

# History

# ========

# Version 1 Shahanwaz A 19-DEC-2013 Created for all users

#

#** ********************************************************************

#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters

# $1 : username/password of the database

# $2 : userid

# $3 : USERNAME

# $4 : Concurrent Request ID

DataFileName=$5

SourceDirectory=$6

TargetDirectory=$7

echo “————————————————–”

echo “Parameters received from concurrent program ..”

echo ” Time : “`date`

echo “————————————————–”

echo “Arguments : ”

echo ” Data File Name : “${DataFileName}

echo ” SourceDirectory : “${SourceDirectory}

echo ” TargetDirectory : “${TargetDirectory}

echo “————————————————–”

echo ” Copying the file from source directory to target directory…”

cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}

if [ $? -ne 0 ]

# the $? will contain the result of previously executed statement.

#It will be 0 if success and 1 if fail in many cases

# -ne represents not “equal to”

then

echo “Entered Exception”

exit 1

# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success

else

echo “File Successfully copied from source to destination”

exit 0

fi

echo “****************************************************************”

+++Basic Shell Script Commands++++++

# Create Directory

mkdir <dirname>

# Remove Directory

rmdir <dirname>

#remove folder with files

rm -rf <dirname>

# Change Directory

cd <newpath>

# Create new file

vi <newfile.ext>

#insert data into file

vi <openfilename.ext>

esc i <make changes>

#Save file

esc :wq enter

# exit without saving changes

esc :q! enter

# open existing file

vi <existingfilename.ext>

#remove file

rm <filename.ext>

# copy file with same name

cp <sourcedir>/<sourcefilename.ext> <destinationdir>

# copy file with new name

cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>

# Move file with same name

mv <sourcedir>/<sourcefilename.ext> <destinationdir>

# move file with data appended to filename in the front

mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>

#print line

echo “your text here to print”

#print date

echo `date`

How to create DBlink between two database

To create DBlink between two database

In distributed Oracle environments, database links can be used to define a communications path between two databases. A database link defines a network connection, and, optionally, a username and password, to use when Oracle accesses and establishes a database session in the remote database on behalf of the local application request.

Database link enable SQL statements to be isolated from the underlying physical network topology. Thus, whenever the location of a remote database changes, only the link need be updated, and not each and every SQL queries, significantly the tasks of programmer.

Oracle database link is defined in a specific database, and may be used only by users of that database. If the same database link name was to be used from all databases in the network, the links must be defined identically with that name in each of each databases. For this scenario, the easier way is to be global database link.

Database Link Creation
Login into Oracle database (with SQL*Plus, for example) as a user who has the privilege to create a database link and execute the following command:

CREATE DATABASE LINK link_name
CONNECT TO user_name IDENTIFIED BY password
USING ‘connection_string’;

where connection_string is an entry is tnsnames.ora (usually located in $ORACLE_HOME/network/admin), which enables the Oracle to know the network path to remote database.
connection_string has the following structure:

(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP) (Host = host_name/ip_address) (Port = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = database_name)
)
)

Database Link Deletion
To delete a database link:


DROP DATBASE LINK link_name;

Schema Refresh (Using Export /Import)

1. In PROD Database (Source Database name: Prod1)

1.1 CHECK THE NUMBER OF OBJECTS IN THE SOURCE DATABASE:

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PROD_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

------------------------------ ------------------- ------------------

PROD_USER123                   TABLE                               45

PROD_USER123                   INDEX                              120

PROD_USER123                   VIEW                                 1

PROD_USER123                   TRIGGER                              5

PROD_USER123                   SEQUENCE                            10

PROD_USER123                   LOB                                 19

6 rows selected.

1.2 CHECK THE INVALID OBJECTS IN THE SOURCE DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER='PROD_USER123';

no rows selected

1.3 TAKE EXPORT BACKUP OF PROD_USER123 SCHEMA FROM THE SOURCE DATABASE:

exp userid=system/xxxxxxx file=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp log=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.log owner=PROD_USER123 rows=y indexes=y grants=y constraints=y

1.4 TAKE THE REQUIRED INFORMATION FROM THE SOURCE DATABASE:

SQL> set line 1000

SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PROD_USER123';

USERNAME        PASSWORD   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE   PROFILE

--------------- ---------- -------------------- ---------------------- --------

PROD_USER123               USERDATA             TEMP                   DEFAULT

SQL> select * from dba_role_privs where grantee in('PROD_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in('PROD_USER123');

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

PROD_USER123                   CREATE TRIGGER                           NO

PROD_USER123                   CREATE PROCEDURE                         NO

PROD_USER123                   CREATE SEQUENCE                          NO

PROD_USER123                   CREATE SESSION                           NO

PROD_USER123                   GLOBAL QUERY REWRITE                     NO

PROD_USER123                   CREATE MATERIALIZED VIEW                 NO

PROD_USER123                   CREATE DATABASE LINK                     NO

PROD_USER123                   ALTER  SESSION                           NO

PROD_USER123                   CREATE INDEXTYPE                         NO

PROD_USER123                   CREATE OPERATOR                          NO

PROD_USER123                   CREATE SYNONYM                           NO

PROD_USER123                   CREATE TABLE                             NO

PROD_USER123                   CREATE TYPE                              NO

PROD_USER123                   CREATE VIEW                              NO

PROD_USER123                   CREATE CLUSTER                           NO

15 rows selected.
   
SQL> select * from dba_ts_quotas where username='PROD_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

---------------- --------------- ---------- ---------- ---------- ---------- ---

USERDATA         PROD_USER123    12582912   -1         1536       -1         NO

USERINDX         PROD_USER123    4980736    -1         608        -1         NO


1.4 SEND THE FULL EXPORT BACKUP TO THE TARGET DATABASE SERVER:

[oracle@proddb1 export]$ scp prod1.exp_PROD_USER123_11DEC.dmp oracle@devdb1:/d02/app/oracle/export

 ***********************************Target Database-Start******************************************************** 

2. In wmdevdb1 DEV Database server:(dev1)

2.1 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

------------------------------ ------------------- ------------------

DEV_USER123                    TRIGGER                              5

DEV_USER123                    SEQUENCE                             3

DEV_USER123                    LOB                                 12

DEV_USER123                    INDEX                               65

DEV_USER123                    VIEW                                 1

DEV_USER123                    TABLE                               26

6 rows selected.

2.2 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123';

no rows selected

2.3 TAKE EXPORT BACKUP OF DEV_USER123 SCHEMA FROM THE TARGET DATABASE :

[oracle@devdb1 export]$ exp userid=system/xxxxxx file=/d02/app/oracle/export/exp_DEV_USER123_11DEC.dmp log=/d02/app/oracle/export/exp_DEV_USER123_11DEC.log owner=DEV_USER123 rows=y indexes=y grants=y constraints=y

2.4 TAKE THE REQUIRED INFORMATION FROM THE TARGET DATABASE:
SQL> set line 1000    
SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='DEV_USER123';

USERNAME    PASSWORD   DEFAULT_TABLESPACE TEMP_TABLESPACE PROFILE

----------- ---------- ------------------ --------------- ------------

DEV_USER123            USERDATA           TEMP            DEV_USER123_DEFAULT

SQL> select * from dba_role_privs where grantee in ('DEV_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in ('DEV_USER123');

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

DEV_USER123                    CREATE OPERATOR                          NO

DEV_USER123                    CREATE MATERIALIZED VIEW                 NO

DEV_USER123                    CREATE TYPE                              NO

DEV_USER123                    CREATE SEQUENCE                          NO

DEV_USER123                    CREATE TABLE                             NO

DEV_USER123                    GLOBAL QUERY REWRITE                     NO

DEV_USER123                    CREATE INDEXTYPE                         NO

DEV_USER123                    ALTER  SESSION                           NO

DEV_USER123                    CREATE SESSION                           NO

DEV_USER123                    CREATE DATABASE LINK                     NO

DEV_USER123                    CREATE SYNONYM                           NO

DEV_USER123                    CREATE PROCEDURE                         NO

DEV_USER123                    CREATE VIEW                              NO

DEV_USER123                    CREATE CLUSTER                           NO

DEV_USER123                    CREATE TRIGGER                           NO

15 rows selected.

SQL> select * from dba_ts_quotas where username='DEV_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

---------------- --------------- ---------- ---------- ---------- ---------- ---

USERDATA         DEV_USER123     38928384   -1         4752       -1         NO

USERINDX         DEV_USER123     8847360    -1         1080       -1         NO

2.5 DROP THE DEV_USER123 USERS FROM THE TARGET DATABASE:
SQL> drop user DEV_USER123 cascade;

2.6 CREATE THE DEV_USER123 USER AS PER THE SOURCE DATABASE :
SQL> create user DEV_USER123 identified by DEV_USER123

default tablespace USERDATA

temporary tablespace TEMP

profile DEV_USER123_DEFAULT

quota unlimited on USERDATA

quota unlimited on USERINDX;

2.7 GRANT THE PRIVILAGES TO THE PRODAPP USER AS PER SOURCE DATABASE :
SQL> grant connect,resource to DEV_USER123;
SQL> grant CREATE TABLE,CREATE SESSION,CREATE TRIGGER,CREATE SEQUENCE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY,CREATE VIEW to DEV_USER123;

2.8 IMPORT THE DATA TO THE TARGET DATABASE FROM THE SOURCE DATABASE :

[oracle@devdb1 export]$ imp userid=system/xxxxxxx file=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp 

log=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC_imp.log 

fromuser=PROD_USER123 touser=DEV_USER123 ignore=y

2.9 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :
SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                         OBJECT_TYPE         COUNT(OBJECT_TYPE)

----------------------------- ------------------- ------------------

DEV_USER123                   TABLE                               45

DEV_USER123                   INDEX                              120

DEV_USER123                   VIEW                                 1

DEV_USER123                   TRIGGER                              5

DEV_USER123                   SEQUENCE                            10

DEV_USER123                   LOB                                 19

6 rows selected.
Note: Output of above command should be same as production.

2.10 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123'; 

2.11 IF ANY INVALID OBJECTS THEN RUN UTLRP.SQL
    
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

**********************************END**********************************