Oracle Administración de Logs

SQL> show parameter audit_file_dest;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest string /u01/app/oracle/admin/BDDWSGRD /adump

SQL> show parameter background_dump_dest;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------background_dump_dest string /u01/app/oracle/admin/BDDWSGRD /bdump

SQL> show parameter core_dump_dest;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------core_dump_dest string /u01/app/oracle/admin/BDDWSGRD /cdump

SQL> show parameter user_dump_dest;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /u01/app/oracle/admin/BDDWSGRD /udump

Seguridad Oracle y Funcion Complejidad Clave

Revoke Unnecessary Privileges

REVOKE EXECUTE ON DBMS_RANDOM from public;
REVOKE CREATE DATABASE LINK FROM connect;
REVOKE EXECUTE ON utl_tcp FROM public;
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON utl_mail FROM public;
REVOKE EXECUTE ON utl_inaddr FROM public;
REVOKE EXECUTE ON utl_file FROM public;
REVOKE EXECUTE ON dbms_java FROm public;
REVOKE SYSDBA FROM rman;

FUNCION PARA VALIDAR COMPLEJIDAD DE CONTRASEÑAS.

CREATE OR REPLACE FUNCTION dba_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ischarm boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(54);


BEGIN
digitarray:= '0123456789';
chararray := 'abcdefghijklmnñopqrstuvwxyzABCDEFGHIJKLMNÑOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Valida que el Password no sea Igual al Username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'La clave es la misma o similar al usuario');
END IF;

-- Valida los caraterese Minimos de Longitud del Password

IF length(password) < isdigit =" FALSE">>

ischar:=FALSE;

FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;

IF ischar = FALSE THEN
raise_application_error(-20003, 'El password debe contener un caracter');
END IF;


-- 3. Check for the punctuation

<>

ispunct:=FALSE;

FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;

IF ispunct = FALSE THEN
raise_application_error(-20003, 'La clave debe contener al menos caracter especial');
END IF;

<>

-- Compruebe si la contraseña es diferente de la anterior contraseña por lo menos en Tres caracteres

-- 4 letters

IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'El password debe ser diferente al menos en (3) Tres caracteres');
END IF;
END IF;
END IF;

-- Everything is fine; return TRUE ;

RETURN(TRUE);

END;
/

-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE SEGURIDAD LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION dba_verify_function;

SQL> DECLARE
2 X BOOLEAN;
3 BEGIN
4 x:= dba_verify_function ('dbo','dios','pepe');
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-20002: La clave debe ser de longitud mayor o igual a 8
ORA-06512: at "SYS.FN_COMPLEJIDAD_CLAVE", line 26
ORA-06512: at line 4

Seguridad Oracle 10G Usuarios con el Password igual que el Username

Script para determinar usuarios con el password igual al username

PARA IMPLEMENTAR EN POWER

create or replace procedure dba_valida_usuario_password as
-- Buscar Usuarios que tienen el Password Igual que el username
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16;
begin
execute immediate 'truncate table dbo.aud_val_usuario_password';
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user 'un' identified by 'un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
insert into dbo.aud_val_usuario_password values (un);
else
execute immediate 'alter user 'un' identified by values '''hexpw'''';
end if;
commit;
end loop;
end;
/

SQL> CREATE TABLE AUD_VAL_USUARIO_PASSWORD (USERNAME VARCHAR2(20));/


SQL> set serveroutput on
SQL> exec dba_valida_usuario_password;

OUTLN
DBSNMP
CTXSYS
MDSYS
TEST

PL/SQL procedure successfully completed.

Oracle Mode Shutdown

Shutdown

Shutdown Mode Normal

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: Si
Espera a que la transacción termine: Si
Forza Checkpoint y cierra archivos: Si


Shutdown Mode Transactional

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: Si
Forza Checkpoint y cierra archivos: Si

Shutdown Mode Inmediate

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: No
Forza Checkpoint y cierra archivos: Si


Shutdown Mode Abort

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: No
Forza Checkpoint y cierra archivos: No

Oracle Flashback Query

FLASHBACK CONFIGURACION ANTES.

connect hr/hr

SQL> select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10));

insert into job_history (select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10)));


EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90

10 rows selected.


CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;
CREATE TABLE dept
(deptno NUMBER,
deptname VARCHAR2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;
At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;
Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.INSERT INTO emp VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns.connect dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855 I Tom 927
000200030000002D 113564 D Mike 555
000200030000002E 112670 113564 I Mike 555
3 rows selected
The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction:SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER,
undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected

Oracle Papelera Reciclaje

PAPELERA RECICLAJE ORACLE

SELECT * FROM RECYCLEBIN;

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

VISTAS

USER_RECYCLEBIN
This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN
This view gives administrators visibility to all dropped objects in the recycle bin

PURGE TABLE nombre_tabla;
Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.

PURGE INDEX nombre_índice;
Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.

PURGE RECYCLEBIN;
Elimina todos los objetos (del usuario que lanza la orden) de la papelera.

PURGE DBA_RECYCLEBIN;
Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.

PURGE TABLESPACE nombre_tablespace;
Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.

PURGE TABLESPACE nombre_tablespace USERS nombre_usuario;
Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.

SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49

SQL> flashback table pendientes to before drop;
Flashback complete.
SQL>


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

Tmabien se puede realizar un query con el nombre de la tabla de esta forma.

example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Limpia toda la papelera reciclaje

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;

Oracle Flashback Database

LABORATORIO DE FLASHBACK DATABASE

SQL>select * from v$flash_recovery_area_usage;

VER EL ESPACIO ESTIMADO DE OCUPACION DEL FLASHBACK

SQL> select estimated_flashback_size, flashback_size from v$flashback_database_log;


DETERMINAR PRIMER FLASHBACK PUNTO EN EL PASADO

SQL> SELECT OLDEST_FLASHBACK_SCN, to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS'),"OLDEST_FLASHBACK_TIME" FROM V$FLASHBACK_DATABASE_LOG;

SQL> create table prueba as (select * from per_sexo);

Tabla creada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853235 YES


SQL> update prueba set estado = 50
2 ;

2 filas actualizadas.

SQL> commit;

Confirmacion terminada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853288 YES


SQL> connect sys/sys as sysdba
Conectado.
SQL> flashback database to scn 853235;
flashback database to scn 853235
*
ERROR en linea 1:
ORA-38757: La base de datos debe estar montada y no estar abierta para FLASHBACK.


SQL> shutdown immediate;
Base de datos cerrada.

Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 104857600 bytes
Fixed Size 1217932 bytes
Variable Size 71305844 bytes
Database Buffers 25165824 bytes
Redo Buffers 7168000 bytes
Base de datos montada.
SQL> flashback database to scn 853235;

Flashback terminado.

SQL> alter database open resetlogs;

EN CASO DE QUE CUANDO ME RECUPERE NO SEA EL PUNTO EXACTO

shutdown immediate;
startup mount;
flashback database to before resetlogs;

FLASHBACK DATABASE FECHA;

flashback database TO TIMESTAMP TO_TIMESTAMP('2020-04-29 19:44:00','YYYY-MM-DD HH24:MI:SS');

Oracle UTL_MAIL

ENVIAR CORREO UTL_MAIL

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.The package is loaded by running the following scripts:

CONN sys/password AS SYSDBA @$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb
SQL> grant execute on utl_mail to dbo;
In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server:
CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
ALTER SYSTEM SET smtp_out_server='WBOGEX01.telecom.esp:25' SCOPE=SPFILE;

SHUTDOWN IMMEDIATE STARTUP With the configuration complete we can now send a mail using:

BEGIN UTL_MAIL.send(sender => 'me@domain.com', recipients => 'person1@domain.com,person2@domain.com', cc => 'person3@domain.com', bcc => 'myboss@domain.com', subject => 'UTL_MAIL Test', message => 'If you get this message it worked!'); END; /

Oracle Seguridad Script Determinar Usuario y Password Igual

Script para determinar usuarios con el password igual al username

create or replace procedure sys.find_joes as
-- Find users that have their password equal to their username
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16; -- only consider db authenticated
begin
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user 'un' identified by 'un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
else
-- change password back to what it was
execute immediate
'alter user 'un' identified by values '''hexpw'''';
end if;
end loop;
end;
/


Sample Output
SQL> set serveroutput on
SQL> exec sys.find_joes;

OUTLN
DBSNMP
CTXSYS
MDSYS
TEST

PL/SQL procedure successfully completed.

Oracle Restore Point Laboratorio

select * from v$flash_recovery_area_usage;

SQL>gt; flashback table aud_sesiones to before drop;

DETERMINAR PRIMER FLASHBACK

SELECT OLDEST_FLASHBACK_SCN, to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS'),"OLDEST_FLASHBACK_TIME" FROM V$FLASHBACK_DATABASE_LOG;


LABORATORIO DE RESTORE POINT

RMAM> list restore point all;


SQL>CREATE RESTORE POINT good_data;

Punto de restauracion creado.

SQL> select descripcion from per_sexo where codigo_sexo=1;

DESCRIPCION
----------------------------------------
MASCULINO

SQL> update per_sexo set descripcion='OTRO' where codigo_sexo=1;

1 fila actualizada.

SQL> select descripcion from per_sexo where codigo_sexo=1;

DESCRIPCION
----------------------------------------
OTRO

SQL> commit;

Confirmacion terminada.

SQL> FLASHBACK TABLE PER_SEXO TO RESTORE POINT GOOD_DATA;
FLASHBACK TABLE PER_SEXO TO RESTORE POINT GOOD_DATA
*
ERROR en linea 1:
ORA-08189: no se puede realizar flashback en la tabla porque el movimiento de filas no esta activado


SQL> alter table per_sexo enable row movement;

Tabla modificada.

SQL> flashback table per_sexo to restore point good_data;

Flashback terminado.

SQL> select descripcion from per_sexo where codigo_sexo=1;

DESCRIPCION
----------------------------------------
MASCULINO

PARA VER PUNTOS DE RESTAURACION CREADOS EN LA INSTANCIA

SQL> select name, storage_size from v$restore_point;

BORRAR PUNTO DE RESTAURACION

drop restore point good_data;

Auditoria Oracle

PRUEBAS DE AUDITORIA DBMS_FGA


Hola a Todos

Si quieren una solución de software de Auditoría muy completa favor visitar este sitio estos locos son expertos en el tema tienen un producto excelente y muy buenos precios

www.oraconsultors.com



SQL> ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;

AUDIT_SYS_OPERATIONS = TRUE

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE =SPFILE;

SELECT * FROM ALL_AUDIT_POLICIES;
SELECT POLICY_NAME FROM ALL_AUDIT_POLICIES;

SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,POLICY_COLUMN,SEL,INS,UPD,DEL FROM ALL_AUDIT_POLICIES


Sistema modificado.

-- Clear down the audit trail.
CONN sys/password@db10g AS SYSDBA

ALTER TABLE AUD$ MOVE TABLESPACE USERS;

TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL columnof the EMP table.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SAL_AUDIT',
audit_condition => NULL, -- Equivalent to TRUE
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
CONN scott/tiger@db10g
SELECT * FROM emp;
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
UPDATE emp SET sal = 10 WHERE empno = 9999;
DELETE emp WHERE empno = 9999;
ROLLBACK;

-- Check the audit trail.
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1)
UPDATE emp SET sal = 10 WHERE empno = 9999
DELETE emp WHERE empno = 9999

4 rows selected.

-- Drop the policy.
CONN sys/password@db10g AS SYSDBA
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SAL_AUDIT');
END;
/

-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;

AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;

DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

EJEMPLO PROBADO AUDITORIA

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;


CONNECT sys/password AS SYSDBA

AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
CONN audit_test/password

CREATE TABLE test_tab (
id NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
· DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
· DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.


COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE

OLUMN db_user FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT db_user,
extended_timestamp,
object_schema,
object_name,
action
FROM v$xml_audit_trail
WHERE object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;

DB_USER EXTENDED_TIMESTAMP OBJECT_SCH OBJECT_NAM ACTION
---------- ----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00 AUDIT_TEST TEST_TAB 1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00 AUDIT_TEST TEST_TAB 2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00 AUDIT_TEST TEST_TAB 6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00 AUDIT_TEST TEST_TAB 3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00 AUDIT_TEST TEST_TAB 7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00 AUDIT_TEST TEST_TAB 12

6 rows selected.

SQL>NOAUDIT ALL BY DBO;
SQL>NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY DBO;

MOVER AUDITORIA AUD$

ALTER TABLE AUD$ MOVE TABLESPACE USERS;

ALTER TABLE AUD$ MOVE TABLESPACE AUDITORIA;



drop table system.aud$_bu;

create table system.aud$_bu
tablespace tools
as (select * from sys.aud$ where 1=2);

drop table system.aud$_prev_quarter;

create table system.aud$_prev_quarter
tablespace tools
as (select * from sys.aud$ where 1=2);

CHEQUEAR TAMAÑO AUD$SIZE

Create procedure
keep_size_in_check
is
rowCount number;
begin
select count(*) into rowCount from sys.aud$ ;
if rowCount > 50000
then
commit;
set transaction use rollback segment rbs_bulk;
insert into system.aud$_bu (select * from sys.aud$);
delete from sys.aud$ ;
commit;
sys.dbms_system.ksdwrt (3,'ORA-AUDIT TRAIL: POSSIBLE DOS
ATTACK!!!! AUD$ rows moved to AUD$_BU');
end if;
end keep_size_in_check;

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CHECK_SIZE_AUD$',
job_type => 'STORED_PROCEDURE',
job_action => ' keep_size_in_check',
start_date => SYSDATE,
repeat_interval => 'FREQ = WEEKLY');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE('CHECK_SIZE_AUD$');
END;
/

BEGIN
DBMS_SCHEDULER.RUN_JOB('CHECK_SIZE_AUD$',FALSE);
END;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB('CHECK_SIZE_AUD$',FALSE);
END;
/



procedure quarterly_rotation
is

/* Procedure quarterly_rotation
* Cleans out table system.aud$_prev_quarter
* and repopulates it with the previous quarter's worth of audit info
* Should be scheduled to run every quarter
*/

begin
commit;
set transaction use rollback segment rbs_bulk;
delete from system.aud$_prev_quarter;
insert into system.aud$_prev_quarter
( select * from sys.aud$ au
where au.timestamp# < what =""> ‘manage_aud$.keep_size_in_check;’,
interval => ‘trunc(sysdate)+1/96’);
End;



Examples
Stop Auditing of SQL Statements Related to Roles: Example If you have chosen auditing for every SQL statement that creates or drops a role, then you can stop auditing of such statements by issuing the following statement:
NOAUDIT ROLE;
Stop Auditing of Updates or Queries on Objects Owned by a Particular User: Example If you have chosen auditing for any statement that queries or updates any table issued by the users hr and oe, then you can stop auditing for queries by hr by issuing the following statement:
NOAUDIT SELECT TABLE BY hr;

The preceding statement stops auditing only queries by hr, so the database continues to audit queries and updates by oe as well as updates by hr.
Stop Auditing of Statements Authorized by a Particular Object Privilege: Example To stop auditing on all statements that are authorized by DELETE ANY TABLE system privilege, issue the following statement:
NOAUDIT DELETE ANY TABLE;
Stop Auditing of Queries on a Particular Object: Example If you have chosen auditing for every SQL statement that queries the employees table in the schema hr, then you can stop auditing for such queries by issuing the following statement:
NOAUDIT SELECT
ON hr.employees;
Stop Auditing of Queries that Complete Successfully: Example You can stop auditing for queries that complete successfully by issuing the following statement:
NOAUDIT SELECT
ON hr.employees
WHENEVER SUCCESSFUL;

VER TODAS LAS POLITICAS
SELECT * FROM ALL_AUDIT_POLICIES;

SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,POLICY_COLUMN,SEL,INS,UPD,DEL FROM ALL_AUDIT_POLICIES


execute dbms_fga.drop_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO');
dbms_fga.enable_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO',TRUE);
dbms_fga.disable_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO');

PERFORMANCE QUERYS

SQL> EXPLAIN PLAN FOR SELECT * FROM PER_PERSONA WHERE ESTADO=1;
SQL> SELECT * FROM PLAN_TABLE

OTRA FORMA DE HACERLO ES

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM PER_PERSONA;

SALE RESULTADO Y PLAN DE EJECUCION

SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM PER_PERSONA;

PERFORMANCE
SQL> SHOW PARAMETER CURSOR;
PARAMETR CURSOR_SHARING

SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 16M

SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS


per_persona_tuning_task



EJEMPLO DE VIRTUAL PRIVATE DATABASE


l Virtual Private Database (VPD) with Oracle
_uacct = "UA-221075-1";urchinTracker();
Virtual Private Database is also known as fine graind access control (FGAC). It allows to define which rows users may have access to.
� A simple example
In this example, it is assumed that a company consists of different departments (with each having an entry in the departments table). An employee belongs to exactly on department. A department can have secrets that go into the department_secrets table.
create table department (
dep_id int primary key,
name varchar2(30)
);

create table employee (
dep_id references department,
name varchar2(30)
);

create table department_secrets (
dep_id references department,
secret varchar2(30)
);
Filling in some truly confidential secrets:
insert into department values (1, 'Research and Development');
insert into department values (2, 'Sales' );
insert into department values (3, 'Human Resources' );

insert into employee values (2, 'Peter');
insert into employee values (3, 'Julia');
insert into employee values (3, 'Sandy');
insert into employee values (1, 'Frank');
insert into employee values (2, 'Eric' );
insert into employee values (1, 'Joel' );

insert into department_secrets values (1, 'R+D Secret #1' );
insert into department_secrets values (1, 'R+D Secret #2' );
insert into department_secrets values (2, 'Sales Secret #1');
insert into department_secrets values (2, 'Sales Secret #2');
insert into department_secrets values (3, 'HR Secret #1' );
insert into department_secrets values (3, 'HR Secret #2' );
For any employee, it must be possible to see all secrets of his department, but no secret of another department.
In order to make that happen with Oracle, we need to create a package, a trigger, and set a policy.
First, the package is created.
create or replace package pck_vpd
as
p_dep_id department.dep_id%type;

procedure set_dep_id(v_dep_id department.dep_id%type);

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/

create or replace package body pck_vpd as

procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;


function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return 'dep_id = ' p_dep_id;
end predicate;

end pck_vpd;
/
Then the trigger is defined. This trigger fires whenever someone log on to the database. It finds the user's departement id (dep_id) and calls set_dep_id in the package.
create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from employee where upper(name) = user;

pck_vpd.set_dep_id(v_dep_id);
end;
/
Finally, the policy is defined. The policy states which procedure is used to add a where clause part to the where clause if someone executes a select statement.
begin
dbms_rls.add_policy (
user,
'department_secrets',
'choosable policy name',
user,
'pck_vpd.predicate',
'select,update,delete');
end;
/
To test the setup, some users are created.
create user frank identified by frank default tablespace users temporary tablespace temp;
create user peter identified by peter default tablespace users temporary tablespace temp;
create user julia identified by julia default tablespace users temporary tablespace temp;
The necessary privileges are granted.
grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;

grant create session to frank;
grant create session to peter;
grant create session to julia;
A public synonym is created.
create public synonym department_secrets for department_secrets;
Frank (belonging to R+D) executes a query....
connect frank/frank;

select * from department_secrets;
DEP_ID SECRET
---------- ------------------------------
1 R+D Secret #1
1 R+D Secret #2
Peter (belonging to Sales) executes a query....
connect peter/peter;

select * from department_secrets;
DEP_ID SECRET
---------- ------------------------------
2 Sales Secret #1
2 Sales Secret #2

script ORACLE

SQL> connect dbo/power
Conectado.
SQL>
SQL> create table department (
dep_id int primary key,
name varchar2(30)
); 2 3 4

Tabla creada.

SQL> create table employee (
dep_id references department,
name varchar2(30)
); 2 3 4

Tabla creada.

SQL> create table department_secrets (
dep_id references department,
secret varchar2(30)
); 2 3 4

Tabla creada.

SQL> insert into department values (1, 'Research and Development');
insert into department values (2, 'Sales' );
insert into department values (3, 'Human Resources' );
1 fila creada.

SQL>
1 fila creada.

SQL> rollback;
insert into department values (3, 'Human Resources' );rollback
*
ERROR en linea 1:
ORA-00911: caracter no valido


SQL> rollback;

Rollback terminado.

SQL> insert into department values (1, 'Research and Development');

1 fila creada.

SQL> insert into department values (2, 'Sales' );

1 fila creada.

SQL> insert into department values (3, 'Human Resources' );

1 fila creada.

SQL> insert into employee values (2, 'Peter');

1 fila creada.

SQL> insert into employee values (3, 'Julia');

1 fila creada.

SQL> insert into employee values (3, 'Sandy');

1 fila creada.

SQL> insert into employee values (1, 'Frank');

1 fila creada.

SQL> insert into employee values (2, 'Eric' );

1 fila creada.

SQL> insert into employee values (1, 'Joel' );

1 fila creada.

SQL> insert into department_secrets values (1, 'R+D Secret #1' );

1 fila creada.

SQL> insert into department_secrets values (1, 'R+D Secret #2' );

1 fila creada.

SQL> insert into department_secrets values (2, 'Sales Secret #1');

1 fila creada.

SQL> insert into department_secrets values (2, 'Sales Secret #2');

1 fila creada.

SQL> insert into department_secrets values (3, 'HR Secret #1' );

1 fila creada.

SQL> insert into department_secrets values (3, 'HR Secret #2' );

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> create or replace package pck_vpd
as
p_dep_id department.dep_id%type;

procedure set_dep_id(v_dep_id department.dep_id%type);

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/ 2 3 4 5 6 7 8 9

Paquete creado.

SQL> create or replace package body pck_vpd as

procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;


function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return 'dep_id = ' p_dep_id;
end predicate;

end pck_vpd;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Cuerpo del paquete creado.

SQL> create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from employee where upper(name) = user;

pck_vpd.set_dep_id(v_dep_id);
end;
/ 2 3 4 5 6 7 8 9 10 11

Disparador creado.

SQL> begin
dbms_rls.add_policy (
user,
'department_secrets',
'choosable policy name',
user,
'pck_vpd.predicate',
'select,update,delete');
end;
/

2 3 4 5 6 7 8 9 10
Procedimiento PL/SQL terminado correctamente.

SQL> SQL> create user frank identified by frank default tablespace users temporary tablespace temp;

Usuario creado.

SQL> create user peter identified by peter default tablespace users temporary tablespace temp;

Usuario creado.

SQL> create user julia identified by julia default tablespace users temporary tablespace temp;

Usuario creado.

SQL> grant all on department_secrets to frank;

Concesion terminada correctamente.

SQL> grant all on department_secrets to peter;

Concesion terminada correctamente.

SQL> grant all on department_secrets to julia;

Concesion terminada correctamente.

SQL> grant create session to frank;

Concesion terminada correctamente.

SQL> grant create session to peter;

Concesion terminada correctamente.

SQL> grant create session to julia;

Concesion terminada correctamente.

SQL> create public synonym department_secrets for department_secrets;

Sinonimo creado.

SQL> connect frank/frank;
Conectado.
SQL> select * from department_secrets;

DEP_ID SECRET
---------- ------------------------------
1 R+D Secret #1
1 R+D Secret #2

SQL> connect peter/peter;
Conectado.
SQL> select * from department_secrets;

DEP_ID SECRET
---------- ------------------------------
2 Sales Secret #1
2 Sales Secret #2

SQL> EXECUTE DBMS_RLS.DROP_POLICY('DBO','department_secrets','choosable policy name');

Oracle Database 10g Tareas Administrador

$ORACLE_HOME/install/portlist.ini

Habilitar Consola Enterprise Manager

$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

Habilitar ISQLPLUS

$ isqlplusctl start
$ isqlplusctl stop

http://maquina:1158/em
http://maquina:5560/isqlplus
http://maquina:5560/isqlplus/dba

Archivos configuración Oracle

listener.ora
sqlnet.ora
tnsnames.ora

Oracle Wall Manager
$ own

Database Configurant Assistant
$dbca

Database upgrade Assitant
$dbua


GENERADOR DE REPORTES

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
@$ORACLE_HOME/rdbms/admin/addmrpt.sql


CREATE BASELINE

SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
-- Using procedures.
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2490,
end_snap_id => 2491,
baseline_name => 'test1_bl',
expiration => 60);

DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 17:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 18:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test2_bl',
expiration => NULL);

-- Using functions.
l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2492,
end_snap_id => 2493,
baseline_name => 'test3_bl',
expiration => 30);
DBMS_OUTPUT.put_line('Return: ' || l_return);

l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 19:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 20:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test4_bl',
expiration => NULL);

DBMS_OUTPUT.put_line('Return: ' || l_return);
END;
/

Return: 8
Return: 9

PL/SQL procedure successfully completed.

SQL>

COLUMN baseline_name FORMAT A15

SELECT baseline_id, baseline_name, START_SNAP_ID,
TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,
END_SNAP_ID,
TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM dba_hist_baseline
WHERE baseline_type = 'STATIC'
ORDER BY baseline_id;

BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
----------- --------------- ------------- ----------------- ----------- -----------------
6 test1_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
7 test2_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
8 test3_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00
9 test4_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00

4 rows selected.

SQL>

SELECT *
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));

SELECT *
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));

BEGIN
DBMS_WORKLOAD_REPOSITORY.rename_baseline(
old_baseline_name => 'test4_bl',
new_baseline_name => 'test5_bl');
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test1_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test2_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test3_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test5_bl');
END;
/

The Moving Window Baseline
Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period. The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.

The current AWR retention period can be displayed by querying the RETENTION column of the DBA_HIST_WR_CONTROL view.

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

1 row selected.

SQL>The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200); -- Minutes (= 30 Days).
END;
/

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

1 row selected.

SQL>The current moving window size is displayed by querying the DBA_HIST_BASELINE view.

SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
8

1 row selected.

SQL>The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
window_size => 20);
END;
/

SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
20

1 row selected.

SQL>Oracle recommend of window size greater than or equal to 30 days when using adaptive thresholds.

To adjust the retention periods in Enterprise Manager, click on the "Edit" button in the "Automatic Workload Repository" screen (Server > Automatic Workload Repository).


Baseline Templates
Baseline templates allow you to define baselines you would like to capture in the future. Overloads of the CREATE_BASELINE_TEMPLATE procedure define the capture of individual baselines, or repeating baselines. Creating a single baseline template is similar to creating a time-based baseline, except the time is in the future.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
start_time => TO_DATE('01-DEC-2008 00:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('01-DEC-2008 05:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => '01_dec_008_00_05_bl',
template_name => '01_dec_008_00_05_tp',
expiration => 100);
END;
/Templates for repeating baselines are a little different as they require some basic scheduling information. The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline. Since the template will generate multiple baselines, the baseline name is derived from the BASELINE_NAME_PREFIX concatenated to the date. The following example creates a template that will run for the next six months, gathering a baseline every Monday between 00:00 and 05:00.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
day_of_week => 'MONDAY',
hour_in_day => 0,
duration => 5,
start_time => SYSDATE,
end_time => ADD_MONTHS(SYSDATE, 6),
baseline_name_prefix => 'monday_morning_bl_',
template_name => 'monday_morning_tp',
expiration => NULL);
END;
/Information about baseline templates is displayed using the DBA_HIST_BASELINE_TEMPLATE view.

SELECT template_name,
template_type,
baseline_name_prefix,
start_time,
end_time,
day_of_week,
hour_in_day,
duration,
expiration
FROM dba_hist_baseline_template;

TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME
------------------------------ --------- ------------------------------ --------------------
END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION
-------------------- --------- ----------- ---------- ----------
01_dec_008_00_05_tp SINGLE 01_dec_008_00_05_bl 01-DEC-2008 00:00:00
01-DEC-2008 05:00:00 100

monday_morning_tp REPEATING monday_morning_bl_ 11-JUL-2008 14:43:36
11-JAN-2009 14:43:36 MONDAY 0 5


2 rows selected.

SQL>Notice the BASELINE_NAME_PREFIX column holds either the prefix or full baseline name depending on the type of baseline being captured.

Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => '01_dec_008_00_05_tp');
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => 'monday_morning_tp');
END;
/



Consola Enterprise Manager

$oemapp paramete

Ej oemapp dbastudio

TABLAS VISTAS DE LA BASE DE DATOS

USER
ALL
DBA


SQL>select * from database_properties
SQL>select * from global_name
SQL>select * from v$database
SQL>select * from product_component_version

$ sqlplus '/as sysdba'

ARRANQUE AUTOMATICO DE LA BASE DE DATOS

BASE DE DATOS MONTAR

Solo Administradores

$ starup restrict
$ starup nomount o Alter database mount
$ startup mount Alter database open read only
$ startup force
$ startup open
$ startup pfile=init.ora


COMANDOS SQLPLUS

SQL> DEFINE _EDITOR=vi

BAJAR BASE DE DATOS

$ shutdown abort
$ shutdown Transactional
$ shutdown immediate
$ shutdown normal

Privilegios para entrar a la base de datos en modo restrict

grant restrict session to dbo;

SQL> alter system enable restricted session;

System altered.

SQL> alter system disable restricted session;

System altered.


SQL> ALTER USER dbo ACCOUNT LOCK;
SQL> ALTER USER dbo ACCOUNT UNLOCK;

Ver status Listener

$lsnrctl status

LSNRCTL>statust
LSNRCTL>start

Listener el port defaul 1521

Mostar todas las vistas

v$fixed_table

PING AL SERVICIO

tnsping ORCL

PARAMETROS SGA

SQL> show parameter sga_max_size
SQL> show parameter sga_target

CAMBIAR PARAMETROS MEMORIA INSTANCIA DE LA BASE DE DATOS

SQL> alter system set sga_target= value scope= {spfilememoryboth}
SQL> alter system set sga_max_size=1500M scope=spfile;
SQL> alter system set sga_target=1500M scope=spfile;
SQL> alter system set sga_target 992M scope=both;

SQL> alter system set shared_pool_size=0;


SQL> alter system set large_pool_size=0;
SQL> alter system set java_pool_size=0;
SQL> alter system set db_cache_size=0;
SQL> alter system set streams_pool_size=0;

VER SESIONES

SQL> select username,sid,serial# from v$session
SQL> alter system kill session '7,15';

ACTIVAR MODE ARCHIVE LOG

SQL> sqlplus '/as sysdba'

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Deshabilitado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 8
Siguiente secuencia de log para archivar 10
Secuencia de log actual 10


SQL> select log_mode from v$database;

SQL> select * from v$logfile;

SQL> select * from v$controlfile;

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE

SQL> show parameter db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G


COLOCAR INSTANCIA EN MODE ARCHIVELOG


SQL> show parameter log_archive_format;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf


SQL> shutdown immediate
SQL> startup mount
SQL> alter system set log_archive_dest_n=' ' scope = spfile;
SQL> alter database archivelog;

SQL> show parameter log_archive_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string

ACTIVAR FLASHBACK INSTANCIA

SQL> alter database flashback on;
SQL> alter database archivelog;
SQL> alter system set log_archive_start = true scope=spfile;

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 8
Siguiente secuencia de log para archivar 10
Secuencia de log actual 10

SQL> alter system switch logfile;
SQL> alter system archive log stop;
SQL> alter system checkpoint;
SQL> alter system archive log current;
SQL> select log_mode from v$database;

SQL> show parameter log_archive_max;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 2

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
104857600

VISTAS SGA MEMORIA

V$SGA, V$SGAINFO, V$SGASTAT, V$SGA_DYNAMIC_COMPONENT, V$SGA_DYNAMIC_FREE_MEMORY, V$SGA_RESIZE_OPS, V$SGA_CURRENT_RESIZE_OPS, V$SGA_TARGET_ADVICE

VISTAS ARCHIVED

V$ARCHIVED_LOG, V$ARCHIVED_DEST, V$ARCHIVE_PROCESSES,
V$BACKUP_REDOLOG, V$LOG, V$LOG_HISTORY


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10 G ;

SQL> show parameter db_flashback;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET= 2000;
Sistema modificado.

SQL> show parameter db_block_check;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
db_block_checksum string TRUE PARAMETRO QUE VERIFICA LOS BLOQUES EN LOS RED LOGS

CREACION DE CONTROLFILES

Creating a Controlfile: Example
statement, database demo was created with the WE8DEC character set. The example
uses the word path where you would normally insert the path on your system to the
appropriate Oracle Database directories.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 449
LOGFILE
GROUP 1 '/path/oracle/dbs/t_log1.f' SIZE 500K,
GROUP 2 '/path/oracle/dbs/t_log2.f' SIZE 500K
# STANDBY LOGFILE
DATAFILE
'/path/oracle/dbs/t_db1.f',
'/path/oracle/dbs/dbu19i.dbf',
'/path/oracle/dbs/tbs_11.f',
'/path/oracle/dbs/smundo.dbf',
'/path/oracle/dbs/demo.dbf'
CHARACTER SET WE8DEC
;





CREACION DE DATABASE


The following statement creates a database and fully
Creating a Database: Example
specifies each argument:

CREATE DATABASE sample
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE
'disk1:df1.dbf' AUTOEXTEND ON,
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_ts
UNDO TABLESPACE undo_ts
SET TIME_ZONE = '+02:00';


CREACION DATABASE LINK


CREATE PUBLIC DATABASE LINK remote
USING 'remote';
This database link allows user hr on the local database to update a table on the
remote database (assuming hr has appropriate privileges):
UPDATE employees@remote
SET salary=salary*1.1
WHERE last_name = 'Baer';




CREACION DIMENSION


This statement was used to create the
Creating a Dimension: Examples
customers_dim dimension in the sample schema sh:
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region IS (countries.country_region)
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
subregion CHILD OF
region
JOIN KEY (customers.country_id) REFERENCES country
)
ATTRIBUTE customer DETERMINES
(cust_first_name, cust_last_name, cust_gender,
cust_marital_status, cust_year_of_birth,
cust_income_level, cust_credit_limit)
ATTRIBUTE country DETERMINES (countries.country_name) ;


CREACION DIRECTORIO

CREATE DIRECTORY admin AS 'oracle/admin';

CREACION DE INDICES FUNCIONES

CREATE INDEX ord_customer_ix_demo
ON orders (order_mode)
NOSORT
NOLOGGING;

CREATE INDEX income_ix
ON employees(salary + (salary*commission_pct));

SELECT first_name' 'last_name "Name"
FROM employees
WHERE (salary*commission_pct) + salary > 15000;

CREATE INDEX idx_personnel ON CLUSTER personnel;

CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

CREATE INDEX prod_idx ON hash_products(product_id) LOCAL
STORE IN (tbs_01, tbs_02);

CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
LOCAL
(PARTITION q1_1998,
PARTITION q2_1998,
PARTITION q3_1998,
PARTITION q4_1998,
PARTITION q1_1999,
PARTITION q2_1999,
PARTITION q3_1999,
PARTITION q4_1999,
PARTITION q1_2000,
PARTITION q2_2000
(SUBPARTITION pq2001, SUBPARTITION pq2002,
SUBPARTITION pq2003, SUBPARTITION pq2004,
SUBPARTITION pq2005, SUBPARTITION pq2006,
SUBPARTITION pq2007, SUBPARTITION pq2008),
PARTITION q3_2000
(SUBPARTITION c1 TABLESPACE tbs_02,
SUBPARTITION c2 TABLESPACE tbs_02,
SUBPARTITION c3 TABLESPACE tbs_02,
SUBPARTITION c4 TABLESPACE tbs_02,
SUBPARTITION c5 TABLESPACE tbs_02),
PARTITION q4_2000
(SUBPARTITION pq4001 TABLESPACE tbs_03,
SUBPARTITION pq4002 TABLESPACE tbs_03,
SUBPARTITION pq4003 TABLESPACE tbs_03,
SUBPARTITION pq4004 TABLESPACE tbs_03)
);


CREATE BITMAP INDEX product_bm_ix
ON hash_products(list_price)
TABLESPACE tbs_1
LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,
PARTITION ix_p2,
PARTITION ix_p3 TABLESPACE tbs_03,
PARTITION ix_p4,
PARTITION ix_p5 TABLESPACE tbs_04 );

CREATE UNIQUE INDEX nested_tab_ix
ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);


CREACION LIBRERIA

CREATE LIBRARY ext_lib AS '/OR/lib/ext_lib.so';
/

CREACION VISTAS MATERIALIZADAS


CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;





Creating Materialized Join Views: Example The following statement creates and
populates the materialized aggregate view sales_by_month_by_state using tables
in the sample sh schema. The materialized view will be populated with data as soon
as the statement executes successfully. By default, subsequent refreshes will be
accomplished by reexecuting the defining query of the materialized view:


CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE example
PARALLEL 4
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc, c.cust_state_province,
SUM(s.amount_sold) AS sum_sales
FROM times t, sales s, customers c
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;


CREATE PROFILE


CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 30;

CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;


CREACION DE TRIGGER SHEMA

CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON DBO.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
CREATE OR REPLACE TRIGGER drop_trigger_prueba
BEFORE DELETE ON PRUEBA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot delete object');
END;
/


MANEJO DE LOGFILES

SQL> ALTER DATABASE ADD LOGFILE
('/DISK3/log3a.rdo', '/DISK4/log3b.rdo') SIZE 100M

SQL> ALTER DATABASE ADD LOGFILE GROUP 4
('/DISK3/log1.log') SIZE 100M

SQL> ALTER DATABASE ADD LOGFILE MEMBER
'/DISK4/log1b.rdo' TO GROUP 1;
'/DISK4/log2b.rdo' TO GROUP 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/DISK4/log2b.rdo';

SQL> ALTER DATABASE CLEAR LOGFILE '/DISK3/log2a.rdo';



SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER DATABASE DATAFILE '/u01/example01.dbf' OFFLINE;

SQL> ALTER DATABASE RENAFILE '/u01/example01.dbf' TO '/u02/example01.dbf';

SQL> ALTER DATABASE DATAFILE '/oradatos/BDDWSGRD/DATOS04.dbf' AUTOEXTEND
ON



VISTAS

V$LOG, V$LOGFILE, V$LOG_HISTORY, V$DATAFILE, DBA_DATAFILES, DBA_TEMP_FILES, DBA_USERS, DBA_TS_QUOTAS, DBA_SEGMENTS, USERS_SEGMENTS, V$DBA_FREE_SPACE, USER_FREE_SPACE


CREACION DE TABLESPACES


SQL> CREATE SMALLFILE
TABLESPACE "DATOS"
LOGGING
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' SIZE 5M,
'/u01/app/oracle/oradata/POWER/DATOS02.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> DROP TABLESPACE DATOS

SQL> ALTER TABLESPACE DATOS
DEFAUL STORAGE ( INITIAL 2M NEXT 2M MAXEXTENTS 999);

SQL> drop tablespace datos including contents and datafiles;

Tablespace borrado.

SQL> alter database default temporary tablespace temp2

SQL> ALTER TABLESPACE DATOS OFFLINE;

SQL> ALTER TABLESPACE DATOS ONLINE;

SQL> ALTER TABLESPACE DATOS READ ONLY;


SQL> ALTER TABLESPACE DATOS READ WRITE

SQL> ALTER TABLESPACE DATOS RENAME TO DATOS1;

SQL> ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' RESIZE
10M ;

TABLESPACE UNDO

SQL> ALTER TABLESPACE DATOS RETENTION NOGUARANTEE

SQL> ALTER TABLESPACE DATOS RETENTION GUARANTEE

SQL> ALTER TABLESPACE DATOS NOLOGGING;

SQL> ALTER TABLESPACE DATOS LOGGING;

SQL> ALTER TABLESPACE DATOS BEGIN BACKUP;

Tablespace modificado.

SQL> ALTER TABLESPACE DATOS END BACKUP;

Tablespace modificado.

CREACION TABLESPACE BIGFILE

SQL> CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

SQL> ALTER TABLESPACE DATOS RESIZE 80G;

SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMPO TEMPFILE '/u01/app/oracle/oradata/POWER/TEMPO01.dbf' SIZE 20M, '/u01/app/oracle/oradata/POWER/TEMPO02.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M




CREACION TABLESPACE UNDO

SQL> CREATE UNDO TABLESPACE UNDO2 datafile '/u01/app/oracle/oradata/POWER/undo2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1G

PACKAGE TABLESPACE
DBMS_SPACE_ADMIN

VISTAS TABLESPACES

DBA_FREE_SPACE
DBA_DATA_FILES
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
DBA_EXTENTS
USER_EXTENTS
V$DATAFILE
V$DATAFILE_HEADER


SQL> alter system set db_16k_cache_size=16384;

Sistema modificado.

SQL> CREATE SMALLFILE
TABLESPACE "DATOS"
LOGGING
DATAFILE '/u01/app/oracle/oradata/POWER/DATOS01.dbf' SIZE 5M,
'/u01/app/oracle/oradata/POWER/DATOS02.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K


CREACION DE USUARIOS

SQL> CREATE USER DBO IDENTIFIED BY PRUEBA DEFAULT TABLESPACE USERS;

SQL> CREATE USER DBO IDENTIFIED BY PRUEBA
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP

SQL> alter user dbo account lock;

SQL> alter user dbo account unlock;

SQL> drop user dbo;


CREACION DE PERFILES


SQL> CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;


SQL> DROP PROFILE APP_USER;

SQL> ALTER PROFILE app_user
LIMIT PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED;

SQL> GRANT CREATE SESSION TO DBO;

SQL> GRANT RESTRICTED SESSION TO DBO;

SQL> GRANT CREATE SESSION TO DBO WITH ADMIN OPTION;


CREACION DE ROLES

SQL> CREATE ROLE SALES;

SQL> CREATE ROLE SALES IDENTIFIED BY SALES;

SQL> DROP ROLE SALES;

SQL> GRANT SALES TO DBO;

SQL> ALTER USER DBO DEFAULT ROLE SALES;

SQL> REVOKE SALES FROM DBO;

POLITICAS DE VIOLACIONES ORACLE 10G CUANDO SE INSTALA EL MOTOR

SQL> revoke execute on utl_file from public;
SQL> revoke execute on DBMS_RANDOM from public;
SQL> revoke execute on UTL_HTTP from public;
SQL> revoke execute on UTL_SMTP from public;
SQL> revoke execute on UTL_TCP from public;
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;

VISTAS ROLES

DBA_ROLES
DBA_SYS_PRIVS
ROLE_ROLE_PRIVS
DBA_TAB_PRIVS
DBA_COLS_PRIVS
ALL_COLS_PRIVS
USER_COLS_PRIVS


SQL> GRANT CONNECT, RESOURCE TO DBO;

SQL> ALTER USER DBO QUOTA UNLIMITED ON USERS;


USING ORAPWD

orapwd file= password= entries = force=

tnsping ORCL


CREACION DE TABLAS

CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn_demo NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn_demo NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn_demo NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn_demo NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_nn_demo NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min_demo
CHECK (salary > 0)
, CONSTRAINT emp_email_uk_demo
UNIQUE (email)
) ;

CREATE TABLE employees_demo
( employee_id NUMBER(6)
first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn_demo NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn_demo NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn_demo NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn_demo NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_nn_demo NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, dn VARCHAR2(300)
, CONSTRAINT emp_salary_min_demo
CHECK (salary > 0)
, CONSTRAINT emp_email_uk_demo
UNIQUE (email)
)
TABLESPACE USERS
STORAGE (INITIAL 6144
NEXT 6144
MINEXTENTS 1
MAXEXTENTS 5 );

SQL> CREATE DIRECTORY external AS '/u01/app/oracle/external';

Directorio creado.


SQL> ALTER TABLE EMPLOYEE
MOVE TABLESPACE DATOS;

SQL> TRUCATE TABLE EMPLOYEE;

SQL> DROP TABLE EMPLOYER;

SQL> DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

SQL> DROP TABLE EMPLOYEE PURGE;

SQL> SELECT * FROM RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;


SQL> ALTER TABLE EMPLOYEE DROP COLUMN A;
Tabla modificada.


EJEMPLO TABLA UNUSED

SQL> ALTER TABLE EMPLOYEE SET UNUSED COLUMN A;

Tabla modificada.

SQL> insert into employee values ('PRIMERO','SEGUNDO');
insert into employee values ('PRIMERO','SEGUNDO')
*
ERROR en linea 1:
ORA-00913: demasiados valores


SQL> insert into employee values ('PRIMERO');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> DESC EMPLOYEE;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
B VARCHAR2(30)

SQL> ALTER TABLE EMPLOYEE DROP UNUSED COLUMNS;

Tabla modificada.

SQL> ALTER TABLE EMPLOYEE DROP UNUSED COLUMNS CHECKPOINT 250;

SQL> ALTER TABLE EMPLOYEE DROP CONSTRAIN EMP_ID;

SQL> ALTER TABLE EMPLOYEE MODIFY B ENCRYPT;

SQL> ALTER TABLE EMPLOYEE MODIFY B DECRYPT;


SQL> analyze table DES_TIPO_LINEA compute statistics;

SQL> analyze table DES_TIPO_LINEA VALIDATE STRUCTURE;


SQL> ALTER TABLE EMPLOYEE ENABLE ROW MOVEMENT;

SQL> ALTER TABLE EMPLOYEE SHRINK SPACE;

Tabla modificada.

SQL> ALTER TABLE EMPLOYEE SHRINK SPACE COMPACT;

Tabla modificada.

ADICIONAR COLUMNA A TABLA

SQL> ALTER TABLE EMPLOYEE ADD (BONUS NUMBER(7,2));

Tabla modificada.

SQL> CREATE TABLE PRUEBA AS (SELECT * FROM PER_PERSONA);

Tabla creada.


LABORATORIO DE FLASHBACK DATABASE

SQL> create table prueba as (select * from per_sexo);

Tabla creada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853235 YES

SQL> desc prueba;
Nombre ?Nulo? Tipo
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
CODIGO_SEXO NOT NULL NUMBER(10)
DESCRIPCION NOT NULL VARCHAR2(40)
ABREVIATURA NOT NULL VARCHAR2(20)
USUARIO_CREACION NOT NULL VARCHAR2(40)
FECHA_CREACION NOT NULL DATE
USUARIO_MODIFICA NOT NULL VARCHAR2(40)
FECHA_MODIFICA NOT NULL DATE
USUARIO_RED NOT NULL VARCHAR2(40)
MAQUINA NOT NULL VARCHAR2(40)
ESTADO NOT NULL NUMBER(10)

SQL> update prueba set estado = 50
2 ;

2 filas actualizadas.

SQL> commit;

Confirmacion terminada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853288 YES

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853291 YES

SQL> connect sys/sys as sysdba
Conectado.
SQL> flashback database to scn 853235;
flashback database to scn 853235
*
ERROR en linea 1:
ORA-38757: La base de datos debe estar montada y no estar abierta para FLASHBACK.


SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 104857600 bytes
Fixed Size 1217932 bytes
Variable Size 71305844 bytes
Database Buffers 25165824 bytes
Redo Buffers 7168000 bytes
Base de datos montada.
SQL> flashback database to scn 853235;

Flashback terminado.

SQL> alter database open resetlogs;

SQL> connect dbo/password
Conectado.

SQL> select estado from prueba;

ESTADO
----------
1
1

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_F
-------------------- --------
764664 02/01/08

General Index Examples
The following statement shows how the sample index
Creating an Index: Example
ord_customer_ix on the customer_id column of the sample table oe.orders
was created:

CREATE INDEX ord_customer_ix
ON orders (customer_id);

To create the ord_customer_ix_demo index
Compressing an Index: Example
with the COMPRESS clause, you might issue the following statement:

CREATE INDEX ord_customer_ix_demo
ON orders (customer_id, sales_rep_id)
COMPRESS 1;
The index will compress repeated occurrences of customer_id column values.
If the sample table orders had
Creating an Index in NOLOGGING Mode: Example
been created using a fast parallel load (so all rows were already sorted), you could
issue the following statement to quickly create an index.
/* Unless you first sort the table oe.orders, this example fails
because you cannot specify NOSORT unless the base table is
already sorted.
*/

CREATE INDEX ord_customer_ix_demo
ON orders (order_mode)
NOSORT
NOLOGGING;
To create an index for the personnel cluster,

Creating a Cluster Index: Example
which was created in "Creating a Cluster: Example" on page 14-7, issue the following
statement:

CREATE INDEX idx_personnel ON CLUSTER personnel;

Creating an Index on an XMLType Table: Example The following example creates an
index on the area element of the xwarehouses table (created in "XMLType Table
Examples" on page 16-55):

CREATE INDEX area_index ON xwarehouses e
(EXTRACTVALUE(VALUE(e),'/Warehouse/Area'));

Creating a Function-Based Index: Example The following statement creates a
function-based index on the employees table based on an uppercase evaluation of the
last_name column:

CREATE INDEX upper_ix ON employees (UPPER(last_name));

Partitioned Index Examples
The following statement
Creating a Range-Partitioned Global Index: Example
creates a global prefixed index cost_ix on the sample table sh.sales with three
partitions that divide the range of costs into three groups:
CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
Creating a Hash-Partitioned Global Index: Example The following statement creates
a hash-partitioned global index cust_last_name_ix on the sample table
sh.customers with four partitions:
CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
GLOBAL PARTITION BY HASH (cust_last_name)
PARTITIONS 4;

The following statement
Creating an Index on a Hash-Partitioned Table: Example
creates a local index on the product_id column of the hash_products partitioned
table (which was created in "Hash Partitioning Example" on page 16-57). The STORE
IN clause immediately following LOCAL indicates that hash_products is hash
partitioned. Oracle Database will distribute the hash partitions between the tbs1 and
tbs2 tablespaces:

CREATE INDEX prod_idx ON hash_products(product_id) LOCAL
STORE IN (tbs_01, tbs_02);

Creating an Index on a Composite-Partitioned Table: Example The following
statement creates a local index on the composite_sales table, which was created in
"Composite-Partitioned Table Examples" on page 16-58. The STORAGE clause specifies
default storage attributes for the index. However, this default is overridden for the five
subpartitions of partitions q3_2000 and q4_2000, because separate TABLESPACE
storage is specified.
The creator of the index must have quota on the tablespaces specified. See CREATE
TABLESPACE on page 16-61 for examples that create tablespaces tbs_1 and tbs_2.
CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
LOCAL
(PARTITION q1_1998,
PARTITION q2_1998,
PARTITION q3_1998,
PARTITION q4_1998,
PARTITION q1_1999,
PARTITION q2_1999,
ARTITION q3_1999,
PARTITION q4_1999,
PARTITION q1_2000,
PARTITION q2_2000
(SUBPARTITION pq2001, SUBPARTITION pq2002,
SUBPARTITION pq2003, SUBPARTITION pq2004,
SUBPARTITION pq2005, SUBPARTITION pq2006,
SUBPARTITION pq2007, SUBPARTITION pq2008),
PARTITION q3_2000
(SUBPARTITION c1 TABLESPACE tbs_02,
SUBPARTITION c2 TABLESPACE tbs_02,
SUBPARTITION c3 TABLESPACE tbs_02,
SUBPARTITION c4 TABLESPACE tbs_02,
SUBPARTITION c5 TABLESPACE tbs_02),
PARTITION q4_2000
(SUBPARTITION pq4001 TABLESPACE tbs_03,
SUBPARTITION pq4002 TABLESPACE tbs_03,
SUBPARTITION pq4003 TABLESPACE tbs_03,
SUBPARTITION pq4004 TABLESPACE tbs_03)
);

Bitmap Index Example
The following creates a bitmap join index on the table oe.hash_products, which
was created in "Hash Partitioning Example" on page 16-57:
CREATE BITMAP INDEX product_bm_ix
ON hash_products(list_price)
TABLESPACE tbs_1
LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,
PARTITION ix_p2,
PARTITION ix_p3 TABLESPACE tbs_03,
PARTITION ix_p4,
PARTITION ix_p5 TABLESPACE tbs_04 );

ndexes on Nested Tables: Example
The sample table pm.print_media contains a nested table column ad_textdocs_
ntab, which is stored in storage table textdocs_nestedtab. The following example
creates a unique index on storage table textdocs_nestedtab:

CREATE UNIQUE INDEX nested_tab_ix
ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);


CREACION DE TABLAS

CREACION DE TABLA POR RANGE

CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;

CREACION DE TABLA POR LIST

CREATE TABLE list_customers
( customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, cust_address CUST_ADDRESS_TYP
, nls_territory VARCHAR2(30)
, cust_email VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));





CREACION DE TABLA POR HASH

CREATE TABLE hash_products
( product_id NUMBER(6)
, product_name VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id NUMBER(2)
, weight_class NUMBER(1)
, warranty_period INTERVAL YEAR TO MONTH
, supplier_id NUMBER(6)
, product_status VARCHAR2(20)
, list_price NUMBER(8,2)
, min_price NUMBER(8,2)
, catalog_url VARCHAR2(50)
, CONSTRAINT product_status_lov_demo
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 5
STORE IN (tbs_01, tbs_02, tbs_03, tbs_04)

CREACION DE TABLA COMPOSITE PARTITIONED


CREATE TABLE composite_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))
SUBPARTITIONS 8,
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))
(SUBPARTITION ch_c,
SUBPARTITION ch_i,
SUBPARTITION ch_p,
SUBPARTITION ch_s,
SUBPARTITION ch_t),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 4)
;


CREACION DE TABLA RANGE – SUBPARTITION


CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east VALUES
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE))


ALTER TABLE

ALTER TABLE employees ADD CONSTRAINT check_comp
CHECK (salary + (commission_pct*salary) <= 5000) DISABLE; ALTER TABLE employees ENABLE ALL TRIGGERS; ALTER TABLE employees DEALLOCATE UNUSED; ALTER TABLE customers RENAME COLUMN credit_limit TO credit_amount; ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('MEXICO', 'COLOMBIA') INTO (PARTITION south, PARTITION rest); ALTER TABLE list_customers MERGE PARTITIONS asia, rest INTO PARTITION rest; ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('CHINA', 'THAILAND') INTO (PARTITION asia, partition rest); ALTER TABLE print_media_part DROP PARTITION p3; ALTER TABLE print_media_part MOVE PARTITION p2b TABLESPACE omf_ts1; ALTER TABLE sales RENAME PARTITION sales_q4_2003 TO sales_currentq; ; ALTER TABLE print_media_demo TRUNCATE PARTITION p1 DROP STORAGE; ALTER TABLE countries ADD (duty_pct NUMBER(2,2) CHECK (duty_pct <> alter table per_sexo storage(buffer_pool keep);

SQL> alter table per_sexo storage(buffer_pool default);

CREACION DE SCHEMA

CREATE SCHEMA AUTHORIZATION oe
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED'
GRANT select ON new_product_view TO hr;



CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources

SQL> ANALYZE TABLE PER_PERSONA VALIDATE STRUCTURE;

SQL> ANALYZE TABLE PER_PERSONA VALIDATE STRUCTURE CASCADE;

SQL> TRUNCATE TABLE EMP;

SQL> ALTER TABLE PER_SEXO ENABLE ALL TRIGGERS;

SQL> ALTER TABLE PER_SEXO DISABLE ALL TRIGGERS;

SQL> ALTER TABLE DEPT
RENAME CONSTRAINT DNAME_UK TO DNAME_UNIKEY;

SQL> ALTER VIEW EMP_DEPT COMPILE;

SQL> ALTER PACKAGE ACCT_MGMT COMPILE BODY;

SQL> ALTER PACKAGE ACCT_MGMT COMPILE BODY;



The following statement opens the database in
READ ONLY / READ WRITE: Example
read-only mode:

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> ALTER DATABASE OPEN READ WRITE RESETLOGS;

SQL> ALTER DATABASE
RECOVER TABLESPACE tbs_03
PARALLEL;

SQL> ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K;

SQL> ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log');

SQL> ALTER DATABASE
ADD LOGFILE MEMBER 'diskc:log3.log'
TO GROUP 3;

SQL> ALTER DATABASE
DROP LOGFILE MEMBER 'diskb:log3.log';

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> ALTER DATABASE
RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';

Setting the Default Type of Tablespaces: Example

SQL> ALTER DATABASE
SET DEFAULT BIGFILE TABLESPACE;


SQL> ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE tbs_05;

SQL> ALTER DATABASE
CREATE DATAFILE 'tbs_f03.dbf'
AS 'tbs_f04.dbf';

SQL> ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;

SQL> ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';

SQL> ALTER DATABASE
RENAME GLOBAL_NAME TO demo.world.oracle.com;

Enabling and Disabling Block Change Tracking: Examples The following statement
enables block change tracking and causes Oracle Database to create a block change
tracking file named tracking_file and overwrite the file if it already exists:

SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE 'tracking_file' REUSE;

The following statement disables block change tracking and deletes the existing block
change tracking file:

SQL> ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;

SQL> ALTER DATABASE
CLEAR LOGFILE 'diskc:log3.log';

SQL> ALTER DATABASE
RECOVER AUTOMATIC DATABASE;

SQL> ALTER DATABASE
RECOVER LOGFILE 'diskc:log3.log';


SQL> ALTER DATABASE
RECOVER STANDBY DATAFILE '/finance/stbs_21.f'
UNTIL CONTROLFILE;

The following statement performs time-based recovery of the database:

SQL> ALTER DATABASE
RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00';


Enabling Parallel DML: Example
DML mode for the current session:

SQL> ALTER SESSION ENABLE PARALLEL DML;

SQL> ALTER SESSION
CLOSE DATABASE LINK local;

SQL> ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

Changing the Decimal Character and Group Separator: Example The following
statement dynamically changes the decimal character to comma (,) and the group
separator to period (.):

SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' ;


Enabling SQL Trace: Example To enable the SQL trace facility for your session, issue
the following statement:

SQL> ALTER SESSION
SET SQL_TRACE = TRUE;

Enabling Query Rewrite: Example This statement enables query rewrite in the
current session for all materialized views that have not been explicitly disabled:

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SQL> ALTER SESSION CURRENT_SCHEMA = ' ';
SQL> alter table per_persona enable row movement;
SQL> ALTER TABLE PER_PERSONA SHRINK SPACE;
SQL> ALTER TABLE DEPT MODIFY PARTITION DEPT_P1 SHRINK SPACE;
SQL> EXEC DBMS_UTILITY.analyze_schema('DBO','COMPUTE');
SQL> EXEC DBMS_STATS.gather_schema_stats('DBO');

VISTAS

DBA_OBJECTS
ALL_OBJECTS
USER_OBJECTS
DBA_CATALOG
ALL_CATALOG
USER_CATALOG
DBA_DEPENDENCIES
ALL_DEPENDENCIES
USER_DEPENDENCIES
DBA_EXTENTS
USER_EXTENTS
DBA_FREE_SPACE
USER_FREE_SPACE

PACKAGE DBMS_SPACE

VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
exec DBMS_SPACE.UNUSED_SPACE('DBO', 'PER_SEXO', 'TABLE', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock)
/







DBA_UPDATABLE_COLUMNS;
ALL_UPDATABLE_COLUMNS;
USER_UPDATABLE_COLUMNS;


SQL> CREATE SEQUENCE EMP_SEQUENCE INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;

SQL> ALTER SEQUENCE EMP_SEQUENCE INCREMENT BY 10 MAXVALUE 100000 CYCLE;

SQL> INSERT INTO ORDERS (ORDERNO,CUSTNO) VALUES (ORDERS_SEQ.NETVAL,1032);

SQL> SELECT ORDER_SEQ.NEXTVAL FROM DUAL;

CREACION DE SINONIMOS

SQL> CREATE PUBLIC SYNONYM CLIENTE FOR DBO.PER_PERSONA;

SQL> DROP PUBLIC SYNONYM CLIENTE;


VISTAS

DBA_VIEWS
ALL_VIEWS
USER_VIEWS
DBA_SYNONYMS
ALL_SYNONYMS
USER_SYNONYMS
DBA_SEQUENCES
ALL_SEQUENCES
USER_SEQUENCES
DBA_UPDATABLE_COLUMNS
ALL_UPDATABLE_COLUMNS
USER_UPDATABLE_COLUMNS





CONFIGURACION DE PARAMETROS MEMORIA

SQL> alter system set sga_max_size = 8G scope = spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> alter system set sga_target = 8G scope = spfile;

System altered.


Administración Básica de Oracle 10g. Prácticas sobre trabajos.
PRACTICAS.
TRABAJOS.
1. Identificar los procesos de sistema operativo que corresponden a la base de
datos, ¿existe algún proceso coordinador de trabajos -cjqNNN- corriendo en
la instancia?.
/home/CURSO/curso01 (CURSO01)> ps -efgrep cj
curso01 25608 25524 0 13:11 pts/2 00:00:00 grep cj
2. Ver el contenido del paquete dbms_scheduler.
Revisar documentación en linea: “Oracle Database PL/SQL Packages and Types
Reference”.
3. Ver la descripción de las vistas dba_scheduler_jobs y
dba_scheduler_job_log. Buscar la descripción de cada uno de los campos en
la documentación en línea.
SQL> desc dba_scheduler_jobs
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
JOB_NAME NOT NULL VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
JOB_CREATOR VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
GLOBAL_UID VARCHAR2(32)
PROGRAM_OWNER VARCHAR2(4000)
PROGRAM_NAME VARCHAR2(4000)
JOB_TYPE VARCHAR2(16)
JOB_ACTION VARCHAR2(4000)
NUMBER_OF_ARGUMENTS NUMBER
SCHEDULE_OWNER VARCHAR2(4000)
SCHEDULE_NAME VARCHAR2(4000)
SCHEDULE_TYPE VARCHAR2(12)
START_DATE TIMESTAMP(6) WITH TIME ZONE
REPEAT_INTERVAL VARCHAR2(4000)
EVENT_QUEUE_OWNER VARCHAR2(30)
1
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
EVENT_QUEUE_NAME VARCHAR2(30)
EVENT_QUEUE_AGENT VARCHAR2(30)
EVENT_CONDITION VARCHAR2(4000)
EVENT_RULE VARCHAR2(65)
END_DATE TIMESTAMP(6) WITH TIME ZONE
JOB_CLASS VARCHAR2(30)
ENABLED VARCHAR2(5)
AUTO_DROP VARCHAR2(5)
RESTARTABLE VARCHAR2(5)
STATE VARCHAR2(15)
JOB_PRIORITY NUMBER
RUN_COUNT NUMBER
MAX_RUNS NUMBER
FAILURE_COUNT NUMBER
MAX_FAILURES NUMBER
RETRY_COUNT NUMBER
LAST_START_DATE TIMESTAMP(6) WITH TIME ZONE
LAST_RUN_DURATION INTERVAL DAY(9) TO SECOND(6)
NEXT_RUN_DATE TIMESTAMP(6) WITH TIME ZONE
SCHEDULE_LIMIT INTERVAL DAY(3) TO SECOND(0)
MAX_RUN_DURATION INTERVAL DAY(3) TO SECOND(0)
LOGGING_LEVEL VARCHAR2(4)
STOP_ON_WINDOW_CLOSE VARCHAR2(5)
INSTANCE_STICKINESS VARCHAR2(5)
RAISE_EVENTS VARCHAR2(4000)
SYSTEM VARCHAR2(5)
JOB_WEIGHT NUMBER
NLS_ENV VARCHAR2(4000)
SOURCE VARCHAR2(128)
DESTINATION VARCHAR2(128)
COMMENTS VARCHAR2(240)
FLAGS NUMBER
SQL> desc dba_scheduler_job_log
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
LOG_ID NOT NULL NUMBER
LOG_DATE TIMESTAMP(6) WITH TIME ZONE
OWNER VARCHAR2(30)
JOB_NAME VARCHAR2(65)
JOB_SUBNAME VARCHAR2(65)
JOB_CLASS VARCHAR2(30)
OPERATION VARCHAR2(30)
STATUS VARCHAR2(30)
USER_NAME VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
2
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
GLOBAL_UID VARCHAR2(32)
ADDITIONAL_INFO CLOB

4. Como usuario SYSTEM crear un trabajo que llamará al procedimiento
descrito seguidamente, también propiedad del SYSTEM, que permite analizar
el esquema de un cierto numero de usuarios y generar las estadísticas
internas usadas por el analizador sintáctico de Oracle (previamente deben
crearse la tabla “usuarios_estadisticas” y el procedimiento
“analiza_usuarios”).
Se ejecutara en el momento de su creación y con periodicidad semanal.

create table usuarios_estadisticas (username varchar2(30) not null,
fecha date,
error varchar2(80))
tablespace users
storage (initial 16K next 16K maxextents 10);

CREATE OR REPLACE procedure analiza_usuarios as
cursor usuarios is
select username,rowid from system.usuarios_estadisticas;
werror varchar2(80);
wrowid urowid;
begin
FOR rec_usuarios IN usuarios LOOP
wrowid:=rec_usuarios.rowid;
begin
DBMS_UTILITY.ANALYZE_SCHEMA(rec_usuarios.username,'ESTIMATE',
NULL,3);
update system.usuarios_estadisticas
set fecha=sysdate, error=null
where rowid=rec_usuarios.rowid;
exception
when others then
werror:=rpad(sqlerrm,80);
update system.usuarios_estadisticas
set error=werror, fecha=sysdate
where rowid=wrowid;
end;
END LOOP;
end analiza_usuarios;
/
3
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
Crear un "script" llamado crea_trabajo1.sql, por ejemplo, con el
contenido siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo_1.sql

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_ANALIZA',
job_type => 'STORED_PROCEDURE',
job_action => 'ANALIZA_USUARIOS',
start_date => SYSDATE,
repeat_interval => 'FREQ = WEEKLY');
END;
/
Ejecutarlo desde sqlplus:
SQL> @crea_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
5. Comprobar en las vistas la información sobre el trabajo
“TRABAJO_ANALIZA”. ¿Está el trabajo habilitado o no?, en caso de estar
deshabilitado ... habilitar el trabajo para su ejecución y comprobar de nuevo
sus características.
SQL> select owner, job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
OWNER JOB_NAME JOB_CREATOR
------------------------------ ------------------------------ -------------------------
CLIENT_ID JOB_TYPE
---------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
----------------------------------------------------------------------------------------------
SYSTEM TRABAJO_ANALIZA SYSTEM
STORED_PROCEDURE
08/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
4
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
TRUE FALSE FALSE
Como se observa en la consulta, el trabajo está deshabilitado (columna
ENABLED igual a FALSE). También se observa que el trabajo será borrado
cuando se complete (AUTO_DROP igual a TRUE).
Se habilita el trabajo para su ejecución:
/home/CURSO/curso01 (CURSO01)> vi activa_trabajo1.sql

BEGIN
DBMS_SCHEDULER.ENABLE('TRABAJO_ANALIZA');
END;
/
~
"activa_trabajo1.sql" 4L, 55C escritos
Se lanza el procedimiento DBMS_SCHEDULER.ENABLE y se comprueba las
características del trabajo:
SQL> @activa_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select owner, job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
OWNER JOB_NAME JOB_CREATOR
-------------------------------------------------------------------------------------
CLIENT_ID JOB_TYPE
-------------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
---------------------------------------------------------------------------------------------
SYSTEM TRABAJO_ANALIZA SYSTEM
STORED_PROCEDURE
08/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
TRUE TRUE FALSE
5
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
6. ¿Cuál es el valor para el máximo número de ejecuciones y máximo número
de fallos para este trabajo?. ¿Y su número de fallos?.
SQL> select job_name, max_runs, max_failures, failure_count from
dba_scheduler_jobs where owner='SYSTEM' and job_name ='TRABAJO_ANALIZA';
JOB_NAME MAX_RUNS MAX_FAILURES FAILURE_COUNT
-----------------------------------------------------------------------------------------
TRABAJO_ANALIZA 0
7. Forzar la ejecución del trabajo “TRABAJO_ANALIZA”. Comprobar antes y
después de forzar la ejecución el valor para el número de ejecuciones,
última fecha de ejecución y duración de la última ejecución (“RUN_COUNT”,
“LAST_START_DATE” y “LAST_RUN_DURATION” respectivamente).
/home/CURSO/curso01 (CURSO01)> vi fuerza_trabajo1.sql
BEGIN
DBMS_SCHEDULER.RUN_JOB('TRABAJO_ANALIZA',FALSE);
END;
/
~
"fuerza_trabajo1.sql" 4L, 62C escritos
SQL> select run_count, to_char(last_start_date,'dd-mm-yyyy hh:mi') FECHA,
last_run_duration DURACION from dba_scheduler_jobs where owner='SYSTEM'
and job_name ='TRABAJO_ANALIZA';
RUN_COUNT FECHA DURACION
----------------------------------------------------------
0
SQL> @fuerza_trabajo1.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select run_count, to_char(last_start_date,'dd-mm-yyyy hh:mi') FECHA,
last_run_duration DURACION from dba_scheduler_jobs where owner='SYSTEM' and
job_name ='TRABAJO_ANALIZA';
RUN_COUNT FECHA DURACION
-----------------------------------------------------------------------------------
1 08-12-2006 04:12 +000000000 00:00:00.168563
6
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
8. Como usuario “prueba01” crear un trabajo que llamara al procedimiento
descrito seguidamente, también propiedad de “prueba01”, que permite
averiguar cuáles de las tablas propiedad del usuario tienen ocupado más del
80% de las extensiones que le son permitidas (debe tener cuota sobre el
espacio de almacenamiento donde se creará la tabla y permiso para crear
procedimientos).
Insertará una fila en la tabla “tablas_revision” por cada una de las tablas
que cumplan la condición. Se ejecutará cada hora.
Previamente debe crearse la siguiente tabla:
CREATE TABLE tablas_revision
(nombre_tabla varchar2(30),
ocupacion number)
storage (initial 100k next 100k);
¿En que espacio de almacenamiento se ha creado la tabla?, ¿con que
parámetros de almacenamiento?.
CREATE OR REPLACE procedure chequear_tablas as
extensiones integer;
maximo_extensiones integer;
ocupacion integer;
porcentaje_extensiones integer := 80;
cursor c_tablas is select table_name from user_tables;
begin
FOR rec_tables IN c_tablas LOOP
select count(*) into extensiones
from user_extents
where segment_name=rec_tables.table_name;
select max_extents into maximo_extensiones
from user_tables
where table_name=rec_tables.table_name;
ocupacion:=(round(extensiones*100/maximo_extensiones));
IF (ocupacion > porcentaje_extensiones) THEN
insert into tablas_revision values (rec_tables.table_name,
ocupacion);
commit;
7
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
END IF;
END LOOP;
end chequear_tablas;
/
SQL> connect prueba01
Introduzca su clave:
Conectado.
SQL> CREATE TABLE tablas_revision
2 (nombre_tabla varchar2(30),
3 ocupacion number)
4 storage (initial 100k next 100k);
Tabla creada.
Para ver las caracteristicas de almacenamiento de la tabla consultar la vista
"user_tables".
Crear un "script" llamado chequear_tablas.sql, por ejemplo, que contenga las
sentencias de creacion del procedimiento:
SQL> @chequear_tablas.sql
Procedimiento creado.
Crear un "script" llamado crea_trabajo2.sql, por ejemplo, con el contenido
siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo2.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_CHEQUEAR_TABLAS',
job_type => 'STORED_PROCEDURE',
job_action => 'CHEQUEAR_TABLAS',
start_date => SYSDATE,
repeat_interval => 'FREQ = HOURLY; INTERVAL=1',
ENABLED => TRUE,
AUTO_DROP => FALSE,
8
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
COMMENTS => 'Trabajo para chequear tablas cada hora');
END;
/
~
"crea_trabajo2.sql" 12L, 311C escritos
SQL> @crea_trabajo2.sql
BEGIN
*
ERROR en linea 1:
ORA-27486: privilegios insuficientes
ORA-06512: en "SYS.DBMS_ISCHED", linea 99
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 262
ORA-06512: en linea 2
¿Por qué se ha producido el error anterior al intentar crear el trabajo?: el
usuario “prueba01” carece de permisos para poder crear trabajos. Es
necesario otorgar el permiso “create job”.
SQL> connect system
Introduzca la contrase?a:
Conectado.
SQL> grant create job to prueba01;
Concesion terminada correctamente.
Se vuelve a realizar la conexión como “prueba01” y se ejecuta
“crea_trabajo2.sql”.
SQL> connect prueba01
Introduzca la contrase?a:
Conectado.
SQL> @crea_trabajo2.sql
Procedimiento PL/SQL terminado correctamente.
Se consultan las características del trabajo.
9
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
SQL> select job_name, job_creator, client_id, job_type, start_date,
repeat_interval, end_date, auto_drop, enabled, restartable from
user_scheduler_jobs
JOB_NAME JOB_CREATOR CLIENT_ID
JOB_TYPE
---------------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
---------------------------------------------------------------------------------------------
END_DATE AUTO_ ENABL RESTA
---------------------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS PRUEBA01
STORED_PROCEDURE
08/12/06 17:01:50,000000 +01:00
FREQ = HOURLY; INTERVAL=1
FALSE TRUE FALSE
9. Obtener, a nivel de sistema operativo, un listado de los procesos asociados a
la base de datos.
Forzar la ejecución del trabajo “TRABAJO_CHEQUEAR_TABLAS”, mientras
está ejecutándose volver a obtener, a nivel de sistema operativo, un listado
de los procesos asociados a la base de datos. ¿Qué procesos se observan en
ambos casos?.
Se crea un "script" llamado “fuerza_trabajo2.sql” con el contenido siguiente:
/home/CURSO/curso01 (CURSO01)> vi fuerza_trabajo2.sql
BEGIN
DBMS_SCHEDULER.RUN_JOB('TRABAJO_CHEQUEAR_TABLAS',FALSE);
END;
/
~
"fuerza_trabajo2.sql" 4L, 69C escritos
/home/CURSO/curso01 (CURSO01)>
Se obtiene el listado de los procesos asociados a la bd.
/home/CURSO/curso01 (CURSO01)> ps -efgrep CURSO01
oracle 18916 1 0 Dec03 ? 00:00:01 ora_pmon_CURSO01
...
10
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
oracle 27563 1 0 17:01 ? 00:00:00 ora_cjq0_CURSO01
curso01 27616 27594 0 17:17 pts/1 00:00:00 grep CURSO01
Como usuario “prueba01” se lanza el “script" “fuerza_trabajo2.sql”:
SQL> show user
USER es "PRUEBA01"
SQL> @fuerza_trabajo2.sql
Procedimiento PL/SQL terminado correctamente.
Se obtiene nuevamente el listado de los procesos asociados a la bd.
/home/CURSO/curso01 (CURSO01)> ps -efgrep CURSO01
oracle 18916 1 0 Dec03 ? 00:00:01 ora_pmon_CURSO01
...
oracle 27563 1 0 17:01 ? 00:00:00 ora_cjq0_CURSO01
oracle 27618 1 2 17:17 ? 00:00:00 ora_j000_CURSO01
curso01 27620 27594 0 17:17 pts/1 00:00:00 grep CURSO01
Si posteriormente se vuelve a ejecutar esta consulta, se observará que
ha desaparecido el proceso esclavo “ora_j000_CURSO01”.
10.Como usuario SYSTEM crear un trabajo que llame al procedimiento
“monitorizar_usuarios” descrito seguidamente, también propiedad del
SYSTEM, que permite monitorizar el numero de sesiones activas de usuario
en la instancia. Se ejecutará cada minuto.
CREATE OR REPLACE procedure monitorizar_usuarios as
sesiones number(4);
activas number(4);
cursor c_usuarios is
select usuario
from usuarios_monitorizados
where monitorizar = 'S';
begin
FOR rec_usuarios IN c_usuarios LOOP
select nvl(count(*),0) into sesiones
11
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
from v$session
where username=rec_usuarios.usuario
and status in ('ACTIVE','INACTIVE');
select nvl(count(*),0) into activas
from v$session
where username=rec_usuarios.usuario
and status = 'ACTIVE';
insert into usuarios_monitorizados_log
values (rec_usuarios.usuario,sysdate,sesiones,activas);
END LOOP;
commit;
end monitorizar_usuarios;
/
Previamente deben crearse las siguientes tablas:
CREATE TABLE usuarios_monitorizados_log
(usuario varchar2(30),
fecha_log date,
sesiones number(4),
activas number(4))
storage (initial 100k next 100k);
CREATE TABLE usuarios_monitorizados
(usuario varchar2(30),
monitorizar char(1),
descripcion varchar2(80))
storage (initial 100k next 100k);
SQL> CREATE TABLE usuarios_monitorizados_log
(usuario varchar2(30),
fecha_log date,
sesiones number(4),
activas number(4))
storage (initial 100k next 100k);
Tabla creada.
SQL> CREATE TABLE usuarios_monitorizados
(usuario varchar2(30),
monitorizar char(1),
descripcion varchar2(80))
12
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
storage (initial 100k next 100k);
Tabla creada.
Conectarse como usuario SYS y dar permisos sobre vista SESSION:
SQL> connect / as sysdba;
Conectado.
SQL> grant select on V_$SESSION to system;
Concesion terminada correctamente.
Crear un "script" llamado monitorizar_usuarios.sql, por ejemplo, que contenga
las sentencias de creacion del procedimiento:
SQL> @monitorizar_usuarios.sql
Procedimiento creado.
Crear un "script" llamado crea_trabajo3.sql, por ejemplo, con el contenido
siguiente:
/home/CURSO/curso01 (CURSO01)> vi crea_trabajo3.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TRABAJO_MONITORIZAR_USUARIOS',
job_type => 'STORED_PROCEDURE',
job_action => 'MONITORIZAR_USUARIOS',
start_date => SYSDATE,
repeat_interval => 'FREQ = MINUTELY; INTERVAL=1',
ENABLED => TRUE,
AUTO_DROP => FALSE,
COMMENTS => 'Trabajo para monitorizar usuarios');
END;
/
~
"crea_trabajo3.sql" 12L, 314C escritos
SQL> @crea_trabajo3.sql
Procedimiento PL/SQL terminado correctamente.
13
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
11. Monitorizar el número de conexiones del usuario “prueba01”.
Se crea una entrada en la tabla “usuarios_monitorizados” indicando que se
quiere monitorizar al usuario “prueba01”.
SQL> desc usuarios_monitorizados
Nombre ?Nulo? Tipo
---------------------------------------------------------------------
USUARIO VARCHAR2(30)
MONITORIZAR CHAR(1)
DESCRIPCION VARCHAR2(80)
SQL> insert into usuarios_monitorizados values ('PRUEBA01','S',null);
1 fila creada.
SQL> commit;
Confirmacion terminada.
Se abren distintas sesiones del usuario “prueba01”, cuyo número quedará
reflejado en “usuarios_monitorizados_log”.
SQL> select USUARIO, to_char(FECHA_LOG,'dd-mm-yyyy hh:mi:ss') FECHA,
SESIONES, ACTIVAS from usuarios_monitorizados_log order by 2;
USUARIO FECHA SESIONES ACTIVAS
------------------------------------------------------------------------------------------
PRUEBA01 09-12-2006 10:12:07 1 0
PRUEBA01 09-12-2006 10:12:07 2 0
PRUEBA01 09-12-2006 10:12:07 2 0
PRUEBA01 09-12-2006 11:12:07 2 0
12.Consultar todos los trabajos definidos para el usuario SYSTEM obteniendo el
nombre de trabajo, tipo de trabajo, fecha de la próxima ejecución, intervalo,
numero de fallos producidos y su estado.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where owner='SYSTEM' order by
job_name;
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
14
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
REPEAT_INTERVAL
FAILURE_COUNT ENABL
-----------------------------------------------------------------------------------------------------
TRABAJO_ANALIZA STORED_PROCEDURE 08/12/06 14:41:19,000000
+01:00 15/12/06 14:41:19,000000 +01:00
FREQ = WEEKLY
0 TRUE
TRABAJO_MONITORIZAR_USUARIOS STORED_PROCEDURE 09/12/06
10:36:07,000000 +01:00 09/12/06 12:56:07,000000 +01:00
FREQ = MINUTELY; INTERVAL=1
0 TRUE
13.Consultar todos los trabajos definidos para el usuario PRUEBA01 obteniendo
el nombre de trabajo, tipo de trabajo, fecha de la próxima ejecución,
intervalo, numero de fallos producidos y su estado.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where owner='PRUEBA01' order
by job_name;
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
REPEAT_INTERVAL
FAILURE_COUNT ENABL
-----------------------------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS STORED_PROCEDURE 08/12/06
17:01:50,000000 +01:00 09/12/06 13:01:50,000000 +01:00
FREQ = HOURLY; INTERVAL=1
0 TRUE
14.Conectarse como usuario “prueba01” e intentar eliminar de la cola de
trabajos el trabajo “TRABAJO_MONITORIZAR_USUARIOS” perteneciente al
usuario SYSTEM, ¿qué sucede?.
Se crea un “script” de nombre, por ejemplo, “borra_analiza_usuarios.sql”
con el siguiente contenido:
/home/CURSO/curso01 (CURSO01)> vi borra_analiza_usuarios.sql
BEGIN
DBMS_SCHEDULER.DROP_JOB('TRABAJO_MONITORIZAR_USUARIOS');
15
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
END;
/
~
"borra_analiza_usuarios.sql" 4L, 70C escritos
SQL> connect prueba01
Introduzca la contrase?a:
Conectado.
SQL> @borra_analiza_usuarios.sql
BEGIN
*
ERROR en linea 1:
ORA-27475: "PRUEBA01.TRABAJO_MONITORIZAR_USUARIOS" debe ser job
ORA-06512: en "SYS.DBMS_ISCHED", linea 178
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 544
ORA-06512: en linea 2
No existe ningún trabajo perteneciente al usuario prueba01 llamado de esta
forma.
15.Deshabilitar el trabajo que realiza el chequeo de tablas,
“TRABAJO_CHEQUEAR_TABLAS” perteneciente al usuario PRUEBA01.
Consultar las características del trabajo.
SQL> show user
USER es "PRUEBA01"
SQL> begin
2 DBMS_SCHEDULER.DISABLE('TRABAJO_CHEQUEAR_TABLAS');
3 END;
4 /
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, enabled from user_scheduler_jobs where
job_name='TRABAJO_CHEQUEAR_TABLAS'
JOB_NAME JOB_TYPE ENABL
-------------------------------------------------------------------------------
TRABAJO_CHEQUEAR_TABLAS STORED_PROCEDURE FALSE
16
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
16.Conectarse como usuario SYSTEM y modificar el trabajo que llama al
procedimiento de monitorización de usuarios para que se realice cada dos
minutos. Consultar las nuevas características del trabajo.
/home/CURSO/curso01 (CURSO01)> vi modificar_atributos.sql
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('TRABAJO_MONITORIZAR_USUARIOS','REPEAT_IN
TERVAL','FREQ = MINUTELY; INTERVAL = 2');
END;
/
~
"modificar_atributos.sql" [Nuevo] 4L, 125C escritos
SQL> show user
USER es "SYSTEM"
SQL> @modificar_atributos.sql
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from dba_scheduler_jobs where
job_name='TRABAJO_MONITORIZAR_USUARIOS';
JOB_NAME JOB_TYPE START_DATE
NEXT_RUN_DATE
REPEAT_INTERVAL
FAILURE_COUNT ENABL
----------------------------------------------------------------------------------------------------
TRABAJO_MONITORIZAR_USUARIOS STORED_PROCEDURE 09/12/06
10:36:07,000000 +01:00 09/12/06 13:36:07,000000 +01:00
FREQ = MINUTELY; INTERVAL = 2
0 TRUE
17.Conectarse como usuario SYSTEM y eliminar el trabajo que realiza el
chequeo de ocupación de tablas (“TRABAJO_CHEQUEAR_TABLAS”).
SQL> SHOW USER
USER es "SYSTEM"
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB('TRABAJO_CHEQUEAR_TABLAS',TRUE);
3 END;
17
© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla
Administración Básica de Oracle 10g. Prácticas sobre trabajos.
4 /
BEGIN
*
ERROR en linea 1:
ORA-27475: "SYSTEM.TRABAJO_CHEQUEAR_TABLAS" debe ser job
ORA-06512: en "SYS.DBMS_ISCHED", linea 178
ORA-06512: en "SYS.DBMS_SCHEDULER", linea 544
ORA-06512: en linea 2
No existe ningún trabajo perteneciente al usuario SYSTEM llamado de
esta forma y ni siquiera el administrador puede borrar trabajos de otro
usuario.
18.Conectarse como usuario PRUEBA01 y eliminar el trabajo que realiza el
chequeo de ocupación de tablas (“TRABAJO_CHEQUEAR_TABLAS”).
SQL> show user
USER es "PRUEBA01"
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB('TRABAJO_CHEQUEAR_TABLAS',TRUE);
3 END;
4 /
Procedimiento PL/SQL terminado correctamente.
SQL> select job_name, job_type, start_date, next_run_date, repeat_interval,
failure_count, enabled from user_scheduler_jobs where
job_name='TRABAJO_CHEQUEAR_TABLAS';
ninguna fila seleccionada
18

alter tablespace temp add tempfile '/dev/md/db_olap/rdsk/d104' size 5119M,
'/dev/md/db_olap/rdsk/d105' size 5119M,
'/dev/md/db_olap/rdsk/d106' size 5119M
/

alter tablespace undotbs1 add datafile '/dev/md/db_olap/rdsk/d108' size 5119M
/

alter tablespace undotbs2 add datafile '/dev/md/db_olap/rdsk/d110' size 5119M
/


CREATE TABLESPACES RAW DEVICES EXAMPLE


create tablespace datos datafile '/dev/md/db_olap/rdsk/d201' size 10239M,
'/dev/md/db_olap/rdsk/d202' size 10239M,
'/dev/md/db_olap/rdsk/d203' size 10239M,
'/dev/md/db_olap/rdsk/d204' size 10239M,
'/dev/md/db_olap/rdsk/d205' size 10239M,
'/dev/md/db_olap/rdsk/d206' size 10239M,
'/dev/md/db_olap/rdsk/d207' size 10239M,
'/dev/md/db_olap/rdsk/d208' size 10239M,
'/dev/md/db_olap/rdsk/d209' size 10239M,
'/dev/md/db_olap/rdsk/d210' size 10239M,
'/dev/md/db_olap/rdsk/d211' size 10239M
/

create tablespace indices datafile '/dev/md/db_olap/rdsk/d301' size 10239M,
'/dev/md/db_olap/rdsk/d302' size 10239M,
'/dev/md/db_olap/rdsk/d303' size 10239M,
'/dev/md/db_olap/rdsk/d304' size 10239M,
'/dev/md/db_olap/rdsk/d305' size 10239M,
'/dev/md/db_olap/rdsk/d306' size 10239M,
'/dev/md/db_olap/rdsk/d307' size 10239M,
'/dev/md/db_olap/rdsk/d308' size 10239M,
'/dev/md/db_olap/rdsk/d309' size 10239M,
'/dev/md/db_olap/rdsk/d310' size 10239M,
'/dev/md/db_olap/rdsk/d311' size 10239M
/


-- RECONSTRUYE_INDICES.SQL --
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||
' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' SENTENCIA
from dba_ind_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';'
from dba_ind_partitions
where status='UNUSABLE';


GENERAR ESTADISTICAS TABLAS

set heading off;
set feedback off;
set echo off;
set pagesize 0;
set line 300
set termout off;
spool estaditicas_tablas.sql
select 'exec dbms_stats.gather_table_stats (ownname => ' || '''SPE''' || ', tabname => ' || ' ''' || tname || ''');' from tab;
spool off;
/


GENERAR ESTADISTICAS INDICES

set heading off;
set feedback off;
set echo off;
set pagesize 0;
set line 300;
set termout off;
spool estaditicas_indices.sql
select 'exec dbms_stats.gather_index_stats (ownname => ' || '''SPE''' || ', indname => ' || ' ''' || index_name || ''');' from dba_indexes where owner='SPE';
spool off;
/

Setting Alert Thresholds
For each tablespace, you can set just percent-full thresholds, just free-space-remaining thresholds, or both types of thresholds simultaneously. Setting either type of threshold to zero disables it.

The ideal setting for the warning threshold is one that issues an alert early enough for you to resolve the problem before it becomes critical. The critical threshold should be one that issues an alert still early enough so that you can take immediate action to avoid loss of service.

To set alert threshold values:

•For locally managed tablespaces, use Enterprise Manager (see Oracle Database 2 Day DBA for instructions) or the DBMS_SERVER_ALERT.SET_THRESHOLD package procedure (see Oracle Database PL/SQL Packages and Types Reference for usage details).

•For dictionary managed tablespaces, use Enterprise Manager. See Oracle Database 2 Day DBA for instructions.


Example—Locally Managed Tablespace

The following example sets the free-space-remaining thresholds in the USERS tablespace to 10 MB (warning) and 2 MB (critical), and disables the percent-full thresholds.

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_LE,
warning_value => '10240',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_LE,
critical_value => '2048',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');

DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '0',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '0',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/

Example: Creating a Repair TableThe following example creates a repair table for the users tablespace.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/

For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table and is prefixed by DBA_ (for exampl, DBA_REPAIR_TABLE or DBA_ORPHAN_KEY_TABLE).

The following query describes the repair table that was created for the users tablespace.

DESC REPAIR_TABLE

Name Null? Type
---------------------------- -------- --------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
Example: Creating an Orphan Key TableThis example illustrates the creation of an orphan key table for the users tablespace.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/

The orphan key table is described in the following query:

DESC ORPHAN_KEY_TABLE

Name Null? Type
---------------------------- -------- -----------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
Example: Detecting Corruption
The CHECK_OBJECT procedure checks the specified object, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.

Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.

The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table.

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

Example: Fixing Corrupt Blocks
Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECT procedure. Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp.

This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME=> 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

SQL*Plus outputs the following line:

num fix: 1

The following query confirms that the repair was done.

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE
Example: Finding Index Entries Pointing to Corrupt Data Blocks
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.

This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.

Note:

This should be run for every index associated with a table identified in the repair table.
In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.

SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'PK_DEPT',
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/

The following output indicates that there are three orphan keys:

orphan key count: 3

Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.

Example: Skipping Corrupt Blocks
The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.

The following example enables the skipping of software corrupt blocks for the scott.dept table:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/

Querying scott's tables using the DBA_TABLES view shows that SKIP_CORRUPT is enabled for table scott.dept.

SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES
WHERE OWNER = 'SCOTT';

OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
SCOTT ACCOUNT DISABLED
SCOTT BONUS DISABLED
SCOTT DEPT ENABLED
SCOTT DOCINDEX DISABLED
SCOTT EMP DISABLED
SCOTT RECEIPT DISABLED
SCOTT SALGRADE DISABLED
SCOTT SCOTT_EMP DISABLED
SCOTT SYS_IOT_OVER_12255 DISABLED
SCOTT WORK_AREA DISABLED

10 rows selected.


Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each user:

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
---------- --------- -------- ---------- ------- ----------
USERS JFEE 0 512000 0 250
USERS DCRANNEY 0 -1 0 -1

An index can be monitored to verify if it is used within the period between

alter index index_name monitoring usage;

The result is stored in v$object_usage. See also On verifying if an index is used.

SQL> desc v$object_usage;
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

SQL>