Archive for the ‘Oracle’ Category

Query To Find Legal Entity Organization Company Code

February 3rd, 2020, posted in Oracle Queries
Share
SELECT
xep.legal_entity_id "Legal Entity ID",
xep.name "Legal Entity",
hr_outl.name "Organization Name",
hr_outl.organization_id "Organization ID",
hr_loc.location_id "Location ID",
hr_loc.country "Country Code",
hr_loc.location_code "Location Code",
glev.flex_segment_value "Company Code"
FROM
xle_entity_profiles xep,
xle_registrations reg,
--
hr_operating_units hou,
-- hr_all_organization_units hr_ou,
hr_all_organization_units_tl hr_outl,
hr_locations_all hr_loc,
--
gl_legal_entities_bsvs glev
WHERE
1=1
AND xep.transacting_entity_flag = 'Y'
AND xep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND xep.legal_entity_id = hou.default_legal_context_id
AND reg.location_id = hr_loc.location_id
AND xep.legal_entity_id = glev.legal_entity_id
--
-- AND hr_ou.organization_id = hou.business_group_id
AND hr_outl.organization_id = hou.organization_id
ORDER BY hr_outl.name


Share

SQL for Active Banks and Branches in EBS R12

January 21st, 2020, posted in Oracle Queries
Share
SELECT BankOrgProfile.*SQL for Active Banks and Branches in EBS R12,SQL for Active Banks and Branches in Oracle EBS R12,SQL for Active Banks and Branches in Oracle,Oracle Query,Oracle DBA,DBA Query,SQL for Active Bank and Branche in Oracle,

FROM HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_CODE_ASSIGNMENTS BankCA

WHERE 1 = 1

AND SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)

AND NVL (TRUNC (BankOrgProfile.effective_end_date),

SYSDATE + 1)

AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'

AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')

AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND (BankCA.STATUS = 'A' OR BankCA.STATUS IS NULL)

AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID :
Share

EXP-00111

January 12th, 2020, posted in Oracle
Share

ERROR :
EXP-00111: Table WORKER resides in an Encrypted Tablespace sujeet and will not be exported


Cause :
Encrypted Tablespace feature is not supported by classic export


Action :
Use DATAPUMP Utility to export this table.

Share

NID-00135: There are number active threads.

December 29th, 2019, posted in Oracle Queries
Share

NID-00135: There are number active threads.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD      MOUNTED

[oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES

DBNEWID: Release 11.1.0.7.0 - Production on Thu Apr 26 18:04:18 2018

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

Connected to database ACSEBSP (DBID=2199446376)

Connected to server version 11.1.0

NID-00135: There are 1 active threads


Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Solution :

Action:  Ensure that all threads are closed before retrying the operation. Start and open the database to perform crash recovery, then shut down with the NORMAL or IMMEDIATE options to close it cleanly. Finally, try running the utility again.

 

SQL> alter database open;
Database altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.



[oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES
Share

apps-fnd-01564 oracle error 1653 in summit others

December 22nd, 2019, posted in Oracle Queries
Share

CAUSE :

Typically occurs when writing a new record to oracle database, but there is not enough space to write it. The data file in oracle tablespace called APPS_TS_TX_DATA should be set to AUTOEXTEND. If it is not, Oracle will not auto-increase the size of the datafile, and will raise the above error.

apps-fnd-01564 oracle error 1653 in summit others,apps-fnd-01564,oracle error,imam dba,dba imam,immam dba,dba immam,

SOLUTION :

If all the reports end in COMPLETE – NORMAL, means its work.

If not than follow below steps.

Run a Concurrent Manager Recovery :

1. Please stop Concurrent managers by adcmctl.sh.

2. If not stopped, please abort managers by adcmctl.sh.

adcmctl.sh abort apps/****

3. Please kill if a process remains.

4. relink all (ADADMIN)

5. Please retry Concurrent Manager Recovery.

Navigate :
Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery.

OR

SOLUTION :

WARNING: This procedure should be performed by your ORACLE Database Administrator.
The SYSTEM user should have required privileges to perform this task.

Enable Autoextend on the APPS_TS_TX_DATA tablespace by executing the following Oracle PL/SQL command:

SQL > ALTER DATABASE DATAFILE '/u01/ORACLE/ORADATA/APPS_TS_TX_DATA.ORA' AUTOEXTEND ON NEXT 3072K

 

Share