Generally speaking, the scheme in the oracle is the same as that of the user. Oracle Database automatically creates the schema when creating the user. A file with the .dll file extension is an SQL data definition file.
Create a 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 write:
/ as sysdba
sysdba is a role and is similar to "root" on unix or "Administrator" on Windows. He sees everything, can do everything. Internally, if you connect as sysdba, your schema name will be SYS.
Create a user:
SQL> create user johny identified by 1234;
View all users and see if johny is a user:
SQL> select username from dba_users;
If you try to log in as johny, you get an error:
ERROR: ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied
A user must at least create a session privilege to log in, so we must grant the user these privileges:
SQL> grant create session to johny;
Now you can connect as johny user:
username: johny password: 1234
To get rid of the user, you can delete him:
SQL> drop user johny;
This was a basic example showing how to create a user. It can be trickier. Above, we created a user whose objects are stored in the default tablespace of the database. In order to have the database in order, we must put user objects in its own space (a table space is a distribution of space in a database that can contain schema objects).
Show table spaces already created:
SQL> select tablespace_name from dba_tablespaces;
Create tablespace:
SQL> create tablespace johny_tabspace 2 datafile 'johny_tabspace.dat' 3 size 10M autoextend on;
Creating a temporary table space (Temporaty temporary table space is a distribution of space in the database that can contain temporary data that is stored only during the entire session. This transient data cannot be restored after a process or instance crashes.):
SQL> create temporary tablespace johny_tabspace_temp 2 tempfile 'johny_tabspace_temp.dat' 3 size 5M autoextend on;
Create a user:
SQL> create user johny 2 identified by 1234 3 default tablespace johny_tabspace 4 temporary tablespace johny_tabspace_temp;
Provide some privileges:
SQL> grant create session to johny; SQL> grant create table to johny; SQL> grant unlimited tablespace to johny;
Log in as johny and check what privileges it has:
SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE
With the creation of a table privilege, a 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.');
Choose:
SQL> select * from johny_table; ID TEXT -------------------------- 1 This is some text.
To get DDL data, you can use the DBMS_METADATA package, which "provides you with a way to retrieve metadata from a database dictionary as XML or create DDL and send XML to recreate the object." (using http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm )
For the 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 the 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"
Additional Information:
DDL
DBMS_METADATA
Schema Objects
Differences between schema and user
Rights
Create User / Schema
Create table space
SQL Plus Commands