Creación de esquemas en Oracle

Creación de esquemas en Oracle
Oracle Database
Carlos 12 Junio, 2009 - 17:55
permisos

Este tema es para recoger comentarios acerca de la creación de nuevos esquemas en Oracle, creación de tablespaces, usuarios, asignación de permisos, etc.

Subido por Anonimo (no verificado) el 22 Septiembre, 2007 - 07:47

Un esquema es un owner o un user ???

Subido por Anonimo (no verificado) el 22 Julio, 2008 - 17:33

Entonces puedo entender que la conexion seria  la siguiente.

Usuario: APP

Password: APPPWD

Base de Datos: APPDAT

Estos seran mis parametros desde mi toad o pl/sqldev no es asi? o me equivoco?

 

Saludos.

Henry

Subido por Carlos el 23 Julio, 2008 - 21:20

Los datos del usuario y el password serían correctos, pero APPDAT es el tablespace, que no tiene nada que ver con el nombre de la base de datos.

El tablespace es el lugar donde se crean los objetos de uno o varios usuarios, y una base de datos tiene varios tablespaces. En este caso, al crear el usuario, le dices que todos los objetos (tablas, índices, procedures, etc.) que cree este user lo haga por defecto sobre el tablespace APPDAT.

El nombre de la base de datos lo puedes encontrer en el fichero tnsnames.ora, que estará en el directorio donde se haya instalado esta base de datos.

Es recomendable crear un tablespace para cada usuario que vaya a crear objetos, pero si te lo quieres ahorrar, si no especificas nada la base de datos utiliza el tablespace USERS como tablespace por defecto para cada nuevo usuario que se crea.

Subido por Carlos el 23 Septiembre, 2007 - 22:02

El esquema lo componen todos los objetos propiedad de un usuario, en el ejemplo el User APP será el Owner de todas las tablas, índices, secuencias, etc que cree, y todos estos objetos formarán su esquema.

Carlos Fernández

Analista de sistemas

Subido por Carlos el 1 Junio, 2010 - 09:43

[quote=carlos]El esquema lo componen todos los objetos propiedad de un usuario, en el ejemplo el User APP será el Owner de todas las tablas, índices, secuencias, etc que cree, y todos estos objetos formarán su esquema. [/quote]

Es decir, que por defecto, los objetos que crea un usuario pasan a ser directamente de su propiedad y parte de su esquema.

El tablespace es el lugar físico donde se almacenan objetos de la base de datos. Cada usuario tiene asignado un tablespace por defecto donde se almacenan todos los objetos que crea, si no especifica otro tablespace en la sentencia de creación.

En un mismo tablespace se pueden almacenar objetos de diferentes esquemas. Un buen ejemplo es el Tablespace USERS, que es donde por defecto se crean los objetos de los nuevos usuarios de la base de datos. Un usuario también puede crear objetos en diferentes tablespaces, sólo ha de especificar el tablespace que quiera en la sentencia de creación.

Con estas aclaraciones espero que puedas seguir sin problemas lo que se va haciendo en Cómo crear un nuevo esquema en Oracle paso a paso

 

Subido por José (no verificado) el 10 Octubre, 2007 - 17:33

Tengo una Instancia en Oracle 10g, con varios esquemas, y necesito que uno de ellos pueda ver y consultar todas las tablas de todos los otros usuarios o esquemas.

¿como puedo hacer esto?

Subido por Carlos el 16 Octubre, 2007 - 21:13

Para conceder permisos de consulta a este usuario sobre todas las tablas de los demás esquemas lo mejor es que utilices el diccionario de datos para crear los permisos. Entra con un usuario de sistema (SYS o SYSTEM) que 'vea' todas las tablas y ejecuta una sentencia como esta:

select 'Grant select on '|| t.owner||'.'||t.table_name ||' to usuario;'
from all_tables t
where owner not like '%SYS%';

Si quieres, en el artículo Construcción de scripts con ayuda del diccionario puedes consultar cómo crear un script con estas sentencias y lanzarlo desde la linea de comandos.

Otra opción sería asignar al usuario el rol SELECT_CATALOG_ROLE, o darle permisos de SELECT ANY DICTIONARY, pero con la que te propongo puedes restringir los permisos a lo que quieras en la misma select si, por ejemplo, no te interesa que el usuario tenga acceso a las tablas de sistema.

Carlos Fernández

Analista de sistemas

Subido por Manuel (no verificado) el 12 Junio, 2009 - 16:16

Carlos, que tal, evidentemente sabes bastante de oracle. Tengo 1 esquema creado para dos usuarios:

 

Esquema: x

Usuario: Y

Usuario: YY

 

Uso un soft que se conecta a la base de datos, es un soft de piping, pero la pregunta es, como se conectan estos dos usarios

a la base de datos??? les instalo cliente de oracle, pero cuando pongo en sqlplus, el nombre de usurario: Y y la pass asignada me dice que no tiene privilegios, error ORA-01031? via toad es mejor??

 

Slds compas y gracias de antemano

Subido por Carlos el 12 Junio, 2009 - 17:44

Hola Manuel, la mejor prueba es lo que hagas desde SQLPLUS.

Si te da ese error significa que estos usuarios no tienen permisos para conectar a este esquema. Supongo que especificas el esquema en la conexión, no?
En tu caso:

$ sqlplus Y/passwd@x

Si esto está bien también podría ser que los usuarios no tuvieran permisos de conexión. Asegurate de haberles asignado antes los roles CONNECT y RESOURCE con un usuario DBA de la BD:

GRANT "CONNECT" TO "Y";
GRANT "RESOURCE" TO "Y";

Subido por Carlos el 28 Abril, 2010 - 09:52

En Recopilacion de scripts y consultas utiles de Oracle puedes encontrar varias consultas SQL que te pueden ayudar a utilizar el diccionario de datos de Oracle para obtener este tipo de información.

Esta, por ejemplo, creo que te podría servir:

SELECT owner,
       decode(partition_name, null, segment_name, segment_name || ':' || partition_name) name,
       segment_type, tablespace_name,bytes,initial_extent,
       next_extent, PCT_INCREASE, extents, max_extents
FROM dba_segments
WHERE owner= 'EL_ESQUEMA_QUE_ME_INTERESA' And extents > 1 order by 9 desc, 3

Con esta consulta recuperas los datos de los objetos del esquema 'EL_ESQUEMA_QUE_ME_INTERESA', incluyendo el tablespace donde se crean. Puedes adaptarla o simplificarla a tu gusto.

Subido por Anonim. (no verificado) el 1 Junio, 2010 - 08:09

a ver si comprendo ???

1 tablespace es el espacio en los que los usuarios graban sus datos e indices

y el schema ???

acaso es un usuario propietario de los objetos usados en dicho DB ???

Subido por Rocio (no verificado) el 15 Junio, 2010 - 09:05

Me encantó el post, me fue muy util.

Tengo un tablespace con dos datafile y ¿como puedo eliminar uno de ellos y hacer el otro extensible? 

La versión de Oracle que uso es la 9.2.0.1.0. He visto que en Oracle 10g es realmente facil. 

 

Muchas gracias por todo.

 

 

Subido por Panda (no verificado) el 26 Agosto, 2010 - 21:32

Hola

Tengo un usuario USR1 con su esquema ESQ1, como puedo crear un USR2 que vea ese mismo esquema ESQ1 y que las consultas no me pidan el nombre del esquema, por ejemplo:

Select * from ESQ1.tabla1;

Saludos

Subido por OMARE (no verificado) el 7 Diciembre, 2010 - 20:11

Gran post, tengo una duda sobre este tema.

Ya he creado varios esquemas dentro de una misma base de datos cada uno con sus propios objetos y privilegios, solamente quiero saber como monitorear los recursos de memoria que utiliza cada usuario, un ejemplo tengo los esquemas A y B, despues de la creacion del esquema B, se ha mostrado algo de irregularidad en el esquema A como jobs que se quedan colgados y no se ejecutan o se tardan mas de lo normal. En el esquema B tengo el privilegio de sistema UNLIMITED TABLESPACE por que no podia crear objetos en los tablespace que le tenia asignado a B, no se si esto afecte al esquema de A.

Saludos

Subido por OMARE el 22 Diciembre, 2010 - 18:35

[quote=OMARE]Gran post, tengo una duda sobre este tema.

Ya he creado varios esquemas dentro de una misma base de datos cada uno con sus propios objetos y privilegios, solamente quiero saber como monitorear los recursos de memoria que utiliza cada usuario, un ejemplo tengo los esquemas A y B, despues de la creacion del esquema B, se ha mostrado algo de irregularidad en el esquema A como jobs que se quedan colgados y no se ejecutan o se tardan mas de lo normal. En el esquema B tengo el privilegio de sistema UNLIMITED TABLESPACE por que no podia crear objetos en los tablespace que le tenia asignado a B, no se si esto afecte al esquema de A.

Saludos

 

Otra duda Como puedo ver varios TABLESPACE con un usuario ademas del que se asigna por default al usuario sin tener el privilegio de UNLIMITED TABLESPACE, saludos

 

 

Subido por Carlos el 28 Diciembre, 2010 - 19:23

Cada usuario tiene definido el tablespace por defecto donde se crean sus objetos si este no especifica nada. Para crear objetos en otros tablespaces sólo hay que dar al usuario los permisos necesarios y especificar el tablespace en la sentencia de creación del objeto.

En lugar de darle el privilegio UNLIMITED TABLESPACE al user, puedes asignar una cuota a cada TABLESPACE que vaya a utilizar. Sería algo así:

CREATE USER mi_usuario
IDENTIFIED BY mi_password
DEFAULT TABLESPACE mi_tablespace
QUOTA 20M ON otro_tablespace
QUOTA UNLIMITED ON otro_tablespace_mas;

Subido por Atman (no verificado) el 12 Septiembre, 2013 - 11:25

Hola Carlos, necesito crear un usuario para producción que tenga acceso a tablas, vistas, procedimientos y otros objetos de otro esquema sin necesidad de nominar los objetos anteponiendo el esquema. Se que se puede hacer con scripts creando sinonimos y dando permisos al usuario en el esquema. La idea es que se desarrolle con un usuario que tiene ciertos privilegios que no puede tener el usuario de producción ¿cúal es la dinámica habitual? Gracias

Subido por Carlos el 20 Septiembre, 2013 - 09:28

El procedimiento que yo conozco es el que puedes consultar en el post Utilización de sinónimos para compartir objetos. Se trataría simplemente de crear un script que para cada objeto que quieras 'enmascarar' cree un sinónimo público con sólo su nombre.

La variante que propone ljmunozc de crear un trigger que altere la sesión para 'situar' al usuario en ese esquema me parece muy ocurrente, gracias por la aportación, aunque todo lo que tenga que ver con triggers hay que manejarlo con mucho cuidado, hay que tener siempre en cuenta que se le está cambiando automáticamente la sesión al usuario.

Si lo pruebas ya nos explicarás qué tal funciona.

Subido por brisa (no verificado) el 20 Noviembre, 2013 - 18:33

Hola Carlos Tengo nivel súper básico, tengo que crear varios esquemas, cada uno con varias tablas, entiendo que creas un tablespace, después el usuario, ese usuario lo asignas al tablespace, mi duda es donde entra el schema, cómo asignas un schema a un tablespace?

Subido por virna naranjo (no verificado) el 23 Enero, 2014 - 15:15

Hola Carlos, necesito crear un usuario igualito a otro ya existente, es decir, mismas tablas, misma data, etc... Como lo hago? Cual es el mejor camino?