ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides an interface to examine and manipulate data about pluggable databases.
BEGIN
IF dbms_pdb.check_plug_compatibility('/home/oracle/pdbdev.xml', 'PDBDEV') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
col time format a30
col name format a12
col cause format a10
col type format a8
col action format a50
col message format a95
col con_id format 999
SELECT time, name, type, message
FROM pdb_plug_in_violations;
TIME NAME TYPE MESSAGE
------------------------------ --------- -------- -----------------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option APS mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option XOQ mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING Tablespace SYSTEM is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING Tablespace SYSAUX is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
SELECT time, name, type, status, action
FROM pdb_plug_in_violations;
TIME NAME TYPE STATUS ACTION
------------------------------ --------- -------- --------- --------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
Performs cleanup task previously performed by SMON
-- returns 0 if the next scheduled time for the job does not need to be changed. N if the next schedule job should be N seconds from now (not my experience as shown below)
dbms_pdb.cleanup_task(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_pdb.cleanup_task(153)
FROM dual;
*
ERROR at line 1:
ORA-65160: invalid cleanup task ID
ORA-06512: at "SYS.DBMS_PDB", line 85
dbms_pdb.convert_to_local(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
object_subname IN VARCHAR2 DEFAULT NULL,
sig_mismatch IN BOOLEAN DEFAULT FALSE);
Use only in the ADW cloud where some users/roles, which should have been Oracle maintained, was not and needs to be converted in an existing PDB
Note: This could be extraordinarily dangerous with patching. Do not use this procedure.
dbms_pdb.convert_to_oracle_maintained(user_name IN VARCHAR2);
CREATE USER c##convert NO AUTHENTICATION;
User created.
SELECT oracle_maintained
FROM dba_users
WHERE username = 'C##CONVERT';
O
-
N
exec dbms_pdb.convert_to_oracle_maintained('C##CONVERT');
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_PDB", line 575
ORA-06512: at line 1
-- A decision has been made not to publish the
workaround
This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB
Note: This procedure is called by the package initialization section.
This is accomplished by running the statement as an oracle script (i.e. with the parameter _oracle_script set to TRUE). This is for use by Oracle internal packages only.
dbms_pdb.exec_as_oracle_script(sql_stmt IN VARCHAR2);
Exports RMAN backup information that belong to the pluggable database to its dictionary before unplug so that pre-plugin backups can be used. The pluggable database has to be opened in read write mode.
If the database is non-cdb, then pdb_name must be omitted. If the pdb_name is omitted, then the pluggable database to which the session is connected will be exported. If the pdb_name is omitted, and the session is connected to the Root, an error is returned.
dbms_pdb.exportRMANbackup(pdb_name IN VARCHAR2 DEFAULT NULL);
Generates Partial Archive Logs for all non-spooled online logs
dbms_pdb.generate_partial_logs(
partial_log_dest IN VARCHAR2,
remote_dblink IN VARCHAR2 DEFAULT NULL,
include_fralogs IN BOOLEAN DEFAULT FALSE,
copy_fralogs IN BOOLEAN DEFAULT TRUE,
copy_dop IN NUMBER DEFAULT 16,
force_copy_logs IN BOOLEAN DEFAULT FALSE);
Checks whether given path_name is corresponding to the path_prefix property
dbms_pdb.is_valid_path(path_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
IF dbms_pdb.is_valid_path('/u04/app/oracle/oradata/orabase/orabase/uwapp/') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ F
Generates an XML file describing a pluggable database by using data files belonging to the database. The XML file can be used to plug the PDB into a CDB using CREATE PLUGGABLE DATABASE.
dbms_pdb.recover(
pdb_descr_file IN VARCHAR2,
pdb_name IN VARCHAR2,
filenames IN VARCHAR2);
SQL> exec dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf');
BEGIN dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf'); END;
*
ERROR at line 1:
ORA-65128: PDB recover data file name not specified
ORA-06512: at "SYS.DBMS_PDB", line 104
ORA-06512: at line 1
Used to mark an object as Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Use to mark an object as Extended Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, and where there was no support for application containers
dbms_pdb.set_ext_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark an object as Metadata linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_metadata_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL)
Used to mark a profile as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the profiles would have been marked as implicit Application Common profile.
dbms_pdb.set_profile_explicit(profile_name IN VARCHAR2);
BEGIN
dbms_pdb.set_profile_explicit('MATRIX_PROFILE');
END;
/
Used to mark a role as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the roles would have been marked as implicit Application Common roles.
dbms_pdb.set_role_explicit(role_name IN VARCHAR2);
BEGIN
dbms_pdb.set_role_explicit('MATRIX_RO');
END;
/
Used to set SHARTING=NONE status on an object in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_sharing_none(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark a user as an explicit Application Common user. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the users would have been marked as implicit Application Common users.
dbms_pdb.set_user_explicit(user_name IN VARCHAR2);
BEGIN
dbms_pdb.set_user_explicit('MATRIX');
END;
/
Updates CDB View Stats and returns 0 if the next scheduled job change time does not need to be changed: N if the next scheduled time should be N seconds from SYSDATE
dbms_pdb.update_cdbvw_stats RETURN NUMBER;
set timing on
SELECT dbms_pdb.update_cdbvw_stats
FROM dual;
UPDATE_CDBVW_STATS
------------------
3600
Elapsed: 00:04:27.10
set timing off
-- will appear to hang the system for a very long time while stats are collected
