SELECT BankOrgProfile.*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 :
Posts Tagged ‘Oracle DBA’
SQL for Active Banks and Branches in EBS R12
January 21st, 2020, posted in Oracle QueriesNID-00135: There are number active threads.
December 29th, 2019, posted in Oracle QueriesNID-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
ORA-28014: Cannot Drop Administrative Users
December 2nd, 2019, posted in Oracle QueriesORA-28014: cannot drop administrative users
The below error is encountered when I was trying to drop schema from 12C database :
SQL> drop user BKP CASCADE; drop user BKP CASCADE * ERROR at line 1: ORA-28014: cannot drop administrative users Solution :
SQL> alter session set "_oracle_script"=true; Session altered. SQL> drop user BKP CASCADE; User dropped.
DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
November 24th, 2019, posted in Oracle QueriesCustomer Recommended Datapump Export (expdp) Raises Warnings Like “DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled” (Doc ID 1638799.1)
APPLIES TO :
Oracle Database – Enterprise Edition – Version 11.2.0.4 and later
Oracle Database Cloud Schema Service – Version N/A and later
Oracle Database Exadata Express Cloud Service – Version N/A and later
Oracle Database Exadata Cloud Machine – Version N/A and later
Oracle Cloud Infrastructure – Database Service – Version N/A and later
Information in this document applies to any platform.
SYMPTOMS :
Expdp on Oracle Standard Edition / Standard Edition 1 produces below warnings
DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
CAUSE :
The issue was explained in
Bug 17718899 – EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES “OLAP NOT ENABLED”
closed with status ‘Not a Bug’ with below comment:
OLAP is not available in Standard Edition One (SE1) or Standard Edition (SE) as per the Database Licensing Information document,
it is only available in Enterprise Edition (EE).
This message is intentionally output for DATAPUMP and coded as such in 11.2.0.4 and higher Standard Edition One (SE1) or Standard Edition (SE).
From the Datapump perspective these are warnings and it is safe to ignore them.
NOTE :
The same symptoms can be encountered with the Oracle Enterprise Edition when OLAP is not installed.
SOLUTION :
The OLAP related warnings can be safely ignored for DataPump usage.
REFERENCES :
BUG:17718899 – EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES “OLAP NOT ENABLED”