Shulou(Shulou.com)05/31 Report--

This article is about how Oracle 12c views CDB&PDBs information. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

View CDB&PDBs information (SQL*PLUS)

The attributes of the PDB data determine which CDBs are visible to.

Each container data object has a CON_ID column, with column values representing the entire CDB visible, 1 representing root objects, 2 representing seed objects, and 3 representing PDB objects.

The following views behave differently from other [G] V$ views:

[G] V$SYSSTAT

[G] V$SYS_TIME_MODEL

[G] V$SYSTEM_EVENT

[G] V$SYSTEM_WAIT_CLASS

When querying from root, these views return instance-wide data, which is in each row of CON_ID column returned. However, you can query equivalent views that behave the same as other container data objects. The following views return specific data for each container in CDB: [G] V$CON_SYSSTAT, [G] V$CON_SYS_TIME_MODEL, [G] V$CON_SYSTEM_EVENT, and [G] V$CON_SYSTEM_WAIT_CLASS.

1. View views in CDB

Table 43-2 Views for a CDB

View

Description

Container data objects, including:

L V$ views

L GV$ views

L CDB_ views

L DBA_HIST* views

Container data objects can display information about multiple PDBs. Each container data object includes a CON_ID column to identify containers.

There is a CDB_ view for each corresponding DBA_ view.

{CDB | DBA} _ PDBS

Displays information about the PDBs associated with the CDB, including the status of each PDB.

CDB_PROPERTIES

Displays the permanent properties of each container in a CDB.

{CDB | DBA} _ PDB_HISTORY

Displays the history of each PDB.

{CDB | DBA} _ CONTAINER_DATA

Displays information about the user-level and object-level CONTAINER_DATA attributes specified in the CDB.

{CDB | DBA} _ HIST_PDB_INSTANCE

Displays the PDBs and instances in the Workload Repository.

{CDB | DBA} _ PDB_SAVED_STATES

Displays information about the current saved PDB states in the CDB.

{CDB | DBA} _ CDB_RSRC_PLANS

Displays information about all the CDB resource plans.

{CDB | DBA} _ CDB_RSRC_PLAN_DIRECTIVES

Displays information about all the CDB resource plan directives.

PDB_ALERTS

Contains descriptions of reasons for PDB alerts.

PDB_PLUG_IN_VIOLATIONS

Displays information about incompatibilities between a PDB and the CDB to which it belongs. This view is also used to display information generated by executing DBMS_PDB.CHECK_PLUG_COMPATIBILITY.

{USER | ALL | DBA | CDB} _ OBJECTS

Displays information about database objects, and the SHARINGcolumn shows whether a database object is a metadata-linked object, an object-linked object, or a standalone object that is not linked to another object.

{ALL | DBA | CDB} _ SERVICES

Displays information about database services, and the PDBcolumn shows the name of the PDB associated with each service.

{USER | ALL | DBA | CDB} _ VIEWS

{USER | ALL | DBA | CDB} _ TABLES

The CONTAINER_DATA column shows whether the view or table is a container data object.

{USER | ALL | DBA | CDB} _ USERS

The COMMON column shows whether a user is a common user or a local user.

{USER | ALL | DBA | CDB} _ ROLES

{USER | ALL | DBA | CDB} _ COL_PRIVS

{USER | ALL} _ COL_PRIVS_MADE

{USER | ALL} _ COL_PRIVS_RECD

{USER | ALL} _ TAB_PRIVS_MADE

{USER | ALL} _ TAB_PRIVS_RECD

{USER | DBA | CDB} _ SYS_PRIVS

{USER | DBA | CDB} _ ROLE_PRIVS

ROLE_TAB_PRIVS

ROLE_SYS_PRIVS

The COMMON column shows whether a role or privilege is commonly granted or locally granted.

{USER | ALL | DBA | CDB} _ ARGUMENTS

{USER | ALL | DBA | CDB} _ CLUSTERS

{USER | ALL | DBA | CDB} _ CONSTRAINTS

{ALL | DBA | CDB} _ DIRECTORIES

{USER | ALL | DBA | CDB} _ IDENTIFIERS

{USER | ALL | DBA | CDB} _ LIBRARIES

{USER | ALL | DBA | CDB} _ PROCEDURES

{USER | ALL | DBA | CDB} _ SOURCE

{USER | ALL | DBA | CDB} _ SYNONYMS

{USER | ALL | DBA | CDB} _ VIEWS

The ORIGIN_CON_ID column shows the ID of the container from which the row originates.

[G] V$DATABASE

Displays information about the database from the control file. If the database is a CDB, then CDB-related information is included.

[G] V$CONTAINERS

Displays information about the containers associated with the current CDB, including the root and all PDBs.

[G] V$PDBS

Displays information about the PDBs associated with the current CDB, including the open mode of each PDB.

[G] V$PDB_INCARNATION

Displays displays information about all PDB incarnations. Oracle creates a new PDB incarnation whenever a PDB is opened with the RESETLOGS option.

[G] V$SYSTEM_PARAMETER

[G] V$PARAMETER

Displays information about initialization parameters, and the ISPDB_MODIFIABLE column shows whether a parameter can be modified for a PDB.

two。 How to determine whether or not CDB

SELECT CDB FROM V$DATABASE

3. Query container information in CDB

Example 43-2 Viewing Identifying Information About Each Container in a CDB

COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID

4. Query PDB information

Example 43-3 Viewing Container ID, Name, and Status of Each PDB

COLUMN PDB_NAME FORMAT A15

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID

5. Query the open mode of PDB

COLUMN NAME FORMAT A15

COLUMN RESTRICTED FORMAT A10

COLUMN OPEN_TIME FORMAT A30

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS

6. Query container data objects

Example 43-5 Showing the Tables Owned by Specific Schemas in Multiple PDBs

COLUMN PDB_NAME FORMAT A15

COLUMN OWNER FORMAT A15

COLUMN TABLE_NAME FORMAT A30

SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME

FROM DBA_PDBS p, CDB_TABLES t

WHERE p.PDB_ID > 2 AND

T.OWNER IN ('HR','OE') AND

P.PDB_ID = t.CON_ID

ORDER BY p.PDB_ID

Example 43-6 Showing the Users in Multiple PDBs

COLUMN PDB_NAME FORMAT A15

COLUMN USERNAME FORMAT A30

SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME

FROM DBA_PDBS p, CDB_USERS u

WHERE p.PDB_ID > 2 AND

P.PDB_ID = u.CON_ID

ORDER BY p.PDB_ID

Example 43-7 Showing the Data Files for Each PDB in a CDB

COLUMN PDB_ID FORMAT 999

COLUMN PDB_NAME FORMAT A8

COLUMN FILE_ID FORMAT 9999

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME

FROM DBA_PDBS p, CDB_DATA_FILES d

WHERE p.PDB_ID = d.CON_ID

ORDER BY p.PDB_ID

Example 43-8 Showing the Temp Files in a CDB

COLUMN CON_ID FORMAT 999

COLUMN FILE_ID FORMAT 9999

COLUMN TABLESPACE_NAME FORMAT A15

COLUMN FILE_NAME FORMAT A45

SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME

FROM CDB_TEMP_FILES

ORDER BY CON_ID

Example 43-9 Showing the Services Associated with PDBs

COLUMN NETWORK_NAME FORMAT A30

COLUMN PDB FORMAT A15

COLUMN CON_ID FORMAT 999

SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES

WHERE PDB IS NOT NULL AND

CON_ID > 2

ORDER BY PDB

7. Query user-created tables and views

Example 43-10 Querying a Table Owned by a Common User Across All PDBs

SELECT * FROM CONTAINERS (employees)

Example 43-11 Querying a Table Owned by Local Users Across All PDBs

CREATE OR REPLACE VIEW employees AS SELECT * FROM hr.employees;SELECT * FROM CONTAINERS (employees); SELECT * FROM CONTAINERS (employees) WHERE CON_ID IN (3); 8. Query ID and nameSHOW CON_IDSHOW CON_NAME of the current container

Example 43-12 Returning the Container ID Based on the Container Name

SELECT CON_NAME_TO_ID ('HRPDB') FROM DUAL

Example 43-13 Returning the Container ID Based on the Container DBID

SELECT CON_DBID_TO_ID (2226957846) FROM DUAL

Function

Description

CON_NAME_TO_ID ('container_name')

Returns the container ID based on the container's name.

CON_DBID_TO_ID (container_dbid)

Returns the container ID based on the container's DBID.

CON_UID_TO_ID (container_uid)

Returns the container ID based on the container's unique identifier (UID).

CON_GUID_TO_ID (container_guid)

Returns the container ID based on the container's globally unique identifier (GUID).

9. Query the parameter SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE = 'TRUE' ORDER BY NAME;10 that can be modified in PDB. Query PDB History COLUMN DB_NAME FORMAT A10COLUMN CON_ID FORMAT 999COLUMN PDB_NAME FORMAT A15COLUMN OPERATION FORMAT A16COLUMN OP_TIMESTAMP FORMAT A10COLUMN CLONED_FROM_PDB_NAME FORMAT A15

SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME FROM CDB_PDB_HISTORY WHERE CON_ID > 2 ORDER BY CON_ID; Thank you for reading! This is the end of this article on "how to view CDB&PDBs information on Oracle 12c". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.