CDB내의 모든 PDB의 Keystore 상태확인 Procedure

- CDB내에 존재하는 모든 PDB의 Keystore 상태를 한방에 확인하는 Procedure

CREATE OR REPLACE PROCEDURE all_pdb_v$encryption_wallet

IS

    err_occ            BOOLEAN;

    curr_pdb           VARCHAR2(30);

    pdb_name           VARCHAR2(30);

    wrl_type           VARCHAR2(20);

    status             VARCHAR2(30);

    wallet_type        VARCHAR2(20);

    wallet_order       VARCHAR2(12);

    fully_backed_up    VARCHAR2(15);

    wrl_parameter      VARCHAR2(4000);

    cursor sel_pdbs IS SELECT NAME FROM V$CONTAINERS

                       WHERE NAME <> 'PDB$SEED' order by con_id desc;

  BEGIN

 

    -- Store the original PDB name

    SELECT sys_context('userenv', 'con_name') INTO curr_pdb FROM DUAL;

    IF curr_pdb <> 'CDB$ROOT' THEN

      dbms_output.put_line('Operation valid in ROOT only');

    END IF;

 

    err_occ := FALSE;

    dbms_output.put_line('---');

    dbms_output.put_line('PDB_NAME                       WRL_TYPE STATUS                        ');

    dbms_output.put_line('------------------------------ -------- ------------------------------');

    dbms_output.put_line('WALLET_TYPE          WALLET_ORDER FULLY_BACKED_UP');

    dbms_output.put_line('-------------------- ------------ ---------------');

    dbms_output.put_line('WRL_PARAMETER');

    dbms_output.put_line('--------------------------------------------------------------------------');

    FOR pdbinfo IN sel_pdbs LOOP

 

      pdb_name := DBMS_ASSERT.ENQUOTE_NAME(pdbinfo.name, FALSE);

      EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = ' || pdb_name;

 

      BEGIN

        pdb_name := rpad(substr(pdb_name,1,30), 30, ' ');

        EXECUTE IMMEDIATE 'SELECT wrl_type from V$ENCRYPTION_WALLET' into wrl_type; 

        wrl_type := rpad(substr(wrl_type,1,8), 8, ' ');

        EXECUTE IMMEDIATE 'SELECT status from V$ENCRYPTION_WALLET' into status;

        status := rpad(substr(status,1,30), 30, ' ');

        EXECUTE IMMEDIATE 'SELECT wallet_type from V$ENCRYPTION_WALLET' into wallet_type;

        wallet_type := rpad(substr(wallet_type,1,20), 20, ' ');

        EXECUTE IMMEDIATE 'SELECT wallet_order from V$ENCRYPTION_WALLET' into wallet_order;        

        wallet_order := rpad(substr(wallet_order,1,9), 12, ' ');

        EXECUTE IMMEDIATE 'SELECT fully_backed_up from V$ENCRYPTION_WALLET' into fully_backed_up;

        fully_backed_up := rpad(substr(fully_backed_up,1,9), 15, ' ');

        EXECUTE IMMEDIATE 'SELECT wrl_parameter from V$ENCRYPTION_WALLET' into wrl_parameter;

        wrl_parameter := rpad(substr(wrl_parameter,1,79), 79, ' ');

        dbms_output.put_line(pdb_name || ' ' || wrl_type || ' ' || status);

        dbms_output.put_line(wallet_type || ' ' || wallet_order || ' ' || fully_backed_up);

        dbms_output.put_line(wrl_parameter);

 

      EXCEPTION

        WHEN OTHERS THEN

        err_occ := TRUE;

      END;

    END LOOP;

 

    IF err_occ = TRUE THEN

       dbms_output.put_line('One or more PDB resulted in an error');

    END IF;

  END;

.

/

set serveroutput on

exec all_pdb_v$encryption_wallet;

결과 :

---

PDB_NAME                       WRL_TYPE STATUS

------------------------------ -------- ------------------------------

WALLET_TYPE          WALLET_ORDER FULLY_BACKED_UP

-------------------- ------------ ---------------

WRL_PARAMETER

--------------------------------------------------------------------------

"PDB1"                         FILE     OPEN

AUTOLOGIN            SINGLE       NO

"PDB2"                         FILE     CLOSED

UNKNOWN              SINGLE       UNDEFINED

"CDB$ROOT"                     FILE     OPEN

AUTOLOGIN            SINGLE       NO

/home/oracle/wallet/

PL/SQL procedure successfully completed.