Query to find runtime of a concurrent program

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

-- Query to find runtime for a concurrent program

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

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date -

           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

   AND rq.actual_start_date IS NOT NULL

   AND rq.actual_completion_date IS NOT NULL

   AND tl.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>

   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date

 ORDER BY rq.request_id DESC;

Bank Statement Loader Concurrent Request Is Taking A Long Time To Run

 

Bank Statement Loader Concurrent Request Is Taking A Long Time To Run

ACTUAL BEHAVIOR
---------------
We run a number of Bank Statement Loader concurrent requests on a daily basis (one for each bank account currency). Typically these have completed in approximately 2 minutes. They are now taking 30 minutes to complete slowing down the whole process of bank reconciliation. We have not made any system changes that would affect this.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Submit CESLRPROSWIFT940

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users experience performance issue

ISSUE

After upgrade to 12.2.10 with 19C database program is taking longer time as compare to 12.1.3 with 12C.

In old environment it was taking approx. 1 minute where as in new environment taking approx. 5 minute

SOLUTION

1- Connect to CDB Instance and take backup of "SYS"."_ALL_SYNONYMS_TREE"

2-  Recreate the view "SYS"."_ALL_SYNONYMS_TREE" with a new SELECT that includes a "hint".

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."_ALL_SYNONYMS_TREE"

("SYN_OWNER", "SYN_SYNONYM_NAME", "SYN_TABLE_OWNER", "SYN_TABLE_NAME", "SYN_DB_LINK", "ORIGIN_CON_ID") AS

select /*+ no_connect_by_filtering no_connect_by_combine_sw */

s.syn_owner, s.syn_synonym_name, s.syn_table_owner,

s.syn_table_name, s.syn_db_link, s.origin_con_id

from sys."_ALL_SYNONYMS_FOR_SYNONYMS" s

/* user has any privs on ultimate base object */

start with exists (

select /*+ NO_PUSH_SUBQ */ null

from sys."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" sa

where s.syn_table_owner = sa.owner

and s.syn_table_name = sa.synonym_name

)

connect by nocycle prior s.syn_owner = s.syn_table_owner and

prior s.syn_synonym_name = s.syn_table_name;

3- Backup file $CE_TOP/bin/SWIFT940.ctl . Use the following OS Command

$ cp $CE_TOP/bin/SWIFT940.ctl SWIFT940_CoE000.ctl

4- Edit file $CE_TOP/bin/SWIFT940.ctl

5- Update every occurrence of "ce_stmt_int_tmp" to "CE.ce_stmt_int_tmp". Notice we prefix the schema CE.

6- Run the SWIFT940 Loader program again

EBS PDB service name disappear from listener in 19c

 EBS 19c service name issue

Cause: 

1. Misconfiguration with the listener and registration of services:

2. Adop patching cycle will not work.

Solution:-

1. In 19c database service_name parameter should be set to container name   

2. Source CBD ENV.

3. Close PDB instance.

SQL>  alter pluggable database all close instances=all;

Pluggable database altered.

SQL>  alter system set service_names='CDBSID' scope=both sid='*';

System altered.

4.  Stop database.

5. Start database.

Kill all FNDLIBR processes | EBS

Kill all processes of FNDLIBR.

ps -ef |grep FNDLIBR | grep -v grep | awk '{print $2}' | xargs kill -9

Kill all processes of java.

ps -ef |grep java | grep -v grep | awk '{print $2}' | xargs kill -9

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_.

Error:- 

"An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_<hostname>. There may be a network configuration problem, or the TNS listener on node FNDFS_<hostname> may not be running. Please contact your system administrator."

Environment:-

1) Two node database and two application (PCP)

Changes:-

1) install the latest rpm on the database and application server.

Yum update.

Issue:-

1) After reboot all database and application services started.

2) not able to see output and logs from the front end.

3) All application services started without error.

Solution:-

1) Check FNDSM listener status working fine.

2) (Active user) output and logs and generated on the backend server however not able to see output.

3) Run auto config database and application.

4) You can't get rid of FNDWRR (viewing concurrent log and out) problems, if you have a hostname with capital letters.

 $EBS_APPS_DEPLOYMENT_DIR/oacore/APP-INF/node_info.txt

5) After performing the above steps the issue is on the firewall issue and finally the issue is resolved.

How to unlock user accounts in Oracle APEX

Many times user APEX Work-space accounts gets locked on few wrong attempts or any other reasons.

Solution:-

[oracle@ebstest apex]$ sqlplus / as sysdba

SQL> declare

  2    n_security_group apex_workspaces.WORKSPACE_ID%type;

  3  begin

  4    SELECT workspace_id

    INTO n_security_group

    FROM apex_workspaces

   WHERE workspace = 'workspace';

  wwv_flow_api.set_security_group_id(n_security_group);

 10    APEX_UTIL.UNLOCK_ACCOUNT (p_user_name => 'Username');

 11    commit;

 12  end;

 13  /

PL/SQL procedure successfully completed.

SQL>

shared folder not showing up virtualbox

Virtual machine

OS:- 6.6 Linux

Error:-

Shared folder not showing up VirtualBox.

Solution:-

1- Download VMware tool patch from below link.

https://drive.google.com/file/d/1vasV-ATmdDUAxp2x96mzAPGuvVFNF77s/view?usp=sharing

2- unzip the vmware-tools-patches-master.zip

3- Run below commands(root user).

a- ./patched-open-vm-tools.sh

b- ./download-tools.sh latest

c- ./untar-and-patch.sh

d- ./compile.sh

4- able to see shared folder .

[root@ebstest apps]# cd /mnt/hgfs/

[root@ebstest hgfs]# ls

Sandbox_backup

[root@ebstest hgfs]# pwd

/mnt/hgfs

[root@ebstest hgfs]#

How To Run An Empty Patching Cycle Without Applying A Patch

 

[applmgr@test scripts]$ adop phase=prepare,finalize,cutover,cleanup

How to apply language patches after the main patch has been applied EBS r12.2

1- Apply main patch using below command.

time adop phase=apply patches=31625670 

2- To apply language patches.

time adop phase=apply patches=31625670_D:u31625670.drv,31625670_DK:u31625670.drv,31625670_ESA:u31625670.drv,31625670_F:u31625670.drv,31625670_I:u31625670.drv,31625670_NL:u31625670.drv,31625670_ZHS:u31625670.drv,31625670_ZHT:u31625670.drv  workers=16 wait_on_failed_job=yes flags=autoskip

Presentation Catalog Permission Behavior When Same EBS User Logs Into OBIEE Using Different Responsibilities Back And Forth

What happens is the following:

• User selects a responsibility '<respA>' in EBS, which is mapped to an OBIEE Application Role '<respA>' and is logged in to OBIEE with SSO. The user gets the '<respA>' Application Role in OBIEE
• User logs off using the 'Sign Out' link which returns the user back to EBS
• User selects a different responsibility, '<respB>', but after login to OBIEE the Application Role of the previous session remains active '<respA>', instead of using the new application role '<respB>'

Disable connection pooling:

1- For each Connection Pool in the OBIEE rpd (repository) file, uncheck the "Enable connection pooling" option.

2- Save the rpd and distribute it to the server.

 

ADOP fails with [ERROR] ETCC not run in the database node DBHOSTNAME in R12.2

 Error:-

1- adop phase=prepare.

[ERROR]     ETCC not run in the database node prerptdb01
                The EBS Technology Codelevel Checker needs to be run on the database node.
                It is available as Patch 17537119.
    Encountered the above errors when performing database validations.
    Resolve the above errors and restart adop 

Solution:-

1- It might be due to mismatch in host name between APPS.FND_NODES and APPLSYS.TXK_TCC_RESULTS tables.

2-  Take backup of APPLSYS.TXK_TCC_RESULTS

create table APPS.TXK_TCC_RESULTS_backup as select * from APPLSYS.TXK_TCC_RESULTS;

3- Truncate APPLSYS.TXK_TCC_RESULTS table.

truncate table  APPLSYS.TXK_TCC_RESULTS

4-  UPDATE applsys.txk_tcc_results SET node_name ='<node name>';

Upgrade to Oracle Database 19c from 12c using autoupgrade

 1- Download the most recent version from MOS Note: 2485457.1 – AutoUpgrade Tool.

2- Create sample Auto-Upgrade config file.

java -jar $OH19/rdbms/admin/autoupgrade.jar -create_sample_file config

3- Edit Sample file.

[oracle@dbinstance ~]$ vi sample_config.cfg
[oracle@dbinstance ~]$ vi sample_config.cfg
[oracle@dbinstance ~]$ more sample_config.cfg
# $Header: rdbms/src/server/upgrade/autoupgrade/src/main/resources/autoupgrade/templates/sample_config_unix.properties /st_rdbms_pt-autoupgrade/7 2018/07/11 09:38:
27 frealvar Exp $
#
# Copyright (c) 2017, 2018, Oracle and/or its affiliates.
# All rights reserved.*/
#
#
# DESCRIPTION
# This is a template for config file to be used with autoupgrade tool.
#
# NOTES
# <other useful comments, qualifications, etc.>
#
# MODIFIED    (MM/DD/YY)
# frealvar    07/10/18 - code refactor due to AUPG-250
# frealvar    04/30/18 - AUPG-189 optionally run utlrp and timezone upgrades
# fvallin     04/06/17 - Creation
#
#
#Global configurations
#Autoupgrade's global directory, non-job logs generated,
#temp files created and other autoupgrade files will be
#send here
global.autoupg_log_dir=/home/oracle/upgrade_logs

#
# sample config file
#
#
 Database number 1
#
upg1.dbname=PROD
upg1.start_time=NOW
upg1.source_home=/u02/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.sid=PROD
upg1.log_dir=/home/oracle/logs
upg1.upgrade_node=localhost
upg1.target_version=19.3
#upg1.run_utlrp=yes
#upg1.timezone_upg=yes
#

4- Make one directory called upgrade_logs and logs which will hold all the config files, log files during this upgrade.

[oracle@dbinstance ~]$ mkdir -p /home/oracle/upgrade_logs

[oracle@dbinstance ~]$ mkdir -p /home/oracle/logs

[oracle@dbinstance ~]$ cp sample_config.cfg UPGR.cfg

5- Running AutoUpgrade.jar

ANALYZE mode.

[oracle@dbinstance ~]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config /home/oracle/UPGR.cfg -mode analyze
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Enter some command, type 'help' or 'exit' to quit
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
|JOB#|DB NAME|    STAGE|OPERATION| STATUS|    START TIME|END TIME| UPDATED|       MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
| 100|   PROD|PRECHECKS|PREPARING|RUNNING|20/05/18 15:11|     N/A|15:11:09|Remaining 0/75|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
Total jobs 1
upg>
Job 100 for PROD FINISHED
[oracle@dbinstance ~]$

upg> status -job 102

Progress

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

Start time:      20/05/18 15:13

Elapsed (min):   122

End time:        N/A

Last update:     2020-05-18T17:15:05.211

Stage:           POSTFIXUPS

Operation:       EXECUTING

Status:          RUNNING

Pending stages:  2

Job Logs Locations

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

Logs Base:    /home/oracle/logs

Job logs:     /home/oracle/logs/102

Stage logs:   /home/oracle/logs/102/postfixups

TimeZone:     /home/oracle/logs/temp

Additional information

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

Details:

Starting FIXUPS execution

Error Details:

None

upg>

Job 102 for PROD FINISHED

[oracle@dbinstance ~]$

6- Check below html file under /home/oracle/upgrade_logs and fix the errors.

7- Deploy mode.

[oracle@dbinstance ~]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config /home/oracle/UPGR.cfg -mode deploy
Previous execution found
loading latest data
Total jobs recovered: 1
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
Enter some command, type 'help' or 'exit' to quit
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
|JOB#|DB NAME|    STAGE|OPERATION| STATUS|    START TIME|END TIME| UPDATED|MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
| 102|   PROD|PRECHECKS|PREPARING|RUNNING|20/05/18 15:13|     N/A|16:12:07|       |
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
|JOB#|DB NAME|    STAGE|OPERATION| STATUS|    START TIME|END TIME| UPDATED|       MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
| 102|   PROD|PRECHECKS|PREPARING|RUNNING|20/05/18 15:13|     N/A|16:12:14|Remaining 1/75|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
Total jobs 1
upg> lsj

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+--------+-----------+

|JOB#|DB NAME|    STAGE|OPERATION| STATUS|    START TIME|END TIME| UPDATED|    MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+--------+-----------+

| 102|   PROD|DBUPGRADE|EXECUTING|RUNNING|20/05/18 15:13|     N/A|16:35:28|0%Upgraded |

+----+-------+---------+---------+-------+--------------+--------+--------+-----------+

Total jobs 1

upg> status

---------------- Config -------------------

User configuration file    [/home/oracle/UPGR.cfg]

General logs location      [/home/oracle/upgrade_logs/cfgtoollogs/upgrade/auto]

Mode                       [DEPLOY]

DB upg fatal errors        ORA-00600,ORA-07445

DB Post upgrade abort time [60] minutes

DB upg abort time          [1440] minutes

DB restore abort time      [120] minutes

DB drop GRP abort time     [3] minutes

------------------- Jobs -------------------

Total databases in configuration file [1]

Total Non-CDB being processed         [1]

Total CDB being processed             [0]

Jobs finished successfully            [0]

Jobs finished/aborted                 [0]

jobs in progress                      [1]

------------ Resources ----------------

Threads in use                        [62]

JVM used memory                       [105] MB

CPU in use                            [13%]

Processes in use                      [18]

8- Completion Message.

upg>

Job 102 for PROD FINISHED

9- Check the database version

[oracle@dbinstance ~]$ export ORACLE_SID=PROD

[oracle@dbinstance ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 18 17:43:37 2020

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 select name from v$database;

NAME

---------

PROD

HOW TO CREATE TKPROF FILE IN ORACLE APPS R12

 Step 1 :- To find Trace file location using Request id

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID
    AND req.oracle_process_id=proc.spid(+)
    AND proc.addr = ses.paddr(+)
    AND dest.NAME='user_dump_dest'
    AND dbnm.NAME='db_name'
    AND req.concurrent_program_id = prog.concurrent_program_id
    AND req.program_application_id = prog.application_id
    AND prog.application_id = execname.application_id
    AND prog.executable_id=execname.executable_id;

Step2:- RUN TKPROF Command

tkprof PROD_ora_61669742_0132386_1001_172443.trc PROD_ora_61669742_0132386_1001_172443.out sys=no explain=apps/password sort=prsela,exeela,fchela

Configuring and Managing Oracle E-Business Suite Release 12.2.x Forms and Concurrent Processing for Oracle RAC

 Configuring and Managing Oracle E-Business Suite Release 12.2.x Forms and Concurrent Processing for Oracle RAC (Doc ID 2029173.1)

Step 1:- No active ADOP cycle

adop -status

Step 2:- Create Services (Database)

srvctl add service -db vis_a -service UATERP_OACORE_ONLINE -preferred "UATERP1,UATERP2" -notification TRUE -role "PRIMARY,SNAPSHOT_STANDBY" -failovermethod BASIC -failovertype SELECT -failoverretry 10 -failoverdelay 3

srvctl add service -db vis_a -service UATERP_PCP_BATCH1 -preferred "UATERP1" -notification TRUE -role "PRIMARY,SNAPSHOT_STANDBY" -failovermethod BASIC -failovertype SELECT -failoverretry 10 -failoverdelay 3

srvctl add service -db vis_a -service UATERP_PCP_BATCH2 -preferred "UATERP2" -notification TRUE -role "PRIMARY,SNAPSHOT_STANDBY" -failovermethod BASIC -failovertype SELECT -failoverretry 10 -failoverdelay 3

Step 3:- Check the configuration

select inst_id,service_name, count(*)

from gv$session

where service_name not like 'SYS%'

group by inst_id,service_name

order by 1

/

srvctl config service -d UATERP -s UATERP_OACORE_ONLINE 

srvctl config service -d UATERP -s UATERP_PCP_BATCH1

srvctl config service -d UATERP -s UATERP_PCP_BATCH2

Step 4:- Configure Net Services

lsnrctl status UATERP1

lsnrctl status UATERP2

lsnrctl status listener_scan1

lsnrctl status listener_scan2

lsnrctl status listener_scan3

Step 5:- Create a TNS alias for database service

UATERP_OACORE_ONLINE =(DESCRIPTION=(ADDRESS_LIST= (FAILOVER=YES) (ADDRESS=(PROTOCOL=tcp)(HOST=<SCAN IP1>)(PORT=<SCAN PORT>)) (ADDRESS=(PROTOCOL=tcp)(HOST=<SCAN IP2>)(PORT=<SCAN PORT>)) (ADDRESS=(PROTOCOL=tcp)(HOST=<SCAN IP3>)(PORT=<SCAN PORT>)) (CONNECT_DATA= (SERVICE_NAME=UATERP_OACORE_ONLINE)))

"Create Service UATERP_OACORE_ONLINE a and add it to $TNS_ADMIN/<%s_dbSid%>_<%s_hostname%>_ifile.ora file Add tnsentry on both database node."

Step 6:- Copy the default database connection file

"Copy the default database connection file, which is normally $FND_SECURE/DB_NAME.dbc to the database service UATERP_OACORE_ONLINE.dbc"

Step 7:- Context File backup

Step 8:- Edit the context file on each application tier node

s_apps_jdbc_connect_descriptor = <jdbc_url oa_var="s_apps_jdbc_connect_descriptor">jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=exaa-scan3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=UATERP_OACORE_ONLINE )))</jdbc_url>  

s_jdbc_connect_descriptor_generation - false

s_tools_twotask - UATERP_OACORE_ONLINE

s_cp_twotask - UATERP_PCP_BATCH1

s_cp_twotask - UATERP_PCP_BATCH2

Step 9:- Run autoconfig primary and secondary node of application

Step 10:- Stop application services and database services

Step 11:- Start database services and application services

Step 12- HA testing

srvctl relocate service -d <db_name> -s <service_name> -i <instance1> -t <instance2>

srvctl modify service -d <db_name> -s <service_name> -n -i <preferred_list> -a <available_list>

srvctl modify service -d prod -s TestService1 -n -i prod2,prod4 -a prod1,prod3

FRM-92101:There was a failure in the Forms Server during startup" Error When Attempting to Launch Forms

To fix the issue, follow the steps below from your local desktop to clear your cache:

Clearing Java Cache (Part 1)

 1- Go to Control Panel, the Control Panel can be accessed by searching for the control panel using the search box at the bottom of your screen.

2- From the Control Panel Screen, select click the word Program. 

3- Select Java (32-Bit).

4- On the general tab, under temporary internet files, click settings. 

5- Click Delete Files

6- Make sure the Trace and Log Files, Cached Applications and Applets, ad Installed Applications and Applets boxes are checked.  Then click OK

Clearing Internet Explorer Cache (Part 2)

1- You may also want to clear your Internet Explorer Cache.  To do so, click on the gear icon on the top right-hand corner of your browser.

2- Place your cursor over Safety to populate a drop down box, then select Delete Browsing History.

3- Uncheck the box next to Preserve Favorites website data, and make sure the boxes next to Temporary Internet files and website files and Cookies and website data are checked.  Then click delete        

4- A box will appear at the bottom of your screen confirming that your browsing history has been deleted.

5- Log back into the R12 Environment

 

How To Change The Web Port in E-Business Suite R12.2

Change The Web Port {http port} in E-Business Suite R12.2 

In this post i will list out the steps to change the webport to 8009.

Defualt port - 8000

change port - 8009

Solution:-

1- Login to Em console - using http://hostname:port/em

2- make sure oracle apahce Server is up and running.

3- Select the webtier instance from the Web Tier drop down.

4- Select the Oracle HTTP server component and Advanced Configuration.

5- Choose httpd.conf for example.

6- Modify the Listen port value

7- Save the changes.

8-  Run Context File Synchronization $AD_TOP/bin/adSyncContext.pl :

perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

9 - Bring down application services.

10- Run autoconfig at application.

11- Check the value for s_login_page in the context file.

 grep login $CONTEXT_FILE

 grep 8000 $CONTEXT_FILE

12- Start application services and perform sanity test.

How to Check the table Size in Oracle

• To check table name segment type, owner and table size in GB  

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments  where owner='GL' and segment_type='TABLE' and segment_name in

('GL_LEDGERS',

'GL_JE_LINES',

'GL_CODE_COMBINATIONS',

'GL_CODE_COMBINATIONS_KFV',

'GL_PERIODS',

'GL_JE_HEADERS',

'RA_CUST_TRX_LINE_GL_DIST_ALL',

'GL_BUDGET_VERSIONS',

'GL_BUDGETS',

'GL_PERIOD_TYPES',

'GL_BALANCES',

'GL_JE_BATCHES')

group by owner,segment_name order by "SIZE in GB" desc;

• To check table name segment type table size in GB .

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where segment_type='TABLE' and segment_name in

('MTL_TRANSACTION_ACCOUNTS',

'MTL_SYSTEM_ITEMS_B',

'MTL_CATEGORIES_B',

'JTF_RS_GROUPS_B',

'JTF_RS_RESOURCE_EXTNS',

'IBY_PAYMENT_METHODS_TL',

'PO_LINES_ALL',

'PO_HEADERS_ALL',

'RA_CUST_TRX_LINE_GL_DIST_ALL',

'RA_CUSTOMER_TRX_ALL',

'RA_TERMS_B',

'XLA_AE_LINES',

'XLE_ENTITY_PROFILES',

'XLA_AE_HEADERS',

'XLA_DISTRIBUTION_LINKS',

'XLA_CONTROL_BALANCES',

'XLA_TRANSACTION_ENTITIES',

'XLA_AE_LINE_ACS',

'XLA_AC_BALANCES')

group by owner,segment_name order by "SIZE in GB" desc;

shrink Datafiles and Reclaim unused Space in Oracle

Space issue:-

Found space utilization at data file mount point on prod.

/dev/mapper/datavg-lvol0 404G  365G   19G  96% /saprod_data01   

Avail Use% Mounted on

/dev/mapper/datavg-lvol1 404G  372G   12G  97% /saprod_data02

Solution:-

Step1:- verify 

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Step 2:- Output of the script

--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       624    584       40
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     18704  17799      905
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        64     16       48
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32704  32697        7
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32704  32641       63
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m       100     18       82
                ds.dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d      2048    878     1170
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32767  32726       41
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0       100     81       19
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      3072   2120      952
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip        64      2       62
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds       100      6       94
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      3072   1591     1481
                .dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      5120   3264     1856
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     23020  22273      747
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

By the output i can reclaim space around  7.783g of space

Step3:-Run below query 

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

Step4:- Output

SQL

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

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwstage.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwindex.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index02.dbf' resize 5M;

/* reclaim    2043M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index01.dbf' resize 5M;

/* reclaim    1857M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/system01.dbf' resize 3264M;

/* reclaim    1482M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/sysaux01.dbf' resize 1591M;

/* reclaim    1171M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dacrep_data01.dbf' resize 878M;

/* reclaim     953M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/infarep_data01.dbf' resize 2120M;

/* reclaim     905M from   18704M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_odi.dbf' resize 17800M;

/* reclaim     748M from   23020M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/undotbs01.dbf' resize 22273M;

/* reclaim     152M from   31744M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/dwh_data05.dbf */

SQL

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

/* reclaim      95M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_mds.dbf' resize 6M;

/* reclaim      83M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_mds.dbf' resize 18M;

/* reclaim      64M from   10240M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwdata5.dbf */

/* reclaim      64M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata2.dbf' resize 32641M;

/* reclaim      63M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_biplatform.dbf' resize 2M;

/* reclaim      48M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biplatform.dbf' resize 17M;

/* reclaim      42M from   32767M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_data07.dbf' resize 32726M;

/* reclaim      41M from     624M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biacomp.dbf' resize 584M;

/* reclaim      19M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/example01.dbf' resize 82M;

/* reclaim       8M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata1.dbf' resize 32697M;

21 rows selected.

SQL>

Step5:- Resize all datafile using above output.

Step6:-Execute step1 query to check changes after resizing of datafiles.

Tablespace      FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       584    584        0
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     17800  17799        1
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        17     16        1
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32761  32697       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32705  32641       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m        18     18        0
                ds.dbf
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d       878    878        0
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32726  32726        0
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0        82     81        1
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      2120   2120        0
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip         2      2        0
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds         6      6        0
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      1731   1591      140
                .dbf
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      3264   3264        0
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     22273  22273        0
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

In earlier output possible savings is 7.783g , Now possible savings is 356mb, So we reclaimed 7gb of space at OS level

Step6:- check space at os level

/dev/mapper/datavg-lvol0

                      404G  341G   43G  89% /saprod_data01

/dev/mapper/datavg-lvol1

                      404G  360G   24G  94% /saprod_data02

/dev/mapper/datavg-lvol2