How to create a new schema/new user in Oracle Database 11g?

Question

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I have searched in the net for a solution but I could not understand what to do. And after creating a schema, which file should I mail them?

Answer 1

Generally speaking a schema in oracle is the same as an user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file.

Creating new user (using SQL Plus)

Basic SQL Plus commands:

  - connect: connects to a database
  - disconnect: logs off but does not exit
  - exit: exits

Open SQL Plus and log:

/ as sysdba

The sysdba is a role and is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

Create an user:

SQL> create user johny identified by 1234;

View all users and check if the user johny is there:

SQL> select username from dba_users;

If you try to login as johny now you would get an error:

ERROR:
ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied

The user to login needs at least create session priviledge so we have to grant this privileges to the user:

SQL> grant create session to johny;

Now you are able to connect as the user johny:

username: johny
password: 1234

To get rid of the user you can drop it:

SQL> drop user johny;

---

That was basic example to show how to create an user. It might be more complex. Above we created an user whose objects are stored in the database default tablespace. To have database tidy we should place users objects to his own space (tablespace is an allocation of space in the database that can contain schema objects).

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;

Create tablespace:

SQL> create tablespace johny_tabspace
  2  datafile 'johny_tabspace.dat'
  3  size 10M autoextend on;

Create temporary tablespace (Temporaty tablespace is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.):

SQL> create temporary tablespace johny_tabspace_temp
  2  tempfile 'johny_tabspace_temp.dat'
  3  size 5M autoextend on;

Create the user:

SQL> create user johny
  2  identified by 1234
  3  default tablespace johny_tabspace
  4  temporary tablespace johny_tabspace_temp;

Grant some privileges:

SQL> grant create session to johny;
SQL> grant create table to johny;
SQL> grant unlimited tablespace to johny;

Login as johny and check what privileges he has:

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

With create table privilege the user can create tables:

SQL> create table johny_table
  2  (
  3     id int not null,
  4     text varchar2(1000),
  5     primary key (id)
  6  );

Insert data:

SQL> insert into johny_table (id, text)
  2  values (1, 'This is some text.');

Select:

SQL> select * from johny_table;

ID  TEXT
--------------------------
1   This is some text.

To get DDL data you can use DBMS_METADATA package that "provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.". (with help from http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm)

For table:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

Result:

  CREATE TABLE "JOHNY"."JOHNY_TABLE"
   (    "ID" NUMBER(*,0) NOT NULL ENABLE,
        "TEXT" VARCHAR2(1000),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"

For index:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

Result:

  CREATE UNIQUE INDEX "JOHNY"."SYS_C0013353" ON "JOHNY"."JOHNY_TABLE" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"

More information:

DDL

• http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm

DBMS_METADATA

• http://www.dba-oracle.com/t_1_dbms_metadata.htm
• http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
• http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562

Schema objects

• http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

Differences between schema and user

• https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
• Difference between a user and a schema in Oracle?

Privileges

• http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338

Creating user/schema

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
• http://www.techonthenet.com/oracle/schemas/create_schema.php

Creating tablespace

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

SQL Plus commands

• http://ss64.com/ora/syntax-sqlplus.html

Answer 2

It's a working example:

CREATE USER auto_exchange IDENTIFIED BY 123456;
GRANT RESOURCE TO auto_exchange;
GRANT CONNECT TO auto_exchange;
GRANT CREATE VIEW TO auto_exchange;
GRANT CREATE SESSION TO auto_exchange;
GRANT UNLIMITED TABLESPACE TO auto_exchange;

Answer 3

Let's get you started. Do you have any knowledge in Oracle?

First you need to understand what a SCHEMA is. A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL.

1. CREATE USER acoder; -- whenever you create a new user in Oracle, a schema with the same name as the username is created where all his objects are stored.
2. GRANT CREATE SESSION TO acoder; -- Failure to do this you cannot do anything.

To access another user's schema, you need to be granted privileges on specific object on that schema or optionally have SYSDBA role assigned.

That should get you started.

Answer 4

SQL> select Username from dba_users
  2  ;

USERNAME
------------------------------
SYS
SYSTEM
ANONYMOUS
APEX_PUBLIC_USER
FLOWS_FILES
APEX_040000
OUTLN
DIP
ORACLE_OCM
XS$NULL
MDSYS

USERNAME
------------------------------
CTXSYS
DBSNMP
XDB
APPQOSSYS
HR

16 rows selected.

SQL> create user testdb identified by password;

User created.

SQL> select username from dba_users;

USERNAME
------------------------------
TESTDB
SYS
SYSTEM
ANONYMOUS
APEX_PUBLIC_USER
FLOWS_FILES
APEX_040000
OUTLN
DIP
ORACLE_OCM
XS$NULL

USERNAME
------------------------------
MDSYS
CTXSYS
DBSNMP
XDB
APPQOSSYS
HR

17 rows selected.

SQL> grant create session to testdb;

Grant succeeded.

SQL> create tablespace testdb_tablespace
  2  datafile 'testdb_tabspace.dat'
  3  size 10M autoextend on;

Tablespace created.

SQL> create temporary tablespace testdb_tablespace_temp
  2  tempfile 'testdb_tabspace_temp.dat'
  3  size 5M autoextend on;

Tablespace created.

SQL> drop user testdb;

User dropped.

SQL> create user testdb
  2  identified by password
  3  default tablespace testdb_tablespace
  4  temporary tablespace testdb_tablespace_temp;

User created.

SQL> grant create session to testdb;

Grant succeeded.

SQL> grant create table to testdb;

Grant succeeded.

SQL> grant unlimited tablespace to testdb;

Grant succeeded.

SQL>