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

User · Answer

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

User · Answer

From oracle Sql developer  execute the below in sql worksheet   create user lctest identified by lctest  grant dba to lctest    then right click on  Oracle connection  -  new connection  then make everything lctest from connection name to user name password  Test connection shall pass  Then after connected you will see the schema

User · Answer

SQL gt  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 gt  create user testdb identified by password   User created   SQL gt  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 gt  grant create session to testdb   Grant succeeded   SQL gt  create tablespace testdb tablespace   2  datafile  testdb tabspace dat    3  size 10M autoextend on   Tablespace created   SQL gt  create temporary tablespace testdb tablespace temp   2  tempfile  testdb tabspace temp dat    3  size 5M autoextend on   Tablespace created   SQL gt  drop user testdb   User dropped   SQL gt  create user testdb   2  identified by password   3  default tablespace testdb tablespace   4  temporary tablespace testdb tablespace temp   User created   SQL gt  grant create session to testdb   Grant succeeded   SQL gt  grant create table to testdb   Grant succeeded   SQL gt  grant unlimited tablespace to testdb   Grant succeeded   SQL gt

User · Answer

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  exists  Open SQL Plus and log    as sysdba  The sysdba is a role and is like  quot root quot  on unix or  quot Administrator quot  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 gt  create user johny identified by 1234   View all users and check if the user johny is there  SQL gt  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 gt  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 gt  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 gt  select tablespace name from dba tablespaces   Create tablespace  SQL gt  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 gt  create temporary tablespace johny tabspace temp   2  tempfile  johny tabspace temp dat    3  size 5M autoextend on   Create the user  SQL gt  create user johny   2  identified by 1234   3  default tablespace johny tabspace   4  temporary tablespace johny tabspace temp   Grant some privileges  SQL gt  grant create session to johny  SQL gt  grant create table to johny  SQL gt  grant unlimited tablespace to johny   Login as johny and check what privileges he has  SQL gt  select   from session privs   PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE  With create table privilege the user can create tables  SQL gt  create table johny table   2      3     id int not null    4     text varchar2 1000     5     primary key  id    6      Insert data  SQL gt  insert into johny table  id  text    2  values  1   This is some text      Select  SQL gt  select   from johny table   ID  TEXT -------------------------- 1   This is some text   To get DDL data you can use DBMS METADATA package that  quot 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  quot    with help from http   www dba-oracle com oracle tips dbms metadata htm  For table  SQL gt  set pagesize 0 SQL gt  set long 90000 SQL gt  set feedback off SQL gt  set echo off SQL gt  SELECT DBMS METADATA GET DDL  TABLE  u table name  FROM USER TABLES u   Result    CREATE TABLE  quot JOHNY quot   quot JOHNY TABLE quot           quot ID quot  NUMBER   0  NOT NULL ENABLE           quot TEXT quot  VARCHAR2 1000            PRIMARY KEY   quot ID quot     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  quot JOHNY TABSPACE quot   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  quot JOHNY TABSPACE quot   For index  SQL gt  set pagesize 0 SQL gt  set long 90000 SQL gt  set feedback off SQL gt  set echo off SQL gt  SELECT DBMS METADATA GET DDL  INDEX  u index name  FROM USER INDEXES u   Result    CREATE UNIQUE INDEX  quot JOHNY quot   quot SYS C0013353 quot  ON  quot JOHNY quot   quot JOHNY TABLE quot    quot ID quot     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  quot JOHNY TABSPACE quot   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

User · Answer

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

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

Examples related to sql

Examples related to database-design

Examples related to oracle11g

Examples related to schema

Examples related to database