This discussion is archived
4 Replies Latest reply: Oct 6, 2013 9:23 PM by NishantBaurai Branched to a new discussion. RSS

Not Able to Create database objects(Tables, etc) in Oracle 12c

user11388416 Newbie
Currently Being Moderated

Hello Sir,

 

Recently, I have installed oracle 12c in my PC. And I am able to connect with the ANONYMOUS user and connection name ORCL.
But I am not able to create any objects in database like tables creation, it's just showing the error message like- you don't have sufficient privileges.

Could you please help on this? How to start the work on oracle 12c database as I have worked on Oracle 11g with the SCOTT user and connection name ORCL.It was working fine. But SCOTT user is not present in 12c. Is there any other USER in 12c with the default tables like EMP table in 11g in the SCOTT USER schema?

 

Please suggest, what to do?

 

Thanks In Advance!!

  • 1. Re: Not Able to Create database objects(Tables, etc) in Oracle 12c
    NishantBaurai Newbie
    Currently Being Moderated

    Hi,

     

    There are two types of users in 12c.

     

    1.common

    2. local

     

    Users like scott and HR are local users present in PDB. so to connect to a local user u need to connect to a PDB.

     

    [oracle@oracle12c admin]$ sqlplus "/as sysdba"

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 1 01:03:02 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, OLAP, Advanced Analytics and Real Application Testing options

    SQL> select name, CDB from v$database;

    NAME      CDB
    --------- ---
    ORCL      YES

    SQL> SHO CON_ID CON_NAME

    CON_ID
    ------------------------------
    1

    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> set line 150
    SQL> select name, open_mode, open_time from v$pdbs;

    NAME                           OPEN_MODE  OPEN_TIME
    ------------------------------ ---------- ---------------------------------------------------------------------------
    PDB$SEED                       READ ONLY  27-SEP-13 10.54.49.077 PM
    PDBORCL                        READ WRITE 27-SEP-13 11.39.23.518 PM

    SQL> conn hr/hr@PDBORCL
    Connected.
    SQL> SHO CON_ID CON_NAME

    CON_ID
    ------------------------------
    3

    CON_NAME
    ------------------------------
    PDBORCL
    SQL> show user
    USER is "HR"

  • 2. Re: Not Able to Create database objects(Tables, etc) in Oracle 12c
    user11388416 Newbie
    Currently Being Moderated

    Hi Nishant ,

     

    Thanks for the reply.

     

     

        I have done all the steps as you mentioned above. I am not able to create HR user. Please check the below errors and

     

     

    please guide me on this.

     

     

     

    SQL*Plus: Release 12.1.0.1.0 Production on Sat Oct 5 23:46:38 2013

     

     

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

     

     

     

    Enter user-name: anonymous

    Enter password:

    Last Successful login time: Sat Oct 05 2013 23:46:58 +05:30

     

     

    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

     

     

    SQL> connect sys as sysdba;

    Enter password:

    Connected.

    SQL> CREATE USER  hr IDENTIFIED BY Password#123

      2  DEFAULT TABLESPACE  hr_users

      3  TEMPORARY TABLESPACE  hr_temp

      4  QUOTA  5000k ON  hr_users

      5  QUOTA unlimited ON hr_temp

      6  PROFILE  enduser  ;

    CREATE USER  hr IDENTIFIED BY Password#123

                 *

    ERROR at line 1:

    ORA-65096: invalid common user or role name

     

     

    SQL> SELECT NAME, CDB FROM V$DATABASE;

     

     

    NAME      CDB

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

    ORCL      YES

     

     

    SQL> SHO CON_ID CON_NAME

     

     

    CON_ID

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

    1

     

     

    CON_NAME

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

    CDB$ROOT

     

     

    SQL> SET LINE 150

     

     

    SQL> SELECT NAME, OPEN_MODE, OPEN_TIME FROM V$PDBS;

     

     

     

    NAME                           OPEN_MODE  OPEN_TIME

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

    PDB$SEED                   READ ONLY  04-OCT-13 08.57.50.461 PM

    PDBORCL                        MOUNTED

     

     

     

    SQL> CONN HR/HR@PDBORCL

    ERROR:

    ORA-12154: TNS:could not resolve the connect identifier specified

     

     

     

    SQL> SHO CON_ID CON_NAME

    SP2-0640: Not connected

     

    SP2-0641: "SHOW CONTAINER" requires connection to server

     

     

     

     

     

    Thanks in advance!!

     

     

     

    Regards,

    Dharmendra Verma

  • 3. Re: Not Able to Create database objects(Tables, etc) in Oracle 12c
    rp0428 Guru
    Currently Being Moderated
    I have done all the steps as you mentioned above. I am not able to create HR user

    No - you haven't

    ORA-65096: invalid common user or role name

    You can't create local users in the root container. In 12c there are common usrs and local users.

     

    Read chapters 17 and 18 of the Database Concepts doc about the multitenant architecture.

    http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm

    PDBORCL                        MOUNTED

    That shows that the PDB is not open. You can't work with databases that are not open. The PDBs do not open by default when you startup the database; only the CDB is opened.

     

    You have to either open the PDBs manually or create an after startup trigger to open them.

     

    All of those things are explained in the two chapters of the doc I referenced above. You will not get very far if you do not first read and then understand what is explained there.

     

    See my extensive comments in this recent thread.

  • 4. Re: Not Able to Create database objects(Tables, etc) in Oracle 12c
    NishantBaurai Newbie
    Currently Being Moderated

    Hi Dharmendra,

     

    Follow the below steps:

     

    SQL> select name, CDB from v$database;

     

    NAME      CDB

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

    ORCL      YES

     

    SQL> SHO CON_ID CON_NAME

     

    CON_ID

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

    1

     

    CON_NAME

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

    CDB$ROOT

    SQL> select name, open_mode, open_time from v$pdbs; 

     

    NAME                           OPEN_MODE  OPEN_TIME

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

    PDB$SEED                       READ ONLY  27-SEP-13 04.29.56.235 PM

    PDBORCL                        MOUNTED

     

    SQL> alter session set container=PDBORCL;

     

    Session altered.

     

    SQL> select status from v$instance;

     

    STATUS

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

    MOUNTED

     

    SQL> alter database open;

     

    Database altered.

     

    SQL> select status from v$instance;

     

    STATUS

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

    OPEN

     

    once PDBORCL is in open mode, make an entry in tnsnames.ora for PDBORCL database as below and then connect to PDBORCL as HR user.

     

    [oracle@oracle12c admin]$ more tnsnames.ora

    # tnsnames.ora Network Configuration File: /usr1/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora

    # Generated by Oracle configuration tools.

     

    ORCL =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.test)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = orcl.test)

        )

      )

     

    PDBORCL =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.test)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = pdborcl.test)

        )

      )

     

     

    Regards,

    Nishant Baurai

Incoming Links

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points