在 Oracle 中，我尝试使用 listagg，这样每 8 个值都有一个不同的分隔符。具体来说，我希望值用逗号分隔，并且每 8 个值也用换行符分隔。有没有一种方法可以做到这一点，而不涉及在子查询中使用 row_number？
我从本地 Oracle XE 迁移到了 Amazon RDS。它在具有 gp3 存储（200 GB/12000 IOPS）的 T3.medium 服务器上运行。
几乎每五分钟，alert.log 就会显示以下内容：
"Thread 1 cannot allocate new log, sequence 10209
Checkpoint not complete"
3-4 秒后，它会记录另一条消息：
"Thread 1 advanced to log sequence 10209 (LGWR switch), current SCN: 472780758"
紧接着：
"ARC1 (PID:6427): Archived Log entry 10205 added for B-1189761742.T-1.S-10208 ID 0x7efecf3c1c4d LAD:1 [krse.c:4934]"
像这样：
读到第一条消息（“无法分配新日志”），它似乎是一条“坏”消息。典型的建议是增加重做日志文件的大小。所以我添加了 4 个日志文件，每个 512 Mb。建议是删除旧日志（每个 128 Mb）文件，但它们几乎总是“活动”的，所以我只设法删除了 1 个。这是当前状态：
... 我仍然在 alert.log 中收到错误消息。
参数 archive_lag_target 设置为 300（5 分钟），所以我猜这就是它每五分钟切换一次日志文件的原因。（我认为我无法在 RDS 中更改该设置。）
查看 AWS 中的磁盘操作并不意味着磁盘负载过重：
我是否应该担心“线程 1 无法分配新日志...”消息？
如果是，那么可能是什么原因导致的？我该怎么做？
更新：
当我使用以下命令发出手动检查点时：
EXEC rdsadmin.rdsadmin_util.checkpoint;
... 它在不到一秒的时间内就完成了检查点，除了一个日志文件之外，其他所有日志文件都变为非活动状态。
似乎它不自动执行检查点。
Oracle 中长字段的数据是封装的，无法通过简单的 select 语句显示。虽然这种类型已被弃用，但它仍然出现在各种 Oracle 内部视图中，很可能是为了向后兼容和/或替换它比较困难。有没有什么技巧可以让你只用 SQL 语句检索和操作这么长的字段中的文本？我对 PL/SQL 解决方案不感兴趣。
例如，以下查询将失败并显示“ORA-00932：数据类型不一致：预期为 CHAR，但结果为 LONG”：
select *
from dba_triggers a
where a.OWNER = 'YOUR_OWNER'
and a.TRIGGER_BODY like '%begin%';
我从哪里获取 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 主机上。