Playing Around with v$session

I recently came upon Arup Nanda's series on performance tuning, and it contains some really good info especially for someone like me who hasn't really done any tuning for day-to-day performance issues. The first article focuses on gleaming info from v$session regarding states, blocking sessions, etc. So this is just condensing most of the queries used in the article, which might seem like things he already posted, but I guess it's mostly just for my reference for future use :)

To get the most basic information based on the username:

select SID, state from v$session where username='<username>';

select SID, state, event from v$session where username='<username>';

Great little script to nicely display what sessions are doing:

select sid,

decode(state,   

'WAITING','Waiting',

'Working') state,

decode(state,

'WAITING','So far '||seconds_in_wait,

'Last waited '||wait_time/100)||

' secs for '||event "Description",

from v$session

where username='<username>';

The above script considers anything other than 'WAITING' to be 'working'. With regards to the EVENT value, there are two values that can be possibly ignored. 

• SQL*Net message from client - Can be misleading, could be idle, could be network slow. 
• rdbms ipc message - event status for sessions that are really idle.

To show who's blocking who:

select blocking_session, blocking_instance

from v$session

where sid=<n>;

To find out information about the object being contested upon:

select row_wait_obj#,

row_wait_file#,

row_wait_block#,

row_wait_row#

from v$session

where sid=<n>;

select owner,

object_type,

object_name,

data_object_id

from dba_objects

where object_id=<row_wait_block#);

And you can combine the 2 queries if wanted:

select owner,

object_type,

object_name,

data_object_id

from dba_objects

where object_id=(

select row_wait_obj# from v$session where sid=<n>;

To find the query that was being run:

select sql_fulltext

from v$sql

where sql_id = (

select sql_id from v$session where sid=<n>;

The query above may show no results, as the query in question may have been aged out of the shared pool. Was unlucky first few times running this, it took me awhile to confirm that it was indeed aged out, even though I had just run the query a few seconds before.

ORA-65085 - After Converting a Non-PDB to PDB

I was playing around with the pluggable databases in 12c, and had just converted a databases that I had created through DBCA to a PDB. And although the official documentation on the steps are pretty comprehensive, I still had to follow a lot of disparate links to get to my goal. So to summarise, the quick and dirty steps I took were:

• Create database nonpdb through DBCA. 
• Create the .xml file using DBMS_PDB. This xml file contains a description of the physical structure of the database. 
• Shut down nonpdb. 
• Plug in the nonpdb database into my root container database, BASEDB.

SQL> create pluggable database NONPDB using '/u02/oradata/nonpdb.xml' nocopy tempfile reuse;

So for all intents and purposes, we now have our database created through DBCA, converted and plugged into the CDB. I then wanted to clone this (now) pdb into another pdb, which required me to put it into read only mode. So no biggie, right?

From our root container database, we just go:

SQL> alter pluggable database nonpdb close;

And then:

SQL> alter pluggable database nonpdb open read only;

Only problem is I encountered this error:

SQL> alter pluggable database nonpdb open read only;

alter pluggable database nonpdb open read only

*

ERROR at line 1:

ORA-65085: cannot open pluggable database in read only mode

Right. Several blogs suggested that they were able to circumvent this by connecting to the PDB itself, doing a shutdown immediate and then doing an open read only from there. I tried it on my server, and no dice.

After a few hours of hair pulling, it turns out I should've read further into the docs. A post-plug script needed to be executed; noncdb_to_pdb.sql. This removes any objects that relates to the database's previous existence as a non-PDB. Apparently this is what was preventing the database from opening read only, as after I had ran the script:

SQL> alter pluggable database nonpdb open read only;

Pluggable database altered.

Easy! (After 16 hours).

ORA-65149 - Conflicting With Non-Existent DBs

Continuing from my previous post, I was trying to clone a PDB that was originally created through
DBCA, (a non-PDB) and 'converted' into a PDB. Converted in the loosest sense, since it was more
'cloned' into a pluggable database, and plugged in to the root container DB, but for simplicitity's sake we'll just say converted :).

Anyway, just to do a quick recap on the situation thus far:

• Create database with DBCA. 
• Convert into PDB. 
• Plug into CDB. 
• Try and clone said PDB. 

So without any fancy options on storage, tempfile usage, admin, etc, we'll try and clone using the most minimal (oxymoron?) statement:

SQL> create pluggable database pdb2 from nonpdb;

create pluggable database pdb2 from nonpdb

*

ERROR at line 1:

ORA-65149: PDB name conflicts with existing service name in the CDB or the 
PDB

Huh. That doesn't seem right. Well let's check anyway:

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

NONPDB                         MOUNTED

Nothing there either. Maybe in oratab?

[oracle@localhost ~]$ more /etc/oratab

...

HOME:/u01/app/oracle/product/12.1.0/dbhome_1:N 
BASEDB:/u01/app/oracle/product/12.1.0/dbhome_1:N

NONPDB:/u01/app/oracle/product/12.1.0/dbhome_1:N

That's weird. Maybe changing the name of the database that we want to create?

SQL> create pluggable database "PDB2" from pdb3;

create pluggable database "PDB2" from pdb3

*

ERROR at line 1:

ORA-65149: PDB name conflicts with existing service name in the CDB or the 
PDB

SQL> create pluggable database "ASDASDASDASD" from pdb3;

create pluggable database "ASDASDASDASD" from pdb3

*

ERROR at line 1:

ORA-65149: PDB name conflicts with existing service name in the CDB or the 
PDB

So something definitely wrong there, I'm pretty sure that's a unique enough database name that it's not
some coincidence that it conflicts with an already existing database and/or service. After finally getting access to Metalink, what do you, know it's a bug listed under Doc ID 1569267.1 with plans to be fixed in 12c R2.