-------------------------PROFILES---------------------------
PARA PODER CREAR PROFILES DEBE DE ESTAR EL RESORCE_LIMIT EN TRUE, YA QUE ES EL PARAMETRO QUE NOS ACTIVA LA LIMITACION DE RECURSOS EN ORACLE:
SHOW PARAMETERS RESOURCE_LIMIT;
el por que de este archivo debe de estar en true es por que los perfiles permiten de cierta manera limitar los recursos
a los usuarios y si este parametro no esta en verdadero la limitacion de los profiles no sera real aunque paresca que se alla
creado correctamente.
SI NO ESTA CAMBIARLO CON:
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE SCOPE=BOTH;
NOTA: EXISTE UN ARCHIVO BASE DE ORACLE PARA CREAR PERFILES ESTE ARCHIVO SE LLAMA:
UTLPWDMG.SQL
Y PARA CREAR UN PERFIL BASTA CON MODIFICARLO SEGUN NUESTRAS NECESIDADES Y CORRERLO DESDE LA TERMINAL COMO UN SQL ASI:
@C:\ORACLE\ORADATA\RDBMS\ADMIN\UTLPWDMG.SQL
PARA ASIGNAR UN PROFILE HACER:
ALTER USER NOM_USUARIO PROFILE NOM_PROFILE;
PARA MODIFICAR UN PROFILE USAR:
ALTER PROFILE NOM_PROFILE LIMIT //Y PONER LAS MODIFICACIONES QUE
QUERAMOS REALIZAR
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;
ELIMINAR PROFILE:
DROP PROFILE MIPROFILE;
Ó DROP PROFILE MIPROFILE CASCADE;
ALGUNAS DE LAS LIMITACIONES QUE PODEMOS TENER CON UN PROFILE SON:
-- FAILED_LOGIN_ATTEMPTS ERRORES ANTES DE BLOQUEAR LA CUENTA
-- PASSWORD_LOCK_TIME NUMERO DE DIAS EN QUE SE BLOQUEARA LA CUENTA
--PASSWORD_LIFE_TIME TIEMPO DE VIDA DESPUES DE QUE LA CONTRASEÑA EXPIRA
--PASSWORD_GRACE_TIME TIEMPO EXTRA DESPUES DE QUE LA CUENTA AH EXPIRADO
--PASSWORD_REUSE_TIME # DIASANTES DE PODER VOLVER A USAR UNA CONTRASEÑA
--PASSWORD_REUSE_MAX # MAXIMO DE CAMBIOS ANTES DE PODER REUTILIZAR UNA CONTRASEÑA.
--CONNECT_TIME 30 TIEMPO MAXIMO DE CONECCION EN MINUTOS
-- IDLE_TIME TIEMPO EN MINUTOS EN QUE PUEDE ESTAR UN USUARIO DESOCUPADO
INDEPENDIENTEMENTE DE LAS FUNCIONES ANTERIORES EXISTE UNA FUNCION QUE SE ACTIVA AGREGANDO EN LA CREACION DEL PROFILE LA SIGUIENTE LINEA:
--PASSWORD_VERIFY_FUNCTION
LO QUE HACE ESTA LINEA ES ACTIVAR UNA FUNCION LLAMADA VERIFY_FUNCTION LA CUAL PODEMOS MODIFICAR PARA RESTRINGIR A NUESTROS USUARIOS TANTO COMO LO DESEEMOS.
---------ROLES-----------------------------------------------------------------------
PARA CREAR UN ROLE:
CREATE ROLE NOMBRE_ROL;
PONER PASSWORD A UN ROL:
Set role nombre_role identified by contraseña;
DESACTIVAR PASSWORD DE ROL:
Set role none;
VISTAS ASOCIADAS
DBA_ROLES, DBA_ROLES_PRIVS,DBA_SYS_PRIVS,ROLE_ROLE_PRIVS,ROLE_SYS_PRIVS,ROLE_TAB_PRIVS.
EXISTEN DOS ROLES ESPECIALES EL DE SYSDBA Y EL DE SYSOPER, PARA SABER QUE USUARIOS TIENEN ESTOS ROLES HAY QUE REALIZAR LA SIGUIENTE CONSULTA:
Select * from V$PWFILE_USERS;
///////////////PRIVILEGIOS//////////////////////////////////////////////////////////
-OTORGAR PRIVILIGIOS A USUARIOS SOBRE CUALQUIER TABLA:
GRANT SELECT ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE TO NOMBRE_USUARIO WITH ADMIN OPTION;
SI ACTIVAMOS LA OPCION WITH ADMIN OPTION LO QUE HACEMOS ES PERMITIR QUE ESE USUARIO TAMBIEN PUEDA OTORGAR LOS PRIVILEGIOS QUE ESTA RECIVIENDO,
-OTORGAR PRIVILEGIOS DE OBJETOS SOBRE DETEWRMINADO OBJETO O ESQUEMA:
GRANT ALTER,DELETE,EXECUTE,INDEX,INSERT,REFERENCES,SELECT,UPDATE ON BD.TABLA TO NOM_USUARIO WITH GRANT OPTION;
-QUITAR, ELIMINAR PRIVILEGIOS:
REVOKE PRIVILEGIO FROM USUARIO;
REVOKE SELECT ON emi.orders FROM jeff;
-DAR PRIVILEGIOS DE ADMINISTRACION(CREACION, ELIMINACION, ETC):
GRANT CREATE ANY INDEX, ALTER ANY INDEX,DROP ANY INDEX,
CREATE TABLE, CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE,
CREATE SESSION,ALTER SESSION, RESTRICTED SESSION,
CREATE TABLESPACE,ALTER TABLESPACE, DROP TABLESPACE, UNLIMITED TABLESPACE TO NOMBRE_USUARIO;
-PARA DAR TODOS LOS PRIVILEGIOS A UN USUARIO
GRANT GRANT ANY PRIVILEGE TO NOM_USUARIO;
-PRIVILEGIOS DE SYSDBA Y SYSOPER
PARA QUE ESTOS PRIVILEGIOS FUNCIONEN APARTE DE SER OTORGADOS DEBEN ACTIVARSE AL LOGEARSE COMO USUARIOS ESTO SE HACE:
CONN NOMBRE_USUARIO/CNTRASEÑA_USUARIO AS SYSOPER;
CONN NOMBRE_USUARIO/CNTRASEÑA_USUARIO AS SYSDBA;
PUEDEN DARSE TODOS LOS PRIVILEGIOS DE SYSDBA ASI:
GRANT SYSDBA TO NOM_USUARIO;
PUEDEN DARSE TODOSLOS PRIVILEGIOS DE SYSOPER ASI:
GRANT SYSOPER TO NOM_USUARIO;
O PUEDEN DARSE SOLAMENTE ALGUNOS PRIVILEGIOS DE SYSOPER TALES COMO:
STARTUP,SHUTDOWN,ALTER DATABASE OPEN|MOUNT,ALTER DATABASE BACKUP CONTROLFILE TO,RECOVER DATABASE, ALTER DATABASE ARCHIVELOG,RESTRICTED SESSION.
CREATE DATABASE,ALTER DATABASE,ALTER TABLESPACE BEGIN/END BACKUP,RESTRICTED SESSION,RECOVER DATABASE UNTIL.
EJEM: GRANT STARTUP, SHUTDOWN,ALTER DATABASE OPEN,ALTER DATABASE BACKUP CONTROLFILE TO,RECOVER DATABASE,ALTER DATABASE ARCHIVELOG,RESTRICTED SESSION, SYSOPER PRIVILEGES WITH ADMIN OPTION,CREATE DATABASE,ALTER TABLESPACE BEGIN BACKUP,RESTRICTED SESSION, RECOVER DATABASE UNTIL TO USUARIO;
-VISTAS PARA VER QUIEN TIENE PRIVILEGIOS DE SYSDBA O SYSOPER
Select * from V$PWFILE_USERS;
PARA ASIGNAR PRIVILEGIOS SOBRE UN ESQUEMA:----------------------------------------------------------------------
GRANT { object_privilege [(column_list)]
[, object_privilege [(column_list)] ]...
|ALL [PRIVILEGES]}
ON [schema.]object
TO {user|role|PUBLIC}[, {user|role|PUBLIC} ]...
[WITH GRANT OPTION]
DONDE:
object_privilege: ES EL PRIVILIGIO QUE SE CONCEDERA P.E SELECT ANY TABLE
column_list: Specifies a table or view column. (This can be specified only when granting the INSERT, REFERENCES, or UPDATE privileges.)
ALL: Grants all privileges for the object that have been granted WITH GRANT OPTION
ON object: Identifies the object on which the privileges are to be granted
WITH GRANT OPTION: Enables the grantee to grant object privileges to other users or roles
--VISTAS RELACIONADAS
--DBA_SYS_PRIVS
--SESSION_PRIVS
--DBA_TAB_PRIVS
--DBA_COL_PRIVS
////////////////////////// USUARIOS ////////////////////////////////////////////////
-PARA CREAR UN USUARIO:
CREATE USER NOMBRE_USUARIO IDENTIFIED BY PASSWORD_USUARIO;
PROPIEDADES
DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA(ESPACIO MAXIMO QUE PUDE OCUPAR EN UN ESPACIO DE TABLAS), PROFILE.
EJEMPLO:
create user ugo
identified by ugo
default tablespace users
temporary tablespace temp;
-ELIMINAR USUARIO
DROP USER USUARIO1 [CASCADE];
-USUARIO CON PASSWORD ENCRIPTADO
create user nom_user identified by values '123AB456CD789EF0';
-PARA VER LOS PASSWORDS ENCRIPTADOS:
select username, password from dba_users;
-PARA VER LOS USUARIOS CREADOS:
SELECT USERNAME FROM DBA_USERS;
-PARA MODIFICAR LAS PROPIEDADES DE UN USUARIO PONER EN VEZ DE CREATE EL COMANDO ALTER P.E:
ALTER USER NOM_USUARIO PROFILE NOM_PROFILE;
-PARA DESBLOQUEAR USUARIO:
ALTER USER HR ACCOUNT UNLOCK;
-PARA BLOQUEARLO:
ALTER USER NOM_USUARIO ACCOUNT LOCK;
///////////////////////////////// INDICES /////////////////////////////////////////
-CREAR UN INDICE:
CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[, column_name...])
TABLESPACE tab_space;
EJEMPLOS:
CREATE INDEX customers_last_name_idx ON customers(last_name);
CREATE UNIQUE INDEX customers_phone_idx ON customers(phone);
CREATE INDEX employees_flast_name_idx ON employees(first_name, last_name);
-VISTAS ASOSIADAS
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES');
select object_name, object_type from user_objects where object_type='INDEX';
-ELIMINAR INDICE:
DROP INDEX NOMBRE_DEL_INDICE;
-CREAR UN INDICE DE MAPA DE BIT:
CREATE BITMAP INDEX NOMBRE_INDICE ON NOMBRE_TABLE('NOMBRE_COLUMNA');
P.E CREATE BITMAP INDEX IND_PER ON EXAMEN.PERSONAL(ID);
NOTAS: LAS COLUMNAS QUE DEBEN CONTENER INDICES SON AQUELLAS QUE SE ESTAN CONSULTANDO CONSTANTEMENTE.
UN INDICE FUNCIONAL ES AQUE QUE OCUPA FUNCIONES AL CREARLO,P.E LA FUNCION UPPER PARA PONER SU CONTENIDO EN MAYUSCULA;
////////////////////////////////////// RESTRICCIONES //////////////////////////////
-LAS RESTRICCIONES QUE PUEDE TOMAR UNA TABLA O CAMPO SON:
NOT NULL Una columna no puede contener un valor nulo
UNIQUE HACE QUE UNA COLOMNA O CONJUNTO DE COLUMNAS TENGAN VALORES UNICOS PARA TODAS LAS FILAS DE LA TABLA.
PRIMARY KEY Identifica unívocamente cada fila de la tabla
FOREIGN KEY Establece y obliga a que se cumpla una restricción de integridad entre una columna y otra columna de la tabla referenciada.
CHECK Especifica una condición que debe ser cierta
-PARA VER RESTRICCIONES:
SELECT * FROM USER_CONSTRAINTS;
-COMPROVAR COLUMNAS ASOCIADAS:
SELECT * FROM USER_CONS_COLUMNS WHERE table_name=’Empleados’;
- RESTRICCION DE TABLA
CREATE TABLE Empleados (
id NUMBER(6),
apellidos VARCHAR2(40),
nombre VARCHAR2(20) NOT NULL,
supervisor_id NUMBER(6),
CONSTRAINT emp_pk PRIMARY KEY (id) );
- RESTRICCION DE COLUMNA NOT NULL
CREATE TABLE Empleados2 (
nombre VARCHAR2(20) NOT NULL,
Fecha_alta DATE CONSTRAINT fecha_obli NOT NULL);
NOTA:SE PUEDEN HACER RESTRICCIONES COMPUESTAS DE DOS O MAS COLUMNAS:
CONSTRAINT nom_unico UNIQUE (apellidos,nombre);
-RESTRICCION FOREIGN KEY DE COLUMNA:
CREATE TABLE Empleados (
dep NUMBER(4) CONSTRAINT emp_dep_fk REFERENCES Departamentos (num),
id NUMBER);
-FOREIGN KEY A NIVEL DE TABLA
CREATE TABLE Empleados (
dep NUMBER(4),
nom VARCHAR2(20),
CONSTRAINT emp_dep_fk FOREIGN KEY (dep) REFERENCES Departamentos (num) ON DELETE CASCADE|ON DELETE SET NULL);
-CHECK A NIVEL DE TABLA:
CREATE TABLE Empleados (
salario NUMBER(8,2),
neto NUMBER(8,2),
CONSTRAINT neto_max
CHECK (neto<=salario*0’8) ); -AÑADIR RESTRICCIONES ALTER TABLE Empleados ADD CONSTRAINT emp_supervisor_fk FOREIGN KEY (supervisor_id) REFERENCES Empleados(id); -ELIMINAR RESTICCIONES: ALTER TABLE
DROP { PRIMARY KEY | UNIQUE (
P.E: ALTER TABLE Empleados DROP CONSTRAINT emp_supervisor_fk;
ALTER TABLE Departamentos DROP PRIMARY KEY CASCADE;
-VISTAS ASOCIADAS:
USER_CONSTRAINTS Y USER_CONS_COLUMNS
-DESACTIVAR RESTRICCIONES
ALTER TABLE
-ACTIVANDO RESTRICCIONES
ALTER TABLE
-PARA ELIMINAR COLUMNAS CON REFERENCIAS SOLO AGREGAR LA OPCION CASCADE CONSTRAINT ASI:
ALTER TABLE prueba1 DROP (pk) CASCADE CONSTRAINTS;
///////////////////////////////////// TABLESPACES //////////////////////////////////
Crear un tablespace de 10 M de tamaño y qe solo pueda crecer hasta 500M
- create tablespace mamon datafile 'c:\oracle\mamon.dbf' size 10M autoextend on maxsize 500M;
Crear un tablespace llamado ultimo con un datafile ultimo01.ora
de tamaño inicial de 2m y qye pueda crecer al maximo permitido por el espacio en disco.
SQL> CREATE TABLESPACE ULTIMO DATAFILE 'C:\ULTIMITO.ORA' SIZE 3M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
-CREANDO UN TABLESPACE DE UNDO:
CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;
-AGREGAR DATAFILES A TABLESPACE UNDO:
ALTER TABLESPACE MIUNDO ADD DATAFILE 'C:/ORACLE/ORADATA/ITT2008/MIO.DBF' SIZE 5M;
-VISTAS: V$UNDOSTAT
-PARA SABER EL UNDO DEFAULT DE LA BD:
SHOW PARAMETER UNDO_TABLESPACE
-CAMBIAR EL TS UNDO DEFAUL:
ALTER SYSTEM SET UNDO_TABLESPACE=NVO_TS_UNDO;
- CREAR UN TABLESPACE:
CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIMUM EXTENT integer[K|M]]
[BLOCKSIZE integer [K]]
[LOGGING|NOLOGGING]
[DEFAULT storage_clause ]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management_clause]
[segment_management_clause]
CREATE TABLESPACE MITABLESPACE DATAFILE 'C:/ORA/.../NVODATAFIELD.DBF' SIZE 5M;
- TABLESPACE MANEJADO LOCALMENTE:
CREATE TABLESPACE AUX DATAFILE 'C:\TSAUX.DBF' SIZE 5M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128 K;
- BONUS: TABLA ASIGNADA A UN TS MANEJADO LOCALMENTE
create table TABLA01 (C1 VARCHAR2(4000)) tablespace DATACURSOxy storage (initial 256K minextents 1);
Ó SIMPLEMENTE :
create table TABLA01 (C1 VARCHAR2(4000)) tablespace DATACURSOxy;
- TABLESPACE MANEJADO CON EL DICCIONARIO DE DATOS:
IMPORTANTE: Si el tablespace SYSTEM ha sido creado como LOCAL, ya no se podrá crear ningún
tablespace manejado por DICCIONARIO en la BD.
PARA VERIFICAR COMO ESTA DECLARADO DICHO TABLESPACE HACER LA SIGUIENTE CONSULTA:
SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='SYSTEM';
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf'
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(initial 1M NEXT 1M PCTINCREASE 0);
- PASAR DE UN TABLESPACE MANEJADO POR DICTIONARY A UNO MANEJADO LOCALMENTE
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
PARA CONOCER CUAL ES EL ESPACIOLIBRE DE UN TABLESPACE HACER:
select bytes from dba_free_space where tablespace_name='NOMBRE_TS';
-Podemos ver el tablespace por defecto (y el temporal) de cada usuario en DBA_USERS:
SELECT USERNAME,rpad(DEFAULT_TABLESPACE,20),rpad(TEMPORARY_TABLESPACE,20) FROM DBA_USERS;
-CREAR TABLESPACE TEMPORALES
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
-ASIGNAR UN TS DESDE LA CREACION DE LA BD CON:
DEFAULT TABLESPACE(NO COMANDO, AGREGADO PARA DETERMINAR UNA TTS EN -- UNA NVA BD),
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M
Ó MODIFICARLA MEDIANTE:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp2;
-PARA VER LAS PROPIEDADES DE LA BD PONER:
SELECT * FROM DATABASE_PROPERTIES;
-PARA VER EL TEMPORARY TS DE LA BD PONER:
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
-- VISTAS ASOCIADAS
--DATABASE_PROPERTIES
--DBA_DATA_FILES
--DBA_TABLESPACES
--V$TABLESPACE
--DBA_DATA_FILES
--V$DATAFILE
--DBA_TEMP_FILES
--V$TEMPFILE
- MODIFICANDO LAS PROPIEDADES DE LOS TABLESPACES
ALTER TABLESPACE NOMBRE_DEL_TS READ ONLY;
ALTER TABLESPACE NOMBRE_DEL_TS OFFLINE;
ALTER TABLESPACE NOMBRE_DEL_TS ONLINE;
ALTER TABLESPACE NOMBRE_DEL_TS MINIMUM EXTENT 2M;
ALTER TABLESPACE NOMBRE_DEL_TS DEFAULT STORAGE (INITIAL 2M NEXT 2M --MAXEXTENTS 999);
-CREAR UN TABLESPACE CON AUTOEXTEND:
CREATE TABLESPACE userdata02
DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
PONER EL TS DE LA BD CON AUTOEXTEND:
ALTER DATABASE DATAFILE '/u01/oradata/userdata02.dbf' AUTOEXTEND ON NEXT 2M;
-REDIMENCIONAR UN DATAFILE:
ALTER DATABASE DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M
- AGREGAR DATAFILE A TABLESPACE:
ALTER TABLESPACE NOMBRE_DEL_TS ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 5M;
- BORRAR TABLESPACE:
DROP TABLESPACE tablespace [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];
- PONER DIRECCION PARA GUARDAR ARCHIVOS CREACION AUTOMATICA (OMF):
ALTER SYSTEM SET db_create_file_dest = '/u01/oradata/dba01';
-PARA VER SI UN TS ES AUTOEXTEND EJECUTAR LA SIGUIENTE CONSULTA:
SELECT TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
-PARA CREAR UN TS CON VARIOS ARCHIVOS EN UNA SOLA SENTENCIA:
CREATE TABLESPACE NOM_MI_TS DATAFILE 'C:\DU.DBF' SIZE 1M, 'C:\DD.DBF' SIZE 2M;
ESTRUCTURAS:
ESTRUCTURA GENERAL DE CREACION:
CREATE TABLESPACE NOMBRETABLESPACE
DATAFILE 'NOMBREARCHIVO' [SIZE ENTERO [K | M] [REUSE]
[DEFAULT STORAGE
(INITIAL TAMAÑO
MINEXTENTS TAMAÑO
MAXEXTENTS TAMAÑO
PCTINCREASE VALOR
)]
[ONLINE | OFFLINE];
REUSE= Reutiliza el archivo si ya existe o lo crea si no existe.
DEFAULT STORAGE= Define el almacenamiento por omisión para todos los objetos que se creen en este espacio de la tabla. Fija la cantidad de espacio si no se especifica en la sentencia CREATE TABLE.
- ESTRUCTURA DE Modificación de tablespaces
ALTER TABLESPACE NOMBRETABLESPACE
{[ADD DATAFILE 'NOMBREARCHIVO' [SIZE ENTERO [K | M] [REUSE]
[AUTOEXTEND ON… | OFF]
]
[REANME DATAFILE 'ARCHIVO' [, 'ARCHIVO']…
TO 'ARCHIVO' [, 'ARCHIVO']]
[DEFAULT STORAGE CLAUSULAS_ALMACENAMIENTO]
[ONLINE | OFFLINE]
};
ADD_DATAFILE= Añade al tablespace uno o varios archivos.
AUTOEXTEND= Activa o desactiva el crecimiento automático de los archivos de datos del tablespace. Cuando un tablespace se llena podemos usar esta opción para que el tamaño del archivo o archivos de datos asociados crezca automáticamente.
Autoextend off: desactiva el crecimiento automático.
RENAME_DATAFILE= Cambia el nombre de un archivo existente del tablespace. Este cambio se tiene que hacer desde el sistema operativo y, después, ejecutar la orden SQL.
//////////////////////////////////////// ACTIVAR USUARIO DEL S.O/////////////////////
show parameters authent;
ALTER SYSTEM SET os_authent_prefix ="OPS$" SCOPE=SPFILE;
--USUARIO CON EL CUAL ESTAMOS CONECTADOS CON EL SO
select UPPER(sys_context('userenv','os_user')) from dual;
create user "OPS$LAURALANTHALAS\ABEL" IDENTIFIED BY EXTERNALLY;
GRANT CONNECT TO "OPS$LAURALANTHALASA\ABEL“;
Connect /
Show user;
/////////////////////////// VISTAS /////////////////////////////////////////////////
-1)CREAR UNA VISTA QUE MUESTRE SOLO LOS USUARIOS CONECTADOS AL SISTEMA --QUE CONTENGA USUARIO DEL SO, USUARIO DE LA BD, MAQUINA DESDE LA QUE --ESTA CONECTADO, HORA MINUTO Y SEGUNDO DESDE QUE SE CONECTO Y PROGRAMA --QUE ESTA UTILIZANDO.
CREATE VIEW MIvISTA
select username,machine,program,osuser,to_char(logon_time,'hh:mi:ss') from v$session;
-ESQUEMA GRAL
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[(
AS
[WITH CHECK OPTION [CONSTRAINT
[WITH READ ONLY [CONSTRAINT
P.E
CREATE VIEW EmpDepVentas
AS SELECT apellidos, nombre
FROM Empleados
WHERE dep=”Ventas”
WITH CHECK OPTION;
-VISTASPARA CONSULTAR:
SELECT * FROM USER_VIEWS;
-VISTAS CON ALIAS:
CREATE VIEW EmpDepVentas
AS SELECT apellidos Last_name, nombre First_name
FROM Empleados
WHERE dep=”Ventas”;
Ó
CREATE VIEW EmpDepVentas (Last_name, First_name)
AS SELECT apellidos, nombre
FROM Empleados
WHERE dep=”Ventas”;
-VISTAS COMPLEJAS
CREATE VIEW DepSalarios
(Departamento, Minimo, Maximo, Medio)
AS SELECT d.nombre, MIN(e.salario), MAX(e.salario), AVG(e.salario)
FROM Empleados e, Departamentos d
WHERE e.dep=d.nombre
GROUP BY d.nombre;
-ELIMINAR VISTA:
DROP VIEW NOMBRE_DE_LA_VISTA;
- MODIFICAR UNA VISTA:
UNA VISTA NO SE MODIFICA PERO SE PUEDE REESTRUCTURAR CON LA SENTENCIA
CREATE OR REPLACE VIEW nombre_vista AS SELECT APELLIDO FROM EMPLADO WHERE DEP="VENTAS";
/////////////////////// PROCEDIMIENTOS Y FUNCIONES ////////////////////////////////
- PROCEDIMIENTOS
CREATE or replace PROCEDURE AUMENTO (PCT_AUMENTO IN NUMBER) AS BEGIN UPDATE EMP SET SAL=SAL+(SAL*(PCT_AUMENTO/100));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE("No hay ningun empleado.");
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("Error ejecución.");
RAISE;
END;
- ESQUEMA
CREATE [OR REPLACE] PROCEDURE name [(param [IN|OUT|IN OUT|] datatype) . . .]
[IS|AS] pl/sql_subprogram
Los modificadores IN, OUT, IN OUT indican si el parametro es de entrada, salida o ambos.
- FUNCIONES
CREATE FUNCTION FUNCION_AUMENTO (ID_EMP IN NUMBER, PCT_AUMENTO IN NUMBER)
RETURN NUMBER
AS
PORCENTAJE NUMBER (11,2);
BEGIN
SELECT SAL*(PCT_AUMENTO/100)
INTO PORCENTAJE
FROM EMP
WHERE EMPNO=ID_EMP;
RETURN (PORCENTAJE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE("No hay ningun empleado.");
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("Error ejecución.");
RAISE;
END;
//////////////////// SECUENCIAS //////////////////////////////////////////////
Para crear una secuencia en Oracle mediante SQL utilizaremos el comando create sequence con la siguiente sintaxis:
CREATE SEQUENCE nombre_secuencia
INCREMENT BY numero_incremento
START WITH numero_por_el_que_empezara
MAXVALUE valor_maximo | NOMAXVALUE
MINVALUE valor_minimo | NOMINVALUE
CYCLE | NOCYCLE
ORDER | NOORDER
Por ejemplo, si queremos crear una secuencia que empiece en 100 y se incremente de uno en uno utilizaremos la siguiente consulta SQL:
CREATE SEQUENCE incremento_id_cliente
INCREMENT BY 1
START WITH 100
Para utilizar la secuencia, en primer lugar, crearemos una tabla de prueba (para insertar un registro y comprobar que la secuencia anterior funciona correctamente):
create table clientes (
codigo number not null primary key,
nombre varchar2(100) unique not null,
cif varchar2(15) unique,
fechaalta date)
Para utilizar la secuencia creada en una inserción de fila:
insert into clientes values (
incremento_id_cliente.NextVal,
'AjpdSoft',
'11225522F',
sysdate)
Realizamos otra inserción para comprobar que el incremento es de 1:
insert into clientes values (
incremento_id_cliente.NextVal,
'Otro cliente',
'00000G',
sysdate)
Como se puede observar en el ejemplo anterior, para obtener el siguiente valor de la secuencia almacenada se utiliza el comando: nombre_secuencia.NextVal.
/////////////////// ARCHIVOS DE INICIO PFILE Y SPFILE //////////////////////////////
1)CREAR PFILE A PARTIR DE SPFILE
ver si el spfile esta activo con:
show parameters spfile
si tiene un valor quiere decir que si esta activo, Y COMO ESTA ACTIVO UN SPFILE ENTONCES SI PODEMOS CREAR UN PFILE APARTIR DE ESTE.
CREATE PFILE='C:\ORACLE\ORA92\PFILENUEVO.ORA' FROM SPFILE;
2)crear SPFILE APARTIR DE PFILE
create SPFILE='C:\SPFILENUEVO.ORA' FROM PFILE='C:\ORACLE\ORA92\PFILENUEVO.ORA';
3)LEVANTAR LA BD APARTIR DEL PFILE
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\ORACLE\ORA92\PFILENUEVO.ORA';
4)ABRIR LA BD EN MODO DE SOLO LECTURA
STARTUP OPEN READ ONLY;
STARTUP OPEN READ WRITE;//MODO LECTURA ESCRITURA
5)PARA ABRIR LA BASE DE DATOS EN BASE DE UN SPFILE QUE SE ENCUENTRA EN OTRA LOCALIZACION, LO QUE HAY QUE HACER ES EDITAR EL ARCHIVO PFILE CORRIENTE Y AGREGAR EN EL PARAMETRO SPFILE LA DIRECCION DEL ARCHIVO QUE DESEAMOS USE PARA CARGAR LA BD.
//////////////////// DE EXAMENES ////////////////////////////////////////////////
1) DETERMINAR CUANDO SE REALIZO EL ULTIMO ANALISIS A NUESTRAS TABLAS
//PARA VERIFICAR
SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES;
//PARA ANALIZAR
ANALIZE TABLE MATERIA COMPUTE STATISTICS FOR TABLE;
2)OBTENER LA TABLA CON EL MAYOR NUMERO DE REGISTROS
SELECT TABLE_NAME FROM DBA_TABLES WHERE NUM_ROWS=(SELECT MAX(NUM_ROWS)FROM DBA_TABLES);
3)OBTENER EL NOMBRE Y DUEÑO DE LOS INDICES EN LOS CUALES TENGAMOS PROBLEMAS
SELECT INDEX_NAME,OWNER,STATUS FROM DBA_INDEXES WHERE STATUS='N/A';
SELECT INDEX_NAME,OWNER,STATUS FROM DBA_INDEXES WHERE STATUS!='VALID';
4)OBTENER MEDIANTE EL DICCIONARIO DE DATOS LA VERSION DE ORACLE INSTALADA
SELECT * FROM V$VERSION;
5)OBTENER MEDIANTE EL DICCIONARIO LOS TIPOS DE SEGMENTOS EXISTENTES EN NUESTRA BASE DE DATOS
SELECT UNIQUE(SEGMENT_TYPE) FROM DBA_SEGMENTS;
6)OBTENER EL ESTATUS Y NOMBRE DE LOS DATAFILES
SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;
7)CREAR UNA CONSULTA QUE MUESTRE EL NUMERO DE EXTENTS QUE TIENE UN SEGMENTO(TABLA) POR EJEMPLO: EL SEGMENTO VENTAS DEL ESQUEMA VENTAS
SELECT COUNT(EXTENT_ID) FROM DBA_EXTENTS WHERE SEGMENT_NAME='IDL_UB1$';
8)OBTENER LOS USUARIOS DEL SISTEMA QUE NO SE ENCUENTREN BLOQUEADOS
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';
9)OBTENER LOS TABLESPACES DEL SISTEMA Y EL TAMAÑO DE LOS MISMOS EN MB
SELECT FILE_NAME,TO_NUMBER(BYTES/1024/1024) FROM DBA_DATA_fILES WHERE TABLESPACE_NAME='SYSTEM';
////////////////////////////////// MOVER DATAFILES ///////////////////////////////
EXISTEN DOS TIPOS DE DATAFILES LOS QUE PERTENECEN AL SYSTEMA Y LOS QUE NO, PARA LOS DATAFILES DE SYSTEM HAY QUE MOVERLOS TENIENDO LA BASE DE DATOS UNICAMENTE MONTADA, MIENTAS QUE LOS OTROS HAY QUE MOVERLOS CON LA BASE DE DATOS ABIERTA, EL CAMBIO DESDE EL ORACLE SE HACE LOGICAMENTE, POR LO QUE LOS ARCHIVOS FISICOS YA SE DEBERAN DE ALLAR EN LA DIRECCION A LA QUE DESEAMOS CAMBIARLOS, ES DECIR EL DESTINO FISICO YA DEBE DE ALLARSE, PARA EVIAR HACER ESTE PROCEDIMIENTO PASO POR PASO, PODEMOS CREAR SCRIPT ATRA VEZ DE UN SELECT, PARA HACER ESTE CAMBIO POR LOTES, ESTE PROCESO ES EL SIGUIENTE:
1) COPIAR FISICAMENTE TODOS LOS DATAFILES YA SEAN DE SYSTEM O NO:
SELECT 'HOST COPY '||FILE_NAME ||' S'||FILE_NAME FROM DBA_DATA_FILES;
2)REDIRECCIONAR DATAFILES DE SYSTEM:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
SELECT 'ALTER DATABASE FILE '||FILE_NAME ||' TO S'||FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME IN ('SYSTEM','TEMP','UNDOTBS1');
3) ABRIR LA BD, DAR DE BAJA LOS TS Y REDIRECCIONAR TODOS LOS DATAFILES QUE NO SON DE SYSTEM:
ALTER DATABASE OPEN;
SELECT 'ALTER TABLESPACE '|| TABLESPACE_NAME|| ' OFFLINE' FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDOTBS1');
SELECT 'ALTER TABLESPACE '|| TABLESPACE_NAME ||' RENAME DATAFILE '||FILE_NAME||' TO S'||FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN ('U
NDOTBS1','SYSTEM');
4)LEVANTAR TODOS LOS TABLE ESPACE QUE SE ENCONTRABAN EN OFFLINE
SELECT 'ALTER TABLESPACE '|| TABLESPACE_NAME|| ' ONLINE' FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDOTBS1');
5) REINICIAR LA BD:
SHUTDOWN IMMEDIATE
STARTUP
////////////////// VARIAS FUNCIONES UTILES /////////////////////////////////////////
set long 1000 PARA VER POR COMPLETO EL TEXTO DE UNA CONSULTA
Funciones SQL
Las funciones SQL permiten mostrar columnas calculadas dentro de sentencias DML (SELECT, INSERT, DELETE y UPDATE).
Funciones de tratamiento numérico
|| Función || Descripción ||
|| ABS( n ) || Retorna el valor absoluto del parámetro. ||
|| CEIL( n ) || Retorna el entero mayor del parámetro. ||
|| FLOOR( n ) || Retorna el entero menor del parámetro. ||
|| MOD( m,n ) || Retorna el resto de la división m/n ||
|| POWER( m,n ) || Retorna mn ||
|| ROUND( m[,n] ) || Retorna m, redondeado a n decimales. Si m se omite es 0. ||
|| SIGN( n ) || Retorna 1 si n es positivo, -1 si negativo y 0 si es 0. ||
|| TRUNC( n[,m] ) || Trunca un número a m decimales. Si m se omite es 0. ||
Funciones de tratamiento alfanumérico Funciones de tratamiento de fechas
|| Función || Descripción ||
|| CHR( n ) || Retorna el carácter equivalente al código n en la tabla de ||
|| || conjunto de caracteres utilizado (ASCII, UNICODE...) ||
|| CONCAT( s1, s2 ) || Concatena dos cadenas de caracteres. Equivalente al operador ||
|| || || ||
|| INITCAP( s ) || Pasa el mayúscula la primera letra de cada palabra ||
|| LOWER( s ) || Pasa a minúsculas toda la cadena de caracteres ||
|| LPAD( s, n ) || Retorna los n primeros caracteres de la cadena s. ||
|| RPAD( s, n ) || Retorna los n últimos caracteres de la cadena s. ||
|| LTRIM( s1[, s2] ) || Elimina todas las ocurrencias de s2 en s1 por la izquierda. Si se ||
|| || omite s2, se eliminarán los espacios. ||
|| RTRIM( s1[, s2] ) || Elimina todas las ocurrencias de s2 en s1 por la derecha. Si se ||
|| || omite s2, se eliminarán los espacios. ||
|| REPLACE( s1, s2, s3 ) || Retorna s1 con cada ocurrencia de s2 reemplazada por s3. ||
|| SUBSTR( s, m, n ) || Retorna los n caracteres de s desde la posición m. ||
|| UPPER( s ) || Pasa a mayúsculas toda la cadena de caracteres ||
|| LENGTH( s ) || Retorna la longitud (en caracteres) de la cadena pasada. ||
|| Función || Descripción || ||
|| ADD_MONTHS( d, n ) || Suma un número (positivo o negativo) de meses fecha. || a una ||
|| LAST_DAY( d ) || Retorna el ultimo día de mes de la fecha pasada. || ||
|| MONTHS_BETWEEN( d1, d2 ) || Retorna la diferencia en meses entre dos fechas. || ||
|| ROUND( d, s ) || Redondea la fecha d según el formato indicado en s. ( || *) ||
|| TRUNC( d, s ) || Trunca la fecha d según el formato indicado en s. (*) || ||
Formatos para ROUND y TRUNC para fechas:
|| Formato || Descripción ||
|| ‘MONTH’, ‘MON’, ‘MM’ || Principio de mes ||
|| ‘DAY’, ‘DY’, ‘D’ || Principio de semana ||
|| ‘YEAR’, ‘YYYY’, ‘Y’ || Principio de año ||
Funciones de grupo
Estas funciones actúan sobre un conjunto de valores, retornando sólo un registro.
|| Función || Descripción ||
|| SUM( valores ) || Retorna la suma. ||
|| AVG( valores ) || Retorna la media aritmética ||
|| MAX( valores ) || Retorna el máximo. ||
|| MIN( valores ) || Retorna el mínimo ||
|| COUNT(valores|* ) || Retorna la cuenta. ||
|| Todas estas funciones permite incluir el modificador DISTINCT delante de la lista de valores para que omita los repetidos. ||
Funciones de conversión
|| Función || Descripción ||
|| CHARTOROWID( s ) || Convierte una cadena en tipo de dato ROWID. ||
|| ROWIDTOCHAR( rowid ) || Convierte un tipo de dato ROWID en cadena de caracteres. ||
|| TO_CHAR( *[, s] ) || Convierte el tipo de dato * en cadena de caracteres. Si * ||
|| || es una fecha, se podrá utilizar la cadena s como formato ||
|| || de conversión. ||
|| TO_DATE( s1[, s2] ) || Convierte la cadena s1 en fecha, conforme al formato de ||
|| || convesión s2. ||
|| TO_NUMBER( s ) || Convierte una cadena de caracteres en valor numérico. ||
Otras funciones
|| Función || || Descripción ||
|| DUMP( columna ) || || Retorna información de almacenamiento para la columna ||
|| || || indicada. ||
|| GREATEST( expr1, expr2 || , ... || Retorna la expresión mayor. ||
/////////////////////////////////////////// PROCEDIMIENTOS /////////////////////////
- CREAR PROCEDIMIENTO
CREATE or replace PROCEDURE AUMENTO (PCT_AUMENTO IN NUMBER)
AS
BEGIN UPDATE EMP_TABLE
SET SAL=SAL+(SAL*(PCT_AUMENTO/100));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay ningun empleado.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ejecución.');
[RAISE_APPLICATION_ERROR(-20000,'ERROR: '||SQLCODE); | RAISE;]
END;
- EJEMPLO
NOTA_ PARA ACTIVAR LOS MENSAGES DESDE LA LINEA DE TEXTO HAY QUE PONER EN CONSOLA LA SIGUIENTE LINEA:
set serveroutput on
CREATE OR REPLACE PROCEDURE MIPROCEDIMIENTO(VALOR IN NUMBER)
AS
BEGIN
DECLARE
VALOR2 NUMBER;
NUEVA NUMBER:=0;
BEGIN
SELECT EDAD INTO VALOR2 FROM PRUEBAS WHERE ID=VALOR;
IF VALOR2<30 nueva="1" codigo="5;" codigo="5;" proceso="" execute="" esquema="" name="" param="" pl="" sql_subprogram="" los="" modificadores="" indican="" si="" el="" parametro="" es="" salida="" o="" procedimiento="" con="" de="" procedure="" p1="" table_name="" out="" open="" for="" variable="" refcursor="" exec="" dual="" print="" c="" funciones="" crear="" funcion="" consulta_inventario="" cosa="" precio="" regreso="" inventario="" existe="" tal="" articulo="COSA;" funcion_aumento="" id_emp="" in="" as="" pct_aumento="" into="" emp="" where="" empno="ID_EMP;" exception="" no_data_found="" then="" no="" hay="" ningun="" empleado="" when="" others="" then ="" error="" para="" ejecutar="" otra="" select="" porcentaje="" from="" ejemplo="" tiwire="" ejemplilloo="" create="" or="" replace="" function="" valor="" number="" is="" begin="" return="" n="" sql=""> var x number;
SQL> exec :x:=ejemplo(5);
Procedimiento PL/SQL terminado correctamente.
SQL> print x;
X
----------
10
//////////////////////////// EXAMENES RESUELTOS //////////////////////////////////
1)REALIZAR IMPORTACION DEL RESPALDOLOGICO
PRIMERO HAY QUE SABER QUE SE ESTA EXPORTANDO, ESTO SE HACE HACIENDO UNA IMPORTACION COMPLETA:
HOST IMP SYSTEM/ABCD FILE=C:\v$audit_hcge.dmp FULL=Y SHOW=Y;
EN ESTE CASO OBSERVAMOS QUE QUEREMOS IMPORTAR UNA TABLA, EN EL MISMORESULTADO PODEMOS VER EL NOMBRE Y DUEÑO DE LA TABLA, POR LO QUE AHORA REALIZAMOS EL RESPALDO DE LA SIGUIENTE MANERA:
HOST IMP SYSTEM/ABCD FILE=C:\v$audit_hcge.dmp FROMUSER=SYS TOUSER=SCOTT TABLES=v$audit_hcge
2)REALIZAR RESPALDO LOGICO DEL ESQUEMA HR
HOST EXP USERID=SYSTEM/ABCD FILE =C:\REPHRAVL.DMP OWNER=HR LOG= HRLOG.LOG;
3)RESPALDO EN FRIO DE LA BD
SHUTDOWN IMMEDIATE
COPIAR CARPETA DE LA INSTANCIA (DEBERA CONTENER ARCHIVOS REDOLOG,DATAFILES Y CONTROLFILES)
COPIAR EL SPFILE Y EL PFILE
COMPRIMIR CARPETA
DAR DE ALTA LA BD
STARTUP
4)ACTIVAR AUDITORIA Y MOSTRAR REGISTROS
CHECAMOS QUE ESTE ACTIVA LA UDITORIA
SHOW PARAMETERS AUDIT_TRAIL=TRUE
SI NO ESTA ACTIVA, ACTIVARLA CON:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER SYSTEM SET AUDIT_TRAIL=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
AUDIT SESSION WHENEVER SUCCESSFUL;
SELECT USERNAME, ACTION_NAME FROM DBA_AUDIT_TRAIL;
5)TRIGGER
6)CALCULAR LAS ESTADISTICAS SOBRE EL SEGMENTO SCOTT.EMP
ANALYZE TABLE SCOTT.EMP COMPUTE STATISTICS;
SELECT TABLE_NAME,CHAIN_CNT FROM ALL_TABLES WHERE CHAIN_CNT>0;
7)OBTENER LOS TABLESPACES DEL SYSTEMA Y SU TAMAÑO:
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
-------------------------------
PARA EXPORTAR UTILIZANDO QUERIS
exp scott/tiger tables=HR.emp query="where deptno=10"
exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes
////////////////////////////////////////// EXEPCIONES //////////////////////////////
set serveroutput on
DECLARE
vprecio inventario.precio%TYPE;
BEGIN
SELECT precio INTO vprecio FROM inventario
WHERE cantidad = 100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay ningún artículo.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Hay más de un artículo');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error, abortando ejecución.');
END;
[Otras sentencias]
END;
DUP_VAL_ON_INDEX - Se produce cuando se intenta almacenar un valor ya existente en una columna que tiene restricción de índice único.
TIMEOUT_ON_RESOURCE - Se excedió el tiempo máximo de espera por un recurso en Oracle.
NOT_LOGGED_ON - El programa efectuó una llamada a Oracle sin estar conectado.
LOGIN_DENIED - El login o la contraseña utilizados para entrar en Oracle son inválidos.
NO_DATA_FOUND - Una sentencia SELECT INTO no devolvió ningún registro.
TOO_MANY_ROWS - Una sentencia SELECT INTO devolvió más de un registro.
ZERO_DIVIDE - Se ha ejecutado una división donde el divisor valía cero.
STORAGE_ERROR - Si no se dispone de más memoria o la memoria esta dañada.
PROGRAM_ERROR - Ocurrió un problema interno al ejecutar el código PL/SQL.
INVALID_NUMBER - Cuando falla la conversión de una cadena de caracteres hacia un número porque la cadena no representa un número válido.
VALUE_ERROR - Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, esto sucede cuando se intenta dar un valor muy grande a una variable que no soporta dicho tamaño.
ROWTYPE_MISMATCH - Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) son de tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado.
SYS_INVALID_ROWID - Falla la conversión de una cadena de caracteres hacia un tipo rowid porque la cadena no representa un número.
INVALID_CURSOR - Se efectuó una operación no válida sobre un cursor. Suele ocurrir cuando un cursor no está abierto y se ejecuta una sentencia para cerrar dicho cursor.
CURSOR_ALREADY_OPEN - Cuando se intenta abrir un cursor que ya estaba abierto. Hay que recordar que un cursor de tipo FOR se abre automáticamente por lo que no se debe ejecutar la sentencia OPEN.
ACCESS_INTO_NULL - Se intentó asignar un valor a los atributos de un objeto no inicializado.
COLLECTION_IS_NULL - Se intentó asignar un valor a una tabla anidada aún no inicializada.
SELF_IS_NULL - El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo.
/////////////////////////// TRIGGERS //////////////////////////////////////////
TRIGGER:
1- Primero seleccionar una tabla como esta:
* create table alukno(nombre varchar(10), salon number);
**********************************************************
2- crear una tabla que va a guardar lo que le manda el trigger
* create table piglet(nombre varchar(10), salon number, salon_nuevo number);
**********************************************************
3- crear trigger:
SQL> create trigger alumno
2 before
3 insert or update of salon
4 on alukno
5 for each row
6 begin
7 insert into piglet values(:old.nombre, :old.salon, :new.salon);
8 end;
9 /
Explicacion:
- (before) puede llevar before o after, segun queramos que se ejecute el trigger antes o despues de
actualizar la tabla.
- (insert or update of salon) especificamos cuando queremos qe se ejecute el trigger y sobre qe
campo.
- (on alukno) le decimos que tabla es la qe esperamos qe se actualice
- (insert into piglet values(:old.nombre, :old.salon, :new.salon);) le4 decimos en qe tabla
va a guardar lo qe especificamos, se pone old qe es lo qe jala de la tabla y qe ya estaba, y
y se pone new qe son los valores nuevos qe cambiamos con el update.
***************************************************************
4- Por ultimo hacer un update a la tabla y verificar qe la nueva tabla contenga un nuevo registro
SQL> select * from alukno;
NOMBRE SALON
---------- ----------
chabela 400
karla 333
ariadna 123
SQL> update alukno set salon=333 where nombre='chabela';
1 fila actualizada.
SQL> select * from alukno;
NOMBRE SALON
---------- ----------
chabela 333
karla 333
ariadna 123
SQL> select * from piglet;
NOMBRE SALON SALON_NUEVO
---------- ---------- -----------
chabela 400 333
- ESTRUCTURA:
CREATE [OR REPLACE] TRIGGER
BEFORE|AFTER
DELETE|INSERT|UPDATE|SELECT
OF COL1,COL2,COL3....
[OR DELETE|INSERT|UPDATE OF COL1,COL2....]
ON TABLE
[REFERENCING OLD AS OLDNAME, NEW AS NEWNAME]
[FOR EACH ROW [WHEN (CONDICION)]]
BEGIN
.
.
.
CODIGO PLSQL;
////////////////////////////////// PLSQL /////////////////////////////////////
IF CONDICION THEN
.
.
.
ELSE
.
.
.END IF;
WHILE CONDICION LOOP
.
.
.
END LOOP;
FOR I IN 1..5 LOOP
.
.
.
END LOOP
///////////////////////////////// CURSORES /////////////////////////////////
DECLARE CURSOR NOMBRE_CURSOR (PARAMETRO)
IS
CONSULTA;
EJEMPLO:
DECLARE CURSOR CVENTAS(CFECHA DATE)
IS
SELECT ARTICULO,VALOR FROM VENTAS WHERE FECHA=CFECHA ORDER BY VALOR DESC;
XARTICULO VENTAS.ARTICULO%TYPE;
XVALOR VENTAS.VALOR%TYPE;
BEGIN
OPEN CVENTAS('11-02-08');//ABRIENDO CURSOR
FOR I IN 1..5 LOOP
FETCH CVENTAS INTO XARTICULO,XVALOR;
EXIT WHEN CVENTAS%NOFOUND;
INSERT INTO VENTAMAYOR VALUES(XARTICULO,XVALOR);
COMMIT;
END LOOP;
CLOSE CVENTAS;// CERRAR CURSOR
END;
/////////////////////////// RESPALDOS LOGICOS //////////////////////////////////////
EXPORT E IMPORT
-OJO PRIMERO PORNER LA BASE DE DATOS EN MODO RESTRICTED:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER DATABASE RESTRICTED SESSION;
HOST IMP HELP=YES;
HOST EXP HELP=YES;
para import:
inctype=complete -- tipo de exportacion incremental (solo cambios dados en
un lapso de tiempo)
constraints=Y -- exporta los constraints
usar el parametro SHOW para mostrar los archivos que se van a importar
COPIA DEL FICHERO DE CONTROL EN MODO BINARIO
(puede ser copiado mientras la BD está abierta):
ALTER DATABASE BACKUP CONTROLFILE TO 'C:\CTRLFILECOPYBIN.CTL';
COPIA DEL FICHERO DE CONTROL EN MODO TEXTO (SCRIPT)
(puede ser copiado mientras la BD está abierta):
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS c:\CTRLFILECPYTXT.SQL’;
--------------------------------------------- IMPORT --------------------------------
Importando las tablas de un usuario
imp userid=system file=ventas.dmp fromuser=ventas touser=userx log=ventas.log
Utilizar la copia más reciente del import para restaurar las definiciones del sistema:
$ imp userid=sys/passwd inctype=system full=Y file=export_filename
Poner los segmentos de rollback online.
Importar el fichero de exportación completa más reciente:
$ imp userid=sys/passwd inctype=restore full=Y file=filename
Importar los ficheros de exportación en modo acumulación desde la exportación completa más reciente, en orden cronológico:
$ imp userid=sys/passwd inctype=restore full=Y file=filename
Importar los ficheros de exportación en modo incremental desde la exportación completa o acumulativa más reciente, en orden cronológico:
$ imp userid=sys/passwd inctype=restore full=Y file=filename
IMPORTAR ESQUEMA
IMP USERID=SYSTEM FILE=MINSUPER_JUAN.DMP FROMUSER=ELBUENAS TOUSER=JUAN_EL_TRAIDOR SHOW=Y;
IMP USERID=ELBUENAS/BUENAS FILE=MINSUPER_JUAN.DMP FROMUSER=ELBUENAS TOUSER=JUAN_EL_TRAIDOR SHOW=Y;
IMPORTAR TABLAS DE USUARIO
IMP USERID=SYSTEM/SYSTEM@CONEXIONNUEVA FILE=C:\EXPORTA.DMP FROMUSER
(ORIGEN)VENTAS TOUSER ALQUESEPASAUSERS TABLES CLIENTES ROWS=N LOG=LOGOLD.LOG
BACKUP LOGICO
//OJO PRIMERO PORNER LA BASE DE DATOS EN MODO RESTRICTED
alter system enable restricted session
SHUTDOWN
STARTUP RESTRICT
alter database default temporary tablespace temp;
DESDE MS-DOS
IMP HELP=YES;
EXP HELP=YES;
exp userid=system/itt2006@itt2006 full=yes file=itt2006.dmb
------------------------------------------ EXPORT ----------------------------------
-RESPALDO COMPLETO:
EXP USERID=SYSTEM INCTYPE=COMPLETE FILE=C:\NOMBRE.DAT FULL=Y LOG=LOG_NOMBRE.LOG
-Respaldar los objetos de un usuario:
Indicamos que haremos el respaldo con usuario system y exportar todos los
objetos en los que el dueño sea ventas.
HOST exp userid=system owner=ventas file=ventas.dmp log=ventas.log
-EXPORTAR TABLAS DE UNA BD:
HOST exp userid=system file=ventas_ventas.dmp tables=(ventas.ventas,ventas.productos,ventas.proveedores) log=ventas.log
-ACOMULATIVA (OBJETOS CREADOS DEPUES DEL ULTIMO EXPORT)
EXP userid=system/manager full=y inctype=cumulative constraints=Y file=cumulative_export_filename
INCREMENTAL(OBJETOS MODIFICADOS O CREADOS DESDE ULTIMO EXPORT)
Exp userid=system/manager full=y inctype=incremental constraints=Y file=incremental_export_filename
FULL EXPORT (TODA LA BD)
EXP USERID=SYSTEM FILE = MINSUPER.DMP FULL=YES LOG= MINSUPER.LOG;
//DONDE SYSTEM ES UN USUARIO CON PRIVS DE SYSDBA
TODA LA BASE
EXP userid=system/manager full=y inctype=complete constraints=Y file=full_export_filename;
OBJECT EXPORT (SOLO UN OBJETO)
EXP USERID=SYSTEM FILE = MINSUPER_ELBUENAS_MARACAS.DMP
TABLES=ELBUENAS.MARACAS LOG= MINSUPER_ELBUENAS_MARACAS.LOG;
USER EXPORT (SOLO UN ESQUEMA)
EXP USERID=SYSTEM FILE = MINSUPER_JUAN.DMP OWNER=JUAN LOG= MINSUPER_JUAN.LOG;
BACKUP MAS OPTIMO
Activar el modo ARCHIVELOG:
CHECAR QUE NO ESTE ACTIVO: SELECT LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
SELECT LOG_MODE FROM V$DATABASE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%S.ARC' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST='C:\' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
ALTER SYSTEM SWITCH LOGFILE;
Realizar un backup al menos una vez a la semana si la BD se puede parar. En otro caso, realizar backups en caliente cada día.
Copiar todos los ficheros redo log archivados cada cuatro horas. El tamaño y el número de ellos dependerá de la tasa de transacciones.
Efectuar un export de la BD semanalmente en modo RESTRICT.
///////////////////// RESPALDOS EN CALIENTE Y FRIO ////////////////////////////
-------------------------- RESPALDO EN FRIO -----------------------------
CONN SYSTEM
SELECT LOG_MODE FROM V$DATABASE;
SHOW PARAMETER LOG_ARCHIVE_START;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
SELECT LOG_MODE FROM V$DATABASE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%S.ARC' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST='C:\' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
ALTER SYSTEM SWITCH LOGFILE;
1. Listar los datafiles, controlfiles y logfiles. Esto se hace ejecutando:
select file_name from dba_datafiles;
select name from v$controlfile;
select member from v$logfile;
2. Ejecutar un shutdown normal o inmediato de la base de datos.
3. Copiar con un utilitario del sistema operativo todos los archivos listados en el paso 1 hacia un medio de backup preferido como cinta, disco duro, otra máquina, etc.
--------------------------- RESPALDO EN CALIENTE ----------------------------------
BACKUP EN CALIENTE (SE HACE POR CADA TSPACE A RESPALDAR)
ALTER TABLESPACE TS_USERS BEGIN BACKUP;
COPIAMOS EL ARCHIVO FISICAMENTE A LA DIRECCION DESEADA
ALTER TABLESPACE TB_USERS END BACKUP;
ALTER SYSTEM SWITCH LOGFILE;
HACEMOS RESPALDO DE LOS ARCHIVOS DE CONTROL:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\RESPCONTROLFILE.SQL';
SELECT LOG_MODE FROM V$DATABASE;
SHUTDOWN NORMAL;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SELECT LOG_MODE FROM V$DATABASE;
SHOW PARAMETER LOG_ARCHIVE_START;
ALTER SYSTEM SET LOG_ARCHIVE_DEST='C:\ARCHIVE\' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
ALTER TABLESPACE USERS BEGIN BACKUP;
HOST COPY C:\oracle\oradata\minsuper\users01.DBF C:\backup;
ALTER TABLESPACE USERS END BACKUP;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘C:\CTRLFILECOPYTXT.SQL’;
SCRIPT QUE HACE BACKUP EN CALIENTE DE TODOS LOS TSPACE
SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||'BEGIN BACKUP; HOST COPY '||FILE_NAME||' C:\BACKUP ‘FROM DBA_DATA_FILES;
///////////////////////////// RECUPERACION /////////////////////////////////
RECUPERAR DE UN TABLESPACE (la parte que no se usa de la bd puede estar abierta pero el ts a recuperarse debe estar en offline)
RECOVER FROM redologfile TABLESPACE nom_ts
RECUPERAR DE UN DATAFILE(bd abierta (si es el de system debe estar la bd en modo mount Y fichero en offline)
RECOVER FROM redologfile DATAFILE ruta+nom_datafile
SQL> alter database datafile 'c:\oracle\oradata\salab07\USERS01.dbf' OFFLINE;
SQL> RECOVER datafile 'c:\oracle\oradata\salab07\USERS01.dbf';
SQL> alter database datafile 'c:\oracle\oradata\salab07\USERS01.dbf' ONLINE;
CREACION DE UN FICHERO DE CONTROL (cuando se pierde)
//bd en estado nomount
CREATE CONTROL FILE
RECUPERAR REDOLOG POR DIA
ARCHIVE LOG LIST;
MANTENER CONTROLFILE A SALVO
alter database backup controlfile to trace;
FORZAR A UN CHECKPOINT (OBLIGANDO QUE TRANSACCIONES SE ESCRIBAN EN LA BD)
ALTER SYSTEM LOG FILE;
ALTER SYSTEM CHECKPOINT LOCAL;
ALTER SYSTEM CHECKPOINT GLOBAL;
APLICACION DEL REDOLOG
Cuando una BD se arranca con el comando startup, la BD pasa por los estados nomount, mount y open. En este tercer
estado, se verifica que se pueden abrir todos los ficheros de log y de datos. Si la BD se arranca por primera vez
después de una caida, se necesitará efectuar una recuperación que consiste en dos pasos: avanzar la BD hacia adelante
aplicando los registros redo log, deshacer las transacciones no confirmadas.
RECUPERACION INCOMPLETA Y LEVANTAR BASE
ALTER DATABASE OPEN RESETLOGS;
RECUPERACION
bd modo archive;
bd mount
RECOVER [AUTOMATIC] [FROM 'localizacion'] [VALOR] nombre_valor
[UNTIL CANCEL]
[UNTIL TIME fecha]
[UNTIL CHANGE entero]
[USING BACKUP CONTROLFILE] donde valor puede ser de un datafile, de la bd completa o del redo != al log_archive_dest)
UNTIL CANCEL recuperar un redo log cada vez, parando cuando se teclea CANCEL.
UNTIL TIME recuperar hasta un instante dado dentro de un fichero de redo log
UNTIL CHANGE recuperar hasta un SCN dado.
USING BACKUP CONTROLFILE utiliza una copia de seguridad del fichero de control para gobernar la recuperación.
//////////////////////////////////////// AUDITORIA ////////////////////////////
1) COMPROBAR SI ESTA HABILITADA LA AUDITORIA:
SHOW PARAMETERS AUDIT_TRAIL
SI NO ESTA ACTIVADA HACER:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER SYSTEM SET AUDIT_TRAIL=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
- PARA VER LOS USUARIOS CON PRIVILEGIOS DE AUDITSYSTEM Y AUDIT ANY
SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE='AUDIT ANY';
SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE='AUDIT SYSTEM';
-PARA AUDITAR TODAS LAS CONEXIONES EXITOSAS Y FALLIDAS
AUDIT SESSION;
2)ABRIR SESSION CON DOS USUARIOS DIFERENTES. COMPROBAR QUE INFORMACION SE AH GUARDADO EN LA AUDITORIA Y CONSULTAR
DBA_AUDIT_TRAIL
SELECT USERNAME, ACTION_NAME FROM DBA_AUDIT_TRAIL;
-PARA AUDITAR CONECCIONES DE PRUEBA1 Y PRUEBA2 HACER:
AUDIT SESSION BY PRUEBA1, PRUEBA2;
-AUDITAR SELECT TABLE,DELETE TABLE PARA CUALQUIER CASO
AUDIT SELECT TABLE,DELETE TABLE, UPDATE TABLE, INSERT TABLE BY SCOOT;
VER TABLA DBA_STMT_AUDIT PARA CHECAR LAS OPCIONES ACTIVAS
SELECT USER_NAME USUARIO,AUDIT_OPTION OPCION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME;
ESPECIFICAR OPCIONES DE AUDITORIA PARA OBJETOS CREADOS EN UN FUTURO, DE FORMA QUE REGISTRARA INFORMACION SIEMPRE QUE SE HAGA UN ALTER, GRANT,INSERT,UPDATE O DELETE
AUDIT ALTER,GRANT,INSERT,UPDATE,DELETE ON DEFAULT;
PARA VERIFICAR:
SELECT * FROM ALL_DEF_AUDIT_OPTS;
-PARA DESACTIVAR LA AUDITORIA DE SESSIONES:
NOAUDIT SESSION;
NOAUDIT SESSION BY PRUEBA1,PRUEBA2;
NOAUDIT SELECT TABLE,INSERT TABLE,DELETE TABLE BY SCOTT;
NOAUDIT ALL ON DEFAULT;
HACER PARA VERIFICAR:
SELECT USER_NAME USUARIO,AUDIT_OPTION OPCION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME,AUDIT_OPTION;
ACTIVAR AUDITORIA PARA CUALQUIER OPERACION EN UNA TABLA:
AUDIT SELECT,INSERT,DELETE ON SCOTT.CODIGOS_NOTAS;
AUDITORIAS
AUDIT {DELETE||UPDATE||INSERT} (ON ESQUEMA.NOM_TABLE|| ANY TABLE U OBJETO);
AUDIT create any trigger;
AUDIT SELECT ON emi.orders;
AUDIT ALL ON Juan.productos BY ACCESS;
AUDIT ALL ON esquema.tabla BY { ACCESS || SESSION };
audit table;
noaudit table;
DE UN USUARIO:
audit table by perez;
TABLA
audit insert on perez.emp;
audit delete on perez.emp by access
AUDITANDO AL USUARIO SYS
________________________________________________________________________________________________
PARAMETROS: SELECT name, value FROM gv$parameter WHERE name LIKE '%audit%';
________________________________________________________________________________________________
MODIFICAR: ALTER SYSTEM SET audit_sys_operations=TRUE COMMENT='Begin auditing SYS' SCOPE=SPFILE;
shutdown immediate
startup
PARA AUDITORIA POR SISTEMA
VISTAS
DESC SYS.AUD$
ALL_DEF_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_OBJ_AUDIT_OPTS
DBA_AUDIT_TRAIL
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
PROBLEMAS CON TABLESPACE SYSTEM POR AUMENTO DE TAMAÑO
ALTER SYSTEM SET audit_trail='XML' COMMENT='Audit trail as XML' SCOPE=SPFILE;
shutdown immediate
startup
ALTER SYSTEM SET audit_trail='db' COMMENT='Change auditing to sys.aud$' SCOPE=SPFILE;
NONE Deshabilita la auditoría
BD Habilita la auditoría, escribiendo en la tabla SYS.AUD$.
OS Habilita la auditoría, dejando al SO su gestión.
____________________________________________________________________________________________________________________
____________________________________________________________________________________________________________________
SESIONES
SVRMGR> audit session;
Para determinar si se deben registrar sólo los éxitos, o sólo los fracasos se pueden utilizar los siguientes comandos:
____________________________________________________________________________________________________________________
audit session;
audit session whenever successful; (EXITOS)
audit session whenever not successful; (FRACASOS)
SELECT USERID, USERHOST,TERMINAL FROM SYS.AUD$;
____________________________________________________________________________________________________________________
select
os_username, /* nombre de usuario SO */
username, /* nombre de usuario BD */
terminal,
decode(returncode,'0','Conectado',
'1005','Solo username, sin password',
'1017','Password incorrecto',
returncode), /* comprobacion de error */
to_char(timestamp,'DD-MON-YY HH24:MI:SS'), /* hora de entrada */
to_char(logoff_time,'DD-MON-YY HH24:MI:SS') /* hora de salida */
from dba_audit_session;
noaudit session;
ACCIONES SOBRE SYS.ADU$
audit all on sys.aud$ by access;
DEFINIR GUIA DE AUDITORIA
ALTER SYSTEM SET AUDIT_TRAIL = TRUE SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
////////////////////////////////////////////// CADENA DE CONEXION //////////////////////////////////////////
PARA HACER LA CADENA DE CONEXION MANUALMENTE
BUSCAR ARCHIVO TNSNAMES.ORA Y MODIFICAR UN BLOQUE P.E:
ALFONSO=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.123)(PORT = 1521))
)
(CONNECT_DATA =
(SID=EXFINAL)
(SERVER = DEDICATED)
)
)
Y CONECTARSE DESDE CONSOLA CON:
CONN SYSTEM/ABCD @ALFONSO
PARA CONECTARSE A OTRA MAQUINA Y EXPORTAR TABLAS:
HOST EXP USERID=SYSTEM/ABCD@ALFONSO TABLES=SYSTEM.ABEL,SCOTT.EMP FILE='C:\ARCHIVO3.DMP' LOG='C:\MMDA.LOG';
PARA EXPORTAS ESQUEMAS
HOST EXP USERID=SYSTEM/ABCD@ALFONSO OWNER=SYSTEM FILE='C:\ARCHIVO.DMP' LOG='C:\MMDA.LOG';
PARA DESCONECTARTE HACER:
DISCONN
///////////////////////////////// PLAN DE EJECUCION ////////////////////////////////
1)CREAR TABLA PLAN_TABLE MEDIANTE EL SCRIPT UTLXPLAN.SQL
2)PONER: SET AUTOTRACE ON
ESTO VERIFICA LA CORRECTA CREACION DE LA TABLA
3)CREAMOS SINONIMO DE TABLA: CREATE PUBLIC SYNONYM plan_table FOR plan_table;
4)ASIGNARLE PRIVILEGIOS PUBLICOS A LA TABLA: GRANT ALL ON plan_table TO public
5)ACTIVAR ROL PLUSTRACE CON EL ARCHIVO PLUSTRCE.SQL
6)ASIGNAR ESTE ROL PRIVILEGIOS PUBLICOS: GRANT plustrace TO PUBLIC;
SET AUTOTRACE OFF;
7)VERIFICAR QUE LOS DEMAS USUARIOS YA PUEDAN TENER ACCESOS DESDE PUBLIC
8)VERIFICAMOS EL CAMPO COSTO Y OPTIMIZAMOS Y VOLVEMOS A CHECAR EL CAMPO PARA VER SI SE REDUCIO Y NUESTRA OPTIMIZACION FUNCIONO.
PÀRA un tabla;
explain plan for select * from tablauno;
PARA PLAN DE EJECUCION
La diferencia es que la información del plan se muestra después de completarse la consulta mientras que la información de salida de explain plan se genera sin llegar a ejecutar el comando.
//El Explain plan nos permite llenar la tabla PLAN_TABLE creda con datos sobre el plan de ejecución de la consulta en una tabla del esquema.
Explain plan Set Statement_Id= ‘TEST’ For Select * from EMP Where sal>’0’;
//Checarlo con
SELECT ID,TIMESTAMP,CPU_COST,COST,IO_COST,OPERATION FROM PLAN_TABLE WHERE STATEMENT_ID=‘TEST’;
SET AUTOTRACE OFF
No genera el reporte (default).
SET AUTOTRACE ON EXPLAIN
Muestra la ruta de ejecución de la consulta.
SET AUTOTRACE ON STATISTICS
Muestra solo las estadísticas de la ejecución de la consuta.
SET AUTOTRACE ON
Reporta las rutas y estadisticas de ejecución de la consulta.
SET AUTOTRACE TRACEONLY
Elimina la salida de la consulta.
SET AUTOTRACE ON
Genera reporte.
//En sTATISTICS tambien podemos observar el numero de bytes
enviados/recibidos por la red
//en base a el "peso" de la consulta
////////////////////////////////// ESTADISTICAS //////////////////////////////
CHECA TODA LA BD:
Exec DBMS_utility.analyze_database(‘COMPUTE’);
- Calcula estadisticas sobre un esquema
Exec DBMS_utility.analyze_schema(‘HR’,’compute’);
- Calcula estadisticas sobre una tabla
Analyze table ser_esc.alumnos compute statistics;
- Verificar si hay migración de datos
Select table_name,chain_cnt from all_tables where chain_cnt>0;
Ejecutar como sysdba este package el cual nos da informacion estadistica
hacerca de la B.D
connect / as sysdba
exec DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
/HACER CONSULTA
SELECT table_name,chain_cnt FROM all_tables WHERE CHAIN_CNT>0;
CHAIN_CNT>0 son la tablas que se deberian de reconstruir por que tienen
bloques enlazados
moviendola de TSpace y regresarlas a su TS
ANALIZAR ESTADISTICAS DE TABLA
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
ANALIZAR ESTADISTICAS DE ESQUEMA
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
ANALIZAR ESTADISTICAS DE LA BD. COMPLETA
EXEC DBMS_STATS.gather_database_stats;
ANALIZAR UNA TABLA Y SU INDICE
ANALYZE TABLE employees COMPUTE STATISTICS; Select table_name,chain_cnt from all_tables where chain_cnt>0;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;
ANALIZAR LA BD. COMPLETA
EXEC DBMS_UTILITY.analyze_database('COMPUTE');
Indices Funcionales
//Sirven para crear índices a partir de condiciones//QUERY REWRITE y el parámetro QUERY_REWRITE_ENABLED en true.
Create index emp_upper_name on employees(upper(name))
MOVER INDICE A OTRO TSPACE
ALTER INDEX CLIENTE.SYS_C003006 REBUILD TABLESPACE nomts COMPUTE STATISTICS;
VERIFICAR INDICES DE UN USUARIO
SELECT INDEX_NAME, OWNER,STATUS, TABLESPACE_NAME FROM DBA_INDEXES WHERE OWNER = 'CLIENTE';
RECONSTRUIR INDICES (REINDEXAR)
//lo correcto es tener un ts_d para las tablas y un ts_x para los indices
pero de no existir ambos irian al mismo. (LOS INDICES NO SE PUEDEN COMPRIMIR COMO LAS TABLAS SOLO SE RECONSTRUYEN)
ALTER INDEX ser_esc.hist_eval_docente_pk REBUILD TABLESPACE ser_escx COMPUTE STATISTICS;
SENTENCIA PARA RECONSTRUIR INDICES INUTILIZABLES
SELECT 'DEBES DE HACER UN ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE '||TABLESPACE_NAME ||'COMPUTE STATISTICS'|| FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
CREAR INDICE SIMPLE
//Cuando el campo no es unique
CREATE INDEX index_name ON table_name (column_name);
CREAR INDICE UNICO
//2 registros no pueden tener el mismo valor de indice (usarlo en campos que son unique).
CREATE UNIQUE INDEX index_name ON table_name (column_name);
//////////////////////////////// FRAGMENTACION ///////////////////////
1-SELECT count(1) FROM SYS.FET$;
2)VER LAS TABLAS QUE NECESITAN RECREARSE
select substr(owner,1,20) own, substr(table_name,1,30) tabla, chain_cnt from dba_tables
where owner not in ('SYS','SYSTEM','OUTLN') and chain_cnt>0;
-- Colapsar todos los tablespaces
select 'PROMPT Colapsando el tablespace '||tablespace_name||' alter tablespace '||tablespace_name||' coalesce;' from dba_tablespaces where tablespace_name !='TEMP';
-- ver la fragmentacion
select substr(owner, 1,20) own, substr(table_name,1,30) tabla, substr(tablespace_name,1,20) tbspace, chain_cnt from dba_tables where owner not in ('SYS','SYSTEM','OUTLN') and chain_cnt > 0;
-- desfragmentacion: mover a otro tablespace la tabla
alter table biblioteca.ejemplares move tablespace se_paso;
-- Regresar las tablas al tablespace original
alter table biblioteca.ejemplares move tablespace bibliotecad;
-- Verificar los indices que quedaron no utilizables:
select index_name, owner, tablespace_name from dba_indexes where status='UNUSABLE';
-- Reconstruir estos indices:
alter index ser_esc.HIST_EVAL_DOCENTE_PK rebuild tablespace SER_ESCx compute statistics;
-- scripts mover segmentos
Spool c:\mover.sql
select 'alter table '||owner||'.'||table_name||' move tablespace se_paso;'
From dba_tables where chain_cnt>0 and owner not in ('SYS','SYSTEM','OUTLN');
Spool off;
-- regresa segmentos
Spool c:\regresar.sql
select 'alter table '||owner||'.'||table_name||' move tablespace '||tablespace_name||';'
from dba_tables where chain_cnt>0 and owner not in ('SYS','SYSTEM','OUTLN');
Spool off;
Reconstruir Indices
select 'alter index '||owner||'.'||index_name|| ' rebuild tablespace example compute statistics;'
from dba_indexes where status='UNUSABLE';
-- Ejecutar el script:
@mover.sql
-- Y después :
@regresar.sql
Indices que no podemos usar:
select index_name, owner, tablespace_name from dba_indexes where status='UNUSABLE';
--------------- estos son unos comando para generar los scripts que mueven los TB de la BD---------------------------------------------------------------------------
/////////////////////////////////MOVER///////////////////////////////////////////////////
select 'alter table '||owner||'.'||table_name||' move tablespace se_paso;'
from dba_tables where chain_cnt>0 and owner not in ('SYS', 'SYSTEM', 'OUTLN');
//////////////////////////////////REGRESAR////////////////////////////////////////////////
select 'alter table '||owner||'.'||table_name||' move tablespace '||tablespace_name||';'
from dba_tables where chain_cnt>0 and owner not in ('SYS', 'SYSTEM', 'OUTLN');
/////////////////////////HACER UN SCRIP QUE RECONSTRUYA LOS INDICES//////////////////////
select 'alter index'||table_owner||'.'||index_name||'rebuild' from dba_indexes where status='UNUSABLE' and owner not in ('SYS','SYSTEM','OUTLN');
----------------- estos son las intrucciones parta mover las tablas de la bd y asi generar la fragmentacion de la misma -------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Primero verificamos si existe fragmentación:
select count(1) from sys.fet$;
COUNT(1)
----------
0
NOTA: Recordar que la fragmentación sólo existe en Oracle 8i
-----------------------------------------------------------------------------------------------------------
Para ver que tablas necesitamos recrear, usamos el CHAIN_CNT:
select substr(owner,1,20) own, substr(table_name,1,30) tabla, chain_cnt
from dba_tables where owner not in('SYS','SYSTEM','OUTLN') and chain_cnt>0;
OWN TABLA CHAIN_CNT
-------------------- ------------------------------ ----------
PM ONLINE_MEDIA 7
PM PRINT_MEDIA 2
SH PROMOTIONS 68
SH CUSTOMERS 2460
SH PRODUCTS 35
-----------------------------------------------------------------------------------------------------------
checamos indices que no podemos usar:
select index_name, owner, tablespace_name from dba_indexes where status='UNUSABLE';
-----------------------------------------------------------------------------------------------------------
Creamos un tb de paso para mover las tablas, y luego regresarlas a su tb original y reconstruir sus indices:
create tablespace de_paso datafile 'c:\oracle\oradata\itt2006\de_paso.dbf' size 100M;
-----------------------------------------------------------------------------------------------------------
--> Creamos el script para mover las tablas, y lo guardamos en un archivo para despues ejecutarlo:
select 'alter table '||owner||'.'||table_name||' move tablespace de_paso;'
from dba_tables where owner not in('SYS','SYSTEM','OUTLN') and chain_cnt>0;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACEDE_PASO;'
--------------------------------------------------------------------------------
alter table PM.PRINT_MEDIA move tablespace de_paso;
alter table SH.PROMOTIONS move tablespace de_paso;
alter table SH.CUSTOMERS move tablespace de_paso;
alter table SH.PRODUCTS move tablespace de_paso;
Nota: No ejecutar sino hasta después de crear el script para regresar las tablas
-------------------------------------------------------------------------------------------------------------
--> Después creamos el script para regresar las tablas:
select 'alter table '||owner||'.'||table_name||' move tablespace '||tablespace_name||';'
from dba_tables where owner not in('SYS','SYSTEM','OUTLN') and chain_cnt>0;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACE'||TABLESPACE_NAME||';'
--------------------------------------------------------------------------------
alter table PM.PRINT_MEDIA move tablespace EXAMPLE;
alter table SH.PROMOTIONS move tablespace EXAMPLE;
alter table SH.CUSTOMERS move tablespace EXAMPLE;
alter table SH.PRODUCTS move tablespace EXAMPLE;
---------------------------------------------------------------------------------------------------------------
--> Ejecutamos el script para mover las tablas:
@c:\mover.sql
alter table PM.PRINT_MEDIA move tablespace de_paso
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
NOTA: El error que aparece es porque pm.print.media no se puede mover porque contiene imagenes
----------------------------------------------------------------------------------------------------------------
--> Ejecutamos el script para regresar las tablas:
@c:\regresar.sql
alter table PM.PRINT_MEDIA move tablespace EXAMPLE
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
NOTA: El error que marca es porque esa tabla no la movimos y por lo tanto no la podemos regresar
-----------------------------------------------------------------------------------------------------------------
--> Por lo tanto la única tabla que nos queda para reconstruir es la de imagenes:
select substr(owner,1,20) own, substr(table_name,1,30) tabla, chain_cnt, tablespace_name
from dba_tables where owner not in('SYS','SYSTEM','OUTLN') and chain_cnt>0;
OWN TABLA CHAIN_CNT
-------------------- ------------------------------ ----------
TABLESPACE_NAME
------------------------------
PM PRINT_MEDIA 2
EXAMPLE
--> Esta consulta también puede ser así:
select substr(owner,1,20) own, substr(table_name,1,30) tabla, chain_cnt, substr(tablespace_name,1,20) tablespace
from dba_tables where owner not in('SYS','SYSTEM','OUTLN') and chain_cnt>0;
OWN TABLA CHAIN_CNT
-------------------- ------------------------------ ----------
TABLESPACE
--------------------
PM PRINT_MEDIA 2
EXAMPLE
--------------------------------------------------------------------------------------------------------------------
--> Ahora nos marca que no podemos usar los indices de las tablas que reconstruimos:
select index_name, owner, tablespace_name from dba_indexes where status='UNUSABLE';
INDEX_NAME OWNER
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
ONLINEMEDIA_PK PM
EXAMPLE
---------------------------------------------------------------------------------------------------------------------
--> Por lo tanto hacemos un script para reconstruir los indices:
select 'alter index '||owner||'.'||index_name|| ' rebuild tablespace example compute statistics;'
from dba_indexes where status='UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDTABLESPACEEXAMPLECOMPUTESTATISTICS
--------------------------------------------------------------------------------
alter index PM.ONLINEMEDIA_PK rebuild tablespace example compute statistics;
alter index SH.PROMO_PK rebuild tablespace example compute statistics;
alter index SH.CUSTOMERS_PK rebuild tablespace example compute statistics;
alter index SH.PRODUCTS_PK rebuild tablespace example compute statistics;
----------------------------------------------------------------------------------------------------------------------
--> Lo ejecutamos:
@c:\reconstruir_indices.sql
--> Y ya no tenemos ningún indice unusable:
select index_name, owner, tablespace_name from dba_indexes where status='UNUSABLE';
no rows selected
---------------------------------------------------------------------------------------------------------------------
y bueno por el momento de oracle es todo, espero que les sirva y ya saben cualquier duda pregunten sin temor y por favor no dejen de participar y complementar esta informacion que seguro le falta mucho. bye
4 comentarios:
Gracias .. me ha servido mucho
The duly answer
Bravo, this brilliant idea is necessary just by the way
He intención de publicar algo como esto en mi página web y me dio una idea. Saludos.
Publicar un comentario