Creating a New Database and New Connection in Oracle SQL Developer

Creating a new database and new connection in Oracle SQL Developer

This tutorial should help you:

Getting Started with Oracle SQL Developer

See the prerequisites:

1. Install Oracle SQL Developer. You already have it.
2. Install the Oracle Database. Download available here.

3. Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:

   alter user hr identified by hr account unlock;

4. Download and unzip the sqldev_mngdb.zip file that contains all the files you need to perform this tutorial.

---

Another version from May 2011: Getting Started with Oracle SQL Developer

---

For more info check this related question:

How to create a new database after initally installing oracle database 11g Express Edition?

How to create Oracle 19C Database SQL Developer New Connection?

So it sound like the container database (orcl) is started but the pluggable database (orclpdb) is not. You need to start it.

In order to actually manage a database you are going to have to learn to work without SQL Developer, and use the command-line utility sqlplus.

From a command line:

C:> set ORACLE_SID=orcl
C:> sqlplus / as sysdba   
SQL>  alter pluggable database orclpdb open;

In the above, the sqlplus command is launching the command line utility 'sqlplus'. the '/' indicates to make a local connection to the database specified by the env variable ORACLE_SID, using OS authentication (os user is a member of the ORACLE_DBA group), and connect with 'sysdba' authority. On the next line the 'SQL>' is just indicating you are now at the sql prompt within sqlplus, you actually enter the 'alter' command, whose purpose should be self-evident.

The listener is a totally separate process. It is like a telephone switchboard. It 'listens' (hence, its name) for connection requests coming across the network, and set up the connection, then is out of the picture. You check its status at the command line:

C:> lsnrctl status

One last bit of useful (for us) information. What this the connection 'type' you've defined in SQL Dev? Is it 'basic' or 'tns'? Either way, what did you specify for the values? Please name the field(s) AND the value you supplied.

Create Local Connections - SQL Developer

Here's what the code behind that is doing.

1) Checking the OCI Driver is available from the ORACLE_HOME. This has to be equal to the driver sqldev is using. To test that issue this in the worksheet. This controls the enabling of the menu.

SQL> show jdbc
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
Database Major Version: 12
Database Minor Version: 1
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.2.0.1.0    <<<<<<<<<<< THIS VERSION <<<<<<<<<<<<<<
Driver Major Version: 12
Driver Minor Version: 2

2) Next the code connects basically as "/ as sysdba". That means the ORACLE_SID will have to be setup in the env.

3) When all that works, we issue this sql and create a connection for each returned.

select username from dba_users 
                        where account_status = 'OPEN' 
                        and username not in ('SYS','MGMT_VIEW','DBSNMP','SYSMAN')

make a new connection with Oracle sql developer

Are you really trying to connect to a local database with your oracle ´s website account?

You need to get a valid username/password from the guy who has installed the database on your local computer

If it is you, then you provided a password for the « system » user during install

Edit :

How did I miss it! Your current issue is not about an invalid login/password if it was you would get an « invalid username » message.

The message you get is « could not establish connection ». This message means the database « orcl » is not accessible on localhost:1521.

You must:

• create the database orcl, if not already done

• configure a listener to make the database accessible

See https://docs.oracle.com/cd/E11882_01/server.112/e10897/network.htm#ADMQS0411

Oracle SQL Developer - pre existing tables in new connection. Why?

As you're establishing connection to user which already contains some objects, that's what you see. Now, you said that you'd like to create a new - empty - user. OK, no problem - as long as you can connect as a privileged user (such as SYS) which is capable of creating other users.

I'm using Oracle database 11gXE; this is SQL*Plus session output which shows how to create a new user. You can do the same in SQL Developer, no difference at all.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP             --> this will be TEMPORARY tablespace
USERS            --> this will be DEFAULT   tablespace

SQL> create user yash identified by tuesday
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session, create table to yash;

Grant succeeded.

SQL>

Screenshot shows

1. my "previous" connection (as user scott, who already has some tables)
2. new connection (that's the green "plus" sign in the upper left corner of your SQL Developer)
3. yash doesn't have any tables; that's what you wanted, right? An empty schema. Privilege I granted (create table) lets you create new tables. If/when you'll need other privileges, connect as SYS and grant them (e.g. grant create view to yash, etc.)

---

Related Topics