Saltar a contenido

Recetas de Administración de Bases de Datos

Ver tablas de una aplicación

  • Ver todas las tablas pertenecientes a una aplicación 
    select * from dba_tables where owner = 'PRIN_DES';
  • Ver todas las tablas a las que tiene acceso un usuario 
    select * from all_tables where owner = 'PRIN_DES';
  • Ver las tablas creadas por un usuario ```sql select * from user_tables where owner = ‘PRIN_DES’;

Usuarios conectados

select username, schemaname, osuser,machine from v$session order by username;
  • Usuarios conectados desde el IAS 
    select username, schemaname from v$session 
WHERE MACHINE like 'GSS\GSS-IAS%'
ORDER BY username;

Hacer una copia de una tabla

create table club_des.cl_programas_copia
as
select * from cl_programas;

Cómo dar permisos

  • Los permisos hay que darlos con el usuario propietario del objeto
  • Para dar permisos de consulta se da SELECT.
  • Para dar permisos de actualización se da SELECT, INSERT, UPDATE y DELETE

Por ejemplo:

Al usuario VAMI los siguientes permisos:

  • Acceso en modo CONSULTA a la tabla CM_TDISCA
  • Acceso en modo ACTUALIZACION a la tabla CM_DISCAPACIDAD

Se hace (como COMU_DES): 

GRANT DELETE 
    ON "COMU_DES"."CM_DISCAPACIDAD" TO "VAMI";
GRANT INSERT ON "COMU_DES"."CM_DISCAPACIDAD" TO "VAMI";
GRANT SELECT ON "COMU_DES"."CM_DISCAPACIDAD" TO "VAMI";
GRANT SELECT ON "COMU_DES"."CM_TDISCA" TO "VAMI";
GRANT UPDATE ON "COMU_DES"."CM_DISCAPACIDAD" TO "VAMI";

Ejecución de scripts

A veces desde desarrollo nos encargarán ejecutar scripts sql.

Sólo hay que tener en cuenta lo siguiente:

  • Creación, modificación, borrado de tablas, claves, etc -> con SYSTEM.

Siempre hay que referirse a la tabla de la forma ESQUEMA.TABLA (sobre todo al crearla, porque sino lo haríamos en SYSTEM). Normalmente en los scripts que nos pasan no está incluido el esquema.

  • Asignación de permisos -> con el usuario propietario del esquema, tabla, etc. (comu_des, reso_des, etc.)

Importación y exportación de esquemas

Ejemplo importación esquema

Cómo importar el esquema de RESO 0.1

  1. Creación tablespaces

  • Creación de directorio /datos1/exgss02/reso (propietario oracle:oinstall)

    1. Tablespace de datos 
      CREATE TABLESPACE "RE_TSD" 
    LOGGING 
    DATAFILE '/datos1/exgss02/reso/re_tsd.dbf' SIZE 100M 
    AUTOEXTEND 
    ON NEXT  1024K MAXSIZE 400M
      se retocan los valores de extensión: 
      ALTER TABLESPACE "RE_TSD" DEFAULT 
    STORAGE ( INITIAL 128K NEXT 64K MAXEXTENTS 4096 PCTINCREASE 0 );
ALTER TABLESPACE "RE_TSD" MINIMUM EXTENT 128K
    2. Tablespace de índices

    igual 

    CREATE TABLESPACE "RE_TSI" 
    LOGGING 
    DATAFILE '/datos1/exgss02/reso/re_tsi.dbf' SIZE 100M 
    AUTOEXTEND 
    ON NEXT  1024K MAXSIZE 400M;

    retocando: 

    ALTER TABLESPACE "RE_TSI" DEFAULT 
    STORAGE ( INITIAL 128K NEXT 64K MAXEXTENTS 4096 PCTINCREASE 0 );

ALTER TABLESPACE "RE_TSI" MINIMUM EXTENT 128K
    2. Creación usuario RESO_DES

    1. Creación de Rol RESO 
      CREATE ROLE "RESO"  NOT IDENTIFIED;
GRANT "USUARIO" TO "RESO";
    2. Creación del usuario RESO_DES 
      CREATE USER "RESO_DES"  PROFILE "DEFAULT" IDENTIFIED BY "kk" 
    DEFAULT 
    TABLESPACE "RE_TSD" TEMPORARY 
    TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "RESO_DES";
GRANT "RESO" TO "RESO_DES";
    3. Importar todo lo de RESO

    Como usuario oracle desde el servidor unix de BD: 

    export ORACLE_SID=exgss02

imp / fromuser=reso_des touser=reso_des recalculate_statistics=y file=/datos1/exp.dmp log=/datos1/exp.log

Exportación de tablas

exp userid=uzac@exjcyl1 file=modelos.dmp tables=(modelo,dispositivosmaquina) compress=y consistent=y log=modelos.log statistics=none

Importación de tablas

  • Tablas

Normalmente se recupera una tabla de explotación y se importa en pruebas

  1. Se recupera el export de la base de datos

  2. Se borra la tabla a importar de pruebas

  3. Desde el unix se hace (como usuario oracle) :

    import ORACLE_SID=pxgss01
imp / fromuser=--- touser=--- recalculate_statistics=y tables=TABLA1,TABLA2,...TABLAN file=---- log=---

    Por ejemplo, para importar la tabla W_PE_PENDS se hace:

    imp / fromuser=pncs_des touser=pncs_des recalculate_statistics=y tables=w_pe_pends file=/datos1/bck/exp_exgssg
    4. Después hay que ver los constraints, foreign-keys, permisos, etc.

  • Un esquema completo

    Por ejemplo el de reso_des

    Como usuario oracle desde el servidor unix de BD:

    export ORACLE_SID=exgss02

imp / fromuser=reso_des touser=reso_des recalculate_statistics=y file=/datos1/exp.dmp log=/datos1/exp.log

    Nota: si las tablas ya existían hay que borrarlas antes

Modificación de ficheros redolog

Se trataba de crear un nuevo grupo de ficheros redolog, y modificar el tamaño de los que ya había.

El primer punto es que los ficheros redolog no se pueden modificar (creo), así que para modificar el tamaño hay que crear nuevos grupos y borrar los anteriores.

Bueno, pues se entra con una cuenta con privilegios de administración y se hace lo siguiente (en principio no hace falta parar la BD, ni nada de eso):

  • Miramos la información de los grupos redolog:
    SQLWKS> select * from v$log;

GROUP#     THREAD#    SEQUENCE#    BYTES      MEMBERS   ARC STATUS 
- ---------- ---------- ---------- --- ---------------- ---------- 
        1          1       3878    1048576          3 NO  CURRENT 
        2          1       3880    1048576          3 YES INACTIVE 
        3          1       3877    1048576          3 YES INACTIVE 
3 filas seleccionadas.

Con esto vemos los grupos redolog que hay (columna GROUP#), el número de ficheros de cada uno (columna MEMBERS) y el tamaño (columna BYTES). Además vemos qué grupos están en uso (tienen CURRENT en la columna STATUS).

  • Se pueden ver otras propiedades de los grupos haciendo:
    SQLWKS> select * from v$logfile;

GROUP#     STATUS  MEMBER                                                                          
---------- ------- ----------------------------------------
        3 STALE   E:\DATOS\EXP\ARCHIVE\LOG3EXP1.ORA                                               
        3 STALE   E:\DATOS\EXP\ARCHIVE\LOG3EXP2.ORA                                               
        3 STALE   D:\DATOS\EXP\ARCHIVE\LOG3EXP3.ORA                                               
        2         E:\DATOS\EXP\ARCHIVE\LOG2EXP1.ORA                                               
        2         E:\DATOS\EXP\ARCHIVE\LOG2EXP2.ORA                                               
        2         D:\DATOS\EXP\ARCHIVE\LOG2EXP3.ORA                                               
        1         E:\DATOS\EXP\ARCHIVE\LOG1EXP1.ORA                                               
        1         E:\DATOS\EXP\ARCHIVE\LOG1EXP2.ORA                                               
        1         D:\DATOS\EXP\ARCHIVE\LOG1EXP3.ORA

9 filas seleccionadas.

Aquí se ven los ficheros de cada grupo. Los ficheros que tienen el STATUS vacío están en uso (pueden estarlo aunque el grupo redolog no esté activo en ese momento).

  • Creamos un nuevo grupo redolog:
    SQLWKS> alter database
    2>  add logfile group 4 ('E:\DATOS\EXP\ARCHIVE\LOG4EXP1.ORA',
    3>                'E:\DATOS\EXP\ARCHIVE\LOG4EXP2.ORA',
4>                        'D:\DATOS\EXP\ARCHIVE\LOG4EXP3.ORA')
5>  size 2M;

Sentencia procesada.

Ya está creado (se comprueba con las vistas anteriores).

  • Borrado de un grupo redolog.

Ahora pasamos a borrar un grupo redolog. Lo intentamos con el primero:

    SQLWKS> alter database
    drop logfile group 1;

    alter database
    *
    ORA-01623: el log 1 es el log actual para el thread 1 - no puede borrarlo
    ORA-00312: log 'online' 1 thread 1: ‘E:\DATOS\EXP\ARCHIVE\LOG1EXP1.ORA’
    ORA-00312: log 'online' 1 thread 1: ‘E:\DATOS\EXP\ARCHIVE\LOG1EXP2.ORA’
    ORA-00312: log 'online' 1 thread 1: ‘D:\DATOS\EXP\ARCHIVE\LOG1EXP3.ORA’

Nos da un error porque este grupo estaba en uso.

  • Cambiamos los ficheros redolog en uso:
    SQLWKS> alter system switch logfile;

Sentencia procesada.

Con esto usa otro grupo de ficheros redolog (lo comprobamos mirando las tablas v\(log y v\)logfile). Puede que no nos haga caso si está haciendo alguna transacción, por eso es mejor hacerlo cuando no haya usuarios trabajando contra la base de datos; sino pues se espera un ratillo.

Ahora ya podemos borrar el grupo redolog.

Pues ya está con esto se borran y crean según sea necesario.

Sinónimos

Cuando se crea una tabla se hace dentro de un esquema (como puede ser COMU_DES), no directamente en SYSTEM; por eso para referirse a la tabla hay que indicar:

ESQUEMA.TABLA

si nos queremos referir a la tabla sólo de la forma:

TABLA

tenemos que crear un sinónimo público.

La sintaxis para hacerlo es:

CREATE PUBLIC SYNONYM TABLA FOR ESQUEMA.TABLA

(se hace como system)

por ejemplo:

CREATE PUBLIC SYNONYM CM_TDISCA FOR COMU_DES.CM_TDISCA;

Roles

  • Ver todos los usuarios que tienen el rol LETE 
    select grantee from dba_role_privs where granted_role='LETE';
  • Dar rol LETE al usuario HERVELRO 
    GRANT "LETE" TO "HERVELRO";
ALTER USER "HERVELRO" DEFAULT ROLE  ALL;

Usuarios

  • Crea un usuario

    en este caso de LETE. Se llama hervelro y su contraseña es prueba

CREATE USER "HERVELRO"  PROFILE "USUARIO" IDENTIFIED BY "PRUEBA" 
    DEFAULT TABLESPACE "LT_TSD" 
    TEMPORARY TABLESPACE "TEMPORAL" 
    ACCOUNT UNLOCK;

GRANT "LETE" TO "HERVELRO";
Última actualización: August 15, 2021