Problem
I registered for an internship with a company, and they asked me to construct a schema for their company with specific specifications and provide them the DDL file as a question. How do I build a new schema in Oracle database 11g? I’ve installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I looked on the internet for a remedy but couldn’t figure out what to do. And which file should I send them after I’ve created a schema?
Asked by acoder
Solution #1
In Oracle, a schema is essentially the same as a user. When you create a user in Oracle Database, it automatically constructs a schema. An SQL Data Definition Language file has the DDL file extension.
Adding a new user (using SQL Plus)
Basic SQL Plus commands are as follows:
- connect: connects to a database
- disconnect: logs off but does not exit
- exit: exists
Open SQL Plus and create the following log:
/ as sysdba
The sysdba is a role that functions similarly to “root” on Unix and “Administrator” on Windows. It sees everything and has the ability to do everything. If you join as sysdba, your schema name will appear to be SYS on the inside.
Create an user:
SQL> create user johny identified by 1234;
View all users and see if johny is among them:
SQL> select username from dba_users;
If you try to log in as johny right now, you’ll get the following error:
ERROR:
ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied
To login, the user must have at least the ability to create a session, hence we must offer the following capabilities to the user:
SQL> grant create session to johny;
You can now connect using the username johny:
username: johny
password: 1234
You can drop the user to get rid of it:
SQL> drop user johny;
That was a simple example of how to make a user. It may be more complicated. We generated a user whose objects are stored in the default tablespace of the database. To keep the database clean, we should assign each user’s objects to their 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 a temporary tablespace (A temporary tablespace is a database allocation that can hold transient data that only lasts for the duration of a session.) After a process or instance failure, this temporary data cannot be recovered.):
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;
Check what privileges johny has by logging in as him:
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
The user with the create table privilege can construct the following 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.
The DBMS METADATA package “provides a means for you to get metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object,” according to the documentation. (using http://www.dba-oracle.com/oracle tips dbms metadata.htm as a guide)
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
DBMS_METADATA
Schema objects
What are the differences between a schema and a user?
Privileges
Creating user/schema
Creating tablespace
SQL Plus commands
Answered by vitfo
Solution #2
It’s a practical 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;
Answered by Do Nhu Vy
Solution #3
Let’s get this party started. Do you have any Oracle experience?
To begin, you must first comprehend what a SCHEMA is. A schema is a set of logical data structures, often known as schema objects. A database user owns a schema, which has the same name as the user. Each user has their own schema. SQL may be used to generate and manipulate schema objects.
You must be allowed privileges on specific objects on another user’s schema or have the SYSDBA role assigned to access another user’s schema.
That should be plenty to get you started.
Answered by Tav
Solution #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>
Answered by Raj Sharma
Solution #5
Execute the following in a sql spreadsheet using Oracle Sql Developer:
create user lctest identified by lctest;
grant dba to lctest;
Then right-click on “Oracle connection” -> create connection, and everything from the connection name to the user name password should be lctest. The connection must pass the test. After you’ve connected, you’ll see the schema.
Answered by Feng Zhang
Post is based on https://stackoverflow.com/questions/18403125/how-to-create-a-new-schema-new-user-in-oracle-database-11g