Forum Stats

  • 3,758,090 Users
  • 2,251,327 Discussions
  • 7,870,025 Comments

Discussions

Any view or table in sysman schema that has all PDB names?

I want to get the names of all PDBs by connecting to sysman of the repository database. The closest I can find is:

col service_name for a30
col cluster_name for a20
col pdb_name for a10
select distinct cluster_name, service_name, pdb_name
from mgmt_rac_services
where pdb_name is not null
order by 1,2,3;

The only problem with that is the databases have to be RAC. I want to find all PDBs even if the database is single-node. Thanks.

Best Answer

  • EricvdS
    EricvdS Member Posts: 973 Bronze Trophy
    Accepted Answer

    Hi,


    Something like

    select db.TARGET_NAME , db.type_display_name,

    db.TYPE_QUALIFIER4 DATABASE_VERSION,

    os.TARGET_NAME SERVER

    from

    MGMT$TARGET db, MGMT$TARGET os

    where db.HOST_NAME = os.TARGET_NAME

    and db.target_type IN ('oracle_database' ,'oracle_pdb' , 'rac_database' )

    and db.target_name not like '%CDBROOT'

    and os.target_type='host'

    order by 1

    I don't have RAC so I cannot check on that,


    Eric

    User51642 Yong Huang

Answers

  • EricvdS
    EricvdS Member Posts: 973 Bronze Trophy
    Accepted Answer

    Hi,


    Something like

    select db.TARGET_NAME , db.type_display_name,

    db.TYPE_QUALIFIER4 DATABASE_VERSION,

    os.TARGET_NAME SERVER

    from

    MGMT$TARGET db, MGMT$TARGET os

    where db.HOST_NAME = os.TARGET_NAME

    and db.target_type IN ('oracle_database' ,'oracle_pdb' , 'rac_database' )

    and db.target_name not like '%CDBROOT'

    and os.target_type='host'

    order by 1

    I don't have RAC so I cannot check on that,


    Eric

    User51642 Yong Huang
  • User51642 Yong Huang
    User51642 Yong Huang Member Posts: 147 Blue Ribbon

    Thank you Eric. I think I can just run this

    select target_name from MGMT$TARGET where target_type='oracle_pdb' and target_name not like '%\_CDBROOT' escape '\' order by 1;

    and parse the output. The string before "_" is CDB and the string after it is PDB.