4 Replies Latest reply: Oct 6, 2013 11:23 PM by Nishant Baurai Branched to a new discussion. RSS

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

    user11388416

      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
          Nishant Baurai

          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

            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
              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
                Nishant Baurai

                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