我从哪里获取 Oracle 网格软件以在 Oracle 23c 上运行 ASM。我已经为 19 和其他版本做过了，但找不到 23ai 的网格。
我有一个 Oracle 存储过程，它接受一堆参数并且大量执行左外连接，如下所示：
create or replace PROCEDURE "SOME_PROCEDURE" (
cRECORDSET OUT SYS_REFCURSOR,
iPHY_YEAR IN SOME_PROCEDURE_TYPE.PHYSICAL_YEAR % TYPE,
iPHYSICAL_DOC_TYPE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE % TYPE,
iPHYSICAL_DOC_TYPE_NONSAP IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE_NONSAP % TYPE,
sREFERENCE_NO IN SOME_PROCEDURE_TYPE.REFERENCE_NO % TYPE,
sPHYSICAL_NO IN SOME_PROCEDURE_TYPE.PHYSICAL_NO % TYPE,
iLOCATION_ID IN SOME_PROCEDURE_TYPE.LOCATION_ID % TYPE,
iCONTAINER_ID IN SOME_PROCEDURE_TYPE.CONTAINER_ID % TYPE,
sCONTAINER_NUMBER IN SOME_PROCEDURE_TYPE.CONTAINER_NUMBER % TYPE,
dREGISTERED_DATE_FROM IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_FROM % TYPE,
dREGISTERED_DATE_TO IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_TO % TYPE,
iPHYSICAL_STATUS_ID IN SOME_PROCEDURE_TYPE.PHYSICAL_STATUS_ID % TYPE,
sDRAFT_FLAG IN SOME_PROCEDURE_TYPE.DRAFT_FLAG % TYPE,
sCANCEL_FLAG IN SOME_PROCEDURE_TYPE.CANCEL_FLAG % TYPE,
sPHYSICAL_REMARK IN SOME_PROCEDURE_TYPE.PHYSICAL_REMARK % TYPE,
sPHYSICAL_DOC_TITLE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TITLE % TYPE,
sFI_DOC_NO_FROM IN SOME_PROCEDURE_TYPE.FI_DOC_NO_FROM % TYPE,
sFI_DOC_NO_TO IN SOME_PROCEDURE_TYPE.FI_DOC_NO_TO % TYPE,
iCOMPANY_ID IN SOME_PROCEDURE_TYPE.COMPANY_ID % TYPE,
iFI_DOC_YEAR IN SOME_PROCEDURE_TYPE.FI_DOC_YEAR % TYPE,
sFI_DOC_TYPE IN SOME_PROCEDURE_TYPE.FI_DOC_TYPE % TYPE,
sIS_ASSET IN SOME_PROCEDURE_TYPE.IS_ASSET % TYPE,
sWORKFLOW_STATUS_CODE IN SOME_PROCEDURE_TYPE.WORKFLOW_STATUS_CODE % TYPE,
sPO_NO IN SOME_PROCEDURE_TYPE.PO_NO % TYPE,
sVENDOR_NAME IN SOME_PROCEDURE_TYPE.VENDOR_NAME % TYPE,
sCUSTOMER_NAME IN SOME_PROCEDURE_TYPE.CUSTOMER_NAME % TYPE,
dPOSTING_DATE_FROM IN SOME_PROCEDURE_TYPE.POSTING_DATE_FROM % TYPE,
dPOSTING_DATE_TO IN SOME_PROCEDURE_TYPE.POSTING_DATE_TO % TYPE,
sCLEARING_DOC_NO IN SOME_PROCEDURE_TYPE.CLEARING_DOC_NO % TYPE,
iCLEARING_DOC_YEAR IN SOME_PROCEDURE_TYPE.CLEARING_DOC_YEAR % TYPE,
dCLEARING_DOC_DATE IN SOME_PROCEDURE_TYPE.CLEARING_DOC_DATE % TYPE,
sPAYMENT_BLOCK IN SOME_PROCEDURE_TYPE.PAYMENT_BLOCK % TYPE,
iAMOUNT_INC_VAT IN SOME_PROCEDURE_TYPE.AMOUNT_INC_VAT % TYPE,
sFI_DOC_REFNO IN SOME_PROCEDURE_TYPE.FI_DOC_REFNO % TYPE,
sSEND_TO IN SOME_PROCEDURE_TYPE.SEND_TO % TYPE,
sSAP_REVERSE_FLAG IN SOME_PROCEDURE_TYPE.SAP_REVERSE_FLAG % TYPE,
sCLEARING_FLAG IN SOME_PROCEDURE_TYPE.CLEARING_FLAG % TYPE,
iPAGE_SIZE IN SOME_PROCEDURE_TYPE.PAGE_SIZE % TYPE,
iPAGE_INDEX IN SOME_PROCEDURE_TYPE.PAGE_INDEX % TYPE,
iUSERID IN USR_DETAIL.USER_ID % TYPE
) IS
LAST_INDEX NUMBER;
USR_PERM_TYPE GET_USR_PERM_TYPE;
FIRST_INDEX NUMBER;
BEGIN
LAST_INDEX := iPAGE_SIZE * iPAGE_INDEX;
FIRST_INDEX := LAST_INDEX - iPAGE_SIZE + 1;
SELECT FN_GET_USR_PERM(iUSERID) INTO USR_PERM_TYPE FROM DUAL;
OPEN cRECORDSET FOR
WITH CTE_PHD_DETAIL AS (
SELECT
*
FROM
PHD_DETAIL
WHERE
USR_PERM_TYPE.VIS_MODE = 'ALL'
OR (
USR_PERM_TYPE.VIS_MODE = 'UNT'
AND (UNIT_ID = USR_PERM_TYPE.USUB_ID)
)
OR (
USR_PERM_TYPE.VIS_MODE = 'SUB'
AND SUB_UNIT_ID = USR_PERM_TYPE.USUB_ID
)
)
SELECT
PHD_ITEMS.RECORD_NUMBER,
PHD.PHYSICAL_ID,
PHD.PHYSICAL_DOC_TYPE,
PHD.PHYSICAL_NO,
PHD.REGISTERED_DATE,
PHD.CANCEL_FLAG,
PHD.DRAFT_FLAG,
CASE
WHEN PHD.REQUESTED_NO IS NOT NULL THEN
PHD.REQUESTED_NO ELSE PHD.TEXT_FILE_NAME
END REFERENCE_NO,
PHDS.PHYSICAL_STATUS_ID,
PHDS.PHYSICAL_STATUS_NAME,
CNT.CONTAINER_ID,
CASE
WHEN PHD.PHYSICAL_STATUS_ID = 2 THEN
NULL ELSE CNT.CONTAINER_NUMBER
END CONTAINER_NUMBER,
CNT.CONTAINER_TYPE,
CNTU.IS_DOC_HOUSE CONTAINER_IS_DOC_HOUSE,
CNTU.UNIT_ABBR CONTAINER_UNIT_ABBR,
LOC.LOCATION_ID,
CASE
WHEN PHD.PHYSICAL_STATUS_ID = 2
OR PHD.PHYSICAL_STATUS_ID = 12
OR PHD.PHYSICAL_STATUS_ID = 14 THEN
(
SELECT
SEND_TO
FROM
( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC )
WHERE
ROWNUM = 1
AND PHYSICAL_ID = PHD_ITEMS.PHYSICAL_ID
) --DECODE( PHDLG.SENT_TO,NULL , '',PHDLG.SENT_TO )
WHEN CNT.CONTAINER_TYPE = 1 THEN
CASE
WHEN CNT.CONTAINER_STATUS_ID = 2 THEN
CASE
WHEN CNT.CURRENT_UNIT_ID IS NOT NULL THEN
CNTU.UNIT_FULL_NAME ELSE CCOM.COMPANY_CODE || ' - ' || CCOM.COMPANY_NAME
END ELSE LOC.LOCATION_NAME
END ELSE LOC.LOCATION_NAME -- LOC.LOCATION_NAME
END LOCATION_NAME,
FIDS.WORKFLOW_STATUS_NAME,
FID.FI_DOC_ID,
FID.FI_DOC_NO,
FID.FI_DOC_YEAR,
CASE
WHEN COM_FID.COMPANY_ID IS NOT NULL THEN
COM_FID.COMPANY_CODE || ' - ' || COM_FID.COMPANY_NAME ELSE
CASE
WHEN COM_PHD.COMPANY_ID IS NOT NULL THEN
COM_PHD.COMPANY_CODE || ' - ' || COM_PHD.COMPANY_NAME ELSE ''
END
END AS COMPANY_FULL_NAME,
FID.FI_DOC_REFNO,
CASE
WHEN FID.FI_DOC_ID IS NULL THEN
PTEMP.AMOUNT_INC_VAT ELSE FID.AMOUNT_INC_VAT
END AMOUNT_INC_VAT,
CASE
WHEN FID.FI_DOC_ID IS NULL THEN
PTEMP.AMOUNT_CURRENCY ELSE FID.AMOUNT_CURRENCY
END AMOUNT_CURRENCY,
FID.IS_ASSET,
CASE
WHEN PHD_ITEMS.FI_DOC_ID IS NULL THEN
CASE
WHEN PTEMP.CUSTOMER_CODE IS NOT NULL THEN
PTCUS.CUSTOMER_FULL_NAME ELSE PTVEN.VENDOR_FULL_NAME
END ELSE
CASE
WHEN FID.FIRST_ITEMS = 'CUSTOMER' THEN
FID.CUSTOMER_NAME ELSE FID.VENDOR_NAME
END
END AS CUSTOMER_VENDOR_NAME,
PHD.PHYSICAL_REMARK
FROM
(
SELECT
ROW_NUMBER ( ) over ( ORDER BY PHD.PHYSICAL_NO DESC, FID_ID.FI_DOC_NO, PHDTEMP.PHD_TEMP_DATA_ID DESC ) RECORD_NUMBER,
PHD.PHYSICAL_ID,
PHD.PHYSICAL_NO,
FID_ID.FI_DOC_ID,
FID_ID.FI_DOC_NO,
PHDTEMP.PHD_TEMP_DATA_ID
FROM
(
SELECT DISTINCT
PHD.PHYSICAL_ID,
PHD.PHYSICAL_NO,
FID_ID.FI_DOC_ID,
PHDTEMP.PHD_TEMP_DATA_ID
FROM
CTE_PHD_DETAIL PHD
LEFT JOIN (
SELECT
t1.FI_DOC_ID,
t1.PHYSICAL_ID,
t2.fi_Doc_no,
t2.fi_doc_year
FROM
PHD_FID_RELATE_DOC t1
INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID UNION ALL
SELECT
t1.FI_DOC_ID,
t1.PHYSICAL_ID,
t2.fi_Doc_no,
t2.fi_doc_year
FROM
FID_SAP_PHYSICAL_HISTORY t1
INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID
) FID_ID ON FID_ID.PHYSICAL_ID = PHD.PHYSICAL_ID
LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHYSICAL_ID = PHDTEMP.PHYSICAL_ID
AND FID_ID.FI_DOC_ID
IS NULL LEFT JOIN PHD_STATUS_LOGS PHDSL ON PHD.PHYSICAL_ID = PHDSL.PHYSICAL_ID
LEFT JOIN PHD_TEMP_DOCTITLE PHDT ON PHD.PHYSICAL_ID = PHDT.PHYSICAL_ID
LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
LEFT JOIN FID_SAP_DETAIL FID ON FID_ID.FI_DOC_ID = FID.FI_DOC_ID
LEFT JOIN COM_DETAIL COM ON FID.COMPANY_CODE = COM.COMPANY_CODE
LEFT JOIN FID_SAP_CUSTOMER FCUS ON FID.FI_DOC_ID = FCUS.FI_DOC_ID
LEFT JOIN FID_SAP_VENDOR FVEN ON FID.FI_DOC_ID = FVEN.FI_DOC_ID
LEFT JOIN FID_SAP_CLEARING_DOC CLR ON FID.FI_DOC_ID = CLR.FI_DOC_ID
LEFT JOIN FID_SAP_PO PO ON FID.FI_DOC_ID = PO.FI_DOC_ID
LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
LEFT JOIN MAS_VENDOR PTVEN ON PHDTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
AND COM_PHD.SERVER_ID = COM_PHD.SERVER_ID
LEFT JOIN MAS_CUSTOMER PTCUS ON PHDTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
PHD.PHYSICAL_NO IS NOT NULL
AND (
(
(
( PHD.PHYSICAL_DOC_TYPE = 1 AND iPHYSICAL_DOC_TYPE = 1 )
OR ( ( PHD.PHYSICAL_DOC_TYPE = 2 OR PHD.PHYSICAL_DOC_TYPE = 4 ) AND iPHYSICAL_DOC_TYPE = 2 )
)
AND iPHYSICAL_DOC_TYPE_NONSAP IS NULL
)
OR ( PHD.PHYSICAL_DOC_TYPE = iPHYSICAL_DOC_TYPE_NONSAP AND iPHYSICAL_DOC_TYPE_NONSAP IS NOT NULL )
)
AND (
sREFERENCE_NO IS NULL
OR (
UPPER( PHD.REQUESTED_NO ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
OR UPPER( PHD.TEXT_FILE_NAME ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
)
)
AND ( sPHYSICAL_NO IS NULL OR ( PHD.PHYSICAL_NO = sPHYSICAL_NO ) )
AND ( iLOCATION_ID IS NULL OR ( LOC.LOCATION_ID = iLOCATION_ID ) )
AND ( iPHYSICAL_STATUS_ID IS NULL OR ( PHD.PHYSICAL_STATUS_ID = iPHYSICAL_STATUS_ID ) )
AND ( iCONTAINER_ID IS NULL OR ( CNT.CONTAINER_ID = iCONTAINER_ID AND CNT.CONTAINER_TYPE = 2 ) )
AND (
sCONTAINER_NUMBER IS NULL
OR ( UPPER( CNT.CONTAINER_NUMBER ) LIKE '%' || UPPER( sCONTAINER_NUMBER ) || '%' AND CNT.CONTAINER_TYPE = 1 )
)
AND ( TRUNC( dREGISTERED_DATE_FROM ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) >= TRUNC( dREGISTERED_DATE_FROM ) ) )
AND ( TRUNC( dREGISTERED_DATE_TO ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) <= TRUNC( dREGISTERED_DATE_TO ) ) )
AND ( ( PHD.DRAFT_FLAG IS NULL AND sDRAFT_FLAG IS NULL ) OR ( PHD.DRAFT_FLAG = sDRAFT_FLAG ) ) --AND (PHD.CANCEL_FLAG IS NULL
--OR (PHD.CANCEL_FLAG = sCANCEL_FLAG))
AND ( sPHYSICAL_REMARK IS NULL OR ( UPPER( PHD.PHYSICAL_REMARK ) LIKE '%' || UPPER( sPHYSICAL_REMARK ) || '%' ) )
AND ( sPHYSICAL_DOC_TITLE IS NULL OR ( UPPER( PHDT.PHYSICAL_DOC_TITLE ) LIKE '%' || UPPER( sPHYSICAL_DOC_TITLE ) || '%' ) )
AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
AND ( iPHY_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iPHY_YEAR ) )
AND ( iFI_DOC_YEAR IS NULL OR ( FID_ID.FI_DOC_YEAR = iFI_DOC_YEAR OR FID_ID.FI_DOC_ID IS NULL ) ) /* AND (
sSEND_TO IS NULL
OR ( PHDSL.SEND_TO LIKE '%' || sSEND_TO || '%' OR LOWER( PHDSL.SEND_TO ) LIKE '%' || sSEND_TO || '%' )
)*/
AND (
sSEND_TO IS NULL
OR (
iPHYSICAL_STATUS_ID = 2
AND EXISTS (
SELECT
PDSENT.SEND_TO
FROM
( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC ) PDSENT
WHERE
ROWNUM = 1
AND PDSENT.PHYSICAL_ID = PHD.PHYSICAL_ID
AND LOWER( PDSENT.SEND_TO ) LIKE '%' || LOWER( sSEND_TO ) || '%'
)
)
)
AND ( sFI_DOC_NO_FROM IS NULL OR ( FID.FI_DOC_NO >= sFI_DOC_NO_FROM ) )
AND ( sFI_DOC_NO_TO IS NULL OR ( FID.FI_DOC_NO <= sFI_DOC_NO_TO ) )
AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
AND ( iFI_DOC_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iFI_DOC_YEAR OR FID.FI_DOC_YEAR = iFI_DOC_YEAR ) ) --AND ( sFI_DOC_TYPE IS NULL
--OR ( UPPER(T1.FI_DOC_TYPE) = UPPER(sFI_DOC_TYPE) ) )
AND ( sIS_ASSET IS NULL OR ( sIS_ASSET IS NOT NULL AND FID.IS_ASSET IS NOT NULL ) )
AND ( sWORKFLOW_STATUS_CODE IS NULL OR ( FID.WORKFLOW_STATUS_CODE = sWORKFLOW_STATUS_CODE ) )
AND ( sPO_NO IS NULL OR ( UPPER( PO.PO_NO ) = UPPER( sPO_NO ) ) )
AND (
TRUNC( dPOSTING_DATE_FROM ) IS NULL
OR ( TRUNC( FID.POSTING_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) OR TRUNC( PHDTEMP.CREATE_DATE ) <= TRUNC( dPOSTING_DATE_TO ) )
)
AND (
TRUNC( dPOSTING_DATE_TO ) IS NULL
OR ( TRUNC( FID.POSTING_DATE ) <= TRUNC( dPOSTING_DATE_TO ) OR TRUNC( PHDTEMP.CREATE_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) )
)
AND ( sCLEARING_DOC_NO IS NULL OR ( UPPER( CLR.CLEARING_DOC_NO ) LIKE '%' || UPPER( sCLEARING_DOC_NO ) || '%' ) )
AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )
AND ( dCLEARING_DOC_DATE IS NULL OR ( CLR.CLEARING_DATE = dCLEARING_DOC_DATE ) ) --AND ( sPAYMENT_BLOCK IS NULL
-- OR ( UPPER(FID.PAYMENT_BLOCK) LIKE '%'
-- || UPPER(sPAYMENT_BLOCK)
-- || '%' ) )
AND ( sFI_DOC_REFNO IS NULL OR ( UPPER( FID.FI_DOC_REFNO ) LIKE '%' || UPPER( sFI_DOC_REFNO ) || '%' ) )
AND ( ( sSAP_REVERSE_FLAG IS NULL AND ( FID.SAP_REVERSE_FLAG IS NULL OR FID.FI_DOC_ID IS NULL ) ) OR ( sSAP_REVERSE_FLAG IS NOT NULL ) )
AND ( iAMOUNT_INC_VAT IS NULL OR ( FID.AMOUNT_INC_VAT = iAMOUNT_INC_VAT OR PHDTEMP.AMOUNT_INC_VAT = iAMOUNT_INC_VAT ) )
AND (
sCUSTOMER_NAME IS NULL
OR UPPER( PTCUS.CUSTOMER_FULL_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
OR UPPER( FCUS.CUSTOMER_CODE || ' ' || FCUS.CUSTOMER_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
)
AND (
sVENDOR_NAME IS NULL
OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
) --AND FID_ID.FI_DOC_ID IS NULL AND PHDTEMP.PHD_TEMP_DATA_ID IS NOT NULL
) PHD
LEFT JOIN FID_SAP_DETAIL FID_ID ON FID_ID.FI_DOC_ID = PHD.FI_DOC_ID
LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHD_TEMP_DATA_ID = PHDTEMP.PHD_TEMP_DATA_ID
AND PHD.FI_DOC_ID IS NULL
) PHD_ITEMS
INNER JOIN CTE_PHD_DETAIL PHD ON PHD_ITEMS.PHYSICAL_ID = PHD.PHYSICAL_ID
LEFT JOIN PHD_STATUS PHDS ON PHD.PHYSICAL_STATUS_ID = PHDS.PHYSICAL_STATUS_ID
LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
LEFT JOIN COM_DETAIL COM_FID ON COM_FID.COMPANY_CODE = FID.COMPANY_CODE
LEFT JOIN PHD_TEMP_DATA PTEMP ON PHD_ITEMS.PHD_TEMP_DATA_ID = PTEMP.PHD_TEMP_DATA_ID
LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
AND PHD_ITEMS.FI_DOC_ID IS NULL
AND PTVEN.SERVER_ID = COM_PHD.SERVER_ID
LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
AND PHD_ITEMS.FI_DOC_ID IS NULL
AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX
AND LAST_INDEX
ORDER BY
RECORD_NUMBER;
END;
此查询大约需要 1 分 30 秒，然后才会发出约 4000 条记录的查询结果。很难提取此长查询的执行计划。有什么建议或策略，或者我应该做些什么来获取计划？
我认为，我目前有 3 个选择：
动态查询
第一个选项是将过程升级为 ORM 语句，使用一些 if 语句来组成正确的语句，使用内连接而不是左外连接（如果参数为空，则不连接）。乍一看，我觉得这个选项很聪明。但是，我可能花了很长时间才完成这项任务。
物化视图
我想到的一个选项是将左连接子句重写为Materialized View。不幸的是，此存储过程涉及应用程序，因此当与ON COMMIT或结合时，物化视图将频繁更新ON STATEMENT。此选项是否被认为对死锁或性能不佳很危险？
删除一些搜索条件（存储过程参数）
假设我可以删除一些条件，这意味着我可以删除一些左外连接子句。但是，在我与队友讨论后，这个选项不可行。
我该如何解决性能问题，或者我有什么其他策略或选择？欢迎提出任何建议。
我正在尝试导出一个包含 long raw 的表。表大小为 800GB。数据泵未导出 long raw。我做了一些研究，似乎唯一的方法是将它们传输到 LOB，然后导出/导入。然后我必须在另一个数据库中将它们再次传输到 long raw。还有其他简单的方法吗？
我做了以下操作。请注意，大小很小。对表格进行求和后，dba_table结果约为 800GB。我知道这种数据类型已被弃用，但目前我无法更改。
Export: Release 19.0.0.0.0 - Production on Tue Dec 17 23:53:38 2024
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "db1"."SYS_EXPORT_TABLE_01": db1/********@sv1 directory=backup dumpfile=tablle1_d.dmp LOGFILE=log_tablle1_d.log TABLES=tablle1
EXCLUDE=STATISTICS,INDEX parallel=10
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "db1"."tablle1" 12.54 MB 77 rows
Master table "db1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for db1.SYS_EXPORT_TABLE_01 is:
/backup/tablle1_d.dmp
Job "db1"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 17 23:55:21 2024 elapsed 0 00:01:26
我已经执行了以下命令，并且可以在目录中找到它。但是查询“select * from dba_tablespaces”时我找不到它。我试图在这个表空间上创建一个新的模式，但抛出了错误tablessoace不存在。我的错误是什么？
CREATE BIGFILE TABLESPACE ESC_SCHEMA_DATA DATAFILE '/datafiles/CDB1/O19/ESC_SCHEMA_DATA.DBF' SIZE 536870912000 AUTOEXTEND ON NEXT 1073741824 MAXSIZE 33554431M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
我需要唯一值的数量，并尝试使用 Oracle 的COUNTDISTINCT()函数来获取：
select COUNTDISTINCT(a.m_label)
from user_rep a, user_group_rep b, trn_grp_rep c
where b.m_user_id = a.m_reference
...
这导致ORA-00904: "COUNTDISTINCT": invalid identifier...使用普通的COUNT()作品，但没有返回正确的结果。
我做错了什么？有什么有效的解决方法吗？
考虑以下代码：
CREATE OR REPLACE PACKAGE the_package AS
PROCEDURE procA ( pval1 IN VARCHAR2 );
PROCEDURE procA ( pval1 IN NUMBER );
END;
/
创建了一个具有重载程序的PLSQL包procA。
现在我们创建调用这些的过程：
CREATE OR REPLACE testProc1 AS
l_val VARCHAR2 := 'Boncho';
BEGIN
the_package.procA( l_val );
END;
/
CREATE OR REPLACE testProc21 AS
l_val NUMBER := 88;
BEGIN
the_package.procA( l_val );
END;
/
我想要做的是能够识别哪些变体testProc1并testProc2进行调用。
我无法使用，DBA_DEPENDENCIES因为它只是告诉我testProc1和testProc2依赖于包the_package。
我无法使用DBA_PROCEDURES，因为它只告诉我可以调用哪些程序。
似乎唯一的选择是以某种方式使用DBA_PROCEDURES和的组合DBA_ARGUMENTS并DBA_SOURCE进行一些类似编译器的分析来获得所需的输出：
第 2 行的包的过程testProc1调用。 第 3 行的包的过程调用。procAthe_packagetestProc2procAthe_package
但是谁想编写 PLSQL 编译器？
但是我认为 Oracle 在编译时会进行验证，即在编译过程时会检查所调用的过程是否存在。因此，我想也许在元数据的某个地方存储了有关所调用过程的确切信息。
是否有人知道在元数据视图中这些信息被保存在哪里？
几周以来，当我尝试调用外部过程/dll 时，我不断看到间歇性 oracle 错误。收到的消息是：
ORA-28576: 丢失与外部过程代理的 RPC 连接
还有人遇到过这种情况吗？如果是这样，采取了哪些步骤来解决这个问题？我正在使用带有最新补丁 19.24.0 的 Oracle 标准版 19c，并且我在 Windows 主机上。
我删除了不再需要的表空间，并且不小心遗漏了删除文件的 SQL 查询。表空间已被删除，但 DFB 仍留在操作系统路径中。
我想知道删除它的最佳方法是什么，因为它占用了不必要的空间。
我不敢执行
rm -f file.dbf
lsof 并未表明有任何进程正在使用它，但我认为如果可能的话最好从 DB 本身来执行此操作。
系统：RHEL4 Oracle数据库：10.2
谢谢！
我有一个名为“ADMINX”的非常老的用户，该用户几乎可以控制 Oracle 19c 数据库中的所有表空间。它被赋予了“DBA”角色，还有一个名为“APP_ADMIN_ROLE”的自定义角色，其中包含 DBA 角色。
如果我从 ADMINX 本身撤销 DBA 角色，但保留 APP_ADMIN_ROLE，表空间配额会被重置吗？我认为它应该保持无限配额，因为 APP_ADMIN_ROLE 仍然具有 DBA 角色，但我不确定 Oracle 在这种情况下会如何表现。遗憾的是，DBA_TS_QUOTAS 不显示 DBA 角色占用的无限配额。
我有一个自定义 oracle 用户（假设为 monitor_user），它有很多程序和作业，用于发送查询结果的电子邮件。但出于安全原因，我想禁用它从外部客户端登录数据库。我该怎么做？我认为删除“CREATE SESSION”对我没有帮助。