Tag: Osama ACE

SQL> select name, open_mode from v$pdbs;

NAME                OPEN_MODE
—————————— ———-
TEST       READ WRITE

SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=1; 

TABLESPACE_NAME       CON_ID
—————————— ———-
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1

• List Database In root Container 

 SQL> select file_name, con_id from cdb_data_files where con_id=1;

FILE_NAME CON_ID
————————————————————————— ———-
/u01/app/oracle/oradata/db12c/users01.dbf      1
/u01/app/oracle/oradata/db12c/undotbs01.dbf      1
/u01/app/oracle/oradata/db12c/sysaux01.dbf      1
/u01/app/oracle/oradata/db12c/system01.dbf      1
/u01/app/oracle/oradata/db12c/gls/test.dbf      1

• Temp Tablespace  in root container 

• Create Tablespace  ( already mentioned in Part (1)) 

SQL> conn / as sysdba
Connected.

SQL> create user c##osama identified by osama ;User created.

SQL> select username, common, con_id from cdb_users where username like ‘C##%’;

USERNAME COM CON_ID
———————————— — ———-
C##TEST YES      1
C##OSAMA YES      1
C##TEST YES      3
C##OSAMA YES      3

SQL> grant create session to c##osama ;Grant succeeded.

SQL> conn c##osama/osama@test12c:1521/db12c ;
Connected.

Let’s connect to pluggable database :

The user i will created it here will not appear in root container.

SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.

SQL> create user test identified by test ;User created.

SQL> grant create session to test ;Grant succeeded.

 SQL> select username, common, con_id  from cdb_users where username =’TEST’;

USERNAME      COM CON_ID
—————— ———-
TEST  NO      3

 SQL> conn test/test@test12c:1521/new ;
Connected.

 Same rules and conditions applied on Roles if you created in Root Container it will be replicated to pluggable database, on other hand if you created in Pluggable database it will be local without effecting Container.

Let’s connect to Root Container

SQL> conn / as sysdba
Connected.

SQL> select role, common, con_id from cdb_roles where role=’C##ROOT_ROLE’;

ROLE  COM CON_ID
——————— — ———-
C##ROOT_ROLE  YES      1
C##ROOT_ROLE  YES      3

SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.

SQL> create role test2;Role created.

SQL> create role hr container=all ;
create role hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

SQL> create user hr identified by hr container=all ;
create user hr identified by hr container=all
                             *
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

SQL> select grantee, privilege, common, con_id from cdb_sys_privs
where privilege=’CREATE SESSION’ and grantee=’TEST’; 

GRANTEE PRIVILEGE  COM  CON_ID
————– —————————————
TEST CREATE SESSION  NO       3