AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题

问题[oracle](dba)

Martin Hope
Bernie L
Asked: 2025-02-14 03:55:19 +0800 CST

带有变量分隔符的 listagg

  • 5

在 Oracle 中,我尝试使用 listagg,这样每 8 个值都有一个不同的分隔符。具体来说,我希望值用逗号分隔,并且每 8 个值也用换行符分隔。有没有一种方法可以做到这一点,而不涉及在子查询中使用 row_number?

oracle
  • 1 个回答
  • 24 Views
Martin Hope
Björn
Asked: 2025-02-11 19:35:35 +0800 CST

AWS RDS 上的 Oracle alert.log“线程 1 无法分配新日志”

  • 5

我从本地 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 中的磁盘操作并不意味着磁盘负载过重:
磁盘队列深度
EBS IO 操作
重做大小

我是否应该担心“线程 1 无法分配新日志...”消息?
如果是,那么可能是什么原因导致的?我该怎么做?

更新:
当我使用以下命令发出手动检查点时:

EXEC rdsadmin.rdsadmin_util.checkpoint;

... 它在不到一秒的时间内就完成了检查点,除了一个日志文件之外,其他所有日志文件都变为非活动状态。
似乎它不自动执行检查点。

oracle
  • 1 个回答
  • 59 Views
Martin Hope
XXX
Asked: 2025-02-04 23:50:43 +0800 CST

Oracle - 如何仅使用 SQL 来操作长字段的内容?

  • 5

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
  • 2 个回答
  • 60 Views
Martin Hope
user326351
Asked: 2025-01-23 14:02:43 +0800 CST

寻找适用于 23 的 Oracle 网格基础设施下载

  • 5

我从哪里获取 Oracle 网格软件以在 Oracle 23c 上运行 ASM。我已经为 19 和其他版本做过了,但找不到 23ai 的网格。

oracle
  • 1 个回答
  • 32 Views
Martin Hope
Pranithan T.
Asked: 2025-01-07 18:22:04 +0800 CST

如何调优复杂的Oracle存储过程?

  • 5

我有一个 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。此选项是否被认为对死锁或性能不佳很危险?

删除一些搜索条件(存储过程参数)

假设我可以删除一些条件,这意味着我可以删除一些左外连接子句。但是,在我与队友讨论后,这个选项不可行。

我该如何解决性能问题,或者我有什么其他策略或选择?欢迎提出任何建议。

oracle
  • 1 个回答
  • 35 Views
Martin Hope
Baalback
Asked: 2024-12-18 06:23:46 +0800 CST

使用 long raw 导出表,但 long raw 中的数据未移动

  • 5

我正在尝试导出一个包含 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
oracle
  • 1 个回答
  • 30 Views
Martin Hope
Baalback
Asked: 2024-12-05 04:03:08 +0800 CST

表空间创建成功后未显示在 dba_tablepsaces 中的原因是什么

  • 5

我已经执行了以下命令,并且可以在目录中找到它。但是查询“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
  • 1 个回答
  • 26 Views
Martin Hope
Mikhail T.
Asked: 2024-11-13 01:43:12 +0800 CST

Oracle 的 COUNTDISTINCT() 函数发生了什么?

  • 4

我需要唯一值的数量,并尝试使用 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()作品,但没有返回正确的结果。

我做错了什么?有什么有效的解决方法吗?

oracle
  • 1 个回答
  • 17 Views
Martin Hope
Pro West
Asked: 2024-10-24 17:53:23 +0800 CST

确定调用了哪些 PLSQL 重载过程

  • 5

考虑以下代码:

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 在编译时会进行验证,即在编译过程时会检查所调用的过程是否存在。因此,我想也许在元数据的某个地方存储了有关所调用过程的确切信息。

是否有人知道在元数据视图中这些信息被保存在哪里?

oracle
  • 1 个回答
  • 10 Views
Martin Hope
Chip Douglas
Asked: 2024-10-10 04:58:55 +0800 CST

如何解决尝试调用外部 dll 时出现的间歇性 oracle 错误:ORA-28576:丢失与外部过程代理的 RPC 连接?

  • 6

几周以来,当我尝试调用外部过程/dll 时,我不断看到间歇性 oracle 错误。收到的消息是:

ORA-28576: 丢失与外部过程代理的 RPC 连接

还有人遇到过这种情况吗?如果是这样,采取了哪些步骤来解决这个问题?我正在使用带有最新补丁 19.24.0 的 Oracle 标准版 19c,并且我在 Windows 主机上。

oracle
  • 1 个回答
  • 27 Views

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve