SQL 명령어 이것저것

select username from dba_users;

create user kkw identified by 1234;

show con_name;

SQL> select * from session_roles;
ROLE
-------
CONNECT
DBA
PDB_DBA
.... output truncated ....

A column ISPDB_MODIFIABLE in V$PARAMETER shows whether the parameter can be modified in a PDB or not

SQL> select name, ispdb_modifiable
2 from v$parameter
3 where name in (
4 'optimizer_index_cost_adj',
5 'audit_trail'
6* )
SQL> /
NAME ISPDB
------------------------------ -----
audit_trail FALSE
optimizer_index_cost_adj TRUE

we identify the session from V$SESSION

SQL> show con_id
CON_ID
------------------------------
5

SQL> select username, sid, serial#
2 from v$session
3 where con_id = 5;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYSPDB3 49 54303
C##FINMASTER 280 13919
2 rows selected.

SQL> alter system kill session '280,13919';
System altered.

Closing Pluggable Database

[oracle@prosrv1 pluggable]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Mar 9 14:51:38 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> shutdown
Pluggable Database closed.

CDB_USERS, which shows the users across the entire CDB

Connect to the root container and issue this SQL:

SQL> select con_id, name, open_time, create_scn, total_size
2* from v$pdbs
SQL> /

CON_ID NAME OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ----------------------- ------------------------- ---------- ----------
2 PDB$SEED 19-FEB-13 09.54.26.452 PM 1688774 283115520
3 PDB1 19-FEB-13 09.55.06.421 PM 1875166 288358400
4 PDB2 24-FEB-13 10.53.08.615 AM 2710636 288358400

When you are connected to the root (the CDB directly) the container ID of 0 shows the data for the entire CDB. Here is an example:

SQL> select sid, username, program
2 from v$session
3 where con_id = 0;

SID USERNAME PROGRAM
---------- ------------------------------ -------------------------------------
1 oracle@prosrv1.proligence.com (PMON)
6 oracle@prosrv1.proligence.com (LGWR)
... output truncated ...