Default role

SQL> grant connect,resource to datauser identified by 123123;

Grant succeeded.

SQL> grant connect,resource to selectuser identified by 123123;

Grant succeeded.

SQL> create table datauser.table1(id number,data varchar2(10));

Table created.

SQL> create table datauser.table2(id number,data varchar2(10));

Table created.

SQL>  create table datauser.table3(id number,data varchar2(10));

Table created.

SQL> insert into datauser.table1 values (1,'table1');

1 row created.

SQL> insert into datauser.table2 values(2,'table2');

1 row created.

SQL> insert into datauser.table3 values(3,'table3');

1 row created.

SQL> commit;

Commit complete.

SQL> create role role_r_table1;

Role created.

SQL> create role role_r_table2;

Role created.

SQL> create role role_r_table3;

Role created.

SQL> grant select on datauser.table1 to role_r_table1;

Grant succeeded.

SQL> grant select on datauser.table2 to role_r_table2;

Grant succeeded.

SQL> grant select on datauser.table3 to role_r_table3;

Grant succeeded.

SQL> grant role_r_table1,role_r_table2 to selectdata;
grant role_r_table1,role_r_table2 to selectdata
                                     *
ERROR at line 1:
ORA-01917: user or role 'SELECTDATA' does not exist


SQL> grant role_r_table1,role_r_table2 to selectuser;

Grant succeeded.

SQL> alter user selectuser default role role_r_table1;

User altered.

SQL>
SQL>
SQL> conn selectuser/123123;
ERROR:
ORA-01045: user SELECTUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to selectuser;

Grant succeeded.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table2;

Role set.

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> set role role_r_table3;
set role role_r_table3
*
ERROR at line 1:
ORA-01924: role 'ROLE_R_TABLE3' not granted or does not exist


SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role all;

User altered.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user
USER is "SELECTUSER"
SQL> conn / as sysdba
Connected.
SQL> grant role_r_table3 to selectuser;

Grant succeeded.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role role_r_table1;

User altered.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table3;

Role set.

SQL> select * from datauser.table1;
select * from datauser.table1
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3

SQL> set role all
  2  ;

Role set.

SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3

Start ASM - Error ORA-01078, ORA-29701

Error:

[grid@OCA11g ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 27 09:43:00 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service



Solution:

[grid@OCA11g ~]$ crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'oca11g'
CRS-2679: Attempting to clean 'ora.diskmon' on 'oca11g'
CRS-2681: Clean of 'ora.diskmon' on 'oca11g' succeeded
CRS-2672: Attempting to start 'ora.diskmon' on 'oca11g'
CRS-2676: Start of 'ora.diskmon' on 'oca11g' succeeded
CRS-2676: Start of 'ora.cssd' on 'oca11g' succeeded

[grid@OCA11g ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type OFFLINE   OFFLINE
ora.FRA.dg     ora....up.type OFFLINE   OFFLINE
ora....ER.lsnr ora....er.type ONLINE    ONLINE    oca11g
ora.asm        ora.asm.type   OFFLINE   OFFLINE
ora.cssd       ora.cssd.type  ONLINE    ONLINE    oca11g
ora.diskmon    ora....on.type ONLINE    ONLINE    oca11g
ora.orcl.db    ora....se.type OFFLINE   OFFLINE


[grid@OCA11g ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 27 09:47:42 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size                  1336036 bytes
Variable Size             258063644 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

Get Meta Data of Table

SQL> grant connect,resource to dungnc identified by oracle123;
Grant succeeded.

SQL> conn dungnc/oracle123;
Connected.

SQL> create table test (id number);
Table created.

SQL> conn / as sysdba
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','TEST','DUNGNC') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','DUNGNC')
--------------------------------------------------------------------------------

  CREATE TABLE "DUNGNC"."TEST"
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED