¿Cómo crear un nuevo esquema / nuevo usuario en Oracle Database 11g?

Solicité una pasantía en una empresa y me pidieron que creara un esquema para su empresa con ciertos requisitos y les enviara el file DDL . He instalado Oracle Express 11g Express Edition, pero ¿cómo creo un nuevo esquema en la database Oracle 11g? He buscado en la networking una solución pero puedo entender qué hacer. Y después de crear un esquema, ¿qué file debo enviar por correo?

En general, un esquema en Oracle es lo mismo que un usuario. Oracle Database crea automáticamente un esquema cuando crea un usuario. Un file con la extensión de file DDL es un file de SQL Data Definition Language.

Crear un nuevo usuario (usando SQL Plus)

Comandos básicos de SQL Plus:

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

Abra SQL Plus y log:

 / as sysdba 

El sysdba es un rol y es como "root" en Unix o "Administrador" en Windows. Lo ve todo, puede hacer todo. Internamente, si se conecta como sysdba, su nombre de esquema aparecerá como SYS.

Crear un usuario:

 SQL> create user johny identified by 1234; 

Ver todos los usuarios y verificar si el usuario johny está allí:

 SQL> select username from dba_users; 

Si intentas iniciar session como johny ahora obtendrías un error:

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

El usuario para iniciar session necesita al less crear privilegios de session, por lo que debemos otorgarle estos privilegios al usuario:

 SQL> grant create session to johny; 

Ahora puede conectarse como el usuario johny:

 username: johny password: 1234 

Para deshacerte del usuario, puedes soltarlo:

 SQL> drop user johny; 

Ese fue un ejemplo básico para mostrar cómo crear un usuario. Puede ser más complejo. Arriba, creamos un usuario cuyos objects se almacenan en el espacio de tablas pnetworkingeterminado de la database. Para tener orderada la database, debemos ubicar los objects de los usuarios en su propio espacio (tablespace es una asignación de espacio en la database que puede contener objects de esquema).

Mostrar espacios de tabla ya creados:

 SQL> select tablespace_name from dba_tablespaces; 

Crear espacio de tabla:

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

Crear espacio de tabla temporal (Temporaty tablespace es una asignación de espacio en la database que puede contener datos transitorios que persisten solo durante la session. Estos datos transitorios no se pueden recuperar después del process o de la falla de la instancia):

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

Crear el usuario:

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

Concede algunos privilegios:

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

Inicia session como johny y comtesting qué privilegios tiene:

 SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE 

Con el privilegio create table, el usuario puede crear tablas:

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

Insertar datos:

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

Seleccionar:

 SQL> select * from johny_table; ID TEXT -------------------------- 1 This is some text. 

Para get datos DDL, puede usar el package DBMS_METADATA que "proporciona una forma de recuperar metadatos del dictionary de la database como XML o DDL de creación y enviar el XML para volver a crear el object". (con la ayuda de http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm )

Para la 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; 

Resultado:

  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" 

Para el índice:

 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; 

Resultado:

  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" 

Más información:

DDL

DBMS_METADATA

Objetos de esquema

Diferencias entre esquema y usuario

Privilegios

Crear usuario / esquema

Crear espacio de tabla

Comandos SQL Plus

Vamos a empezar. ¿Tienes algún conocimiento en Oracle?

Primero, debe comprender qué es un SCHEMA. Un esquema es una colección de estructuras lógicas de datos u objects de esquema. Un esquema es propiedad de un usuario de database y tiene el mismo nombre que ese usuario. Cada usuario posee un único esquema. Los objects de esquema se pueden crear y manipular con SQL.

  1. CREAR el progtwig de USUARIO; – cada vez que crea un nuevo usuario en Oracle, se crea un esquema con el mismo nombre que el nombre de usuario donde se almacenan todos sus objects.
  2. GRANT CREATE SESSION TO acoder; – No hacer esto no puede hacer nada.

Para acceder al esquema de otro usuario, debe tener privilegios otorgados en un object específico en ese esquema u opcionalmente tener asignada la function SYSDBA.

Eso debería hacerte comenzar.

Es un ejemplo de trabajo:

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