Search code examples
linuxdatabaseoracle-databaseoracle12c

How to use existing Database

I have successfully installed oracle 12c release 2 on centos7 also i am able to access my database using sqlplus command.

My questions are:

  1. How to create new database ?
  2. How to use existing data which i had created during installations time ? we are trying to access created databases using command [use databases_name;] as used in sql but we are to able to access the databases in oracle.
  3. Is it possible to access database in GUI ?

Please provide some adequate solution that will be appreciated.

Thanks

Solution

  • Connect as a privileged user (such as SYS) and check which users you already have by running

    SQL> select username, account_status
  2  from dba_users
  3  order by username;

USERNAME                       ACCOUNT_STATUS
------------------------------ -------------------------
ANONYMOUS                      OPEN
APEX_PUBLIC_USER               LOCKED
APEX_040000                    LOCKED
<snip>
HR                             LOCKED    --> this one
<snip>
SCOTT                          OPEN
SYS                            OPEN
SYSTEM                         OPEN
<snip>    
17 rows selected.

SQL>

    One of pre-installed users is HR (human resources); it contains several tables you might want to use. If it is locked (and if you don't know its password), do the following:

    SQL> alter user hr account unlock;

User altered.

SQL> alter user hr identified by hr;

User altered.

SQL>

    Now you can connect as HR and practice your SQL skills:

    SQL> connect hr/hr
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
PRODUCT_INFORMATION
MYTABLE
DEPT
COUNTRIES

10 rows selected.

SQL>

    As of your question about "creating a database": I presume you meant "user", not "database". Unless you are a DBA, you will not be creating any of those. Though, creating users is somewhat similar (I mean, DBAs do that, not developers). Anyway: still connected as SYS, you'd

    • check which tablespaces you have
    • create user
    • grant it some privileges

    Here's how:

    SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

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

User created.

SQL> grant create session, create table to sushant;

Grant succeeded.

    Now, connect as newly created user and do something:

    SQL> connect sushant/kunwar
Connected.
SQL> create table test (id number);

Table created.

SQL> insert into test (id) values (100);

1 row created.

SQL> select * from test;

        ID
----------
       100

SQL> drop table test;

Table dropped.

SQL>

    If it turns out that you need additional privileges, you know how to grant them.

    GUI you're looking for is SQL Developer, a free tool available for download on Oracle Technology Network.

    Shortly, that would be it. For much more info, I'd suggest you read documentation. Start with the Concepts book, have a look at 2-day DBA, then move on to SQL, PL/SQL etc. Good luck!