TABLESPACE And Temporary Tablespace Analyse

select file_name,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA';

--ALTER DATABASE DATAFILE '/kzn01/TEST/db/apps_st/data/a_txn_data06.dbf' RESIZE 2G;

----------------------------------------
select * from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_IDX';

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/kzn03/Oracle/PROD/db/apps_st/data/a_txn_ind07.dbf' SIZE 2G;

---------------------------------------
list tablespaces, size, free space, and percent free
 
SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;



--------------------------------------------------
Full database size

SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'
"Database Size",ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)
- ROUND (free.p / 1024 / 1024 / 1024)|| ' GB' "Used space",
ROUND (free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT   bytes FROM v$datafile UNION ALL
SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,
(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;

============================================================================

Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

SELECT group_name, tablespace_name FROM dba_tablespace_groups;
SELECT * FROM dba_tablespace_groups;

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';


SELECT * FROM dba_tablespace_groups;

SELECT TABLESPACE_NAME,free_space/1024/1024/1024 from DBA_TEMP_FREE_SPACE;

SELECT * from DBA_TEMP_FREE_SPACE;

SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;

SELECT * FROM v$tempfile;

 
 
-- /u03/CRP1GST/db/apps_st/data/temp02.dbf
 
--ALTER DATABASE TEMPFILE '/u01/UAT/db/apps_st/data/temp03.dbf' RESIZE 2G;
 --ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/tempfile/tempo3.dbf' size 1000M reuse;
 commit;
 

TBALESPACE CHECK

 
 SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;

While Apply GST patch 19287293 Locked object

1.

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine 
from v$locked_object a , v$session b, dba_objects c 
where b.sid = a.session_id 
and a.object_id = c.object_id;

OutPut

APPLSYS FND_CONCURRENT_QUEUES TABLE 417 62192 INACTIVE orapreprod dev.tbzoriginal.local
APPLSYS FND_CONCURRENT_REQUESTS TABLE 418 46553 INACTIVE orapreprod dev.tbzoriginal.local

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


Solution
----------


Hi, 

Please execute the following: 

ALTER SYSTEM KILL SESSION '417,62192'; 
ALTER SYSTEM KILL SESSION '418,46553'; 

Check if the 2 locks have been released: 

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine 
from v$locked_object a , v$session b, dba_objects c 
where b.sid = a.session_id 
and a.object_id = c.object_id; 

Retry the patching. 

CPU Utilization analyze and find running query , concurrent using OS PID

To Find the OS PID which utilize more CPU Kill the session or concurrent

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid');


select sid,serial#,p2,program,module,machine from gv$session where sid=915;

SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID=915);

select * from gv$session where SID=915;

alter system kill session '915,28268';

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


Run This query as APPS user to find the concurrent Request ID using OS PID

select * from fnd_concurrent_requests
where 1=1 and oracle_process_id=&OS_PID
and trunc(request_date)=trunc(sysdate);

FNDCPASS apps password change in application

FNCPASS CHANGE
--------------------------

First change system password in DB.
alter user system identified by clonemanager

FNDCPASS apps/welcome 0 Y system/clonemanager SYSTEM APPLSYS cloneapps
FNDCPASS apps/cloneapps 0 Y system/clonemanager USER sysadmin clonewelcome

Form Compile

Form Compile
--------------------
/kzn02/Oracle/PROD/scripts


frmcmp_batch userid=apps/kzn123 module=XXMISC_RECEIPT_REF.fmb output_file=$XXKZJ_TOP/forms/US/XXMISC_RECEIPT_REF.fmx module_type=form batch=no compile_all=special

EXPDP / IMPDP TABLE in oracle database

If You Want Export Particular Table 

expdp system/manager directory=DATA_PUMP_DIR tables='sales.XXPOS_NEW_ORDER_LINES_STG' DUMPFILE=XXPOS_NEW_ORDER_LINES_STG.dmp

Import Command

impdp system/manager directory=DATA_PUMP_DIR tables='sales.XXPOS_NEW_ORDER_LINES_STG' DUMPFILE=XXPOS_NEW_ORDER_LINES_STG.dmp

Reference Video

https://www.youtube.com/watch?v=QmFsC_3tSgU

EXPDP / IMPDP Schema in oracle database

Export Command

expdp system/manager schemas=PMS directory=DATA_PUMP_DIR dumpfile=13JUL17PMS_SCHEMA.dmp logfile=13JUL17PMS_SCHEMA.log

Import Command

impdp system/clonemanager schemas=PMS directory=DATA_PUMP_DIR dumpfile=13JUL17PMS_SCHEMA.dmp logfile=13JUL17PMS_SCHEMA.log remap_schema=PMS:PMS

Source : CRP1

Destination : GST

I am Having PMS Schema in CRP1 Instance.

I need the same schema in GST Instance.

So First I am going to Export PMS schema

Step 1:

Connect as system/manager

desc ALL_DIRECTORIES;

select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from ALL_DIRECTORIES;

Find the DATA_PUMP_DIR Location

Step 2:

If You Want to create New DATAPUMP_DIR DIRECTORY

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/backup/DATAPUMP_DIR';

Step 3:

expdp system/manager schemas=PMS directory=DATA_PUMP_DIR dumpfile=13JUL17PMS_SCHEMA.dmp logfile=test.log

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

Once PMS Schema Exported need to be import in GST instance.

Import the same schema in GST instance.

Step to remember,

In GST instance there is no schema available with name of PMS.

Now only i am going to Import.

Step 1:

desc ALL_DIRECTORIES;

select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from ALL_DIRECTORIES;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u03/CRP1GST/DATA_PUMP_DIR';

commit;

Before Import Schema Need to create the same TABLESPACE which exist in CRP1 instance for default PMS schema.

Step 2 :

CREATE TABLESPACE TBZ_CUSTOM DATAFILE '/u03/CRP1GST/db/apps_st/data/tbz_custom01.dbf' SIZE 2G;

CREATE TEMPORARY TABLESPACE TEMP_CUSTOM TEMPFILE '/u03/CRP1GST/db/apps_st/data/tbz_temp01.dbf' SIZE 500M AUTOEXTEND ON;

Step 3 :

impdp system/clonemanager schemas=PMS directory=DATA_PUMP_DIR dumpfile=13JUL17PMS_SCHEMA.dmp logfile=13JUL17PMS_SCHEMA.log remap_schema=PMS:PMS

EBS Currently connected user details

#to check users connected to the instance via toad;
================================================================;
select SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,SQL_ID,SQL_EXEC_START,PREV_EXEC_START,MODULE,ACTION,CLIENT_IDENTIFIER 
FROM V$SESSION  
WHERE  PROGRAM LIKE 'toad.exe%';

#to check users connected to EBS;
=================================;
select distinct fu.user_name User_Name, fu.email_address emailid,
fu.description, TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed", ic.nls_territory, FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME", fr.RESPONSIBILITY_KEY "Responsibility Key", ic.function_type,ic.time_out, fu.user_id, fu.employee_id,
ic.responsibility_application_id, ic.responsibility_id, ic.org_id, ic.counter, fr.menu_id, ic.disabled_flag
from apps.fnd_user fu,
apps.fnd_responsibility fr, apps.icx_sessions ic, apps.fnd_responsibility_VL FVL
where fu.user_id = ic.user_id AND
FR.RESPONSIBILITY_KEY=FVL.RESPONSIBILITY_KEY AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect > sysdate - (ic.time_out/60)/96
order by "Last Accessed" desc;

Create DB LINK

Create DB LINK

step 1:

connect as system user 

select * from dba_db_links;

step 2:

I am getting already created db link named below.

PUBLIC APPS.CHEMFABNT.COM APPS (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = caltest.chemfabnt.com)(PORT = 1562))(CONNECT_DATA = (SERVICE_NAME = TEST))) 01-DEC-16 12.40.39
DROP PUBLIC DATABASE LINK APPS;


step 3:

suppose i need to use existing db link or create new dblink

step 4:

now i am going to drop existing db link created by system user (db link name is PUBLIC)

DROP PUBLIC DATABASE LINK APPS;

step 5:

to create new db link (as a system user)

CREATE PUBLIC DATABASE LINK APPS  connect to apps identified by cloneapps USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = caltest.chemfabnt.com)(PORT = 1562))(CONNECT_DATA = (SERVICE_NAME = TEST)))';

step 6:

to create new db link ( as a another user)

APPS (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prodebs.chemfabnt.com)(PORT=1522)) (CONNECT_DATA= (SERVICE_NAME=PROD) (INSTANCE_NAME=PROD))) 22-JUN-16 10.27.51



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


select db_link from USER_DB_LINKS;


create database link XXCAL  connect to apps identified by cloneapps USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = caltest.chemfabnt.com)(PORT = 1562))(CONNECT_DATA = (SERVICE_NAME = TEST)))';

select CREATED,LOG_MODE,OPEN_MODE,ARCHIVELOG_CHANGE# from v$database@APPS;

desc V$database;

Database User check / Query

DBA USER Check
------------------------

SQL> select * from dba_users  where username='APPSREADONLY';

DROP User in Oracle Database

DROP USER
-----------------

DROP USER APPSREADONLY CASCADE;

 select * from dba_users  where username='APPSREADONLY';

 DROP USER APPSREADONLY CASCADE;

 commit;

tkprof steps

How to take tkprof

Step 1: 

source db environment file and go to

/u01/TEST/db/tech_st/12.1.0/admin/TEST_r12fwtest/diag/rdbms/test/TEST/trace

Step 2: 

Syntax


tkprof source.trc /u01/***/***.prf explain=apps/******* sys=no sort='(prsela, exeela, fchela)' 

tkprof TEST_ora_20967_CHAINSYS.trc /u01/scripts/TEST_ora_20967_CHAINSYS.prf explain=apps/******* sys=no sort='(prsela, exeela, fchela)' 

Read only User Create in Oracle Database

1. Create user:

create user APPSREADONLY identified by APPSREADONLY123 default tablespace APPS_TS_TX_DATA;


2. Create Role:

Create role readrole;
Grant create session to readrole;
Grant connect to readrole;
Grant select any table to readrole;
Grant execute any procedure to readrole;
Grant create trigger to readrole;

3. Grant role to user:

Grant readrole to APPSREADONLY;

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

If User need to have an existing user privelegemeans run below query
Eg.
I am creating now APPSREADONLY user and this user having all the priveleges of APPS user.


4. Create trigger:

Login as APPSREADONLY and create the following trigger:

Create or replace trigger rlogin_trigger
After logon on APPSREADONLY.schema
declare
Begin
EXECUTE IMMEDIATE 
        'declare begin ' ||
        'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

Schema / User Creation in Oracle Database

Schema Creation in Oracle Database
---------------------------------------------

-- TO CREATE TABLESPACE

CREATE TABLESPACE TBS_XXAPLDALL DATAFILE 'datafile_name.dbf' SIZE 3G;

CREATE TEMPORARY TABLESPACE TEMP_XXAPLDALL TEMPFILE 'tempfile_name.dbf' SIZE 5M AUTOEXTEND ON;

-- TO CREATE SCHEMA

CREATE USER XXAPLDALL IDENTIFIED BY XXAPLDALL DEFAULT TABLESPACE TBS_XXAPLDALL TEMPORARY TABLESPACE TEMP_XXAPLDALL PROFILE DEFAULT ACCOUNT UNLOCK;

-- TO CREATE SESSION

GRANT CREATE SESSION TO systemappsil;

-- TO ALTER SESSION

GRANT ALTER SESSION TO systemappsil;

-- TO CREATE SYNONYM

GRANT CREATE SYNONYM TO systemappsil;

-- TO CREATE TABLE

GRANT CREATE TABLE TO systemappsil;

-- TO CREATE VIEW

GRANT CREATE VIEW TO systemappsil;

-- TO CREATE SEQUENCE

GRANT CREATE SEQUENCE TO systemappsil;

-- TO CREATE TRIGGER 

GRANT CREATE TRIGGER TO systemappsil;

-- TO CREATE PROCEDURE

GRANT CREATE PROCEDURE TO systemappsil;

-- TO UNLIMIT TABLESPACE

GRANT UNLIMITED TABLESPACE TO systemappsil;

-- TO GIVE THE EDITIONS:

ALTER USER XXAPLDALL ENABLE EDITIONS;

-- TO GIVE THE "ENQUEUE","DEQUEUE" PRIVILEGE

begin
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'ENQUEUE_ANY',
        GRANTEE => 'XXAPLDALL',
        ADMIN_OPTION => FALSE);

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'DEQUEUE_ANY',
        GRANTEE => 'XXAPLDALL',
        ADMIN_OPTION => FALSE);
        end ;

-- TO CREATE EXECUTE PRIVILEGE

grant execute any procedure to XXAPLDALL;


*************************************************************************************************************
USER CREATION LIKE APPS/SYSTEM :

CREATE OR REPLACE TRIGGER readonly_logon_trg
AFTER logon ON systemappsil.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 
        'declare begin ' ||
        'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =SYSTEM';
END;
/

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