Creación de esquemas en Oracle

Un esquema es un owner o un user ???

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

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?

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

El Schéma es un user...

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

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.

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

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

hola me pueden ayudar con un script para identificar  los tablespace utilizados por un esquema 

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.

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

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

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.

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

Puedes hacerlo utilizando sinónimos públicos de Oracle, se trata sólo de crear un sinónimo para cada objeto excluyendo del mismo el nombre del owner.

Te enlazo el post Utilización de sinónimos para compartir objetos, donde se explica con más detalle cómo hacerlo.

Un saludo,