Re: 19c multi-tenant

From: Karthikeyan Panchanathan <keyantech_at_gmail.com>
Date: Fri, 12 May 2023 17:33:52 +0000
Message-ID: <SJ0P220MB06216A19A8E9FA723516E13EFF759_at_SJ0P220MB0621.NAMP220.PROD.OUTLOOK.COM>

Amir

You can use sys_context(‘USERENV’,’CON_NAME’) which returns either CDB or PDB based USERENV context

Karth

Sent from Outlook for iOS<https://aka.ms/o0ukef>

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sayan Malakshinov <xt.and.r_at_gmail.com> Sent: Friday, May 12, 2023 1:24:43 PM
To: Amir Hameed <axhameed_at_yahoo.com> Cc: amir.hameed_at_sleepnumber.com <amir.hameed_at_sleepnumber.com>; ORACLE-L <oracle-l_at_freelists.org> Subject: Re: 19c multi-tenant

Amir,

Yes, since PDB name is a different thing. Moreover, you can have problems in case of the same pdb name on two different CDBs: https://oracle-base.com/articles/12c/multitenant-pluggable-database-names-12cr1

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

On Fri, 12 May 2023, 18:01 Amir Hameed, <axhameed_at_yahoo.com<mailto:axhameed_at_yahoo.com>> wrote: Thank you Sayan!

Does it mean that v$database will always provide DB_NAME and DB_UNIQUE_NAME of the CDB even if the statement is run from a PDB?

Thanks,
Amir

On Friday, May 12, 2023 at 12:23:06 PM EDT, Sayan Malakshinov <xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>> wrote:

Hi Amir,

You can also get it from v$pdbs:
select name from v$pdbs;

And you can dynamically get your current pdb name in case of CDB='YES':

SELECT
   db.name<http://db.name> as db_name
,db.db_unique_name
,pdb.name<http://pdb.name> as pdb_name
,case db.cdb

     when 'YES' then pdb.name<http://pdb.name>
     else db.name<http://db.name>

   end as c_name
FROM v$database db,

     v$pdbs pdb;

On Fri, May 12, 2023 at 4:33 PM Hameed Amir <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote:

Hi folks,

In a multitenant environment, does the following query always return values from the CDB even if run from a PDB?

SELECT db_name, db_unique_name FROM v$database ;

We are upgrading our Oracle EBS database from 11g to 19c and at any place where the custom code is getting the DB name using the query “SELECT name INTO l_dbname FROM v$database”, it is failing because the query is returning the CDB name instead of the PDB’s.

• I am assuming that this is the expected behavior where the DB_NAME and UNIQUE_DB_NAME when queries from V$DATABASE from any PDB always return values from the CDB.
• Is the best way to get the PDB name to use the following query? SELECT SYS_CONTEXT(‘userenv’,’db_name’) FROM dual ;

Thank you,

Amir

--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 12 2023 - 19:33:52 CEST